大家好，我是哪吒。

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

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

1. 客戶端發(fā)送一條查詢語句到服務(wù)器；
2. 服務(wù)器先查詢緩存，如果命中緩存，則立即返回存儲在緩存中的數(shù)據(jù)；
3. 未命中緩存后，MySQL通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一顆對應(yīng)的解析樹，MySQL解析器將使用MySQL語法進(jìn)行驗證和解析。例如，驗證是否使用了錯誤的關(guān)鍵字，或者關(guān)鍵字的使用是否正確；
4. 預(yù)處理是根據(jù)一些MySQL規(guī)則檢查解析樹是否合理，比如檢查表和列是否存在，還會解析名字和別名，然后預(yù)處理器會驗證權(quán)限；
5. 根據(jù)執(zhí)行計劃查詢執(zhí)行引擎，調(diào)用API接口調(diào)用存儲引擎來查詢數(shù)據(jù)；
6. 將結(jié)果返回客戶端，并進(jìn)行緩存；

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

1. 所有數(shù)據(jù)庫對象名稱必須使用小寫字母并用下劃線分割；
2. 所有數(shù)據(jù)庫對象名稱禁止使用mysql保留關(guān)鍵字；
3. 數(shù)據(jù)庫對象的命名要能做到見名識意，并且最后不要超過32個字符；
4. 臨時庫表必須以tmp_為前綴并以日期為后綴，備份表必須以bak_為前綴并以日期(時間戳)為后綴；
5. 所有存儲相同數(shù)據(jù)的列名和列類型必須一致；

3、所有表必須使用Innodb存儲引擎

沒有特殊要求（即Innodb無法滿足的功能如：列存儲，存儲空間數(shù)據(jù)等）的情況下，所有表必須使用Innodb存儲引擎（mysql5.5之前默認(rèn)使用Myisam，5.6以后默認(rèn)的為Innodb）。

Innodb 支持事務(wù)，支持行級鎖，更好的恢復(fù)性，高并發(fā)下性能更好。

4、每個Innodb表必須有個主鍵

Innodb是一種索引組織表：數(shù)據(jù)的存儲的邏輯順序和索引的順序是相同的。每個表都可以有多個索引，但是表的存儲順序只能有一種。

Innodb是按照主鍵索引的順序來組織表的

1. 不要使用更新頻繁的列作為主鍵，不適用多列主鍵；
2. 不要使用UUID、MD5、HASH、字符串列作為主鍵（無法保證數(shù)據(jù)的順序增長）；
3. 主鍵建議使用自增ID值；

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

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

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

select *的弊端：

1. 增加很多不必要的消耗，比如CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬；
2. 增加了使用覆蓋索引的可能性；
3. 增加了回表的可能性；
4. 當(dāng)表結(jié)構(gòu)發(fā)生變化時，前端也需要更改；
5. 查詢效率低；

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

1. 使用or可能會使索引失效，從而全表掃描；
2. 對于or沒有索引的salary這種情況，假設(shè)它走了id的索引，但是走到salary查詢條件時，它還得全表掃描；
3. 也就是說整個過程需要三步：全表掃描+索引掃描+合并。如果它一開始就走全表掃描，直接一遍掃描就搞定；
4. 雖然mysql是有優(yōu)化器的，處于效率與成本考慮，遇到or條件，索引還是可能失效的；

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

1. 因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符；
2. 而對于數(shù)字型而言只需要比較一次就夠了；
3. 字符會降低查詢和連接的性能，并會增加存儲開銷；

9、使用varchar代替char

1. varchar變長字段按數(shù)據(jù)內(nèi)容實際長度存儲，存儲空間小，可以節(jié)省存儲空間；
2. char按聲明大小存儲，不足補(bǔ)空格；
3. 其次對于查詢來說，在一個相對較小的字段內(nèi)搜索，效率更高；

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

• 非精準(zhǔn)浮點：float,double
• 精準(zhǔn)浮點：decimal

1. Decimal類型為精準(zhǔn)浮點數(shù)，在計算時不會丟失精度；
2. 占用空間由定義的寬度決定，每4個字節(jié)可以存儲9位數(shù)字，并且小數(shù)點要占用一個字節(jié)；
3. 可用于存儲比bigint更大的整型數(shù)據(jù)；

11、避免使用ENUM類型

• 修改ENUM值需要使用ALTER語句；
• ENUM類型的ORDER BY操作效率低，需要額外操作；
• 禁止使用數(shù)值作為ENUM的枚舉值；

12、去重distinct過濾字段要少

1. 帶distinct的語句占用cpu時間高于不帶distinct的語句
2. 當(dāng)查詢很多字段時，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進(jìn)行比較，過濾掉重復(fù)數(shù)據(jù)
3. 然而這個比較、過濾的過程會占用系統(tǒng)資源，如cpu時間

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

