• 售前

  • 售后

热门帖子
入门百科

MySql子查询IN的实验和优化的实现

[复制链接]
123456825 显示全部楼层 发表于 2021-8-14 14:44:55 |阅读模式 打印 上一主题 下一主题
目次


  • IN为什么慢?
  • IN和EXISTS哪个快?
  • 如何提高服从?
  • MySQL5.6对子查询的优化?

    • SEMI JOIN战略
    • Duplicate Weedout优化
    • Materialization优化
    • FirstMacth优化
    • LooseScan优化
    • SEMI JOIN变量

  • 参考

IN为什么慢?

在应用程序中使用子查询后,SQL语句的查询性能变得非常糟糕。比方:
  1. SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM driver where _create_date > '2016-07-25 00:00:00');
复制代码
独立子查询返回了符合条件的driver_id,这个标题是办理了,但是所用的时间须要6秒,可以通过EXPLAIN检察SQL语句的执行操持:

可以看到上面的SQL语句变成了相关子查询,通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令,可以看到如下结果:
复制代码 代码如下:select `northwind`.`driver`.`driver_id` AS `driver_id` from `northwind`.`driver` where <in_optimizer>(`northwind`.`driver`.`driver_id`,<exists>(select 1 from `northwind`.`driver` where ((`northwind`.`driver`.`_create_date` > '2016-07-25 00:00:00') and (<cache>(`northwind`.`driver`.`driver_id`) = `northwind`.`driver`.`driver_id`))))
可以看出MySql优化器直接把IN子句转换成了EXISTS的相关子查询。下面这条相关IN子查询:
  1. SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM user where user.uid = driver.driver_id);
复制代码
检察SQL语句的执行操持:

就是相关子查询,通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令,看到如下结果:
复制代码 代码如下:select `northwind`.`driver`.`driver_id` AS `driver_id` from `northwind`.`driver` where <in_optimizer>(`northwind`.`driver`.`driver_id`,<exists>(select 1 from `northwind`.`user` where ((`northwind`.`user`.`uid` = `northwind`.`driver`.`driver_id`) and (<cache>(`northwind`.`driver`.`driver_id`) = `northwind`.`driver`.`driver_id`))))
可以看出无论是独立子查询还是相关子查询,MySql 5.5之前的优化器都是将IN转换成EXISTS语句。假如子查询和外部查询分别返回M和N行,那么该子查询被扫描为O(N+N*M),而不是O(N+M)。这也就是为什么IN慢的缘故因由。

IN和EXISTS哪个快?

网上百度到许多认为IN和EXISTS服从一样是错误的文章。
假如查询的两个表巨细相称,那么用in和exists差异不大。
假如两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
比方:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B) 服从低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) 服从高,用到了B表上cc列的索引。

相反的

2:
select * from B where cc in (select cc from A) 服从高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc) 服从低,用到了A表上cc列的索引。
总结上面的形貌,个人认为其主要的缘故因由在于对索引的使用。任何环境下,只要是大表的索引被使用,就可以使服从提高。
但是在编辑本文的时间,多次测试,却没能得到上面所总结的结果。下面是测试SQL语句,先是外表为大表,内表为小表。(示例一)
  1. SELECT count(driver_id) FROM driver where driver_id in (SELECT uid FROM user);
  2. SELECT count(driver_id) FROM driver where exists (SELECT 1 FROM user where uid = driver.driver_id);
复制代码
执行结果是:

再是外表是小表,内表是大表。(示例二)
  1. select count(uid) from user where uid in (SELECT driver_id FROM driver);
  2. select count(uid) from user where exists (SELECT 1 FROM driver where driver.driver_id = user.uid);
复制代码
执行结果是:

可以发现IN和EXISTS的执行服从,在任何环境下都正好是雷同的。基于此,我们继承检察示例一两条SQL语句的执行操持,如下:

可以看到IN和EXISTS的执行操持是一样的,对此得出的结论两者的执行服从应该是一样的。
《MySql技能内幕:SQL编程》:书中形貌简直实有许多DBA认为EXISTS比IN的执行服从更高,大概是其时优化器还不是很稳定和充足良好,但是现在绝大数的环境下,IN和EXISTS都具有雷同的执行操持。

如何提高服从?

上面示例二中的SQL语句执行时间约8秒,由于存在M*N的缘故因由造成慢查询,但是还是可以举行优化,留意到慢的缘故因由就是内部每次与外部比力时,都须要遍历一次表使用,可以接纳另外一个方法,在嵌套一层子查询,制止多次遍历使用,语句如下:
  1. SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);
复制代码
执行结果如图:

可以发现优化减少了6s多的执行时间,下面是SQL的执行操持:

同样的还是相关子查询,但是减少了内部遍历查询的使用。以是可以通过预查询来减少遍历使用,而提高服从。
其着实现实编程中,许多开发人员选择不使用毗连表查询,而是自己先把数据从一张表中取出,再到另一张表中执行WHEREIN使用,这原理和上面SQL语句实现的是一样的。

MySQL5.6对子查询的优化?


SEMI JOIN战略

优化器会识别出须要子查询的IN语句以便从地区表返回每个地区键的一个实例。这就导致了MySQL会以半毗连的方式执行SELECT语句,以是全局表中每个地区只会有一个实例与记录相匹配。
半毗连和通例毗连之间存在两个非常紧张的区别:
       
  • 在半毗连中,内表不会导致重复的结果。   
  • 此使用不会有内表中的字段添加到结果中去。
因此,半毗连的结果常常是来自外表记录的一个子集。从有效性上看,半毗连的优化在于有效的消除了来自内表的重复项,MySQL应用了四个差异的半毗连执行战略用往复重。
Table Pullout优化
  1. Convert the subquery to a join, or use table pullout and run the query as an inner join between subquery tables and outer tables. Table pullout pulls a table out from the subquery to the outer query.将子查询转变为一个连接,或是利用table pullout并将查询作为子查询表和外表之间的一个内连接来执行。Table pullout会为外部查询从子查询抽取出一个表。
复制代码
有些时间,一个子查询可以被重写为JOIN,比方:
  1. SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3);
复制代码
假如知道OrderID是唯一的,即主键大概唯一索引,那么SQL语句会被重写为Join情势。
  1. SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Employees.EmployeeID and Employees.EmployeeID > 3;
复制代码
Table pullout的作用就是根据唯一索引将子查询重写为JOIN语句,在MySql 5.5中,上述的SQL语句执行操持:

假如通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令,可以看到如下结果:
复制代码 代码如下:select `northwind`.`Orders`.`OrderID` AS `OrderID` from `northwind`.`Orders` where <in_optimizer>(`northwind`.`Orders`.`EmployeeID`,<exists>(<primary_index_lookup>(<cache>(`northwind`.`Orders`.`EmployeeID`) in Employees on PRIMARY where ((`northwind`.`Employees`.`EmployeeID` > 3) and (<cache>(`northwind`.`Orders`.`EmployeeID`) = `northwind`.`Employees`.`EmployeeID`)))))
正是上面说的in为什么慢?
在MySql 5.6中,优化器会对SQL语句重写,得到的执行操持:

在MySql 5.6中,优化器没有将独立子查询重写为相关子查询,通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令,得到优化器的执行方式为:
复制代码 代码如下:/* select#1 */ select `northwind`.`orders`.`OrderID` AS `OrderID` from `northwind`.`employees` join `northwind`.`orders` where ((`northwind`.`orders`.`EmployeeID` = `northwind`.`employees`.`EmployeeID`) and (`northwind`.`employees`.`EmployeeID` > 3))
很显然,优化器将上述子查询重写为JOIN语句,这就是Table Pullout优化。

Duplicate Weedout优化

Run the semi-join as if it was a join and remove duplicate records using a temporary table.执行半毗连,就犹如它是一个毗连并使用临时表移除了重复的记录。
上面内部表查出的列是唯一的,因此优化器会将子查询重写为JOIN语句,以提高SQL执行的服从。Duplicate Weedout优化是指外部查询条件是列是唯一的,MySql优化器会先将子查询查出的结果举行去重。比如下面这条SQL语句:
  1. SELECT ContactName FROM Customers where CustomerID in (select CustomerID from Orders where OrderID > 10000 and Customers.Country = Orders.ShipCountry);
复制代码
由于CustomerID是主键,以是应该对子查询得到的结果举行去重。在MySql 5.6中的执行操持:

Extra选项提示的Start temporary表示创建一张去重的临时表,End temporary表示删除该临时表。而通过EXPLAIN EXTENDED 和 SHOW WARNINGS命令,得到优化器的执行方式为:
复制代码 代码如下:/* select#1 */ select `northwind`.`customers`.`ContactName` AS `ContactName` from `northwind`.`customers` semi join (`northwind`.`orders`) where ((`northwind`.`customers`.`CustomerID` = `northwind`.`orders`.`CustomerID`) and (`northwind`.`customers`.`Country` = `northwind`.`orders`.`ShipCountry`) and (`northwind`.`orders`.`OrderID` > 10000))
与Table Pullout优化差异的是,体现的是semi join而不是join,此中缘故因由在于多了一些去重的工作,对于上述的执行操持,其扫描成本约为830+830*1=1660次。
而在MySql 5.5中的执行操持为:

