慢SQL优化实战笔记

一、存在问题

经过sql慢查询的优化，我们系统中发现了以下几种类型的问题:

1.未建索引：整张表没有建索引；

2.索引未命中：有索引,但是部分查询条件下索引未命中；

3.搜索了额外的非必要字段，导致回表；

4.排序，聚合导致慢查询；

5.相同内容多次查询数据库；

6.未消限制搜索范围或者限制的搜索范围在预期之外，导致全部扫描；

二、解决方案

1.优化索引，增加或者修改当前的索引；         

2.重写sql；

3.利用redis缓存，减少查询次数；

4.增加条件，避免非必要查询；

5.增加条件，减少查询范围；                          

三、案例分析

（一）药材搜索接口

完整sql语句在附录，为方便阅读和脱敏，部分常用字段采用中文。

这儿主要讲一下我们拿到Sql语句后的整个分析过程，思考逻辑，然后进行调整的过程和最后解决的办法。

给大家提供一些借鉴，也希望大家能够提出更好的建议。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3LVsQBp9-1640158134398)(https://cdn.learnku.com/uploads/images/202007/26/32495/NVJW4Y8wxW.png!large “image.png”)]

这个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，建立索引的顺序是供