MySQL索引与查询语句分析调优

索引（Index）是帮助 MySQL 高效获取数据的数据结构。除数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。在实际业务中合理设置索引可以提高语句查询的速度，大数据量未设置索引、不合理的语句、不当的数据库参数会导致慢 SQL 查询，从而导致接口响应变慢。

InnoDB 存储引擎支持两种常见的索引数据结构：B+ 树索引和 HASH 索引。HASH 索引无序存储、对于等值查询友好，B+ 索引有序存储、支持范围查询和排序操作，有序存储这一特性对磁盘访问友好。其中 B+ 树索引因为其各种特性注定了其是目前关系型数据库中应用最广泛、最有效的索引。 B+ 树索引又分为聚簇索引和非聚簇索引。

聚簇索引是按照每张表的主键构造一个 B+ 树，B+ 树的叶子节点中记录着表中一行记录的所有值。只要找到这个叶子节点也就得到了这条记录的所有值。因此根据聚簇索引进行查询的速度非常快。

非聚簇索引是根据非主键字段创建的索引，索引的叶子节点中不包含行记录的所有值，叶子节点只包含索引字段的值、主键的值以及指向数据页数据行的逻辑指针。因此根据非聚簇索引进行查询需要先查询到主键，再根据主键回表查询所需要的具体数据。

MySQL 创建索引的语法如下，其中 idx_columns 为索引名称。table_name 为数据表名。(column1(n), column2) 括号内为需要创建索引的列名，多个列名一起则为联合索引。(n) 限制索引长度，指定了列前缀 n 位内容作为索引舍弃后续内容。

1
CREATE INDEX idx_columns ON table_name (column1(n), column2);

需要注意在 MySQL 5.6 版本之前，索引构建期间会对表进行排它锁定，其他会话不能读取或修改表中的任何数据，这将导致长时间阻塞。即使在 MySQL 5.6 之后引入了 Online DDL 的支持，允许在不阻塞其他会话的情况下创建或删除索引，在索引构建期间仍然可能出现锁定和阻塞。例如，在添加索引时，如果表中有许多未提交的事务，则需要等待这些事务提交后才能开始索引构建。

索引并非银弹也具有一定的副作用，对于索引的创建、修改应该谨慎操作。以下是创建索引时需要考虑的一些建议：

  1. 根据查询频率考虑：创建索引之前，需要分析查询频次和效率，非频繁查询的列、数据量较小（大量NULL值）的列，无需创建索引。
  2. 选择合适的索引类型：MySQL 提供了多种索引类型，B+ 树索引（适合范围查询、排序）、哈希索引（适合等值查询）、全文索引，不同的索引类型适合不同的查询操作。
  3. 根据内容区分度考虑：区分度不高的字段作为索引无法有效地过滤数据，大多数情况下无需创建索引。但如果数据内容具有倾斜性，查询条件又偏向于内容较少的数据，则可以为该列创建索引。例如：表中具有 init_status 字段，该字段值由 0(未初始化)/1(已初始化) 构成，当等值查询 init_status = 0 的数据量较少频率较高时，为该列建立索引可优化查询时间。
  4. 考虑联合索引及覆盖索引：根据索引的性质，非聚簇索引包含索引列以及主键的值，如果创建的联合索引包含查询所需的全部列（称为覆盖索引），则可以减少回表次数提高查询效率。
  5. 避免创建过多的索引；创建索引需要消耗额外的磁盘存储空间，更新索引行记录时也需要同步更新索引导致更新记录需要花费更多时间。
  6. 选择合适的索引长度：索引的内容长度越长，索引的效率就越低。对于长文本列可以选择前缀索引限制文本长度，但也不可设置过短，索引匹配到大量内容时，对数据进行加锁或更新操作会带来大量的锁冲突。

索引最左匹配原则

在 MySQL 索引中，查询要使用索引则需要利用索引最左边的一部分进行匹配（字段、值）。假设在表中创建了联合索引 (col1, col2, col3)，如果查询条件中带有 col1, col2, col3 则可以利用这个联合索引进行最左前缀匹配，其中 col2 / col3 是可选的，但 col1 则必须出现，如果只有 col2 / col3 则没有遵循最左匹配原则，会扫描整个索引树。最左匹配原则与查询条件的顺序没有关系，例如 SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2;，WHERE 条件的 col1 和 col2 的顺序调换对结果没有影响。

另外，即使索引是单字段索引不是联合索引，字段值的匹配也需要遵循最左匹配原则，例如有这样一条记录 col1 = 'abc'，如果使用 LIKE 模糊查询 col1 LIKE "ab%" 此时的查询值可以与索引前缀匹配，遵循最左匹配原则，并不会导致索引失效，但 col1 LIKE "%bc" 与 col1 LIKE "b%" 均不遵循最左匹配原则，这也是为什么 LIKE 模糊查询会导致索引失效的原因。

