乐趣区

关于mysql:慢SQL优化实战笔记

jiezi

一、存在问题

通过 sql 慢查问的优化，咱们零碎中发现了以下几种类型的问题:

1. 未建索引：整张表没有建索引；2. 索引未命中：有索引, 然而局部查问条件下索引未命中；3. 搜寻了额定的非必要字段，导致回表；4. 排序，聚合导致慢查问；5. 雷同内容屡次查询数据库；6. 未消限度搜寻范畴或者限度的搜寻范畴在预期之外，导致全副扫描；

二、解决方案

1. 优化索引，减少或者批改以后的索引；2. 重写 sql；3. 利用 redis 缓存，缩小查问次数；4. 减少条件，防止非必要查问；5. 减少条件，缩小查问范畴；

三、案例剖析

（一）药材搜寻接口

残缺 sql 语句在附录，为不便浏览和脱敏，局部常用字段采纳中文。

这儿次要讲一下咱们拿到 Sql 语句后的整个剖析过程，思考逻辑，而后进行调整的过程和最初解决的方法。

给大家提供一些借鉴，也心愿大家可能提出更好的倡议。

这个 sql 语句要求是依据医生搜寻的拼音或者中文，进行含糊查问，找到药材，而后依据医生抉择的药库，查找上面的供应商，而后依据供应商，进行药材匹配，排除掉供应商没有的药材，而后依据真名在前，别名在后，齐全匹配在前，局部匹配在后，附加医生最近半年的应用习惯，把药材排序进去。最初把不同名称的同一味药聚合起来，以真名 (另名) 的模式展示。

1．剖析 sql

  • （1）14-8

第 14 排,id 为 8 的 explain 后果剖析:

①Explain
8,DERIVED,ssof,range,"ix_district,ix_供应商 id",ix_district,8,NULL,18,Using where; Using index; Using temporary
②Sql
SELECT DISTINCT (ssof. 供应商 id) AS 供应商 id FROM  药库供应商关系表 AS ssof  WHERE ssof. 药库 id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33)  AND ssof. 药方剂型 id IN (1)
③索引
PRIMARY KEY (`id`),    UNIQUE KEY `ix_district` (` 药库 id`, ` 药方剂型 id`, ` 供应商 id`) USING BTREE,KEY `ix_供应商 id` (` 供应商 id`) USING BTREE
④剖析

应用了索引，建设了长期表，这个中央索引曾经齐全笼罩了，然而还有回表操作。

起因是用 in，这个导致了回表。如果 in 能够被 mysql 主动优化为等于，就不会回表。如果无奈优化，就回表。

长期表是因为有 distinct，所以无奈防止。

同时应用 in 须要留神，如果外面的值数量比拟多，有几万个。即便区分度高，就会导致索引生效，这种状况须要屡次分批查问。

2． 12-7

  • （1）Explain
7,DERIVED,<derived8>,ALL,NULL,NULL,NULL,NULL,18,Using temporary; Using filesort
  • （2）Sql
INNER JOIN (下面 14- 8 长期表) tp ON tp. 供应商 id= ms. 供应商 id
  • （3）索引

无

  • （4）剖析

对长期表操作，无索引，用了文件排序。

这一部分是对长期表和药材表进行关联操作的一部分，有文件排序是因为须要对药材表 id 进行 group by 导致的。

   1、默认状况下，mysql 在应用 group by 之后，会产生长期表，而后进行排序（此处排序默认是快排），这会耗费的性能。

   2、group by 实质是先分组后排序【而不是先排序后分组】。

   3、group by column 默认会依照 column 分组, 而后依据 column 升序排列;  group by column order by null 则默认依照 column 分组, 而后依据标的主键 ID 升序排列。

3． 13-7

  • （1）Explain
7,DERIVED,ms,ref,"ix_title,idx_audit,idx_mutiy",idx_mutiy,5,"tp. 供应商 id,const",172,NULL
  • （2）Sql
