本文主要從工作經(jīng)驗(yàn)中總結(jié)出來的經(jīng)驗(yàn)總結(jié)sql語(yǔ)句優(yōu)化問題，下面我們用Demo來具體說明如何提高sql的執(zhí)行效率：
1、關(guān)于limit分頁(yè)優(yōu)化的問題
SELECT * FROM message_1 LIMIT 10000,10
這條語(yǔ)句執(zhí)行速度很快，當(dāng)我們把語(yǔ)句改成下面語(yǔ)句的時(shí)候再看看用了多久？
SELECT * FROM message_1 LIMIT 1000000,10
運(yùn)行結(jié)果如下圖：
上面的語(yǔ)句整整用了17.7秒！這么慢，用戶可是等不了了，那么我們?nèi)绾蝺?yōu)化這句sql呢？
解決方法：
我們加“order by id”改造后的sql為：
SELECT * FROM message_1 order by id LIMIT 1000000,10
執(zhí)行圖如下：
只用了1.1秒，可以說稍微改動(dòng)一下，效率提升了17倍。這是因?yàn)檫@個(gè)sql語(yǔ)句使用了主鍵id來作為索引，所以速度很快。
2、任何情況下都禁止使用 select * from table
因?yàn)檫@樣會(huì)進(jìn)行全表掃描而導(dǎo)致效率很低
SELECT * FROM message_1
應(yīng)改為
SELECT content FROM message_1
3、優(yōu)化批量插入
INSERT INTO message_1(id,content) values(1,'內(nèi)容1')
INSERT INTO message_1(id,content) values(2,'內(nèi)容2')
INSERT INTO message_1(id,content) values(3,'內(nèi)容3')
INSERT INTO message_1(id,content) values(4,'內(nèi)容4')
INSERT INTO message_1(id,content) values(5,'內(nèi)容5')
應(yīng)改為
INSERT INTO message_1(id,content) values(1,'內(nèi)容1'),(2,'內(nèi)容2'),(3,'內(nèi)容3'),(4,'內(nèi)容4'),(5,'內(nèi)容5')
4、like語(yǔ)句的優(yōu)化
SELECT content message_1 A WHERE content like '%我要學(xué)習(xí)%'
like后面使用了“%”，所以該sql語(yǔ)句查詢會(huì)進(jìn)行全表掃描，使索引失效，因此速度會(huì)很慢，如果想走索引的話，可以改下下面的語(yǔ)句：
SELECT content FROM message_1 WHERE content like '我要學(xué)習(xí)%'
5、避免where語(yǔ)句中使用IS NULL或IS NOT NULL語(yǔ)句
SELECT content FROM message_1 WHERE content IS NULL
上面的語(yǔ)句同樣會(huì)使索引失效而進(jìn)行全表掃描，我們?cè)趙here語(yǔ)句中避免使用 IS NULL或者IS NOT NULL來進(jìn)行條件選擇，這樣做會(huì)使索引失效，從而導(dǎo)致查詢速度很慢
6、不要在where條件中出現(xiàn)函數(shù)、算數(shù)運(yùn)算或其他表達(dá)式運(yùn)算
SELECT content FROM message_1 WHERE datediff(day,createTime,'2020-05-25')=0
改為
SELECT content FROM message_1 WHERE createTime>='2020-05-01' and createTime<'2020-05-25'
這句話告訴我們不要在where條件中出現(xiàn)函數(shù)、算數(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則也會(huì)使索引失效。
7、排序的索引問題
如果MySQL查詢語(yǔ)句中只用了一個(gè)索引，而where條件中已經(jīng)使用了索引，則order by中的字段就不會(huì)使用索引。因此盡量不要同時(shí)對(duì)多個(gè)字段進(jìn)行排序，如果有這樣的場(chǎng)景存在的話，那最好給這些字段設(shè)置聯(lián)合索引。
8、union all替換union
union和union all的區(qū)別在于前者需要合并兩個(gè)以上的結(jié)果集，然后在進(jìn)行唯一性過濾操作，這樣做肯定會(huì)涉及到數(shù)據(jù)的排序，增大cpu的運(yùn)算與資源消耗和延遲。因此，當(dāng)在確定沒有重復(fù)數(shù)據(jù)或者不關(guān)心重復(fù)數(shù)據(jù)的情況下，要使用union all。
9、left joinright join和inner join
SELECT A.id,A.name,B.id,B.name FROM message_1 LEFT JOIN message_2 b ON b.id =B.id;
SELECT A.id,A.name,B.id,B.name FROM message_1 RIGHT JOIN message_2 b ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM message_1 INNER JOIN message_2 b ON A.id =B.id;
上面的語(yǔ)句運(yùn)行后發(fā)現(xiàn)inner join運(yùn)行速度比較快，因?yàn)閕nner join是等值連接，返回的行數(shù)比較少。所以在項(xiàng)目開發(fā)過程中最好使用inner join
10、索引的數(shù)量控制在5個(gè)以內(nèi)
因?yàn)椴皇撬饕蕉嗑驮胶谩Ｋ饕M管提高了查詢效率，但是也是降低修改和新增的效率。而且insert和update有重建索引的可能，所以一張表的索引數(shù)最好不要超過五個(gè)，如果超過五個(gè)那么就得針對(duì)這張表做優(yōu)化處理了
