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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

沒有特殊要求（即Innodb無法滿足的功能如：列存儲(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是按照主鍵索引的順序來組織表的：

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

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

（3）主鍵建議使用自增ID值；

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

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

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

select *的弊端：

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

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

（3）增加了回表的可能性；

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

（5）查詢效率低；

7、避免在where子句中使用 or 來連接條件

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

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

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

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

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

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

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

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

9、使用varchar代替char

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

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

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

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

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

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

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

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

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

11、避免使用ENUM類型

（1）修改ENUM值需要使用ALTER語句；

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

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

12、去重distinct過濾字段要少

（1）帶distinct的語句占用cpu時(shí)間高于不帶distinct的語句

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

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

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

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

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

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

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

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

（1）使用!=和很可能會(huì)讓索引失效

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

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

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ì)返回左表所有的行，即使在右表中沒有匹配的記錄；

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

為什么？

如果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語句的效率

（1）反例

先分組，再過濾

select job, avg（salary） from employee

group by job

having job ='develop' or job = 'test';

（2）正例

先過濾，后分組

select job，avg（salary） from employee

where job ='develop' or job = 'test'

group by job;

（3）理由

可以在執(zhí)行到該語句前，把不需要的記錄過濾掉

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

truncate table在功能上與不帶 where子句的 delete語句相同：二者均刪除表中的全部行。但 truncate table比 delete速度快，且使用的系統(tǒng)和事務(wù)日志資源少。

delete語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。truncate table通過釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。

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語句。

對(duì)于由 foreign key約束引用的表，不能使用 truncate table，而應(yīng)使用不帶 where子句的 DELETE 語句。由于 truncate table不記錄在日志中，所以它不能激活觸發(fā)器。

truncate table不能用于參與了索引視圖的表。

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

（1）降低寫錯(cuò)SQL的代價(jià)

清空表數(shù)據(jù)可不是小事情，一個(gè)手抖全沒了，刪庫跑路？如果加limit，刪錯(cuò)也只是丟失部分?jǐn)?shù)據(jù)，可以通過binlog日志快速恢復(fù)的。

（2）SQL效率很可能更高

SQL中加了limit 1，如果第一條就命中目標(biāo)return， 沒有l(wèi)imit的話，還會(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ù)無法使用。

（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ù)的記錄，最常見的是過程表與歷史表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)閁NION ALL操作只是簡(jiǎn)單的將兩個(gè)結(jié)果合并后就返回。

20、SQL語句中IN包含的字段不宜過多

MySQL的IN中的常量全部存儲(chǔ)在一個(gè)數(shù)組中，這個(gè)數(shù)組是排序的。如果值過多，產(chǎn)生的消耗也是比較大的。如果是連續(xù)的數(shù)字，可以使用between代替，或者使用連接查詢替換。

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ù)開啟和事務(wù)提交，而批量處理是一次事務(wù)開啟和提交，效率提升明顯，達(dá)到一定量級(jí)，效果顯著，平時(shí)看不出來。

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

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

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

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

應(yīng)該把連接表拆開成較小的幾個(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)行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定；

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

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

真有這么干的，我也是醉了。

2萬字帶你精通MySQL索引

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

建立索引的目的是：希望通過索引進(jìn)行數(shù)據(jù)查找，減少隨機(jī)IO，增加查詢性能 ，索引能過濾出越少的數(shù)據(jù)，則從磁盤中讀入的數(shù)據(jù)也就越少。

區(qū)分度最高的放在聯(lián)合索引的最左側(cè)（區(qū)分度=列中不同值的數(shù)量/列的總行數(shù)）。

盡量把字段長(zhǎng)度小的列放在聯(lián)合索引的最左側(cè)（因?yàn)樽侄伍L(zhǎng)度越小，一頁能存儲(chǔ)的數(shù)據(jù)量越大，IO性能也就越好）。

使用最頻繁的列放到聯(lián)合索引的左側(cè)（這樣可以比較少的建立一些索引）。

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

覆蓋索引：就是包含了所有查詢字段(where,select,ordery by,group by包含的字段)的索引。

覆蓋索引的好處：

（1）避免Innodb表進(jìn)行索引的二次查詢

