Mysql索引失效场景

CREATE DATABASE tindex CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


USE tindex;



EXPLAIN 执行结果说明

#select_type 查询类型      
常见取值 SIMPLE 简单表不使用表连接或子查询 
         PRIMARY 主查询即外层的查询
         UNION  union中的第二个或者后面的查询语句
         subquery 子查询中的第一个
         
#table 输出结果集的表
#type  在表中找到所需行的方式，访问类型

      从上到下 由差到最好
      ALL      全表扫描           MySQL遍历全表来找到匹配一般是没有where条件或者where条件没有使⽤索引的查询语句
      INDEX       索引全扫描          MySQL遍历整个索引来查询匹配行，并不会扫描表一般是查询的字段都有索引的查询语句
      RANGE       索引范围扫描          索引范围扫描，常用于<、<=、>、>=、between等操作
      ref      非唯一索引扫描      使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行 
                                  经常出现在join操作中 表关联查询时必定有一张表全扫描（行最少的表）然后再通过非唯一索引寻找其他关联表中的匹配行 以此达到表关联时扫描行最少
      eq_ref   唯一索引扫描       类似ref 区别在于使用的索引是唯一索引 对于每个索引值 表中只有一条记录匹配 
                                  一版出现在多表连接查询时使用主键或者unique index作为关联条件
      const|system 单表最多有一个匹配行 单表中最多有一条匹配行 查询起来非常迅速 一版出现在根据主键或unique_index进行查询
      NULL       不用扫描表或索引   不用访问表或者索引直接就能得到结果
      
#possible_keys 表示查询可能使用的索引
#key 实际使用的索引
#key_len 使用索引字段的长度
#ref 使用哪个列或者常数与key一起从表中选择行
#rows 扫描行的数量
#filtered 储存引擎返回的数据在server层过滤后 剩下多少满足查询的记录数量的百分比
#Extra 执行情况说明和描述 包含不适合在其他列中显示但是对执行计划非常重要的额外信息      
      一版有：
      USING WHERE 表示进行了回表查询
      USING INDEX CONDITION 表示进行了ICP优化
      USING Flesort 表示mysql需要额外排序操作 不能通过索引顺序达到排序效果
      
      
    

什么是ICP？
MySQL5.6引入了Index CONDITION Pushdown（ICP）的特性，进一步优化了查询。
Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。
EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;
在5.6版本之前：
优化器首先使用复合索引idx_rental_date过滤出符合条件
的记录，然后根据复合索引idx_rental_date回表获取记录，最终根据条件
过滤出
rental_date='2005-05-25'
customer_id>=300 AND customer_id<=400
最后的查询结果（在服务层完成）。
在5.6版本之后：
MySQL使用了ICP来进⼀步优化查询，在检索的时候，把条件customer_id>=300 AND customer_id<=400
也推到存储引擎层完成过滤，这样能够降低不必要的IO访问。Extra为  USING INDEX CONDITION
使用了ICP优化。

最左匹配原则
联合索引的一种规则，索引的底层是B+树，联合索引是键值数量多个的B+树
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 AND b = 2 AND c > 3 AND d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，
如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
1 条件查询中条件顺序没有关系
2 在最左匹配原则中，有如下说明：
最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 AND b = 2 AND c > 3 AND d = 4 如果建立(a,b,c,d)顺序的索引，
d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
《深入浅出mysql》




CREATE TABLE `t_user` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`id_no` VARCHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '身份编号',
`username` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名',
`age` INT(11) DEFAULT NULL COMMENT '年龄',
`create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `union_idx` (`id_no`,`username`,`age`),
KEY `create_time_idx` (`create_time`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;





-- 有三个索引：KEY `union_idx` (`id_no`,`username`,`age`)
-- id ：为数据库主键；
-- union_idx ：为id_no、username、age构成的联合索引；
-- create_time_idx ：是由create_time构成的普通索引；

INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (NULL, '1001', 'Tom1', 11, '2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (NULL, '1002', 'Tom2', 12, '2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (NULL, '1003', 'Tom3', 13, '2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`, `id_no`, `username`, `age`, `create_time`) VALUES (NULL, '1004', 'Tom4', 14, '2023-02-25 09:04:23');



