覆盖索引，但是没有最左前缀匹配，性能如何

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 1552 天前的主题，其中的信息可能已经有所发展或是发生改变。

有一个组合索引 idx on t1(c1, c2); 查询语句 select id, c1 from t1 where c2 = xxx; 这里会用到覆盖索引，但是却是用 c2 做条件查询，这样会全索引扫描吗，数据量大了，性能如何？

索引

select

idx

查询

22 条回复 • 2020-09-04 17:34:15 +08:00

zhouhu

2020-09-03 14:36:22 +08:00

这个查询不会用到你所说的索引

optional

2020-09-03 14:37:57 +08:00 via iPhone

这个会用覆盖索引？

hackingwu

2020-09-03 14:46:32 +08:00

@zhouhu 肯定会呀，你 explain 一下就知道了，走覆盖索引呀

useben

2020-09-03 14:52:59 +08:00

不走索引, 就是全表扫描的性能

qianProgrammer

2020-09-03 15:02:44 +08:00

type 是 index 吧,扫描了整个索引树,只是正好匹配到了你的条件,不如最左匹配的效率高,但也比 all 全表扫描快

Jooooooooo

2020-09-03 15:03:58 +08:00

这和没索引一样

wangritian

2020-09-03 15:06:56 +08:00

覆盖索引应该是指索引树上包含了本次查询的全部字段，无需回表，你的 sql 是符合的
但查询条件 c2 不在 t1 的开头，所以走全表扫描

yukong

2020-09-03 15:22:04 +08:00

这不是全表扫描了吗

CODEWEA

2020-09-03 15:27:39 +08:00

怎么可能呢

wps353

2020-09-03 15:34:16 +08:00

在 8.0.13 以前的话用不到覆盖索引，在 8.0.13 后，如果说 a 的 Cardinality 比较低的话，有可能通过 skip index scan 使用到。

zhangysh1995

2020-09-03 15:56:43 +08:00

根据文档应该是全表扫描或者尝试选过滤高的

If separate single-column indexes exist on col1 and col2, the optimizer attempts to use the Index Merge optimization (see Section 8.2.1.3, “Index Merge Optimization”), or attempts to find the most restrictive index by deciding which index excludes more rows and using that index to fetch the rows.

https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

hackingwu

2020-09-03 16:15:48 +08:00

@wangritian 我的 c1,c2 信息都在索引里，不会再回表啦。

hackingwu

2020-09-03 16:16:50 +08:00

@zhangysh1995 我这不是 single-column index, 我这是组合索引呀，不是你文档描述的场景

SjwNo1

2020-09-03 16:29:16 +08:00

直接没走你的联合索引吧自建索引树都没走怎么利用覆盖索引（有点忘了

wangritian

2020-09-03 19:51:30 +08:00

@hackingwu 突然发现自己上面的描述不对，应该是走了全索引树扫描。我猜优化器发现你的 t1 索引包含了全部需求字段，所以在 t1 索引树上做全部扫描，而没有去主键索引树（原表）做扫描，因为 t1 树的字节大小一定小于原表，更节省磁盘 I/O 。所以你的 explain 结果用到了覆盖索引。说的不对请指正。

changdy

2020-09-03 22:29:53 +08:00

心疼题主....那么多人都说直接走全表,不知道有没有怀疑人生...

狗尾续貂 , 也请教各位一个问题 , mysql 的分区表如何 ? 目前系统业务量比较大, 想用 mysql 本身的分区表实现类似分表功能.

RedisMasterNode

2020-09-03 23:31:39 +08:00

心疼楼主...还有这么多人说走全表，一定要坚持有自己的观点呀^_^特别是你觉得有足够的东西支撑观点的时候

这个查询毫无疑问（正常情况下）会走你的(c1, c2)索引，前提是你的表可能得不止有 id, c1, c2 这 3 个字段，理由是主键索引的每个页能够存放下的数据行比(c1, c2)索引每个页能存放下的数据行更少，如果扫描主键获取结果，需要扫过更多的数据页，如果扫(c1, c2)索引，尽管没有用上"索引"的查询特性，但是可以通过扫更少的页得到正确结果。

性能的差距 = 主键索引体积 vs (c1, c2)索引体积，当你的表字段越多，越大的时候，差距越明显；反例则是如果表只有 id, c1, c2 三个字段的时候，扫描的时间理论上应该是非常接近的，并且优化器在选择上两个索引都有可能选择使用

RedisMasterNode

2020-09-03 23:40:32 +08:00

em 补充一点，在字段少（但不仅有 id, c1, c2 ）的情况下，如果 c1, c2 是 varchar 之类的类型，未必会真的选择(c1, c2)索引进行索引扫描，因为选择使用什么查询路径是根据 cardinality 值来决定的，这个值是通过定期对页进行采样、统计的结果，是个预估值，如果不巧 mysql 记录的主键索引和(c1, c2)索引的预估值与实际不符，那有可能会采取（实际性能更差）的主键扫描来执行

maigebaoer

2020-09-03 23:54:46 +08:00 via Android

sql 已经是黑箱子了😂以 explain 为准吧！猜测是因为索引已经包含了所有需要的信息，直接从索引取值了。

pkoukk

2020-09-04 09:58:27 +08:00

不会走全表扫描（主键扫描），但是会走你这个索引的全索引扫描。
怎么说呢，肯定比全表强，但是到底强多少得看你这个索引的区分度

zhangysh1995

2020-09-04 16:09:01 +08:00

@hackingwu 你应该看下上线文，这里是说组合索引用不了，如果有 single column 索引会用的。和你的情况没有冲突。

zhouhu

2020-09-04 17:34:15 +08:00

@hackingwu 不好意思，我说错了。试了一下，确实是可以的。不过实际上也是不推荐这种用法吧。如果索引能全部加载进内存，速度应该还行。