MySQL索引的优化方式

索引是MySQL数据库中的一个重要概念，使用合理的话，可以显著提升数据库执行效率，这篇文章就介绍在创建与使用索引时的一些常见优化方式。

索引是什么？

索引是一种数据结构，通常为树或哈希表，用于数据库表中数据的快速查找。在数据库中，索引类似于书籍的目录，允许数据库引擎高效地定位到特定行的数据，而无需扫描整个表。
索引的使用就是为了提高数据查询的效率。

创建索引时要遵循的原则

使用唯一性索引

对于唯一性约束的列，使用唯一索引可以确保数据的唯一性，并提高查询性能。

CREATE UNIQUE INDEX idx_member_cdoe_unique ON t_member ( member_code );

为经常作为查询条件的字段建立索引

CREATE INDEX idx_member_name ON t_member ( member_name );

为经常需要排序、分组和联合操作的字段建立索引

对于表中常用于 JOIN、ORDER BY、GROUP BY 的列，为其创建索引。

使用复合索引

当查询条件涉及多个列时，使用复合索引可以提高查询效率。

CREATE INDEX idx_memeber_name_email ON t_member ( member_name, member_email );

限制索引的数目

索引数目不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间越大，更新表时越复杂，建议单表索引控制在 5个以内。

尽量使用数据量少的索引

如果索引列的值很长，数据量很大，索引查询的速度会受影响。

尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。较长的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
示例：

CREATE INDEX idx_member_name_prefix ON t_member (member_name ( 10 ));

数据量小的表最好不要使用索引

由于数据量较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

选择区分度高的列作为索引

区分度公式：

COUNT(DISTINCT 列名)/COUNT(*)

例如表里面有 100 条数据，表索引列有 96 个不同的值，这个列索引的区分度就是 96/100=0.96 。
索引的区分度越接近1，这个索引的效率很高，唯一键的区分度是1，效率就很高。

使用索引的注意事项与维护

避免冗余索引

创建重复或者冗余的索引会增加数据库写操作的开销。
如果已经有一个复合索引 (member_name, member_email)，再单独为 member_name 创建索引就是冗余的。

避免在索引列上使用函数或表达式

索引列不能参与计算，保持“列干净”。对索引列应用函数或表达式时，数据库系统可能无法直接使用该索引来快速定位数据，从而导致全表扫描。

避免过度索引

索引虽然可以加快查询速度，也会增加写入操作（insert、update、delete）的开销，如果一个表有过多的索引，在每次插入新纪录的时候，MySQL需要更新全部的索引，会影响性能。

索引维护

定期检查，删除不再使用或很少使用的索引。

使用 EXPLAIN 分析查询

使用 EXPLAIN 命令分析查询执行计划，确保查询使用了索引。

使用索引时的常见问题与优化方式

有时搜索往往会出现数据库没有按照预期使用索引来加速查询，查询效率慢的问题，很大的概率就是 SQL语句执行索引失效导致转向全表扫描。常见导致索引失效的情况与优化方式如下：

不符合最左前缀匹配原则

最左前缀匹配原则：在使用复合索引时，该原则要求查询条件必须从索引的最左边开始匹配，并且不能跳过中间的列。
如果有一个表，对id列、name列、age列建立了一个联合索引 idx_id_name_age，实际上相当于建立了三个索引(id) (id,name) (id,name,age)
如果搜索为： SELECT * FROM user WHERE id=1; 使用 id 作为第一个查询条件。该搜索是遵循最左匹配原则的,在搜索过程中使用到了联合索引。
如果搜索为： SELECT * FROM user WHERE name = 'abc'; 或者 SELECT * FROM user WHERE age = 20; 使用 name 或者 age 作为第一个查询条件。那么没有遵守最左匹配原则，就不会使用到该复合索引了。

减少 SELECT * 的使用

具体需要展示那些列，就查询哪些列。

