阿里云分布式关系型数据库DRDS-开发手册

mysql> show datasources;
+------+---------+-----------------------+---------------+-------------------------------+-------+-------+------+------+-
-----+--------------+----------+--------------+---------------+
|
| 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... |
+------------+---------+---------------------+--------------+------------+--------------------------------------------------
mysql> show node; +------+---------------+-------------------+------------------+---------------------+--------------------+
2
分布式关系型数据库 DRDS/开发手册
| ID | NAME
分布式关系型数据库 DRDS 开发手册
分布式关系型数据库 DRDS/开发手册
开发手册
DRDS慢SQL查询
本文档主要描述如何通过连接DRDS服务排查慢SQL。 TIPS
1.建议通过mysql命令行进行连接，mysql -hip -Pport -uuser -ppassword -c,最后-c请务必带上，让 mysql客户端将注释下发 2.如果碰到命令报错，请执行select version()，并且工单反馈，由我们来帮你升级实例版本，老版本实例 可能存在部分指令异常
| ID | SCHEMA | NAME
| GROUP
| URL
| USER | TYPE | INIT | MIN | MAX |
IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT |
+------+---------+-----------------------+---------------+-------------------------------+-------+-------+------+------+-
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
| 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%
1.查看慢sql top10
如果我们只是想看看当前DRDS库中有没有慢SQL，可以尝试执行以下指令进行TOP 10慢SQL查询。这个慢查 询是DRDS层面的逻辑SQL, 可能对应到RDS执行的SQL会分发到多个库和表中去执行。
mysql> show slow;
+------------+---------+---------------------+--------------+------------+--------------------------------------------------
|
| 10.168.1.3 | test_db | 2015-10-13 15:37:34 | 955541 |
1 | SELECT count(*) AS `TotalCount` FROM
`App_CarCard` AS `i` INNE test_db | 2015-10-13 08:37:26 | 943829 |
------------------------------------+
XXX rows in set (0.22 sec)
如果已经有明确的慢SQL，直接跳到第二步。
1
分布式关系型数据库 DRDS/开发手册
2.查看DRDS执行计划
DRDS提供的explain指令执行结果会显示这个SQL具体在哪几个分片上执行，然后具体在数据节点上执行的 SQL是什么， 执行的策略是什么等等信息。我们将这些信息叫做DRDS的执行计划，这个信息是静态的，并没 有真正在数据库上执行。
|
| ... | ...
|
... |
... | 100%
| 0%
|
+------+---------------+-------------------+------------------+---------------------+--------------------+
XX rows in set (0.36 sec)
mysql> explain detail select * from App_ChartAuditTrend limit 1; +---------------+-------------------------------------------------------------------+ | TEST_0000_RDS | Merge as App_ChartAuditTrend
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
------------------------------------+
| HOST | SCHEMA | START_TIME
| EXECUTE_TIME | AFFECT_ROW | SQL
|
+------------+---------+---------------------+--------------+------------+--------------------------------------------------
... +---------------+-------------------------------------------------------------------+
里面所显示的XXXX_RDS代表数据分片，具体对应的库可以通过show node和show datasourcse两个命令得 到
------------------------------------+
| 10.168.1.2 | test_db | 2015-10-13 15:45:03 | 1049429 |
10 | SELECT * FROM `App_IdentityCard` AS `i` INNER
JOIN `App_Applicati...
`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...
Query from App_ChartAuditTrend as App_ChartAuditTrend limitFrom:0 limitTo:1 queryConcurrency:SEQUENTIAL columns:[App_ChartAuditTrend.Id, App_ChartAuditTrend.DomainName, App_ChartAuditTrend.UpdateTime,
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