Mysql慢查询分析

在弄数据库分库分表的时候出现了一条慢查询:

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)

1. 通过这个例子可以知道 explain查看执行计划作用是有限的。
2. 算是一个坑，比如在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秒(优化之后)，但是两者执行计划是一样的

3. workbench的query statistic是个好东西，能看到很多信息。
4. 可以开启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环节的耗时是比较长的

总结: 在某些情况下，简单的全表扫描反而性能更好，二级索引反而会导致回表操作而成为负面技