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

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-阿裏雲