MySQL 常见索引优化原则

目录

1 构造数据

2 索引优化原则

1 构造数据

-- 构造表
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;


-- 构造数据
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 索引优化原则

下面根据我们构造的表和数据，以形成一个更加完整的MySQL索引优化指南：

1.监控查询性能：

原则：使用 EXPLAIN 语句分析查询的执行计划，看看哪些查询在使用索引，哪些没有，然后进行相应的优化。

例：如果我们要查询b为5的所有记录，我们可以执行 EXPLAIN SELECT * FROM t2 WHERE b = 5; 来查看是否使用了索引。

2.避免全表扫描：

原则：如果频繁进行全表扫描，那么需要考虑加入或优化索引，使查询尽量利用索引。

例：查询SELECT * FROM t2 WHERE c = 'a'; 可能会导致全表扫描，因为列c没有索引。为了避免这种情况，可以考虑为c列添加索引。

3.考虑列的区分度：

原则：区分度高的列（即列中唯一值的数量与表中行数的比例）更适合建立索引。count(distinct 字段)/count(字段) 越接近1，区分度越高。

例：在表t2中，b列有多个不同的值，因此它的区分度较高，适合建立索引。

4.避免在索引列上使用函数或运算：

原则：避免导致索引失效的函数或运算。

例：查询SELECT * FROM t2 WHERE LENGTH(c) = 1; 不会使用c的任何索引（如果存在），因为它对c列使用了函数。

5.使用复合索引：

原则：考虑使用包含多个列的索引，但要遵循最左前缀原则。

例：索引idx_bc是一个复合索引，涵盖了b和c两列。如果你查询 SELECT * FROM t2 WHERE b = 5 AND c = 'm';，那么此查询会利用复合索引。

6.限制索引的数量：

原则：虽然索引可以加速查询，但它们会增加写操作的开销。

例：表t2已经有3个索引（包括主键），如果我们再为每个列添加更多索引，那么虽然读操作可能会更快，但写操作将受到影响。

7定期分析和优化表：

原则：为了更有效地使用索引，定期对表进行分析和优化。

例：如果表t2进行了大量的数据修改，可以定期执行ANALYZE TABLE t2;和OPTIMIZE TABLE t2;以提高性能。

8.考虑索引长度：

原则：根据实际需要，可能只需要为字符串的部分字符创建索引。

例：尽管表t2的c列数据相对较短，但如果有一个较长的VARCHAR列，我们可以考虑只对它的前N个字符进行索引。

9.删除冗余和重复索引：

原则：避免不必要的存储和写入开销。

例：idx_b和idx_bc都包括列b。在某些情况下，如果我们发现仅仅基于b的查询不多，可以考虑删除idx_b。

10.考虑使用覆盖索引：

原则：查询如果可以只通过索引返回结果，那么性能会更好。

例：如果你执行查询 SELECT b, c FROM t2 WHERE b = 5;，那么由于idx_bc可以覆盖查询，因此不需要访问实际的表行。

11.避免使用隐式类型、编码转换：

原则：避免在查询时发生隐式类型转换或字符编码转换，这些都可能导致索引失效。

例：尽管在你的示例中没有出现这种情况，但如果我们执行查询 SELECT * FROM t2 WHERE b = '5';，这会导致隐式类型转换，可能影响索引的使用。