在弄数据库分库分表的时候出现了一条慢查询:
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的执行计划
image.png
看到了possible_keys出现了transfer_date_index，key列为NULL
现在看上图query statistic中的
【Index Usage】中出现了at least one index was used
可以推断workbanch中执行的
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索引，也就是执行计划提示的并不准确。
在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(回表)
下面展示强制走索引和不走索引的执行计划和执行时间：
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
通过上图执行的情况看，transfer_date_index索引导致了回表操作，成为了负面技
优化方式就是添加ignore index(transfer_date_index)
- 通过这个例子可以知道 explain查看执行计划作用是有限的。
- 算是一个坑，比如在workbench中执行
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中执行只有0.6秒(优化之后)，但是两者执行计划是一样的
- workbench的query statistic是个好东西，能看到很多信息。
- 可以开启set profile查看SQL执行的开销处于那个环节：
# 开启 profile
set profiling = on;
##要统计的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环节的耗时是比较长的
总结: 在某些情况下，简单的全表扫描反而性能更好，二级索引反而会导致回表操作而成为负面技
