741 阿裏雲技術社區[雲棲]

阿裏雲慢SQL優化挑戰大賽分析

【背景】
阿裏雲慢SQL優化挑戰賽：
https://yq.aliyun.com/articles/136363?spm=5176.100240.searchblog.32.oYlhtr
【考點分析】
本次慢SQL優化挑戰賽的題目全部來自於生產案例，將眾多考察點揉合到一條SQL中，主要考慮了以下方麵：
1. 表設計：考察字符和數字字段定義，字符集大小寫校驗，時間字段存儲。
2. 驅動表：考察多表join時候最優的連接順序。
3. 索引優化：考察索引消除排序以，索引隱式轉換，覆蓋索引避免回表的問題。
4. 執行計劃：使用explain extended獲取SQL執行計劃中的異常點。
備注：
1. 可以修改字段定義，可以修改SQL寫法，可以添加創建索引，不得刪除添加字段。
2. 基礎數據統一由阿裏雲來提供，測試環境可以是在RDS中或者自建的數據庫中。
3. 數據庫版本：MySQL 5.6，數據庫要求關閉query_cache。
4. 最終的執行時間以阿裏雲RDS上執行時間為準。
【問題分析】

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_id` bigint(20) DEFAULT NULL,
  `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32744 DEFAULT CHARSET=utf8;
CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `seller_name` varchar(100) DEFAULT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `sales` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32744 DEFAULT CHARSET=utf8;
CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) DEFAULT NULL,
  `order_id` varchar(100) DEFAULT NULL,
  `state` bigint(20) DEFAULT NULL,
  `gmt_create` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=458731 DEFAULT CHARSET=utf

8;
待優化SQL：

select a.seller_id,a.seller_name,b.user_name,c.state from a,b,c
where  a.seller_name=b.seller_name  and b.user_id=c.user_id and  c.user_id=17  and
a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE) AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE)  order  by  a.gmt_create；

原始SQL每次執行需要200ms以上，所以我們開始進行優化。
1.優化的第一步就是分析SQL的執行計劃：

2.上圖執行計劃中全部是type=ALL的全表掃描，需要創建合適的索引來避免全表掃描。同時注意到執行計劃的表連接順序是小表驅動大表：A->B->C，符合MySQL 優化器NLP的算法。所以我們在選擇驅動表的時候，需要注意將小表作為驅動表。所以接下來就需要選擇到底是那張表作為驅動表。
A表：

mysql> select count(*) from a where  a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL - 600 MINUTE) AND  DATE_ADD(NOW(), INTERVAL 600 MINUTE);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
B表：
mysql> select count(*) from b;
+----------+
| count(*) |
+----------+
|    16385 |
+----------+
C表：
mysql> select count(*) from c where c.user_id=17;
+----------+
| count(*) |
+----------+
|        1 |
+----------+

可以看到A，B，C三張表中，A和C表根據條件過濾都隻有1條記錄，B由於沒有直接的篩選條件，所以按照原始記錄進行計算。同時我們看到SQL的最後還需要按照gmt_create時間字段進行排序，所以我們優先考慮以A表作為驅動表來進行優化：A->B->C的順序。

A表上創建索引：
Alter table a add index ind_a_gmt_create(gmt_create);
如果回表取數據量較大，可以考慮將關聯字段和查詢字段冗餘到索引中
Alter table a add index ind_a_gmt_create(gmt_create,seller_name,seller_id);
B表上創建索引：
Alter table b add index ind_b_seller_name(seller_name);
如果回表取數據量較大，可以考慮將關聯字段和查詢字段冗餘到索引中
Alter table b add index ind_b_seller_name1(seller_name,user_name,user_id);
C表創建索引：
Alter table c add index ind_c_user_id(user_id);
如果回表取數據量較大，可以考慮將關聯字段和查詢字段冗餘到索引中
Alter table c add index ind_c_user_id(user_id,state);

再次查看執行計劃：

添加完索引之後我們在看執行計劃，發現A，B並沒有按照我們的預設走索引，同時C表即使走了索引，但是Rows項還是很大，這到底是什麼原因？
3.現在要分析為什麼我們添加的索引沒有走上，可以使用explain extended 的方式去查看執行計劃更加詳細的部分，比如索引為什麼不能夠使用。

