SQL性能優(yōu)化的47個(gè)小技巧，果斷收藏！

責(zé)編：架構(gòu)君 | 來(lái)源：Java就該這么學(xué)

1、先了解MySQL的執(zhí)行過(guò)程

了解了MySQL的執(zhí)行過(guò)程，我們才知道如何進(jìn)行sql優(yōu)化。
           
           

            
            

             
             

              
              客戶端發(fā)送一條查詢語(yǔ)句到服務(wù)器；
             
             
            
            

             
             

              
              服務(wù)器先查詢緩存，如果命中緩存，則立即返回存儲(chǔ)在緩存中的數(shù)據(jù)；
             
             
            
            

             
             

              
              未命中緩存后，MySQL通過(guò)關(guān)鍵字將SQL語(yǔ)句進(jìn)行解析，并生成一顆對(duì)應(yīng)的解析樹，MySQL解析器將使用MySQL語(yǔ)法進(jìn)行驗(yàn)證和解析。例如，驗(yàn)證是否使用了錯(cuò)誤的關(guān)鍵字，或者關(guān)鍵字的使用是否正確；
             
             
            
            

             
             

              
              預(yù)處理是根據(jù)一些MySQL規(guī)則檢查解析樹是否合理，比如檢查表和列是否存在，還會(huì)解析名字和別名，然后預(yù)處理器會(huì)驗(yàn)證權(quán)限；
             
             
            
            

             
             

              
              根據(jù)執(zhí)行計(jì)劃查詢執(zhí)行引擎，調(diào)用API接口調(diào)用存儲(chǔ)引擎來(lái)查詢數(shù)據(jù)；
             
             
            
            

             
             

              
              將結(jié)果返回客戶端，并進(jìn)行緩存；
             
             
           
           
           
           

            
            
           
           

2、數(shù)據(jù)庫(kù)常見(jiàn)規(guī)范

           
           

            
            

             
             

              
              所有數(shù)據(jù)庫(kù)對(duì)象名稱必須使用小寫字母并用下劃線分割；
             
             
            
            

             
             

              
              所有數(shù)據(jù)庫(kù)對(duì)象名稱禁止使用mysql保留關(guān)鍵字；
             
             
            
            

             
             

              
              數(shù)據(jù)庫(kù)對(duì)象的命名要能做到見(jiàn)名識(shí)意，并且最后不要超過(guò)32個(gè)字符；
             
             
            
            

             
             

              
              臨時(shí)庫(kù)表必須以tmp_為前綴并以日期為后綴，備份表必須以bak_為前綴并以日期(時(shí)間戳)為后綴；
             
             
            
            

             
             

              
              所有存儲(chǔ)相同數(shù)據(jù)的列名和列類型必須一致；
             
             
           
           

3、所有表必須使用Innodb存儲(chǔ)引擎

沒(méi)有特殊要求（即Innodb無(wú)法滿足的功能如：列存儲(chǔ)，存儲(chǔ)空間數(shù)據(jù)等）的情況下，所有表必須使用Innodb存儲(chǔ)引擎（mysql5.5之前默認(rèn)使用Myisam，5.6以后默認(rèn)的為Innodb）。
Innodb 支持事務(wù)，支持行級(jí)鎖，更好的恢復(fù)性，高并發(fā)下性能更好。

4、每個(gè)Innodb表必須有個(gè)主鍵

Innodb是一種索引組織表：數(shù)據(jù)的存儲(chǔ)的邏輯順序和索引的順序是相同的。每個(gè)表都可以有多個(gè)索引，但是表的存儲(chǔ)順序只能有一種。
Innodb是按照主鍵索引的順序來(lái)組織表的
           
           

            
            

             
             

              
              不要使用更新頻繁的列作為主鍵，不適用多列主鍵；
             
             
            
            

             
             

              
              不要使用UUID、MD5、HASH、字符串列作為主鍵（無(wú)法保證數(shù)據(jù)的順序增長(zhǎng)）；
             
             
            
            

             
             

              
              主鍵建議使用自增ID值；
             
             
           
           

5、數(shù)據(jù)庫(kù)和表的字符集統(tǒng)一使用UTF8

兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼，不同的字符集進(jìn)行比較前需要進(jìn)行轉(zhuǎn)換會(huì)造成索引失效，如果數(shù)據(jù)庫(kù)中有存儲(chǔ)emoji表情的需要，字符集需要采用utf8mb4字符集。

6、查詢SQL盡量不要使用select *，而是具體字段

select *的弊端：
           
           

            
            

             
             

              
              增加很多不必要的消耗，比如CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬；
             
             
            
            

             
             

              
              增加了使用覆蓋索引的可能性；
             
             
            
            

             
             

              
              增加了回表的可能性；
             
             
            
            

             
             

              
              當(dāng)表結(jié)構(gòu)發(fā)生變化時(shí)，前端也需要更改；
             
             
            
            

             
             

              
              查詢效率低；
             
             
           
           

7、避免在where子句中使用 or 來(lái)連接條件

           
           

            
            

             
             

              
              使用or可能會(huì)使索引失效，從而全表掃描；
             
             
            
            

             
             

              
              對(duì)于or沒(méi)有索引的salary這種情況，假設(shè)它走了id的索引，但是走到salary查詢條件時(shí)，它還得全表掃描；
             
             
            
            

             
             

              
              也就是說(shuō)整個(gè)過(guò)程需要三步：全表掃描+索引掃描+合并。如果它一開(kāi)始就走全表掃描，直接一遍掃描就搞定；
             
             
            
            

             
             

              
              雖然mysql是有優(yōu)化器的，處于效率與成本考慮，遇到or條件，索引還是可能失效的；
             
             
           
           

8、盡量使用數(shù)值替代字符串類型

           
           

            
            

             
             

              
              因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符；
             
             
            
            

             
             

              
              而對(duì)于數(shù)字型而言只需要比較一次就夠了；
             
             
            
            

             
             

              
              字符會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷；
             
             
           
           

9、使用varchar代替char

           
           

            
            

             
             

              varchar變長(zhǎng)字段按數(shù)據(jù)內(nèi)容實(shí)際長(zhǎng)度存儲(chǔ)，存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間；
             
             
            
            

             
             

              char按聲明大小存儲(chǔ)，不足補(bǔ)空格；
             
             
            
            

             
             

              
              其次對(duì)于查詢來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索，效率更高；
             
             
           
           

10、財(cái)務(wù)、銀行相關(guān)的金額字段必須使用decimal類型

           
           

            
            

             
             

              
              非精準(zhǔn)浮點(diǎn)：float,double
             
             
            
            

             
             

              
              精準(zhǔn)浮點(diǎn)：decimal
             
             
           
           
           
           

            
            

             
             

              
              Decimal類型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會(huì)丟失精度；
             
             
            
            

             
             

              
              占用空間由定義的寬度決定，每4個(gè)字節(jié)可以存儲(chǔ)9位數(shù)字，并且小數(shù)點(diǎn)要占用一個(gè)字節(jié)；
             
             
            
            

             
             

              
              可用于存儲(chǔ)比bigint更大的整型數(shù)據(jù)；
             
             
           
           

11、避免使用ENUM類型

           
           

            
            

             
             

              
              修改ENUM值需要使用ALTER語(yǔ)句；
             
             
            
            

             
             

              
              ENUM類型的ORDER BY操作效率低，需要額外操作；
             
             
            
            

             
             

              
              禁止使用數(shù)值作為ENUM的枚舉值；
             
             
           
           

12、去重distinct過(guò)濾字段要少

           
           

            
            

             
             

              
              帶distinct的語(yǔ)句占用cpu時(shí)間高于不帶distinct的語(yǔ)句
             
             
            
            

             
             

              
              當(dāng)查詢很多字段時(shí)，如果使用distinct，數(shù)據(jù)庫(kù)引擎就會(huì)對(duì)數(shù)據(jù)進(jìn)行比較，過(guò)濾掉重復(fù)數(shù)據(jù)
             
             
            
            

             
             

              
              然而這個(gè)比較、過(guò)濾的過(guò)程會(huì)占用系統(tǒng)資源，如cpu時(shí)間
             
             
           
           

