一、存在问题
通过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_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