日常工作中，有些同学一遇到查询性能问题，就盲目要求 DBA 给表字段创建索引。这种做法对不对呢？今天，我们就来具体看看这背后的细节。
本文的例子均在 MySQL 5.7.26 中执行。
聚簇索引和二级索引
说到索引，页目录就是最简单的索引。但当数据页有无数个时，就需要考虑建立索引，才能定位到记录所在的页。
为了解决这个问题，InnoDB 引入了 B 树。
聚簇索引
上图叶子节点下面方块中的省略号是实际数据，这样的 B 树就是聚簇索引。由于数据在物理上只会保存一份，所以聚簇索引只能有一个。InnoDB 会自动使用主键作为聚簇索引的索引键，如果没有主键，就选择第一个不包含 NULL 值的唯一列。
为了实现非主键字段的快速搜索，就有了二级索引，如下图所示：
二级索引
这次二级索引的叶子节点中保存的不是实际数据，而是主键，获得主键值后去聚簇索引中获得数据行。
创建二级索引的代价，主要表现在维护代价、空间代价和回表代价三个方面。
二级索引的维护代价
创建 N 个二级索引，就需要再创建 N 棵 B 树，新增数据时不仅要修改聚簇索引，还需要修改这 N 个二级索引。
我们通过实验测试一下创建索引的代价。
代码语言：javascript复制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 万条测试数据：
代码语言：javascript复制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 秒。如果再创建两个索引：
代码语言：javascript复制KEY `name_score` (`name`,`score`) USING BTREE,
KEY `create_time` (`create_time`) USING BTREE那么创建 10 万条记录的耗时提高到 154 秒。
这里其实还有一个代价。页中的记录都是按照索引值从小到大的顺序存放的，新增记录就需要往页中插入数据，现有的页满了就需要新创建一个页，把现有页的部分数据移过去，这就是页分裂；如果删除了许多数据使得页比较空闲，还需要进行页合并。
页分裂和合并，都会有 IO 代价，并且可能在操作过程中产生死锁。
二级索引的空间代价
虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间。比如，person 表创建了两个索引后，使用下面的 SQL 查看数据和索引占用的磁盘：
代码语言：javascript复制select DATA_LENGTH, INDEX_LENGTH from information_schema.TABLES where TABLE_NAME='person';结果显示，数据本身只占用了 4.7M，而索引占用了 8.4M。
二级索引的回表代价
使用 SELECT * 按照 name 字段查询用户，使用 EXPLAIN 查看执行计划：
代码语言：javascript复制explain select * from person where NAME='name1';
执行计划
可以发现，key 字段代表实际走的是哪个索引，其值是 name_score，说明走的是 name_score 这个索引。
type 字段代表了访问表的方式，其值 ref 说明是二级索引等值匹配，符合我们的查询。
把 SQL 中的 * 修改为 NAME 和 SCORE，也就是 SELECT name_score 联合索引包含的两列，查看执行计划：
代码语言：javascript复制explain select NAME,SCORE from person where NAME='name1';
执行计划
可以看到，Extra 列多了一行 Using index 的提示，证明这次查询直接查的是二级索引，免去了回表。
创建索引最佳实践
了解了上面的三条代价，现在我们知道，索引并不是解决查询慢的万能钥匙。这里我总结了三条创建索引的最佳实践供你参考。
第一，无需一开始就建立索引。
可以等到业务场景明确后，或者是数据量超过 1 万、查询变慢后，再针对需要查询、排序或分组的字段创建索引。创建索引后可以使用 EXPLAIN 命令，确认查询是否可以使用索引。
第二，尽量索引轻量级的字段。
比如能索引 int 字段就不要索引 varchar 字段。索引字段也可以是部分前缀，在创建的时候指定字段索引长度。针对长文本的搜索，可以考虑使用 Elasticsearch 等专门用于文本搜索的索引数据库。
第三，尽量不要在 SQL 语句中 SELECT *。
应该 SELECT 必要的字段，甚至可以考虑使用联合索引来包含我们要搜索的字段（即索引覆盖），既能实现索引加速，又可以避免回表的开销。
小结
本文我们分析了创建二级索引的三个代价，即维护代价、空间代价、回表代价。索引不是解决查询性能问题的万能钥匙。
整理自极客时间《Java开发常见错误》学习笔记。如对你有帮助，请转发支持，非常感谢！ 一起学习请关注公众号：【杨同学technotes】领取 MySQL 精品技术书籍。
