MySQL 5.6 not in 和表关联性能测试对比
来源：互联网 发布：如何用java编写小游戏 编辑：程序博客网 时间：2024/05/22 09:01
对比的结果是加索引的情况下，最好是选择表关联。
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.37-log |
DELIMITER $
CREATE PROCEDURE pre_test()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<40000 DO INSERT INTO a VALUES( i,LPAD(i, 1200, 0));
SET i = i+1;
END WHILE;
END$
CALL pre_test();
#多执行几次
INSERT INTO a SELECT * FROM a;
COMMIT;
CREATE TABLE b LIKE a;
SHOW CREATE TABLE b;
INSERT INTO b SELECT * FROM a LIMIT 20000;
COMMIT;
SELECT COUNT(1) FROM a;
+----------+
| COUNT(1) |
+----------+
| 160000 |
+----------+
SELECT COUNT(1) FROM b;
+----------+
| COUNT(1) |
+----------+
| 20000 |
+----------+
#第一组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##3.092
EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 2 | SUBQUERY | a | ALL | NULL | NULL | NULL | NULL | 146674 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.018
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 146674 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |
SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.081
EXPLAIN SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 146674 | USING WHERE |
| 2 | SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 18348 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#出不来
EXPLAIN SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 146674 | NULL |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
#加上索引后
CREATE INDEX ind_a_c1 ON a(c1);
CREATE INDEX ind_b_c1 ON b(c1);
#第三组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##0.053
EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
mysql> EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 2 | DEPENDENT SUBQUERY | a | index_subquery | ind_a_c1 | ind_a_c1 | 5 | func | 2 | USING INDEX; FULL scan ON NULL KEY |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.001
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
| 1 | SIMPLE | b | ref | ind_b_c1 | ind_b_c1 | 5 | const | 1 | USING INDEX CONDITION |
| 1 | SIMPLE | a | ref | ind_a_c1 | ind_a_c1 | 5 | test.b.c1 | 1 | USING INDEX |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
#第四组测试
SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.022
EXPLAIN SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | PRIMARY | a | INDEX | NULL | ind_a_c1 | 5 | NULL | 146674 | USING WHERE; USING INDEX |
| 2 | SUBQUERY | b | INDEX | ind_b_c1 | ind_b_c1 | 5 | NULL | 18348 | USING INDEX |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
SELECT COUNT(1) FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#0.052
EXPLAIN SELECT COUNT(1) FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
| 1 | SIMPLE | a | INDEX | NULL | ind_a_c1 | 5 | NULL | 146674 | USING INDEX |
| 1 | SIMPLE | b | ref | ind_b_c1 | ind_b_c1 | 5 | test.a.c1 | 1 | USING WHERE; USING INDEX |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.37-log |
+------------+
##############开始制造数据##############
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.37-log |
+------------+
DELIMITER $
CREATE PROCEDURE pre_test()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<40000 DO INSERT INTO a VALUES( i,LPAD(i, 1200, 0));
SET i = i+1;
END WHILE;
END$
CALL pre_test();
#多执行几次
INSERT INTO a SELECT * FROM a;
COMMIT;
CREATE TABLE b LIKE a;
SHOW CREATE TABLE b;
INSERT INTO b SELECT * FROM a LIMIT 20000;
COMMIT;
SELECT COUNT(1) FROM a;
+----------+
| COUNT(1) |
+----------+
| 160000 |
+----------+
SELECT COUNT(1) FROM b;
+----------+
| COUNT(1) |
+----------+
| 20000 |
+----------+
#第一组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##3.092
EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 2 | SUBQUERY | a | ALL | NULL | NULL | NULL | NULL | 146674 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.018
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 146674 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.081
EXPLAIN SELECT * FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 146674 | USING WHERE |
| 2 | SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 18348 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#出不来
EXPLAIN SELECT a.* FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 146674 | NULL |
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE; USING JOIN buffer (Block Nested LOOP) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
#加上索引后
CREATE INDEX ind_a_c1 ON a(c1);
CREATE INDEX ind_b_c1 ON b(c1);
#第三组测试
SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a ); ##0.053
EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
mysql> EXPLAIN SELECT * FROM b WHERE c1 NOT IN(SELECT c1 FROM a );
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
| 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 18348 | USING WHERE |
| 2 | DEPENDENT SUBQUERY | a | index_subquery | ind_a_c1 | ind_a_c1 | 5 | func | 2 | USING INDEX; FULL scan ON NULL KEY |
+----+--------------------+-------+----------------+---------------+----------+---------+------+-------+------------------------------------+
SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;#0.001
EXPLAIN SELECT b.* FROM b LEFT JOIN a ON a.c1 = b.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
| 1 | SIMPLE | b | ref | ind_b_c1 | ind_b_c1 | 5 | const | 1 | USING INDEX CONDITION |
| 1 | SIMPLE | a | ref | ind_a_c1 | ind_a_c1 | 5 | test.b.c1 | 1 | USING INDEX |
+----+-------------+-------+------+---------------+----------+---------+-----------+------+-----------------------+
#第四组测试
SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b ); ##0.022
EXPLAIN SELECT COUNT(1) FROM a WHERE c1 NOT IN(SELECT c1 FROM b );
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
| 1 | PRIMARY | a | INDEX | NULL | ind_a_c1 | 5 | NULL | 146674 | USING WHERE; USING INDEX |
| 2 | SUBQUERY | b | INDEX | ind_b_c1 | ind_b_c1 | 5 | NULL | 18348 | USING INDEX |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+--------------------------+
SELECT COUNT(1) FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;#0.052
EXPLAIN SELECT COUNT(1) FROM a LEFT JOIN b ON b.c1 = a.c1 WHERE b.c1 IS NULL;
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
| id | select_type | TABLE | TYPE | possible_keys | KEY | key_len | ref | ROWS | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
| 1 | SIMPLE | a | INDEX | NULL | ind_a_c1 | 5 | NULL | 146674 | USING INDEX |
| 1 | SIMPLE | b | ref | ind_b_c1 | ind_b_c1 | 5 | test.a.c1 | 1 | USING WHERE; USING INDEX |
+----+-------------+-------+-------+---------------+----------+---------+-----------+--------+--------------------------+
阅读全文
0 0
- MySQL 5.6 not in 和表关联性能测试对比
- mysql not exists 和 not in对比
- in和exists、not in和not exists性能对比
- mysql B-tree和hash索引性能测试对比
- not in,not exists,left join性能对比
- mysql not in 和in
- PHP4和PHP5性能测试和对比
- ORACLE:exits和in的性能对比
- SQLITE和ACCESS性能对比测试
- nginx和apache并发性能测试对比
- Redis和Memcache性能测试对比
- lua 和 luajit 性能对比测试
- ArrayList和LinkedList性能对比测试
- mysql多表关联更新和in效率问题
- Mysql 在windows 2000和 hp-ux上性能 对比测试
- Mysql 使用UUID和自增主键ID性能对比测试
- JS for与for in对比（包含性能测试）
- MySQL的deocode，与not in的关联查询实现
- ros 安装 qt creator plugin
- 教育直播开发：直播+教育新模式的大活力 在全民直播时代，大部分东西都可以和直播联系起来，比如商城直播系统、在线抓娃娃系统、教育直播系统等，这些分别是电商、线下娃娃机、在线教育与直播相结合，今天要大家说
- 用C++实现俄罗斯方块
- Phyton安装MySQL驱动
- 201712月如何买到阿里云免费SSL证书（0元SSL证书）
- MySQL 5.6 not in 和表关联性能测试对比
- java的enum作用
- 变长参数模版函数初探
- Android gralde 更换不同资源
- windows搭建laravel开发环境
- 博客搬家
- 【Linux使用及问题解决】用户登录时显示 -bash-4.2$ 问题
- R语言实现层次聚类分析
- 服务器模型——从单线程阻塞到多线程非阻塞（上）