SELECT ms. 药材表 id, max(ms.audit) AS audit, max(ms.price) AS price, max(ms.market_price) AS market_price,max(ms.is_granule) AS is_granule,max(ms.is_decoct) AS is_decoct, max(ms.is_slice) AS is_slice,max(ms.is_cream) AS is_cream, max(ms.is_extract) AS is_extract,max(ms.is_cream_granule) AS is_cream_granule, max(ms.is_extract_granule) AS is_extract_granule,max(ms.is_drychip) AS is_drychip,            max(ms.is_pill) AS is_pill,max(ms.is_powder) AS is_powder, max(ms.is_bolus) AS is_bolus FROM 供应商药材表 AS ms INNER JOIN (SELECT                    DISTINCT (ssof. 供应商 id) AS 供应商 id                FROM                    药库供应商关系表 AS ssof WHERE  ssof. 药库 id IN (1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22, 24, 25, 26, 27, 31, 33) AND ssof. 药方剂型 id IN (1) ) tp ON tp. 供应商 id= ms. 供应商 id WHERE  ms.audit = 1  GROUP BY  ms. 药材表 id
  • （3）索引
   KEY `idx_mutiy` (` 供应商 id`, `audit`, ` 药材表 id`)
  • （4）剖析

命中了索引，表间连接应用了供应商 id，建设索引的程序是供应商 id，where 条件中 audit，Group by 条件药材表 id。

这部分临时不须要更改。

4．10-6

  • （1）Explain
6,DERIVED,r,range,"PRIMARY,id,idx_timeline,idx_did_timeline,idx_did_isdel_statuspay_timecreate_payorderid,idx_did_statuspay_ischecked_isdel",idx_did_timeline,8,NULL,546,Using where; Using index; Using temporary; Using filesort
  • （2）Sql
SELECT 
       count(*) AS total, 
       rc.i AS m 药材表 id 
     FROM 
        处方药材表 AS rc 
        INNER JOIN 药方表 AS r ON r.id = rc. 药方表_id 
     WHERE 
         r.did = 40 
         AND r.timeline > 1576115196 
         AND rc.type_id in (1, 3) 
         GROUP BY 
    rc.i
  • （3）索引
KEY `idx_did_timeline` (`did`, `timeline`),
  • （4）剖析

驱动表与被驱动表，小表驱动大表。

先理解在 join 连贯时哪个表是驱动表，哪个表是被驱动表：

1. 当应用 left join 时，左表是驱动表，右表是被驱动表；

2. 当应用 right join 时，右表时驱动表，左表是驱动表；

3. 当应用 join 时，mysql 会抉择数据量比拟小的表作为驱动表，大表作为被驱动表；

4. in 前面跟的是驱动表, exists 后面的是驱动表；

5． 11-6

  • （1）Explain
6,DERIVED,rc,ref,"orderid_药材表, 药方表_id", 药方表_id,5,r.id,3,Using where
  • （2）Sql

同上

  • （3）索引
  KEY `idx_药方表_id` (` 药方表_id`, `type_id`) USING BTREE,
  • （4）剖析

索引的程序没有问题，仍旧是 in 导致了回表。

6．8-5

  • （1）Explain
5,UNION,malias,ALL,id_tid,NULL,NULL,NULL,4978,Using where
  • （2）Sql
 SELECT 
      mb.id, 
      mb.sort_id, 
      mb.title, 
      mb.py, 
      mb.unit, 
      mb.weight, 
      mb.tid, 
      mb.amount_max, 
      mb.poisonous, 
      mb.is_auxiliary, 
      mb.is_auxiliary_free, 
      mb.is_difficult_powder, 
      mb.brief, 
      mb.is_fixed_recipe, 
      ASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title = 'GC' THEN malias.title ELSE '' END END AS atitle, 
      alias.py AS apy, 
      CASE WHEN malias.py = 'GC' THEN 2 ELSE CASE WHEN malias.title = 'GC' THEN 2 ELSE 1 END END AS ttid 
 FROM 
      药材表 AS mb 
      LEFT JOIN 药材表 AS malias ON malias.tid = mb.id 
WHERE 
      alias.title LIKE '%GC%' 
      OR malias.py LIKE '%GC%'
  • （3）索引 
KEY `id_tid` (`tid`) USING BTREE,
  • （4）剖析