從上麵的warnings信息可以看到索引不能夠使用的原因式因為字段類型或者校驗規則出現了不一致的情況，所以我們回到上述題目中表結構的定義，發現了問題的根源：

a表：gmt_create使用了varchar來存儲，在5.6支持更高時間精度後，將會發生隱式轉換。

b表：a表和b表的seller_name字段在COLLATE定義上不一致，也會導致隱式轉換。

c表：b表和c表的user_id字段都定義為了varchar，但是SQL傳入為數字，也會導致隱式轉換。

所以表結構定義該改為：

alter table  a modify column  gmt_create datetime;  
alter table  a modify column  seller_name varchar(100) ;
alter table  c modify column user_id  bigint;

經過上麵的優化，執行時間基本在2ms左右，在返回數據量大的情況下，可以將查詢關聯字段冗餘到索引中去避免回表訪問。

4.在眾多候選人的答案中發現了還有一種方法就是使用C->B->A的方式，也就是：

alter table c add index ind_c_user_id(user_id);
alter table  c modify column user_id  bigint;
alter table b add index ind_user_id(user_id);
alter table b modify column user_id bigint;
alter table  a modify column  seller_name varchar(100) ;
alter table a add index ind_a_seller_name(seller_name);

這種驅動表的順序在本次數據環境下的性能也是很好的，但是執行計劃會有排序，這樣在大數據量排序的情況下性能會變得很差，所以最優的答案還是需要消除排序。

最後看到後多候答案修改gmt_create字段為date類型，導致最終表的數據被截斷，最終查詢結果無法正常顯示，非常可惜。

【案例擴展】
在這些年的工作之中，由於SQL問題導致的數據庫故障層出不窮。於是將過去工作中遇到的SQL問題總結歸類，還原問題原貌，給出分析和解決問題的思路，幫助用戶在使用數據庫的過程中能夠少走一些彎路。
一、索引篇
索引問題是SQL問題中出現頻率最高的，常見的索引問題包括：無索引，隱式轉換，。上述所舉的案例中就是因為訪問表中的SQL無索引導致全表掃描，掃描大量的數據，應用請求變慢占用數據庫連接，連接堆積很快達到數據庫的最大連接數設置，新的應用請求將會被拒絕導致故障發生。隱式轉換是指SQL查詢條件中的傳入值與對應字段的數據定義不一致導致索引無法使用。常見隱士轉換如字段的表結構定義為字符類型，但SQL傳入值為數字；或者是字段定義collation為區分大小寫，在多表關聯的場景下另外的關聯字段卻不區分大小寫。隱式轉換會導致索引無法使用，進而出現上述慢SQL堆積數據庫連接數跑滿的情況。
1.無索引案例：
表結構：
CREATE TABLE user (
……
mo bigint NOT NULL DEFAULT '' ,
KEY ind_mo (mo)
……
) ENGINE=InnoDB;

SELECT uid FROM user WHERE mo=1377255 LIMIT 0,1
執行計劃
mysql> explain SELECT uid FROM user WHERE mo=1377255 LIMIT 0,1;
id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: NULL
key: NULL
rows: 707250
Extra: Using where
從上麵的SQL看到執行計劃中ALL，代表了這條SQL執行計劃是全表掃描，每次執行需要掃描707250行數據，這是非常消耗性能的，該如何進行優化？添加索引。

驗證mo字段的過濾性
mysql> select count(*) from user where mo=1377255;
| 0 |
添加索引
mysql> alter table user add index ind_mo(mo);
mysql>SELECT uid FROM user WHERE mo=1377255 LIMIT 0,1;
Empty set (0.05 sec)
執行計劃
mysql> explain SELECT uid FROM user WHERE mo=1377255 LIMIT 0,1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: index
possible_keys: ind_mo
key: ind_mo
rows: 1
Extra: Using where; Using index
2.隱式轉換案例一：
表結構
CREATE TABLE user (
……
mo char(11) NOT NULL DEFAULT '' ,
KEY ind_mo (mo)
……
) ENGINE=InnoDB;
執行計劃
mysql> explain extended select uid fromuser where mo=1377255 limit 0,1;
mysql> show warnings;
Warning1：Cannot use index 'ind_mo' due to type or collation conversion on field 'mo'
Note：select user.uid AS uid from user where (user.mo = 1377255) limit 0,1
如何解決：
mysql> explain SELECT uid FROM user WHERE mo='1377255' LIMIT 0,1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
type: ref
possible_keys: ind_mo
key: ind_mo
rows: 1
Extra: Using where; Using index
上述案例中由於表結構定義mo字段後字符串數據類型，而應用傳入的則是數字，進而導致了隱式轉換，索引無法使用，所以有兩種方案：
第一，將表結構mo修改為數字數據類型。
第二，修改應用將應用中傳入的字符類型改為數據類型。
3.隱式轉換案例二：
CREATE TABLE test_date (
id int(11) DEFAULT NULL,
gmt_create varchar(100) DEFAULT NULL,
KEY ind_gmt_create (gmt_create)
) ENGINE=InnoDB AUTO_INCREMENT=524272;

