1. 除了不要 SELECT * 肌毅，使用數(shù)據(jù)庫(kù)還應(yīng)知道的11個(gè)技巧疚颊！

• 參考鏈接

技巧

1. 比較運(yùn)算符能使用 = 就不用 <>乡翅，= 增加了索引的使用幾率
2. 明知只有一條查詢結(jié)構(gòu)時(shí)站宗，請(qǐng)使用 LIMIT 1滋迈， LIMIT 1 可以避免全表掃描霎奢，找到對(duì)應(yīng)結(jié)果就不會(huì)再繼續(xù)掃描了
3. 為列選擇合適的數(shù)據(jù)類型，比如能用 TINYINT 就不用 SMALLINT饼灿，能用 SMALLINT 就不用 INT幕侠，這樣會(huì)有更小的磁盤和內(nèi)存消耗
4. 將大的 DELETE, UPDATE, INSERT 查詢拆分成多個(gè)小查詢，以達(dá)到更好的性能和更好的數(shù)據(jù)控制
5. 使用 UNION ALL 替代 UNION赔退，如果結(jié)果集允許重復(fù)的話橙依，因?yàn)?UNION ALL 不去重，其效率高于 UNION
6. 為獲得相同結(jié)果集的多次執(zhí)行硕旗，請(qǐng)確保 SQL 語(yǔ)句完全一致窗骑，這樣做的目的是為了充分利用查詢緩存（注意列的查詢順序也不能換，否則緩存不會(huì)命中）
7. 盡量避免使用 SELECT *漆枚，盡量只返回需要的數(shù)據(jù)
8. WHERE 子句里面的列盡量被索引创译，因?yàn)椴樵儣l件里的列若沒有索引，則將導(dǎo)致全表掃描
9. JOIN 子句里面的列盡量被索引墙基，同理若 JOIN 的列若沒有索引软族，也將導(dǎo)致全表掃描
10. ORDER BY 的列盡量被索引，這樣會(huì)有更好的性能
11. 使用 LIMIT 實(shí)現(xiàn)分頁(yè)邏輯残制，分頁(yè)不僅提高了性能立砸，同時(shí)減少了不必要的數(shù)據(jù)庫(kù)和應(yīng)用之間的網(wǎng)絡(luò)傳輸
12. 使用 explain 關(guān)鍵字去查看執(zhí)行計(jì)劃，explain 可以檢查索引使用情況以及掃描的表

2. 面試官問你 MySQL 的優(yōu)化初茶，看這篇文章就夠了

• 參考鏈接

2.1 使用 EXPLAIN

• explain 詳細(xì)可參考 mysql explain 筆記
• type : 連接類型颗祝，一個(gè)好的 sql 語(yǔ)句至少要達(dá)到 range 級(jí)別，杜絕出現(xiàn) all 級(jí)別
• key : 使用到的索引名恼布，如果沒有選擇索引螺戳，值是 NULL，可以采取強(qiáng)制索引方式
• key_len : 索引長(zhǎng)度
• rows : 掃描行數(shù)折汞，該值是個(gè)預(yù)估值
• extra : 詳細(xì)說明倔幼，注意常見的不太友好的值有：Using filesort, Using temporary

2.2 SQL 語(yǔ)句中 IN 包含的值不應(yīng)過多

• MySQL 對(duì) IN 做了相應(yīng)的優(yōu)化，即將 IN 中的常量全部存儲(chǔ)在一個(gè)數(shù)組里面爽待，而且這個(gè)數(shù)據(jù)是排好序的损同，但如果數(shù)值較多翩腐，則產(chǎn)生的消耗也是比較大的
• 再比如， select id from table_name where num in(1, 2, 3) 這樣連續(xù)的數(shù)值揖庄，能用 between 就不要用 in 了
• 或者使用連接來替換栗菜，子查詢有時(shí)可能導(dǎo)致性能降低

2.3 SELECT 語(yǔ)句務(wù)必指明字段名稱

• 使用 SELECT * 會(huì)增加很多不必要的消耗（cpu, io, 內(nèi)存, 網(wǎng)絡(luò)帶寬），增加了使用覆蓋索引和全表掃描的可能性的可能性
• 而且若表結(jié)構(gòu)發(fā)生變化蹄梢，前端也需要進(jìn)行更新，因此不能使用 SELECT * 而是在后面加上字段名

