在弄數據庫分庫分表的時候出現了一條慢查詢:
SELECT count(1) FROM house_price_1995 WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
image.png
可以看到這條SQL的開銷時間是12.51秒，在【Joins per Type】可以看到有join using range(select_range):1
查看的SQL的執(zhí)行計劃
image.png
看到了possible_keys出現了transfer_date_index，key列為NULL
現在看上圖query statistic中的
【Index Usage】中出現了at least one index was used
可以推斷workbanch中執(zhí)行的
SELECT count(1) FROM house_price_1995 WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
一定是走了transfer_date_index索引，也就是執(zhí)行計劃提示的并不準確。
在SQL強制走transfer_date_index
explain SELECT count(1) FROM house_price_1995 force index (transfer_date_index) WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
image.png
從上圖中可以看到走了transfer_date_index，并且出現了Using MRR(回表)
下面展示強制走索引和不走索引的執(zhí)行計劃和執(zhí)行時間：
SELECT count(1) FROM house_price_1995 force index (transfer_date_index) WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
EXPLAIN SELECT count(1) FROM house_price_1995 force index (transfer_date_index) WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' \G
SELECT count(1) FROM house_price_1995 ignore index (transfer_date_index) WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
EXPLAIN SELECT count(1) FROM house_price_1995 ignore index (transfer_date_index) WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' \G
image.png
通過上圖執(zhí)行的情況看，transfer_date_index索引導致了回表操作，成為了負面技
優(yōu)化方式就是添加ignore index(transfer_date_index)
- 通過這個例子可以知道 explain查看執(zhí)行計劃作用是有限的。
- 算是一個坑，比如在workbench中執(zhí)行
SELECT count(1) FROM house_price_1995 WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0' ;
時間是12秒，而在terminal中執(zhí)行只有0.6秒(優(yōu)化之后)，但是兩者執(zhí)行計劃是一樣的
- workbench的query statistic是個好東西，能看到很多信息。
- 可以開啟set profile查看SQL執(zhí)行的開銷處于那個環(huán)節(jié)：
# 開啟 profile
set profiling = on;
##要統(tǒng)計的SQL
select count(1) FROM house_price_1995 WHERE town_or_city = 'TORQUAY' AND transfer_date BETWEEN '1995-01-01 00:00:00.0' AND '1995-12-31 23:59:59.0';
#=查看SQL的對應的query id
show profiles;
image.png
可以看到目標SQL的query_id=2是
##查詢
show profile cpu, block io for query 2;
結果如下
image.png
可以看到sending data環(huán)節(jié)的耗時是比較長的
總結: 在某些情況下，簡單的全表掃描反而性能更好，二級索引反而會導致回表操作而成為負面技
