MySQL索引下推学习

表结构

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '职位',
  `card_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '工卡号',
  PRIMARY KEY (`id`),
  KEY `index_union` (`name`,`age`,`position`)
) ENGINE=InnoDB AUTO_INCREMENT=450001 DEFAULT CHARSET=utf8;

索引长度申明

name列202
age列4
position列202
index_union都用到的话大概408
所有的数据都输UUID生成的,45w数据。

案例一(like查询)

image-20210821122431438

image-5.6-pre

image-5.6-last

在mysql5.6之前,name like 'xxx%' 查询出结果集首先拿着这部分集,去主键索引树回表,在主键索引树找到这些结果集
再在这些结果集中查name和position。所以如图二展示,mysql通过name like '王五%'找到红色部分的结果集,同时也可
以得到绿色部分的主键结果集去下面的主键索引树中找到整行数据,在蓝色部分的结果集中通过age和position筛选出合适的结
果集:紫色所圈住的部分。


在mysql5.6之后,name like 'xxx%' 查询出结果集后,现在结果集中搜寻满足age和position的结果集,这样发筛选
出的结果集就相对之前的结果集小很多。回表时查询的主键id也少。如图三展示,mysql通过name like '王五%'找到红色部分1的
结果集,此时先不拿id结果集直接去回表,会在1的结果集直接筛选满足age结果集2,在拿着满足position的结果集3,然后拿着合
适的主键id集去主键索引树去回表,得到结果集4。

案例二(大于等范围查询)

image-20210821132705494

由图可知,虽然也用到了index_union索引,但是key_len长度只有202,索引使用不充分,只有name列参与了索引,按照案例一来说
like和>都是找的范围数据,应该也可以充分使用该索引的。只能猜测大于这样的范围查询在mysql底层就不让后续的条件参与索引。

联合索引第一个字段用范围

image-20210821182719984

image-20210821183002346

这种情况不一定,看范围查找的数据在全部数据中所占的比例,还是用explain分析保险。
强行用索引,牵扯到回表,mysql认为不如直接在主键索引树扫描,效率可能还高点。

Q.E.D.


一个热爱生活的95后精神小伙