这种sql写法真的会导致索引失效吗
作者:谭小谭啊 发布时间:2024-01-18 18:31:47
前言
网上经常能看到一些文章总结在 mysql 中不能命中索引的各种情况,其中有一种说法就是指使用了 or 的语句都不能命中索引。
这种说法其实是不够正确的,正确的结论应该是,从 mysql5.0 后,如果在 or 连接的字段上都有独立的索引的话,是可以命中索引的,这里就是用到了 index_merge 特性。
在 mysql5.0 版本以前一条 sql 只能选择使用一个索引,而且如果 sql 中使用了 or 关键字,那么已有的索引就会失效,会走全表扫描。因为无论走哪个索引,mysql 都不能一次性查找出符合条件的数据,所以只能放弃索引。
mysql 也是一直在不断升级更新,所以在 mysql5.0 版本后,增加了 index_merge 索引合并这个特性,也因此支持了一条 sql 使用多个索引。
index_merge 核心思想就是先分别使用单个索引查出满足要求的数据,然后再将这些数据合并到一起返回。
我们可以看一个的例子。
这里依然沿用我们前面文章中创建的表和测试数据,表中插入了 10 w 条测试数据,表结构如下。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
我们先来给 a 字段添加一个索引,然后执行一条带 or 的查询语句看看。
mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | a_index | NULL | NULL | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
因为字段 b 上没有索引,mysql 认为走全表扫描代价更低一些,因为可以免去回表过程。
那么我们给 b 字段也加上索引试试,然后再执行刚刚那条 sql 。
mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select a from t where a=100 or b=6000;
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | t | index_merge | a_index,b_index | a_index,b_index | 5,5 | NULL | 2 | Using union(a_index,b_index); Using where |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+
1 row in set (0.00 sec)
这回可以看到 mysql 同时使用了 a、b 两个索引,并且看到 type 字段的值为 index_merge。
接下来再来看另一条 sql,看看结果又是怎样的。
mysql> explain select a from t where a>100 or b>6000;
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | a_index,b_index | NULL | NULL | NULL | 100332 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
这条 sql 仅仅是把等号改成了大于号,也就是说返回的结果集是一个区间集,mysql 在这里又放弃了索引,走的全表扫描,不过有看文章说在 mysql5.7 版本后优化了这个问题,即在区间查询中也支持使用 index_merge,我的版本是 5.6 ,暂未验证这个优化,有兴趣的可以去验证下。
其实在 mysql 中很多东西都是不绝对的,对于同一条 sql 不同 mysql 版本的内部处理方式有可能是不太一样的,同时也可以看到 mysql 一直在不断优化升级,一些老旧的知识点很容易就会不再适用了。
希望文章对你有帮助,欢迎关注,点个赞是对我最好的支持,感谢。
另外,关于 mysql 的底层数据结构,大家可以参考我前面写的其他文章,对你理解这篇文章或许有帮助。
来源:https://juejin.im/post/5d29cc206fb9a07ece67fd74
猜你喜欢
- 跨数据库服务器,跨实例访问是比较常见的一种访问方式,在Oracle中可以通过DB LINK的方式来实现。对于MySQL而言,有一个FEDER
- 前言本文的文字及图片来源于网络,仅供学习、交流使用,不具有任何商业用途,如有问题请及时联系我们以作处理。以下文章来源于Python进击者 ,
- 我们可用正规表达式来寻找并替换URL和邮件地址为活动的超级链接。用到的主要函数就是InsertHyperlinks(inText),语法为:
- “你不必严格遵守这些原则,违背它们也不会被处以宗教刑罚。但你应当把这些原则看成警铃,若违背了其中的一条,那么警铃就会响起
- 1 写在前面1. 本文介绍的是基于TensorFlow tf.estimator接口的深度学习网络,而非TensorFlow&nb
- 目的:设计一个应用GUI用于对比两个Excel文件思路1.参数同一个excel文件两个sheet页其中一个ODS(老数据),一个DWH(新数
- 今天在论坛上看到一个问题,如下: 解决这个问题,Insus.NET写了一个函数,可以方便以后的扩展,如果数值出现TB或是或更高时,
- 引用计数Python 语言默认采用的垃圾收集机制是『引用计数法 Reference Counting』,该算法最早 George E. Co
- 导语"盘子里最后一块肉给你 一 冰激凌的第一口给你 一手机最后的10%电量给你!"哈喽大家好!我是木木子,我要开始给大家
- JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,它使得人们很容易的进行阅读和编写。同时也方便了
- 除了使用 sys.exc_info() 方法获取更多的异常信息之外,还可以使用 traceback 模块,该模块可以用来查看异常的传播轨迹,
- 由于微博的网页端有反爬虫,需要登录,所以我们换个思路,曲线救国。我们找到微博在浏览器上面用于手机端的调试的APL,如何找到呢?我这边直接附上
- 练手项目,先上图先实现一个简单的串口工具,为之后的上位机做准备代码如下:github 下载地址pyserial_demo.pyimport
- 1、下载gliffy-confluence-plugin-9.1.2.obr2、解压后找到GliffyLicenseManager.clas
- 今天分享一个我自己写的实用脚本,主要是将.xml文件进行批量的修改首先,声明我并不是很了解.xml的相关知识,所以今天主要是以我遇到的问题来
- DTD实际上可以看作一个或多个XML文件的模板,这些XML文件中的元素、元素的属性、元素的排列方式/顺序、元素能够包含的内容等,都必须符合D
- 从过往MySQL数据库生产环境的维护工作中,总结的一些小经验和知识,未必有多深奥,但是对我们消除隐患,确保MySQL数据库生产环境四个9的作
- Doug Bowman,Google的Visual Design Lead离职了,一封带有感 * 彩的离职信惹发了大家不少的讨论。甚至还有人用
- 本文实例为大家分享了python手写均值滤波的具体代码,供大家参考,具体内容如下原理与卷积类似,设置一个n*n的滤波模板,滤波模板内的值累加
- 一个完整的数据挖掘模型,最后都要进行模型评估,对于二分类来说,AUC,ROC这两个指标用到最多,所以 利用sklearn里面相应的函数进行模