文档章节

结合explain extended浅析使用mysql in 的效率

 小小人故事
发布于 2015/12/05 21:04
字数 1200
阅读 189
收藏 3

用explain extended查看执行计划会比explain多一列 filtered。
filtered列给出了一个百分比的值,这个百分比值和rows列的值一起使用,可以估计出那些将要和explain中的前一个表进行连接的行的数目。
前一个表就是指explain 的 id列的值比当前表的id小的表。

1. mysql sql查询中,in是会走索引的:

点击(此处)折叠或打开

  1. mysql> explain extended select *,sleep(0.2) from testinfo where id in (1232,232,324,2342,23); 

  2. +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------------+

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

  4. +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------------+

  5. | 1 | SIMPLE | testinfo | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where |

  6. +----+-------------+----------+-------+---------------+---------+---------+------+------+----------+-------------+

  7. 1 row in set, 1 warning (0.00 sec)


  8. mysql> show warnings \G

  9. *************************** 1. row ***************************

  10.   Level: Note

  11.    Code: 1003

  12. Message: select `test`.`testinfo`.`id` AS `id`,`test`.`testinfo`.`idtest` AS `idtest`,`test`.`testinfo`.`nametest` AS `nametest`,`test`.`testinfo`.`author` AS`author`,`test`.`testinfo`.`typetest` AS `typetest`,sleep(0.2) AS `sleep(0.2)` from `test`.`testinfo` where (`test`.`testinfo`.`id` in (1232,232,324,2342,23))

  13. 1 row in set (0.00 sec)


  14. mysql> select *,sleep(0.2) from testinfo where id in (1232,232,324,2342,23);

  15. 5 rows in set (1.02 sec)


  16. Time: 130725 11:47:51

  17. User@Host: root[root] @ localhost []

  18. # Query_time: 1.017450 Lock_time: 0.000219 Rows_sent: 5 Rows_examined: 5

  19. SET timestamp=1374724071;

  20. select *,sleep(0.2) from testinfo where id in (1232,232,324,2342,23);

可见,id in (1232,232,324,2342,23是走了主键索引,而且效果很好,扫描5行就出结果了。




2.看看sql为:select count(*) from testinfo where id not in (select id from testinfo group by idtest);的效率

点击(此处)折叠或打开

  1. mysql> explain extended select count(*) from testinfo where id not in (select id from testinfo group by idtest);

  2. +----+--------------------+----------+-------+---------------+------------+---------+------+------+-----------+--------------------------+

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

  4. +----+--------------------+----------+-------+---------------+------------+---------+------+------+-----------+--------------------------+

  5. | 1 | PRIMARY | testinfo | index | NULL | key_idtest | 62 | NULL | 8761 | 100.00 | Using where; Using index |

  6. | 2 | DEPENDENT SUBQUERY | testinfo | index | NULL | key_idtest | 62 | NULL | 1 | 876100.00 | Using index |

  7. +----+--------------------+----------+-------+---------------+------------+---------+------+------+-----------+--------------------------+

  8. 2 rows in set, 1 warning (0.00 sec)

       表面上看都走了索引,但仔细发现filtered列为876100,这个值很大,直接影响到执行sql时扫描的行数。

  1. mysql> show warnings \G

  2. *************************** 1. row ***************************

  3.   Level: Note

  4.    Code: 1003

  5. Message: select count(0) AS `count(*)` from `test`.`testinfo` where (not(<in_optimizer>(`test`.`testinfo`.`id`,<exists>(select `test`.`testinfo`.`id` from `test`.`testinfo` group by `test`.`testinfo`.`idtest` having (<cache>(`test`.`testinfo`.`id`) = <ref_null_helper>(`test`.`testinfo`.`id`))))))

  6. 1 row in set (0.00 sec)

  7. 可见,经过mysql优化器后,in 给转换成exists的方式,下面实际执行一次sql花了36秒

  8. mysql> select count(*) from testinfo where id not in (select id from testinfo group by idtest);

  9. +----------+

  10. | count(*) |

  11. +----------+

  12. | 1059 |

  13. +----------+

  14. 1 row in set (36.79 sec)


  15. 根据上面的执行计划,估算大概的扫描的行数为:76755121

  16. mysql> select 8761*((876100*1)/100) 

  17.     -> ;

  18. +-----------------------+

  19. | 8761*((876100*1)/100) |

  20. +-----------------------+

  21. | 76755121.0000 |

  22. +-----------------------+

  23. 1 row in set (0.00 sec)


  24. 而实际执行扫描的行数为:50910026

  25. User@Host: root[root] @ localhost []

  26. # Query_time: 36.793302 Lock_time: 0.000227 Rows_sent: 1 Rows_examined: 50910026

  27. SET timestamp=1374723426;

  28. select count(*) from testinfo where id not in (select id from testinfo group by idtest);

从上面测试可知,in里面的子查询并非是先查出结果后再执行外层的查询。当in中子查询含有group by时,需注意是否会产生扫描的行数很大,sql执行效率很低。

3.将上面的sql变换一下:
先创建一个临时表:
create table wjlcn_temp(id int auto_increment primary key);
再将中间结果insert到临时表中:
insert into wjlcn_temp select id from testinfo group by idtest;
再来查询结果:select count(*) from testinfo where id not in (select id from wjlcn_temp);

点击(此处)折叠或打开

  1. mysql> explain extended select count(*) from testinfo where id not in (select id from wjlcn_temp);

  2. +----+--------------------+------------+-----------------+---------------+------------+---------+------+------+----------+--------------------------+

  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

  4. +----+--------------------+------------+-----------------+---------------+------------+---------+------+------+----------+--------------------------+

  5. | 1 | PRIMARY | testinfo | index | NULL | key_idtest | 62 | NULL | 8761 | 100.00 | Using where; Using index |

  6. | 2 | DEPENDENT SUBQUERY | wjlcn_temp | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index |

  7. +----+--------------------+------------+-----------------+---------------+------------+---------+------+------+----------+--------------------------+

  8. 2 rows in set, 1 warning (0.00 sec)



  9. mysql> show warnings \G

  10. *************************** 1. row ***************************

  11.   Level: Note

  12.    Code: 1003

  13. Message: select count(0) AS `count(*)` from `test`.`testinfo` where (not(<in_optimizer>(`test`.`testinfo`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`testinfo`.`id`) in wjlcn_temp on PRIMARY)))))

  14. 1 row in set (0.00 sec)


  15. mysql> select count(*),sleep(1) from testinfo where id not in (select id from wjlcn_temp);

  16. +----------+----------+

  17. | count(*) | sleep(1) |

  18. +----------+----------+

  19. | 1059 | 0 |

  20. +----------+----------+

  21. 1 row in set (1.02 sec)



  22. Time: 130725 11:41:04

  23. User@Host: root[root] @ localhost []

  24. # Query_time: 1.026054 Lock_time: 0.000231 Rows_sent: 1 Rows_examined: 9999

  25. SET timestamp=1374723664;

  26. select count(*),sleep(1) from testinfo where id not in (select id from wjlcn_temp);

