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

02-03

文章簡介

玄慚，真名羅龍九，阿里雲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只讀節點架構圖如下：

由於RDS只讀節點採用原生的MySQL Binlog複製技術，那麼延遲必然會成為他成立之初就會存在的問題。

點擊閱讀詳情

6. 搗蛋SQL導致實例iops 100%

一用戶RDS每天隔一段時間就會出現IOPS 100%的問題，求助到阿里雲，這類問題的出現有以下一些排查思路：

慢SQL問題：通過優化索引，子查詢，隱士轉換，分頁改寫等優化；
DDL：create index，optimze table，alter table add column，create as select 。

點擊閱讀詳情

7. MySQL update use index merge(Using intersect) increase chances for deadlock

昨天一同事發現線上系統在並發更新的時候出現了死鎖，通過排查定位於update更新使用了兩個索引導致。

點擊閱讀詳情

8. 一次資料庫上雲遷移性能下降的排查

某客戶目前正在將本地的業務系統遷移上雲，測試過程中發現後台運營系統，在rds上運行時間明顯要比線下PC上自建資料庫運行時間要慢1倍，導致客戶系統割接延期的風險。用戶線下一台PC伺服器的性能居然還比頂配的RDS跑的快，這讓用戶對RDS的性能產生了質疑，需要立刻調查原因。

點擊閱讀詳情

9. RDS鏈路卡慢問題的診斷

經常會收到用戶反饋在使用RDS的過程中出現卡慢，閃斷地情況，當出現此類問題的時候，首先我們要進行一下測試，看看問題出現在哪一個階段，RDS給到用戶的是一個DNS地址，其實他包括三個階段：DNS–>VIP–>DB

我們可以在本地的應用伺服器（VM）上通過簡單的ping命令，或者資料庫的客戶端去不斷的連接測試RDS，來獲取每次連接RDS的響應時間（RT）。

點擊閱讀詳情

10. ibdata1文件持續增加的問題定位

用戶的ibdata1文件持續增加：

Innodb的表有兩種存放方式：

第一種共享表空間方式：所有表的索引，數據統一存放在一個共享表空間中，這樣會導致共享表空間的空間迅速增長，同時空間回收困難；

第二種獨佔表空間方式：就是RDS目前採用的，也就是一張表一個表空間，表中的索引和數據存放在自己獨立的表空間中，空間能夠比較容易的回收；

無論是獨佔還是共享表空間，innodb都會有系統共享表空間（ibdata1），該系統表空間主要用於存儲數據字典，undo entry，insert buffer，doublewrite buffer，

點擊閱讀詳情

11. 有趣的大小寫問題-utf8_bin

問題：

xxx@3023 14:51:26>insert into test_tmp_log_node_10445__01 select * from test;

ERROR 1062 (23000): Duplicate entry 『taobao|維西v』 for key 『idx_nodetemp_10445_01』

點擊閱讀詳情

12. 關於RDS實例CPU超過100%的分析

經常聽見用戶說自己的rds實例cpu超過100%，通常這種情況都是由於sql性能問題導致的。

點擊閱讀詳情

13. mysql主鍵的缺少導致備庫hang

最近線上頻繁的出現slave延時的情況，經排查發現為用戶在刪除數據的時候，由於表主鍵的主鍵的缺少，同時刪除條件沒有索引，或或者刪除的條件過濾性極差，導致slave出現hang住，嚴重的影響了生產環境的穩定性，也希望通過這篇博客，來加深主鍵在innodb引擎中的重要性，希望用戶在使用RDS，設計自己的表的時候，一定要為表加上主鍵，主鍵可以認為是innodb存儲引擎的生命，下面我們就來分析一下這個案例（本案例的生產環境的binlog為row模式，對於myisam存儲引擎也有同樣的問題）。

點擊閱讀詳情

14. Waiting Auto-INC LOCK導致死鎖

今天下午在看死鎖相關的文檔，到線上查看一生產資料庫的時候，正好發現了show engine innodb status有一個死鎖的信息： LATEST DETECTED DEADLOCK

點擊閱讀詳情

15. 執行計劃錯誤—索引統計信息的不準確

mysql在生成執行計劃的時候，需要根據索引的統計信息進行一個估算，計算出成本最低的索引；但是mysql索引統計信息的採集默認8個pag，其中5.1估算rows estimate的演算法存在bug http://bugs.mysql.com/bug.php?id=53761 ，

么你的執行計劃很有可能由於索引統計信息的不準確，導致優化不能夠正確的選擇索引。

點擊閱讀詳情

16. 主備不一致：Table definition on master and slave does not match

昨天一同事在線上做變更，為了保證主庫的穩定性，先在備庫把binlog關閉，然後在進行DDL變更，在通過切換HA，把備庫切換為主庫，在老的主庫上做DDL變更

看上去這樣做法沒有太大的問題，但是當備庫變更一做完，HA切換到備庫，開始老主庫變更的時候，備庫就出現複製出現錯誤.。