因为 like 是左右 like，无奈建设索引，所以只能建 tid。Type 是 all，遍历全表以找到匹配的行，左右表大小一样，估算的找到所需的记录所须要读取的行数有 4978。这个因为是 like 的缘故，无奈优化，这个语句并没有走索引，药材表 AS mb FORCE INDEX (id_tid) 改为强制索引，读取的行数缩小了 700 行。

7．9-5

  • （1）Explain
5,UNION,mb,eq_ref,"PRIMARY,ix_id",PRIMARY,4,malias.tid,1,NULL
  • （2）Sql

同上

  • （3）索引
PRIMARY KEY (`id`) USING BTREE,
  • （4）剖析

走了主键索引，行数也少，通过。

8．7-4

  • （1）Explain
4,DERIVED,mb,ALL,id_tid,NULL,NULL,NULL,4978,Using where
  • （2）Sql           
SELECT 
       mb.id, 
       mb.sort_id, 
       mb.title, 
       mb.py, 
       mb.unit, 
       mb.weight, 
       mb.tid, 
       mb.amount_max, 
       mb.poisonous, 
       mb.is_auxiliary, 
       mb.is_auxiliary_free, 
       mb.is_difficult_powder, 
       mb.brief, 
       mb.is_fixed_recipe, 
       ''AS atitle,'' AS apy, 
       CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title = 'GC' THEN 3 ELSE 1 END END AS ttid 
    FROM 
       药材表 AS mb 
      WHERE 
       mb.tid = 0 
       AND (
           mb.title LIKE '%GC%' 
           OR mb.py LIKE '%GC%'
                                )
  • （3）索引
KEY `id_tid` (`tid`) USING BTREE,
  • （4）剖析

 tid int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘ 真名药品的 id’,

他也是 like, 这个没法优化。

9．6-3

  • （1）Explain
3,DERIVED,<derived4>,ALL,NULL,NULL,NULL,NULL,9154,Using filesort
  • （2）Sql

  UNION ALL

  • （3）索引

无

  • （4）剖析

就是把真名搜寻后果和他人搜寻后果合并。防止用 or 连贯，加快速度 造成一个 munion 的表，初步实现药材搜寻，接下去就是排序。

这一个进行了 2 次查问，而后用 union 连贯，能够思考合并为一次查问。用 case when 进行辨别, 计算出权重。

这边是一个优化点。

10．4-2

  • （1）Explain
2,DERIVED,<derived3>,ALL,NULL,NULL,NULL,NULL,9154,NULL
  • （2）Sql

 SELECT 
       munion.id, 
       munion.sort_id, 
       case when length(
         trim(group_concat(munion.atitle SEPARATOR ' ')
                        )
                    )> 0 then concat(
                        munion.title, 
                        '(', 
                        trim(group_concat(munion.atitle SEPARATOR ' ')
                        ), 
                        ')'
                    ) else munion.title end as title, 
          munion.py, 
          munion.unit, 
          munion.weight, 
          munion.tid, 
          munion.amount_max, 
          munion.poisonous, 
          munion.is_auxiliary, 
          munion.is_auxiliary_free, 
          munion.is_difficult_powder, 
          munion.brief, 
          munion.is_fixed_recipe, 
          --  trim(group_concat( munion.atitle SEPARATOR ' ') ) AS atitle,
                    ##  --  
           trim(group_concat(munion.apy SEPARATOR ' ')
                 ) AS apy, 
             ##   
               max(ttid) * 100000 + id AS ttid 
         FROM 
                munion <derived4>
             GROUP BY 
                id -- 全副实名药材 完结 ##
  • （3）索引

无

  • （4）剖析

这里全副在长期表中搜寻了。

11．5-2

  • （1）Explain
2,DERIVED,<derived6>,ref,<auto_key0>,<auto_key0>,5,m.id,10,NULL
  • （2）Sql
Select fields from 全副实名药材表 as m  LEFT JOIN (集体应用药材统计表) p ON m.id = p.m 药材表 id
  • （3）索引

无

  • （4）剖析

2 张虚构表 left join

应用了优化器为派生表生成的索引 <auto_key0>

这边比拟节约性能，每次查问，都要对医生历史开方记录进行统计，并且统计还是几张大表计算后的后果。然而如果只是 sql 优化，这边临时无奈优化。

12．2-1

  • （1）Explain