修改COLLATE
mysql> alter table t1 modify column c1 varchar(100) COLLATE utf8_bin ;
Query OK, 401920 rows affected (2.79 sec)
Records: 401920 Duplicates: 0 Warnings: 0

執行計劃
mysql> explain select t1.* from t2 left join t1 on t1.c1=t2.c1 where t2.c2='b';
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
| 1 | SIMPLE| t2| ref | ind_c2| ind_c2 | 303 | const | 258 | Using where |
| 1 |SIMPLE| t1|ref| ind_c1 | ind_c1 | 303 | test.t2.c1 | 33527 | |
+----+-------------+-------+------+---------------+--------+---------+------------+-------+-------------+
可以看到修改了字段的COLLATE後執行計劃使用到了索引，所以一定要注意表字段的collate屬性的定義保持一致。

5.兩個索引的常見誤區：
誤區一：對查詢條件的每個字段建立單列索引，例如查詢條件為：A=？and B=？and C=？
在表上創建了3個單列查詢條件的索引ind_A(A)，ind_B(B)，ind_C(C)，應該根據條件的過濾性，創建適當的單列索引或者組合索引。
誤區二：對查詢的所有字段建立組合索引，例如查詢條件為select A,B,C,D,E,Ffrom T where G=？
在表上創建了ind_A_B_C_D_E_F_G(A,B,C,D,E,F,G)；

6.最佳實踐
那如何避免無索引及發生了隱式轉換呢？主要有以下四個途徑：
1）在使用索引時，我們可以通過explain+extended查看SQL的執行計劃，判斷是否使用了索引以及發生了隱式轉換。
2）由於常見的隱式轉換是由字段數據類型以及collation定義不當導致，因此我們在設計開發階段，要避免數據庫字段定義，避免出現隱式轉換。
3）由於MySQL不支持函數索引，在開發時要避免在查詢條件加入函數，例如date(gmt_create)。
4）所有上線的SQL都要經過嚴格的審核，創建合適的索引。
二、SQL改寫篇
SQL優化在這裏總結了三類常見的，包括分頁優化、子查詢優化
1.分頁優化：
表結構
CREATE TABLE buyer (
id int(11) NOT NULL AUTO_INCREMENT,
……
PRIMARY KEY (id)
KEY ind_seller (sellerid)
) ENGINE=InnoDB;
SQL語句
select * from buyer where sellerid=100 limit 100000，5000
這條語句是普通的Limit M、N的翻頁寫法，在越往後翻頁的過程中速度越慢，因為MySQL會讀取表M+N條數據，M越大，性能越差。
我們通過采用高效的Limit寫法，可以將上述語句改寫成：
select t1.* from buyer t1,
(select id from buyer sellerid=100 limit 100000，5000) t2
where t1.id=t2.id;
從而避免分頁查詢給數據庫帶來性能影響。需要注意一點是，這裏需要在t表的sellerid字段上創建索引，id為表的主鍵。
2.子查詢優化：
典型子查詢
SELECT first_name
FROM employees
WHERE emp_no IN
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);
MySQL的處理邏輯是遍曆employees表中的每一條記錄，代入到子查詢中中去
改寫子查詢
SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
WHERE emp.emp_no = sal.emp_no;
3.最佳實踐：
采用高效的 Limit 寫法，避免分頁查詢給數據庫帶來性能影響；
子查詢在5.1，5.5版本中都存在較大風險，將子查詢改為關聯，使用Mysql 5.6的版本，可以避免麻煩的子查詢改寫；
另外避免用 SELECT * 查詢所有字段數據，隻查詢需要的字段數據。

三、參數優化篇
數據庫中的參數配置對SQL執行速度快慢也有非常大的影響，常見的三個參數包括innodb_buffer_pool_size，tmp_table_size,query_cache_size,table_cache：
1.innodb_buffer_pool_size
作用：定義了innodb引擎緩衝池的大小，該緩衝池主要緩存了索引以及數據，如果SQL查詢的數據在緩衝池中已經緩存，那就不需要從磁盤中讀取，性能會得到快速的提升。筆者曾經看到有生產係統使用了mysql的默認配置128MB，導致數據庫磁盤的使用率達到了100%。
建議：通常配置主機內存的70%~80%之間。
2.tmp_table_size
作用：該參數用於決定內部內存臨時表的最大值，每個線程都要分配（實際起限製作用的是tmp_table_size和max_heap_table_size的最小值），如果內存臨時表超出了限製，MySQL就會自動地把它轉化為基於磁盤的MyISAM表，優化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在內存中的。
現象：如果複雜的SQL語句中包含了group by/distinct等不能通過索引進行優化而使用了臨時表，則會導致SQL執行時間加長。
建議：如果應用中有很多group by/distinct等語句，同時數據庫有足夠的內存，可以增大tmp_table_size(max_heap_table_size)的值，以此來提升查詢性能。
3.table_open_cache
作用：該參數用於決定內部內存臨時表的最大值，每個線程都要分配（實際起限製作用的是tmp_table_size和max_heap_table_size的最小值），如果內存臨時表超出了限製，MySQL就會自動地把它轉化為基於磁盤的MyISAM表，優化查詢語句的時候，要避免使用臨時表，如果實在避免不了的話，要保證這些臨時表是存在內存中的。
現象：如果複雜的SQL語句中包含了group by/distinct等不能通過索引進行優化而使用了臨時表，則會導致SQL執行時間加長。
建議：通常在設置table_open_cache參數的時候，在業務的高峰時期，檢查open_Tables的值，如果open_Tables的值與table_open_cache的值相等，並且opened_tales的值在不斷的增加，這個時候就需要對table_open_cache的值增加了，這個時候線程的狀態：Opening tables
mysql> show profiles;
+———-+————+—————–+
| Query_ID | Duration | Query |
+———-+————+—————–+
| 1 | 0.09211525 | select * from d |
| 2 | 0.03659925 | select * from d |
| 3 | 0.22665400 | select * from d |
| 4 | 0.11063350 | select * from d |
| 5 | 0.06929725 | select * from d |
| 6 | 0.09054975 | select * from d |
| 7 | 0.15971375 | select * from d |
| 8 | 0.12960625 | select * from d |
| 9 | 0.22713975 | select * from d |
| 10 | 0.00124025 | select * from d |
+———-+————+—————–+
mysql> show profile cpu for query 4;
+———————-+———-+———-+————+
| Status | Duration | CPU_user | CPU_system |
+———————-+———-+———-+————+
| starting | 0.000198 | 0.001000 | 0.000000 |
| checking permissions | 0.000053 | 0.000000 | 0.000000 |
| Opening tables | 0.000454 | 0.000999 | 0.001000 |
| init | 0.000059 | 0.000000 | 0.000999 |
| System lock | 0.000055 | 0.000000 | 0.000000 |
| optimizing | 0.000053 | 0.000000 | 0.000000 |
| statistics | 0.000056 | 0.000000 | 0.000000 |
| preparing | 0.000056 | 0.000000 | 0.000000 |
| executing | 0.000052 | 0.001000 | 0.000000 |
| Sending data | 0.000072 | 0.000000 | 0.000000 |
| end | 0.000053 | 0.000000 | 0.000000 |
| query end | 0.000056 | 0.000000 | 0.000000 |
| closing tables | 0.000056 | 0.000000 | 0.000000 |
| freeing items | 0.000076 | 0.000000 | 0.000000 |
| cleaning up | 0.000056 | 0.000000 | 0.000000 |

