MySQL優(yōu)化（索引與查詢優(yōu)化）

1. 如何定位及優(yōu)化SQL語句的性能問題伏钠？

對于低性能的SQL語句的定位管宵，最重要也是最有效的方法就是使用執(zhí)行計劃敦间，MySQL提供了explain命令來查看語句的執(zhí)行計劃。 我們知道梦皮，不管是哪種數(shù)據(jù)庫，或者是哪種數(shù)據(jù)庫引擎桃焕，在對一條SQL語句進行執(zhí)行的過程中都會做很多相關的優(yōu)化剑肯，對于查詢語句，最重要的優(yōu)化方式就是使用索引覆旭。

2. 大表數(shù)據(jù)查詢七兜，怎么優(yōu)化

• 優(yōu)化shema丸凭、sql語句+索引；
• 第二加緩存腕铸，memcached, redis惜犀；
• 主從復制，讀寫分離狠裹；
• 垂直拆分虽界，根據(jù)你模塊的耦合度，將一個大的系統(tǒng)分為多個小的系統(tǒng)涛菠，也就是分布式系統(tǒng)莉御；
• 水平切分撇吞，針對數(shù)據(jù)量大的表，這一步最麻煩礁叔，最能考驗技術水平牍颈，要選擇一個合理的sharding key, 為了有好的查詢效率，表結構也要改動琅关，做一定的冗余煮岁，應用也要改，sql中盡量帶sharding key涣易，將數(shù)據(jù)定位到限定的表上去查画机，而不是掃描全部的表；

3. 超大分頁怎么處理?

數(shù)據(jù)庫層面,這也是我們主要集中關注的(雖然收效沒那么大),類似于select * from table where age > 20 limit 1000000,10 這種查詢其實也是有可以優(yōu)化的余地的. 這條語句需要 load1000000 數(shù)據(jù)然后基本上全部丟棄,只取 10 條當然比較慢. 當時我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也 load 了一百萬的數(shù)據(jù),但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快都毒。

解決超大分頁,其實主要是靠緩存,可預測性的提前查到內容,緩存至redis等k-V數(shù)據(jù)庫中,直接返回即可.

在阿里巴巴《Java開發(fā)手冊》中,對超大分頁的解決辦法是類似于上面提到的第一種.

【推薦】利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景色罚。

說明：MySQL并不是跳過offset行，而是取offset+N行账劲，然后返回放棄前offset行戳护，返回N行，那當offset特別大的時候瀑焦，效率就非常的低下腌且，要么控制返回的總頁數(shù)，要么對超過特定閾值的頁數(shù)進行SQL改寫榛瓮。

正例：先快速定位需要獲取的id段铺董，然后再關聯(lián)：

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

4. 統(tǒng)計過慢查詢嗎？對慢查詢都怎么優(yōu)化過禀晓？

在業(yè)務系統(tǒng)中精续，除了使用主鍵進行的查詢，其他的我都會在測試庫上測試其耗時粹懒，慢查詢的統(tǒng)計主要由運維在做重付，會定期將業(yè)務中的慢查詢反饋給我們。

定位執(zhí)行慢的 SQL：慢查詢日志

1. 開啟慢查詢日志參數(shù)

2. 開啟slow_query_log set global slow_query_log='ON';

3. 修改long_query_time閾值

4. 查看慢查詢數(shù)目

查詢當前系統(tǒng)中有多少條慢查詢記錄
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

3. 案例演示

4. 測試及分析

5. 慢查詢日志分析工具：mysqldumpslow

6. 關閉慢查詢日志

7. 方式1：永久性方式

8. [圖片上傳失敗...(image-a50598-1655208719518)]

9. 或者凫乖，把slow_query_log一項注釋掉 或 刪除 [圖片上傳失敗...(image-43820d-1655208719518)]

10. 重啟MySQL服務确垫，執(zhí)行如下語句查詢慢日志功能。

SHOW VARIABLES LIKE '%slow%'; #查詢慢查詢日志所在目錄

SHOW VARIABLES LIKE '%long_query_time%'; #查詢超時時長

2. 方式2：臨時性方式：使用SET語句來設置帽芽。

3. （1）停止MySQL慢查詢日志功能删掀，具體SQL語句如下

4. （2）重啟MySQL服務，使用SHOW語句查詢慢查詢日志功能信息导街，具體SQL語句如下

5. SHOW VARIABLES LIKE '%slow%';

6. 以及

7. SHOW VARIABLES LIKE '%long_query_time%';

8. 刪除慢查詢日志

慢查詢的優(yōu)化首先要搞明白慢的原因是什么披泪？ 是查詢條件沒有命中索引？是load了不需要的數(shù)據(jù)列搬瑰？還是數(shù)據(jù)量太大付呕？