13、where中使用默認(rèn)值代替null

           
           

            
            

             
             

              
              并不是說(shuō)使用了is null或者 is not null就會(huì)不走索引了，這個(gè)跟mysql版本以及查詢成本都有關(guān)；
             
             
            
            

             
             

              
              如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會(huì)放棄索引，這些條件 !=，<>，is null，is not null經(jīng)常被認(rèn)為讓索引失效；
             
             
            
            

             
             

              
              其實(shí)是因?yàn)橐话闱闆r下，查詢的成本高，優(yōu)化器自動(dòng)放棄索引的；
             
             
            
            

             
             

              
              如果把null值，換成默認(rèn)值，很多時(shí)候讓走索引成為可能，同時(shí)，表達(dá)意思也相對(duì)清晰一點(diǎn)；
             
             
           
           

14、避免在where子句中使用!=或<>操作符

           
           

            
            

             
             

              
              使用!=和<>很可能會(huì)讓索引失效
             
             
            
            

             
             

              
              應(yīng)盡量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描
             
             
            
            

             
             

              
              實(shí)現(xiàn)業(yè)務(wù)優(yōu)先，實(shí)在沒(méi)辦法，就只能使用，并不是不能使用
             
             
           
           

15、inner join 、left join、right join，優(yōu)先使用inner join

三種連接如果結(jié)果相同，優(yōu)先使用inner join，如果使用left join左邊表盡量小。
           
           

            
            

             
             

              
              inner join 內(nèi)連接，只保留兩張表中完全匹配的結(jié)果集；
             
             
            
            

             
             

              
              left join會(huì)返回左表所有的行，即使在右表中沒(méi)有匹配的記錄；
             
             
            
            

             
             

              
              right join會(huì)返回右表所有的行，即使在左表中沒(méi)有匹配的記錄；
             
             
           
           
為什么？
           
           

            
            

             
             

              
              如果inner join是等值連接，返回的行數(shù)比較少，所以性能相對(duì)會(huì)好一點(diǎn)；
             
             
            
            

             
             

              
              使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少；
             
             
            
            

             
             

              
              這是mysql優(yōu)化原則，就是小表驅(qū)動(dòng)大表，小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集，從而讓性能更優(yōu)；
             
             
           
           

16、提高group by語(yǔ)句的效率

1、反例
先分組，再過(guò)濾
select job, avg（salary） from employee 
group by job
having job ='develop' or job = 'test';
2、正例
先過(guò)濾，后分組
select job，avg（salary） from employee 
where job ='develop' or job = 'test' 
group by job;
3、理由
可以在執(zhí)行到該語(yǔ)句前，把不需要的記錄過(guò)濾掉

17、清空表時(shí)優(yōu)先使用truncate

truncate table在功能上與不帶 where子句的 delete語(yǔ)句相同：二者均刪除表中的全部行。但 truncate table比 delete速度快，且使用的系統(tǒng)和事務(wù)日志資源少。
delete語(yǔ)句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。truncate table通過(guò)釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁(yè)來(lái)刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁(yè)的釋放。
truncate table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用 DELETE。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 drop table語(yǔ)句。
對(duì)于由 foreign key約束引用的表，不能使用 truncate table，而應(yīng)使用不帶  where子句的 DELETE 語(yǔ)句。由于 truncate table不記錄在日志中，所以它不能激活觸發(fā)器。
truncate table不能用于參與了索引視圖的表。

18、操作delete或者update語(yǔ)句，加個(gè)limit或者循環(huán)分批次刪除

（1）降低寫錯(cuò)SQL的代價(jià)
清空表數(shù)據(jù)可不是小事情，一個(gè)手抖全沒(méi)了，刪庫(kù)跑路？如果加limit，刪錯(cuò)也只是丟失部分?jǐn)?shù)據(jù)，可以通過(guò)binlog日志快速恢復(fù)的。
（2）SQL效率很可能更高
SQL中加了limit 1，如果第一條就命中目標(biāo)return， 沒(méi)有limit的話，還會(huì)繼續(xù)執(zhí)行掃描表。
（3）避免長(zhǎng)事務(wù)
delete執(zhí)行時(shí),如果age加了索引，MySQL會(huì)將所有相關(guān)的行加寫鎖和間隙鎖，所有執(zhí)行相關(guān)行會(huì)被鎖住，如果刪除數(shù)量大，會(huì)直接影響相關(guān)業(yè)務(wù)無(wú)法使用。
（4）數(shù)據(jù)量大的話，容易把CPU打滿
如果你刪除數(shù)據(jù)量很大時(shí)，不加 limit限制一下記錄數(shù)，容易把cpu打滿，導(dǎo)致越刪越慢。
（5）鎖表
一次性刪除太多數(shù)據(jù)，可能造成鎖表，會(huì)有l(wèi)ock wait timeout exceed的錯(cuò)誤，所以建議分批操作。

