-- mysql 建表语句
CREATE TABLE tb_test_qiu ( id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT )
-- tb_test_qiu表 插入联合索引
ALTER TABLE `tb_test_qiu` ADD INDEX `testabc` (`a`, `b`, `c`) ;
-- 索引最左原则和索引什么时候会失效的场景测试:
-- 实际上创建了 a,ab,abc索引,其中ab,abc的字母调换顺序完全不影响,也是命中索引。
-- 测试a
explain select id,a,b,c from tb_test_qiu where a = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where a > 1 -- range索引
-- 测试ab
explain select id,a,b,c from tb_test_qiu where b = 1 -- 无索引
explain select id,a,b,c from tb_test_qiu where b = 1 and a = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where a = 1 and b = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where b = 1 and a > 1 -- range索引
explain select id,a,b,c from tb_test_qiu where a > 1 and b = 1 -- range索引
-- 测试abc
explain select id,a,b,c from tb_test_qiu where c = 1 -- 无索引
explain select id,a,b,c from tb_test_qiu where b = 1 and c = 1 -- 无索引(index)
explain select id,a,b,c from tb_test_qiu where c = 1 and b = 1 -- 无索引
explain select id,a,b,c from tb_test_qiu where a = 1 and b = 1 and c > 0 -- range索引 (范围索引)
explain select id,a,b,c from tb_test_qiu where a = 1 and c = 1 and b = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where b = 1 and a = 1 and c = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where b = 1 and c = 1 and a = 1 -- 索引
explain select id,a,b,c from tb_test_qiu where c = 1 and a = 1 and b= 1 -- 索引
explain select id,a,b,c from tb_test_qiu where c = 1 and b = 1 and a= 1 -- 索引
explain select id,a,b,c from tb_test_qiu where c BETWEEN 1 and 2 and b = 1 and a= 1 -- range索引 (范围索引)
explain select id,a,b,c from tb_test_qiu where c <> 2 and b = 1 and a= 1 -- range索引 (范围索引)
explain select id,a,b,c from tb_test_qiu where a in (1) -- 索引(a = 1,命中索引)
explain select id,a,b,c from tb_test_qiu where a in (1,2,3) -- range索引 (in里的数据小于表里的总数据)
explain select id,a,b,c from tb_test_qiu where a in (1,2,3,4,5) -- 无索引(in里的数据>=表里的总数据)
explain select id,a,b,c from tb_test_qiu where a like '%1' -- 无索引
explain select id,a,b,c from tb_test_qiu where a like '%1%' -- 无索引
explain select id,a,b,c from tb_test_qiu where a like 'ddd%'-- 有索引
explain select id,a,b,c from tb_test_qiu where a is not null -- range索引
explain select id,a,b,c from tb_test_qiu where a is null -- 索引
-- is not null不管什么情况下都不会走索引(默认是range),is null在字段允许为空时会使用索引
-- 要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
对于索引失效问题工作中总结:
order by 的条件会影响正常的索引失效
例如:主键是id,优化器会优先选择主键id索引,使正常的索引或者联合索引不被使用,既失效
>= <= 和 between也会导致索引失效
例如:AND time_range_start >= '2023-10-02' and time_range_end <= '2023-10-02' 因为不是同一个字段,所以范围没有界限,导致失效
AND time_range_start >= '2023-10-02' and time_range_start <= '2023-10-02' 因为是同一个字段,有范围界限,索引生效
对于联合索引的工作中的理解;
举例:abcd_index(a,b,c) 的联合索引会有多少种组合情况命中索引?
1.a
2.b
3.c
联合索引的每个字段都是一个单独的索引,如上1,2,3所示
联合索引组合的情况,必须是有最左边的字段a出现,不然任何组合都失效
联合索引针对where条件的顺序没有要求,既ba,ca,cba都会命中联合索引(原理是查询优化器会自动进行排序优化,再进行索引匹配,但是ba只能部分命中索引,比如只能命中a【这是因为最左原则问题的体现】,cab只能命中ab索引,cba也只能命中a所以,bc都不能命中【最左原则必须有a索引的出现】)
4.ab,ba
5.ac,ca
6.abc,acb, bac,bca,cab,cba
如果where条件多个字段,
例如: 1.a = 1 and b = 1 and f = 1 and c = 1,也会正常命中abc索引,查询优化器只查找where条件里跟索引相同的字段,f不是索引字段,也不会影响索引失效
2.a = 1 and b = 1 and f = 1 ,会命中 ab 索引,f不是索引字段,不影响索引失效
总结:查询优化器会主动在where条件中找出符合abcd_index(a,b,c)联合索引的某个组合索引,如上举例所示。
索引顺序和where条件的顺序没有关系,优化器会主动调整where条件后面的顺序和索引顺序一致