2022/12/17 MySQL索引失效的底层原理

1 复合索引-最左前缀原理

where子句中使用最频繁的一列放在最左边；我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照 **（a ）, （a，b ）,（a，b，c ）**的顺序都可以利用到索引，这就是最左前缀匹配。

如果查询语句是：
select * from table where a=1 and c=3； 那么只会用到索引a。
如果查询语句是：
select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是没有用到索引的。

2 构造测试数据-1000000

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4;

delimiter //
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=187635267;
SET ageVal=100;
SET i=1;
WHILE i <= 1000000 DO
        INSERT INTO t_user(`name`,age,create_time,update_time) VALUES(CONCAT(FLOOR(RAND()*100), pre_name,'@qq.com'),(RAND() * 100),NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END //

delimiter ;
call proc_batch_insert();

3 索引失效的情况

1 在索引列上做任何操作，如计算、函数，会导致索引失效从而全表扫描。

2 违背最左匹配原则

alter table t_user add index index_name_age(name, age);

3 like查询是以%开头

5 范围查询中的索引与全表查询

前提是 where 条件后为索引字段——如果根据查询条件查询到数据的结果数量小于总数量的五分之一，则会走索引，否则会走全表扫描。因此，在进行范围查询时，比如>、< 、>=、<=等，如果数据量过大的话where语句的条件虽然添加了索引，但也有可能会进行全表扫描。所以，在查询时查询的范围要考虑进行限制或其他方式进行拆分。

6 存储引擎不能使用索引中范围条件右边的列

比如说有三个字段 a b c，建立复合索引a_b_c
此时叶子节点的数据排序后可能为

(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)

假设查找

 select a,b,c from table where a = 2 and b = 5 and c = 2

此时先根据a = 2找到第二行的四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

然后根据b=5查到两条

(a=2 b=5 c=1) (a=2 b=5 c=2)

最后根据c=2查到目标数据

(a=2 b=5 c=2)

接下来 假设使用了范围条件

select a,b,c from table where a = 2 and b >1 and c = 2

此时先根据a = 2找到第二行的四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

然后根据b>1查到四条数据

(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)

此时要查找c=2了 但是我们发现 这四条数据的c分别是
3,5,1,2 是无序的 所以索引失效了
总结：
前一个条件相同的情况下 当前条件才会是有序的。
当前一个条件不同 那么无法保证当前条件为有序的 所以索引失效。

7 查询条件中带有or，除非所有的查询条件都建有索引，否则索引失效