mysql 5.1与5.6 子查询性能对比

最新推荐文章于 2021-02-07 12:29:39 发布

不吃草的牛德

最新推荐文章于 2021-02-07 12:29:39 发布

阅读量3.2k

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/taurendruids/article/details/38755417

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

今天下午想mysql 5.1与5.6子查询性能，发现性能差距是相当大

本测试是基于两台一样的虚拟机，通过对其中一台clone来创建的虚拟机。

源表数据使用sysbench生成50000条数据，两个库数据完全一样。

mysql>select @@version
    ->;
+----------------------+
|@@version            |
+----------------------+
|5.1.73-community-log|
+----------------------+
1rowinset(0.00sec)
 
mysql>select count(*) from sbtest;
ERROR1046(3D000): No database selected
mysql>use sysbench
Database changed
mysql>select count(*) from sbtest;
+----------+
|count(*)|
+----------+
|    50000|
+----------+
1rowinset(0.02sec)
 
mysql>create table idtable as select id from sbtest where id<=1000;
QueryOK,1000rowsaffected(0.01sec)
Records:1000  Duplicates:0  Warnings:0
 
mysql>select count(*) from sbtest where id in(select id from idtable);
+----------+
|count(*)|
+----------+
|    1000|
+----------+
1 row in set(35.60sec)
 
mysql>create index id_st on sbtest(id);
QueryOK,50000 rows affected(1.64sec)
Records:50000  Duplicates:0  Warnings:0
 
mysql>select count(*) from sbtest where id in(select id from idtable);
+----------+
|count(*)|
+----------+
|    1000|
+----------+
1 row in set(34.96sec)

mysql>select @@version
    ->;
+------------+
|@@version  |
+------------+
|5.6.17-log|
+------------+
1 row in set(0.00sec)
 
mysql>select count(*) from sbtest;
+----------+
|count(*)|
+----------+
|    50000|
+----------+
1rowinset(0.02sec)
 
mysql>create table idtable as select id from sbtest where id<=1000;
QueryOK,1000 rows affected(0.09sec)
Records:1000  Duplicates:0  Warnings:0
 
mysql>select count(*) from sbtest where id in(select id from idtable);
+----------+ 
|count(*)|
+----------+
|    1000|
+----------+
1 row in set(0.00sec)

所有测试表的数据完全一样，性能差距不是一般的大

查看两边执行计划

5.1

mysql>explain select count(*) from sbtest where id in(select id from idtable);
+----+--------------------+---------+-------+---------------+------+---------+------+-------+--------------------------+
|id|select_type        |table  |type  |possible_keys|key  |key_len|ref  |rows  |Extra                    |
+----+--------------------+---------+-------+---------------+------+---------+------+-------+--------------------------+
|  1|PRIMARY            |sbtest  |index|NULL          |k    |4      |NULL|50088|Using where;Using index|
|  2|DEPENDENT SUBQUERY|idtable|ALL  |NULL          |NULL|NULL    |NULL|  1000|Using where              |
+----+--------------------+---------+-------+---------------+------+---------+------+-------+--------------------------+
2rowsinset(0.00sec)

5.6


mysql>select * from sbtest a join(select id from idtable where id=78)b  ona.id=b.id;
+----+---+---+----------------------------------------------------+----+
|id|k|c|pad                                                |id|
+----+---+---+----------------------------------------------------+----+
|78|0|  |qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|78|
+----+---+---+----------------------------------------------------+----+
1 row inset(0.01sec)

MySQL 5.1中子查询性能是相当差，在5.6之前的版本，子查询仅仅被看成一个功能，生产环境完全不宜使用。在5.6之后针对这个问题进行了优化，性能有了相当大的提升，因此在5.5之前不建议使用子查询。

将5.1的in 子查询改为join 性能会有很大提升。

mysql>select * from sbtest where id in(select id from idtable where id=78);
+----+---+---+----------------------------------------------------+
|id|k|c|pad                                                |
+----+---+---+----------------------------------------------------+
|78|0|  |qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|
+----+---+---+----------------------------------------------------+
1 row in set(47.51sec)
 
mysql>select * from sbtest a join(select id from idtable where id=78)b  ona.id=b.id;
+----+---+---+----------------------------------------------------+----+
|id|k|c|pad                                                |id|
+----+---+---+----------------------------------------------------+----+
|78|0|  |qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt|78|
+----+---+---+----------------------------------------------------+----+
1 row in set(0.01sec)

从以上测试来看查询时间由原来的47.51s提升至0.01s。

在5.0,5.1中如果使用子查询，尽量使用join吧。性能问题不容忽视。