mysql子查询性能_MySQL子查询性能

1、语法

子查询有IN和EXISTS两种，哪种速度更快呢？主要取决于两张表的大小关系。

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc);

IN会先查询B，然后把查询B的结果，做为输入，再查询A，当 A 记录多时，可以很好的利用 A 的索引，时间复杂度是O(b*log a)。

EXISTS会先查询A，然后把查询A的结果，做为输入，再查询B，当 B 记录多时，可以很好的利用 B 的索引，时间复杂度是O(a*log b)。

2、下面来做个实验

(1)先执行 source first.sql; 来创建 2 张数据表

DROP TABLE IF EXISTS `t_big`;

CREATE TABLE `t_big` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`phone` varchar(30) NOT NULL,

PRIMARY KEY (`id`),

KEY idx_name(`name`),

KEY idx_phone(`phone`)

) ENGINE = InnoDB;

DROP TABLE IF EXISTS `t_small`;

CREATE TABLE `t_small` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`nickname` varchar(30) NOT NULL,

PRIMARY KEY (`id`),

KEY idx_name(`name`),

KEY idx_nickname(`nickname`)

) ENGINE = InnoDB;

(2)执行如下 shell 脚本bash rand.sh来生成随机数据的 sql 插入语句的 second.sql 文件，再执行 source second.sql; 来插入数据：

#!/bin/bash

small=50

big=500000

rm -f second.sql

for((i=0;i

do

printf "INSERT INTO \`t_big\` (\`name\`, \`phone\`) VALUES ('zhang%d', '%08d');\n" $[$RANDOM%1000000] $[$RANDOM%100000000] >> second.sql

done

for((i=0;i

do

printf "INSERT INTO \`t_small\` (\`name\`, \`nickname\`) VALUES ('zhang%d', '%s');\n" $[$RANDOM%1000000] $(tr -dc "a-z"> second.sql

done

echo "create second.sql success!"

(3)此时大表 t_big 的记录数量多，小表 t_small 的记录少，可以看到使用 EXISTS比 IN 慢很多。

mysql> set profiling = 1;

SELECT * FROM t_big WHERE `name` IN (SELECT `name` FROM t_small);

mysql> show profiles;

+----------+------------+------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------------------+

| 1 | 0.01406125 | SELECT * FROM t_big WHERE `name` IN (SELECT `name` FROM t_small) |

+----------+------------+------------------------------------------------------------------+

+----------+------------+---------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------------------------------------------------------------------+

| 1 | 1.65336600 | SELECT * FROM t_big WHERE EXISTS (SELECT `name` FROM t_small WHERE t_big.name=t_small.name) |

+----------+------------+---------------------------------------------------------------------------------------------+

(4)再做一个相反的实验让两个表的位置互换一下，可以看到 EXISTS 比 IN 快。

+----------+------------+------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------------------+

| 1 | 0.00277950 | SELECT * FROM t_small WHERE `name` IN (SELECT `name` FROM t_big) |

+----------+------------+------------------------------------------------------------------+

+----------+------------+---------------------------------------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+---------------------------------------------------------------------------------------------+

| 1 | 0.00050700 | SELECT * FROM t_small WHERE EXISTS (SELECT `name` FROM t_big WHERE t_small.name=t_big.name) |

+----------+------------+---------------------------------------------------------------------------------------------+

3、结论

当 A > B，用 IN：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

当 A < B，用 EXISTS：

SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc);