MySQL 笔记 - 索引优化

MySQL 索引优化笔记

一、使用 Explain 分析 SQL 语句性能

在 MySQL 中，可以使用 EXPLAIN 关键字来查看 SQL 语句的性能。其中，type 字段的部分含义如下：

• all：全表扫描，说明这个 SQL 语句没有使用到索引，可能是因为表本身没有创建索引，或者 SQL 语句的写法导致没有使用到索引。
• range：使用有范围的索引扫描，性能优于全表扫描和仅使用索引扫描。
• index：使用了索引，但如果 extra 列中的值为 Using index，则表示索引覆盖，即查询的数据在索引中已经存在，无需再回表取数据。
• ref：条件列使用了索引，但不是主键或唯一索引，索引值不唯一，有重复的情况。
• eq_ref：相对于 ref 来说，使用的是唯一索引，对于每个索引键值，只有唯一的一条匹配记录。
• const/system：单表中最多只有一条匹配行，查询非常迅速，匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理。
• index_merge：使用了 MySQL 的索引合并优化方法，需要检查所创建的索引是否为多个单列索引。

二、有效优化索引使用

1. 索引作为独立列出现：索引不能作为表达式的一部分，也不能作为函数的参数出现，否则索引会失效。因为 MySQL 无法自动解析表达式和参数，所以无法使用索引。

2. 索引失效的其他情况：

   条件中使用 IS NULL 或 IS NOT NULL 会导致索引失效，因为索引中不会存储 NULL 值。

   使用 %LIKE 进行模糊查询时，如果以 % 开头会导致全表查询。

   使用多列索引时，如果索引顺序不是建立索引的顺序，或者跳过第一个索引直接使用后面的索引，也会导致索引失效。

   条件中包含 OR 时，只有所有列都是单独索引时才会使用索引。

3. 创建有效的索引

   前缀索引：如果需要索引很长的字符串，可以考虑前缀索引。前缀索引即选择所需字符串的一部分前缀作为索引。需要计算该列完整列的选择性，使得前缀选择性接近于完整列的选择性。

   多列索引：尽量不要为多列上创建单列索引，因为这样的情况下最多只能使用一星索引。不如去创建一个全覆盖索引，即索引中包含了查询中的所有字段，这样可以减少回表查询。

   选择合适的索引列顺序：通常将选择性最高的列放到索引最前列，但也要考虑到 GROUP BY、ORDER BY 等情况。

4. 覆盖索引：索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了。MySQL 中只能使用 B-Tree 索引做覆盖索引。

三、其他优化建议

1. 减少 SELECT * 操作：尽量明确指定需要查询的字段，避免使用 SELECT *。

2. 使用索引扫描来做排序：MySQL 生成有序的结果有两种方法：通过排序操作，或者按照索引顺序扫描。使用排序操作需要占用大量的 CPU 和内存资源，而使用索引性能是很好的。所以，当查询有序结果时，尽量使用索引顺序扫描来生成有序结果集。

3. 压缩索引：MyISAM 中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引。但这个优化在一定情况下性能比较好，对于某些情况可能会导致更慢。

4. 减少重复、冗余以及未使用的索引：避免在同一列上增加主键、唯一限制再创建索引，这样是重复索引。尽量减少新增索引，而应该扩展已有的索引。可以考虑删除没有使用到的索引。

5. 索引和锁：使用索引可以让查询锁定更少的行，提升查询性能。InnoDB 支持行锁和表锁，默认使用行锁；而 MyISAM 使用的是表锁。

6. 减少索引和数据碎片：大量删除数据会导致留空的空间比实际的存储空间还要大，这时候如果进行新的插入操作，MySQL 会尝试重新使用这部分空间，但无法彻底占用，从而产生碎片。可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据。

四、总结

数据库的索引优化需要结合实际情况进行，了解数据库索引本身的一些原理对于优化会有很大帮助。巧妙地使用 EXPLAIN 分析自己所写的 SQL 语句，可以更好地进行优化。