所以優(yōu)化也是針對這三個方向來的计福，

• 首先分析語句，看看是否load了額外的數(shù)據(jù)徽职，可能是查詢了多余的行并且拋棄掉了，可能是加載了許多結果中并不需要的列佩厚，對語句進行分析以及重寫姆钉。
• 分析語句的執(zhí)行計劃，然后獲得其使用索引的情況抄瓦，之后修改語句或者修改索引潮瓶，使得語句可以盡可能的命中索引。
• 如果對語句的優(yōu)化已經無法進行钙姊，可以考慮表中的數(shù)據(jù)量是否太大毯辅，如果是的話可以進行橫向或者縱向的分表。

5. 如何優(yōu)化查詢過程中的數(shù)據(jù)訪問

• 訪問數(shù)據(jù)太多導致查詢性能下降
• 確定應用程序是否在檢索大量超過需要的數(shù)據(jù)煞额，可能是太多行或列
• 確認MySQL服務器是否在分析大量不必要的數(shù)據(jù)行
• 查詢不需要的數(shù)據(jù)思恐。解決辦法：使用limit解決
• 多表關聯(lián)返回全部列。解決辦法：指定列名
• 總是返回全部列膊毁。解決辦法：避免使用SELECT *
• 重復查詢相同的數(shù)據(jù)胀莹。解決辦法：可以緩存數(shù)據(jù)，下次直接讀取緩存
• 是否在掃描額外的記錄婚温。解決辦法：使用explain進行分析描焰，如果發(fā)現(xiàn)查詢需要掃描大量的數(shù)據(jù)，但只返回少數(shù)的行栅螟，可以通過如下技巧去優(yōu)化：使用索引覆蓋掃描荆秦，把所有的列都放到索引中，這樣存儲引擎不需要回表獲取對應行就可以返回結果力图。
• 改變數(shù)據(jù)庫和表的結構步绸，修改數(shù)據(jù)表范式
• 重寫SQL語句，讓優(yōu)化器可以以更優(yōu)的方式執(zhí)行查詢搪哪。

6. 如何優(yōu)化關聯(lián)查詢

• 確定ON或者USING子句中是否有索引靡努。

• 確保GROUP BY和ORDER BY只有一個表中的列，這樣MySQL才有可能使用索引晓折。

• 保證被驅動表的JOIN字段已經創(chuàng)建了索引

• 需要JOIN 的字段惑朦，數(shù)據(jù)類型保持絕對一致。

• LEFT JOIN 時漓概，選擇小表作為驅動表漾月， 大表作為被驅動表 。減少外層循環(huán)的次數(shù)胃珍。

• INNER JOIN 時梁肿，MySQL會自動將 小結果集的表選為驅動表 蜓陌。選擇相信MySQL優(yōu)化策略。

• 能夠直接多表關聯(lián)的盡量直接關聯(lián)吩蔑，不用子查詢钮热。(減少查詢的趟數(shù))

• 不建議使用子查詢，建議將子查詢SQL拆開結合程序多次查詢烛芬，或使用 JOIN 來代替子查詢隧期。

• 衍生表建不了索引

子查詢優(yōu)化

MySQL從4.1版本開始支持子查詢，使用子查詢可以進行SELECT語句的嵌套查詢赘娄，即一個SELECT查詢的結

果作為另一個SELECT語句的條件仆潮。 子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作 。

子查詢是 MySQL 的一項重要的功能遣臼，可以幫助我們通過一個 SQL 語句實現(xiàn)比較復雜的查詢性置。但是，子

查詢的執(zhí)行效率不高揍堰。原因：

① 執(zhí)行子查詢時鹏浅，MySQL需要為內層查詢語句的查詢結果 建立一個臨時表 ，然后外層查詢語句從臨時表

中查詢記錄个榕。查詢完畢后篡石，再 撤銷這些臨時表 。這樣會消耗過多的CPU和IO資源西采，產生大量的慢查詢凰萨。

② 子查詢的結果集存儲的臨時表，不論是內存臨時表還是磁盤臨時表都 不會存在索引 械馆，所以查詢性能會

受到一定的影響胖眷。

③ 對于返回結果集比較大的子查詢，其對查詢性能的影響也就越大霹崎。

在MySQL中珊搀，可以使用連接（JOIN）查詢來替代子查詢。連接查詢 不需要建立臨時表 尾菇，其 速度比子查詢

要快 境析，如果查詢中使用索引的話，性能就會更好派诬。

結論：盡量不要使用NOT IN 或者 NOT EXISTS劳淆，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序優(yōu)化

問題：在 WHERE 條件字段上加索引，但是為什么在 ORDER BY 字段上還要加索引呢默赂？

