sql会导致索引失效吗

发布时间:2021-07-30 11:22:48 作者:小新
来源:亿速云 阅读:162

小编给大家分享一下sql会导致索引失效吗,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!

前言

网上经常能看到一些文章总结在 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 一直在不断优化升级,一些老旧的知识点很容易就会不再适用了。

以上是“sql会导致索引失效吗”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!

推荐阅读:
  1. MySql学习笔记(九):索引失效
  2. ORACLE索引失效,更新统计信息

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sql

上一篇:如何从Oracle数据库中读取数据自动生成INSERT语句

下一篇:如何监听mysql表内容变化

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》