點擊閱讀詳情

17. 一則優化案例

昨晚收到客服MM電話，一用戶反饋資料庫響應非常慢，手機收到load異常報警，登上主機後發現大量sql執行非常慢，有的執行時間超過了10s，本文是解決思路總結。

點擊閱讀詳情

18.mysql中的Waiting for tables

接著上篇中遇到的mysql子查詢，在問題的診斷中，丹臣注意到一個較為嚴重的問題，就是我們生產庫中全部的資料庫訪問請求都處於Waiting for tables的狀態，在將大查詢kill掉後，所有的請求恢復正常；簡單的理解為大查詢阻塞了其他訪問請求，但是這個理論是不可信，如果阻塞該表的DML還可以理解，但是把該資料庫上的所有請求都阻塞了，這還是說不通的。那麼我們就來看看所有的請求處於Waiting for tables這個狀態是什麼原因導致的。

點擊閱讀詳情

19. 生產庫中遇到mysql的子查詢

使用過oracle或者其他關係資料庫的DBA或者開發人員都有這樣的經驗，在子查詢上都認為資料庫已經做過優化，能夠很好的選擇驅動表執行，然後在把該經驗移植到mysql資料庫上，但是不幸的是，mysql優化器在處理子查詢的時候，會將將子查詢改寫。

點擊閱讀詳情

20. mysql子查詢的弱點

mysql的子查詢的優化不是很友好，一直有受業界批評比較多.

關於mysql的查詢有兩個知識點:

第一個為mysql在處理所有的查詢的時候都強行轉換為聯接來執行，將每個查詢包括多表中關聯匹配，關聯子查詢，union，甚至單表的的查詢都處理為聯接，接著mysql執行聯接，把每個聯接在處理為一個嵌套循環(oracle-nest-loop);

第二個知識點：在mysql在處理子查詢的時候，會將將子查詢改寫,通常情況下，我們希望由內到外，先完成子查詢的結果，然後在用子查詢來驅動外查詢的表，完成查詢。

點擊閱讀詳情

21. Drop table 出現的問題

由於應用下線，需要把資料庫中相關應用的表刪除，庫中有一千多張表，事先已經將所有的表rename到test庫中，drop table的腳步也已經準備好，所以接下來的工作本以為是很輕鬆的事情，但是在執行腳本的過程中，發現刪除表的速度感覺有點慢，查看主機的負載也在挺高的，報警消息中thread running過高也出現了，發現大多數線程的狀態是Opening Tables，但還是勉強的忍受了過去，事後想想為什麼刪除表也會這麼的慢？

點擊閱讀詳情

22. Incorrect datetime value

MySQL中設置sql_mode。

點擊閱讀詳情

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

最佳實踐

1. RDS MySQL空間優化最佳實踐

本期將介紹存儲空間相關的最佳實踐。

點擊閱讀詳情

2. MySQL鎖問題最佳實踐

近一段時間處理了較多鎖的問題，包括鎖等待導致業務連接堆積或超時，死鎖導致業務失敗等，這類問題對業務可能會造成嚴重的影響，沒有處理經驗的用戶往往無從下手。下面將從整個資料庫設計，開發，運維階段介紹如何避免鎖問題的發生，提供一些最佳實踐供RDS的用戶參考。

點擊閱讀詳情

3. RDS MySQL參數調優最佳實踐

很多時候，RDS用戶經常會問如何調優RDS MySQL的參數，為了回答這個問題，寫一篇blog來進行解釋：

哪一些參數不能修改，那一些參數可以修改；
這些提供修改的參數是不是已經是最佳設置，如何才能利用好這些參數。

點擊閱讀詳情

4. 如何將RDS的數據同步到本地自建資料庫

長期以來有很多的用戶諮詢如何將RDS的數據同步到本地的資料庫環境中，本篇文章以在阿里雲的ECS伺服器為例來說明如何將RDS的數據同步到本地資料庫中。RDS對外提供服務是一個DNS地址+埠3306，這樣就屏蔽了RDS後端的主從節點，那麼該如何將數據同步到本地？

點擊閱讀詳情

5. RDS最佳實踐(五)—Mysql大欄位的頻繁更新導致binlog暴增

RDS Mysql採用的binlog 格式默認為ROW，在Mysql 5.6的版本之前，Mysql每次列的修改（update）都需要記錄表中所有列的值。這樣就存在一個問題，如果表中包含很多的大欄位，表的單行長度就會非常長，這樣每次update就會導致大量的 binlog空間生成。針對這個問題，在mysql 5.6中進行了改進，複製支持」row image control」，只記錄修改的列而不是行中所有的列，這對一些包含 BLOGs 欄位的數據來說可以節省很大的處理能力，因此此項改進不僅節省了磁碟空間，同時也提升了性能。

點擊閱讀詳情

6. RDS最佳實踐(四)—如何處理Mysql的子查詢

MySQL低版本中該如何優化的子查詢。

點擊閱讀詳情

