关于Mysql5.7及8.0版本索引失效情况汇总_Mysql

来源:脚本之家  责任编辑:小易  
目录
一个独立索引多个独立索引总结

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三个字段信息,而主键索引中包含所有字段信息,用该索引做全表扫描的效率更高,所以还是会使用到该索引!

总结

上述所有可能用到可能没用到索引的情况,并不是一定的!导致索引失效的阈值也不一定100%准确,毕竟不同数据类型、不同数据量的情况下,MySQL的优化器的选择可能不同。但可以肯定的是,同样一条语句,可能由于筛选率等原因导致索引失效。

所有简单查询(执行计划中 select_type = simple),只要where条件中有索引列(无论什么条件),且返回值中只包含该索引列(和主键),都会用到索引。根据执行计划中的extra可以区分索引的用途:

1、extra = Using index,表示索引覆盖。

2、extra = Using index, Using where,表示存在回表操作。

拓展:

为什么只要返回值只包含索引和主键就会用到索引?

众所周知,InnoDB保存数据是通过B+树结构存储的。且只有主键索引所在的B+树的叶子节点会保存实际数据,其他节点只保存主键值,这种数据与索引在一起的索引我们称之为聚簇索引。

二级索引(非主键索引)的所有节点除了保存索引列的值外还会保存主键的值。

所以当我们通过二级索引查询数据时,第一步先通过二级索引查询到对应的主键值;再通过主键值到主键索引中查询对应的实际数据,这个过程我们称之为回表。

而回表操作是随机IO,所以性能较差,当需要回表的数据量比较大时,优化器可能就会选择不走索引,直接全表扫描,因为走全表是顺序IO,指不定走全表比走索引还快。(这也解释了为什么同样的SQL,表数据不同查询策略也不同)

其中一个特殊情况是当我们的查询只涉及到索引列和主键的时候,我们就不需要再回表查询实际数据了,因为二级索引中保存了主键和索引列的数据,这个时候就肯定会走索引了。

在复制其他地方提供的sql建表脚本时,注意其字符集和排序规则是否跟自己数据库默认的一致,否则可能出现索引失效的问题。

不同版本不同情况下,索引的使用情况不一致。上文提到的可能使用可能不使用的情况是由MySQL的优化器决定的,可能还会有其他情况下优化器也不使用索引,此时我们可以强制指定需要使用的索引:

-- 通过 force index(IDX_NAME) 强制指定索引
EXPLAIN SELECT * FROM test_idx force index (idx_notnull_idx) WHERE notnull_idx BETWEEN 1 AND 2;

以上为个人经验,希望能给大家一个参考,也希望大家多多支持真格学网。

您可能感兴趣的文章:MySQL细数发生索引失效的情况分享15个Mysql索引失效的场景MySQL索引失效原理mysql索引失效的几种情况分析

  • 本文相关:
  • 规范化的sql数据修改语句总结
  • mysql explain语句的使用示例
  • 如何通过配置自动实现valuelist中hql语句的整型参数转换
  • mysql数据库优化推荐的编译安装参数小结
  • 浅析mysql的注入安全问题
  • 浅谈mysql?update会锁定哪些范围的数据
  • 一篇文章带你了解sql之case when用法详解
  • 关于django连接mysql数据库并进行数据库的创建的问题
  • mysql如何按照中文排序解决方案
  • mysql多版本并发控制mvcc详解
  • mysql5.7的表空间是什么意思?
  • mysql数据库5.7怎么安装图解
  • mysql数据库5.7怎么安装图解
  • 如何配置mysql5.7安装版
  • MySQL 5.7 版本的安装及简单使用(图文教程)
  • Win10下免安装版MySQL5.7的安装和配置教程详解
  • 电脑已经装了mysql8.0的情况下安装mysql5.7
  • 如何安装mysql5.7
  • mysql5.7 安装失败tryagain
  • mysql5.7及mysql 8.0版本修改root密码的方法小结
  • RedHat6.5安装MySQL5.7教程详解
  • Mysql5.7忘记root密码怎么办(简单且有效方法)
  • mysql5.x升级到mysql5.7后导入之前数据库date出错的快速解决...
  • mysql5.7版本root密码登录问题的解决方法
  • mysql5.7安装及配置教程
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql细数发生索引失效的情况分享15个mysql索引失效的场景mysql索引失效原理mysql索引失效的几种情况分析规范化的sql数据修改语句总结mysql explain语句的使用示例如何通过配置自动实现valuelist中hql语句的整型参数转换mysql数据库优化推荐的编译安装参数小结浅析mysql的注入安全问题浅谈mysql?update会锁定哪些范围的数据一篇文章带你了解sql之case when用法详解关于django连接mysql数据库并进行数据库的创建的问题mysql如何按照中文排序解决方案mysql多版本并发控制mvcc详解mysql安装图解 mysql图文安装教程(详细说明)can""""t connect to mysql servwindows下mysql5.6版本安装及配置过程附有截图和mysql字符串截取函数substring的用法说明mysql提示:the server quit withoumysql之timestamp(时间戳)用法详解mysql创建用户与授权方法mysql——修改root密码的4种方法(以windows为mysql查看版本号的几种方式mysql日期数据类型、时间类型使用总结linux下mysql乱码问题的解决方案mysql 5.7.14 安装配置简单教程linux下如何实现mysql定时任务一文带你了解mysql主从同步原理mysql 查询某个字段不重复的所有记录mysql 用 limit 为什么会影响性能浅谈mysql的b树索引与索引优化小结详解mysql中的死锁情况以及对死锁的处理方法mysql字符集设置的方法详解(终端的字符集)mysql设置更改root密码、mysql服务器的连接、mysql常用命令的
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved