目錄

分析慢 SQL

SQL 優(yōu)化

單表優(yōu)化

多表優(yōu)化

---

?

• 慢 SQL：指 MySQL 中執(zhí)行比較慢的 SQL
• 排查慢 SQL 最常用的方法：通過(guò)慢查詢(xún)?nèi)罩緛?lái)查找慢 SQL

MySQL 的慢查詢(xún)?nèi)罩臼?MySQL 提供的一種日志記錄，它用來(lái)記錄在 MySQL 中響應(yīng)時(shí)間超過(guò)閾值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò)?long_query_time（慢查詢(xún)閾值） 值的 SQL，就會(huì)被記錄到慢查詢(xún)?nèi)罩局?#xff0c;long_query_time 的默認(rèn)值為 10s，意思是運(yùn)行超過(guò) 10s 以上的語(yǔ)句就會(huì)被當(dāng)做慢 SQL 記錄到日志中。

---

分析慢 SQL

如果一條 sql 執(zhí)行很慢的話(huà)，我們通常會(huì)使用 mysql 自動(dòng)的執(zhí)行計(jì)劃 explain 來(lái)去查看這條 sql 的執(zhí)行情況

關(guān)注 type 字段：

• all — 掃描全表數(shù)據(jù)
• index — 遍歷索引
• range — 索引范圍查找
• index_subquery — 在子查詢(xún)中使用 ref
• unique_subquery — 在子查詢(xún)中使用 eq_ref
• ref_or_null — 對(duì) null 進(jìn)行索引的優(yōu)化的 ref
• fulltext — 使用全文索引
• ref — 使用非唯一索引查找數(shù)據(jù)
• eq_ref — 在 join 查詢(xún)中使用主鍵或唯一索引關(guān)聯(lián)
• const — 將一個(gè)主鍵放置到 where 后面作為條件查詢(xún)， MySQL 優(yōu)化器就能把這次查詢(xún)優(yōu)化轉(zhuǎn)化為一個(gè)常量，如何轉(zhuǎn)化以及何時(shí)轉(zhuǎn)化，這個(gè)取決于優(yōu)化器，這個(gè)比 eq_ref 效率高一點(diǎn)。

如果存在全索引掃描（type = all） 則說(shuō)明沒(méi)有走索引，我們可以給查詢(xún)的慢字段加上相應(yīng)的索引就可以提交效率。

通過(guò) key 和 key_len 檢查是否命中了索引，如果本身已經(jīng)添加了索引，也可以判斷索引是否又失效的情況

通過(guò) extra 建議判斷是否出現(xiàn)回表的情況，如果出現(xiàn)了可以嘗試添加索引或修改返回字段來(lái)修復(fù)

SQL 優(yōu)化

MySQL 優(yōu)化分為 單表優(yōu)化 和 多表優(yōu)化

單表優(yōu)化

• 建立并使用索引：索引是提高查詢(xún)最有效的手段
• 優(yōu)化查詢(xún)語(yǔ)句：避免使用 select * ，只查詢(xún)需要的字段；使用小表驅(qū)動(dòng)大表，比如當(dāng) B 表的數(shù)據(jù)小于 A 表時(shí)，先查 B 表，再查 A 表，查詢(xún)語(yǔ)句：select * from A where id in (select id from B)；如果是聚合查詢(xún)，盡量使用 union all 代替 union，union 會(huì)多義詞過(guò)濾，效率比較低；不使用 order by rand()；
• 優(yōu)化表結(jié)構(gòu)和數(shù)據(jù)類(lèi)型：單表不要有太多字段，建議在 20 個(gè)字段以?xún)?nèi)，使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類(lèi)型，盡可能使用 not null 定義字段，因?yàn)?null 占用 4 字節(jié)空間。

多表優(yōu)化

• 表拆分：就是分表，讓每張表的數(shù)據(jù)量變小，從而提高查詢(xún)效率。表拆分又分為：垂直分隔和水平分隔。

? ? ? ? 垂直拆分：是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表，比如，用戶(hù)表中一些字段經(jīng)常被訪(fǎng)問(wèn)，將這些字段放在一張表中，另外一些不常用的字段放在另一張表中，插入數(shù)據(jù)時(shí)，使用事務(wù)確保兩張表的數(shù)據(jù)一致性。

? ? ? ? 水平拆分：指數(shù)據(jù)表行的拆分，表的行數(shù)超過(guò)200萬(wàn)行時(shí)，就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來(lái)存放。通常情況下，我們使用取模的方式來(lái)進(jìn)行表的拆分，比如，一張有 400W 的用戶(hù)表 users，為提高其查詢(xún)效率我們把其分成 4 張表 users1，users2，users3，users4，然后通過(guò)用戶(hù) ID 取模的方法，同時(shí)查詢(xún)、更新、刪除也是通過(guò)取模的方法來(lái)操作。

• 讀寫(xiě)分離：一般情況下對(duì)數(shù)據(jù)庫(kù)而言都是“讀多寫(xiě)少”，換言之，數(shù)據(jù)庫(kù)的壓力多數(shù)是因?yàn)榇罅康淖x取數(shù)據(jù)的操作造成的，我們可以采用數(shù)據(jù)庫(kù)集群的方案，使用一個(gè)庫(kù)作為主庫(kù)，負(fù)責(zé)寫(xiě)入數(shù)據(jù)；其他庫(kù)為從庫(kù)，負(fù)責(zé)讀取數(shù)據(jù)。這樣可以緩解對(duì)數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)壓力。

優(yōu)化方式有很多，?比如索引、查詢(xún)優(yōu)化（減少聯(lián)表查詢(xún)等）、減少鎖競(jìng)爭(zhēng)等因素，所以具體的慢 SQL 優(yōu)化，需要根據(jù)實(shí)際的業(yè)務(wù)場(chǎng)景再做優(yōu)化決策。