MySQL 性能優化小結

基礎概念簡述

鎖

資料庫通過鎖機制來解決并發場景 — 共享鎖（讀鎖）和排他鎖（寫鎖），讀鎖是不阻塞的，多個客戶端可以在同一時刻讀取同一個資源；寫鎖是排他的，并且會阻塞其他的讀鎖和寫鎖，

簡單提下樂觀鎖和悲觀鎖：

• 樂觀鎖：通常用于資料競爭不激烈的場景，多讀少寫，通過版本號和時間戳實作
• 悲觀鎖：通常用于資料競爭激烈的場景，每次操作都會鎖定資料

要鎖定資料需要一定的鎖策略來配合，

• 表鎖：鎖定整張表，開銷最小，但是會加劇鎖競爭
• 行鎖：鎖定行級別，開銷最大，但是可以最大程度的支持并發

但是 MySQL 的存盤引擎的真實實作不是簡單的行級鎖，一般都是實作了多版本并發控制（MVCC），MVCC 是行級鎖的變種，多數情況下避免了加鎖操作，開銷更低，MVCC 是通過保存資料的某個時間點快照實作的，

事務

事務保證一組原子性的操作，要么全部成功，要么全部失敗，一旦失敗，回滾之前的所有操作，MySQL 采用自動提交，如果不是顯式的開啟一個事務，則每個查詢都作為一個事務，

隔離級別控制了一個事務中的修改，哪些在事務內和事務間是可見的，四種常見的隔離級別：

• 未提交讀（Read UnCommitted）：事務中的修改，即使沒提交對其他事務也是可見的，事務可能讀取未提交的資料，造成 臟讀
• 提交讀（Read Committed）：一個事務開始時，只能看見已提交的事務所做的修改，事務未提交之前，所做的修改對其他事務是不可見的，也叫 不可重復讀，同一個事務多次讀取同樣記錄可能不同
• 可重復讀（RepeatTable Read）：同一個事務中多次讀取同樣的記錄結果時結果相同
• 可串行化（Serializable）：最高隔離級別，強制事務串行執行

存盤引擎

• InnoDB 引擎：最重要，使用最廣泛的存盤引擎，被用來設計處理大量短期事務，具有高性能和自動崩潰恢復的特性
• MyISAM引擎：不支持事務和行級鎖，崩潰后無法安全恢復

創建時優化

Schema和資料型別優化

整數

TinyInt, SmallInt, MediumInt, Int, BigInt 使用的存盤8,16,24,32,64位存盤空間，

使用 Unsigned 表示不允許負數，可以使正數的上線提高一倍，

實數

• Float, Double：支持近似的浮點運算
• Decimal：用于存盤精確的小數

字串

• VarChar：存盤變長的字串，需要1或2個額外的位元組記錄字串的長度
• Char：定長，適合存盤固定長度的字串，如MD5值
• Blob, Text：為了存盤很大的資料而設計的，分別采用二進制和字符的方式

時間型別

• DateTime：保存大范圍的值，占8個位元組
• TimeStamp：推薦，與 UNIX 時間戳相同，占4個位元組

優化建議點

• 盡量使用對應的資料型別，比如，不要用字串型別保存時間，用整型保存IP
• 選擇更小的資料型別，能用 TinyInt 不用Int
• 標識列（identifier column），建議使用整型，不推薦字串型別，占用更多空間，而且計算速度比整型慢
• 不推薦 ORM 系統自動生成的 Schema，通常具有不注重資料型別，例如，使用很大的 VarChar 型別，索引利用不合理等問題
• 真實場景混用范式和反范式，冗余高查詢效率高，插入更新效率低；冗余低插入更新效率高，查詢效率低
• 創建完全的獨立的匯總表\快取表，定時生成資料，用于用戶耗時時間長的操作，對于精確度要求高的匯總操作，可以采用 歷史結果+最新記錄的結果 來達到快速查詢的目的
• 資料遷移，表升級的程序中可以使用影子表的方式，通過修改原表的表名，達到保存歷史資料，同時不影響新表使用的目的

索引

索引包含一個或多個列的值，MySQL 只能高效的利用索引的最左前綴列，索引的優勢：

• 減少查詢掃描的資料量
• 避免排序和零時表
• 將隨機 IO 變為順序 IO （順序IO的效率高于隨機IO）

B-Tree

使用最多的索引型別，采用 B-Tree 資料結構來存盤資料（每個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的遍歷），B-Tree 索引適用于全鍵值，鍵值范圍，鍵前綴查找，支持排序，

B-Tree 索引限制：