19、UNION操作符

UNION在進(jìn)行表鏈接后會(huì)篩選掉重復(fù)的記錄，所以在表鏈接后會(huì)對(duì)所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。實(shí)際大部分應(yīng)用中是不會(huì)產(chǎn)生重復(fù)的記錄，最常見(jiàn)的是過(guò)程表與歷史表UNION。如：
select username,tel from user
union
select departmentname from department
這個(gè)SQL在運(yùn)行時(shí)先取出兩個(gè)表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會(huì)導(dǎo)致用磁盤進(jìn)行排序。推薦方案：采用UNION ALL操作符替代UNION，因?yàn)?code style="margin-right: 2px;margin-left: 2px;padding: 2px 4px;outline: 0px;font-size: 14px;border-radius: 4px;background-color: rgba(27, 31, 35, 0.047);font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(239, 112, 96);">UNION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。

20、SQL語(yǔ)句中IN包含的字段不宜過(guò)多

MySQL的IN中的常量全部存儲(chǔ)在一個(gè)數(shù)組中，這個(gè)數(shù)組是排序的。如果值過(guò)多，產(chǎn)生的消耗也是比較大的。如果是連續(xù)的數(shù)字，可以使用between代替，或者使用連接查詢替換。另外，搜索公眾號(hào)Linux就該這樣學(xué)后臺(tái)回復(fù)“猴子”，獲取一份驚喜禮包。

21、批量插入性能提升

（1）多條提交
INSERT INTO user (id,username) VALUES(1,'哪吒編程');

INSERT INTO user (id,username) VALUES(2,'妲己');
（2）批量提交
INSERT INTO user (id,username) VALUES(1,'哪吒編程'),(2,'妲己');
默認(rèn)新增SQL有事務(wù)控制，導(dǎo)致每條都需要事務(wù)開(kāi)啟和事務(wù)提交，而批量處理是一次事務(wù)開(kāi)啟和提交，效率提升明顯，達(dá)到一定量級(jí)，效果顯著，平時(shí)看不出來(lái)。

22、表連接不宜太多，索引不宜太多，一般5個(gè)以內(nèi)

（1）表連接不宜太多，一般5個(gè)以內(nèi)
           
           

            
            

             
             

              
              關(guān)聯(lián)的表個(gè)數(shù)越多，編譯的時(shí)間和開(kāi)銷也就越大
             
             
            
            

             
             

              
              每次關(guān)聯(lián)內(nèi)存中都生成一個(gè)臨時(shí)表
             
             
            
            

             
             

              
              應(yīng)該把連接表拆開(kāi)成較小的幾個(gè)執(zhí)行，可讀性更高
             
             
            
            

             
             

              
              如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個(gè)糟糕的設(shè)計(jì)了
             
             
            
            

             
             

              
              阿里規(guī)范中，建議多表聯(lián)查三張表以下
             
             
           
           
（2）索引不宜太多，一般5個(gè)以內(nèi)
           
           

            
            

             
             

              
              索引并不是越多越好，雖其提高了查詢的效率，但卻會(huì)降低插入和更新的效率；
             
             
            
            

             
             

              
              索引可以理解為一個(gè)就是一張表，其可以存儲(chǔ)數(shù)據(jù)，其數(shù)據(jù)就要占空間；
             
             
            
            

             
             

              
              索引表的數(shù)據(jù)是排序的，排序也是要花時(shí)間的；
             
             
            
            

             
             

              insert或update時(shí)有可能會(huì)重建索引，如果數(shù)據(jù)量巨大，重建將進(jìn)行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來(lái)定；
             
             
            
            

             
             

              
              一個(gè)表的索引數(shù)最好不要超過(guò)5個(gè)，若太多需要考慮一些索引是否有存在的必要；
             
             
           
           

