這篇文章主要介紹SQL如何優(yōu)化，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！
在康縣等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站制作、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需定制開(kāi)發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),網(wǎng)絡(luò)營(yíng)銷推廣,外貿(mào)網(wǎng)站制作,康縣網(wǎng)站建設(shè)費(fèi)用合理。
一、存在問(wèn)題
經(jīng)過(guò)sql慢查詢的優(yōu)化，我們系統(tǒng)中發(fā)現(xiàn)了以下幾種類型的問(wèn)題:
1.未建索引：整張表沒(méi)有建索引；2.索引未命中：有索引,但是部分查詢條件下索引未命中；3.搜索了額外的非必要字段，導(dǎo)致回表；4.排序，聚合導(dǎo)致慢查詢；5.相同內(nèi)容多次查詢數(shù)據(jù)庫(kù)；6.未消限制搜索范圍或者限制的搜索范圍在預(yù)期之外，導(dǎo)致全部掃描；
二、解決方案
1.優(yōu)化索引，增加或者修改當(dāng)前的索引； 2.重寫(xiě)sql；3.利用redis緩存，減少查詢次數(shù)；4.增加條件，避免非必要查詢；5.增加條件，減少查詢范圍；
三、案例分析
（一）藥材搜索接口
完整sql語(yǔ)句在附錄，為方便閱讀和脫敏，部分常用字段采用中文。
這兒主要講一下我們拿到Sql語(yǔ)句后的整個(gè)分析過(guò)程，思考邏輯，然后進(jìn)行調(diào)整的過(guò)程和最后解決的辦法。
給大家提供一些借鑒，也希望大家能夠提出更好的建議。
這個(gè)sql語(yǔ)句要求是根據(jù)醫(yī)生搜索的拼音或者中文，進(jìn)行模糊查詢，找到藥材，然后根據(jù)醫(yī)生選擇的藥庫(kù)，查找下面的供應(yīng)商，然后根據(jù)供應(yīng)商，進(jìn)行藥材匹配，排除掉供應(yīng)商沒(méi)有的藥材，然后根據(jù)真名在前，別名在后，完全匹配在前，部分匹配在后，附加醫(yī)生最近半年的使用習(xí)慣，把藥材排序出來(lái)。最后把不同名稱的同一味藥聚合起來(lái)，以真名(另名)的形式展現(xiàn)。
1．分析sql
（1）14-8
第14排,id為8的explain結(jié)果分析:
8,DERIVED,ssof,range,"ix_district,ix_供應(yīng)商id",ix_district,8,NULL,18,Using where; Using index; Using temporary
SELECT DISTINCT (ssof.供應(yīng)商id) AS 供應(yīng)商id FROM 藥庫(kù)供應(yīng)商關(guān)系表 AS ssof WHERE ssof.藥庫(kù)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` ( `藥庫(kù)id`, `藥方劑型id`, `供應(yīng)商id` ) USING BTREE,KEY `ix_供應(yīng)商id` (`供應(yīng)商id`) USING BTREE
使用了索引，建立了臨時(shí)表，這個(gè)地方索引已經(jīng)完全覆蓋了，但是還有回表操作。
原因是用in，這個(gè)導(dǎo)致了回表。如果in可以被MySQL 自動(dòng)優(yōu)化為等于，就不會(huì)回表。如果無(wú)法優(yōu)化，就回表。
臨時(shí)表是因?yàn)橛衐istinct，所以無(wú)法避免。
同時(shí)使用in需要注意，如果里面的值數(shù)量比較多，有幾萬(wàn)個(gè)。即使區(qū)分度高，就會(huì)導(dǎo)致索引失效，這種情況需要多次分批查詢。
2． 12-7
（1）Explain
7,DERIVED,,ALL,NULL,NULL,NULL,NULL,18,Using temporary; Using filesort
（2）Sql
INNER JOIN (上面14-8臨時(shí)表) tp ON tp.供應(yīng)商id= ms.供應(yīng)商id
（3）索引
無(wú)
（4）分析
對(duì)臨時(shí)表操作，無(wú)索引，用了文件排序。
這一部分是對(duì)臨時(shí)表和藥材表進(jìn)行關(guān)聯(lián)操作的一部分，有文件排序是因?yàn)樾枰獙?duì)藥材表id進(jìn)行g(shù)roup by 導(dǎo)致的。
1、默認(rèn)情況下，mysql在使用group by之后，會(huì)產(chǎn)生臨時(shí)表，而后進(jìn)行排序（此處排序默認(rèn)是快排），這會(huì)消耗的性能。
2、group by本質(zhì)是先分組后排序【而不是先排序后分組】。
3、group by column 默認(rèn)會(huì)按照column分組, 然后根據(jù)column升序排列; group by column order by null 則默認(rèn)按照column分組,然后根據(jù)標(biāo)的主鍵ID升序排列。
3． 13-7
（1）Explain
7,DERIVED,ms,ref,"ix_title,idx_audit,idx_mutiy",idx_mutiy,5,"tp.供應(yīng)商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 供應(yīng)商藥材表 AS ms INNER JOIN ( SELECT DISTINCT (ssof.供應(yīng)商id) AS 供應(yīng)商id FROM 藥庫(kù)供應(yīng)商關(guān)系表 AS ssof WHERE ssof.藥庫(kù)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.供應(yīng)商id= ms.供應(yīng)商id WHERE ms.audit = 1 GROUP BY ms.藥材表id
（3）索引
KEY `idx_mutiy` (`供應(yīng)商id`, `audit`, `藥材表id`)
（4）分析
命中了索引，表間連接使用了供應(yīng)商id，建立索引的順序是供應(yīng)商id，where條件中audit，Group by 條件藥材表id。
這部分暫時(shí)不需要更改。
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）分析
驅(qū)動(dòng)表與被驅(qū)動(dòng)表，小表驅(qū)動(dòng)大表。
先了解在join連接時(shí)哪個(gè)表是驅(qū)動(dòng)表，哪個(gè)表是被驅(qū)動(dòng)表：
1.當(dāng)使用left join時(shí)，左表是驅(qū)動(dòng)表，右表是被驅(qū)動(dòng)表；
2.當(dāng)使用right join時(shí)，右表時(shí)驅(qū)動(dòng)表，左表是驅(qū)動(dòng)表；
3.當(dāng)使用join時(shí)，mysql會(huì)選擇數(shù)據(jù)量比較小的表作為驅(qū)動(dòng)表，大表作為被驅(qū)動(dòng)表；
4. in后面跟的是驅(qū)動(dòng)表, exists前面的是驅(qū)動(dòng)表；
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）分析
索引的順序沒(méi)有問(wèn)題，仍舊是in 導(dǎo)致了回表。
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）分析
因?yàn)閘ike是左右like，無(wú)法建立索引，所以只能建tid。Type是all，遍歷全表以找到匹配的行，左右表大小一樣，估算的找到所需的記錄所需要讀取的行數(shù)有4978。這個(gè)因?yàn)槭莑ike的緣故，無(wú)法優(yōu)化，這個(gè)語(yǔ)句并沒(méi)有走索引，藥材表 AS mb FORCE INDEX (id_tid) 改為強(qiáng)制索引，讀取的行數(shù)減少了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）分析
走了主鍵索引，行數(shù)也少，通過(guò)。
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,這個(gè)沒(méi)法優(yōu)化。
9．6-3
（1）Explain
3,DERIVED,,ALL,NULL,NULL,NULL,NULL,9154,Using filesort
（2）Sql
UNION ALL
（3）索引
無(wú)
（4）分析
就是把真名搜索結(jié)果和別人搜索結(jié)果合并。避免用or連接，加快速度 形成一個(gè)munion的表，初步完成藥材搜索，接下去就是排序。
這一個(gè)進(jìn)行了2次查詢，然后用union連接，可以考慮合并為一次查詢。用case when進(jìn)行區(qū)分,計(jì)算出權(quán)重。
這邊是一個(gè)優(yōu)化點(diǎn)。
10．4-2
（1）Explain
2,DERIVED,,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
GROUP BY
id -- 全部實(shí)名藥材 結(jié)束## （3）索引
無(wú)
（4）分析
這里全部在臨時(shí)表中搜索了。
11．5-2
（1）Explain
2,DERIVED,,ref, , ,5,m.id,10,NULL
（2）Sql
Select fields from 全部實(shí)名藥材表 as m LEFT JOIN ( 個(gè)人使用藥材統(tǒng)計(jì)表 ) p ON m.id = p.m藥材表id
（3）索引
無(wú)
（4）分析
2張?zhí)摂M表left join
使用了優(yōu)化器為派生表生成的索引
這邊比較浪費(fèi)性能，每次查詢，都要對(duì)醫(yī)生歷史開(kāi)方記錄進(jìn)行統(tǒng)計(jì)，并且統(tǒng)計(jì)還是幾張大表計(jì)算后的結(jié)果。但是如果只是sql優(yōu)化，這邊暫時(shí)無(wú)法優(yōu)化。
12．2-1
（1）Explain
1,PRIMARY,,ALL,NULL,NULL,NULL,NULL,3096,Using where; Using temporary; Using filesort
（2）Sql
（3）索引
（4）分析
臨時(shí)表操作
13．3-1
（1）Explain
1,PRIMARY,,ref, , ,4,msu.藥材表id,29,NULL
（2）Sql
（3）索引
（4）分析
臨時(shí)表操作
14．null
（1）Explain