• 如果不是按照索引的最左列開始查詢，則無法使用索引
• 不能跳過索引中的列，如果使用第一列和第三列索引，則只能使用第一列索引
• 如果查詢中有個范圍查詢，則其右邊的所有列都無法使用索引優化查詢

哈希索引

只有精確匹配索引的所有列，查詢才有效，存盤引擎會對所有的索引列計算一個哈希碼，哈希索引將所有的哈希碼存盤在索引中，并保存指向每個資料行的指標，

哈希索引限制：

• 無法用于排序
• 不支持部分匹配
• 只支持等值查詢如 =, IN( )，不支持 < >

優化建議點

• 注意每種索引的適用范圍和適用限制，
• 索引的列如果是運算式的一部分或者是函式的引數，則失效
• 針對特別長的字串，可以使用前綴索引，根據索引的選擇性選擇合適的前綴長度
• 使用多列索引的時候，可以通過 AND 和 OR 語法連接
• 重復索引沒必要，如（A，B）和（A）重復
• 索引在 where 條件查詢和 group by 語法查詢的時候特別有效
• 將范圍查詢放在條件查詢的最后，防止范圍查詢導致的右邊索引失效的問題
• 索引最好不要選擇過長的字串，而且索引列也不宜為 null

查詢時優化

三個重要指標

• 回應時間 （服務時間，排隊時間）
• 掃描的行
• 回傳的行

查詢優化點

• 避免查詢無關的列，如使用 select * 回傳所有的列，
• 避免查詢無關的行
• 切分查詢，將一個對服務器壓力較大的任務，分解到一個較長的時間中，并分多次執行，如要洗掉一萬條資料，可以分10次執行，每次執- 行完成后暫停一段時間，再繼續執行，程序中可以釋放服務器資源給其他任務，
• 分解關聯查詢，將多表關聯查詢的一次查詢，分解成對單表的多次查詢，可以減少鎖競爭，查詢本身的查詢效率也比較高，因為 MySQL 的連接和斷開都是輕量級的操作，不會由于查詢拆分為多次，造成效率問題，
• 注意 count 的操作只能統計不為 null 的列，所以統計總的行數使用 count(*)
• group by 按照標識列分組效率高，分組結果不宜出行分組列之外的列
• 關聯查詢延遲關聯，可以根據查詢條件先縮小各自要查詢的范圍，再關聯
• union 查詢默認去重，如果不是業務必須，建議使用效率更高的 union all
• limit 分頁優化，可以根據索引覆寫掃描，再根據索引列關聯自身查詢其他列，如：

SELECT
 id,
 NAME,
 age
WHERE
 student s1
INNER JOIN (
 SELECT
  id
 FROM
  student
 ORDER BY
  age
 LIMIT 50,5
) AS s2 ON s1.id = s2.id

其它優化點

• 表關聯查詢時務必遵循 小表驅動大表 原則；
• 使用查詢陳述句 where 條件時，不允許出現 函式，否則索引會失效；
• 使用單表查詢時，相同欄位盡量不要用 OR，因為可能導致索引失效，比如：SELECT * FROM table WHERE name = '手機' OR name = '電腦'，可以使用 UNION 替代；
• LIKE 陳述句不允許使用 % 開頭，否則索引會失效；
• 組合索引一定要遵循 從左到右 原則，否則索引會失效；比如：SELECT * FROM table WHERE name = '張三' AND age = 18，那么該組合索引必須是 name,age 形式；
• 索引不宜過多，根據實際情況決定，盡量不要超過 10 個；
• 每張表都必須有 主鍵，達到加快查詢效率的目的；
• 分表，可根據業務欄位尾數中的個位或十位或百位（以此類推）做表名達到分表的目的；
• 分庫，可根據業務欄位尾數中的個位或十位或百位（以此類推）做庫名達到分庫的目的；
• 表磁區，類似于硬碟磁區，可以將某個時間段的資料放在磁區里，加快查詢速度，可以配合 分表 + 表磁區 結合使用
• 文章作者：彭超
• 本文首發于個人博客：https://antoniopeng.com/2019/12/08/mysql/MySQL%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E5%B0%8F%E7%BB%93/
• 著作權宣告：本博客所有文章除特別宣告外，均采用 CC BY-NC-SA 4.0 許可協議，轉載請注明來自 彭超 | Blog！

標籤：MySQL

上一篇：一文快速入門 MySQL 索引

下一篇：Bug: 洗掉中間某條記錄后/洗掉最后一條記錄再添加 ，auto-increment的自動增長不連續