23、禁止給表中的每一列都建立單獨(dú)的索引

真有這么干的，我也是醉了。
2萬(wàn)字帶你精通MySQL索引

24、如何選擇索引列的順序

建立索引的目的是：希望通過(guò)索引進(jìn)行數(shù)據(jù)查找，減少隨機(jī)IO，增加查詢性能 ，索引能過(guò)濾出越少的數(shù)據(jù)，則從磁盤中讀入的數(shù)據(jù)也就越少。
區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）。
盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L(zhǎng)度越小，一頁(yè)能存儲(chǔ)的數(shù)據(jù)量越大，IO性能也就越好）。
使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）。

25、對(duì)于頻繁的查詢優(yōu)先考慮使用覆蓋索引

覆蓋索引：就是包含了所有查詢字段(where,select,ordery by,group by包含的字段)的索引。
覆蓋索引的好處：
（1）避免Innodb表進(jìn)行索引的二次查詢
Innodb是以聚集索引的順序來(lái)存儲(chǔ)的，對(duì)于Innodb來(lái)說(shuō)，二級(jí)索引在葉子節(jié)點(diǎn)中所保存的是行的主鍵信息，如果是用二級(jí)索引查詢數(shù)據(jù)的話，在查找到相應(yīng)的鍵值后，還要通過(guò)主鍵進(jìn)行二次查詢才能獲取我們真實(shí)所需要的數(shù)據(jù)。
而在覆蓋索引中，二級(jí)索引的鍵值中可以獲取所有的數(shù)據(jù)，避免了對(duì)主鍵的二次查詢 ，減少了IO操作，提升了查詢效率。
（2）可以把隨機(jī)IO變成順序IO加快查詢效率
由于覆蓋索引是按鍵值的順序存儲(chǔ)的，對(duì)于IO密集型的范圍查找來(lái)說(shuō)，對(duì)比隨機(jī)從磁盤讀取每一行的數(shù)據(jù)IO要少的多，因此利用覆蓋索引在訪問(wèn)時(shí)也可以把磁盤的隨機(jī)讀取的IO轉(zhuǎn)變成索引查找的順序IO。

26、建議使用預(yù)編譯語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)操作

預(yù)編譯語(yǔ)句可以重復(fù)使用這些計(jì)劃，減少SQL編譯所需要的時(shí)間，還可以解決動(dòng)態(tài)SQL所帶來(lái)的SQL注入的問(wèn)題。
只傳參數(shù)，比傳遞SQL語(yǔ)句更高效。
相同語(yǔ)句可以一次解析，多次使用，提高處理效率。

27、避免產(chǎn)生大事務(wù)操作

大批量修改數(shù)據(jù)，一定是在一個(gè)事務(wù)中進(jìn)行的，這就會(huì)造成表中大批量數(shù)據(jù)進(jìn)行鎖定，從而導(dǎo)致大量的阻塞，阻塞會(huì)對(duì)MySQL的性能產(chǎn)生非常大的影響。
特別是長(zhǎng)時(shí)間的阻塞會(huì)占滿所有數(shù)據(jù)庫(kù)的可用連接，這會(huì)使生產(chǎn)環(huán)境中的其他應(yīng)用無(wú)法連接到數(shù)據(jù)庫(kù)，因此一定要注意大批量寫操作要進(jìn)行分批。

28、避免在索引列上使用內(nèi)置函數(shù)

使用索引列上內(nèi)置函數(shù)，索引失效。

29、組合索引

排序時(shí)應(yīng)按照組合索引中各列的順序進(jìn)行排序，即使索引中只有一個(gè)列是要排序的，否則排序性能會(huì)比較差。
create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java開(kāi)發(fā)' order by deptid,position,createtime desc; 
實(shí)際上只是查詢出符合 deptid= 1 and position = 'java開(kāi)發(fā)'條件的記錄并按createtime降序排序，但寫成order by createtime desc性能較差。

30、復(fù)合索引最左特性

