排查DRDS慢SQL__開發手冊_分布式關係型數據庫 DRDS-阿裏雲

本文檔主要描述如何通過連接DRDS服務排查慢SQL。

TIPS

1.建議通過mysql命令行進行連接，mysql -hip -Pport -uuser -ppassword -c,最後-c請務必帶上，讓mysql客戶端將注釋下發

2.如果碰到命令報錯，請執行select version()，並且工單反饋，由我們來幫你升級實例版本，老版本實例可能存在部分指令異常

1.查看慢sql top10

如果我們隻是想看看當前DRDS庫中有沒有慢SQL，可以嚐試執行以下指令進行TOP 10慢SQL查詢。這個慢查詢是DRDS層麵的邏輯SQL,可能對應到RDS執行的SQL會分發到多個庫和表中去執行。

mysql> show slow;
+------------+---------+---------------------+--------------+------------+--------------------------------------------------------------------------------------+
| HOST       | SCHEMA  | START_TIME          | EXECUTE_TIME | AFFECT_ROW | SQL                                                                                  |
+------------+---------+---------------------+--------------+------------+--------------------------------------------------------------------------------------+
| 10.168.1.2 | test_db | 2015-10-13 15:45:03 |      1049429 |         10 | SELECT * FROM `App_IdentityCard` AS `i` INNER JOIN `App_Applicati...                 |
| 10.168.1.3 | test_db | 2015-10-13 15:37:34 |       955541 |          1 | SELECT count(*) AS `TotalCount` FROM `App_CarCard` AS `i` INNER JOIN...              |
| 10.168.1.3 | test_db | 2015-10-13 08:37:26 |       943829 |          0 | CREATE  INDEX `i_UpdateTime`  ON `App_AuditStatistics` ( `DomainName`, `UpdateTime`) |
| 10.168.1.3 | test_db | 2015-10-13 08:21:43 |       768825 |          0 | CREATE  INDEX `i_DomainName`  ON `App_AuditStatistics` ( `DomainName`)               |
| 10.168.1.3 | test_db | 2015-10-13 15:49:22 |       707314 |          5 | SELECT * FROM `App_CarCard` AS `i` INNER JOIN `App_Applicatione...                   |
| 10.168.1.3 | test_db | 2015-10-13 15:34:00 |       547408 |          1 | SELECT count(*) AS `TotalCount` FROM `App_PhoneKey` AS `i` INNER JOIN `App_A...      |
+------------+---------+---------------------+--------------+------------+--------------------------------------------------------------------------------------+
XXX rows in set (0.22 sec)

如果已經有明確的慢SQL，直接跳到第二步。

2.查看DRDS執行計劃

DRDS提供的explain指令執行結果會顯示這個SQL具體在哪幾個分片上執行，然後具體在數據節點上執行的SQL是什麼，執行的策略是什麼等等信息。我們將這些信息叫做DRDS的執行計劃，這個信息是靜態的，並沒有真正在數據庫上執行。

mysql> explain detail  select * from App_ChartAuditTrend limit 1;
+---------------+-------------------------------------------------------------------+
| TEST_0000_RDS | Merge as App_ChartAuditTrend
    limitFrom:0
    limitTo:1
    queryConcurrency:SEQUENTIAL
    columns:[App_ChartAuditTrend.Id, App_ChartAuditTrend.DomainName, App_ChartAuditTrend.UpdateTime, App_ChartAuditTrend.Url, App_ChartAuditTrend.AllNum, App_ChartAuditTrend.IdentityNum, App_ChartAuditTrend.CarNum, App_ChartAuditTrend.PhoneNum]
    executeOn:YUANWANG_CAS_1444638155126TKJWYUANWANG_CAS_ZJRX_0000_RDS
        Query from App_ChartAuditTrend as App_ChartAuditTrend
            limitFrom:0
            limitTo:1
            queryConcurrency:SEQUENTIAL
            columns:[App_ChartAuditTrend.Id, App_ChartAuditTrend.DomainName, App_ChartAuditTrend.UpdateTime, App_ChartAuditTrend.Url, App_ChartAuditTrend.AllNum, App_ChartAuditTrend.IdentityNum, App_ChartAuditTrend.CarNum, App_ChartAuditTrend.PhoneNum]
            tableName:App_ChartAuditTrend
            executeOn:YUANWANG_CAS_1444638155126TKJWYUANWANG_CAS_ZJRX_0000_RDS
        Query from App_ChartAuditTrend as App_ChartAuditTrend
            limitFrom:0
            limitTo:1
            queryConcurrency:SEQUENTIAL
            columns:[App_ChartAuditTrend.Id, App_ChartAuditTrend.DomainName, App_ChartAuditTrend.UpdateTime, App_ChartAuditTrend.Url, App_ChartAuditTrend.AllNum, App_ChartAuditTrend.IdentityNum, App_ChartAuditTrend.CarNum, App_ChartAuditTrend.PhoneNum]
            tableName:App_ChartAuditTrend
            executeOn:YUANWANG_CAS_1444638155126TKJWYUANWANG_CAS_ZJRX_0001_RDS
        Query from App_ChartAuditTrend as App_ChartAuditTrend
            limitFrom:0
            limitTo:1
            queryConcurrency:SEQUENTIAL
            columns:[App_ChartAuditTrend.Id, App_ChartAuditTrend.DomainName, App_ChartAuditTrend.UpdateTime, App_ChartAuditTrend.Url, App_ChartAuditTrend.AllNum, App_ChartAuditTrend.IdentityNum, App_ChartAuditTrend.CarNum, App_ChartAuditTrend.PhoneNum]
            tableName:App_ChartAuditTrend
            executeOn:YUANWANG_CAS_1444638155126TKJWYUANWANG_CAS_ZJRX_0002_RDS     
...
+---------------+-------------------------------------------------------------------+

裏麵所顯示的XXXX_RDS代表數據分片，具體對應的庫可以通過show node和show datasourcse兩個命令得到


mysql> show node;
+------+---------------+-------------------+------------------+---------------------+--------------------+
| ID   | NAME          | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |
+------+---------------+-------------------+------------------+---------------------+--------------------+
| 0    | TEST_0032_RDS |                51 |                0 | 100%                | 0%                 |
| 1    | TEST_0033_RDS |                43 |                0 | 100%                | 0%                 |
| 2    | TEST_0034_RDS |                43 |                0 | 100%                | 0%                 |
| 3    | TEST_0035_RDS |                43 |                0 | 100%                | 0%                 |
| ...  | ...           |               ... |              ... | 100%                | 0%                 |
+------+---------------+-------------------+------------------+---------------------+--------------------+
XX rows in set (0.36 sec)
mysql> show datasources;
+------+---------+-----------------------+---------------+-------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+
| ID   | SCHEMA  | NAME                  | GROUP         | URL                           | USER  | TYPE  | INIT | MIN  | MAX  | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT |
+------+---------+-----------------------+---------------+-------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+
|    0 | test_db | test_db_0032_imnc_59  | TEST_0032_RDS | jdbc:mysql://ip:3306/test_0032| user1 | mysql | 0    | 1    | 150  | 1            | 5000     | 0            | 3             |
|    1 | test_db | test_db_0033_imnc_108 | TEST_0033_RDS | jdbc:mysql://ip:3306/test_0033| user1 | mysql | 0    | 1    | 150  | 1            | 5000     | 1            | 2             |
|    2 | test_db | test_db_0034_imnc_47  | TEST_0034_RDS | jdbc:mysql://ip:3306/test_0034| user1 | mysql | 0    | 1    | 150  | 1            | 5000     | 1            | 2             |
+------+---------+-----------------------+---------------+-------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+
XXX rows in set (0.23 sec)
3.跟蹤sql執行過程
在得到大致SQL的執行範圍和策略的之後，我們就需要實際跟蹤下這個SQL在各個DRDS組件和底下的數據分片上執行的時間以便能夠基本定位到瓶頸在什麼地方。如果你非常熟悉DRDS，也可以一開始上來就直接執行trace命令。

TIPS

一開始之所以建議大家直接使用MySQL官方命令行，其中一個原因就是像DRDS trace命令需要保持連接的上下文信息，而某些GUI客戶端可能會使用連接池，導致命令不正常。

trace命令會實際執行SQL，因為可能存在SQL超時(DRDS層麵應對服務不被hang住所做的一個保護)，所以建議SQL前加一個防止超時的注釋，如果這個注釋沒生效，建議檢查下連接串是否帶了-c選項。
mysql> trace /!TDDL:SOCKET_TIMEOUT=0*/select * from App_IdentityCard limit 1;
+--------------------------------+---------------------+---------------------+--------------------+
| DomainName                     | ApplicationId       | UpdateTime          | IdentityCard       |
+--------------------------------+---------------------+---------------------+--------------------+
| sdfsdfdsfsdfsdfsdfsdf          | 6205076500132922050 | 2015-10-13 18:25:57 | 000000263180292197 |
+--------------------------------+---------------------+---------------------+--------------------+
1 row in set (0.35 sec)
執行完畢後，執行以下命令查看結果，你可以看到每個數據分片(類似XXX_RDS)的執行時長，以及DRDS層麵的主要組件時間消耗。一般情況下，如果數據分片執行時間相對過長，需要注意排查3個事情。

1.數據庫上的執行計劃是否對。
2.到數據庫上執行的SQL是否返回大量數據。
3.DRDS是否存在類似merge sort ,temp table merge等耗時操作。

mysql> show trace;
+------+----------+---------------+---------------+--------------------------+------+--------------------------------------------------+--------+
| ID   | TYPE     | DATA_NODE     | TIME_COST(MS) | CONNECTION_TIME_COST(MS) | ROWS | STATEMENT                                        | PARAMS |
+------+----------+---------------+---------------+--------------------------+------+--------------------------------------------------+--------+
|    0 | Optimize | DRDS          | 175           | 0.00                     |    0 | /*+TDDL({"extra":{"SOCKET_TIMEOUT":0}})*/selec...| NULL   |
|    1 | Query    | TEST_0000_RDS | 10            | 6.00                     |    0 | select `App_IdentityCard`.`DomainName`,`App_Id...| NULL   |
|    2 | Query    | TEST_0000_RDS | 1             | 0.02                     |    0 | select `App_IdentityCard`.`DomainName`,`App_Id...| NULL   |
|  ... | ...      | ...           | 2             | 0.01                     |    0 | select `App_IdentityCard`.`DomainName`,`App_Id...| NULL   |
+------+----------+---------------+---------------+--------------------------+------+--------------------------------------------------+--------+
1 row in set (0.35 sec)
另外關於數據庫上執行的狀況，我們也可以通過打開另外一個命令行，執行show processlist 命令來查看底下數據庫中正在執行的SQL詳情
mysql> show processlist;
+------------+-----------+------------------------+---------+------+-----------+-----------------------------------------+-----------+---------------+-----------+
| ID         | USER      | DB                     | COMMAND | TIME | STATE     | INFO                                    | ROWS_SENT | ROWS_EXAMINED | ROWS_READ |
+------------+-----------+------------------------+---------+------+-----------+-----------------------------------------+-----------+---------------+-----------+
| 0-0-73777  | ftwobwdf0 | yuanwang_cas_zjrx_0038 | Sleep   |   58 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-78566  | ftwobwdf0 | yuanwang_cas_zjrx_0038 | Query   |    0 | query end | insert into `App_IdentityCard_286` ( `D |      NULL |          NULL |      NULL |
| 0-0-78834  | ftwobwdf0 | yuanwang_cas_zjrx_0032 | Sleep   |    1 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-78843  | ftwobwdf0 | yuanwang_cas_zjrx_0036 | Sleep   |    0 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-79031  | ftwobwdf0 | yuanwang_cas_zjrx_0035 | Sleep   |    0 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-79166  | ftwobwdf0 | yuanwang_cas_zjrx_0035 | Sleep   |   17 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-79477  | ftwobwdf0 | yuanwang_cas_zjrx_0039 | Query   |    0 | query end | insert into `App_IdentityCard_286` ( `D |      NULL |          NULL |      NULL |
| 0-0-79636  | ftwobwdf0 | yuanwang_cas_zjrx_0037 | Sleep   |    0 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-79639  | ftwobwdf0 | yuanwang_cas_zjrx_0032 | Sleep   |    0 |           | NULL                                    |      NULL |          NULL |      NULL |
| 0-0-79889  | ftwobwdf0 | yuanwang_cas_zjrx_0034 | Sleep   |    0 |           | NULL                                    |      NULL |          NULL |      NULL |
+------------+-----------+------------------------+---------+------+-----------+-----------------------------------------+-----------+---------------+-----------+
XXX rows in set (0.35 sec)
這裏順帶提下kill指令，目前DRDS層麵還沒有根據邏輯SQL執行kill命令的，這個後續需求中會做掉，但是並不意味著DRDS這邊不能做kill,實際操作這個命令還是非常有效和直接的，DRDS會聚合所有當前DRDS數據庫對應的數據分片，並將這些分片每個processlist進行展現，並且能夠直接通過以下命令kill 相關分片上的某一個執行.
mysql> kill '0-0-79477'；
4.查看RDS/MYSQL執行計劃
trace基本上能夠找到慢的或者相對慢的部分在什麼地方，如果是DRDS層麵的，可以找到我們進行谘詢，看怎麼改SQL，如果是RDS層麵也比較慢，我們可以先通過執行以下兩個命令進行數據庫層麵的執行計劃查看，注意，如果您使用mysql命令行連接DRDS，請在啟動參數中加入-c，否則注釋會被命令行過濾掉：
查看某個分庫上的執行計劃
mysql> /*TDDL:node=0*/explain select * from tddl5_users_00;
+----+-------------+----------------+------+---------------+-----+---------+-----+------+-------+
| id | select_type | table          | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+-----+---------+-----+------+-------+
|  1 | SIMPLE      | tddl5_users_00 | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
+----+-------------+----------------+------+---------------+-----+---------+-----+------+-------+
1 row in set (0.02 sec)
查看所有分庫上的執行計劃
分庫不分表：
mysql> /*TDDL:scan*/explain select * from tddl6_users;
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
| id | select_type | table       | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
|  1 | SIMPLE      | tddl6_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl6_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl6_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl6_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
4 rows in set (0.02 sec)
分庫分表：
mysql> /*TDDL:scan='tddl5_users'*/explain select * from tddl5_users;
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
| id | select_type | table       | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
|  1 | SIMPLE      | tddl5_users | ALL  | NULL          | NULL| NULL    | NULL|    1 |       |
+----+-------------+-------------+------+---------------+-----+---------+-----+------+-------+
16 rows in set (0.32 sec)
TIPS

以上三條注釋也對其他SQL有效，如果有碰到不支持的，請反饋給我們。

5.查看索引和RDS數據庫診斷報告
我們可以直接查看表的索引狀態，並且結合RDS數據庫的診斷報告加上索引。大部分SQL都是索引加得不正確導致SQL執行緩慢。
mysql> show create table App_ChartAuditTrend;
+---------------------+--------------------------------------------------------------------+
| Table               | Create Table                                                       |
+---------------------+--------------------------------------------------------------------+
| App_ChartAuditTrend | CREATE TABLE `App_ChartAuditTrend` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `DomainName` varchar(30) DEFAULT NULL,
  `UpdateTime` datetime DEFAULT NULL,
  `Url` varchar(500) DEFAULT NULL,
  `AllNum` int(11) DEFAULT NULL,
  `IdentityNum` int(11) DEFAULT NULL,
  `CarNum` int(11) DEFAULT NULL,
  `PhoneNum` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 dbpartition by hash(`DomainName`)                     |
+---------------------+--------------------------------------------------------------------+
1 row in set (0.05 sec)
6.調整DRDS的SQL
DRDS層麵的SQL調優主要記住幾個原則。

1.盡量帶上拆分字段，減少數據的查詢範圍，等值、in、有限範圍都比不帶的要好。
2.盡可能帶上過濾條件或者有效的過濾條件將返回的數據量降低。
3.如果兩表join或者多表join, 嚐試對join的表進行位置互換(保持語義的情況下)。

7.理解DRDS的SQL主要會消耗在什麼地方以及調優
a.DRDS層麵除了需要做比較重的heap sort或者heap computer的時候會比較費時(從trace中能夠看到,並且絕大部分sql DRDS會避免此種計算)，其他大部分對sql的處理操作都不會占用太多資源和時間損耗，需要注意的時候，無論從RDS到DRDS返回大量數據進行merge還是從DRDS返回大量結果給客戶端，都會帶來比較大延遲和資源損耗,所以建議控製返回結果的大小。
b.如果從trace sql裏麵獲得的時間在RDS上消耗比較大，請首先觀察RDS的狀態，包括幾個大的指標iops,cpu,內存，以及mysql自身指標：連接數、鎖、buffer消耗等，然後請務必看RDS的診斷報告，大部分sql慢都是索引創建不合理導致,另外就是針對特別的sql進行參數調優,這個需要專業DBA介入，RDS有部分參數的修改界麵。
c.對於一些類分析的sql操作，建議能夠做結果或者中間結果緩存，mysql的存儲特性決定如果純粹在MySQL做所有業務操作，可能會導致連鎖反應，甚至原本很快的一個insert操作都會變得非常緩慢，所以嚐試減輕數據庫的負擔——減小比較重的sql操作的qps或者執行複雜度。
 最後更新：2016-11-23 18:56:53
  上一篇： SQL優化__最佳實踐_分布式關係型數據庫 DRDS-阿裏雲
  下一篇： DRDS慢SQL明細__開發手冊_分布式關係型數據庫 DRDS-阿裏雲
相關內容
 DeleteObject__關於Object操作_API 參考_對象存儲 OSS-阿裏雲
 獲取任務列表__SDK接口說明_Java版SDK_批量計算-阿裏雲
 LogShipper-狀態__Getting-Started_日誌服務-阿裏雲
 變更應用配置__應用管理_用戶指南_容器服務-阿裏雲
 添加APP錄製配置__直播流操作接口_API 手冊_CDN-阿裏雲
 虛擬主機被搜索引擎爬蟲訪問耗費大量流量解決方法___安全問題_技術分享_雲虛機主機-阿裏雲
 GetMachineGroup__Logtail機器組相關接口_API-Reference_日誌服務-阿裏雲
 轉換函數__函數_SQL語法參考_雲數據庫 OceanBase-阿裏雲
 如何開通ECS 8000 端口__創建ECS Pipeline_產品使用問題_數據集成-阿裏雲
 使用金融雲OSS__使用金融雲產品_金融雲-阿裏雲
熱門內容
 常見錯誤說明__附錄_大數據計算服務-阿裏雲
 發送短信接口__API使用手冊_短信服務-阿裏雲
 接口文檔__Android_安全組件教程_移動安全-阿裏雲
 運營商錯誤碼（聯通）__常見問題_短信服務-阿裏雲
 設置短信模板__使用手冊_短信服務-阿裏雲
 OSS 權限問題及排查__常見錯誤及排除_最佳實踐_對象存儲 OSS-阿裏雲
 消息通知__操作指南_批量計算-阿裏雲
 設備端快速接入(MQTT)__快速開始_阿裏雲物聯網套件-阿裏雲
 查詢API調用流量數據__API管理相關接口_API_API 網關-阿裏雲
 使用STS訪問__JavaScript-SDK_SDK 參考_對象存儲 OSS-阿裏雲
最新內容
 阿裏雲是什麼？深度解析阿裏雲的雲計算服務
 阿裏雲：中國領先的雲計算平台深度解析
 阿裏雲IoT平台設備數據獲取全攻略
 阿裏雲盤高效查看圖片的多種方法詳解
 阿裏雲工具雲深度解析：高效協同，加速雲上開發
 阿裏金融雲：深入解析其定位與服務
 阿裏雲智慧雲：解讀雲端智能化轉型利器
 阿裏雲資料高效打印指南：從控製台到本地文件
 阿裏雲OSS、ECS等多種場景下文件名打印詳解
 阿裏雲區塊鏈服務：技術、應用與未來展望