美文网首页
Mysql慢查询分析

Mysql慢查询分析

作者: lixwcqs | 来源:发表于2020-06-07 00:06 被阅读0次

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

    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秒(优化之后),但是两者执行计划是一样的

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

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

    相关文章

      网友评论

          本文标题:Mysql慢查询分析

          本文链接:https://www.haomeiwen.com/subject/vtwftktx.html