MySQL查询性能慢，该不该建索引？

日常工作中，有些同学一遇到查询性能问题，就盲目要求 DBA 给表字段创建索引。这种做法对不对呢？今天，我们就来具体看看这背后的细节。

本文的例子均在 MySQL 5.7.26 中执行。

聚簇索引和二级索引

说到索引，页目录就是最简单的索引。但当数据页有无数个时，就需要考虑建立索引，才能定位到记录所在的页。

为了解决这个问题，InnoDB 引入了 B+ 树。

聚簇索引

上图叶子节点下面方块中的省略号是实际数据，这样的 B+ 树就是聚簇索引。由于数据在物理上只会保存一份，所以聚簇索引只能有一个。InnoDB 会自动使用主键作为聚簇索引的索引键，如果没有主键，就选择第一个不包含 NULL 值的唯一列。

为了实现非主键字段的快速搜索，就有了二级索引，如下图所示：

二级索引

这次二级索引的叶子节点中保存的不是实际数据，而是主键，获得主键值后去聚簇索引中获得数据行。

创建二级索引的代价，主要表现在维护代价、空间代价和回表代价三个方面。

二级索引的维护代价

创建 N 个二级索引，就需要再创建 N 棵 B+ 树，新增数据时不仅要修改聚簇索引，还需要修改这 N 个二级索引。

我们通过实验测试一下创建索引的代价。

create TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `score` int(11) NOT NULL,
  `create_time` timestamp NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

通过下面的存储过程循环创建 10 万条测试数据：

create DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
    declare c_id integer default 1;
    while c_id<=100000 do
    insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
    -- 需要注意，因为使用的是now()，所以对于后续的例子，使用文中的SQL你需要自己调整条件，否则可能看不到文中的效果
    set c_id=c_id+1;
    end while;
end

执行耗时是 140 秒。如果再创建两个索引：

KEY `name_score` (`name`,`score`) USING BTREE,
KEY `create_time` (`create_time`) USING BTREE

那么创建 10 万条记录的耗时提高到 154 秒。

这里其实还有一个代价。页中的记录都是按照索引值从小到大的顺序存放的，新增记录就需要往页中插入数据，现有的页满了就需要新创建一个页，把现有页的部分数据移过去，这就是页分裂；如果删除了许多数据使得页比较空闲，还需要进行页合并。

页分裂和合并，都会有 IO 代价，并且可能在操作过程中产生死锁。

二级索引的空间代价

虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间。比如，person 表创建了两个索引后，使用下面的 SQL 查看数据和索引占用的磁盘：

select DATA_LENGTH, INDEX_LENGTH from information_schema.TABLES where TABLE_NAME='person';

结果显示，数据本身只占用了 4.7M，而索引占用了 8.4M。

二级索引的回表代价

使用 SELECT * 按照 name 字段查询用户，使用 EXPLAIN 查看执行计划：

explain select * from person where NAME='name1';

执行计划

可以发现，key 字段代表实际走的是哪个索引，其值是 name_score，说明走的是 name_score 这个索引。

type 字段代表了访问表的方式，其值 ref 说明是二级索引等值匹配，符合我们的查询。

把 SQL 中的 * 修改为 NAME 和 SCORE，也就是 SELECT name_score 联合索引包含的两列，查看执行计划：

explain select NAME,SCORE from person where NAME='name1';

执行计划

可以看到，Extra 列多了一行 Using index 的提示，证明这次查询直接查的是二级索引，免去了回表。

创建索引最佳实践

了解了上面的三条代价，现在我们知道，索引并不是解决查询慢的万能钥匙。这里我总结了三条创建索引的最佳实践供你参考。

第一，无需一开始就建立索引。

可以等到业务场景明确后，或者是数据量超过 1 万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用 EXPLAIN 命令，确认查询是否可以使用索引。

第二，尽量索引轻量级的字段。

比如能索引 int 字段就不要索引 varchar 字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本的搜索，可以考虑使用 Elasticsearch 等专门用于文本搜索的索引数据库。

第三，尽量不要在 SQL 语句中 SELECT *。

应该 SELECT 必要的字段，甚至可以考虑使用联合索引来包含我们要搜索的字段（即索引覆盖），既能实现索引加速，又可以避免回表的开销。

小结

本文我们分析了创建二级索引的三个代价，即维护代价、空间代价、回表代价。索引不是解决查询性能问题的万能钥匙。

整理自极客时间《Java开发常见错误》学习笔记。如对你有帮助，请转发支持，非常感谢！ 一起学习请关注公众号：【杨同学technotes】领取 MySQL 精品技术书籍。