（1）創(chuàng)建復(fù)合索引
ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
（2）滿足復(fù)合索引的最左特性，哪怕只是部分，復(fù)合索引生效
SELECT * FROM employee WHERE NAME='哪吒編程'
（3）沒(méi)有出現(xiàn)左邊的字段，則不滿足最左特性，索引失效
SELECT * FROM employee WHERE salary=5000
（4）復(fù)合索引全使用，按左側(cè)順序出現(xiàn) name,salary，索引生效
SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000
（5）雖然違背了最左特性，但MySQL執(zhí)行SQL時(shí)會(huì)進(jìn)行優(yōu)化，底層進(jìn)行顛倒優(yōu)化
SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程'
（6）理由
復(fù)合索引也稱為聯(lián)合索引，當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個(gè)索引，這就是最左匹配原則。
聯(lián)合索引不滿足最左原則，索引一般會(huì)失效。

31、必要時(shí)可以使用force index來(lái)強(qiáng)制查詢走某個(gè)索引

有的時(shí)候MySQL優(yōu)化器采取它認(rèn)為合適的索引來(lái)檢索SQL語(yǔ)句，但是可能它所采用的索引并不是我們想要的。這時(shí)就可以采用forceindex來(lái)強(qiáng)制優(yōu)化器使用我們制定的索引。

32、優(yōu)化like語(yǔ)句

模糊查詢，程序員最喜歡的就是使用like，但是like很可能讓你的索引失效。
           
           

            
            

             
             

              
              首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like ‘…%’，是會(huì)使用索引的；
             
             
            
            

             
             

              
              左模糊like ‘%...’無(wú)法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
             
             
            
            

             
             

              
              全模糊查詢是無(wú)法優(yōu)化的，一定要使用的話建議使用搜索引擎。
             
             
           
           

33、統(tǒng)一SQL語(yǔ)句的寫法

對(duì)于以下兩句SQL語(yǔ)句， 程序員認(rèn)為是相同的，數(shù)據(jù)庫(kù)查詢優(yōu)化器認(rèn)為是不同的。
select * from user;
select * From USER;
這都是很常見(jiàn)的寫法，也很少有人會(huì)注意，就是表名大小寫不一樣而已。然而，查詢解析器認(rèn)為這是兩個(gè)不同的SQL語(yǔ)句，要解析兩次，生成兩個(gè)不同的執(zhí)行計(jì)劃，作為一名嚴(yán)謹(jǐn)?shù)腏ava開(kāi)發(fā)工程師，應(yīng)該保證兩個(gè)一樣的SQL語(yǔ)句，不管在任何地方都是一樣的。

34、不要把SQL語(yǔ)句寫得太復(fù)雜

經(jīng)常聽(tīng)到有人吹牛逼，我寫了一個(gè)800行的SQL語(yǔ)句，邏輯感超強(qiáng)，我們還開(kāi)會(huì)進(jìn)行了SQL講解，大家都投來(lái)了崇拜的目光。。。
一般來(lái)說(shuō)，嵌套子查詢、或者是3張表關(guān)聯(lián)查詢還是比較常見(jiàn)的，但是，如果超過(guò)3層嵌套的話，查詢優(yōu)化器很容易給出錯(cuò)誤的執(zhí)行計(jì)劃，影響SQL效率。SQL執(zhí)行計(jì)劃是可以被重用的，SQL越簡(jiǎn)單，被重用的概率越大，生成執(zhí)行計(jì)劃也是很耗時(shí)的。

35、將大的DELETE，UPDATE、INSERT 查詢變成多個(gè)小查詢

能寫一個(gè)幾十行、幾百行的SQL語(yǔ)句是不是顯得逼格很高？然而，為了達(dá)到更好的性能以及更好的數(shù)據(jù)控制，你可以將他們變成多個(gè)小查詢。

36、關(guān)于臨時(shí)表

           
           

            
            

             
             

              
              避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗；
             
             
            
            

             
             

              
              在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
             
             
            
            

             
             

              
              如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert；
             
             
            
            

             
             

              
              如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除。先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。
             
             
           
           

37、使用explain分析你SQL執(zhí)行計(jì)劃