Innodb是以聚集索引的順序來存儲(chǔ)的，對(duì)于Innodb來說，二級(jí)索引在葉子節(jié)點(diǎn)中所保存的是行的主鍵信息，如果是用二級(jí)索引查詢數(shù)據(jù)的話，在查找到相應(yīng)的鍵值后，還要通過主鍵進(jìn)行二次查詢才能獲取我們真實(shí)所需要的數(shù)據(jù)。

而在覆蓋索引中，二級(jí)索引的鍵值中可以獲取所有的數(shù)據(jù)，避免了對(duì)主鍵的二次查詢 ，減少了IO操作，提升了查詢效率。

（2）可以把隨機(jī)IO變成順序IO加快查詢效率

由于覆蓋索引是按鍵值的順序存儲(chǔ)的，對(duì)于IO密集型的范圍查找來說，對(duì)比隨機(jī)從磁盤讀取每一行的數(shù)據(jù)IO要少的多，因此利用覆蓋索引在訪問時(shí)也可以把磁盤的隨機(jī)讀取的IO轉(zhuǎn)變成索引查找的順序IO。

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

預(yù)編譯語句可以重復(fù)使用這些計(jì)劃，減少SQL編譯所需要的時(shí)間，還可以解決動(dòng)態(tài)SQL所帶來的SQL注入的問題。

只傳參數(shù)，比傳遞SQL語句更高效。

相同語句可以一次解析，多次使用，提高處理效率。

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ù)庫的可用連接，這會(huì)使生產(chǎn)環(huán)境中的其他應(yīng)用無法連接到數(shù)據(jù)庫，因此一定要注意大批量寫操作要進(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開發(fā)' order by deptid,position,createtime desc;

實(shí)際上只是查詢出符合 deptid= 1 and position = 'java開發(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）沒有出現(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來強(qiáng)制查詢走某個(gè)索引

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

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

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

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

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

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

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

對(duì)于以下兩句SQL語句， 程序員認(rèn)為是相同的，數(shù)據(jù)庫查詢優(yōu)化器認(rèn)為是不同的。

select * from user;

select * From USER;

這都是很常見的寫法，也很少有人會(huì)注意，就是表名大小寫不一樣而已。然而，查詢解析器認(rèn)為這是兩個(gè)不同的SQL語句，要解析兩次，生成兩個(gè)不同的執(zhí)行計(jì)劃，作為一名嚴(yán)謹(jǐn)?shù)腏ava開發(fā)工程師，應(yīng)該保證兩個(gè)一樣的SQL語句，不管在任何地方都是一樣的。

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

經(jīng)常聽到有人吹牛逼，我寫了一個(gè)800行的SQL語句，邏輯感超強(qiáng)，我們還開會(huì)進(jìn)行了SQL講解，大家都投來了崇拜的目光……

一般來說，嵌套子查詢、或者是3張表關(guān)聯(lián)查詢還是比較常見的，但是，如果超過3層嵌套的話，查詢優(yōu)化器很容易給出錯(cuò)誤的執(zhí)行計(jì)劃，影響SQL效率。SQL執(zhí)行計(jì)劃是可以被重用的，SQL越簡(jiǎn)單，被重用的概率越大，生成執(zhí)行計(jì)劃也是很耗時(shí)的。

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

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

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

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

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

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

（4）如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(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è)來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；

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

range：只檢索給定范圍的行，使用一個(gè)索引來選擇行。當(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í)表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時(shí)；

38、讀寫分離與分庫分表

當(dāng)數(shù)據(jù)量達(dá)到一定的數(shù)量之后，限制數(shù)據(jù)庫存儲(chǔ)性能的就不再是數(shù)據(jù)庫層面的優(yōu)化就能夠解決的；這個(gè)時(shí)候往往采用的是讀寫分離與分庫分表同時(shí)也會(huì)結(jié)合緩存一起使用，而這個(gè)時(shí)候數(shù)據(jù)庫層面的優(yōu)化只是基礎(chǔ)。

讀寫分離適用于較小一些的數(shù)據(jù)量；分表適用于中等數(shù)據(jù)量；而分庫與分表一般是結(jié)合著用，這就適用于大數(shù)據(jù)量的存儲(chǔ)了，這也是現(xiàn)在大型互聯(lián)網(wǎng)公司解決數(shù)據(jù)存儲(chǔ)的方法之一。

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