1,PRIMARY,<derived7>,ALL,NULL,NULL,NULL,NULL,3096,Using where; Using temporary; Using filesort
  • （2）Sql
  • （3）索引
  • （4）剖析

长期表操作

13．3-1

  • （1）Explain
1,PRIMARY,<derived2>,ref,<auto_key0>,<auto_key0>,4,msu. 药材表 id,29,NULL
  • （2）Sql
  • （3）索引
  • （4）剖析

长期表操作

14．null

  • （1）Explain
NULL,UNION RESULT,"<union4,5>",ALL,NULL,NULL,NULL,NULL,NULL,Using temporary
  • （2）Sql
  • （3）索引
  • （4）剖析

长期表

（二）优化 sql

下面咱们只做索引的优化, 遵循的准则是:

1. 最左前缀匹配准则，十分重要的准则，mysql 会始终向右匹配直到遇到范畴查问 (>、<、between、like) 就进行匹配，比方 a = 1 and b = 2 and c > 3 and d = 4 如果建设 (a,b,c,d) 程序的索引，d 是用不到索引的，如果建设 (a,b,d,c) 的索引则都能够用到，a,b,d 的程序能够任意调整。2.= 和 in 能够乱序，比方 a = 1 and b = 2 and c = 3 建设 (a,b,c) 索引能够任意程序，mysql 的查问优化器会帮你优化成索引能够辨认的模式。3. 尽量抉择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，示意字段不反复的比例，比例越大咱们扫描的记录数越少，惟一键的区分度是 1，而一些状态、性别字段可能在大数据背后区分度就是 0，那可能有人会问，这个比例有什么经验值吗？应用场景不同，这个值也很难确定，个别须要 join 的字段咱们都要求是 0.1 以上，即均匀 1 条扫描 10 条记录。4. 索引列不能参加计算，放弃列“洁净”，比方 from_unixtime(create_time) =’2014-05-29’就不能应用到索引，起因很简略，b+ 树中存的都是数据表中的字段值，但进行检索时，须要把所有元素都利用函数能力比拟，显然老本太大。所以语句应该写成 create_time = unix_timestamp(’2014-05-29’)。5. 尽量的扩大索引，不要新建索引。比方表中曾经有 a 的索引，当初要加 (a,b) 的索引，那么只须要批改原来的索引即可。

查问优化神器 – explain 命令

对于 explain 命令置信大家并不生疏，具体用法和字段含意能够参考官网 explain-output，这里须要强调 rows 是外围指标，绝大部分 rows 小的语句执行肯定很快（有例外，上面会讲到）。所以优化语句基本上都是在优化 rows。

化根本步骤：

0. 先运行看看是否真的很慢，留神设置 SQL_NO_CACHE

1.where 条件单表查，锁定最小返回记录表。这句话的意思是把查问语句的 where 都利用到表中返回的记录数最小的表开始查起，单表每个字段别离查问，看哪个字段的区分度最高；2.explain 查看执行打算，是否与 1 预期统一（从锁定记录较少的表开始查问）；3.order by limit 模式的 sql 语句让排序的表优先查；4. 理解业务方应用场景；5. 加索引时参照建索引的几大准则；6. 察看后果，不合乎预期持续从 0 剖析；

下面曾经具体的剖析了每一个步骤，依据下面的 sql，去除 union 操作，减少索引。能够看出，优化后尽管有所改善。然而间隔咱们的心愿还有很大间隔，然而光做 sql 优化，感觉也没有多少改良空间, 所以决定从其余方面解决。

（三）拆分 sql

   因为速度还是不领人称心, 尤其是集体用药状况统计, 其实没必要每次都全副统计一次, 再要优化, 只靠批改索引应该是不行的了, 所以思考应用缓存。

接下来是批改 php 代码, 把全副 sql 语句拆分, 而后再组装。

  • （1）搜寻真名, 别名(缓存)
