MySQL两表差集最佳实践–云惠网

avatar
avatar
云惠网小编
2266
文章
0
评论
2020年4月18日08:41:16 评论 46 次浏览 11568字阅读38分33秒
摘要

两表求差集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;

两表求差集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)

腾讯云618
avatar
腾讯云618
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: