先做几个实验
SQL1测试:
select distinct hphm from sjkk_gcjl
where jgsj>='2014-04-01 00:00:00'
and jgsj<='2015-04-30 00:00:00'
and hphm like '_BX_27_'
and csys = 'E'
limit 100;
8 rows in set (9.83 sec)
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
| 1 | SIMPLE | sjkk_gcjl | ref | index14,index03,index09 | index03 | 17 | const | 383770 | Using where; Using index; Using temporary |
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
mysql> show profile cpu,block io for query 1;
+----------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------------+----------+----------+------------+--------------+---------------+
| Queried about 630000 rows | 0.059210 | 0.055992 | 0.000000 | 862 | 0 |
| Queried about 640000 rows | 0.058513 | 0.091986 | 0.002000 | 1000 | 0 |
| Queried about 650000 rows | 0.061223 | 0.101984 | 0.002000 | 999 | 0 |
| Queried about 660000 rows | 0.072752 | 0.113983 | 0.000000 | 1009 | 0 |
| Queried about 670000 rows | 0.059195 | 0.099985 | 0.000000 | 998 | 0 |
| Queried about 680000 rows | 0.060985 | 0.105984 | 0.000999 | 876 | 0 |
| Queried about 690000 rows | 0.061721 | 0.105984 | 0.001000 | 1068 | 0 |
| Queried about 700000 rows | 0.058141 | 0.054991 | 0.001000 | 998 | 0 |
| Queried about 710000 rows | 0.059691 | 0.057991 | 0.000000 | 875 | 0 |
| Queried about 720000 rows | 0.063056 | 0.106984 | 0.002000 | 1069 | 0 |
| Queried about 730000 rows | 0.060662 | 0.101985 | 0.001000 | 1000 | 0 |
| Queried about 740000 rows | 0.059837 | 0.119981 | 0.001000 | 874 | 0 |
| Queried about 750000 rows | 0.060930 | 0.105984 | 0.002999 | 1004 | 0 |
| Queried about 760000 rows | 0.061645 | 0.100985 | 0.004999 | 1063 | 0 |
| Queried about 770000 rows | 0.058895 | 0.097985 | 0.003000 | 873 | 0 |
| Queried about 780000 rows | 0.060339 | 0.106984 | 0.000000 | 1005 | 0 |
| Queried about 790000 rows | 0.059922 | 0.109983 | 0.000000 | 876 | 0 |
| Queried about 800000 rows | 0.060070 | 0.102984 | 0.002000 | 1065 | 0 |
| Queried about 810000 rows | 0.060085 | 0.102985 | 0.000000 | 1000 | 0 |
| Queried about 820000 rows | 0.058799 | 0.103984 | 0.001999 | 874 | 0 |
| Queried about 830000 rows | 0.060548 | 0.102984 | 0.000000 | 1059 | 0 |
| Queried about 840000 rows | 0.058795 | 0.098985 | 0.001000 | 1001 | 0 |
| Queried about 850000 rows | 0.059702 | 0.104984 | 0.001000 | 876 | 0 |
| Queried about 860000 rows | 0.085284 | 0.119982 | 0.002999 | 991 | 0 |
| Queried about 870000 rows | 0.060015 | 0.105984 | 0.000000 | 1070 | 0 |
| Queried about 880000 rows | 0.086861 | 0.112983 | 0.018998 | 874 | 32768 |
| Queried about 890000 rows | 0.057708 | 0.059991 | 0.001999 | 992 | 0 |
| Queried about 900000 rows | 0.059749 | 0.062990 | 0.000000 | 878 | 0 |
| Queried about 910000 rows | 0.060904 | 0.106984 | 0.001000 | 1068 | 0 |
| Queried about 920000 rows | 0.058960 | 0.100985 | 0.002000 | 1000 | 0 |
| Queried about 930000 rows | 0.058698 | 0.102984 | 0.000000 | 876 | 0 |
| Queried about 940000 rows | 0.061582 | 0.109983 | 0.002000 | 1058 | 0 |
| Queried about 950000 rows | 0.057122 | 0.099985 | 0.000000 | 997 | 0 |
| Queried about 960000 rows | 0.057954 | 0.105984 | 0.000999 | 865 | 0 |
| Queried about 970000 rows | 0.061030 | 0.111983 | 0.000000 | 999 | 0 |
| Queried about 980000 rows | 0.061993 | 0.111983 | 0.000000 | 1069 | 0 |
| Queried about 990000 rows | 0.058676 | 0.104984 | 0.001000 | 875 | 0 |
| Queried about 1000000 rows | 0.059957 | 0.105984 | 0.001000 | 992 | 0 |
| Queried about 1010000 rows | 0.059833 | 0.102984 | 0.000000 | 882 | 0 |
| Queried about 1020000 rows | 0.059747 | 0.099985 | 0.000000 | 1058 | 0 |
| Queried about 1030000 rows | 0.058914 | 0.098985 | 0.000000 | 998 | 0 |
| Queried about 1040000 rows | 0.059373 | 0.104984 | 0.001000 | 877 | 0 |
| Queried about 1050000 rows | 0.061450 | 0.102984 | 0.001000 | 1068 | 0 |
| Queried about 1060000 rows | 0.058831 | 0.101985 | 0.001999 | 996 | 0 |
| Queried about 1070000 rows | 0.059302 | 0.103984 | 0.001000 | 865 | 0 |
| Queried about 1080000 rows | 0.059300 | 0.102984 | 0.001000 | 1008 | 0 |
| Queried about 1090000 rows | 0.060018 | 0.066990 | 0.003000 | 935 | 0 |
| Queried about 1100000 rows | 0.059607 | 0.055992 | 0.001000 | 997 | 0 |
| Queried about 1110000 rows | 0.059866 | 0.101984 | 0.000000 | 1000 | 0 |
| Queried about 1120000 rows | 0.058746 | 0.100985 | 0.000999 | 873 | 0 |
| Queried about 1130000 rows | 0.061760 | 0.104984 | 0.002000 | 1046 | 0 |
| Queried about 1140000 rows | 0.059472 | 0.108983 | 0.000000 | 1010 | 0 |
| Queried about 1150000 rows | 0.058003 | 0.104984 | 0.002000 | 873 | 0 |
| Queried about 1160000 rows | 0.061985 | 0.103984 | 0.001999 | 1068 | 0 |
| Queried about 1170000 rows | 0.058483 | 0.103985 | 0.000000 | 1000 | 0 |
| Queried about 1180000 rows | 0.058177 | 0.102984 | 0.000000 | 877 | 0 |
| Queried about 1190000 rows | 0.059631 | 0.099985 | 0.001000 | 1002 | 0 |
| Queried about 1200000 rows | 0.060789 | 0.100984 | 0.001000 | 943 | 0 |
| Queried about 1210000 rows | 0.058576 | 0.102985 | 0.000000 | 994 | 0 |
| Queried about 1220000 rows | 0.060485 | 0.104984 | 0.002000 | 1015 | 0 |
| Queried about 1230000 rows | 0.060498 | 0.102984 | 0.002000 | 870 | 0 |
| Queried about 1240000 rows | 0.060536 | 0.102984 | 0.001999 | 1060 | 0 |
| Queried about 1250000 rows | 0.059091 | 0.124981 | 0.004000 | 992 | 0 |
| Queried about 1260000 rows | 0.065156 | 0.116983 | 0.000999 | 875 | 0 |
| Queried about 1270000 rows | 0.064085 | 0.086986 | 0.001000 | 1067 | 0 |
| Queried about 1280000 rows | 0.059056 | 0.053992 | 0.001000 | 872 | 0 |
| Queried about 1290000 rows | 0.098185 | 0.127981 | 0.004000 | 1004 | 0 |
| Queried about 1300000 rows | 0.061578 | 0.110983 | 0.002999 | 999 | 0 |
| Queried about 1310000 rows | 0.060825 | 0.106984 | 0.000000 | 943 | 0 |
| Queried about 1320000 rows | 0.058399 | 0.102984 | 0.001000 | 998 | 0 |
| Queried about 1330000 rows | 0.060508 | 0.107984 | 0.003000 | 1000 | 0 |
| Queried about 1340000 rows | 0.059201 | 0.103984 | 0.000000 | 870 | 0 |
| Queried about 1350000 rows | 0.059936 | 0.106983 | 0.000999 | 1073 | 0 |
| Queried about 1360000 rows | 0.059736 | 0.103985 | 0.000000 | 1000 | 0 |
| Queried about 1370000 rows | 0.059515 | 0.104984 | 0.001000 | 874 | 0 |
| Queried about 1380000 rows | 0.062633 | 0.108983 | 0.001000 | 1067 | 0 |
| Queried about 1390000 rows | 0.058827 | 0.102984 | 0.000000 | 873 | 0 |
| Queried about 1400000 rows | 0.060226 | 0.104985 | 0.003000 | 999 | 0 |
| Queried about 1410000 rows | 0.060084 | 0.104984 | 0.000999 | 999 | 0 |
| Queried about 1420000 rows | 0.060381 | 0.101984 | 0.002000 | 945 | 0 |
| Queried about 1430000 rows | 0.059975 | 0.103984 | 0.001000 | 991 | 0 |
| Queried about 1440000 rows | 0.059889 | 0.103984 | 0.003000 | 999 | 0 |
| Queried about 1450000 rows | 0.060687 | 0.114983 | 0.000999 | 874 | 0 |
| Queried about 1460000 rows | 0.061676 | 0.076988 | 0.001000 | 1066 | 0 |
| Queried about 1470000 rows | 0.058176 | 0.054992 | 0.001000 | 872 | 0 |
| Queried about 1480000 rows | 0.059710 | 0.108983 | 0.001000 | 997 | 0 |
| Queried about 1490000 rows | 0.062022 | 0.104984 | 0.002000 | 1000 | 0 |
| Queried about 1500000 rows | 0.057363 | 0.104984 | 0.000999 | 877 | 0 |
| Queried about 1510000 rows | 0.057744 | 0.103985 | 0.000000 | 993 | 0 |
| Queried about 1520000 rows | 0.059178 | 0.108983 | 0.000000 | 988 | 0 |
| Queried about 1530000 rows | 0.007340 | 0.008999 | 0.002000 | 129 | 0 |
| Sending data | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------------+----------+----------+------------+--------------+---------------+
100 rows in set (0.00 sec)
SQL2测试
select distinct hphm from sjkk_gcjl
where jgsj>='2014-04-01 00:00:00'
and jgsj<='2015-04-30 00:00:00'
and hphm like '%2%'
and csys = 'E'
limit 100;
100 rows in set (0.87 sec)
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
| 1 | SIMPLE | sjkk_gcjl | ref | index14,index03,index09 | index03 | 17 | const | 383770 | Using where; Using index; Using temporary |
+----+-------------+-----------+------+-------------------------+---------+---------+-------+--------+-------------------------------------------+
mysql> show profile cpu,block io for query 4;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000184 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000069 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000066 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000066 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000084 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000082 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.827258 | 0.037994 | 0.008999 | 5957 | 0 |
| preparing | 0.000117 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000072 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.038604 | 0.005999 | 0.000000 | 573 | 0 |
| Sending data | 0.000053 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set (0.00 sec)
从上面的SQL1测试和SQL2测试可以看出,SQL2比SQL1快很多,主要原因是SQL2磁盘读取操作比较少。(虽然符合SQL2条件的数据很多,
但是SQL2只需要扫描很少的数据就可以找到需要的不重复的那100条数据,而符合SQL1过滤条件的数据比较少,SQL1需要扫描更多的数
据才能找到需要的不重复的那100条数据)
SQL3测试
select distinct hphm from sjkk_gcjl use index(index03)
where jgsj>='2015-04-01 00:00:00'
and jgsj<='2015-04-30 00:00:00'
and hphm like '_BX_27_'
and csys = 'E'
limit 100;
.....
5 rows in set (4.82 sec)
+----+-------------+-----------+------+---------------+---------+---------+-------+--------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+--------+-------------------------------------------+
| 1 | SIMPLE | sjkk_gcjl | ref | index03 | index03 | 17 | const | 837454 | Using where; Using index; Using temporary |
+----+-------------+-----------+------+---------------+---------+---------+-------+--------+-------------------------------------------+
从上面SQL1和SQL3测试可以看出,将时间段缩短,即让扫描的数据少一些,可以让其快一些。
总结,从上面的测试可以看出对于带有limit的查询的distinct的优化,在于让其尽量减少检索符合要求的数据时磁盘的读取操作。
这个可以通过索引来完成。有的SQL也可以通过索引来完成distinct工作;下面举一个利用索引完成去重工作的例子。
select distinct hphm from sjkk_gcjl limit 100;
.....
100 rows in set (0.52 sec)
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------------------------------------+
| 1 | SIMPLE | sjkk_gcjl | range | NULL | index01 | 47 | NULL | 10 | Using index for group-by; Using temporary |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------------------------------------+
上面一个(hphm)这样的索引就可以.
如果上面的sql有csys='A'条件那就得用(csys,hphm)这样的索引。
如果上面的sql有csys='A' and jgsj>='2015-04-01 00:00:00' and jgsj<='2015-04-30 00:00:00' 条件,可以加个(csys,jgsj,hphm)但是这个
索引的hphm是用不到的,因为jgsj是个范围条件,索引里加上hphm,可以让这个sql走个覆盖索引。