優(yōu)化建議：

1. SQL 中沛鸵，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表掃
   描 缆八，在 ORDER BY 子句 避免使用 FileSort 排序 曲掰。當然疾捍，某些情況下全表掃描，或者 FileSort 排
   序不一定比索引慢栏妖。但總的來說乱豆，我們還是要避免，以提高查詢效率底哥。
2. 盡量使用 Index 完成 ORDER BY 排序咙鞍。如果 WHERE 和 ORDER BY 后面是相同的列就使用單索引列； 如果不同就使用聯(lián)合索引趾徽。
3. 無法使用 Index 時，需要對 FileSort 方式進行調優(yōu)翰守。

結論：

1. 兩個索引同時存在孵奶，mysql自動選擇最優(yōu)的方案。（對于這個例子蜡峰，mysql選擇 idx_age_stuno_name）了袁。但是， 隨著數(shù)據(jù)量的變化湿颅，選擇的索引也會隨之變化的 载绿。
2. 當【范圍條件】和【group by 或者 order by】的字段出現(xiàn)二選一時，優(yōu)先觀察條件字段的過
   濾數(shù)量油航，如果過濾的數(shù)據(jù)足夠多崭庸，而需要排序的數(shù)據(jù)并不多時，優(yōu)先把索引放在范圍字段
   上谊囚。反之怕享，亦然。

GROUP BY優(yōu)化

• group by 使用索引的原則幾乎跟order by一致 镰踏，group by 即使沒有過濾條件用到索引函筋，也可以直接
• 使用索引。
• group by 先排序再分組奠伪，遵照索引建的最佳左前綴法則
• 當無法使用索引列跌帐，增大 max_length_for_sort_data 和 sort_buffer_size 參數(shù)的設置
• where效率高于having，能寫在where限定的條件就不要寫在having中了
• 減少使用order by绊率，和業(yè)務溝通能不排序就不排序谨敛，或將排序放到程序端去做。Order by即舌、group
• by佣盒、distinct這些語句較為耗費CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的顽聂。
• 包含了order by肥惭、group by盯仪、distinct這些查詢的語句，where條件過濾出來的結果集請保持在1000行
• 以內蜜葱，否則SQL會很慢全景。

優(yōu)化分頁查詢

優(yōu)化思路一

在索引上完成排序分頁操作，最后根據(jù)主鍵關聯(lián)回原表查詢所需要的其他列內容牵囤。

EXPLAIN SELECT * FROM student t,
(SELECT id FROM student ORDER BY id LIMIT 2000000,10)a
WHERE t.id = a.id;

優(yōu)化思路二

該方案適用于主鍵自增的表爸黄，可以把Limit 查詢轉換成某個位置的查詢 。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

優(yōu)先考慮覆蓋索引

什么是覆蓋索引揭鳞？

理解方式一：索引是高效找到行的一個方法炕贵，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它

不必讀取整個行野崇。畢竟索引葉子節(jié)點存儲了它們索引的數(shù)據(jù)称开；當能通過讀取索引就可以得到想要的數(shù)

據(jù)，那就不需要讀取行了乓梨。一個索引包含了滿足查詢結果的數(shù)據(jù)就叫做覆蓋索引鳖轰。

理解方式二：非聚簇復合索引的一種形式，它包括在查詢里的SELECT扶镀、JOIN和WHERE子句用到的所有列

（即建索引的字段正好是覆蓋查詢條件中所涉及的字段）蕴侣。

簡單說就是， 索引列+主鍵 包含 SELECT 到 FROM之間查詢的列 臭觉。

覆蓋索引的利弊 好處：

1. 避免Innodb表進行索引的二次查詢（回表）
2. 可以把隨機IO變成順序IO加快查詢效率
   弊端： 索引字段的維護 總是有代價的昆雀。因此，在建立冗余索引來支持覆蓋索引時就需要權衡考慮了胧谈。這是業(yè)務
   DBA忆肾，或者稱為業(yè)務數(shù)據(jù)架構師的工作。

(前綴索引)如何給字符串添加索引

MySQL是支持前綴索引的菱肖。默認地客冈，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會包含整個字

符串稳强。

mysql> alter table teacher add index index1(email);
#或
mysql> alter table teacher add index index2(email(6));

這兩種不同的定義在數(shù)據(jù)結構和存儲上有什么區(qū)別呢场仲？下圖就是這兩個索引的示意圖。

如果使用的是index1（即email整個字符串的索引結構）退疫，執(zhí)行順序是這樣的：

