关于Mysql5.7及8.0版本索引失效情况汇总
作者：keep丶 发布时间：2024-01-21 08:35:35
标签：Mysql5.7,Mysql8.0,索引失效
TIPS：
没有特殊说明，测试环境均为MySQL8.0，早期版本可能会有更多情况导致索引失效。8.0失效的情况，早期版本也失效；8.0不失效的情况，早期版本可能失效。
所有测试默认不考虑表为空的情况，特殊情况文中会有说明。
本文只介绍Innodb引擎下的索引失效情况。
-- 创建测试表
DROP TABLE IF EXISTS `test_idx`;
CREATE TABLE `test_idx` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`unique_idx` int(11) NOT NULL,
`notnull_idx` int(11) NOT NULL,
`str_idx` varchar(20) DEFAULT NULL,
`normal_idx` int(11) DEFAULT NULL,
`str_col` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique_idx` (`unique_idx`),
KEY `idx_str_idx` (`str_idx`),
KEY `idx_normal_idx` (`normal_idx`) USING BTREE
) DEFAULT CHARSET=utf8;
-- 插入几条测试数据，因为当表里没有数据时，部分使用到索引的情况会失效。
INSERT INTO test_idx VALUES
(1,1,'1',1,'111'),
(2,2,'2',2,'222'),
(3,3,'3',3,'333')
一个独立索引
1、使用like且在左边有“％”。
-- 无法使用索引
EXPLAIN select * from test_idx where bid like '％1％';
-- 可以使用索引
EXPLAIN select * from test_idx where bid like '1％';
2、隐式类型转换，索引字段与条件或关联字段的类型不一致。
-- 无法使用索引
EXPLAIN select * from test_idx where bid = 1;
-- 可以使用索引
EXPLAIN select * from test_idx where bid = '1';
3、条件中对索引列进行运算或使用函数
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE SUBSTR(bid, 1) = '1';
EXPLAIN SELECT * FROM test_idx WHERE id - 1 = 1;
-- 可以使用索引
EXPLAIN SELECT * FROM test_idx WHERE id = 4 - 2;
EXPLAIN SELECT * FROM test_idx WHERE id = TIME_TO_SEC(1);
4、不可空索引使用 is not null，仅当查询列只有该索引列时会使用索引
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE notnull_idx IS NOT NULL;
-- 可以使用索引
EXPLAIN SELECT notnull_idx FROM test_idx WHERE notnull_idx IS NOT NULL;
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NOT NULL;
5、使用OR且存在非索引列
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR str_col = '1';
-- 使用OR时，OR包含的所有列必须都是独立索引才有可能用到索引
6、使用 NOT IN、IN、IS NULL、IS NOT NULL，且返回值中不止包含条件索引列。
-- 部分情况下可以使用索引
-- 当表里没有数据时不使用索引
-- 本次测试当后面的条件查询的返回值大于等于总数据50％时不使用索引;少于总数据50％则使用索引
-- 该数据不一定准，这跟数据总量、表统计信息等会有不同的表现，因此还是得看最终优化器的选择！
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1,2);
EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1,2);
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NULL;
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IS NOT NULL;
拓展：
MySQL环境变量eq_range_index_dive_limit的值对IN语法有很大影响，该参数表示使用索引情况下IN中参数的最大数量。MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10，之后的版本默认值为200。
我们拿MySQL8.0.19举例，eq_range_index_dive_limit=200表示当IN (...)中的值 >200个时，该查询一定不会走索引。<=200则可能用到索引。
7、使用非主键范围条件查询时，部分情况索引失效。
-- 部分情况下可以使用索引
-- 当表里没有数据时不使用索引
-- 本次测试当范围条件查询的返回值大于等于总数据50％时不使用索引;少于总数据50％则使用索引
-- 该数据不一定准，这跟数据总量、表统计信息等会有不同的表现，因此还是得看最终优化器的选择！
EXPLAIN SELECT * FROM test_idx WHERE normal_idx > 1;
EXPLAIN SELECT * FROM test_idx WHERE normal_idx <= 1;
EXPLAIN SELECT * FROM test_idx WHERE normal_idx BETWEEN 0 AND 1;
EXPLAIN SELECT * FROM test_idx WHERE normal_idx BETWEEN 2 AND 5;
8、MySQL5.7，使用 IS NOT NULL或 IS NULL 部分情况下索引失效。
-- 部分情况下可以使用索引
-- 当表里没有数据时索引失效
-- 本次测试条件查询的返回值大于等于总数据50％时不使用索引;少于总数据50％则使用索引
-- 该数据不一定准，这跟数据总量、表统计信息等会有不同的表现，因此还是得看最终优化器的选择！
EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IS NOT NULL;
EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IS NULL;
9、MySQL5.7，使用 != 或 IN 或 NOT IN 部分情况下索引失效
-- 部分情况下可以使用索引
-- 本次测试当条件查询的返回值大于等于总数据50％时不使用索引;少于总数据50％则使用索引
-- 该数据不一定准，这跟数据总量、表统计信息等会有不同的表现，因此还是得看最终优化器的选择！
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1);
EXPLAIN SELECT * FROM test_idx WHERE normal_idx NOT IN (1);
-- 使用 != 也跟数据的筛选率有关，具体数值不能确定(但肯定筛选率要>50％)。视实际情况而定，还得看优化器的选择。
-- 可能使用索引，也可能不使用：
EXPLAIN SELECT * FROM test_idx WHERE normal_idx IN (1);
-- 可以使用索引,但是这里不是使用索引去查数据，而且是去查索引键值。
-- 即先根据该索引去查其他索引的值，再根据查出来的索引值去查数据。
-- extra中显示 Using index 均表示该情况。
EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx IN (1);
EXPLAIN SELECT normal_idx FROM test_idx WHERE normal_idx NOT IN (1);
10、MySQL5.7，表关联时，关联字段字符集不一致会导致索引失效。
-- 创建一个字符集与之前的表不一致的表
CREATE TABLE `test_idx2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`unique_idx` int(11) DEFAULT NULL,
`notnull_idx` int(11) NOT NULL,
`str_idx` varchar(20) DEFAULT NULL,
`normal_idx` int(11) DEFAULT NULL,
`str_col` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `idx_unique_idx` (`unique_idx`),
KEY `idx_str_idx` (`str_idx`),
KEY `idx_normal_idx` (`normal_idx`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入数据，如果没有数据，在MySQL8.0中索引也会失效
INSERT INTO test_idx2 values
(1,1,1,'1',1,'11'),
(2,2,2,'2',2,'22'),
(3,3,3,'3',3,'33');
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx t1
LEFT JOIN test_idx2 t2 ON t1.str_idx = t2.str_idx;
-- 子查询可以使用索引,但是这里不是使用索引去查数据，而且是去查索引键值。
-- 即先根据该索引去查其他索引的值，再根据查出来的索引值去查数据。
-- extra中显示 Using index 均表示该情况。
EXPLAIN SELECT * FROM test_idx t1 WHERE t1.id =
(SELECT id FROM test_idx2 WHERE t1.str_idx = str_idx LIMIT 1);
11、MySQL5.7，表关联时，关联字段字符集排序规则不一致会导致索引失效。
-- 当使用字符串类型索引进行关联或用于子查询时会报错：
-- 1267 - Illegal mix of collations (utf8mb4_german2_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
-- 翻译：操作“=”的排序规则（utf8mb4_german2_ci，隐式）和（utf8mb4_general_ci，隐式）的非法混合
-- 使用其他类型索引进行关联时，索引失效。
-- 使用其他类型索引进行子查询时，可以使用索引。
-- 这里就不做举例了，实际情况下出现的可能性不大。
多个独立索引
1、使用OR且第一个条件是范围查询，且返回值中不止包含条件索引列。
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1;
-- 部分情况下可以使用索引
-- 当表里没有数据时不使用索引
-- 当后面的条件查询的返回值超过总数据50％时不使用索引;少于总数据50％则使用索引
EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 2;
-- 由于一次查询中一张表只能使用一个索引，所以优化器是将该sql优化成UNION执行的。
-- 因此我们可以直接将OR语句改成UNION语句(如果肯定两个条件不会出现重复返回值，则可以使用UNION ALL，UNOIN在查询后还需要做一次去重操作，UNOIN ALL则不需要，可以进一步提高查询速度)。
EXPLAIN
SELECT * FROM test_idx WHERE id = 1
UNION ALL
SELECT * FROM test_idx WHERE normal_idx > 2;
2、 MySQL5.7，使用OR且存在条件是范围查询，且返回值中不止包含条件索引列。
-- 无法使用索引
EXPLAIN SELECT * FROM test_idx WHERE id > 1 OR normal_idx = 1;
EXPLAIN SELECT * FROM test_idx WHERE id = 1 OR normal_idx > 1;
3、组合索引
-- 创建测试表
CREATE TABLE test_idx4 (
id INT ( 11 ) PRIMARY KEY auto_increment,
col1 VARCHAR ( 11 ) DEFAULT NULL,
col2 VARCHAR ( 11 ) DEFAULT NULL,
col3 VARCHAR ( 11 ) DEFAULT NULL,
col4 VARCHAR ( 11 ) DEFAULT NULL,
KEY idx_col1_col2_col3( col1, col2, col3 ) USING BTREE
);
-- 插入测试数据
INSERT INTO test_idx4
VALUES
( 1, '1', '1', '1', '1' ),
( 2, '1', '1', '1', '1' ),
( 3, '1', '1', '1', '1' ),
( 4, '1', '1', '1', '1' );
-- 最左匹配原则：只要查询条件中带有组合索引最左边的列（此处即 col1） , 那么查询就会使用到索引。
-- 所以想让组合索引失效很简单，条件中不包含索引最左边的列（此处即 col1）, 则索引失效。
扩展：  
SELECT col2,col3 FROM test_idx4
SELECT col2,col3 FROM test_idx4 WHERE col2 = '1' AND col3='2'
以上两条SQL是可以使用到索引的，原理就是上面提到的索引覆盖，
虽然根据最左匹配原则是没法使用索引去快速检索数据的；但是因为该查询中所查询的列是col1,col2，而该索引只包含col1,col2,col3三个字段信息，而主键索引中包含所有字段信息，用该索引做全表扫描的效率更高，所以还是会使用到该索引！
-- 通过 force index(IDX_NAME) 强制指定索引
EXPLAIN SELECT * FROM test_idx force index (idx_notnull_idx) WHERE notnull_idx BETWEEN 1 AND 2;
来源：https://blog.csdn.net/qq_38688267/article/details/104900507
0
投稿猜你喜欢
- 如下所示：# u [32,30,200]# u_logits [400,32,30]q_j_400 = [] for j in range(
- 之前有写利用md5方式来做差异备份，但是这种md5方式来写存在以下问题：•md5sum获取有些软连接的MD5值存在问题 •不支持对空目录进行
单张人脸关键点检测定义可视化图像函数导入三维人脸关键点检测模型导入可视化函数和可视化样式读取图像将图像模型输入，获取预测结果BGR转RGB将- seek()方法在偏移设定该文件的当前位置。参数是可选的，默认为0，这意味着绝对的文件定位，它的值如果是1，这意味着寻求相对于当
- 一、什么是匿名函数？在Javascript定义一个函数一般有如下三种方式：函数关键字(function)语句：function f
- 本例子实现从hbase获取数据，并发送kafka。使用#!/usr/bin/env python#coding=utf-8import sy
- json_encode()如何转化一个对象？ 使用 json_encode() 将数组 array
- mysql 查看表空间主要是查看创建的库和系统自带的库大小 - SELECT table_schema,
- 在Python中，安装第三方模块，是通过setuptools这个工具完成的。Python有两个封装了setuptools的包管理工具：eas
- TF-IDFTF-IDF（Term Frequencey-Inverse Document Frequency）指词频-逆文档频率，它属于数
- 情况1：在一个目录下面只有文件，没有文件夹，这个时候可以使用os.listdir在我们的桌面上有一个file目录（文件夹），里面有三个文件f
- 人生苦短，我用python！为什么很多人喜欢用python，因为包多呀，各种调包。但是调包有的时候也调的闹心，因为安装包不是失败就是很慢，很
- 一、Pandas Series对象Pandas 是基于 NumPy 设计实现的 Python 数据分析库，Pandas 提供了大量的能让我们
- 引言将对象的状态信息转换为可以存储或传输的形式的过程叫作序列化类似地从序列化后的数据转换成相对应的对象叫作 反序列化本文介绍 Python
- 看到这个先思考，自己怎么输出他？为什么它有颜色？特殊符号去哪找？特殊符号在符号大全找 符号大全http://www.fhdq.net/任务1
- PyCharm是一种Python IDE，带有一整套可以帮助用户在使用Python语言开发时提高其效率的工具，比如调试、语法高亮、Proje
- 前言在很多网站中，基本上的都会有一个开头和一个结尾，在每一个网页中都会显示。相对于这种的来说，在Django中，最好的方法就是使用inclu
- 在Python中是通过一套命名体系来识别成约的访问范围的 class MyObjec(object): username = "d
- Django RBAC权限管理概述RBAC(Role-Based Access Control,基于角色的访问控制)，通过角色绑定权限，然后
- 一、引出问题假如有这两张表，它们中的课程可能价格不一样、周期不一样、等等...不一样...，现在有一张价格策略表，怎么就用一张表报保存它们之
