MySQL性能優化、故障排查及最佳實踐秘籍，阿里雲資料庫專家玄慚的「武功」全記錄

最新 07-13

文章簡介

玄慚，真名羅龍九，阿里雲DBA專家，負責阿里雲RDS線上穩定以及專家服務團隊。他經歷過阿里歷年雙11實戰考驗，積累了7年對阿里雲資料庫用戶的運維、調優、診斷等豐富DBA經驗。本專題集結了玄慚排查經驗、性能優化心得、最佳實踐以及其他思考。

性能優化

1. 性能測試：自建資料庫對比RDS中應當注意的地方（適用於MySQL，SQL SERVER，MongoDB）

常常很多用戶對比測試自建資料庫和RDS的性能差異，其測試結果往往是RDS不如ECS自建，用戶往往懷疑難道我花了那麼多的錢買的RDS難道還不如自己在ECS上搭建嗎？從資料庫測試的角度來看，測試首先必須是的公平的進行，其結果才具有說服力。

2. 一個價值「千萬」的秒殺場景參數優化

秒殺最早來自天貓雙11各種商品的促銷活動中，現在已經有很多業務場景在使用，比如搶紅包，搶票等。其特點有三高：瞬時並發高，數據一致性高，熱點更新頻度高。這樣三高的場景下往往給資料庫造成極大的壓力，大量更新資料庫中的同一行，這樣必然會產生鎖等待，導致資料庫的性能急劇下降的問題，很容出現雪崩效應。面對秒殺業務的場景，資料庫成為了底層系統中最重要的瓶頸點，阿里經過幾年的沉澱也誕生了很多的技術手段來進行優化，這裡我們就重點講一下底層數據所做的優化。

3. 複雜關聯SQL的優化

昨天處理了一則複雜關聯SQL的優化，這類SQL的優化往往考慮以下四點：

查詢所返回的結果集，通常查詢返回的結果集很少，是有信心進行優化的；

驅動表的選擇至關重要，通過查看執行計劃，可以看到優化器選擇的驅動表,從執行計劃中的rows可以大致反映出問題的所在；

理清各表之間的關聯關係，注意關聯欄位上是否有合適的索引；

使用straight_join關鍵詞來強製表之間的關聯順序，可以方便我們驗證某些猜想；

4. 化繁為簡-優化sql

這裡有一段對話取自於和用戶的一段旺旺聊天記錄，在徵得用戶的同意後，放到我的blog中，希望更多的人能夠看見，分享是一件快樂的事情;同時也想藉此來說明一些問題，有時候試圖用一條sql完成所有的業務邏輯可能會遇到麻煩，需要對複雜的sql進行一些拆分，可能會得到更好的效果。

5. MySql sql優化之order by desc/asc limit M

Order by desc/asc limit M是我在mysql sql優化中經常遇到的一種場景，其優化原理也非常的簡單，就是利用索引的有序性，優化器沿著索引的順序掃描，在掃描到符合條件的M行數據後，停止掃描。看起來非常的簡單，但是我經常看到很多性能較差的SQL沒有利用這個優化規律，這裡將結合一些實際的案例來分析說明。

6. mysql sql優化之straight_join

在mysql中就有之對應的straight_join,由於mysql只支持nested loops的連接方式，所以這裡的straight_join類似oracle中的use_nl hint。mysql優化器在處理多表的關聯的時候，很有可能會選擇錯誤的驅動表進行關聯，導致了關聯次數的增加，從而使得sql語句執行變得非常的緩慢，這個時候需要有經驗的DBA進行判斷，選擇正確的驅動表，這個時候straight_join就起了作用了，下面我們來看一看使用straight_join進行優化的案例。

7. 淺談mysql的子查詢

mysql的子查詢的優化一直不是很友好，一直有受業界批評比較多,也是我在sql優化中遇到過最多的問題之一，你可以點擊這裡，這裡來獲得一些信息，mysql在處理子查詢的時候，會將子查詢改寫,通常情況下，我們希望由內到外，也就是先完成子查詢的結果，然後在用子查詢來驅動外查詢的表，完成查詢，但是恰恰相反，子查詢不會先被執行；今天希望通過介紹一些實際的案例來加深對mysql子查詢的理解。

8. SQL優化的一些總結

SQL的優化是DBA日常工作中不可缺少的一部分，我們可以按照 T=S/V(T代表時間，S代表路程，V代表速度)的思路來進行優化。

9. mysql explain 中key_len的計算