2.4 當(dāng)只需要一條數(shù)據(jù)時(shí)富俄，使用 LIMIT 1

• 這是為了使 EXPLAIN 中 type 達(dá)到 const 類型禁炒，即找到一條記錄后就不再繼續(xù)往下掃描

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

• 在非索引字段上的排序會(huì)降低 sql 語(yǔ)句效率

2.6 如果查詢條件中其他字段沒有索引霍比，則盡量少使用 or

• or 兩邊的字段中幕袱，如果有一個(gè)不是索引字段，將導(dǎo)致該查詢不走索引悠瞬，即使另一個(gè)查詢條件是索引字段
• 該種情況下们豌，可以考慮使用 union all 和 union 替代 or 進(jìn)行查詢
• 但也許根據(jù)具體情況進(jìn)行判斷，使用 union all 的話一個(gè)查詢走索引浅妆，另一個(gè)查詢走全表掃描望迎，而統(tǒng)一查詢也會(huì)走一次全表掃描

2.7 盡量使用 UNION ALL 替代 UNION

• union 和 union all 的差異主要是前者需要將結(jié)果集合并在進(jìn)行唯一性過濾，這就會(huì)涉及到排序凌外，增加大量的 CPU 運(yùn)算辩尊，加大資源消耗及延遲
• 當(dāng)然，使用 union all 的前提是兩個(gè)結(jié)果集沒有重復(fù)數(shù)據(jù)康辑，否則只能使用 union 剔除重復(fù)

2.8 不使用 ORDER BY RAND()

2.9 區(qū)分 in, exists, not in, not exists

• select * from tb_A where id in (select id from tb_B)
• 用 exists 改寫 : select * from tb_A where exists (select * from tb_B where tb_B.id = tb_a.id)
• in 和 exists 主要的卻別是驅(qū)動(dòng)順序的不同摄欲，這是性能優(yōu)化的關(guān)鍵，如果是 exists疮薇，那么那么外層表是驅(qū)動(dòng)表胸墙，先被訪問，如果是 in 那么先執(zhí)行子查詢
• 所以 in 適合外表大而內(nèi)表小的情況按咒，而 exists 適合外表小而內(nèi)表大的情況

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

• 使用 limit 關(guān)鍵字進(jìn)行分頁(yè)迟隅，第一個(gè)參數(shù)為偏移量，第二個(gè)參數(shù)為數(shù)據(jù)大小
• 當(dāng)數(shù)據(jù)量很大時(shí)胖齐，使用 limit 進(jìn)行分頁(yè)查詢效率將變得低下玻淑，此時(shí)可以取前一頁(yè) id 的最大值，然后用這個(gè) id 作為查詢條件進(jìn)行過濾呀伙，然后返回對(duì)應(yīng)大小的數(shù)據(jù)
• select id, name from table_name limit 866613, 20
• select id, name from table_name where id > ... limit 20
• 進(jìn)行該轉(zhuǎn)換的前提是根據(jù) id 順序查詢

2.11 分段查詢

• 在一些用戶選擇界面中补履，用戶選擇的時(shí)間范圍或者查詢范圍范圍過大，造成查詢緩慢
• 主要的原因是掃描行數(shù)過多剿另，這個(gè)時(shí)候可以通過程序箫锤，分段進(jìn)行查詢贬蛙，循環(huán)遍歷各個(gè)分段，然后將結(jié)果合并處理進(jìn)行展示

2.12 避免在愛 where 字句中對(duì)字段進(jìn)行 null 值判斷

• 對(duì)于 null 的判斷會(huì)導(dǎo)致引擎放棄使用索引進(jìn)而導(dǎo)致全表掃描
• 一般建表時(shí)建議禁止 null 并提供一個(gè)默認(rèn)值

2.13 不建議使用 % 前綴模糊查詢

• 例如 LIKE '%name' 或者 Like '%name%'谚攒，這種查詢會(huì)導(dǎo)致索引失效進(jìn)而導(dǎo)致全表掃描
• 對(duì)于需要模糊查詢的字段阳准，建議使用全文索引，全文索引相關(guān)內(nèi)容參考筆記 : mysql 全文索引

2.14 避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作

• 對(duì)字段進(jìn)行算數(shù)運(yùn)算會(huì)導(dǎo)致引擎放棄使用索引
• 例如 : select * from user where age * 2 = 36; 建議改成 select * from user from user where age = 36/2;

2.15 避免隱式類型轉(zhuǎn)換