1. 從index1索引樹找到滿足索引值是’ zhangssxyz@xxx.com ’的這條記錄渠缕，取得ID2的值；
2. 到主鍵上查到主鍵值是ID2的行褒繁，判斷email的值是正確的亦鳞，將這行記錄加入結果集；
3. 取index1索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經不滿足email=' zhangssxyz@xxx.com ’的
   條件了燕差，循環(huán)結束遭笋。
   這個過程中，只需要回主鍵索引取一次數(shù)據(jù)徒探，所以系統(tǒng)認為只掃描了一行瓦呼。

如果使用的是index2（即email(6)索引結構），執(zhí)行順序是這樣的：

4. 從index2索引樹找到滿足索引值是’zhangs’的記錄测暗，找到的第一個是ID1央串；
5. 到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’ zhangssxyz@xxx.com ’碗啄，這行記錄丟棄质和；
6. 取index2上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’稚字，取出ID2侦另，再到ID索引上取整行然 后判斷，這次值對了尉共，將這行記錄加入結果集；
7. 重復上一步弃锐，直到在idxe2上取到的值不是’zhangs’時袄友，循環(huán)結束。

也就是說使用前綴索引霹菊，定義好長度剧蚣，就可以做到既節(jié)省空間，又不用額外增加太多的查詢成本旋廷。前面
已經講過區(qū)分度鸠按，區(qū)分度越高越好。因為區(qū)分度越高饶碘，意味著重復的鍵值越少目尖。

前綴索引對覆蓋索引的影響

結論：

使用前綴索引就用不上覆蓋索引對查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時需要考

慮的一個因素扎运。

索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性瑟曲，是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)

化方式。ICP可以減少存儲引擎訪問基表的次數(shù)以及MySQL服務器訪問存儲引擎的次數(shù)豪治。

ICP的使用條件：

① 只能用于二級索引(secondary index)

②explain顯示的執(zhí)行計劃中type值（join 類型）為 range 洞拨、 ref 、 eq_ref 或者 ref_or_null 负拟。

③ 并非全部where條件都可以用ICP篩選烦衣，如果where條件的字段不在索引列中，還是要讀取整表的記錄

到server端做where過濾。

④ ICP可以用于MyISAM和InnnoDB存儲引擎

⑤ MySQL 5.6版本的不支持分區(qū)表的ICP功能花吟，5.7版本的開始支持秸歧。

⑥ 當SQL使用覆蓋索引時，不支持ICP優(yōu)化方法示辈。

普通索引 vs 唯一索引

從性能的角度考慮寥茫，你選擇唯一索引還是普通索引呢？選擇的依據(jù)是什么呢矾麻？

假設纱耻，我們有一個主鍵列為ID的表，表中有字段k险耀，并且在k上有索引弄喘，假設字段 k 上的值都不重復。

1. 查詢過程

假設甩牺，執(zhí)行查詢的語句是 select id from test where k=5蘑志。

• 對于普通索引來說，查找到滿足條件的第一個記錄(5,500)后贬派，需要查找下一個記錄急但，直到碰到第一個不滿足k=5條件的記錄。
• 對于唯一索引來說搞乏，由于索引定義了唯一性波桩，查找到第一個滿足條件的記錄后，就會停止繼續(xù)檢
• 索请敦。

那么镐躲，這個不同帶來的性能差距會有多少呢？答案是侍筛， 微乎其微 萤皂。

2. 更新過程

為了說明普通索引和唯一索引對更新語句性能的影響這個問題，介紹一下change buffer匣椰。

當需要更新一個數(shù)據(jù)頁時裆熙，如果數(shù)據(jù)頁在內存中就直接更新，而如果這個數(shù)據(jù)頁還沒有在內存中的話窝爪，

在不影響數(shù)據(jù)一致性的前提下弛车， InooDB會將這些更新操作緩存在change buffer中 ，這樣就不需要從磁

盤中讀入這個數(shù)據(jù)頁了蒲每。在下次查詢需要訪問這個數(shù)據(jù)頁的時候纷跛，將數(shù)據(jù)頁讀入內存，然后執(zhí)行change

buffer中與這個頁有關的操作邀杏。通過這種方式就能保證這個數(shù)據(jù)邏輯的正確性贫奠。

將change buffer中的操作應用到原數(shù)據(jù)頁唬血，得到最新結果的過程稱為 merge 。除了 訪問這個數(shù)據(jù)頁 會觸

發(fā)merge外唤崭，系統(tǒng)有 后臺線程會定期 merge拷恨。在 數(shù)據(jù)庫正常關閉（shutdown） 的過程中，也會執(zhí)行merge

操作谢肾。

如果能夠將更新操作先記錄在change buffer腕侄， 減少讀磁盤 ，語句的執(zhí)行速度會得到明顯的提升芦疏。而且冕杠，

