Java基础、中级、高级、架构面试资料

MySQL 的 in 查询到底走不走索引?

SQL herman 29174浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:xttblog2,发送下载链接帮助你免费下载!
本博客日IP超过2000,PV 3000 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:xttblog2,之前的微信号好友位已满,备注:返现
受密码保护的文章请关注“业余草”公众号,回复关键字“0”获得密码
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领
腾讯云】1核2G5M轻量应用服务器50元首年,高性价比,助您轻松上云

为什么写这篇文章呢?因为我不想让大家在错误下去!

昨天 CSDN 正在进行一个 2018 年的博客之星活动,入围了 200 名的博客专家!大家正在讨论说 2018 年博客之星页面有 bug 的问题时,有一位网友发了一张图片,一条 SQL 查询有问题。于是下面就有几个回答说 MySQL 查询 in 是不走索引的!

哇,我惊呆了。这种话都出来了!我想证明我是错的,于是我到网上搜索了非常多的文章,什么 MySQL 优化实战,MySQL 军规 36 条,30条SQL优化军规,SQL语句优化原则,mysql语句优化建议,数据库查询优化方法总结等等有非常多的文章在描述,MySQL 中 in 查询不走索引。

这些文章当中有些是 2018 年才发出来的,完全属于误导读者。今天我们一起来看看 MySQL 的 in 查询到底走不走索引?

首先说一下,我的测试环境:

MySQL 版本:SELECT VERSION(); — 5.6.28-cdb2016-log
存储引擎:innodb
一些基本配置,我就不贴了,对于我们这个问题影响的不大。

使用 desc xttblog; 名称查看一下表结构。我就不贴图了,直接给大家贴上建表语句。

CREATE TABLE `xttblog` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `page` bigint(20) DEFAULT NULL,
  `status` tinyint(3) unsigned NOT NULL COMMENT '状态:0 正常,1 冻结,2 删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4

然后循环插入 100 万条测试数据。

DELIMITER ;; 
    CREATE PROCEDURE test_insert()
    BEGIN 
        declare i int;
        set i = 1 ;
        WHILE (i < 1000000) DO
            INSERT INTO xttblog(`name`,title,`page`,`status`) 
                VALUES(CONCAT('xttblog_', i),
                    CONCAT('xttblog_', i),
                i, (SELECT FLOOR(RAND() * 2)));
            set i = i + 1;
        END WHILE;
        commit; 
END;; 
CALL test_insert();

首先我们看一下无索引的情况,xttblog 表中的 name 值已经非常的散了。查询时间 0.55 秒左右。

SELECT * FROM xttblog WHERE `name` IN
('xttblog_1','xttblog_100','xttblog_1000','xttblog_10000');

我们在查一下 xttblog 表中的 status in 的情况,耗时 17 秒左右。

SELECT * FROM xttblog WHERE `status` IN(0,2);

通过 EXPLAIN 对上面两条查询语句进行分析(不懂 EXPLAIN 的可以看我的这篇文章《MySQL 性能优化神器 Explain 使用教程》),它们都没有走索引。name 的值因为更分散,查询速度更快。

再来看看有索引的情况下,in 的执行效率。

SELECT * FROM xttblog WHERE id IN(1,100,1000,10000);

我们的主键 id 是一个索引,上面语句的查询耗时 0.02 秒左右。

为了比较,我们再给 xttblog 表的 name 字段加上索引。

CREATE INDEX index_xttblog_name ON xttblog(`name`(255));

然后执行下面的查询:

SELECT * FROM xttblog WHERE `name` 
IN('xttblog_1','xttblog_100','xttblog_1000','xttblog_10000');

当 name 字段添加了索引后,in 查询耗时 0.02 毫秒左右。

EXPLAIN 执行结果

EXPLAIN 分析发现它们的 type 都是 range,表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 操作中。而且 possible_keys 的值也表明了分别使用主键索引和 index_xttblog_name 索引。并且在 in 索引的情况下,rows 的值,大家都可以看到是 4。以上就可以证明了,在 MySQL 5.6 版本中,in 在有索引的情况下,会走索引进行查询。

虽然 Extra 的值是 Using where,它表示优化器需要通过索引回表查询数据。使用了 WHERE 从句来限制哪些行将与下一张表匹配或者是返回给用户。Extra 列出现 Using where 表示 MySQL 服务器将存储引擎返回服务层以后再应用 WHERE 条件过滤。

现在来看,在回答题目中的问题。MySQL 的 in 查询在 5.5 以上的版本中存储引擎都是 innodb 的,正常情况下会走索引的!至于 MyISAM 的情况大家自己去研究,或者我以后抽时间了来写!

如果是 5.5 之前的版本确实不会走索引的,在 5.5 之后的版本,MySQL 做了优化。MySQL 在 2010 年发布 5.5 版本中,优化器对 in 操作符可以自动完成优化,针对建立了索引的列可以使用索引,没有索引的列还是会走全表扫描。

比如,5.5 之前的版本(以下都是 5.5 以前的版本)。select * from a where id in (select id from b); 这条 sql 语句它的执行计划其实并不是先查询出 b 表的所有 id,然后再与 a 表的 id 进行比较。mysql 会把 in 子查询转换成 exists 相关子查询,所以它实际等同于这条 sql 语句:select * from a where exists(select * from b where b.id=a.id);

而 exists 相关子查询的执行原理是:循环取出 a 表的每一条记录与 b 表进行比较,比较的条件是 a.id=b.id。看 a 表的每条记录的 id 是否在 b 表存在,如果存在就行返回 a 表的这条记录。

关于 exists 的问题,我们以后抽时间再说。

互联网上的信息太过广泛,但这不应该成为我们掉以轻心的借口,抱着存疑求是的精神进行甄别,任何信息只有在我们实践验证后方可全信,用在生产开发上的知识,不容有失!

尽信书,不如无书!

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号:xttblog2。备注:“1”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!

本文原文出处:业余草: » MySQL 的 in 查询到底走不走索引?