-- 存储过程及其调用的SQL，方便批量插入数据，用来验证数据比较多的场景：
-- 删除历史存储过程
DROP PROCEDURE IF EXISTS `insert_t_user`
-- 创建存储过程
DELIMITER $
CREATE PROCEDURE insert_t_user(IN limit_num INT)
BEGIN
DECLARE i INT DEFAULT 10;
DECLARE id_no VARCHAR(18) ;
DECLARE username VARCHAR(32) ;
DECLARE age TINYINT DEFAULT 1;
WHILE i < limit_num DO
SET id_no = CONCAT("NO", i);
SET username = CONCAT("Tom",i);
SET age = FLOOR(10 + RAND()*2);
INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
SET i = i + 1;
END WHILE;
END $
-- 调用存储过程
CALL insert_t_user(100);



-- 联合索引不满足最左匹配原则
-- 使用了select *
-- 索引列参与运算
-- 索引列参使用了函数
-- 错误的Like使用
-- 类型隐式转换
-- 使用OR操作
-- 两列做比较
-- 不等于比较
-- is not null    
-- not in和not exists
-- order by导致索引失效
-- 参数不同导致索引失效
-- 当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。
-- Mysql优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。

-- 查看SQL语句执行计划
EXPLAIN SELECT * FROM t_user WHERE id = 1

-- 可以看到上述SQL语句使用了主键索引（PRIMARY）， key_len 为4；
-- 其中 key_len 的含义为：表示索引使用的字节数，根据这个值可以判断索引的使用情况，
-- 特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。



-- 联合索引遵从最左匹配原则，顾名思义，在联合索引中，最左侧的字段优先匹配。
-- 因此，在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。
-- 而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。
--  union_idx 联合索引组成： KEY `union_idx` (`id_no`,`username`,`age`)
-- 最左边的字段为id_no，一般情况下，只要保证id_no出现在查询条件中，则会走该联合索引。

-- key_len的计算：
id_no 类型为varchar(18)，字符集为utf8mb4_bin，也就是使用4个字节来表示一个完整的UTF-8。此时，key_len = 18* 4 = 72；
由于该字段类型varchar为变长数据类型，需要再额外添加2个字节。此时，key_len = 72 + 2 = 74；
由于该字段运行为NULL（default NULL），需要再添加1个字节。此时，key_len = 74 + 1 = 75；

-- 下面都是走索引的正向例子，也就是满足最左匹配原则的例子
EXPLAIN SELECT * FROM t_user WHERE id_no = '1002';
-- 依旧走了union_idx索引，根据上面key_len的分析，大胆猜测，在使用索引时，不仅使用了id_no列，还使用了username列。
-- var(32) 32*4+3
EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND username = 'Tom2';
-- 走了union_idx索引，但跟示例一一样，只用到了id_no列。
EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND age = 12;


-- 反向示例：
EXPLAIN SELECT * FROM t_user WHERE username = 'Tom2' AND age = 12;
EXPLAIN SELECT * FROM t_user WHERE age = 12;
EXPLAIN SELECT * FROM t_user WHERE username = 'Tom2';

### 第一种索引失效的场景就是：在联合索引的场景下，查询条件不满足最左匹配原则。


-- 在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范：
-- 【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
-- 说明:
-- 1)增加查询分析器解析成本。
-- 2)增减字段容易与 resultMap 配置不一致。
-- 3)无用字段增加网络 消耗，尤其是 text 类型的字段。
-- 4)禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引

-- 比如，在上面的联合索引中，如果查询条件是age或username，当使用了select * ，肯定是不会走索引的。
-- 但如果希望根据username查询出id_no、username、age这三个结果（均为索引字段），明确查询结果字段，是可以走覆盖索引的：