• where 子句中出現(xiàn) column 字段的類型和傳入的參數(shù)類型不一致時(shí)會(huì)發(fā)生類型轉(zhuǎn)換馏臭，建議先確定 where 中的參數(shù)類型

2.16 對(duì)于聯(lián)合索引來說野蝇，要遵守最左前綴法則

• 例如，假設(shè)一聯(lián)合索引包含 id, name, school 三個(gè)字段括儒，可以直接使用 id 字段绕沈，也可以使用 id, name 這樣的順序，但 name, school 都無(wú)法使用這個(gè)索引帮寻，因此在創(chuàng)建聯(lián)合索引時(shí)要注意索引字段的順序

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

• 有的時(shí)候 MySQL 優(yōu)化器采取它任何合適的索引來檢索 sql 語(yǔ)句乍狐，但是可能它所采用的索引并不是我們想要的，這時(shí)就可以采用 force index 來強(qiáng)制優(yōu)化器使用我們指定的索引

2.18 注意范圍查詢語(yǔ)句

• 對(duì)于聯(lián)合索引來說固逗，如果存在范圍查詢浅蚪，比如 between, >, < 等條件時(shí)，會(huì)造成后面的索引字段失效烫罩，因此對(duì)于聯(lián)合索引惜傲，除非是最后一個(gè)查詢條件，否則前面的要采用等值查詢

2.19 關(guān)于 join 優(yōu)化

• A left join B : A 為驅(qū)動(dòng)表
• A inner join B : MySQL 會(huì)自動(dòng)找出那個(gè)數(shù)據(jù)少的表用作驅(qū)動(dòng)表
• A right join B : B 為驅(qū)動(dòng)表
• 注意 MySQL 沒有 full join
• 盡量使用 inner join嗡髓，避免使用 left join : 參數(shù)關(guān)聯(lián)查詢的表至少有兩張操漠，一般都有大小之分，如果使用 inner join饿这，在沒有其他過濾情況下浊伙，MySQL 會(huì)自動(dòng)選擇小表作為驅(qū)動(dòng)表，但如果是 left join 則選擇左邊的作為驅(qū)動(dòng)表长捧，如果左表數(shù)據(jù)較大則效率會(huì)更低
• 注意連接時(shí)嚣鄙，連接的字段盡量采用具有索引的字段
• 盡量使用小表去驅(qū)動(dòng)大表，連接時(shí)存儲(chǔ)引擎采用的策略是用驅(qū)動(dòng)表中的每一行和被驅(qū)動(dòng)表進(jìn)行連接串结，若減小驅(qū)動(dòng)表哑子，則可以減少嵌套循環(huán)的循環(huán)次數(shù)，以減少 IO 總量以及 CPU 運(yùn)算次數(shù)
• 巧用straight_join : inner join 是由 mysql 選擇驅(qū)動(dòng)表肌割，但有些情況需要自行選擇驅(qū)動(dòng)表卧蜓，比如有 order by, group by 子句時(shí)，此時(shí)可以采用 straight_join 來強(qiáng)制連接順序把敞，其左邊的即為驅(qū)動(dòng)表弥奸，右邊的為被驅(qū)動(dòng)表
• 但要注意，使用 straight_join 的前提條件是該查詢時(shí)內(nèi)連接奋早，也就是 inner join盛霎，其他連接不推薦使用 straight_join赠橙，否則可能造成查詢結(jié)果不準(zhǔn)確

3. MySQL 推薦使用規(guī)范

• 參考鏈接

3.1 基礎(chǔ)規(guī)范

1. 使用 InnoDB 存儲(chǔ)引擎 : 支持事務(wù)、行鎖愤炸、并發(fā)性能更好期揪、CPU 及內(nèi)存緩存頁(yè)優(yōu)化使得資源利用率更高
2. 推薦使用 utf8mb4 字符集 : 無(wú)需轉(zhuǎn)碼，無(wú)亂碼風(fēng)險(xiǎn)规个，支持 emoji 表情以及部分不常見漢字
3. 表凤薛、字段必須加注釋 : 方便他人理解字段意思
4. 不在數(shù)據(jù)庫(kù)做計(jì)算 :
   • 禁止使用存儲(chǔ)過程、視圖诞仓、觸發(fā)器枉侧、Event
   • 在并發(fā)量大的情況下，這些功能可能將數(shù)據(jù)庫(kù)拖垮狂芋，業(yè)務(wù)邏輯放到服務(wù)層才具備更好的擴(kuò)展性，能夠輕易實(shí)現(xiàn)“增機(jī)器就加性能”
