MySQL索引下推探索

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查询）

在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。

案例二（大于等范围查询）

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

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

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