从上面可以看到执行计划的 filtered列为100,跟上面的sql有很大的区别。
其次,在explain中出现了 unique_subquery

文档中解释:
unique_subquery
This type replaces ref for some IN subqueries of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

当sql中出现 unique_subquery时,sql会自动替换in 后面的子查询。从上面的执行计划中可以看到sql实际执行的是:
select   count ( 0 )   AS   `count(*)`   from   `test` . `testinfo`   where   ( not ( < in_optimizer > ( `test` . `testinfo` . `id` , < exists > ( < primary_index_lookup > ( < cache > ( `test` . `testinfo` . `id` )   in  wjlcn_temp on PRIMARY ) ) ) ) )
当sql中用到  primary_index_lookup时,sql的执行效率也比较好。
从慢查询中看到扫描的行数为:9999,而实际的执行时间为: Query_time: 1 . 026054 - 1 = 0 . 026054 秒。
比起前面的36秒好了很多,如果表的记录数更大时,执行时间相差更加明显。

所以,在sql中用in子查询时,最后看看执行计划。若在线上大表频繁执行 select count(*) from testinfo where id not in (select id from testinfo group by idtest);类的sql,可能会导致服务器的性能问题。


本文转载自:http://blog.chinaunix.net/uid-21879027-id-3818874.html

下一篇: PHP 导出CSV
粉丝 3
博文 73
码字总数 22668
作品 0
昌平
私信 提问
MySQL 索引条件下推 Index Condition Pushdown

MySQL 索引条件下推 Index Condition Pushdown 出现在MySQL5.6及之后的版本中,能大幅提升查询效率,原因如下: 内容摘录自《深入理解MariaDB和MySQL》 下面使实验,使用官方提供的employees...

lirulei90
2018/01/12
0
0
浅析index condition pushdown

另一篇文章讲叙了 MRR和BKA 什么是indexcondition pushdown(ICP)? 在数据库中pushdown表示某些操作“下推”,也就是某些操作提前执行,在生成执行计划时某些操作下推可以大大提升效率(为什么...

长平狐
2012/11/01
129
0
MySQL查询执行计划的警告信息辨析一例

实例1 :引入实例 Step 1: 创建表 CREATE TABLE ( SMALLINT(6) NOT NULL DEFAULT '99', TEXT, PRIMARY KEY () ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO t1 (id,txt) VALUES (1,'......

1415699306
2014/08/31
0
0
MySQL Explain详解

MySQL Explain详解 若想查看MySQL优化器优化后的sql语句可以使用如下语句: Explain输出字段解释 Explain输出字段: Column 含义 id 查询序号 select_type 查询类型 table 表名 partitions 匹...

Gen_zhou
2016/10/25
216
0
Netty浅析 - 3. 总结

前言 本篇为《Netty浅析》系列最后一篇,主要对Netty做简单的总结,如果对Netty的细节感兴趣,可以阅读本系列的另外两篇: Netty浅析 - 1. 基础 Netty浅析 - 2. 实现 Netty适用场景 Netty只是...

简xiaoyao
2018/12/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

c++队列(转自本人csdn)

#include using namespace std;int main(){char input, alphabet = ‘A’;cout << "输入最后一个大写字母: ";cin >> input;for(int i = 1; i <= (input-'A'+1); ++i)......

WinkJie
28分钟前
1
0
外观模式(Facade)

//这篇写的可以 https://www.jianshu.com/p/f71051475e32

南桥北木
46分钟前
2
0
锤子科技确认卖身,官网微博均变更所属公司

年前曾有过消息,罗永浩创办的锤子科技将被今日头条和抖音的母公司字节跳动收购。其中的部分工作人员将随专利一同成为字节跳动所属,而罗永浩本人将离开锤子科技。 今天我们终于看到了锤子科...

linux-tao
53分钟前
2
0
[json]阿里fastjson1.2.24字符串转为json对象/数组

java.lang.ClassCastException: com.alibaba.fastjson.JSONArray cannot be cast to com.alibaba.fastjson.JSONObject at com.alibaba.fastjson.JSON.parseObject(JSON.java:206) 多行要用J......

Danni3
今天
1
0
关于jenkins 构建打包的技巧

关于jenkins 构建打包的技巧 jenkins是一个专业构建平台,在各大公司内部比较受欢迎,可以自定义构建方式,以下整理一些技巧,防止以后忘记。 邮件通知 jenkins 自带邮件通知功能,但是比较单...

shzwork
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部