5. 禁止文件存儲(chǔ) : 文件存儲(chǔ)在文件系統(tǒng)憨栽，數(shù)據(jù)庫(kù)里存 URI
6. 控制單表數(shù)據(jù)量 : 單表的記錄數(shù)控制在千萬(wàn)級(jí)

3.2 命名規(guī)范

1. 庫(kù)名帜矾、表名、字段名 : 小寫屑柔，下劃線風(fēng)格屡萤，非唯一索引名 idx_xxx，唯一索引名 unq_xxx
2. 表必須有主鍵掸宛，例如自增主鍵
   • 主鍵遞增死陆，數(shù)據(jù)庫(kù)行寫入可以提高性能（高并發(fā)下好像不適用）
   • 主鍵要選擇較短的數(shù)據(jù)類型，InnoDB 引擎普通索引都會(huì)保存主鍵的值唧瘾，較短的數(shù)據(jù)類型可以有效地減少索引的磁盤空間措译，提高索引的緩存效率
   • 使用主鍵來保證實(shí)體的完整性、唯一性
3. 不要使用外鍵饰序，如果有外鍵领虹，用應(yīng)用程序控制
   • 外鍵會(huì)導(dǎo)致表與表之間耦合，update 與 delete 操作都會(huì)涉及相關(guān)聯(lián)的表求豫，十分影響 sql 的性能塌衰，甚至造成死鎖
   • 高并發(fā)情況下外鍵容易造成數(shù)據(jù)庫(kù)性能下降，大數(shù)據(jù)高并發(fā)業(yè)務(wù)場(chǎng)景數(shù)據(jù)庫(kù)使用以性能優(yōu)先

3.3 字段設(shè)計(jì)規(guī)范

1. 把字段定義為 not null 并提供默認(rèn)值
   • null 的列使索引蝠嘉、索引統(tǒng)計(jì)最疆、值判斷都更加復(fù)雜，對(duì) MySQL 來說更難優(yōu)化
   • null 這種類型 MySQL 內(nèi)部需要進(jìn)行特殊處理蚤告，增加數(shù)據(jù)庫(kù)處理記錄的復(fù)雜性努酸；同等條件下，表中有較多 null 字段時(shí)罩缴，數(shù)據(jù)庫(kù)的處理性能會(huì)降低很多
   • null 需要更多的存儲(chǔ)空間蚊逢，無(wú)論是表层扶、索引中、每行中的 null 的列都需要額外的空間來標(biāo)識(shí)
   • 對(duì) null 的判斷烙荷，只能采用 is null, is not null 而不能采用 =, in, <, <>, !=, not in 這些操作符镜会，例如 : 對(duì)于 where name != 'zhangsan'，即使存在 name 為 null 值的記錄终抽，查詢的結(jié)果也不會(huì)包含 name 為 null 的值的記錄
2. 不要使用 text, blob 類型
   • 會(huì)浪費(fèi)更多的磁盤和內(nèi)存空間
   • 非必要的大量的大字段查詢會(huì)淘汰掉熱數(shù)據(jù)戳表，導(dǎo)致內(nèi)存命中率急劇降低，從而影響數(shù)據(jù)庫(kù)性能
   • 如果必須要使用則獨(dú)立出來一張表昼伴，用主鍵來對(duì)應(yīng)匾旭，避免影響其他字段的索引效率
3. 不要使用小數(shù)存儲(chǔ)貨幣 : 建議使用整數(shù)，小數(shù)容易導(dǎo)致錢對(duì)不上
4. 必須使用 varchar 存儲(chǔ)手機(jī)號(hào) : 手機(jī)號(hào)不會(huì)去做數(shù)學(xué)運(yùn)算
5. 為提高效率可以犧牲范式設(shè)計(jì)圃郊，冗余數(shù)據(jù)
   • 不能是頻繁修改的字段
   • 不能是 varchar 超長(zhǎng)字段价涝，更不能是 text 字段

3.4 索引設(shè)計(jì)規(guī)范