慢查询分析

一条 SQL 语句执行慢的原因有很多，以下列出了一些可能导致慢 SQL 查询的原因：

  • 索引失效
  • 多表join
  • 查询字段太多
  • 表中数据量太大
  • 索引区分度不高
  • 数据库表结构设计不合理
  • 数据库连接数不够
  • 数据库参数不合理
  • 执行事务时间较长
  • 锁竞争导致等待
  • 服务器IO或CPU异常占用

排查分析慢查询首先需要找到慢查询 SQL 语句，定位慢 SQL 语句的方法有很多，例如 MySQL 自带的慢查询日志、Java 应用层数据源连接池使用的 Druid 等等。定位到具体 SQL 语句后，就可以围绕着表或语句展开具体分析。开启 MySQL 自带的慢查询日志需要数据库修改配置文件后重启数据库：

1
2
3
4
5
6
# 开启slow query log，0或OFF为关闭，1或ON为开启，默认为关闭
slow_query_log = 1
# 配置 slow query log 存储路径
slow_query_log_file = /tmp/slow_query.log
# 配置 slow query log 时长阈值，单位为秒，默认为10秒，最大支持31536000秒
long_query_time = 30

找到慢查询 SQL 语句后就可以借助 MySQL 提供的 explain 执行计划分析，通过 explain 原语句 提供的信息分析 MySQL 是如何执行语句的。需要注意的是，explain 分析不支持存储过程，需要手动将语句抽取出来逐条分析。同时，explain 也有可能误导，例如对 LIMIT 1 的语句显示全索引扫描。执行 explain 语句后，会返回以下列信息(其中重点关注的有 type、possible_keys、key 和 Extra 这几个列)：

  • id：执行计划中每个操作的唯一标识符。查询语句的每个操作都有一个唯一的id。多表join时一次explain中的多条记录的id是相同的，执行顺序由上至下。id不同时，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
  • select_type：操作的类型。常见的类型包括SIMPLE简单查询、PRIMARY包含复杂的子部分的查询，、SUBQUERY子查询、UNION等。不同类型的操作会影响查询的执行效率。
  • table：当前操作所涉及的表。
  • partitions：当前操作所涉及的分区。
  • type：表示查询时所使用的索引类型，包括ALL、index、range、ref、eq_ref、const、system等。
  • possible_keys：表示可能被查询优化器选择使用的索引，但实际查询不一定使用。
  • key：表示查询经过优化器选择后查询实际使用的索引，如果为 NULL 则没有使用索引。
  • key_len：表示索引的长度。索引的长度越短，查询时的效率越高。
  • ref：用来表示哪些列或常量被用来与key列中命名的索引进行比较。
  • rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。
  • filtered：表示此操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。
  • Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary等。

type 表示查询时所使用的索引类型。

  1. system：表示系统表（或表只有一行记录），表中只有少量数据。
  2. const：表示使用了常量索引，只需要一次索引即可找到数据（如主键、唯一索引）。
  3. eq_ref：表示只使用唯一索引扫描，例如 SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table1.uk = 'union_value';。
  4. ref：表示使用了非唯一索引扫描，只会扫描索引树中一部分等值查找匹配的数据。例如 SELECT * FROM table1 WHERE idx_col = 'value';。
  5. range：表示使用了索引进行范围扫描，只会扫描索引树中范围查找匹配的数据。例如 SELECT * FROM table1 WHERE idx_col > 1 and idx_col < 10;。
  6. index：表示对索引树进行全索引扫描，会遍历整个索引树查找匹配的数据。例如创建联合索引 (a, b, c)，但查询不符合最左匹配原则，此时会扫描整个索引树。SELECT c FROM table1 WHERE b = 'value';。
  7. ALL：表示不使用索引，直接回表进行全表扫描。index 扫描的是索引树，而 ALL 扫描的是原始数据文件，索引文件通常比数据文件小，速度会稍快一些。例如查询条件中没有任何索引字段。

Extra 表示其他额外的信息。

  1. Using where：表示 MySQL 在检索行之后再根据条件过滤，通常发生在查询列未被索引覆盖或查询未遵循最左匹配原则。
  2. Using index：表示 MySQL 使用了索引优化，索引覆盖了查询列，无需再回表查询。
  3. Using index condition：表示 MySQL 使用索引过滤了部分数据，通常和索引下推有关。
  4. Using join buffer：表示 MySQL 使用了连接缓存，通常发生在 join 连表时。
  5. Using temporary：表示 MySQL 使用了临时表存储查询结果后再返回，通常发生在排序或分组操作之后。
  6. Using filesort：表示 MySQL 将使用文件排序而不是索引排序，这通常发生在无法使用索引来进行排序或优化器认为索引排序效率不高时。在进行排序时，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，它的大小是由 sort_buffer_size 参数控制的，如果要排序的数据量大于此参数，则使用磁盘临时文件辅助排序，否则使用内存。