4.query_cache_size
作用：該參數用於控製MySQL query cache的內存大小；如果MySQL開啟query cache，再執行每一個query的時候會先鎖住query cache，然後判斷是否存在query cache中，如果存在直接返回結果，如果不存在，則再進行引擎查詢等操作；同時insert、update和delete這樣的操作都會將query cahce失效掉，這種失效還包括結構或者索引的任何變化，cache失效的維護代價較高，會給MySQL帶來較大的壓力，所以當我們的數據庫不是那麼頻繁的更新的時候，query cache是個好東西，但是如果反過來，寫入非常頻繁，並集中在某幾張表上的時候，那麼query cache lock的鎖機製會造成很頻繁的鎖衝突，對於這一張表的寫和讀會互相等待query cache lock解鎖，導致select的查詢效率下降。
現象：數據庫中有大量的連接狀態為checking query cache for query、Waiting for query cache lock、storing result in query cache；
建議：RDS默認是關閉query cache功能的，如果您的實例打開了query cache，當出現上述情況後可以關閉query cache；當然有些情況也可以打開query cache，比如：巧用query cache解決數據庫性能問題。
四、優化器篇
優化器根據統計信息以及優化器參數計算出SQL的執行計劃，所以統計信息和優化算法決定著執行計劃的優劣。常見優化器導致SQL執行出現緩慢的情況包括兩種，統計信息不準確導致索引走錯出現性能下降；數據庫版本升級導致優化器參數發生變化，進而導致執行計劃發生變化，性能可能變差。
1.優化器參數
數據庫從5.5升級到5.6，一條sql在 5.5執行隻需要零點幾秒，而在5.6上需要10多秒。通過對比5.5版本和5.6版本的執行計劃，發現5.6版本中的執行計劃多出了block_nested_loop。
1) 5.5的優化器策略：
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
2) 5.6的優化器策略：
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,block_nested_loop=on…..

五．小結
SQL的優化是DBA／開發日常工作中不可缺少的一部分，記得在學生時期，曾經在ITPUB上看到一篇帖子，當時樓主在介紹SQL優化的時候，用一個公式來講解他在做sql優化的時候遵循的原則：
T=S/V(T代表時間，S代表路程，V代表速度)
S指SQL所需訪問的資源總量，V指SQL單位時間所能訪問的資源量，T自然就是SQL執行所需時間了;我們為了獲得SQL最快的執行時間，可以根據公式定義上去反推：
1. 在S不變的情況下，我們可以提升V來降低T：通過適當的索引調整，我們可以將大量的速度較慢的隨機IO轉換為速度較快的順序IO；通過提升服務器的內存，使得將更多的數據放到內存中，會比數據放到磁盤上會得到明顯的速度提升；采用電子存儲介質進行數據存儲和讀取的SSD，突破了傳統機械硬盤的性能瓶頸，使其擁有極高的存儲性能;在提升V上我們可以采用較高配置的硬件來完成速度的提升；
2. 在V不變的情況下，我們可以減小S來降低T：這是SQL優化中非常核心的一個環節，在減小S環節上，DBA可以做的可以有很多，通常可以在查詢條件中建立適當的索引，來避免全表掃描；有時候可以改寫SQl，添加一些適當的提示符，來改變SQL的執行計劃，使SQL以最少的掃描路徑完成查詢；當這些方法都使用完了之後，你是否還有其他方案來優化？在阿裏係的DBA職位描述中有條就是要求DBA需要深入的了解業務，當DBA深入的了解業務之後，這個時候能站在業務上，又站DB角度上考慮，這個時候在去做優化，有時候能達到事半功倍的效果。

最後更新：2017-08-30 15:32:53

阿裏雲慢SQL優化挑戰大賽分析

上一篇：開發者論壇一周精粹(第十七期) ：【漏洞預警】Windows再被爆SMB服務0day漏洞，阿裏雲提示您關注並修複

下一篇：開發者論壇一周精粹(第十六期)：阿裏雲全球十四個節點評測分析及服務器地域選擇攻略

相關內容

熱門內容

最新內容

阿裏雲慢SQL優化挑戰大賽分析

上一篇： 開發者論壇一周精粹(第十七期) ：【漏洞預警】Windows再被爆SMB服務0day漏洞，阿裏雲提示您關注並修複

下一篇： 開發者論壇一周精粹(第十六期)：阿裏雲全球十四個節點評測分析及服務器地域選擇攻略

相關內容

熱門內容

最新內容

上一篇：開發者論壇一周精粹(第十七期) ：【漏洞預警】Windows再被爆SMB服務0day漏洞，阿裏雲提示您關注並修複

下一篇：開發者論壇一周精粹(第十六期)：阿裏雲全球十四個節點評測分析及服務器地域選擇攻略