创建二级索引的代价,主要表现在维护代价、空间代价和回表代价三个方面。 首先是维护代价。创建 N 个二级索引,就需要再创建 N 棵 B+ 树,新增数据时不仅要修改聚簇索引,还需要修改这 N 个二级索引。
页中的记录都是按照索引值从小到大的顺序存放的,新增记录就需要往页中插入数据,现有的页满了就需要新创建一个页,把现有页的部分数据移过去,这就是页分裂;如果删除了许多数据使得页比较空闲,还需要进行页合并。页分裂和合并,都会有 IO 代价,并且可能在操作过程中产生死锁。 其次是空间代价。虽然二级索引不保存原始数据,但要保存索引列的数据,所以会占用更多的空间。可以使用下面的 SQL 查看数据和索引占用的磁盘:
SELECT DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_NAME='person'最后是回表的代价。二级索引不保存原始数据,通过索引找到主键后需要再查询聚簇索引,才能得到我们要的数据。
2.6 联合索引数据结构
//比如下面的 LIKE 语句,搜索 name 后缀为 name123 的用户无法走索引,执行计划的 type=ALL 代表了全表扫描EXPLAIN SELECT * FROM person WHERE NAME LIKE '%name123' LIMIT 100//把百分号放到后面走前缀匹配,type=range 表示走索引扫描,key=name_score 看到实际走了 name_score 索引EXPLAIN SELECT * FROM person WHERE NAME LIKE 'name123%' LIMIT 100原因很简单,索引 B+ 树中行数据按照索引值排序,只能根据前缀进行比较。如果要按照后缀搜索也希望走索引的话,并且永远只是按照后缀搜索的话,可以把数据反过来存,用的时候再倒过来。
第二,条件涉及函数操作无法走索引。
//比如搜索条件用到了 LENGTH 函数,肯定无法走索引:EXPLAIN SELECT * FROM person WHERE LENGTH(NAME)=7同样的原因,索引保存的是索引列的原始值,而不是经过函数计算后的值。如果需要针对函数调用走数据库索引的话,只能保存一份函数变换后的值,然后重新针对这个计算列做索引。
第三,联合索引只能匹配左边的列。
//虽然对 name 和 score 建了联合索引,但是仅按照 score 列搜索无法走索引:EXPLAIN SELECT * FROM person WHERE SCORE>45678原因也很简单,在联合索引的情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。也就是说,如果我们想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索,肯定无法走索引。
//把搜索条件加入 name 列,可以看到走了 name_score 索引:EXPLAIN SELECT * FROM person WHERE SCORE>45678 AND NAME LIKE 'NAME45%'需要注意的是,因为有查询优化器,所以 name 作为 WHERE 子句的第几个条件并不是很重要。 第四,排序使用到索引
排序使用到索引,在执行计划中的体现就是 key 这一列。如果没有用到索引,会在 Extra 中看到 Using filesort,代表使用了内存或磁盘进行排序。而具体走内存还是磁盘,是由 sort_buffer_size 和排序数据大小决定的。
排序无法使用到索引的情况有:
所以,全表扫描的总成本是 20306 左右
接下来,我还是用 person 表这个例子,和你分析下 MySQL 如何基于成本来制定执行计划。现在,我要用下面的 SQL 查询 name>‘name84059’ AND create_time>‘2020-01-24 05:00:00’
EXPLAIN SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00'只要把 create_time 条件中的 5 点改为 6 点就变为走索引了,并且走的是 create_time 索引而不是 name_score 联合索引:
可以得到两个结论:
MySQL 选择索引,并不是按照 WHERE 条件中列的顺序进行的;
即便列有索引,甚至有多个可能的索引方案,MySQL 也可能不走索引。
原因就是,MySQL 并不是猜拳决定是否走索引的,而是根据成本来判断的。虽然表的统计信息不完全准确,但足够用于策略的判断了。
不过,有时会因为统计信息的不准确或成本估算的问题,实际开销会和 MySQL 统计出来的差距较大,导致 MySQL 选择错误的索引或是直接选择走全表扫描,这个时候就需要人工干预,使用强制索引了。比如,像这样强制走 name_score 索引:
EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00' 查看优化器生成执行计划的整个过程
MySQL 会根据成本选择执行计划,通过 EXPLAIN 知道优化器最终会选择怎样的执行计划,但 MySQL 如何制定执行计划始终是一个黑盒。那么,有没有什么办法可以了解各种执行计划的成本,以及 MySQL 做出选择的依据呢?
在 MySQL 5.6 及之后的版本中,我们可以使用 optimizer trace 功能查看优化器生成执行计划的整个过程。有了这个功能,我们不仅可以了解优化器的选择过程,更可以了解每一个执行环节的成本,然后依靠这些信息进一步优化查询。
如下代码所示,打开 optimizer_trace 后,再执行 SQL 就可以查询 information_schema.OPTIMIZER_TRACE 表查看执行计划了,最后可以关闭 optimizer_trace 功能:
SET optimizer_trace="enabled=on";SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-01-24 05:00:00';SELECT * FROM information_schema.OPTIMIZER_TRACE;SET optimizer_trace="enabled=off";对于按照 create_time>'2020-01-24 05:00:00’条件走全表扫描的 SQL,我从 OPTIMIZER_TRACE 的执行结果中,摘出了几个重要片段来重点分析: