回表
二级索引不保存原始数据,通过索引找到主键后需要再查询聚簇索引,才能拿到想要的数据。
示例如下:
key=person_name_score_index
,表明走的是person_name_score_index
索引。
type=ref
,表明是二级索引的等值匹配,符合预期
再看如下SQL的执行计划:
Extra列多了一行Using index,说明直接查的二级索引,没有回表。
联合索引保存了多个索引列的值,对于页中的记录先按照字段1排序,若相同再按照字段2排序,如下:
图中叶子节点每一条记录的第1、2个方块是索引列的数据,第三个方块是记录的主键。若查询的是索引列索引或联合索引能覆盖的数据,则查询索引本身已经“覆盖”了需要的数据,无需再回表。这种情况也叫索引覆盖。
索引开销的最佳实践
- 无需一开始就建立索引,可等到场景明确或数据量超过1w、查询变慢,再针对需要查询、排序或分组的字段创建索引。创建索引后可使用EXPLAIN确认查询是否可以使用索引。
- 尽量索引轻量级的字段,比如能索引int字段就不要索引varchar字段。索引字段也可以是部分前缀,在创建的时候指定字段索引长度。针对长文本的搜索,可以考虑使用Elasticsearch等专门用于文本搜索的索引数据库
- 禁止SELECT
*
,而是SELECT必须字段,甚至可以考虑使用联合索引包含我们要搜索的字段,既能实现索引加速,又可避免回表。
不是所有针对索引列的查询都能用上索引
- 是不是建了索引一定可以用上?
- 到底是创建联合索引还是多个独立索引?
索引失效场景
索引只能匹配列前缀
LIKE语句查询name后缀为name123的用户,type=ALL全表扫描
把百分号放到后面走前缀匹配:
- type=range索引扫描
- key=person_name_score_index走
person_name_score_index
索引 - 索引中行数据按索引值排序,只能根据前缀进行比较。
若非要按后缀查询也能走索引,并且永远只是按后缀查询,可以把数据反过来存,用时再倒过来。
条件涉及函数操作无法走索引
比如查询条件用到了LENGTH函数,肯定无法走索引,type=ALL全表扫描
同理,索引保存的是索引列的原始值,而非经过函数计算后的值。若需要针对函数调用还能走索引,只能保存一份函数变换后的值,然后重新针对这个计算列做索引。
联合索引只能匹配左边的列
虽然对name和score建了联合索引,但仅按score列查询无法走索引
因为在联合索引情况下,数据按照索引第一列排序,第一列数据相同时才会按第二列排序。若想使用联合索引中尽可能多的列,查询条件中的各个列必须是联合索引中从最左边开始连续的列。若仅按第二列搜索,肯定无法走索引。
尝试把查询条件加入name列,可见走了person_name_score_index索引
因为有查询优化器,所以name作为WHERE子句的第几个条件并不重要。
现在回答一开始的问题:
- 是不是建了索引一定可以用上?
并不,只有当查询能符合索引存储的实际结构时,才能用上。刚才几个示例都用不上索引。 - 联合索引 or 多个独立索引?
若你的查询条件经常会使用多个字段,则考虑针对这几个字段建联合索引;同时,针对多字段建立联合索引,使用索引覆盖的可能更大。若只会查询单个字段,考虑建单独的索引,毕竟联合索引保存了不必要字段也有成本。
数据库基于成本决定是否走索引
查询数据可直接在聚簇索引上进行全表扫描,也可走二级索引扫描后到聚簇索引回表。
MySQL如何确定走哪个方案?
MySQL在查询数据之前,会先对可能的方案做执行计划,然后依据成本决定走哪个执行计划。
包括IO成本和CPU成本:
- I/O成本
从磁盘把数据加载到内存的成本。默认情况下,读取数据页的I/O成本常数是1(即读取1个页成本是1)。 - CPU成本
检测数据是否满足条件和排序等CPU操作的成本。默认情况下,检测记录的成本是0.2。
全表扫描成本
全表扫描,就是把聚簇索引中的记录依次和给定的查询条件对比,把符合搜索条件的记录加入结果集的过程。
所以要计算全表扫描的代价需要两个信息:
- 聚簇索引占用的页面数,用来计算读取数据的IO成本
- 表中的记录数,用来计算搜索的CPU成本
MySQL是实时统计的这些信息吗?
不是的,MySQL维护了表的统计信息,可使用命令:
可见总行数100147行。里表不是只有10w行记录吗,为啥这里还多了147行?
因为MySQL的统计信息只是个估算。现在我们估算下CPU成本:
100147*0.2=20030
数据长度是5783552B。对于InnoDB,这就是聚簇索引占用空间,等于聚簇索引的页面数量 * 每个页面的大小。InnoDB每个页16K,大概计算出页面数量是353,所以I/O成本是353。
综上,全表扫描总成本约20383。