sql教程介绍sql慢查询的优化
推荐（免费）：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结果分析:
①explain8,derived,ssof,range,ix_district,ix_供应商id,ix_district,8,null,18,using where; using index; using temporary
②sqlselect 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）explain7,derived,<derived8>,all,null,null,null,null,18,using temporary; using filesort
（2）sqlinner 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）explain7,derived,ms,ref,ix_title,idx_audit,idx_mutiy,idx_mutiy,5,tp.供应商id,const,172,null
（2）sqlselect 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）explain6,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）sqlselect 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）explain6,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）explain5,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）explain5,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）explain4,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）explain3,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）explain2,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）explain2,derived,<derived6>,ref,<auto_key0>,<auto_key0>,5,m.id,10,null
（2）sqlselect fields from 全部实名药材表 as m left join ( 个人使用药材统计表 ) p on m.id = p.m药材表id
（3）索引无
（4）分析2张虚拟表left join
使用了优化器为派生表生成的索引
这边比较浪费性能，每次查询，都要对医生历史开方记录进行统计，并且统计还是几张大表计算后的结果。但是如果只是sql优化，这边暂时无法优化。
12．2-1
（1）explain1,primary,<derived7>,all,null,null,null,null,3096,using where; using temporary; using filesort
（2）sql
（3）索引
（4）分析
临时表操作
13．3-1
（1）explain1,primary,<derived2>,ref,<auto_key0>,<auto_key0>,4,msu.药材表id,29,null
（2）sql
（3）索引
（4）分析
临时表操作
14．null
（1）explainnull,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_cache1.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.药材表idwhere msu.药材表id > 0 and msu.is_slice = 1order by total desc, ttid desc
相关免费学习推荐：mysql视频教程
以上就是记录 慢sql优化实战的详细内容。