今天丁原問我mysql執行計劃中的key_len是怎麼計算得到的，當時還沒有注意，在高性能的那本書講到過這個值的計算，但是自己看執行計劃的時候一直都沒有太在意這個值，更不用說深討這個值的計算了： ken_len表示索引使用的位元組數，根據這個值，就可以判斷索引使用情況，特別是在組合索引的時候，判斷所有的索引欄位都被查詢用到。

10. loose index scan 優化distinct

有這樣的一個需求：select count(distinct nick) from user_access_xx_xx;這條sql用於統計用戶訪問的uv，由於單表的數據量在10G以上，即使在user_access_xx_xx上加上nick的索引，通過查看執行計劃，也為全索引掃描，sql在執行的時候，會對整個伺服器帶來抖動。

11. 使用偽』loose index scan』優化max

有時候我們會遇到以下的應用場景：

SELECT MAX(log_time)

FROM log_table

WHERE log_machine IN ($machines)

CREATE TABLE log_table (

id INT NOT NULL PRIMARY KEY,

log_machine VARCHAR(20) NOT NULL,

log_time DATETIME NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

我們建立的索引為：(log_machine,log_time)，當我們傳入單個machine的時候，速度很快，但是當我們傳入多個machines的時候，查詢速度會一下子就降下來。

12. mysql批量提交的優化

Java應用排量寫入MySQL的優化，使用rewriteBatchedStatements=true參數，對批量操作，性能有較大提高，從官方解釋上看，對普通操作沒有。

------------------------------------------------------------------------------------

故障排查

1. Oracle遷移到MySQL性能下降的注意點

最近有較多的客戶系統由原來由Oracle改造到MySQL後出現了性能問題CPU 100%，或是後台的CRM系統複雜SQL在業務高峰的時候出現堆積導致業務故障。在我的記憶裡面淘寶最初從Oracle遷移到MySQL期間也遇到了很多SQL的性能問題，記憶最為深刻的子查詢，當初的版本是MySQL5.1，這個版本對子查詢的優化較差，導致了很多從Oracle遷移到MySQL的系統出現過性能問題，所以後面的開發規範中規定前台交易系統不要有複雜的表join。

2. MySQL 5.5版本注意大內存導致DDL變慢的問題

最近在協助用戶進行系統重構，RDS測試選型自然成為了本項目的一個重點，但是用戶在測試不同規格的時候發現大規格的實例性能居然不如小規格，4C32G規格性能比8C64G規格高出10%，其性能監控也是非常的正常，4C32G規格是8C64G規格資源消耗的一半，TPS也是相當，那問題到底出現在那裡？

3. 注意table_open_cache過小也會導致性能問題

周一的時候有一個客戶反饋自從上次rds重啟後，慢查詢特別多，有大量響應時間在1～3秒的請求，後端的工程師介入調查，發現隨便建一個最簡單的表，插入數據都需要300ms。一開始的時候懷疑可能是網路延遲導致的，客戶測試了從ecs到rds的網路延遲，測試結果網路延時不到1ms，那問題到底出現在那裡？在MySQL中可以使用profile去查看SQL的執行時間主要消耗在哪裡，所以我們來看一下profile。

4. RDS彈性升級後性能反而下降的案例

剛剛結束的2015年雙11，天貓以912億的成交量再次打破去年的記錄成為一個奇蹟，大家可能不知道，這些天貓的訂單最後的處理都是放在阿里雲聚石塔的機房完成，從2012年開始，淘寶的ISV，商家就開始把他們的訂單，CRM後台系統逐漸遷移到雲上，最核心的資料庫就是存放在RDS中。

雙11之前用戶都會進行大批量的彈性升級，期間有較多用戶反饋，在彈性升級後性能出現了大幅度的下降，其中由一個用戶有兩個RDS，一個RDS進行了彈性升級，另外一個RDS沒有出現彈性升級，結果彈性升級後的RDS反而出現了性能下降，這讓我們反思不得其解。RDS的彈性升級包括了兩部分，一部分是磁碟容量的升級，另一部分是內存容量的升級（內存升級會同時升級資料庫的連接數，CPU，IOPS），那麼是什麼原因導致了性能下降？

5. 關於RDS只讀實例延遲分析

只讀實例是目前RDS用戶實現數據讀寫分離的一種常見架構，用戶只需要將業務中的讀請求分擔到只讀節點上，就可以緩解主庫查詢壓力，同時也可以把一些OLAP的分析查詢放到另外的只讀節點上，減小複雜統計查詢對主庫的衝擊，RDS只讀節點架構圖如下：