數(shù)據(jù)讀入內存是需要占用 buffer pool 的，所以這種方式還能夠 避免占用內存 酸茴，提高內存利用率分预。

唯一索引的更新就不能使用change buffer ，實際上也只有普通索引可以使用薪捍。

3. change buffer的使用場景

4. 普通索引和唯一索引應該怎么選擇笼痹？其實，這兩類索引在查詢能力上是沒差別的酪穿，主要考慮的是
   對 更新性能 的影響凳干。所以，建議你 盡量選擇普通索引 被济。

5. 在實際使用中會發(fā)現(xiàn)纺座， 普通索引 和 change buffer 的配合使用，對于 數(shù)據(jù)量大 的表的更新優(yōu)化
   還是很明顯的溉潭。

6. 如果所有的更新后面，都馬上 伴隨著對這個記錄的查詢 少欺，那么你應該 關閉change buffer 喳瓣。而在
   其他情況下，change buffer都能提升更新性能赞别。

7. 由于唯一索引用不上change buffer的優(yōu)化機制畏陕，因此如果 業(yè)務可以接受 ，從性能角度出發(fā)建議優(yōu)
   先考慮非唯一索引仿滔。但是如果"業(yè)務可能無法確保"的情況下惠毁，怎么處理呢？

• 首先崎页， 業(yè)務正確性優(yōu)先 鞠绰。我們的前提是“業(yè)務代碼已經保證不會寫入重復數(shù)據(jù)”的情況下，討論性能
  問題飒焦。如果業(yè)務不能保證蜈膨，或者業(yè)務就是要求數(shù)據(jù)庫來做約束屿笼，那么沒得選，必須創(chuàng)建唯一索引翁巍。
  這種情況下驴一，本節(jié)的意義在于，如果碰上了大量插入數(shù)據(jù)慢灶壶、內存命中率低的時候肝断，給你多提供一
  個排查思路。
• 然后驰凛，在一些“ 歸檔庫 ”的場景胸懈，你是可以考慮使用唯一索引的。比如洒嗤，線上數(shù)據(jù)只需要保留半年箫荡，
  然后歷史數(shù)據(jù)保存在歸檔庫。這時候渔隶，歸檔數(shù)據(jù)已經是確保沒有唯一鍵沖突了羔挡。要提高歸檔效率，
  可以考慮把表里面的唯一索引改成普通索引间唉。

其它查詢優(yōu)化策略

1绞灼、EXISTS 和 IN 的區(qū)分

問題：

不太理解哪種情況下應該使用 EXISTS，哪種情況應該用 IN呈野。選擇的標準是看能否使用表的索引嗎低矮？

2、COUNT()與COUNT(具體字段)效率*

問：在 MySQL 中統(tǒng)計數(shù)據(jù)表的行數(shù)被冒，可以使用三種方式： SELECT COUNT(*) 军掂、 SELECT COUNT(1) 和

SELECT COUNT(具體字段) ，使用這三者之間的查詢效率是怎樣的昨悼？

3 關于SELECT()*

在表查詢中蝗锥，建議明確字段，不要使用 * 作為查詢的字段列表率触，推薦使用SELECT <字段列表> 查詢终议。原

因：

① MySQL 在解析的過程中，會通過 查詢數(shù)據(jù)字典 將"*"按序轉換成所有列名葱蝗，這會大大的耗費資源和時

間穴张。

② 無法使用 覆蓋索引

4 LIMIT 1 對優(yōu)化的影響

針對的是會掃描全表的 SQL 語句，如果你可以確定結果集只有一條两曼，那么加上 LIMIT 1 的時候皂甘，當找

到一條結果的時候就不會繼續(xù)掃描了，這樣會加快查詢速度悼凑。

如果數(shù)據(jù)表已經對字段建立了唯一索引叮贩，那么可以通過索引進行查詢击狮，不會全表掃描的話，就不需要加

上 LIMIT 1 了益老。

5 多使用COMMIT

只要有可能彪蓬，在程序中盡量多使用 COMMIT，這樣程序的性能得到提高捺萌，需求也會因為 COMMIT 所釋放

的資源而減少档冬。

COMMIT 所釋放的資源：

• 回滾段上用于恢復數(shù)據(jù)的信息
• 被程序語句獲得的鎖
• redo / undo log buffer 中的空間
• 管理上述 3 種資源中的內部花費

主鍵如何設計的？

自增ID的問題

自增ID做主鍵桃纯，簡單易懂酷誓，幾乎所有數(shù)據(jù)庫都支持自增類型，只是實現(xiàn)上各自有所不同而已态坦。自增ID除
了簡單盐数，其他都是缺點，總體來看存在以下幾方面的問題：

1. 可靠性不高 存在自增ID回溯的問題伞梯，這個問題直到最新版本的MySQL 8.0才修復玫氢。
2. 安全性不高 對外暴露的接口可以非常容易猜測對應的信息。比如：/User/1/這樣的接口谜诫，可以非常容易猜測用戶ID的
   值為多少漾峡，總用戶數(shù)量有多少，也可以非常容易地通過接口進行數(shù)據(jù)的爬取喻旷。
3. 性能差 自增ID的性能較差生逸，需要在數(shù)據(jù)庫服務器端生成。
4. 交互多 業(yè)務還需要額外執(zhí)行一次類似 last_insert_id() 的函數(shù)才能知道剛才插入的自增值且预，這需要多一次的
   網絡交互槽袄。在海量并發(fā)的系統(tǒng)中，多1條SQL锋谐，就多一次性能上的開銷掰伸。
5. 局部唯一性 最重要的一點，自增ID是局部唯一怀估，只在當前數(shù)據(jù)庫實例中唯一，而不是全局唯一合搅，在任意服務器間都
   是唯一的多搀。對于目前分布式系統(tǒng)來說，這簡直就是噩夢灾部。

業(yè)務字段做主鍵

建議盡量不要用跟業(yè)務有關的字段做主鍵康铭。畢竟，作為項目設計的技術人員赌髓，我們誰也無法預測

在項目的整個生命周期中，哪個業(yè)務字段會因為項目的業(yè)務需求而有重復蝴猪，或者重用之類的情況出現(xiàn)讥邻。

經驗：

剛開始使用 MySQL 時，很多人都很容易犯的錯誤是喜歡用業(yè)務字段做主鍵懊蒸，想當然地認為了解業(yè)

務需求，但實際情況往往出乎意料悯搔，而更改主鍵設置的成本非常高骑丸。

淘寶的主鍵設計

從上圖可以發(fā)現(xiàn)，訂單號不是自增ID妒貌！我們詳細看下上述4個訂單號：

1550672064762308113

1481195847180308113

1431156171142308113

1431146631521308113

訂單號是19位的長度通危，且訂單的最后5位都是一樣的，都是08113灌曙。且訂單號的前面14位部分是單調遞增

的菊碟。

大膽猜測，淘寶的訂單ID設計應該是：

訂單ID = 時間 + 去重字段 + 用戶ID后6位尾號

這樣的設計能做到全局唯一在刺，且對分布式系統(tǒng)查詢及其友好逆害。

推薦的主鍵設計

非核心業(yè)務 ：對應表的主鍵自增ID，如告警增炭、日志忍燥、監(jiān)控等信息。

核心業(yè)務 ：主鍵設計至少應該是全局唯一且是單調遞增隙姿。全局唯一保證在各系統(tǒng)之間都是唯一的梅垄，單調

遞增是希望插入時不影響數(shù)據(jù)庫性能。

這里推薦最簡單的一種主鍵設計：UUID输玷。

UUID的特點：

全局唯一队丝，占用36字節(jié)，數(shù)據(jù)無序欲鹏，插入性能差机久。

認識UUID：

為什么UUID是全局唯一的？

為什么UUID占用36個字節(jié)赔嚎？

為什么UUID是無序的膘盖？

MySQL數(shù)據(jù)庫的UUID組成如下所示：

UUID = 時間+UUID版本（16字節(jié)）- 時鐘序列（4字節(jié)） - MAC地址（12字節(jié)）

為什么UUID是全局唯一的？

在UUID中時間部分占用60位尤误，存儲的類似TIMESTAMP的時間戳侠畔，但表示的是從1582-10-15 00：00：00.00

到現(xiàn)在的100ns的計數(shù)∷鹞睿可以看到UUID存儲的時間精度比TIMESTAMPE更高软棺，時間維度發(fā)生重復的概率降

低到1/100ns。

時鐘序列是為了避免時鐘被回撥導致產生時間重復的可能性尤勋。MAC地址用于全局唯一喘落。

為什么UUID占用36個字節(jié)茵宪？

UUID根據(jù)字符串進行存儲，設計時還帶有無用"-"字符串瘦棋，因此總共需要36個字節(jié)稀火。

為什么UUID是隨機無序的呢？

因為UUID的設計中兽狭，將時間低位放在最前面憾股，而這部分的數(shù)據(jù)是一直在變化的，并且是無序箕慧。

改造UUID

若將時間高低位互換服球，則時間就是單調遞增的了，也就變得單調遞增了颠焦。MySQL 8.0可以更換時間低位和

時間高位的存儲方式斩熊，這樣UUID就是有序的UUID了。

MySQL 8.0還解決了UUID存在的空間占用的問題伐庭，除去了UUID字符串中無意義的"-"字符串粉渠，并且將字符