SELECT  mb.id,  mb.sort_id,  mb.title,  mb.py,  mb.unit,  mb.weight,  mb.tid,  mb.amount_max,  mb.poisonous,  mb.is_auxiliary,  mb.is_auxiliary_free,  mb.is_difficult_powder,  mb.brief,  mb.is_fixed_recipe,  IFNULL(group_concat(malias.title),'') atitle,  IFNULL(group_concat(malias.py),'') apy  FROM  药材表 AS mb  LEFT JOIN 药材表 AS malias ON malias.tid = mb.id  WHERE  mb.tid = 0  AND (malias.title LIKE '%GC%'  OR malias.py LIKE '%GC%'  or mb.title LIKE '%GC%'  OR mb.py LIKE '%GC%')  group by  mb.id

  • （2）如果命中有药材
①排序

真名在前, 别名在后, 齐全匹配在前, 局部匹配在后

// 对搜寻后果进行解决, 减少权重
②对供应商药材搜寻
SELECT ms. 药材表 id, max(ms.audit) AS audit, max(ms.price) AS price, max(ms.market_price) AS market_price, max(ms.is_granule) AS is_granule, max(ms.is_decoct) AS is_decoct, max(ms.is_slice) AS is_slice, max(ms.is_cream) AS is_cream, max(ms.is_extract) AS is_extract, max(ms.is_cream_granule) AS is_cream_granule, max(ms.is_extract_granule) AS is_extract_granule, max(ms.is_drychip) AS is_drychip, max(ms.is_pill) AS is_pill, max(ms.is_powder) AS is_powder, max(ms.is_bolus) AS is_bolus  FROM 供应商药材表 AS ms WHERE ms.audit = 1 AND ms. 供应商 idin (SELECT DISTINCT  ( ssof. 供应商 id) AS 供应商 id FROM  药库供应商关系表 AS ssof  WHERE  ssof. 药库 id IN (1,2,8,9,10,11,12,13,14,15,17,22,24,25,26,27,31,33)  AND ssof. 药方剂型 id IN (1) ) AND ms. 药材表 id IN (78,205,206,207,208,209,334,356,397,416,584,652,988,3001,3200,3248,3521,3522,3599,3610,3624,4395,4396,4397,4398,4399,4400,4401,4402,4403,4404,4405,4406,4407,4408,5704,5705,5706,5739,5740,5741,5742,5743,6265,6266,6267,6268,6514,6515,6516,6517,6518,6742,6743) AND ms.is_slice = 1  GROUP BY ms. 药材表 id  

③拿医生历史开方药材用量数据(缓存)
SELECT  count(*) AS total,  rc.i AS 药材表 id FROM  处方药材表 AS rc  INNER JOIN 药方表 AS r ON r.id = rc. 药方表_id WHERE  r.did = 40  AND r.timeline > 1576116927  AND rc.type_id in (1,3) GROUP BY  rc.i

④  拆卸及排序微调

  • （3）小结

运行速度, 对于开方量不是特地多的医生来说, 两者速度都是 0.1 秒左右. 然而如果碰到开方量大的医生, 优化后的 sql 速度比较稳定, 能始终维持在 0.1 秒左右, 优化前的 sql 速度会超过 0.2 秒. 速度晋升约一倍以上。

最初对搜寻后果和未优化前的搜寻后果进行比对, 后果数量和程序完全一致. 本次优化完结。

四、附录：


SELECT sql_no_cache 
    *
