排查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-阿里云
最新内容
 阿里云盘多人共享及同步观看详解：轻松实现家庭影院和团队协作
 阿里云在阿里巴巴生态系统中的核心地位及战略意义
 华为云与阿里云赚钱模式深度解析
 阿里云盘订阅内容的高效搜索技巧
 阿里云数据盘快速上手指南：购买、挂载及常见问题详解
 阿里云域名解析及文档上传详解：新手入门到高级技巧
 阿里云账单记录彻底删除？详解账单管理及数据安全
 阿里云电脑账户激活及常见问题详解
 阿里云部署API接口：从入门到实践指南
 阿里云账号下如何管理和修改公司名称及相关信息