一文带你了解MySQL是如何优化in子查询的,其实非常简单

发表于:2023-3-02 08:45

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:程序员拾山    来源:今日头条

  对于很多的开发小伙伴来说,在MySQL中进行in子查询是一个非常常见的操作。
  虽然也有很多人说,尽量少用in子查询,in的数量过多会影响查询性能。
  但其实MySQL做了不少的优化手段来保证in子查询的性能,大家也能在实际的业务中感受到in子查询的速度也没那么慢。
  那今天就带大家了解一下,MySQL到底是怎么来优化in子查询的。
  普通in子查询
  首先,我们看一下MySQL是如何执行一个普通的in子查询的。
  以一个简单的子查询为例:
  select  * from table1 where key1 in ('bb','ff','gg');
  对于这个子查询画了一个简单的查询图,不同颜色代表不同的数据页。
  在这个图里,最上层的是根节点,中间的是非叶子节点,最下面的是叶子节点。
  对于一个普通的二级索引来说,叶子节点存储的是索引key和主键id,这些基础知识就不详细展开说了。
  需要注意的是,二级索引在叶子节点中是按照key的顺序从小到大排序的,但是对应的主键id可不一定。
  可能与大家想象的不同,MySQL在执行in子查询时,会把in语句中的条件当做一个个的区间,比如:
  ['bb','bb'],['ff','ff'],['gg','gg']
  然后MySQL在二级索引树上,会先查询['bb','bb']这个区间,比如首先查询到第一个数据页中符合条件的第一条数据(bb,2),获取到主键id=2之后,去聚簇索引回表查询所需的数据(因为我们使用的select *,需要获取到所有的列值)。
  然后查询第一个数据页中符合条件的第二条数据(bb,5),获取到主键id=5之后,去聚簇索引回表查询所需的数据,
  然后查询第二个数据页中符合条件的第三条数据(ff,6),
  不断的重复上面的动作。。。。
  最后获取到一个结果集,返回到Server,再由Server返回到客户端。
  看到这里大家是否可以感觉到,这样查询数据也太麻烦了,特别是当in子查询的条件越来越多时,如何保证性能呢?
  下面,我们一起来看一下,MySQL是如何优化in子查询的。
  物化表
  首先,为了演示我们建两张表table1和table2,并建立两个二级索引idx_c1和idx_c2。
  CREATE TABLE table1  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `c1` int(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_c1`(`c1`) USING BTREE
  ) ENGINE = InnoDB
  CREATE TABLE table2  (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `c2` int(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_c2`(`c2`) USING BTREE
  ) ENGINE = InnoDB
  下面以一个简单的子查询为例:在table2表中查询t2.c2=3的id,并作为table1表c1的查询条件。
  SELECT
    * 
  FROM
    table1 t1 
  WHERE
    t1.c1 IN ( SELECT id FROM table2 t2 WHERE t2.c2 = 3 );
  对于一个这样普通的子查询来说,MySQL使用了一种叫做物化表的方式来提升性能。
  什么意思呢?
  就是将子查询的结果集去重后放入到一个临时表中,临时表的列就是子查询的结果集中的列。
  去重的目的是为了让临时表尽可能的精简,因为在临时表中重复的列并没有什么意义。
  当结果集比较小时,MySQL会为临时表使用memory引擎,并且为临时表中的列建立哈希索引。哈希索引的查询时间复杂度是O(1),查询速度是非常快的。
  但是如果结果集比较大时,MySQL就会将临时表定义为InnoDB类型表,并且建立B+树索引,就像一个普通的表一样使用。
  话说回来,将子查询转换为临时表以后,其实查询就变成了两张表的连接查询,也就是两个表的内连接。
  一旦转换为内连接就好办了,经典的“小表驱动大表”的优化准则就可以派上用场了。
  我们看一下MySQL优化器对上面的SQL优化后的结果:
  SELECT
    `t1`.`id` AS `id`,
    `t1`.`c1` AS `c1` 
  FROM
    `table2` `t2`
    JOIN `table1` `t1` 
  WHERE
    ( ( `t1`.`c1` = `t2`.`id` ) AND ( `t2`.`c2` = 3 ) )
  可以看到,MySQL将其转换为了内连接,并且以table2为驱动表,table1为被驱动表的方式进行了查询。
  由于c1和c2列上都有索引,那么此时这个sql的执行速度还是相当可以的。
  半连接
  虽然通过物化表的方式,MySQL将子查询转换为了连接查询,但是创建临时表的成本也是有的。
  那可不可以再优化一步,将创建临时表的成本也给优化掉呢?
  在某些情况下,确实是可以的。
  在上文我们提到,MySQL会将子查询的结果集去重后,放入一个临时表中。
  那大家是否意识到,这个临时表中的记录都是唯一的,换句话说,就是一个唯一索引的列。
  那么当我们的子查询语句的结果集也类似于一个唯一索引集时,MySQL就不去创建临时表了,而是直接尝试将sql改写成内连接。
  半连接的优化还是比较复杂的,要求条件相对也苛刻一点,这里就不再详细的说了,感兴趣的朋友可以去深入学习一下。
  最后
  无论MySQL采用了哪种优化方法,只要知道了其实现的大致原理,对于使用者来说,就有了对应的优化思路。
  特别建议大家写完SQL以后,习惯性的使用explain分析一下是否命中了索引,扫描的行数是否过多。
  只有不断的实操,优化SQL的能力才会不断提升。
  本文内容不用于商业目的,如涉及知识产权问题,请权利人联系51Testing小编(021-64471599-8017),我们将立即处理
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号