（1）type
           
           

            
            

             
             

              
              system：表僅有一行，基本用不到；
             
             
            
            

             
             

              
              const：表最多一行數(shù)據(jù)配合，主鍵查詢時(shí)觸發(fā)較多；
             
             
            
            

             
             

              
              eq_ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；
             
             
            
            

             
             

              
              ref：對(duì)于每個(gè)來(lái)自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀?。?             
             
            
            

             
             

              
              range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，可以使用range；
             
             
            
            

             
             

              
              index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件?。?             
             
            
            

             
             

              
              all：全表掃描；
             
             
            
            

             
             

              
              性能排名：system > const > eq_ref > ref > range > index > all。
             
             
            
            

             
             

              
              實(shí)際sql優(yōu)化中，最后達(dá)到ref或range級(jí)別。
             
             
           
           
（2）Extra常用關(guān)鍵字
           
           

            
            

             
             

              
              Using index：只從索引樹中獲取信息，而不需要回表查詢；
             
             
            
            

             
             

              
              Using where：WHERE子句用于限制哪一個(gè)行匹配下一個(gè)表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會(huì)有一些錯(cuò)誤。需要回表查詢。
             
             
            
            

             
             

              
              Using temporary：mysql常建一個(gè)臨時(shí)表來(lái)容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)；
             
             
           
           

38、讀寫分離與分庫(kù)分表

當(dāng)數(shù)據(jù)量達(dá)到一定的數(shù)量之后，限制數(shù)據(jù)庫(kù)存儲(chǔ)性能的就不再是數(shù)據(jù)庫(kù)層面的優(yōu)化就能夠解決的；這個(gè)時(shí)候往往采用的是讀寫分離與分庫(kù)分表同時(shí)也會(huì)結(jié)合緩存一起使用，而這個(gè)時(shí)候數(shù)據(jù)庫(kù)層面的優(yōu)化只是基礎(chǔ)。
讀寫分離適用于較小一些的數(shù)據(jù)量；分表適用于中等數(shù)據(jù)量；而分庫(kù)與分表一般是結(jié)合著用，這就適用于大數(shù)據(jù)量的存儲(chǔ)了，這也是現(xiàn)在大型互聯(lián)網(wǎng)公司解決數(shù)據(jù)存儲(chǔ)的方法之一。

39、使用合理的分頁(yè)方式以提高分頁(yè)的效率

select id,name from user limit 100000, 20
使用上述SQL語(yǔ)句做分頁(yè)的時(shí)候，隨著表數(shù)據(jù)量的增加，直接使用limit語(yǔ)句會(huì)越來(lái)越慢。
此時(shí)，可以通過(guò)取前一頁(yè)的最大ID，以此為起點(diǎn)，再進(jìn)行l(wèi)imit操作，效率提升顯著。
select id,name from user where id> 100000 limit 20

40、盡量控制單表數(shù)據(jù)量的大小，建議控制在500萬(wàn)以內(nèi)。

500萬(wàn)并不是MySQL數(shù)據(jù)庫(kù)的限制，過(guò)大會(huì)造成修改表結(jié)構(gòu)，備份，恢復(fù)都會(huì)有很大的問(wèn)題。
可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)），分庫(kù)分表（應(yīng)用于業(yè)務(wù)數(shù)據(jù)）等手段來(lái)控制數(shù)據(jù)量大小。

41、謹(jǐn)慎使用Mysql分區(qū)表

           
           

            
            

             
             

              
              分區(qū)表在物理上表現(xiàn)為多個(gè)文件，在邏輯上表現(xiàn)為一個(gè)表；
             
             
            
            

             
             

              
              謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低；
             
             
            
            

             
             

              
              建議采用物理分表的方式管理大數(shù)據(jù)。
             
             
           
           

42、盡量做到冷熱數(shù)據(jù)分離，減小表的寬度

Mysql限制每個(gè)表最多存儲(chǔ)4096列，并且每一行數(shù)據(jù)的大小不能超過(guò)65535字節(jié)。
減少磁盤IO,保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬，把表裝載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大,也會(huì)消耗更多的IO）；
更有效的利用緩存，避免讀入無(wú)用的冷數(shù)據(jù)；
經(jīng)常一起使用的列放到一個(gè)表中（避免更多的關(guān)聯(lián)操作）。

