MySQL 常見索引優(yōu)化原則

目錄

1 構(gòu)造數(shù)據(jù)

2 索引優(yōu)化原則

1 構(gòu)造數(shù)據(jù)

-- 構(gòu)造表
CREATE TABLE `t2` (
 ?`a` int(11) NOT NULL,
 ?`b` int(11) DEFAULT NULL,
 ?`c` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
 ?PRIMARY KEY (`a`),
 ?KEY `idx_b` (`b`),
 ?KEY `idx_bc` (`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


-- 構(gòu)造數(shù)據(jù)
INSERT INTO t2 (a, b, c) VALUES (20, 2, 'e');
INSERT INTO t2 (a, b, c) VALUES (5, 3, 'y');
INSERT INTO t2 (a, b, c) VALUES (4, 4, 'a');
INSERT INTO t2 (a, b, c) VALUES (10, 4, 'o');
INSERT INTO t2 (a, b, c) VALUES (1, 4, 'u');
INSERT INTO t2 (a, b, c) VALUES (200, 5, 'b');
INSERT INTO t2 (a, b, c) VALUES (320, 5, 'm');
INSERT INTO t2 (a, b, c) VALUES (220, 6, 'i');
INSERT INTO t2 (a, b, c) VALUES (8, 7, 'a');
INSERT INTO t2 (a, b, c) VALUES (12, 7, 'd');
INSERT INTO t2 (a, b, c) VALUES (300, 8, 'a');
INSERT INTO t2 (a, b, c) VALUES (3, 9, 'd');
INSERT INTO t2 (a, b, c) VALUES (100, 9, 'x');

?

2 索引優(yōu)化原則

下面根據(jù)我們構(gòu)造的表和數(shù)據(jù)，以形成一個(gè)更加完整的MySQL索引優(yōu)化指南：

1.監(jiān)控查詢性能：

原則：使用 EXPLAIN 語句分析查詢的執(zhí)行計(jì)劃，看看哪些查詢?cè)谑褂盟饕?，哪些沒有，然后進(jìn)行相應(yīng)的優(yōu)化。

例：如果我們要查詢b為5的所有記錄，我們可以執(zhí)行 EXPLAIN SELECT * FROM t2 WHERE b = 5; 來查看是否使用了索引。

2.避免全表掃描：

原則：如果頻繁進(jìn)行全表掃描，那么需要考慮加入或優(yōu)化索引，使查詢盡量利用索引。

例：查詢SELECT * FROM t2 WHERE c = 'a'; 可能會(huì)導(dǎo)致全表掃描，因?yàn)榱衏沒有索引。為了避免這種情況，可以考慮為c列添加索引。

3.考慮列的區(qū)分度：

原則：區(qū)分度高的列（即列中唯一值的數(shù)量與表中行數(shù)的比例）更適合建立索引。count(distinct 字段)/count(字段) 越接近1，區(qū)分度越高。

例：在表t2中，b列有多個(gè)不同的值，因此它的區(qū)分度較高，適合建立索引。

4.避免在索引列上使用函數(shù)或運(yùn)算：

原則：避免導(dǎo)致索引失效的函數(shù)或運(yùn)算。

例：查詢SELECT * FROM t2 WHERE LENGTH(c) = 1; 不會(huì)使用c的任何索引（如果存在），因?yàn)樗鼘?duì)c列使用了函數(shù)。

5.使用復(fù)合索引：

原則：考慮使用包含多個(gè)列的索引，但要遵循最左前綴原則。

例：索引idx_bc是一個(gè)復(fù)合索引，涵蓋了b和c兩列。如果你查詢 SELECT * FROM t2 WHERE b = 5 AND c = 'm';，那么此查詢會(huì)利用復(fù)合索引。

6.限制索引的數(shù)量：

原則：雖然索引可以加速查詢，但它們會(huì)增加寫操作的開銷。

例：表t2已經(jīng)有3個(gè)索引（包括主鍵），如果我們?cè)贋槊總€(gè)列添加更多索引，那么雖然讀操作可能會(huì)更快，但寫操作將受到影響。

7定期分析和優(yōu)化表：

原則：為了更有效地使用索引，定期對(duì)表進(jìn)行分析和優(yōu)化。

例：如果表t2進(jìn)行了大量的數(shù)據(jù)修改，可以定期執(zhí)行ANALYZE TABLE t2;和OPTIMIZE TABLE t2;以提高性能。

8.考慮索引長度：

原則：根據(jù)實(shí)際需要，可能只需要為字符串的部分字符創(chuàng)建索引。

例：盡管表t2的c列數(shù)據(jù)相對(duì)較短，但如果有一個(gè)較長的VARCHAR列，我們可以考慮只對(duì)它的前N個(gè)字符進(jìn)行索引。

9.刪除冗余和重復(fù)索引：

原則：避免不必要的存儲(chǔ)和寫入開銷。

例：idx_b和idx_bc都包括列b。在某些情況下，如果我們發(fā)現(xiàn)僅僅基于b的查詢不多，可以考慮刪除idx_b。

10.考慮使用覆蓋索引：

原則：查詢?nèi)绻梢灾煌ㄟ^索引返回結(jié)果，那么性能會(huì)更好。

例：如果你執(zhí)行查詢 SELECT b, c FROM t2 WHERE b = 5;，那么由于idx_bc可以覆蓋查詢，因此不需要訪問實(shí)際的表行。

11.避免使用隱式類型、編碼轉(zhuǎn)換：

原則：避免在查詢時(shí)發(fā)生隱式類型轉(zhuǎn)換或字符編碼轉(zhuǎn)換，這些都可能導(dǎo)致索引失效。

例：盡管在你的示例中沒有出現(xiàn)這種情況，但如果我們執(zhí)行查詢 SELECT * FROM t2 WHERE b = '5';，這會(huì)導(dǎo)致隱式類型轉(zhuǎn)換，可能影響索引的使用。

?