Nach langsamer SQL-Abfrage Optimierung, die folgenden Arten von Problemen wurden in unserem System gefunden:
Die vollständige SQL-Anweisung befindet sich im Anhang. Zur besseren Lesbarkeit und Desensibilisierung sind einige allgemeine Felder auf Chinesisch.
Hier sprechen wir hauptsächlich über den gesamten Analyseprozess, nachdem wir die SQL-Anweisung erhalten haben, über die Logik nachdenken und dann Anpassungen vornehmen und die endgültige Lösung finden.
Ich möchte Ihnen einige Hinweise geben und hoffe, dass Sie bessere Vorschläge machen können.
Diese SQL-Anweisung erfordert eine Fuzzy-Abfrage basierend auf dem Pinyin oder Chinesisch, nach dem der Arzt gesucht hat, um die medizinischen Materialien zu finden, und dann basierend auf der vom Arzt ausgewählten Medikamentenbibliothek nach den folgenden Lieferanten zu suchen und diese dann abzugleichen Medizinische Materialien basierend auf den Lieferanten sortieren und ausschließen. Bei medizinischen Materialien, die nicht beim Lieferanten erhältlich sind, werden die medizinischen Materialien dann nach dem echten Namen zuerst, dem Alias zuletzt, der vollständigen Übereinstimmung zuerst, der teilweisen Übereinstimmung zuletzt und dem sortiert Nutzungsgewohnheiten des Arztes in den letzten sechs Monaten. Schließlich wird dasselbe Arzneimittel mit unterschiedlichen Namen aggregiert und in Form seines echten Namens (anderer Name) dargestellt.
Verwenden eines Index zum Erstellen einer temporären Tabelle. Der Index wurde hier vollständig abgedeckt, es gibt jedoch noch einen Tabellenrückgabevorgang.
2. 12-7
3． 13-7. (1) Erklären Sie -Tisch Verbindung wurde verwendet Lieferanten-ID, die Reihenfolge der Indexerstellung ist Lieferanten-ID, Audit in wo Zustand, Gruppierung nach Zustand medizinische Materialtabellen-ID.
Fahrertisch und Steppdecke Fahren Sie den Tisch, der kleine Tisch treibt den großen Tisch an.
1 Bei Verwendung der linken Verknüpfung ist die rechte Tabelle die treibende Tabelle
3 Bei der Verwendung von „join“ wählt MySQL die Tabelle mit dem kleineren Datenvolumen als treibende Tabelle und die größere Tabelle als treibende Tabelle aus ;
5. 11-6 ist kein Problem mit Die Reihenfolge des Index ist immer noch darin, dass die Tabelle zurückgegeben wird.
5,UNION,malias,ALL,id_tid,NULL,NULL,NULL,4978,Using where
Nach dem Login kopieren
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%'
Nach dem Login kopieren
KEY `id_tid` (`tid`) USING BTREE,
Nach dem Login kopieren
Nach dem Login kopieren
因为like是左右like，无法建立索引，所以只能建tid。Type是all，遍历全表以找到匹配的行，左右表大小一样，估算的找到所需的记录所需要读取的行数有4978。这个因为是like的缘故，无法优化，这个语句并没有走索引，药材表 AS mb FORCE INDEX (id_tid) 改为强制索引，读取的行数减少了700行。
7．9-5
5,UNION,mb,eq_ref,"PRIMARY,ix_id",PRIMARY,4,malias.tid,1,NULL
Nach dem Login kopieren
同上
PRIMARY KEY (`id`) USING BTREE,
Nach dem Login kopieren
走了主键索引，行数也少，通过。
8．7-4
4,DERIVED,mb,ALL,id_tid,NULL,NULL,NULL,4978,Using where
Nach dem Login kopieren
（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%' )
Nach dem Login kopieren
（3）索引
KEY `id_tid` (`tid`) USING BTREE,
Nach dem Login kopieren
Nach dem Login kopieren
他也是like,这个没法优化。
9．6-3
3,DERIVED,,ALL,NULL,NULL,NULL,NULL,9154,Using filesort
Nach dem Login kopieren
无
就是把真名搜索结果和别人搜索结果合并。避免用or连接，加快速度 形成一个munion的表，初步完成药材搜索，接下去就是排序。
这一个进行了2次查询，然后用union连接，可以考虑合并为一次查询。用case when进行区分,计算出权重。
这边是一个优化点。
10．4-2
2,DERIVED,,ALL,NULL,NULL,NULL,NULL,9154,NULL
Nach dem Login kopieren
（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 GROUP BY id -- 全部实名药材 结束##
Nach dem Login kopieren
（3）索引
无
这里全部在临时表中搜索了。
11．5-2
2,DERIVED,,ref,,,5,m.id,10,NULL
Nach dem Login kopieren
Select fields from 全部实名药材表 as m LEFT JOIN ( 个人使用药材统计表 ) p ON m.id = p.m药材表id
Nach dem Login kopieren
无
2张虚拟表left join
使用了优化器为派生表生成的索引
这边比较浪费性能，每次查询，都要对医生历史开方记录进行统计，并且统计还是几张大表计算后的结果。但是如果只是sql优化，这边暂时无法优化。
12．2-1
1,PRIMARY,,ALL,NULL,NULL,NULL,NULL,3096,Using where; Using temporary; Using filesort
Nach dem Login kopieren
临时表操作
13．3-1
1,PRIMARY,,ref,,,4,msu.药材表id,29,NULL
Nach dem Login kopieren
临时表操作
14．null
NULL,UNION RESULT,"",ALL,NULL,NULL,NULL,NULL,NULL,Using temporary
Nach dem Login kopieren
临时表
（二）优化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)的索引，那么只需要修改原来的索引即可。
Nach dem Login kopieren
查询优化神器 - 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分析；
Nach dem Login kopieren
上面已经详细的分析了每一个步骤，根据上面的sql，去除union操作， 增加索引。可以看出，优化后虽然有所改善。但是距离我们的希望还有很大距离，但是光做sql优化，感觉也没有多少改进空间,所以决定从其他方面解决。
（三）拆分sql
由于速度还是不领人满意,尤其是个人用药情况统计,其实没必要每次都全部统计一次,再要优化,只靠修改索引应该是不行的了,所以考虑使用缓存。
接下来是修改php代码,把全部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, 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
Nach dem Login kopieren
①排序
真名在前,别名在后,完全匹配在前,部分匹配在后
//对搜索结果进行处理,增加权重
Nach dem Login kopieren
②对供应商药材搜索
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
Nach dem Login kopieren
③拿医生历史开方药材用量数据(缓存)
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
Nach dem Login kopieren
④ 装配及排序微调
运行速度,对于开方量不是特别多的医生来说,两者速度都是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
Nach dem Login kopieren
相关免费学习推荐：mysql视频教程
Das obige ist der detaillierte Inhalt vonZeichnen Sie langsame SQL-Optimierungspraktiken auf. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