EXPLAIN SELECT * FROM t_user WHERE id_no = '1002';  ##最左原则 走联合索引
EXPLAIN SELECT * FROM t_user WHERE username = 'Tom2'; ##查询条件不明确 不走索引
EXPLAIN SELECT id_no, username, age FROM t_user WHERE username = 'Tom2'; ###查询条件明确 走联合索引
EXPLAIN SELECT id_no, username, age FROM t_user WHERE age = 12; ###查询条件明确 走联合索引
EXPLAIN SELECT  username, age,id_no FROM t_user WHERE age = 12;  ###查询条件明确跟字段顺序没关系 走联合索引、

### 第二种索引失效的场景就是：在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引；不明确 不符合最左匹配原则 就会出现不走索引现象


EXPLAIN SELECT * FROM t_user WHERE id + 1 = 2 ;
-- 即便id列有索引，由于进行了计算处理，导致无法正常走索引。
-- 针对这种情况，其实不单单是索引的问题，还会增加数据库的计算负担。就以上述SQL语句为例，
-- 数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值进行比较。
-- 如果每次执行都经历上述步骤，性能损耗可想而知。
-- 建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。
-- 内存计算，得知要查询的id为1
EXPLAIN SELECT * FROM t_user WHERE id = 1 ;
-- 参数侧计算
EXPLAIN SELECT * FROM t_user WHERE id = 2 - 1 ;
### 第三种索引失效的场景就是：索引列参与了运算，会导致全表扫描，索引失效。



EXPLAIN SELECT * FROM t_user WHERE SUBSTR(id_no,1,3) = '100';
-- 索引列使用了函数（SUBSTR，字符串截取），导致索引失效。
-- 此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，
-- 获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。
-- 示例中只列举了SUBSTR函数，像CONCAT等类似的函数，也都会出现类似的情况。
-- 解决方案可参考第三种场景，可考虑先通过内存计算或其他方式减少数据库来进行内容的处理。

### 第四种索引失效的场景就是：索引列参使用了函数


EXPLAIN SELECT * FROM t_user WHERE id_no LIKE '%00%';
EXPLAIN SELECT * FROM t_user WHERE id_no LIKE '00%';
EXPLAIN SELECT * FROM t_user WHERE id_no LIKE '%00';
-- like的使用非常频繁，但使用不当往往会导致不走索引。常见的like使用方式有：
-- 方式一：like '%abc'；
-- 方式二：like 'abc%'；
-- 方式三：like '%abc%'；
-- 其中方式一和方式三，由于占位符出现在首部，导致无法走索引。
-- 这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。
-- 而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

### 第五种索引失效的场景就是：模糊查询时（like语句），模糊匹配的占位符位于条件的首部。


EXPLAIN SELECT * FROM t_user WHERE id_no = 1002;
EXPLAIN SELECT * FROM t_user WHERE id_no = '1002';
EXPLAIN SELECT * FROM t_user WHERE id = '2';
-- id_no字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描。
-- 出现索引失效的原因是：varchar和int是两个种不同的类型。
-- 解决方案就是将参数1002添加上单引号或双引号。
-- 还有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，虽然使用了单引号或双引号：依旧会走索引
### 第六种索引失效的场景就是：参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。



-- 果单独使用username字段作为条件很显然是全表扫描，既然已经进行了全表扫描了，
-- 前面id的条件再走一次索引反而是浪费了。所以，在使用or关键字时，切记两个条件都要添加索引，否则会导致索引失效。
-- 但如果or两边同时使用“>”和“<”，则索引也会失效：
EXPLAIN SELECT * FROM t_user WHERE id = 2 OR username = 'Tom2';
EXPLAIN SELECT * FROM t_user WHERE id  > 1 OR id  < 80;
### 第七种索引失效的场景就是：查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效；
### or两边为“>”和“<”范围查询时，索引失效。



EXPLAIN SELECT * FROM t_user WHERE id > age;
### 第8种索引失效的场景就是：两列数据做比较，即便两列都创建了索引，索引也会失效。