FROM
    (
        -- mbu start##
        SELECT
            m.*,
            ifnull(p.total, 0) AS total
        FROM
            (
                --
全副实名药材
开始
##
SELECT
       munion.id,
       munion.sort_id,
       case when length(
        trim(group_concat(munion.atitle SEPARATOR ' ')
                 )
             )> 0 then concat(
           munion.title,
          '(',
      trim(group_concat(munion.atitle SEPARATOR ' ')
              ),
                ')'
             ) else munion.title end as title,
        munion.py,
        munion.unit,
        munion.weight,
        munion.tid,
        munion.amount_max,
        munion.poisonous,
        munion.is_auxiliary,
        munion.is_auxiliary_free,
        munion.is_difficult_powder,
        munion.brief,
        munion.is_fixed_recipe,
        --  trim(group_concat( munion.atitle SEPARATOR ' ') ) AS atitle,##
        --  trim(group_concat( munion.apy SEPARATOR  ' ') ) AS apy,##
              max(ttid) * 100000 + id AS ttid
           FROM
              (
                -- #union start
联结查找
,
失去全副药材
##
  (
       SELECT
              mb.id,
              mb.sort_id,
              mb.title,
              mb.py,
              mb.unit,
              mb.weight,
              mb.tid,
              mb.amount_max,
              mb.poisonous,
              mb.is_auxiliary,
              mb.is_auxiliary_free,
              mb.is_difficult_powder,
              mb.brief,
              mb.is_fixed_recipe,
              ''AS atitle,'' AS apy,
              CASE WHEN mb.py = 'GC' THEN 3 ELSE CASE WHEN mb.title = 'GC' THEN 3 ELSE 1 END END AS ttid
               FROM
                 药材表 AS mb
                     WHERE
                         mb.tid = 0
                       AND (
                              mb.title LIKE '%GC%'
                              OR mb.py LIKE '%GC%'
                                )
                        ) --
真名药材
 
完结
##
 UNION ALL
    (
      SELECT
            mb.id,
            mb.sort_id,
            mb.title,
            mb.py,
            mb.unit,
            mb.weight,
            mb.tid,
            mb.amount_max,
            mb.poisonous,
            mb.is_auxiliary,
            mb.is_auxiliary_free,
            mb.is_difficult_powder,
            mb.brief,
            mb.is_fixed_recipe,
            CASE WHEN malias.py = 'GC' THEN malias.title ELSE CASE WHEN malias.title = 'GC' THEN malias.title ELSE '' END END AS atitle,
            malias.py AS apy,
            CASE WHEN malias.py = 'GC' THEN 2 ELSE CASE WHEN malias.title = 'GC' THEN 2 ELSE 1 END END AS ttid
          FROM
                药材表 AS mb
                LEFT JOIN 药材表 AS malias ON malias.tid = mb.id
          WHERE
                malias.title LIKE '%GC%'
                OR malias.py LIKE '%GC%'
                      ) --
其余药材完结
##
                 -- #union end##
                ) munion
                GROUP BY
                    id --
全副实名药材
 
完结
##
                    ) m
            LEFT JOIN (
                --
集体应用药材统计
 
开始
##
    SELECT
          count(*) AS total,
          rc.i AS m 药材表 id
     FROM
           处方药材表 AS rc
           INNER JOIN 药方表 AS r ON r.id = rc. 药方表_id
      WHERE
           r.did = 40
            AND r.timeline > 1576115196
            AND rc.type_id in (1, 3)
       GROUP BY
              rc.i --
集体应用药材统计
 
完结
##
           ) p ON m.id = p.m 药材表 id -- mbu end ##
            ) mbu
    INNER JOIN (
        -- msu start
供应商药材筛选
##
        SELECT
            ms. 药材表 id,
            max(ms.audit) AS audit,
            max(ms.price) AS price,
            max(ms.market_price) AS market_price,
            max(ms.is_granule) AS is_granule,
            max(ms.is_decoct) AS is_decoct,
            max(ms.is_slice) AS is_slice,
            max(ms.is_cream) AS is_cream,
            max(ms.is_extract) AS is_extract,
            max(ms.is_cream_granule) AS is_cream_granule,
            max(ms.is_extract_granule) AS is_extract_granule,
            max(ms.is_drychip) AS is_drychip,
            max(ms.is_pill) AS is_pill,
            max(ms.is_powder) AS is_powder,
            max(ms.is_bolus) AS is_bolus
        FROM
            供应商药材表 AS ms
            INNER JOIN (
                SELECT
                    DISTINCT (ssof. 供应商 id) AS 供应商 id
                FROM
                    药库供应商关系表 AS ssof
                WHERE
                    ssof. 药库 id IN (
                        1, 2, 8, 9, 10, 11, 12, 13, 14, 15, 17, 22,
                        24, 25, 26, 27, 31, 33
                    )
                    AND ssof. 药方剂型 id IN (1)
            ) tp ON tp. 供应商 id= ms. 供应商 id
        WHERE
            ms.audit = 1
        GROUP BY
            ms. 药材表 id -- msu end ##
            ) msu ON mbu.id = msu. 药材表 id
WHERE
    msu. 药材表 id > 0
    AND msu.is_slice = 1
order by
    total desc,
    ttid desc
退出移动版