7. RDS最佳實踐(三)—如何制定相關的流程來規範RDS的使用

如何制定相關的流程來規範RDS的使用？

點擊閱讀詳情

8. RDS最佳實踐(二)—如何快速平穩的遷入RDS

用戶在購買完RDS後，接下來就可以開始往RDS遷入數據了。在RDS是否支持在線平滑的遷移？

點擊閱讀詳情

9. RDS最佳實踐(一)—如何選擇你的RDS

我該如何選擇RDS？我要購買多大規格的RDS？RDS的連接數，iops指的是什麼？上訴這些問題相信是每一個RDS用戶在開始使用的時候都會有這樣的疑問。首先我們要了解一下RDS的組成包括哪一些，從阿里雲官網的購買頁面中我們可以看到RDS包括了以下參數：資料庫類型，版本，存儲空間，規格：內存+連接數+io，地域，那我們就一個個來分析一下。

點擊閱讀詳情

10. 巧用query cache

巧用query cache解決慢SQL的問題。

點擊閱讀詳情

11. innodb使用大欄位text，blob的一些優化建議

其中一個應用，對blob欄位的依賴非常的嚴重，查詢和更新的頻率也是非常的高，單表的存儲空間已經達到了近100G，如何優化？

點擊閱讀詳情

12. 為什麼我的RDS慢了？

為什麼我的RDS突然變慢了？相信這是大多數客戶在使用RDS中經常遇到的頭疼問題。這裡我將通過實際的真實案例來分析一下用戶在使用RDS中慢的原因。

點擊閱讀詳情

13. mysql分析函數的實現

MySQL中分析函數是如何實現的。

點擊閱讀詳情

14. 資料庫上雲經典案例分析

本文PPT來自阿里雲技術專家玄慚於10月14日在2016年杭州雲棲大會上發表的演講，分享主題為《資料庫上雲經典案例分析》。

點擊閱讀詳情

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

雙十一

1. 2016阿里雲資料庫雙11復盤-自動化備戰，0干預

2016年雙11狂歡節完美落幕，這是阿里雲RDS連續第五年支持雙11大促，在持續高壓力衝擊下，整個雙11期間0故障0丟單，相比前面四年，在備戰過程中更加的自動和主動，今年雙11高峰期間達到了0干預的目標。本文由核心參加工程師整理總結今年雙11備戰過程中在自動化以及性能優化所作的一些改進。

點擊閱讀詳情

2. 阿里雲資料庫專家玄慚：雲資料庫超大流量峰值保障最佳實踐

本次演講收集整理了自RDS成立至今，在歷次大流量峰值中如何保障活動中雲資料庫備戰的最佳實踐，包括之前的改造，壓測和擴容；期間的監控，預案執行和應急處理；之後的收容和總結。力求全鏈路地幫助客戶安全穩定地渡過超大流量峰值，讓在你備戰過程中少走一些彎路，多一些從容。

點擊閱讀詳情

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

問題分析

1. 查看mysql實時運行sql的工具–orztop

該工具為我的同事朱旭開發的一款可以查看mysql資料庫實時運行的sql狀況的工具，以前苦於通過show processlist/show full processlist抓取sql的同志們現在只要盯一盯屏幕就可以了，非常的方便。

點擊閱讀詳情

2. 使用Percona Data Recovery Tool for InnoDB恢複數據

昨晚收到一則求助，一個用戶的本地資料庫的重要數據由於誤操作被刪除，需要進行緊急恢復，用戶的資料庫日常並沒有進行過任何備份，binlog也沒有開啟，所以從備份和binlog入手已經成為不可能，諮詢了丁奇，發了一篇percona的文章給我，頓時感覺有希望，於是到percona的官網上下載了恢復工具。

點擊閱讀詳情

3. RDS MySql支持online ddl

在日常和客戶溝通的過程中發現，他們在做mysql ddl變更的時候由於MySql本身的缺陷不支持online ddl，導致他們的業務不得不hang住一會兒，表越大，時間影響越長，所以期待有更好的解決方法；有些用戶也想了一些方法，比如通過主備切換的方法，先在備庫進行ddl，然後在通過主備切換到原主庫進行ddl，但由於RDS對外提供給用戶的是一個dns加port，所以後端的主備對用戶是透明的，此方法行不通。其實在開源社區中已經有比較成熟的方法，那就是percona的pt-online-schema-change工具是其中之一，這裡通過測試主要了解該工具的可靠性以及存在的問題，是否在RDS上支持。

點擊閱讀詳情

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

其他

1. 雲資料庫 MySQL 版官網

MySQL是全球最受歡迎的開源資料庫，作為開源軟體組合 LAMP中的重要一環，廣泛應用於各類應用場景。

點擊閱讀詳情

2. 雲資料庫DBA專家服務

ApsaraDB專家服務組，全部來自阿里雲DBA團隊和資料庫內核團隊，多次參與歷年雙11的護航保障活動，為客戶提供阿里原廠頂尖水準的資料庫服務。

點擊閱讀詳情