索引失效的原因

  1. 索引列参与计算
    例如 table1 中有 age(int) 字段并添加了索引，正常查询语句为 SELECT * FROM table1 WHERE age = 23; 或 WHERE age = 22 + 1; 会使用索引，但 WHERE age + 1 = 23; 无法使用索引。

  2. 对索引列进行函数操作
    假设 table1 中有 create_time(datetime) 字段且添加了索引，有查询语句如下：

1
SELECT * FROM table1 WHERE create_time = '2024-02-06 00:00:00';

以上为一条普通查询语句，此时该查询语句可以使用索引。一般情况下，对索引列使用函数之后由于索引列需要参与计算处理，此时索引失效。例如：

1
SELECT * FROM table1 WHERE YEAR(create_time) = 2024;

对索引列使用函数之后就一定无法使用索引了吗？答案是否定的。在 MySQL 8.0 中新增了函数索引(Functional Index)的类型用于优化索引列无法使用函数的问题，这种索引允许在创建索引时包含确定值的函数处理后的列，非确定值函数(例如 now()、uuid()、rand()等)、存储函数、全文检索函数(match()等)无法使用索引函数。以下对函数索引的创建进行举例说明：

假设 table1 中有 first_name(varchar)、last_name(varchar) 两个字段，查询时需要使用 CONCAT 将两个列的值合并处理，查询语句如下：

1
SELECT * FROM table1 WHERE CONCAT(first_name, ' ', last_name) = 'a aofall';

此时就可以使用函数索引优化：CREATE INDEX idx_full_name ON table1 ((CONCAT(first_name, ' ', last_name)));

  1. 使用OR
    查询列已被索引覆盖，但查询条件中包含 OR 条件，且 OR 的左右两边存在 < > 这种无法确定查询范围的情况会导致索引失效。

  2. LIKE查询
    查询列已被索引覆盖，但查询条件包含 LIKE %前缀，由于不遵循最左匹配原则，这种情况同样会导致索引失效。

  3. 隐式类型转换
    假设 table1 中有 age(int)、name(varchar) 字段并添加了索引，根据 age 进行条件查询时 WHERE name = 1，由于 name 字段是 varchar 字符类型，但查询条件值却使用了 int 数字类型，此时隐式转换(varchar -> int)会导致索引失效。反之 WHERE age = '1' (int -> varchar) 不会导致索引失效。

  4. 不等于比较
    查询列已被索引覆盖，但查询的表中数据量较大，且数据条件不偏向某个较小的数据，MySQL 优化器可能会选择索引树扫描或全表扫描。

  5. 判断非空(IS NOT NULL)
    查询列已被索引覆盖，但使用了 IS NOT NULL 判断非空，MySQL优化器会直接选择进行全表扫描。

  6. 排序(ORDER BY)
    查询列已被索引覆盖，当进行 ORDER BY 排序时，如果数据量较小，MySQL 优化器可能会不使用索引，而是直接在内存中进行排序。

  7. IN查询
    查询列已被索引覆盖，当 IN 查询中值较少的情况下可能会走索引优化，值较多的情况下可能会不使用索引，这取决于 MySQL 优化器基于各种开销的自动选择。

索引下推

从 MySQL 5.6 开始引入了一种名为索引下推的优化技术，可以根据查询已存在索引且未失效的字段过滤记录，再根据过滤后的结果进行判断，这个优化过程叫做索引下推。索引下推在索引失效的情况下，在条件允许的情况下可以减少回表扫描的次数。当一条SQL使用到索引下推时，explain的执行计划中的extra字段中内容为：Using index condition。下面举例解释什么是索引下推：

假设一张表中有 id, zipcode, first_name, last_name 四个字段，为 (zipcode, first_name, last_name) 三个字段创建了联合索引。此时有一条查询语句如下：

SELECT * FROM table_name WHERE zipcode = '350001' AND first_name LIKE '%a%' AND last_name LIKE '%aofall%';

由于查询字段左侧 LIKE 查询（或其他原因）导致索引失效了，但 MySQL 仍然会根据可使用索引的字段 zipcode = '350001' ，从索引过滤不符合条件的大多数行记录，再根据过滤后的记录进行具体查询判断返回符合条件的数据。