43、禁止在表中建立預(yù)留字段

           
           

            
            

             
             

              
              預(yù)留字段的命名很難做到見(jiàn)名識(shí)義；
             
             
            
            

             
             

              
              預(yù)留字段無(wú)法確認(rèn)存儲(chǔ)的數(shù)據(jù)類型，所以無(wú)法選擇合適的類型；
             
             
            
            

             
             

              
              對(duì)預(yù)留字段類型的修改，會(huì)對(duì)表進(jìn)行鎖定；
             
             
           
           

44、禁止在數(shù)據(jù)庫(kù)中存儲(chǔ)圖片，文件等大的二進(jìn)制數(shù)據(jù)

通常文件很大，會(huì)短時(shí)間內(nèi)造成數(shù)據(jù)量快速增長(zhǎng)，數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)庫(kù)讀取時(shí)，通常會(huì)進(jìn)行大量的隨機(jī)IO操作，文件很大時(shí)，IO操作很耗時(shí)。
通常存儲(chǔ)于文件服務(wù)器，數(shù)據(jù)庫(kù)只存儲(chǔ)文件地址信息。

45、建議把BLOB或是TEXT列分離到單獨(dú)的擴(kuò)展表中

Mysql內(nèi)存臨時(shí)表不支持TEXT、BLOB這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時(shí)，就不能使用內(nèi)存臨時(shí)表，必須使用磁盤臨時(shí)表進(jìn)行。而且對(duì)于這種數(shù)據(jù)，Mysql還是要進(jìn)行二次查詢，會(huì)使sql性能變得很差，但是不是說(shuō)一定不能使用這樣的數(shù)據(jù)類型。
如果一定要使用，建議把BLOB或是TEXT列分離到單獨(dú)的擴(kuò)展表中，查詢時(shí)一定不要使用select * 而只需要取出必要的列，不需要TEXT列的數(shù)據(jù)時(shí)不要對(duì)該列進(jìn)行查詢。

46、TEXT或BLOB類型只能使用前綴索引

因?yàn)镸ySQL對(duì)索引字段長(zhǎng)度是有限制的，所以TEXT類型只能使用前綴索引，并且TEXT列上是不能有默認(rèn)值的。
MySql基礎(chǔ)知識(shí)總結(jié)（SQL優(yōu)化篇）

47、一些其它優(yōu)化方式

（1）當(dāng)只需要一條數(shù)據(jù)的時(shí)候，使用limit 1
limit 1可以避免全表掃描，找到對(duì)應(yīng)結(jié)果就不會(huì)再繼續(xù)掃描了。
（2）如果排序字段沒(méi)有用到索引，就盡量少排序
（3）所有表和字段都需要添加注釋
使用comment從句添加表和列的備注，從一開(kāi)始就進(jìn)行數(shù)據(jù)字典的維護(hù)。
（4）SQL書寫格式，關(guān)鍵字大小保持一致，使用縮進(jìn)。
（5）修改或刪除重要數(shù)據(jù)前，要先備份。
（6）很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇
（7）where后面的字段，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。
（8）盡量把所有列定義為NOT NULL
NOT NULL列更節(jié)省空間，NULL列需要一個(gè)額外字節(jié)作為判斷是否為 NULL的標(biāo)志位。NULL列需要注意空指針問(wèn)題，NULL列在計(jì)算和比較的時(shí)候，需要注意空指針問(wèn)題。
（9）偽刪除設(shè)計(jì)
（10）索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引
（11）盡量避免使用游標(biāo)
因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫。






           
           

            
            
            
            

           
           
           
           

            回復(fù) 【關(guān)閉】學(xué)永久關(guān)閉App開(kāi)屏廣告
           
           
           
           

            回復(fù) 【刪除】學(xué)自動(dòng)檢測(cè)那個(gè)微信好友刪除、拉黑
           
           
           
           

            回復(fù) 【手冊(cè)】獲取3萬(wàn)字.NET、C#工程師面試手冊(cè)
           
           
           
           

            回復(fù) 【幫助】獲取100+個(gè)常用的C#幫助類庫(kù)
           
           
           
           

            回復(fù) 【加群】加入DotNet學(xué)習(xí)交流群