1. 并不是說使用了is null或者 is not null就會不走索引了，這個跟mysql版本以及查詢成本都有關(guān)；
2. 如果mysql優(yōu)化器發(fā)現(xiàn)，走索引比不走索引成本還要高，就會放棄索引，這些條件 !=，<>，is null，is not null經(jīng)常被認(rèn)為讓索引失效；
3. 其實是因為一般情況下，查詢的成本高，優(yōu)化器自動放棄索引的；
4. 如果把null值，換成默認(rèn)值，很多時候讓走索引成為可能，同時，表達(dá)意思也相對清晰一點；

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

1. 使用!=和<>很可能會讓索引失效
2. 應(yīng)盡量避免在where子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描
3. 實現(xiàn)業(yè)務(wù)優(yōu)先，實在沒辦法，就只能使用，并不是不能使用

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

為什么？

• 如果inner join是等值連接，返回的行數(shù)比較少，所以性能相對會好一點；
• 使用了左連接，左邊表數(shù)據(jù)結(jié)果盡量小，條件盡量放到左邊處理，意味著返回的行數(shù)可能比較少；
• 這是mysql優(yōu)化原則，就是小表驅(qū)動大表，小的數(shù)據(jù)集驅(qū)動大的數(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、清空表時優(yōu)先使用truncate

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

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

truncate table刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果想保留標(biāo)識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 drop table語句。

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

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

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

（1）降低寫錯SQL的代價

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

（2）SQL效率很可能更高

SQL中加了limit 1，如果第一條就命中目標(biāo)return， 沒有limit的話，還會繼續(xù)執(zhí)行掃描表。

（3）避免長事務(wù)

delete執(zhí)行時,如果age加了索引，MySQL會將所有相關(guān)的行加寫鎖和間隙鎖，所有執(zhí)行相關(guān)行會被鎖住，如果刪除數(shù)量大，會直接影響相關(guān)業(yè)務(wù)無法使用。

（4）數(shù)據(jù)量大的話，容易把CPU打滿

如果你刪除數(shù)據(jù)量很大時，不加 limit限制一下記錄數(shù)，容易把cpu打滿，導(dǎo)致越刪越慢。

（5）鎖表

一次性刪除太多數(shù)據(jù)，可能造成鎖表，會有l(wèi)ock wait timeout exceed的錯誤，所以建議分批操作。

19、UNION操作符

UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運算，刪除重復(fù)的記錄再返回結(jié)果。實際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄，最常見的是過程表與歷史表UNION。如：

select?username,tel?from?user
union
select?departmentname?from?department

這個SQL在運行時先取出兩個表的結(jié)果，再用排序空間進(jìn)行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進(jìn)行排序。推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。

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

MySQL的IN中的常量全部存儲在一個數(shù)組中，這個數(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á)到一定量級，效果顯著，平時看不出來。

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

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

1. 關(guān)聯(lián)的表個數(shù)越多，編譯的時間和開銷也就越大
2. 每次關(guān)聯(lián)內(nèi)存中都生成一個臨時表
3. 應(yīng)該把連接表拆開成較小的幾個執(zhí)行，可讀性更高
4. 如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個糟糕的設(shè)計了
5. 阿里規(guī)范中，建議多表聯(lián)查三張表以下

（2）索引不宜太多，一般5個以內(nèi)

1. 索引并不是越多越好，雖其提高了查詢的效率，但卻會降低插入和更新的效率；
2. 索引可以理解為一個就是一張表，其可以存儲數(shù)據(jù)，其數(shù)據(jù)就要占空間；
3. 索引表的數(shù)據(jù)是排序的，排序也是要花時間的；
4. insert或update時有可能會重建索引，如果數(shù)據(jù)量巨大，重建將進(jìn)行記錄的重新排序，所以建索引需要慎重考慮，視具體情況來定；
5. 一個表的索引數(shù)最好不要超過5個，若太多需要考慮一些索引是否有存在的必要；

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

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

2萬字帶你精通MySQL索引

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

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

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

盡量把字段長度小的列放在聯(lián)合索引的最左側(cè)（因為字段長度越小，一頁能存儲的數(shù)據(jù)量越大，IO性能也就越好）。

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

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

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

覆蓋索引的好處：

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

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

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

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

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

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

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

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

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

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

大批量修改數(shù)據(jù)，一定是在一個事務(wù)中進(jìn)行的，這就會造成表中大批量數(shù)據(jù)進(jìn)行鎖定，從而導(dǎo)致大量的阻塞，阻塞會對MySQL的性能產(chǎn)生非常大的影響。

特別是長時間的阻塞會占滿所有數(shù)據(jù)庫的可用連接，這會使生產(chǎn)環(huán)境中的其他應(yīng)用無法連接到數(shù)據(jù)庫，因此一定要注意大批量寫操作要進(jìn)行分批。

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

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

29、組合索引

排序時應(yīng)按照組合索引中各列的順序進(jìn)行排序，即使索引中只有一個列是要排序的，否則排序性能會比較差。

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;?

實際上只是查詢出符合 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時會進(jìn)行優(yōu)化，底層進(jìn)行顛倒優(yōu)化

SELECT?*?FROM?employee?WHERE?salary=5000?AND?NAME='哪吒編程'

（6）理由

復(fù)合索引也稱為聯(lián)合索引，當(dāng)我們創(chuàng)建一個聯(lián)合索引的時候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個索引，這就是最左匹配原則。

聯(lián)合索引不滿足最左原則，索引一般會失效。

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

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

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

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

• 首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like ‘…%’，是會使用索引的；
• 左模糊like ‘%...’無法直接使用索引，但可以利用reverse + function index的形式，變化成 like ‘…%’；
• 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎。

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

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

select?*?from?user;
select?*?From?USER;

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

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

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

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

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

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

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

1. 避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗；
2. 在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
3. 如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert；
4. 如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除。先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。

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

（1）type

1. system：表僅有一行，基本用不到；
2. const：表最多一行數(shù)據(jù)配合，主鍵查詢時觸發(fā)較多；
3. eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；
4. ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；
5. range：只檢索給定范圍的行，使用一個索引來選擇行。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時，可以使用range；
6. index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小；
7. all：全表掃描；
8. 性能排名：system > const > eq_ref > ref > range > index > all。
9. 實際sql優(yōu)化中，最后達(dá)到ref或range級別。

（2）Extra常用關(guān)鍵字

• Using index：只從索引樹中獲取信息，而不需要回表查詢；
• Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
• Using temporary：mysql常建一個臨時表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

38、讀寫分離與分庫分表

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

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

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

select?id,name?from?user?limit?100000,?20

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

此時，可以通過取前一頁的最大ID，以此為起點，再進(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ù)庫的限制，過大會造成修改表結(jié)構(gòu)，備份，恢復(fù)都會有很大的問題。

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

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

• 分區(qū)表在物理上表現(xiàn)為多個文件，在邏輯上表現(xiàn)為一個表；
• 謹(jǐn)慎選擇分區(qū)鍵，跨分區(qū)查詢效率可能更低；
• 建議采用物理分表的方式管理大數(shù)據(jù)。

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

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

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

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

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

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

1. 預(yù)留字段的命名很難做到見名識義；
2. 預(yù)留字段無法確認(rèn)存儲的數(shù)據(jù)類型，所以無法選擇合適的類型；
3. 對預(yù)留字段類型的修改，會對表進(jìn)行鎖定；

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

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

通常存儲于文件服務(wù)器，數(shù)據(jù)庫只存儲文件地址信息。

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

Mysql內(nèi)存臨時表不支持TEXT、BLOB這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時，就不能使用內(nèi)存臨時表，必須使用磁盤臨時表進(jìn)行。而且對于這種數(shù)據(jù)，Mysql還是要進(jìn)行二次查詢，會使sql性能變得很差，但是不是說一定不能使用這樣的數(shù)據(jù)類型。

如果一定要使用，建議把BLOB或是TEXT列分離到單獨的擴(kuò)展表中，查詢時一定不要使用select * 而只需要取出必要的列，不需要TEXT列的數(shù)據(jù)時不要對該列進(jìn)行查詢。

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

因為MySQL對索引字段長度是有限制的，所以TEXT類型只能使用前綴索引，并且TEXT列上是不能有默認(rèn)值的。

MySql基礎(chǔ)知識總結(jié)（SQL優(yōu)化篇）

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

（1）當(dāng)只需要一條數(shù)據(jù)的時候，使用limit 1

limit 1可以避免全表掃描，找到對應(yīng)結(jié)果就不會再繼續(xù)掃描了。

（2）如果排序字段沒有用到索引，就盡量少排序

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

使用comment從句添加表和列的備注，從一開始就進(jìn)行數(shù)據(jù)字典的維護(hù)。

（4）SQL書寫格式，關(guān)鍵字大小保持一致，使用縮進(jìn)。

（5）修改或刪除重要數(shù)據(jù)前，要先備份。

（6）很多時候用 exists 代替 in 是一個好的選擇

（7）where后面的字段，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。

（8）盡量把所有列定義為NOT NULL

NOT NULL列更節(jié)省空間，NULL列需要一個額外字節(jié)作為判斷是否為 NULL的標(biāo)志位。NULL列需要注意空指針問題，NULL列在計算和比較的時候，需要注意空指針問題。

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

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