mysql 联合索引 复合索引(abc)如何索引命中规则实测

可以略过

最近找工作面试时,被问及了mysql联合索引的一些问题,之前看过‘高性能mysql’中第五章关于索引的部分,也看过部分类似的问题,但我回答的却被面试官质疑,我说从mysql某版本后 where后 与a b c 顺序无关了,b = and c = and a = 也会使用联合索引,顺序不是abc时mysql索引优化器会自动优化。说我错了 叉 叉 叉... ... 还出来interview...
不过
确实如果在网上搜索关于这相关问题,一些老的文章中真的说这是错的,但近几年的文章都有说 和顺序无关了

实际在本地测一下吧

数据准备 说明

本地 MYSQL版本 8.0.17

mysql创建一张表,表名:‘test_models’

  • id列为 主键,int类型 ,自增
  • a,b,c,d,e 全部是int(11)
  • 为(a,b,c)添加一个联合索引 index_abc

执行语句大体这样吧:

CREATE TABLE `test_models` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
 `a` INT(11) DEFAULT NULL,
 `b` INT(11) DEFAULT NULL,
 `c` INT(11) DEFAULT NULL,
 `d` INT(11) DEFAULT NULL,
 `e` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_abc` (`a`,`b`,`c`)
);

我用代码往表中写入100万条数据
其中每行数据取值

  • a 范围 0 - 1000 随机整数
  • b 范围 0 - 10000 随机整数
  • c 范围 0 - 100000 随机整数
  • d 范围 0 - 1000 随机整数
  • e 范围 0 - 1000 随机整数

开始测试 联合索引(a,b,c)

使用 ‘EXPLAIN’ sql语句查看执行详情
EXPLAIN SELECT * FROM test_models WHERE a = 100 AND b = 1000 AND c = 10000;

image.png

结论(下面结论全部都实际测试过)

AND AND 只要用到了最左侧a列,和顺序无关 都会使用 索引
a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引
不包含最左侧的 a 的不使用索引
c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引
OR 不使用索引
a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引
a = 1 OR b = 2 OR c = 3 未使用索引
最左侧的‘a’列 被大于,小于,不等于比较的 ,不使用索引
a > 1 AND b = 2 AND c = 3  未使用索引
a < 1 AND b =  2 AND c = 3  未使用索引
a > 1 ; 未使用索引
a <> 1 AND b = 2 AND c = 3 未使用索引
最左侧a=某某,后面列大于小于无所谓,都使用索引(但后面必须 and and )
a = 1 AND b < 2 AND c = 3 使用索引
a = 1 AND c = 2 AND b < 3 使用索引
a = 1 AND b < 2 使用索引
a = 1 AND b <> 2 AND c = 3 使用索引
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 未使用索引
ORDER BY

a = 某,后面order 无所谓 都 使用索引 (和最上面的最左匹配一样)

a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b , ORDER BY c ,ORDER BY d, 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d 使用abc索引

b = 某,不使用

b = 1 ORDER BY a; //ORDER BY b 都 未使用索引
ORDER BY 似乎没有任何影响,去掉 ORDER BY 确实和上面的测试重复

推荐阅读更多精彩内容

  • 专业考题类型管理运行工作负责人一般作业考题内容选项A选项B选项C选项D选项E选项F正确答案 变电单选GYSZ本规程...
    小白兔去钓鱼阅读 7,580评论 0 13
  • 1.A simple master-to-slave replication is currently being...
    Kevin关大大阅读 4,813评论 0 3
  • 今天看到一位朋友写的mysql笔记总结,觉得写的很详细很用心,这里转载一下,供大家参考下,也希望大家能关注他原文地...
    信仰与初衷阅读 3,897评论 0 29
  • 忙碌中我们迎来了中国人最盛大的节日——春节,家家都喜气洋洋,挂上红灯笼,贴上红对联,全家忙忙碌碌 .......
    捕捉仙女阅读 57评论 0 1
  • 《侏罗纪世界2》是在侏罗纪主题公园及豪华度假村被失控的恐龙们摧毁三年后拉开了序幕。 纳布拉尔岛已经被...
    耿艺铭tian阅读 214评论 0 0