串用二進制類型保存，這樣存儲空間降低為了16字節(jié)圾另。

可以通過MySQL8.0提供的uuid_to_bin函數(shù)實現(xiàn)上述功能霸株，同樣的，MySQL也提供了bin_to_uuid函數(shù)進行

轉化：

SET @uuid = UUID();
SELECT @uuid集乔，uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

通過函數(shù)uuid_to_bin(@uuid,true)將UUID轉化為有序UUID了去件。全局唯一 + 單調遞增，這不就是我們想要

的主鍵扰路！

在當今的互聯(lián)網環(huán)境中尤溜，非常不推薦自增ID作為主鍵的數(shù)據(jù)庫設計。更推薦類似有序UUID的全局

唯一的實現(xiàn)汗唱。

另外在真實的業(yè)務系統(tǒng)中宫莱，主鍵還可以加入業(yè)務和系統(tǒng)屬性，如用戶的尾號哩罪，機房的信息等授霸。這樣

的主鍵設計就更為考驗架構師的水平了。

如果不是MySQL8.0 腫么辦际插？

手動賦值字段做主鍵碘耳！

比如，設計各個分店的會員表的主鍵腹鹉，因為如果每臺機器各自產生的數(shù)據(jù)需要合并，就可能會出現(xiàn)主鍵

重復的問題敷硅。

可以在總部 MySQL 數(shù)據(jù)庫中功咒，有一個管理信息表愉阎，在這個表中添加一個字段，專門用來記錄當前會員編

號的最大值力奋。

門店在添加會員的時候榜旦，先到總部 MySQL 數(shù)據(jù)庫中獲取這個最大值，在這個基礎上加 1景殷，然后用這個值

作為新會員的“id”溅呢，同時，更新總部 MySQL 數(shù)據(jù)庫管理信息表中的當 前會員編號的最大值猿挚。

這樣一來咐旧，各個門店添加會員的時候，都對同一個總部 MySQL 數(shù)據(jù)庫中的數(shù)據(jù)表字段進 行操作绩蜻，就解

決了各門店添加會員時會員編號沖突的問題铣墨。

7. 數(shù)據(jù)庫結構優(yōu)化

一個好的數(shù)據(jù)庫設計方案對于數(shù)據(jù)庫的性能往往會起到事半功倍的效果。

需要考慮數(shù)據(jù)冗余办绝、查詢和更新的速度伊约、字段的數(shù)據(jù)類型是否合理等多方面的內容。

1. 將字段很多的表分解成多個表

對于字段較多的表孕蝉，如果有些字段的使用頻率很低屡律，可以將這些字段分離出來形成新表。

因為當一個表的數(shù)據(jù)量很大時降淮，會由于使用頻率低的字段的存在而變慢超埋。

1. 增加中間表

對于需要經常聯(lián)合查詢的表，可以建立中間表以提高查詢效率骤肛。

通過建立中間表纳本，將需要通過聯(lián)合查詢的數(shù)據(jù)插入到中間表中，然后將原來的聯(lián)合查詢改為對中間表的查詢腋颠。

1. 增加冗余字段

設計數(shù)據(jù)表時應盡量遵循范式理論的規(guī)約繁成，盡可能的減少冗余字段，讓數(shù)據(jù)庫設計看起來精致淑玫、優(yōu)雅巾腕。但是，合理的加入冗余字段可以提高查詢速度絮蒿。

表的規(guī)范化程度越高尊搬，表和表之間的關系越多，需要連接查詢的情況也就越多土涝，性能也就越差佛寿。

注意：

冗余字段的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導致數(shù)據(jù)不一致的問題冀泻。

8. MySQL數(shù)據(jù)庫cpu飆升到500%的話他怎么處理常侣？

當 cpu 飆升到 500%時，先用操作系統(tǒng)命令 top 命令觀察是不是 MySQLd 占用導致的弹渔，如果不是胳施，找出占用高的進程，并進行相關處理肢专。

如果是 MySQLd 造成的舞肆， show processlist，看看里面跑的 session 情況博杖，是不是有消耗資源的 sql 在運行椿胯。找出消耗高的 sql，看看執(zhí)行計劃是否準確欧募， index 是否缺失压状，或者實在是數(shù)據(jù)量太大造成。

一般來說跟继，肯定要 kill 掉這些線程(同時觀察 cpu 使用率是否下降)种冬，等進行相應的調整(比如說加索引、改 sql舔糖、改內存參數(shù))之后娱两，再重新跑這些 SQL。

也有可能是每個 sql 消耗資源并不多金吗，但是突然之間十兢，有大量的 session 連進來導致 cpu 飆升，這種情況就需要跟應用一起來分析為何連接數(shù)會激增摇庙，再做出相應的調整旱物，比如說限制連接數(shù)等。

