点击上方蓝色文字关注我们
鸟枪换大炮
sql慢查询优化笔记一、存在问题
经过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,,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_t