数据和表结构完全一样的两个表，查询性能相差一倍多
来源：互联网 发布：单片机原理及应用林立 编辑：程序博客网 时间：2024/06/05 09:03
说明：物理读，指的是第一次查询，数据完全来自磁盘。
内存读，指的是查询时没有磁盘读，数据完全来自内存。
下面的这些表都是tokudb引擎的
第一个表的查询
select jgsj,jlbh,hphm,clsd from sjkk_gcjl force index(index09) where jgsj>='2010-01-20 00:00:00' and jgsj<='2015-05-20 00:00:00' and hphm like '_BX797_' and csys = 'Z' order by jgsj desc ,jlbh desc;---物理读40 rows in set (3 min 52.50 sec) --内存读40 rows in set (3 min 10.15 sec)+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+| 1 | SIMPLE | sjkk_gcjl | range | index09 | index09 | 25 | NULL | 43706085 | Using where; Using index |+----+-------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+show profiles;mysql> show profile cpu,block io for query 1;+-----------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+-----------------------------+----------+----------+------------+--------------+---------------+| Queried about 44280000 rows | 0.046684 | 0.043993 | 0.000000 | 409 | 0 || Queried about 44290000 rows | 0.048204 | 0.043994 | 0.001000 | 478 | 0 || Queried about 44300000 rows | 0.047913 | 0.043993 | 0.000999 | 421 | 0 || Queried about 44310000 rows | 0.056145 | 0.043993 | 0.000000 | 496 | 0 || Queried about 44320000 rows | 0.047843 | 0.043993 | 0.001000 | 502 | 0 || Queried about 44330000 rows | 0.046915 | 0.043994 | 0.000000 | 431 | 0 || Queried about 44340000 rows | 0.047354 | 0.043993 | 0.000000 | 433 | 0 || Queried about 44350000 rows | 0.065969 | 0.045993 | 0.001000 | 569 | 0 || Queried about 44360000 rows | 0.047554 | 0.043993 | 0.000000 | 434 | 0 || Queried about 44370000 rows | 0.046946 | 0.042994 | 0.001000 | 432 | 0 || Queried about 44380000 rows | 0.047212 | 0.043993 | 0.000000 | 431 | 0 || Queried about 44390000 rows | 0.047862 | 0.042994 | 0.002000 | 504 | 0 || Queried about 44400000 rows | 0.061183 | 0.044993 | 0.000000 | 498 | 0 || Queried about 44410000 rows | 0.050762 | 0.043993 | 0.000000 | 424 | 0 || Queried about 44420000 rows | 0.046939 | 0.043993 | 0.000000 | 491 | 0 || Queried about 44430000 rows | 0.046026 | 0.043994 | 0.000000 | 403 | 0 || Queried about 44440000 rows | 0.055757 | 0.042993 | 0.000999 | 462 | 0 || Queried about 44450000 rows | 0.052806 | 0.042994 | 0.002000 | 392 | 0 || Queried about 44460000 rows | 0.049510 | 0.047992 | 0.000000 | 325 | 0 || Queried about 44470000 rows | 0.047199 | 0.043994 | 0.001000 | 433 | 0 || Queried about 44480000 rows | 0.053476 | 0.049992 | 0.000000 | 505 | 0 || Queried about 44490000 rows | 0.056939 | 0.043993 | 0.001000 | 498 | 0 || Queried about 44500000 rows | 0.047061 | 0.043994 | 0.000000 | 432 | 0 || Queried about 44510000 rows | 0.048635 | 0.044993 | 0.000000 | 506 | 0 || Queried about 44520000 rows | 0.048076 | 0.045993 | 0.000000 | 432 | 0 || Queried about 44530000 rows | 0.055739 | 0.044993 | 0.001000 | 496 | 0 || Queried about 44540000 rows | 0.047754 | 0.042993 | 0.001000 | 501 | 0 || Queried about 44550000 rows | 0.052273 | 0.048993 | 0.000999 | 253 | 0 || Queried about 44560000 rows | 0.052004 | 0.048993 | 0.001000 | 257 | 0 || Queried about 44570000 rows | 0.053480 | 0.050992 | 0.001000 | 241 | 0 || Queried about 44580000 rows | 0.059521 | 0.051992 | 0.000000 | 333 | 0 || Queried about 44590000 rows | 0.053386 | 0.050992 | 0.000000 | 227 | 0 || Queried about 44600000 rows | 0.052641 | 0.049993 | 0.001000 | 236 | 0 || Queried about 44610000 rows | 0.053801 | 0.051992 | 0.000000 | 275 | 0 || Queried about 44620000 rows | 0.063820 | 0.050992 | 0.000000 | 310 | 0 || Queried about 44630000 rows | 0.060727 | 0.047993 | 0.002000 | 257 | 0 || Queried about 44640000 rows | 0.054200 | 0.049992 | 0.000000 | 315 | 0 || Queried about 44650000 rows | 0.054330 | 0.051992 | 0.000000 | 237 | 0 || Queried about 44660000 rows | 0.054982 | 0.052992 | 0.000000 | 228 | 0 || Queried about 44670000 rows | 0.054531 | 0.049992 | 0.000000 | 333 | 0 || Queried about 44680000 rows | 0.054212 | 0.051993 | 0.000000 | 226 | 0 || Queried about 44690000 rows | 0.053953 | 0.051992 | 0.000000 | 237 | 0 || Queried about 44700000 rows | 0.058408 | 0.055991 | 0.000999 | 239 | 0 || Queried about 44710000 rows | 0.064937 | 0.052992 | 0.001000 | 282 | 0 || Queried about 44720000 rows | 0.053356 | 0.050992 | 0.000000 | 226 | 0 || Queried about 44730000 rows | 0.055527 | 0.052992 | 0.001000 | 249 | 0 || Queried about 44740000 rows | 0.054652 | 0.051992 | 0.001000 | 219 | 0 || Queried about 44750000 rows | 0.065079 | 0.050993 | 0.000000 | 293 | 0 || Queried about 44760000 rows | 0.053888 | 0.048992 | 0.002000 | 230 | 0 || Queried about 44770000 rows | 0.053796 | 0.049993 | 0.001000 | 286 | 0 || Queried about 44780000 rows | 0.050730 | 0.048992 | 0.000000 | 289 | 0 || Queried about 44790000 rows | 0.047473 | 0.044993 | 0.000000 | 433 | 0 || Queried about 44800000 rows | 0.057210 | 0.044993 | 0.000000 | 574 | 0 || Queried about 44810000 rows | 0.047081 | 0.042994 | 0.000999 | 437 | 0 || Queried about 44820000 rows | 0.047546 | 0.043993 | 0.000000 | 436 | 0 || Queried about 44830000 rows | 0.047732 | 0.043994 | 0.002000 | 510 | 0 || Queried about 44840000 rows | 0.055867 | 0.044993 | 0.000000 | 501 | 0 || Queried about 44850000 rows | 0.047395 | 0.042993 | 0.000000 | 439 | 0 || Queried about 44860000 rows | 0.047606 | 0.043993 | 0.001000 | 485 | 0 || Queried about 44870000 rows | 0.044773 | 0.040994 | 0.000000 | 376 | 0 || Queried about 44880000 rows | 0.046065 | 0.044993 | 0.000000 | 443 | 0 || Queried about 44890000 rows | 0.053119 | 0.043994 | 0.000000 | 611 | 0 || Queried about 44900000 rows | 0.047747 | 0.042993 | 0.000000 | 546 | 0 || Queried about 44910000 rows | 0.046893 | 0.043993 | 0.000000 | 470 | 0 || Queried about 44920000 rows | 0.047757 | 0.044994 | 0.000000 | 546 | 0 || Queried about 44930000 rows | 0.058168 | 0.044993 | 0.000000 | 517 | 0 || Queried about 44940000 rows | 0.047911 | 0.042993 | 0.001000 | 514 | 0 || Queried about 44950000 rows | 0.047323 | 0.043994 | 0.000000 | 434 | 0 || Queried about 44960000 rows | 0.047408 | 0.044993 | 0.000000 | 522 | 0 || Queried about 44970000 rows | 0.054083 | 0.041993 | 0.002000 | 451 | 0 || Queried about 44980000 rows | 0.047874 | 0.042994 | 0.000999 | 414 | 0 || Queried about 44990000 rows | 0.044854 | 0.040994 | 0.001000 | 361 | 0 || Queried about 45000000 rows | 0.044495 | 0.041993 | 0.000000 | 359 | 0 || Queried about 45010000 rows | 0.046180 | 0.041994 | 0.001000 | 423 | 0 || Queried about 45020000 rows | 0.054907 | 0.040994 | 0.001000 | 428 | 0 || Queried about 45030000 rows | 0.044274 | 0.038994 | 0.003999 | 359 | 0 || Queried about 45040000 rows | 0.045585 | 0.039994 | 0.002000 | 419 | 0 || Queried about 45050000 rows | 0.045103 | 0.042993 | 0.000000 | 362 | 0 || Queried about 45060000 rows | 0.057961 | 0.043993 | 0.000000 | 419 | 0 || Queried about 45070000 rows | 0.045552 | 0.041994 | 0.001000 | 408 | 0 || Queried about 45080000 rows | 0.045388 | 0.041994 | 0.000000 | 363 | 0 || Queried about 45090000 rows | 0.044844 | 0.042993 | 0.001000 | 357 | 0 || Queried about 45100000 rows | 0.050970 | 0.047993 | 0.000000 | 364 | 0 || Queried about 45110000 rows | 0.050254 | 0.041993 | 0.000000 | 558 | 0 || Queried about 45120000 rows | 0.044770 | 0.041994 | 0.000000 | 443 | 0 || Queried about 45130000 rows | 0.045107 | 0.041994 | 0.000000 | 391 | 0 || Queried about 45140000 rows | 0.045265 | 0.042993 | 0.000000 | 437 | 0 || Queried about 45150000 rows | 0.046857 | 0.040994 | 0.001000 | 373 | 0 || Queried about 45160000 rows | 0.055035 | 0.039994 | 0.001999 | 368 | 0 || Queried about 45170000 rows | 0.036706 | 0.034994 | 0.000000 | 165 | 0 || Queried about 45180000 rows | 0.029045 | 0.028996 | 0.000000 | 0 | 0 || Queried about 45190000 rows | 0.029261 | 0.027996 | 0.001000 | 0 | 0 || Queried about 45200000 rows | 0.027392 | 0.027996 | 0.000000 | 0 | 0 || end | 0.024045 | 0.000000 | 0.000000 | 184 | 0 || query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000016 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000052 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000006 | 0.000000 | 0.000000 | 0 | 0 || cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |+-----------------------------+----------+----------+------------+--------------+---------------+100 rows in set (0.02 sec)
第二个表的查询
说明：第二个表的表结构和第一个表完全一样，第二个表的数据是从第一个表通过下面SQL插入的 insert into sjkk_gcjl_asc select * from sjkk_gcjl order by jgs,jlbh;
select jgsj,jlbh,hphm,clsd from sjkk_gcjl_asc force index(index09) where jgsj>='2010-01-20 00:00:00' and jgsj<='2015-05-20 00:00:00' and hphm like '_BX797_' and csys = 'Z' order by jgsj desc ,jlbh desc;---物理读40 rows in set (1 min 8.35 sec)---内存读+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+| 1 | SIMPLE | sjkk_gcjl_asc | range | index09 | index09 | 25 | NULL | 46377889 | Using where; Using index |+----+-------------+---------------+-------+---------------+---------+---------+------+----------+--------------------------+show profiles;mysql> show profile cpu,block io for query 2;+-----------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+-----------------------------+----------+----------+------------+--------------+---------------+| Queried about 44280000 rows | 0.005955 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44290000 rows | 0.042468 | 0.010998 | 0.002999 | 1619 | 0 || Queried about 44300000 rows | 0.005950 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44310000 rows | 0.006121 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44320000 rows | 0.042170 | 0.012998 | 0.003000 | 1662 | 0 || Queried about 44330000 rows | 0.005945 | 0.004999 | 0.000000 | 0 | 0 || Queried about 44340000 rows | 0.006141 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44350000 rows | 0.006135 | 0.006998 | 0.000000 | 0 | 0 || Queried about 44360000 rows | 0.041770 | 0.009999 | 0.003999 | 1657 | 0 || Queried about 44370000 rows | 0.006134 | 0.006999 | 0.000000 | 0 | 0 || Queried about 44380000 rows | 0.005915 | 0.004999 | 0.000000 | 0 | 0 || Queried about 44390000 rows | 0.006116 | 0.006999 | 0.000000 | 0 | 0 || Queried about 44400000 rows | 0.041795 | 0.009999 | 0.003999 | 1630 | 0 || Queried about 44410000 rows | 0.005919 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44420000 rows | 0.006112 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44430000 rows | 0.005928 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44440000 rows | 0.042600 | 0.014998 | 0.000000 | 1457 | 0 || Queried about 44450000 rows | 0.006103 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44460000 rows | 0.005897 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44470000 rows | 0.042726 | 0.009998 | 0.005000 | 1656 | 0 || Queried about 44480000 rows | 0.006167 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44490000 rows | 0.005930 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44500000 rows | 0.006144 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44510000 rows | 0.041940 | 0.014998 | 0.000000 | 1644 | 0 || Queried about 44520000 rows | 0.006126 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44530000 rows | 0.006119 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44540000 rows | 0.005932 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44550000 rows | 0.041693 | 0.010999 | 0.001999 | 1024 | 0 || Queried about 44560000 rows | 0.005883 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44570000 rows | 0.006085 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44580000 rows | 0.019374 | 0.007999 | 0.005000 | 968 | 0 || Queried about 44590000 rows | 0.005881 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44600000 rows | 0.006076 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44610000 rows | 0.005867 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44620000 rows | 0.038987 | 0.010998 | 0.001999 | 1029 | 0 || Queried about 44630000 rows | 0.006071 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44640000 rows | 0.005888 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44650000 rows | 0.006080 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44660000 rows | 0.042528 | 0.009998 | 0.003000 | 955 | 0 || Queried about 44670000 rows | 0.005902 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44680000 rows | 0.006063 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44690000 rows | 0.035778 | 0.009999 | 0.002000 | 903 | 0 || Queried about 44700000 rows | 0.006052 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44710000 rows | 0.006074 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44720000 rows | 0.005863 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44730000 rows | 0.021200 | 0.010998 | 0.002999 | 943 | 0 || Queried about 44740000 rows | 0.005869 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44750000 rows | 0.006057 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44760000 rows | 0.006054 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44770000 rows | 0.038302 | 0.012998 | 0.001000 | 1359 | 0 || Queried about 44780000 rows | 0.006099 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44790000 rows | 0.006141 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44800000 rows | 0.042922 | 0.011998 | 0.002999 | 1670 | 0 || Queried about 44810000 rows | 0.006160 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44820000 rows | 0.005936 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44830000 rows | 0.006132 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44840000 rows | 0.042036 | 0.011998 | 0.003000 | 1645 | 0 || Queried about 44850000 rows | 0.005928 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44860000 rows | 0.006145 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44870000 rows | 0.005922 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44880000 rows | 0.033826 | 0.013998 | 0.001000 | 1836 | 0 || Queried about 44890000 rows | 0.006056 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44900000 rows | 0.006058 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44910000 rows | 0.045155 | 0.015997 | 0.002999 | 1782 | 0 || Queried about 44920000 rows | 0.006093 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44930000 rows | 0.006091 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44940000 rows | 0.005919 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44950000 rows | 0.030402 | 0.013997 | 0.001000 | 1663 | 0 || Queried about 44960000 rows | 0.006089 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44970000 rows | 0.006129 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44980000 rows | 0.041108 | 0.013997 | 0.000000 | 1540 | 0 || Queried about 44990000 rows | 0.006107 | 0.006000 | 0.000000 | 0 | 0 || Queried about 45000000 rows | 0.006102 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45010000 rows | 0.005912 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45020000 rows | 0.042697 | 0.011998 | 0.002000 | 1533 | 0 || Queried about 45030000 rows | 0.005897 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45040000 rows | 0.006114 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45050000 rows | 0.006091 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45060000 rows | 0.042715 | 0.013998 | 0.000000 | 1505 | 0 || Queried about 45070000 rows | 0.006144 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45080000 rows | 0.006115 | 0.006999 | 0.000000 | 0 | 0 || Queried about 45090000 rows | 0.042440 | 0.012998 | 0.001000 | 1717 | 0 || Queried about 45100000 rows | 0.006098 | 0.004999 | 0.000000 | 0 | 0 || Queried about 45110000 rows | 0.005927 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45120000 rows | 0.006125 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45130000 rows | 0.041342 | 0.014998 | 0.000000 | 1570 | 0 || Queried about 45140000 rows | 0.005660 | 0.004999 | 0.000000 | 0 | 0 || Queried about 45150000 rows | 0.005827 | 0.006000 | 0.000000 | 0 | 0 || Queried about 45160000 rows | 0.005633 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45170000 rows | 0.035292 | 0.012998 | 0.000000 | 1624 | 0 || Queried about 45180000 rows | 0.005868 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45190000 rows | 0.005663 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45200000 rows | 0.005438 | 0.004999 | 0.000000 | 0 | 0 || end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000046 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || cleaning up | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |+-----------------------------+----------+----------+------------+--------------+---------------+从上面看出，上面的SQL block_ops_in比较集中，相对于第一个SQL.
第三个查询
说明：第三个表的表结构和第一个表完全一样，第三个表的数据是从第一个表通过下面SQL插入的 insert into sjkk_gcjl_desc select * from sjkk_gcjl order by jgs desc,jlbh desc;
select jgsj,jlbh,hphm,clsd from sjkk_gcjl_desc force index(index09) where jgsj>='2010-01-20 00:00:00' and jgsj<='2015-05-20 00:00:00' and hphm like '_BX797_' and csys = 'Z' order by jgsj desc ,jlbh desc;---物理读40 rows in set (1 min 4.04 sec)---内存读+----+-------------+----------------+-------+---------------+---------+---------+------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------------+-------+---------------+---------+---------+------+----------+--------------------------+| 1 | SIMPLE | sjkk_gcjl_desc | range | index09 | index09 | 25 | NULL | 46379740 | Using where; Using index |+----+-------------+----------------+-------+---------------+---------+---------+------+----------+--------------------------+show profiles;mysql> show profile cpu,block io for query 1;+-----------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+-----------------------------+----------+----------+------------+--------------+---------------+| Queried about 44280000 rows | 0.005927 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44290000 rows | 0.044303 | 0.011999 | 0.002000 | 1628 | 0 || Queried about 44300000 rows | 0.005684 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44310000 rows | 0.005887 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44320000 rows | 0.022284 | 0.012998 | 0.002000 | 1667 | 0 || Queried about 44330000 rows | 0.005680 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44340000 rows | 0.005878 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44350000 rows | 0.005856 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44360000 rows | 0.030696 | 0.009999 | 0.002999 | 1659 | 0 || Queried about 44370000 rows | 0.005856 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44380000 rows | 0.005664 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44390000 rows | 0.005856 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44400000 rows | 0.044768 | 0.014998 | 0.000000 | 1633 | 0 || Queried about 44410000 rows | 0.005821 | 0.004999 | 0.000000 | 0 | 0 || Queried about 44420000 rows | 0.006006 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44430000 rows | 0.005807 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44440000 rows | 0.046247 | 0.011998 | 0.002000 | 1460 | 0 || Queried about 44450000 rows | 0.006074 | 0.006999 | 0.000000 | 0 | 0 || Queried about 44460000 rows | 0.005845 | 0.004999 | 0.000000 | 0 | 0 || Queried about 44470000 rows | 0.022606 | 0.012998 | 0.002999 | 1658 | 0 || Queried about 44480000 rows | 0.006136 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44490000 rows | 0.005893 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44500000 rows | 0.006090 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44510000 rows | 0.045146 | 0.013998 | 0.000000 | 1646 | 0 || Queried about 44520000 rows | 0.006114 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44530000 rows | 0.006068 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44540000 rows | 0.005887 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44550000 rows | 0.019728 | 0.010998 | 0.003000 | 1024 | 0 || Queried about 44560000 rows | 0.005840 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44570000 rows | 0.006025 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44580000 rows | 0.018998 | 0.011998 | 0.001000 | 967 | 0 || Queried about 44590000 rows | 0.005826 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44600000 rows | 0.006036 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44610000 rows | 0.005810 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44620000 rows | 0.043835 | 0.010998 | 0.001000 | 1030 | 0 || Queried about 44630000 rows | 0.006012 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44640000 rows | 0.005848 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44650000 rows | 0.006026 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44660000 rows | 0.019258 | 0.011998 | 0.001999 | 955 | 0 || Queried about 44670000 rows | 0.005835 | 0.005000 | 0.000000 | 0 | 0 || Queried about 44680000 rows | 0.006033 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44690000 rows | 0.018489 | 0.009998 | 0.003000 | 903 | 0 || Queried about 44700000 rows | 0.006004 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44710000 rows | 0.006025 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44720000 rows | 0.005836 | 0.006000 | 0.000000 | 0 | 0 || Queried about 44730000 rows | 0.039645 | 0.008998 | 0.003999 | 943 | 0 || Queried about 44740000 rows | 0.005819 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44750000 rows | 0.006022 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44760000 rows | 0.006035 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44770000 rows | 0.021059 | 0.011999 | 0.002000 | 1359 | 0 || Queried about 44780000 rows | 0.006051 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44790000 rows | 0.006091 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44800000 rows | 0.042813 | 0.011998 | 0.002999 | 1671 | 0 || Queried about 44810000 rows | 0.006083 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44820000 rows | 0.005889 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44830000 rows | 0.006079 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44840000 rows | 0.022479 | 0.013998 | 0.001000 | 1645 | 0 || Queried about 44850000 rows | 0.005885 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44860000 rows | 0.006095 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44870000 rows | 0.005889 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44880000 rows | 0.034940 | 0.010999 | 0.005000 | 1842 | 0 || Queried about 44890000 rows | 0.006024 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44900000 rows | 0.006010 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44910000 rows | 0.044673 | 0.012998 | 0.000999 | 1789 | 0 || Queried about 44920000 rows | 0.006044 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44930000 rows | 0.006048 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44940000 rows | 0.005873 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44950000 rows | 0.030599 | 0.013998 | 0.001000 | 1668 | 0 || Queried about 44960000 rows | 0.006038 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44970000 rows | 0.006092 | 0.005999 | 0.000000 | 0 | 0 || Queried about 44980000 rows | 0.029064 | 0.009999 | 0.004000 | 1540 | 0 || Queried about 44990000 rows | 0.006067 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45000000 rows | 0.006057 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45010000 rows | 0.005859 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45020000 rows | 0.029620 | 0.011998 | 0.001999 | 1533 | 0 || Queried about 45030000 rows | 0.005860 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45040000 rows | 0.006086 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45050000 rows | 0.006052 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45060000 rows | 0.029755 | 0.010999 | 0.002000 | 1505 | 0 || Queried about 45070000 rows | 0.006110 | 0.006999 | 0.000000 | 0 | 0 || Queried about 45080000 rows | 0.006071 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45090000 rows | 0.045540 | 0.009998 | 0.003999 | 1717 | 0 || Queried about 45100000 rows | 0.006054 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45110000 rows | 0.005883 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45120000 rows | 0.006083 | 0.006000 | 0.000000 | 0 | 0 || Queried about 45130000 rows | 0.021880 | 0.012998 | 0.002000 | 1570 | 0 || Queried about 45140000 rows | 0.005790 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45150000 rows | 0.005975 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45160000 rows | 0.005816 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45170000 rows | 0.029333 | 0.010998 | 0.002000 | 1623 | 0 || Queried about 45180000 rows | 0.006019 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45190000 rows | 0.005829 | 0.005999 | 0.000000 | 0 | 0 || Queried about 45200000 rows | 0.005598 | 0.005000 | 0.000000 | 0 | 0 || end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000054 | 0.000000 | 0.001000 | 0 | 0 || logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 || cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |+-----------------------------+----------+----------+------------+--------------+---------------+从上面看出，上面的SQL block_ops_in也比较集中，相对于第一个SQL.
继续测试
因为上面的查询是覆盖索引方式走的index09，我怀疑是index09不够紧凑，下面试着重新建立下Index09。看可以解决不
select jgsj,jlbh,hphm,clsd from sjkk_gcjl force index(index09) where jgsj>='2010-01-20 00:00:00' and jgsj<='2015-05-20 00:00:00' and hphm like '_BX797_' and csys = 'Z' order by jgsj desc ,jlbh desc;---物理读40 rows in set (4 min 2.70 sec) show profiles; mysql> show profile cpu,block io for query 2;+-----------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+-----------------------------+----------+----------+------------+--------------+---------------+| Queried about 44280000 rows | 0.047981 | 0.042994 | 0.001999 | 409 | 0 || Queried about 44290000 rows | 0.049289 | 0.046993 | 0.000000 | 478 | 0 || Queried about 44300000 rows | 0.046801 | 0.044993 | 0.000000 | 421 | 0 || Queried about 44310000 rows | 0.058415 | 0.043993 | 0.000000 | 496 | 0 || Queried about 44320000 rows | 0.049964 | 0.043993 | 0.000000 | 502 | 0 || Queried about 44330000 rows | 0.046654 | 0.041994 | 0.002000 | 431 | 0 || Queried about 44340000 rows | 0.047051 | 0.043993 | 0.000000 | 433 | 0 || Queried about 44350000 rows | 0.056640 | 0.044994 | 0.000000 | 569 | 0 || Queried about 44360000 rows | 0.047047 | 0.042993 | 0.000000 | 434 | 0 || Queried about 44370000 rows | 0.046466 | 0.043993 | 0.000000 | 432 | 0 || Queried about 44380000 rows | 0.046911 | 0.044993 | 0.000000 | 431 | 0 || Queried about 44390000 rows | 0.047349 | 0.043994 | 0.001000 | 504 | 0 || Queried about 44400000 rows | 0.057259 | 0.043993 | 0.000000 | 498 | 0 || Queried about 44410000 rows | 0.045991 | 0.041994 | 0.001000 | 424 | 0 || Queried about 44420000 rows | 0.053157 | 0.049992 | 0.000000 | 491 | 0 || Queried about 44430000 rows | 0.045555 | 0.042994 | 0.000999 | 403 | 0 || Queried about 44440000 rows | 0.058867 | 0.042993 | 0.001000 | 462 | 0 || Queried about 44450000 rows | 0.051197 | 0.042993 | 0.001000 | 392 | 0 || Queried about 44460000 rows | 0.048520 | 0.045993 | 0.001000 | 325 | 0 || Queried about 44470000 rows | 0.048085 | 0.042994 | 0.002000 | 433 | 0 || Queried about 44480000 rows | 0.049237 | 0.044993 | 0.000000 | 505 | 0 || Queried about 44490000 rows | 0.051492 | 0.043993 | 0.000000 | 498 | 0 || Queried about 44500000 rows | 0.047241 | 0.044994 | 0.000000 | 432 | 0 || Queried about 44510000 rows | 0.048281 | 0.044993 | 0.000000 | 506 | 0 || Queried about 44520000 rows | 0.047653 | 0.044993 | 0.001000 | 432 | 0 || Queried about 44530000 rows | 0.055559 | 0.042993 | 0.001999 | 496 | 0 || Queried about 44540000 rows | 0.047502 | 0.043994 | 0.001000 | 501 | 0 || Queried about 44550000 rows | 0.052827 | 0.049992 | 0.000000 | 253 | 0 || Queried about 44560000 rows | 0.053259 | 0.050992 | 0.002000 | 257 | 0 || Queried about 44570000 rows | 0.054896 | 0.052992 | 0.000000 | 241 | 0 || Queried about 44580000 rows | 0.064943 | 0.052992 | 0.000000 | 333 | 0 || Queried about 44590000 rows | 0.053339 | 0.049993 | 0.001000 | 227 | 0 || Queried about 44600000 rows | 0.052507 | 0.047992 | 0.001999 | 236 | 0 || Queried about 44610000 rows | 0.053706 | 0.051992 | 0.000000 | 275 | 0 || Queried about 44620000 rows | 0.059879 | 0.052992 | 0.000000 | 310 | 0 || Queried about 44630000 rows | 0.052048 | 0.047993 | 0.002000 | 257 | 0 || Queried about 44640000 rows | 0.052868 | 0.047993 | 0.002000 | 315 | 0 || Queried about 44650000 rows | 0.052772 | 0.049992 | 0.001999 | 237 | 0 || Queried about 44660000 rows | 0.053842 | 0.051992 | 0.000000 | 228 | 0 || Queried about 44670000 rows | 0.058972 | 0.049993 | 0.001000 | 333 | 0 || Queried about 44680000 rows | 0.053328 | 0.050992 | 0.000000 | 226 | 0 || Queried about 44690000 rows | 0.053196 | 0.050992 | 0.001000 | 237 | 0 || Queried about 44700000 rows | 0.057762 | 0.055992 | 0.000000 | 239 | 0 || Queried about 44710000 rows | 0.068532 | 0.053991 | 0.000000 | 282 | 0 || Queried about 44720000 rows | 0.052845 | 0.050993 | 0.000000 | 226 | 0 || Queried about 44730000 rows | 0.055047 | 0.052992 | 0.000000 | 249 | 0 || Queried about 44740000 rows | 0.054275 | 0.052992 | 0.000000 | 219 | 0 || Queried about 44750000 rows | 0.060813 | 0.047992 | 0.003000 | 293 | 0 || Queried about 44760000 rows | 0.051905 | 0.049993 | 0.001000 | 230 | 0 || Queried about 44770000 rows | 0.061900 | 0.047992 | 0.001999 | 286 | 0 || Queried about 44780000 rows | 0.051060 | 0.046993 | 0.002000 | 289 | 0 || Queried about 44790000 rows | 0.047671 | 0.044993 | 0.000000 | 433 | 0 || Queried about 44800000 rows | 0.052504 | 0.043994 | 0.000000 | 574 | 0 || Queried about 44810000 rows | 0.046545 | 0.043993 | 0.000000 | 437 | 0 || Queried about 44820000 rows | 0.046893 | 0.043993 | 0.000000 | 436 | 0 || Queried about 44830000 rows | 0.046978 | 0.043994 | 0.001000 | 510 | 0 || Queried about 44840000 rows | 0.056722 | 0.042993 | 0.001000 | 501 | 0 || Queried about 44850000 rows | 0.048469 | 0.043993 | 0.001999 | 439 | 0 || Queried about 44860000 rows | 0.046912 | 0.043994 | 0.000000 | 485 | 0 || Queried about 44870000 rows | 0.044422 | 0.041993 | 0.000000 | 376 | 0 || Queried about 44880000 rows | 0.046006 | 0.042994 | 0.000000 | 443 | 0 || Queried about 44890000 rows | 0.061753 | 0.043993 | 0.000000 | 611 | 0 || Queried about 44900000 rows | 0.047805 | 0.045993 | 0.000000 | 546 | 0 || Queried about 44910000 rows | 0.047309 | 0.043993 | 0.000000 | 470 | 0 || Queried about 44920000 rows | 0.047503 | 0.043994 | 0.002000 | 546 | 0 || Queried about 44930000 rows | 0.053789 | 0.041993 | 0.001000 | 517 | 0 || Queried about 44940000 rows | 0.047566 | 0.043994 | 0.001000 | 514 | 0 || Queried about 44950000 rows | 0.048600 | 0.044993 | 0.001000 | 434 | 0 || Queried about 44960000 rows | 0.047446 | 0.043993 | 0.000999 | 522 | 0 || Queried about 44970000 rows | 0.052413 | 0.039994 | 0.002000 | 451 | 0 || Queried about 44980000 rows | 0.044823 | 0.041994 | 0.000000 | 414 | 0 || Queried about 44990000 rows | 0.044420 | 0.039994 | 0.002000 | 361 | 0 || Queried about 45000000 rows | 0.044001 | 0.041993 | 0.001000 | 359 | 0 || Queried about 45010000 rows | 0.045354 | 0.041994 | 0.000000 | 423 | 0 || Queried about 45020000 rows | 0.049576 | 0.040994 | 0.000999 | 428 | 0 || Queried about 45030000 rows | 0.043736 | 0.040993 | 0.000000 | 359 | 0 || Queried about 45040000 rows | 0.045017 | 0.042994 | 0.000000 | 419 | 0 || Queried about 45050000 rows | 0.044125 | 0.041993 | 0.001000 | 362 | 0 || Queried about 45060000 rows | 0.050496 | 0.040994 | 0.001000 | 419 | 0 || Queried about 45070000 rows | 0.044514 | 0.041994 | 0.000000 | 408 | 0 || Queried about 45080000 rows | 0.044797 | 0.039994 | 0.001000 | 363 | 0 || Queried about 45090000 rows | 0.044068 | 0.042993 | 0.000000 | 357 | 0 || Queried about 45100000 rows | 0.044654 | 0.042994 | 0.000000 | 364 | 0 || Queried about 45110000 rows | 0.049599 | 0.041993 | 0.000000 | 558 | 0 || Queried about 45120000 rows | 0.044183 | 0.040994 | 0.000000 | 443 | 0 || Queried about 45130000 rows | 0.044694 | 0.042994 | 0.000000 | 391 | 0 || Queried about 45140000 rows | 0.044786 | 0.039993 | 0.002000 | 437 | 0 || Queried about 45150000 rows | 0.048630 | 0.041994 | 0.000000 | 373 | 0 || Queried about 45160000 rows | 0.044343 | 0.040994 | 0.000000 | 368 | 0 || Queried about 45170000 rows | 0.037987 | 0.035994 | 0.000000 | 165 | 0 || Queried about 45180000 rows | 0.029176 | 0.027996 | 0.001000 | 0 | 0 || Queried about 45190000 rows | 0.029162 | 0.029996 | 0.000000 | 0 | 0 || Queried about 45200000 rows | 0.027520 | 0.025996 | 0.000999 | 0 | 0 || end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000052 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000007 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000006 | 0.000000 | 0.000000 | 0 | 0 || cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |+-----------------------------+----------+----------+------------+--------------+---------------+100 rows in set (0.00 sec)
冲上面的测试看，重建索引不能解决问题，感觉很奇怪,查询又不需要回表。
继续测试
使用 optimize table sjkk_gcjl;对标优化下看看。
mysql> optimize table sjkk_gcjl;+----------------------+----------+----------+----------+| Table | Op | Msg_type | Msg_text |+----------------------+----------+----------+----------+| netposa229.sjkk_gcjl | optimize | status | OK |+----------------------+----------+----------+----------+1 row in set (4 min 14.94 sec)select jgsj,jlbh,hphm,clsd from sjkk_gcjl force index(index09) where jgsj>='2010-01-20 00:00:00' and jgsj<='2015-05-20 00:00:00' and hphm like '_BX797_' and csys = 'Z' order by jgsj desc ,jlbh desc;---物理读40 rows in set (4 min 26.05 sec)+----+-------------+-----------+-------+---------------+---------+---------+------+----------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-----------+-------+---------------+---------+---------+------+----------+----------+--------------------------+| 1 | SIMPLE | sjkk_gcjl | range | index09 | index09 | 25 | NULL | 43711899 | 100.00 | Using where; Using index |+----+-------------+-----------+-------+---------------+---------+---------+------+----------+----------+--------------------------+ show profiles; mysql> show profile cpu,block io for query 4;mysql> show profile cpu,block io for query 4;+-----------------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+-----------------------------+----------+----------+------------+--------------+---------------+| Queried about 44280000 rows | 0.048180 | 0.042994 | 0.002000 | 409 | 0 || Queried about 44290000 rows | 0.046983 | 0.045993 | 0.000000 | 478 | 0 || Queried about 44300000 rows | 0.046778 | 0.043993 | 0.000000 | 421 | 0 || Queried about 44310000 rows | 0.059664 | 0.042994 | 0.000999 | 495 | 0 || Queried about 44320000 rows | 0.049965 | 0.042993 | 0.001000 | 502 | 0 || Queried about 44330000 rows | 0.046562 | 0.042994 | 0.002000 | 431 | 0 || Queried about 44340000 rows | 0.047074 | 0.043993 | 0.000000 | 433 | 0 || Queried about 44350000 rows | 0.055568 | 0.044993 | 0.000000 | 569 | 0 || Queried about 44360000 rows | 0.047056 | 0.041994 | 0.001000 | 434 | 0 || Queried about 44370000 rows | 0.046975 | 0.043993 | 0.000000 | 432 | 0 || Queried about 44380000 rows | 0.046863 | 0.044993 | 0.000000 | 431 | 0 || Queried about 44390000 rows | 0.047736 | 0.044993 | 0.000000 | 504 | 0 || Queried about 44400000 rows | 0.056081 | 0.044993 | 0.000000 | 498 | 0 || Queried about 44410000 rows | 0.046275 | 0.042994 | 0.000000 | 424 | 0 || Queried about 44420000 rows | 0.047872 | 0.044993 | 0.000000 | 491 | 0 || Queried about 44430000 rows | 0.046044 | 0.043993 | 0.000000 | 403 | 0 || Queried about 44440000 rows | 0.056768 | 0.042994 | 0.001000 | 462 | 0 || Queried about 44450000 rows | 0.048922 | 0.044993 | 0.001000 | 392 | 0 || Queried about 44460000 rows | 0.050220 | 0.046993 | 0.000000 | 325 | 0 || Queried about 44470000 rows | 0.046831 | 0.043993 | 0.000999 | 433 | 0 || Queried about 44480000 rows | 0.048140 | 0.044993 | 0.000000 | 505 | 0 || Queried about 44490000 rows | 0.059661 | 0.043994 | 0.002000 | 498 | 0 || Queried about 44500000 rows | 0.051066 | 0.043993 | 0.000000 | 432 | 0 || Queried about 44510000 rows | 0.047523 | 0.043993 | 0.000000 | 506 | 0 || Queried about 44520000 rows | 0.048618 | 0.046993 | 0.000000 | 433 | 0 || Queried about 44530000 rows | 0.051525 | 0.046993 | 0.000000 | 496 | 0 || Queried about 44540000 rows | 0.047629 | 0.043993 | 0.001000 | 500 | 0 || Queried about 44550000 rows | 0.052342 | 0.049993 | 0.000000 | 253 | 0 || Queried about 44560000 rows | 0.051985 | 0.049992 | 0.001000 | 257 | 0 || Queried about 44570000 rows | 0.053609 | 0.051992 | 0.000000 | 241 | 0 || Queried about 44580000 rows | 0.068776 | 0.051992 | 0.002000 | 333 | 0 || Queried about 44590000 rows | 0.054949 | 0.052992 | 0.000000 | 227 | 0 || Queried about 44600000 rows | 0.054195 | 0.051992 | 0.000000 | 236 | 0 || Queried about 44610000 rows | 0.055317 | 0.052992 | 0.000000 | 275 | 0 || Queried about 44620000 rows | 0.065600 | 0.051992 | 0.001999 | 310 | 0 || Queried about 44630000 rows | 0.060621 | 0.049993 | 0.001000 | 257 | 0 || Queried about 44640000 rows | 0.054590 | 0.051992 | 0.001000 | 315 | 0 || Queried about 44650000 rows | 0.054941 | 0.049992 | 0.002999 | 238 | 0 || Queried about 44660000 rows | 0.055665 | 0.054992 | 0.000000 | 228 | 0 || Queried about 44670000 rows | 0.061768 | 0.051992 | 0.000000 | 333 | 0 || Queried about 44680000 rows | 0.055036 | 0.052992 | 0.000000 | 226 | 0 || Queried about 44690000 rows | 0.055625 | 0.053992 | 0.001000 | 237 | 0 || Queried about 44700000 rows | 0.060093 | 0.057991 | 0.000000 | 239 | 0 || Queried about 44710000 rows | 0.068402 | 0.055991 | 0.000000 | 282 | 0 || Queried about 44720000 rows | 0.054897 | 0.053992 | 0.000000 | 226 | 0 || Queried about 44730000 rows | 0.057174 | 0.053992 | 0.000000 | 249 | 0 || Queried about 44740000 rows | 0.056482 | 0.054992 | 0.000000 | 219 | 0 || Queried about 44750000 rows | 0.064511 | 0.053991 | 0.000000 | 294 | 0 || Queried about 44760000 rows | 0.055568 | 0.051993 | 0.000000 | 230 | 0 || Queried about 44770000 rows | 0.055456 | 0.051992 | 0.001000 | 286 | 0 || Queried about 44780000 rows | 0.052426 | 0.049992 | 0.001000 | 289 | 0 || Queried about 44790000 rows | 0.048669 | 0.046993 | 0.000000 | 433 | 0 || Queried about 44800000 rows | 0.073950 | 0.045993 | 0.000000 | 574 | 0 || Queried about 44810000 rows | 0.067546 | 0.043993 | 0.001000 | 437 | 0 || Queried about 44820000 rows | 0.050068 | 0.045993 | 0.001000 | 436 | 0 || Queried about 44830000 rows | 0.063407 | 0.058991 | 0.001000 | 510 | 0 || Queried about 44840000 rows | 0.055836 | 0.044993 | 0.000000 | 501 | 0 || Queried about 44850000 rows | 0.047153 | 0.040994 | 0.001999 | 439 | 0 || Queried about 44860000 rows | 0.046830 | 0.043994 | 0.000000 | 485 | 0 || Queried about 44870000 rows | 0.044392 | 0.041993 | 0.000000 | 376 | 0 || Queried about 44880000 rows | 0.045943 | 0.043993 | 0.000000 | 443 | 0 || Queried about 44890000 rows | 0.052560 | 0.042994 | 0.001000 | 611 | 0 || Queried about 44900000 rows | 0.048934 | 0.042993 | 0.002000 | 546 | 0 || Queried about 44910000 rows | 0.048209 | 0.044994 | 0.001000 | 470 | 0 || Queried about 44920000 rows | 0.049413 | 0.044993 | 0.001999 | 546 | 0 || Queried about 44930000 rows | 0.067731 | 0.044993 | 0.001000 | 517 | 0 || Queried about 44940000 rows | 0.049143 | 0.043993 | 0.001000 | 514 | 0 || Queried about 44950000 rows | 0.048553 | 0.044993 | 0.000000 | 434 | 0 || Queried about 44960000 rows | 0.048589 | 0.046993 | 0.000000 | 522 | 0 || Queried about 44970000 rows | 0.068567 | 0.050992 | 0.000000 | 451 | 0 || Queried about 44980000 rows | 0.051476 | 0.042994 | 0.000000 | 414 | 0 || Queried about 44990000 rows | 0.045015 | 0.041994 | 0.001000 | 361 | 0 || Queried about 45000000 rows | 0.046254 | 0.042993 | 0.001000 | 359 | 0 || Queried about 45010000 rows | 0.048540 | 0.044993 | 0.000000 | 422 | 0 || Queried about 45020000 rows | 0.046209 | 0.041994 | 0.001000 | 428 | 0 || Queried about 45030000 rows | 0.045563 | 0.043993 | 0.000000 | 359 | 0 || Queried about 45040000 rows | 0.046440 | 0.042994 | 0.000999 | 419 | 0 || Queried about 45050000 rows | 0.046068 | 0.042993 | 0.001000 | 362 | 0 || Queried about 45060000 rows | 0.061211 | 0.042993 | 0.000000 | 419 | 0 || Queried about 45070000 rows | 0.078615 | 0.041994 | 0.002000 | 408 | 0 || Queried about 45080000 rows | 0.046370 | 0.042994 | 0.001000 | 363 | 0 || Queried about 45090000 rows | 0.046313 | 0.044993 | 0.000000 | 357 | 0 || Queried about 45100000 rows | 0.046137 | 0.043993 | 0.000000 | 364 | 0 || Queried about 45110000 rows | 0.065999 | 0.042993 | 0.000000 | 558 | 0 || Queried about 45120000 rows | 0.045209 | 0.043994 | 0.000000 | 443 | 0 || Queried about 45130000 rows | 0.046324 | 0.041993 | 0.001000 | 391 | 0 || Queried about 45140000 rows | 0.047024 | 0.044994 | 0.000000 | 437 | 0 || Queried about 45150000 rows | 0.045854 | 0.042993 | 0.000000 | 373 | 0 || Queried about 45160000 rows | 0.044906 | 0.042993 | 0.000000 | 368 | 0 || Queried about 45170000 rows | 0.037076 | 0.034995 | 0.000999 | 165 | 0 || Queried about 45180000 rows | 0.030259 | 0.028996 | 0.001000 | 0 | 0 || Queried about 45190000 rows | 0.030329 | 0.030995 | 0.000000 | 0 | 0 || Queried about 45200000 rows | 0.028528 | 0.026996 | 0.001000 | 0 | 0 || end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000015 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000060 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || cleaning up | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |+-----------------------------+----------+----------+------------+--------------+---------------+100 rows in set (0.02 sec)<span style="color:#FF0000;"></span>使用 optimize table后更慢了。
疑问：1、这个profile中的Queried about这些为啥要在不同的行，感觉应该有含义，不知道是什么意思。并且发现block_ops_in比较集中的，查询速度快。
2、上面sjkk_gcjl、sjkk_gcjl_asc、sjkk_gcjl_desc表结构和数据都是完全一样的，区别就在于插入的顺序。
查询时又只使用了索引index09，难道原表的数据顺序也影响到了索引？
知道原因的大神，留言告知下吧，谢谢
0 0
- 数据和表结构完全一样的两个表，查询性能相差一倍多
- 怎样将结构完全一样的两个表的内容合并到一个表中，SQL语句
- 怎样将结构完全一样的两个表的内容合并到一个表中，SQL语句
- 怎样将结构完全一样的两个表的内容合并到一个表中，SQL语句
- sql查询案例:删除2条完全一样的数据
- 求两个表相差
- 合并两个结构一样的dataset数据集
- SQL 两张结构一样的表合并查询
- 同一个表中查询不同的数据,执行时间相差很远,是什么原因?
- Oracle 创建和视图结构一样的表
- 查询两个表的数据 union
- CPU和硬盘的性能相差多少！
- SQL Server 表结构和数据完全复制(可跨库)
- 连接两个表查询数据
- oracle查询出树形结构的表，父节点数据是子节点的数据和
- 两个数据库间复制表结构和数据
- 两个时间数据求它的相差小时，分钟等等
- 计算两个日期型数据相差的天数
- 【算法总结-数组相关】 数组中找特定元素相关~
- vmware workstation 11 安装RAC第二问
- 优化算法——截断梯度法(TG)
- CALayer
- Leetcode#12||Integer to Roman
- 数据和表结构完全一样的两个表，查询性能相差一倍多
- 你的Matlab安装路径，你可以在Matlab中输入matlabroot命令获取
- 【算法总结-DP】求子数组的最大和
- 核心动画
- Linux操作学习笔记------Linux安装前的工作。
- 内存对齐
- 指向二维数组的元素
- zookeeper 配置文件说明（zoo.cfg）
- iOS入门（二十六）iOS程序启动流程