LIKE模糊查询不能使用索引

使用 LIKE 时注意通配符位置，不要以 % 开头的。

SELECT member_name FROM t_member WHERE member_name LIKE 'abc%';  可以使用索引。
SELECT member_name FROM t_member WHERE member_name LIKE '%abc';  索引失效，使用全表扫描，可以使用函数索引，反向索引。
SELECT member_name FROM t_member WHERE member_name LIKE '%abc%'; 索引失效，无法使用反向索引

范围查询右边的列不能使用索引

MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
不能使用索引中范围条件右边的列（范围列可以用到索引），范围列之后列的索引全失效，范围条件有：<、<=、>、>=、between等。

在WHERE子句中对字段进行函数操作

不要在索引列上面做任何操作（计算、函数），否则会导致索引失效而转向全表扫描。
例如：

SELECT id FROM t_member WHERE substring(member_name,1,3) = 'abc';

WHERE 子句中使用 OR 连接条件

必须保证 OR 前后的字段都有索引，索引才会生效，否则会导致索引失效而转向全表扫描。

SELECT id FROM t_member WHERE num =10 OR member_name = 'abc';

可以使用 UNION ALL 利用索引。

SELECT id FROM t_member WHERE num = 10
UNION ALL
SELECT id FROM t_member WHERE member_name = 'abc';

UNION、OR、IN 都能够命中索引，建议使用 IN

使用 UNION、OR 和 IN 都能够在一定条件下命中索引，但是从使用上说，推荐 IN 。

对于连续的数值，能用 BETWEEN 就不要用 IN

在处理连续的数值范围时， BETWEEN 可以更高效的利用索引。

负向条件查询不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等。
可以使用正向条件查询，如：将 SELECT * FROM table WHERE column != 1; 转换为 SELECT * FROM table WHERE column IN (2,3);。
使用复合索引，在一些情况下，创建复合索引（多列索引）可以帮助优化查询。

IS NULL 和 IS NOT NULL 不能使用索引

尽量避免在 WHERE子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描。实际上，IS NULL 和 IS NOT NULL 条件在某些情况下是可以使用索引的，可以使用 EXPLAIN命令查看执行计划，看看能否支持使用索引。

大量数据分页效率低问题

在具有大量数据的场景下，使用传统的LIMIT offset, N分页查询可能会导致性能问题。MySQL不会跳过前 offset 行，而是获取 offset + N 行数据，然后抛弃前 offset行，返回 N 行。这种方法在 offset特别大的时候效率非常低下。例如：

SELECT * FROM t_member LIMIT 10000,10;

该 SQL 表示查询从第 10001  开始的 10 行数据，但是实际上会先读取 10010 条记录，抛弃前 10000 条记录，然后读后面 10 条想要的数据。
可以使用延迟关联（Deferred Join）或者子查询优化超多分页场景。

子查询优化

可以先通过子查询获取需要的数据ID，再根据这个ID进行范围搜索。

SELECT a.* 
FROM t_member a 
WHERE a.id <= ( SELECT id FROM t_member ORDER BY id DESC LIMIT 10000, 1)
ORDER BY a.id DESC 
LIMIT 10;

注意：这种范围搜索要注意排序问题。

延迟关联优化

延迟关联的优化思路跟子查询的类似，先把条件转移到主键索引，减少回表。不同点就是使用关联查询代替子查询。

SELECT
  a.* 
FROM
  t_member a
  INNER JOIN ( SELECT id FROM t_member ORDER BY a.id LIMIT 10000, 10 ) AS b ON a.id = b.id;

不必要的数据转换

查询时注意字段类型与数据库表中的字段类型相匹配，避免隐式的数据类型转换。例如，字符串类型不加单引号会导致索引失效。

复杂的嵌套查询

嵌套查询（子查询）虽然功能强大，但在某些情况下会导致性能瓶颈。可以使用 JOIN 代替子查询，提升查询效率。