9. 大表怎么優(yōu)化卫袒？

類似的問題：某個表有近千萬數(shù)據(jù)宵呛，CRUD比較慢，如何優(yōu)化夕凝？分庫分表了是怎么做的宝穗？分表分庫了有什么問題？有用到中間件么码秉？他們的原理知道么逮矛？

當MySQL單表記錄數(shù)過大時，數(shù)據(jù)庫的CRUD性能會明顯下降转砖，一些常見的優(yōu)化措施如下：

• 限定數(shù)據(jù)的范圍： 務必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句须鼎。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內；
• 讀/寫分離： 經典的數(shù)據(jù)庫拆分方案晋控，主庫負責寫挑围，從庫負責讀；
• 緩存： 使用MySQL的緩存糖荒，另外對重量級、更新少的數(shù)據(jù)可以考慮模捂；
• 通過分庫分表的方式進行優(yōu)化捶朵，主要有垂直分表和水平分表。

[10. 分析查詢語句：EXPLAIN]

MySQL 5.6.3以前只能 EXPLAIN SELECT 狂男；MYSQL 5.6.3以后就可以 EXPLAIN SELECT综看，UPDATE，

DELETE

EXPLAIN 語句輸出的各個列的作用如下：

image.png

EXPLAIN各列作用

為了讓大家有比較好的體驗岖食，我們調整了下 EXPLAIN 輸出列的順序红碑。

1. table

不論我們的查詢語句有多復雜，里邊兒 包含了多少個表 泡垃，到最后也是需要對每個表進行 單表訪問 的析珊，所

以MySQL規(guī)定EXPLAIN語句輸出的每條記錄都對應著某個單表的訪問方法，該條記錄的table列代表著該

表的表名（有時不是真實的表名字蔑穴，可能是簡稱）忠寻。

2. id

我們寫的查詢語句一般都以 SELECT 關鍵字開頭，比較簡單的查詢語句里只有一個 SELECT 關鍵字存和，比

如下邊這個查詢語句：

SELECT * FROM s1 WHERE key1 = 'a';</pre>

稍微復雜一點的連接查詢中也只有一個 SELECT 關鍵字奕剃，比如：

SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common
field = 'a';

小結:

• id如果相同，可以認為是一組捐腿，從上往下順序執(zhí)行
• 在所有組中纵朋，id值越大，優(yōu)先級越高茄袖，越先執(zhí)行
• 關注點：id號每個號碼操软，表示一趟獨立的查詢, 一個sql的查詢趟數(shù)越少越好

3. select_type

image.png

4. partitions (可略)

5. type ☆

完整的訪問方法如下： system ， const 绞佩， eq_ref 寺鸥， ref ， fulltext 品山， ref_or_null 胆建，index_merge unique_subquery ， index_subquery 肘交， range 笆载， index ， ALL 。

• system

CREATE TABLE t(i int) Engine=MyISAM;

INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t;

• const

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

image.png

• eq_ref

 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

image.png

從執(zhí)行計劃的結果中可以看出凉驻，MySQL打算將s2作為驅動表腻要，s1作為被驅動表，重點關注s1的訪問

方法是 eq_ref 涝登，表明在訪問s1表的時候可以 通過主鍵的等值匹配 來進行訪問雄家。

• ref

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

• fulltext 全文索引
• ref_or_null

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

• index_merge

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

image.png

從執(zhí)行計劃的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式來執(zhí)行

對 s1 表的查詢胀滚。

• unique_subquery

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 =
s2.key1) OR key3 = 'a';

• index_subquery

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where
s1.key1 = s2.key1) OR key3 = 'a';

• range

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
或者：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

• index

<pre data-language="sql" id="Btx0v" class="ne-codeblock language-sql" style="border: 1px solid #e8e8e8; border-radius: 2px; background: #f9f9f9; padding: 16px; font-size: 13px; color: #595959">EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';</pre>

[圖片上傳失敗...(image-b11941-1655208719520)]

• ALL

EXPLAIN SELECT * FROM s1;

小結：

結果值從最好到最壞依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >

unique_subquery > index_subquery >range > index > ALL其中比較重要的幾個提取出來（見上圖中的藍

色）趟济。SQL 性能優(yōu)化的目標：至少要達到 range 級別，要求是 ref 級別咽笼，最好是 consts級別顷编。（阿里巴巴

開發(fā)手冊要求）

【面向校招】全力備戰(zhàn)2023Golang實習與校招
https://huchao.blog.csdn.net/article/details/124220802?spm=1001.2014.3001.5502

歡迎共同進步：
QQ群：1007576722