MySQL两表差集最佳实践
原创
两表求差集SQL: select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
表test,test1克隆sakila库中的payment表，数据量16049。
一、测试总结：
1.test1决定SQL语句的性能，有索引性能最好，无索引性能最差。
2.test1表在关联条件上有索引，test表有无索引，性能相近。
二、最佳实践：
1.test1和test在关联条件上都有索引。
三、测试结果：
1.test复合索引，test1无索引 | 2.test复合索引，test1有索引 | 3.test单列索引，test1无索引 | 4.test单列索引，test1有索引 | |
|---|---|---|---|---|
1.test主键（只含主键列） | 18.24 | 3.55 | 10.93 | 3.56 |
2.test主键（含非主键列） | 19.76 | 3.57 | 12.46 | 3.64 |
3.test普通索引（只含索引列） | 13.29 | 3.71 | 10.79 | 3.61 |
4.test普通索引（含非索引列） | 18.7 | 4.36 | 12.15 | 3.95 |
5.test无索引 | 13.84 | 3.72 | 10.55 | 4.23 |
四、测试脚本：
#1.test主键（只含主键列）
#2.test主键（含非主键列）
#3.test普通索引（只含索引列）
#4.test普通索引（含非索引列）
#5.test无索引
#1.test复合索引，test1无索引
#2.test复合索引，test1有索引
#3.test单列索引，test1无索引
#4.test单列索引，test1有索引
------------------------------------------------------------------------
#环境准备：
create table test as select * from sakila.payment;
create table test1 as select * from test;
delete from test1 limit 10;
select count(*) from test;
select count(*) from test1;
desc test;
desc test1;
set profiling=1;
###########################################################################
#1.test复合索引，test1无索引
show indexes from test1;
#1.1.test复合主键（只含主键列）
alter table test add primary key test(payment_id,customer_id,staff_id);
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#1.2.test复合主键（含非主键列）
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#1.3.test普通复合索引（只含索引列）
alter table test drop primary key ;
create index test_idx on test(payment_id,customer_id,staff_id);
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#1.4.test普通复合索引（含非索引列）
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#1.5.test无索引
drop index test_idx on test;
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
###########################################################################
#2.test复合索引，test1有索引
alter table test1 add primary key test1(payment_id,customer_id,staff_id);
show indexes from test1;
#--------------------------------------------------------------------------
#2.1.test复合主键（只含主键列）
alter table test add primary key test(payment_id,customer_id,staff_id);
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#2.2.test复合主键（含非主键列）
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#2.3.test普通复合索引（只含索引列）
alter table test drop primary key ;
create index test_idx on test(payment_id,customer_id,staff_id);
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#2.4.test普通复合索引（含非索引列）
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id and a.customer_id=b.customer_id and a.staff_id = b.staff_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#2.5.test无索引
drop index test_idx on test;
show indexes from test;
desc select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.customer_id,a.staff_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#############################################################################
#3.test单列索引，test1无索引
alter table test1 drop primary key;
show indexes from test1;
#--------------------------------------------------------------------------
#3.1.test单列主键（只含主键列）
alter table test add primary key test(payment_id);
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#3.2.test单列主键（含非主键列）
show indexes from test;
desc select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#3.3.test普通单列索引（只含索引列）
alter table test drop primary key ;
create index test_idx on test(payment_id);
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#3.4.test普通单列索引（含非索引列）
show indexes from test;
desc select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#3.5.test无索引
drop index test_idx on test;
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
########################################################################
#4.test单列索引，test1有索引
alter table test1 add primary key test1(payment_id);
show indexes from test1;
#--------------------------------------------------------------------------
#4.1.test单列主键（只含主键列）
alter table test add primary key test(payment_id);
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#4.2.test单列主键（含非主键列）
show indexes from test;
desc select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#4.3.test普通单列索引（只含索引列）
alter table test drop primary key ;
create index test_idx on test(payment_id);
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#4.4.test普通单列索引（含非索引列）
show indexes from test;
desc select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id,a.amount from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;
#--------------------------------------------------------------------------
#4.5.test无索引
drop index test_idx on test;
show indexes from test;
desc select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
select a.payment_id from test a left join test1 b on a.payment_id=b.payment_id where b.payment_id is null;
SHOW PROFILE;五、详细执行结果：
mysql> #环境准备：
mysql> create table test as select * from sakila.payment;
Query OK, 16049 rows affected (2.02 sec)
Records: 16049 Duplicates: 0 Warnings: 0
mysql> create table test1 as select * from test;
Query OK, 16049 rows affected (0.77 sec)
Records: 16049 Duplicates: 0 Warnings: 0
mysql> delete from test1 limit 10;
Query OK, 10 rows affected (0.03 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.02 sec)
mysql>
mysql> select count(*) from test1;
+----------+
| count(*) |
+----------+
| 16039 |
+----------+
1 row in set (0.00 sec)
mysql> desc test;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id | smallint(5) unsigned | NO | | 0 | |
| customer_id | smallint(5) unsigned | NO | | NULL | |
| staff_id | tinyint(3) unsigned | NO | | NULL | |
| rental_id | int(11) | YES | | NULL | |
| amount | decimal(5,2) | NO | | NULL | |
| payment_date | datetime | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.01 sec)
mysql>
mysql> desc test1;
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
| payment_id | smallint(5) unsigned | NO | | 0 | |
| customer_id | smallint(5) unsigned | NO | | NULL | |
| staff_id | tinyint(3) unsigned | NO | | NULL | |
| rental_id | int(11) | YES | | NULL | |
| amount | decimal(5,2) | NO | | NULL | |
| payment_date | datetime | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。