(2022.04.22 Fri)
SQL語(yǔ)句的優(yōu)化目的在于提高SQL語(yǔ)句的運(yùn)行效率给梅。注意SQL優(yōu)化和數(shù)據(jù)庫(kù)優(yōu)化的區(qū)別。
SQL語(yǔ)句的優(yōu)化有很大一部分是和SQL索引有關(guān)，善用索引避免全表掃描可以提升運(yùn)行效率。
利用索引
- 避免全表掃描，在WHERE和ORDER BY涉及的列上建立索引
以下列出的各種查詢(xún)方法都會(huì)避開(kāi)索引而直接使用全表掃描啰脚，建議使用修改建議做查詢(xún)。
- 使用IN/NOT IN的地方实夹，可用
=代替橄浓，或EXIST，或>=,<=亮航，或BETWEEN代替荸实，
# 低效
SELECT name_field, score_field
FROM table_a
WHERE score_field IN (90, 91, 92);
############################
SELECT name_field, score_field FROM table_a
WHERE score_field >= 90 AND score_field <=92;
############################
SELECT name_field, score_field FROM table_a
WHERE score_field = 90
UNION ALL
SELECT name_field, score_field FROM table_a
WHERE score_field = 91
UNION ALL
...
############################
SELECT name_field, score_field FROM table_a
WHERE score_field BETWEEN 90 AND 92;
-
NULL值作為查詢(xún)條件。建議在創(chuàng)建表示設(shè)置默認(rèn)值非NULL缴淋，比如0
SELECT * FROM table_a
WHERE some_field IS NULL;
###########################
SELECT * FROM table_a
WHERE some_field = 0;
- 使用
OR作為條件從句准给，建議使用UNION ALL代替泄朴。
SELECT * FROM table_a
WHERE sid = 10 OR sid = 20;
##########################
SELECT * FROM table_a
WHERE sid = 10
UNION ALL
SELECT * FROM table_a
WHERE sid = 20;
-
'%abc%'的首字母是%的檢索方式，在大數(shù)據(jù)量的情況建議換成'abc%'露氮，或者在MySQL中用INSTR(field, substr)指令檢索祖灰。如果是小數(shù)據(jù)量的數(shù)據(jù)，可使用'%abc%'指令查詢(xún)
SELECT * FROM table_a
WHERE content LIKE '%abc%'
##########################
SELECT * FROM table_a
WHERE content LIKE 'abc%'
##########################
SELECT * FROM table_a
WHERE INSTR(content, 'abc');
- 在
WHERE語(yǔ)句中對(duì)字段進(jìn)行表達(dá)式或函數(shù)操作畔规，轉(zhuǎn)換為其他局扶；或WHERE子句中=左側(cè)的運(yùn)算應(yīng)放在右側(cè)
SELECT * FROM table_a
WHERE some_field/10 > 10;
#########################
SELECT * FROM table_a
WHERE some_field > 100;
SELECT * FROM table_a
WHERE SUBSTRING(some_field, 1, 3) = 'abc';
#########################
SELECT * FROM table_a
WHERE some_field LIKE 'abc%';
- 在
WHERE從句中使用的!=,<>，建議用其他方式和語(yǔ)法代替
SQL優(yōu)化
SELECT語(yǔ)句中用
SELECT <col_names>代替SELECT *叁扫，避免找出不需要的字段三妈，占用過(guò)多CPU資源。另莫绣，查全部變量不會(huì)使用覆蓋索引(?)從而降低了查詢(xún)效率JOIN查詢(xún)代替子查詢(xún)沈跨。子查詢(xún)往往涉及
IN操作，而IN操作不僅會(huì)使用全表掃描兔综，還會(huì)生成臨時(shí)表，等查詢(xún)結(jié)束再銷(xiāo)毀臨時(shí)表狞玛，給系統(tǒng)造成負(fù)擔(dān)软驰。用JOIN操作代替
SELECT a.name, a.id, b.subjects, b.score
FROM stu_info a
INNER JOIN stu_score b
ON a.id = b.id;
能用
UNION的語(yǔ)句盡量換成UNION ALL語(yǔ)句。這兩個(gè)指令都可進(jìn)行結(jié)果的合并與整理心肪，但UNION在執(zhí)行過(guò)程中會(huì)對(duì)重復(fù)的選項(xiàng)做篩選锭亏，之后進(jìn)行排序，在返回結(jié)果前會(huì)將重復(fù)的選項(xiàng)重新加入到返回結(jié)果中硬鞍。UNION ALL對(duì)重復(fù)的數(shù)據(jù)不做操作慧瘤，不對(duì)數(shù)據(jù)做排序處理。顯然后者的效率更高固该。使用表的別名代替表名锅减，減少解析時(shí)間和友列歧義導(dǎo)致的語(yǔ)法錯(cuò)誤
調(diào)整
WHERE子句中的篩選字段連接順序。MySQL中是自左向右伐坏、自上而下的順序?qū)ψ兞孔龊Y選怔匣，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放，最快縮小數(shù)據(jù)集小表驅(qū)動(dòng)大表 - 多表關(guān)聯(lián)查詢(xún)(JOIN)時(shí)桦沉，在MySQL中執(zhí)行WHERE語(yǔ)句中的表從左到右查詢(xún)(Oracle相反)每瞒，第一張表會(huì)涉及全表掃描，所以把小表放在前面纯露， 大表放在后面剿骨，提高效率。能用
INNER JOIN盡量不用LEFT JOIN考慮語(yǔ)句的執(zhí)行順序埠褪，在先執(zhí)行的
WHERE子句中先篩選數(shù)據(jù)浓利，為后面的操作提供更小的數(shù)據(jù)挤庇，用于取代HAVING子句做數(shù)據(jù)篩選創(chuàng)建數(shù)據(jù)表時(shí)，字段的類(lèi)型
varchar比char好荞膘，因前者更靈活罚随，后者占用空間固定且檢索效率低