可以看到,在MySql 5.5中还是将语句转化为相关子查询,扫描成本约为93+93*9=930次。
我们可以看到MySql 5.6优化以后比5.5的扫描成本反而大,实在这只是在两张表较小的的环境下的结果,假如表很大,优化的结果会非常显着。

Materialization优化
  1. Materialize the subquery into a temporary table with an index and use the temporary table to perform a join. The index is used to remove duplicates. The index might also be used later for lookups when joining the temporary table with the outer tables; if not, the table is scanned.
复制代码
上面的子查询是相关子查询,假如子查询是独立子查询,则优化器可以选择将独立子查询产生的结果添补到单独一张物化临时表中,如图:

根据JOIN的顺序,Materialization优化可分为:
       
  • Materialization scan:JOIN是将物化临时表和表举行关联。   
  • Materialization lookup:JOIN是将表和物化临时表举行关联。
下面的子查询可以使用Materialization来举行优化:
  1. SELECT OrderID FROM Orders where OrderID in (select OrderID from `Order Details` where UnitPrice < 50 );
复制代码
SQL语句的执行操持:

可以看到,在举行JOIN时(也就是id为1的步调),先扫描的表是Orders,然后是subquery2,因此这是Materialization lookup的优化。对于下面的SQL:
  1. select * FROM driver where driver_id in (select uid from user);
复制代码
SQL语句的执行操持:

先扫描的是subquery2,再是driver表,这就是Materialization scan的优化。

FirstMacth优化

When scanning the inner tables for row combinations and there are multiple instances of a given value group, choose one rather than returning them all. This "shortcuts" scanning and eliminates production of unnecessary rows.为了对记录举行合并而在扫描内表,而且对于给定值群组有多个实例时,选择其一而不是将它们全部返回。这为表扫描提供了一个早期退出机制而且还消除了不须要记录的产生。
半毗连的最先匹配(FirstMatch)战略执行子查询的方式与MySQL稍早版本中的IN-TO-EXISTS是非常相似的。对于外表中的每条匹配记录,MySQL都会在内表中举行匹配检查。当发现存在匹配时,它会从外表返回记录。只有在未发现匹配的环境下,引擎才会回退去扫描整个内表。

LooseScan优化

Scan a subquery table using an index that enables a single value to be chosen from each subquery's value group.使用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。

SEMI JOIN变量

Each of these strategies except Duplicate Weedout can be enabled or disabled using the optimizer_switch system variable. The semijoin flag controls whether semi-joins are used. If it is set to on, the firstmatch, loosescan, and materialization flags enable finer control over the permitted semi-join strategies. These flags are on by default.除Duplicate Weedout之外的每个战略可以用变量控制开关,semijoin控制semi-joins优化是否开启,假如设置开启,其他的战略也有独立的变量控制。所有的变量在5.6默认是打开的。
  1. mysql> SELECT @@optimizer_switch\G;
  2. *************************** 1. row ***************************
  3. @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
  4. 1 row in set (0.00 sec)
复制代码
EXPLAIN检察战略
       
  • Semi-joined tables show up in the outer select. EXPLAIN EXTENDED plus SHOW WARNINGS shows the rewritten query, which displays the semi-join structure. From this you can get an idea about which tables were pulled out of the semi-join. If a subquery was converted to a semi-join, you will see that the subquery predicate is gone and its tables and WHERE clause were merged into the outer query join list and WHERE clause.   
  • Temporary table use for Duplicate Weedout is indicated by Start temporary and End temporary in the Extra column. Tables that were not pulled out and are in the range of EXPLAIN output rows covered by Start temporary and End temporary will have their rowid in the temporary table.   
  • FirstMatch(tbl_name) in the Extra column(列) indicates join shortcutting.   
  • LooseScan(m..n) in the Extra column indicates use of the LooseScan strategy. m and n are key part numbers.   
  • As of MySQL 5.6.7, temporary table use for materialization is indicated by rows with a select_type value of MATERIALIZED and rows with a table value of .   
  • Before MySQL 5.6.7, temporary table use for materialization is indicated in the Extra column by Materialize if a single table is used, or by Start materialize and End materialize if multiple tables are used. If Scan is present, no temporary table index is used for table reads. Otherwise, an index lookup is used.
上面介绍中FirstMacth优化、LooseScan优化的具体结果没有很好的例子去体现出来。有时机可以交换学习。

参考

《MySql技能内幕:SQL编程》
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
http://tech.it168.com/a2013/0506/1479/000001479749.shtml
到此这篇关于MySql子查询IN的执行和优化的实现的文章就介绍到这了,更多相关MySql子查询IN 内容请搜刮草根技术分享从前的文章或继承欣赏下面的相关文章希望大家以后多多支持草根技术分享!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作