1. 禁止在更新十分頻繁、區(qū)分度不高的屬性上建立索引
   • 數(shù)據(jù)更新會(huì)變更索引的 B+ 數(shù)持舆，更新頻繁的字段建立索引會(huì)大大降低數(shù)據(jù)庫(kù)的性能
   • 性別這種區(qū)分度不大的屬性色瘩，建立索引沒有什么意義，還會(huì)浪費(fèi)空間
2. 建立聯(lián)合索引時(shí)逸寓，必須把區(qū)分度高的放在最左邊
   • 如果 where a = ? and b = ?居兆，a 的列幾乎接近于唯一值，則 a 單獨(dú)建 idx_a 索引即可
3. 對(duì)于聯(lián)合索引竹伸，禁止左范圍或者全范圍
   • 索引文件具有 B-Tree 的最左前綴匹配特性泥栖，如果左邊的值未確定，則無(wú)法使用索引
   • 即最左的列是一個(gè)范圍或不確定值勋篓，這樣會(huì)導(dǎo)致不走索引
   • 如果有需要可以考慮使用搜索組件來解決

3.5 SQL 使用規(guī)范

1. 禁止使用 select *吧享，只獲取必要字段，顯式說明列屬性
   • 浪費(fèi) cpu, io, 內(nèi)存, 帶寬
   • 不能有效地利用覆蓋索引（覆蓋索引為只差索引列生巡，對(duì)索引進(jìn)行全覆蓋查詢耙蔑，如果是 * 則直接導(dǎo)致全表掃描）
   • 使用 select * 容易在增加或者刪除字段后出現(xiàn)程序 bug，不具有擴(kuò)展性
2. 使用 insert into t_xxx(col1, col2) values(xxx) 時(shí)必須顯式指定插入的列
   • 否則容易在增加或者刪除字段后出現(xiàn)程序 bug
3. where 中的查詢條件保證為同類型比較孤荣，否則可能導(dǎo)致全表掃描
   • 例如 phone 為 varchar 類型甸陌，select name from t_user where phone = 13333 會(huì)導(dǎo)致全表掃描，需要改為 phone = '13333'
4. 禁止在 where 條件的屬性列上使用函數(shù)或者計(jì)算
   • 在 where 條件的屬性列上使用函數(shù)或者計(jì)算會(huì)導(dǎo)致全表掃描盐股，但屬性值的計(jì)算是允許的
   • 例如 age 列存在索引钱豁，但 where age * 2 = 28 將導(dǎo)致全表掃描，而 where age = 28/2 則可以走索引
5. 禁止負(fù)向查詢疯汁，以及 % 開頭的模糊查詢
   • 負(fù)向查詢 : not, !=, <>, !<, !>, not in, no like 等牲尺，都會(huì)導(dǎo)致全表掃描
   • % 開頭的模糊查詢也會(huì)導(dǎo)致全表掃描
6. 不要大表使用 join 查詢，禁止大表使用子查詢
   • join 和子查詢都會(huì)產(chǎn)生臨時(shí)表，從而導(dǎo)致消耗較多的內(nèi)存與 CPU谤碳，極大影響數(shù)據(jù)庫(kù)性能
   • in 是內(nèi)表做驅(qū)動(dòng)表溃卡，適合外表大而內(nèi)表小的情況，而 exists 是外表做驅(qū)動(dòng)表蜒简，適合外表小而內(nèi)標(biāo)大的情況（同連接一樣瘸羡，更小的驅(qū)動(dòng)表會(huì)有更高的性能）
7. or 改寫為 in() 或者 union all
   • or 兩邊如果有一個(gè)非索引，則必定不走索引而導(dǎo)致全表掃描
   • 在低版本中搓茬，一個(gè)表查詢一次只能使用一個(gè)索引犹赖，而如果兩端各自存在索引，無(wú)法使用多個(gè)索引分別進(jìn)行條件掃描卷仑，從而可能導(dǎo)致全表掃描
   • 高版本引入了 index merge 優(yōu)化技術(shù)峻村，對(duì)于同一個(gè)表可以多個(gè)索引分別進(jìn)行條件掃描，然后再合并（intersect, union, sort_union）锡凝，好像可以用 or 了粘昨，MySQL 會(huì)自動(dòng)優(yōu)化
8. 簡(jiǎn)單的事務(wù) : 事務(wù)就像程序中的鎖一樣，粒度盡可能要小
9. 不要一次性更新大量數(shù)據(jù) : 數(shù)據(jù)更新會(huì)對(duì)行或者表加鎖窜锯，應(yīng)該分為多次更新