select id,name from user limit 100000, 20

使用上述SQL語句做分頁的時(shí)候，隨著表數(shù)據(jù)量的增加，直接使用limit語句會(huì)越來越慢。

此時(shí)，可以通過取前一頁的最大ID，以此為起點(diǎn)，再進(jìn)行l(wèi)imit操作，效率提升顯著。

select id,name from user where id 100000 limit 20

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

500萬并不是MySQL數(shù)據(jù)庫的限制，過大會(huì)造成修改表結(jié)構(gòu)，備份，恢復(fù)都會(huì)有很大的問題。

可以用歷史數(shù)據(jù)歸檔（應(yīng)用于日志數(shù)據(jù)），分庫分表（應(yīng)用于業(yè)務(wù)數(shù)據(jù)）等手段來控制數(shù)據(jù)量大小。

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

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

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

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

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

Mysql限制每個(gè)表最多存儲(chǔ)4096列，并且每一行數(shù)據(jù)的大小不能超過65535字節(jié)。

減少磁盤IO,保證熱數(shù)據(jù)的內(nèi)存緩存命中率（表越寬，把表裝載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大,也會(huì)消耗更多的IO）；

更有效的利用緩存，避免讀入無用的冷數(shù)據(jù)；

經(jīng)常一起使用的列放到一個(gè)表中（避免更多的關(guān)聯(lián)操作）。

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

（1）預(yù)留字段的命名很難做到見名識(shí)義；

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

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

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

通常文件很大，會(huì)短時(shí)間內(nèi)造成數(shù)據(jù)量快速增長(zhǎng)，數(shù)據(jù)庫進(jìn)行數(shù)據(jù)庫讀取時(shí)，通常會(huì)進(jìn)行大量的隨機(jī)IO操作，文件很大時(shí)，IO操作很耗時(shí)。

通常存儲(chǔ)于文件服務(wù)器，數(shù)據(jù)庫只存儲(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性能變得很差，但是不是說一定不能使用這樣的數(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）如果排序字段沒有用到索引，就盡量少排序

（3）所有表和字段都需要添加注釋

使用comment從句添加表和列的備注，從一開始就進(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列需要注意空指針問題，NULL列在計(jì)算和比較的時(shí)候，需要注意空指針問題。

（9）偽刪除設(shè)計(jì)

（10）索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引

（11）盡量避免使用游標(biāo)

因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

來源丨公眾號(hào)：哪吒編程（ID:gh_61b183bcf690）

dbaplus社群歡迎廣大技術(shù)人員投稿，投稿郵箱：editor@dbaplus.cn

活動(dòng)推薦

第八屆DAMS-中國數(shù)據(jù)智能管理峰會(huì)將于2023年3月31日在上海舉辦，與大家一起探索大數(shù)據(jù)與云原生強(qiáng)強(qiáng)聯(lián)合的方式、挖掘由此激發(fā)的軟件發(fā)展和技術(shù)進(jìn)步。

報(bào)名鏈接：https://www.bagevent.com/event/8100960?bag_track=SH

演講嘉賓所在單位：阿里、騰訊、京東、美團(tuán)、華為云、字節(jié)、螞蟻、網(wǎng)易、新浪、攜程、嗶哩嗶哩、小紅書、vivo、快狗打車、貨拉拉、工商銀行、建設(shè)銀行、中國銀行、平安銀行、光大銀行、匯豐銀行、微眾銀行、復(fù)旦大學(xué)等產(chǎn)學(xué)研界技術(shù)領(lǐng)跑單位。

演講議題聚焦：

大數(shù)據(jù)數(shù)據(jù)資產(chǎn)管理：數(shù)據(jù)治理丨存算分離丨云原生OLAP丨湖倉一體丨智能分析

數(shù)據(jù)庫：云原生分布式丨時(shí)間序列丨服務(wù)自治丨中間件丨跨云多活

運(yùn)維：AIOps丨故障分析丨性能優(yōu)化丨離在線混部丨高可用建設(shè)

金融科技：規(guī)模化監(jiān)控丨實(shí)時(shí)數(shù)倉丨分布式改造丨國產(chǎn)化替代丨數(shù)字化轉(zhuǎn)型丨混沌工程