-- 当查询条件为字符串时，使用”<>“或”!=“作为条件查询，有可能不走索引，但也不全是。
-- 由于“2022-02-27 09:56:42”是存储过程在同一秒生成的，大量数据是这个时间。
-- 执行之后会发现，当查询结果集占比比较小时，会走索引，占比比较大时不会走索引。此处与结果集与总体的占比有关。
EXPLAIN SELECT * FROM t_user WHERE id_no <> '1002';
EXPLAIN SELECT * FROM t_user WHERE create_time != '2022-02-27 09:56:42';
EXPLAIN SELECT * FROM t_user WHERE id != 2;

### 第9种索引失效的场景就是：查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。



EXPLAIN SELECT * FROM t_user WHERE id_no IS NOT NULL;
EXPLAIN SELECT * FROM t_user WHERE id_no IS  NULL;
### 第10种索引失效的场景就是：查询条件使用is null时正常走索引，使用is not null时，不走索引。


-- 正常
EXPLAIN SELECT * FROM t_user WHERE id IN (2,3);
EXPLAIN SELECT * FROM t_user WHERE id_no IN ('1001','1002');
EXPLAIN SELECT * FROM t_user u1 WHERE EXISTS (SELECT 1 FROM t_user u2 WHERE u2.id  = 2 AND u2.id = u1.id);
EXPLAIN SELECT * FROM t_user WHERE id_no BETWEEN '1002' AND '1003';
EXPLAIN SELECT * FROM t_user WHERE id NOT IN (2,3);
-- 反常
EXPLAIN SELECT * FROM t_user WHERE id_no NOT IN('1002' , '1003');
###第11种索引失效的场景就是：查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

EXPLAIN SELECT * FROM t_user u1 WHERE NOT EXISTS (SELECT 1 FROM t_user u2 WHERE u2.id  = 2 AND u2.id = u1.id);
###第12种索引失效情况：查询条件使用not exists时，索引失效。

-- 在网络上看到有说如果order by条件满足最左匹配则会正常走索引， 
-- 在当前8.0.18版本中并未出现。所以，在基于order by和limit进行使用时，要特别留意。
-- 是否走索引不仅涉及到数据库版本，还要看Mysql优化器是如何处理的。
-- 这里还有一个特例，就是主键使用order by时，可以正常走索引。
EXPLAIN SELECT * FROM t_user ORDER BY id_no ;  ##order by导致索引失效
EXPLAIN SELECT * FROM t_user ORDER BY id_no LIMIT 10;###在网络上看到有说如果order by条件满足最左匹配则会正常走索引， 在当前8.0.18版本中并未出现。所以，在基于order by和limit进行使用时，
要特别留意。是否走索引不仅涉及到数据库版本，还要看Mysql优化器是如何处理的
EXPLAIN SELECT * FROM t_user ORDER BY id DESC; ###主键使用order by时，可以正常走索引
EXPLAIN SELECT id FROM t_user ORDER BY age;###覆盖索引的场景也是可以正常走索引
EXPLAIN SELECT id , username FROM t_user ORDER BY age;###覆盖索引的场景也是可以正常走索引
EXPLAIN SELECT id_no FROM t_user ORDER BY id_no;###覆盖索引的场景也是可以正常走索引
EXPLAIN SELECT * FROM t_user ORDER BY id,id_no DESC;###未走索引
EXPLAIN SELECT * FROM t_user ORDER BY id,id_no DESC LIMIT 10;###未走索引
EXPLAIN SELECT * FROM t_user ORDER BY id_no DESC,username DESC;###未走索引
###第13种索引失效情况：当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，
###而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。
###order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。


-- 当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。
-- 某些场景下即便强制SQL语句走索引，也同样会失效。
-- 类似的问题，在进行范围查询（比如>、< 、>=、<=、in等条件）时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。
-- 上述索引失效是因为DBMS发现全表扫描比走索引效率更高，因此就放弃了走索引。
EXPLAIN SELECT * FROM t_user WHERE create_time > '2023-02-24 09:04:23'; #走索引
EXPLAIN SELECT * FROM t_user WHERE create_time > '2022-02-27 09:04:23'; #不走
###第14种索引失效情况：当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。



###第15种索引失效的情况：Mysql优化器的其他优化策略，比如优化器认为在某些情况下，全表扫描比走索引快，则它就会放弃索引。