mysql 5.1与5.6 子查询性能对比

今天下午想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 selectedmysql>use sysbenchDatabase changedmysql>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吧。性能问题不容忽视。