192
阿里云
DRDS控制指令__开发手册_分布式关系型数据库 DRDS-阿里云
为方便用户使用和维护DRDS ，DRDS 提供自己特有的辅助语句。
说明：[] 中的为用户提供的标识符，( ) 中为可选内容。此外，此文档适用于当前版本，如一些语句不可用，说明用户使用的版本过低。
1 一般帮助语句
1） SHOW HELP 语句
使用方式:
mysql> show help;+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+| STATEMENT | DESCRIPTION | EXAMPLE |+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+| show rule | Report all table rule | || show rule from TABLE | Report table rule | show rule from user || show full rule from TABLE | Report table full rule | show full rule from user || show topology from TABLE | Report table physical topology | show topology from user || show partitions from TABLE | Report table dbPartition or tbPartition columns | show partitions from user || show broadcasts | Report all broadcast tables | || show datasources | Report all partition db threadPool info | || show node | Report master/slave read status | || show slow | Report top 100 slow sql | || show physical_slow | Report top 100 physical slow sql | || clear slow | Clear slow data | || trace SQL | Start trace sql, use show trace to print profiling data | trace select count(*) from user; show trace || show trace | Report sql execute profiling info | || explain SQL | Report sql plan info | explain select count(*) from user || explain detail SQL | Report sql detail plan info | explain detail select count(*) from user || explain execute SQL | Report sql on physical db plan info | explain execute select count(*) from user || show sequences | Report all sequences status | || create sequence NAME [start with COUNT] | Create sequence | create sequence test start with 0 || alter sequence NAME [start with COUNT] | Alter sequence | alter sequence test start with 100000 || drop sequence NAME | Drop sequence | drop sequence test |+-----------------------------------------+---------------------------------------------------------+---------------------------------------------+20 rows in set (0.00 sec)
使用场景：
对于不清楚DRDS 提供的维护SQL，可以通过此语句获取。
使用说明：
SHOW HELP 内的各个SQL，会在后面具体说明。
2 规则查看、节点拓扑语句
1） SHOW RULE 语句
使用方式：
mysql> show rule ;+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | barbecue_acc | 0 | id | hash | 4 | id | hash | 16 || 1 | ljh_example | 0 | | NULL | 1 | | NULL | 1 || 2 | ljh_test | 0 | id | hash | 4 | id | hash | 2 || 3 | tddl88_users | 0 | school_id | hash | 4 | | NULL | 1 || 4 | tddl8_users | 0 | school_id | hash | 4 | | NULL | 1 || 5 | tddl_mgr_log | 0 | host | hash | 4 | host | hash | 16 || 6 | usermfrdtbl | 0 | uid | hash | 4 | | NULL | 1 |+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+7 rows in set (2.80 sec)
使用场景：
查看对应逻辑库下，使用分库分表的表所采用的路由规则。
使用说明：
所有采用分表的逻辑表路由规则查看，包含是否为广播表，分库分表方案，分库分表数等信息。
2） SHOW RULE FROM [tablename]
使用方式：
mysql> show rule from tddl_mgr_log;+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| ID | TABLE_NAME | BROADCAST | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT |+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+| 0 | tddl_mgr_log | 0 | host | hash | 4 | host | hash | 16 |+------+--------------+-----------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+1 row in set (0.46 sec)
使用场景：
查看对应逻辑库下，表的分库分表规则。
使用说明：
类似 SHOW RULE，但是可以查看所有表的信息，不单单是分库分表的表。
3） SHOW FULL RULE FROM [tablename]
使用方式：
mysql> show full rule from tddl_mgr_log;+------+--------------+-----------+------------+-----------------------+------------------+--------------------------------------------------------------+-------------------+---------------------------------------------------+----------------+------------------+| ID | TABLE_NAME | BROADCAST | JOIN_GROUP | ALLOW_FULL_TABLE_SCAN | DB_NAME_PATTERN | DB_RULES_STR | TB_NAME_PATTERN | TB_RULES_STR | PARTITION_KEYS | DEFAULT_DB_INDEX |+------+--------------+-----------+------------+-----------------------+------------------+--------------------------------------------------------------+-------------------+---------------------------------------------------+----------------+------------------+| 0 | tddl_mgr_log | 0 | NULL | 1 | TDDL5_{00}_GROUP | ((#host,1,64#).hashCode().abs().longValue() % 64).intdiv(16) | tddl_mgr_log_{00} | ((#host,1,64#).hashCode().abs().longValue() % 64) | [host] | TDDL5_00_GROUP |+------+--------------+-----------+------------+-----------------------+------------------+--------------------------------------------------------------+-------------------+---------------------------------------------------+----------------+------------------+1 row in set (0.00 sec)
使用场景：
需要获取更多规则信息，如可否全分表扫描，分库、分表具体规则等。
使用说明：
比SHOW RULE 更加详细，包含是否可全表扫描，分库、分表具体规则，但对于非分库分表的表，无法获取对应信息。
4） SHOW TOPOLOGY FROM [tablename]
使用方式：
mysql> show topology from tddl_mgr_log;+------+----------------+-----------------+| ID | GROUP_NAME | TABLE_NAME |+------+----------------+-----------------+| 0 | TDDL5_00_GROUP | tddl_mgr_log_00 || 1 | TDDL5_00_GROUP | tddl_mgr_log_01 || 2 | TDDL5_00_GROUP | tddl_mgr_log_02 || 3 | TDDL5_00_GROUP | tddl_mgr_log_03 || 4 | TDDL5_00_GROUP | tddl_mgr_log_04 || 5 | TDDL5_00_GROUP | tddl_mgr_log_05 || 6 | TDDL5_00_GROUP | tddl_mgr_log_06 || 7 | TDDL5_00_GROUP | tddl_mgr_log_07 || 8 | TDDL5_00_GROUP | tddl_mgr_log_08 || 9 | TDDL5_00_GROUP | tddl_mgr_log_09 || 10 | TDDL5_00_GROUP | tddl_mgr_log_10 || 11 | TDDL5_00_GROUP | tddl_mgr_log_11 || 12 | TDDL5_00_GROUP | tddl_mgr_log_12 || 13 | TDDL5_00_GROUP | tddl_mgr_log_13 || 14 | TDDL5_00_GROUP | tddl_mgr_log_14 || 15 | TDDL5_00_GROUP | tddl_mgr_log_15 || 16 | TDDL5_01_GROUP | tddl_mgr_log_16 || 17 | TDDL5_01_GROUP | tddl_mgr_log_17 || 18 | TDDL5_01_GROUP | tddl_mgr_log_18 || 19 | TDDL5_01_GROUP | tddl_mgr_log_19 || 20 | TDDL5_01_GROUP | tddl_mgr_log_20 || 21 | TDDL5_01_GROUP | tddl_mgr_log_21 || 22 | TDDL5_01_GROUP | tddl_mgr_log_22 || 23 | TDDL5_01_GROUP | tddl_mgr_log_23 || 24 | TDDL5_01_GROUP | tddl_mgr_log_24 || 25 | TDDL5_01_GROUP | tddl_mgr_log_25 || 26 | TDDL5_01_GROUP | tddl_mgr_log_26 || 27 | TDDL5_01_GROUP | tddl_mgr_log_27 || 28 | TDDL5_01_GROUP | tddl_mgr_log_28 || 29 | TDDL5_01_GROUP | tddl_mgr_log_29 || 30 | TDDL5_01_GROUP | tddl_mgr_log_30 || 31 | TDDL5_01_GROUP | tddl_mgr_log_31 || 32 | TDDL5_02_GROUP | tddl_mgr_log_32 || 33 | TDDL5_02_GROUP | tddl_mgr_log_33 || 34 | TDDL5_02_GROUP | tddl_mgr_log_34 || 35 | TDDL5_02_GROUP | tddl_mgr_log_35 || 36 | TDDL5_02_GROUP | tddl_mgr_log_36 || 37 | TDDL5_02_GROUP | tddl_mgr_log_37 || 38 | TDDL5_02_GROUP | tddl_mgr_log_38 || 39 | TDDL5_02_GROUP | tddl_mgr_log_39 || 40 | TDDL5_02_GROUP | tddl_mgr_log_40 || 41 | TDDL5_02_GROUP | tddl_mgr_log_41 || 42 | TDDL5_02_GROUP | tddl_mgr_log_42 || 43 | TDDL5_02_GROUP | tddl_mgr_log_43 || 44 | TDDL5_02_GROUP | tddl_mgr_log_44 || 45 | TDDL5_02_GROUP | tddl_mgr_log_45 || 46 | TDDL5_02_GROUP | tddl_mgr_log_46 || 47 | TDDL5_02_GROUP | tddl_mgr_log_47 || 48 | TDDL5_03_GROUP | tddl_mgr_log_48 || 49 | TDDL5_03_GROUP | tddl_mgr_log_49 || 50 | TDDL5_03_GROUP | tddl_mgr_log_50 || 51 | TDDL5_03_GROUP | tddl_mgr_log_51 || 52 | TDDL5_03_GROUP | tddl_mgr_log_52 || 53 | TDDL5_03_GROUP | tddl_mgr_log_53 || 54 | TDDL5_03_GROUP | tddl_mgr_log_54 || 55 | TDDL5_03_GROUP | tddl_mgr_log_55 || 56 | TDDL5_03_GROUP | tddl_mgr_log_56 || 57 | TDDL5_03_GROUP | tddl_mgr_log_57 || 58 | TDDL5_03_GROUP | tddl_mgr_log_58 || 59 | TDDL5_03_GROUP | tddl_mgr_log_59 || 60 | TDDL5_03_GROUP | tddl_mgr_log_60 || 61 | TDDL5_03_GROUP | tddl_mgr_log_61 || 62 | TDDL5_03_GROUP | tddl_mgr_log_62 || 63 | TDDL5_03_GROUP | tddl_mgr_log_63 |+------+----------------+-----------------+64 rows in set (0.06 sec)
使用场景：
查看分库与分表对应关系。
使用说明：
针对所有的逻辑表，即包含分表的逻辑表，也包含不分表的逻辑表。
5） SHOW PARTITIONS FROM [tablename]
使用方式：
mysql> show partitions from tddl_mgr_log;+------+| KEYS |+------+| host |+------+1 row in set (0.00 sec)
使用场景：
查看分库分表键。
使用说明：
针对所有逻辑表。
6） SHOW BROADCASTS
使用方式：
mysql> show broadcasts;
使用场景：
查看广播表信息。
使用说明：
7） SHOW DATASOURCES
使用方式：
mysql> show datasources;+------+-----------+-------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+-----------------------------------+-------------+--------------+| ID | SCHEMA | NAME | GROUP | URL | USER | TYPE | INIT | MIN | MAX | IDLE_TIMEOUT | MAX_WAIT | ACTIVE_COUNT | POOLING_COUNT | ATOM | READ_WEIGHT | WRITE_WEIGHT |+------+-----------+-------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+-----------------------------------+-------------+--------------+| 0 | TDDL5_APP | db218249098_sqa_zmf_tddl5_00_3309_1 | TDDL5_00_GROUP | jdbc:mysql://10.218.249.98:3309/tddl5_00?autoReconnect=true&connectTimeout=1000&rewriteBatchedStatements=true&allowMultiQueries=true&socketTimeout=12000&useServerPrepStmts=false&netTimeoutForStreamingResults=0&failOverReadOnly=false&readOnlyPropagatesToServer=false&characterEncoding=utf8 | tddl5 | mysql | 0 | 3 | 30 | 1 | 1000 | 0 | 1 | db218249098_sqa_zmf_tddl5_00_3309 | 10 | 10 || 1 | TDDL5_APP | db218249098_sqa_zmf_tddl5_01_3309_2 | TDDL5_01_GROUP | jdbc:mysql://10.218.249.98:3309/tddl5_01?autoReconnect=true&connectTimeout=1000&rewriteBatchedStatements=true&allowMultiQueries=true&socketTimeout=12000&useServerPrepStmts=false&netTimeoutForStreamingResults=0&failOverReadOnly=false&readOnlyPropagatesToServer=false&characterEncoding=utf8 | tddl5 | mysql | 0 | 1 | 30 | 1 | 1000 | 0 | 0 | db218249098_sqa_zmf_tddl5_01_3309 | 10 | 10 || 2 | TDDL5_APP | db218249098_sqa_zmf_tddl5_02_3309_3 | TDDL5_02_GROUP | jdbc:mysql://10.218.249.98:3309/tddl5_02?autoReconnect=true&connectTimeout=1000&rewriteBatchedStatements=true&allowMultiQueries=true&socketTimeout=12000&useServerPrepStmts=false&netTimeoutForStreamingResults=0&failOverReadOnly=false&readOnlyPropagatesToServer=false&characterEncoding=utf8 | tddl5 | mysql | 0 | 1 | 30 | 1 | 1000 | 0 | 0 | db218249098_sqa_zmf_tddl5_02_3309 | 10 | 10 || 3 | TDDL5_APP | db218249098_sqa_zmf_tddl5_03_3309_4 | TDDL5_03_GROUP | jdbc:mysql://10.218.249.98:3309/tddl5_03?autoReconnect=true&connectTimeout=1000&rewriteBatchedStatements=true&allowMultiQueries=true&socketTimeout=12000&useServerPrepStmts=false&netTimeoutForStreamingResults=0&failOverReadOnly=false&readOnlyPropagatesToServer=false&characterEncoding=utf8 | tddl5 | mysql | 0 | 1 | 30 | 1 | 1000 | 0 | 0 | db218249098_sqa_zmf_tddl5_03_3309 | 10 | 10 |+------+-----------+-------------------------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+-------+------+------+------+--------------+----------+--------------+---------------+-----------------------------------+-------------+--------------+4 rows in set (0.00 sec)
使用场景：
查看底层存储信息。
使用说明：
查看底层存储信息，包含schema，数据库分组名，JDBC 信息，用户名，底层存储类型，读写权重等。
8） SHOW NODE
使用方式：
mysql> show node;+------+----------------+-------------------+------------------+---------------------+--------------------+| ID | NAME | MASTER_READ_COUNT | SLAVE_READ_COUNT | MASTER_READ_PERCENT | SLAVE_READ_PERCENT |+------+----------------+-------------------+------------------+---------------------+--------------------+| 0 | TDDL5_00_GROUP | 7 | 0 | 100% | 0% || 1 | TDDL5_01_GROUP | 0 | 0 | 0% | 0% || 2 | TDDL5_02_GROUP | 0 | 0 | 0% | 0% || 3 | TDDL5_03_GROUP | 0 | 0 | 0% | 0% |+------+----------------+-------------------+------------------+---------------------+--------------------+4 rows in set (0.05 sec)
使用场景：
查看物理库的读写信息。
使用说明：
3 SQL 调优语句
1) SHOW SLOW (WHERE)
使用方式：
mysql> show slow;mysql> show slow where execute_time >1000 limit 1;+-----------+---------------------+--------------+------------+-----------+| HOST | START_TIME | EXECUTE_TIME | AFFECT_ROW | SQL |+-----------+---------------------+--------------+------------+-----------+| 127.0.0.1 | 2016-03-16 13:02:57 | 2785 | 7 | show rule |+-----------+---------------------+--------------+------------+-----------+1 row in set (0.02 sec)
使用场景：
查看最近的100条DRDS 慢SQL。
使用说明：
包含DRDS 执行IP,具体慢SQL，和执行时间,单位为毫秒,执行时间超过 1秒的SQL语句会被记录为慢SQL。此外支持添加WHERE 条件和LIMIT 限制。
2) SHOW PHYSICAL_SLOW (WHERE)
使用方式：
mysql> show physical_slow;+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+| GROUP_NAME | DBKEY_NAME | START_TIME | EXECUTE_TIME | SQL_EXECUTE_TIME | GETLOCK_CONNECTION_TIME | CREATE_CONNECTION_TIME | AFFECT_ROW | SQL |+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+| TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 2016-03-16 13:05:38 | 1057 | 1011 | 0 | 0 | 1 | select sleep(1) |+----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+1 row in set (0.01 sec)
使用场景：
查看对应底层存储的最近100条慢SQL。
使用说明：
SHOW SLOW 与 SHOW PHYSICAL_SLOW 的区别是针对的层次不一样，一个是DRDS 层级，一个是物理存储层级。 包含物理库分组名，DBKEY等内部信息）。执行时间单位为毫秒，执行时间超过 1秒的SQL语句会被记录为慢SQL。注意其中的下划线。此外支持添加WHERE 条件和LIMIT 限制。
3) CLEAR SLOW
使用方式：
mysql> clear slow;Query OK, 0 rows affected (0.00 sec)
使用场景：
清理慢SQL 信息。
使用说明：
针对所有慢SQL信息，DRDS 和底层存储的都会清除。
4) TRACE [ SQL] 和SHOW TRACE
使用方式：
mysql> trace select 1;+---+| 1 |+---+| 1 |+---+1 row in set (0.03 sec)mysql> show trace;+------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+| ID | TYPE | GROUP_NAME | DBKEY_NAME | TIME_COST(MS) | CONNECTION_TIME_COST(MS) | ROWS | STATEMENT | PARAMS |+------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+| 0 | Optimize | DRDS | DRDS | 3 | 0.00 | 0 | select 1 | NULL || 1 | Query | TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 7 | 0.15 | 1 | select 1 | NULL |+------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+2 rows in set (0.01 sec)
使用场景：
查看具体SQL 的执行情况。
使用说明：
TRACE [SQL] 和SHOW TRACE 要结合使用，对分表与不分表都可以使用。包含数据库分组，物理SQL 等信息。
5) EXPLAIN [SQL]
使用方式：
mysql> explain select * from ab;+----------------+------------------+--------+| GROUP_NAME | SQL | PARAMS |+----------------+------------------+--------+| TDDL5_00_GROUP | select * from ab | {} |+----------------+------------------+--------+1 row in set (0.04 sec)
使用场景：
查看语句对应的分库，物理语句，和整体参数。
使用说明：
不同于底层存储的EXPLAIN 语句，要实现底层存储EXPLAIN 功能，要使用EXPLAIN EXECUTE [SQL] 语句。
6) EXPLAIN DETAIL [SQL]
使用方式：
mysql> explain detail select * from ab;+----------------+-----------------------------------------------------------------------------------------------+--------+| GROUP_NAME | SQL | PARAMS |+----------------+-----------------------------------------------------------------------------------------------+--------+| TDDL5_00_GROUP | QueryqueryConcurrency:SEQUENTIALsql: select * from abexecuteOn:TDDL5_00_GROUP| NULL |+----------------+-----------------------------------------------------------------------------------------------+--------+1 row in set (0.03 sec)
使用场景：
查询语句的DRDS 执行信息。
使用说明：
针对所有SQL，返回信息包含执行语句类型，并发程度，返回列信息，物理表，库分组等。
7) EXPLAIN EXECUTE [SQL]
使用方式：
mysql> explain execute select * from tddl_mgr_log limit 1;+----+-------------+--------------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tddl_mgr_log | ALL | NULL | NULL | NULL | NULL | 1 | NULL |+----+-------------+--------------+------+---------------+------+---------+------+------+-------+1 row in set (0.07 sec)
使用场景：
查看底层存储的执行计划。
使用说明：
等同于MYSQL 的EXPLAIN 语句。
8) CHECK TABLE [name]
使用方式：
mysql> check table tddl_mgr_log;+------------------------+-------+----------+----------+| TABLE | OP | MSG_TYPE | MSG_TEXT |+------------------------+-------+----------+----------+| TDDL5_APP.tddl_mgr_log | check | status | OK |+------------------------+-------+----------+----------+1 row in set (0.56 sec)mysql> check table tddl_mg;+-------------------+-------+----------+----------------------------------------+| TABLE | OP | MSG_TYPE | MSG_TEXT |+-------------------+-------+----------+----------------------------------------+| TDDL5_APP.tddl_mg | check | Error | Table 'tddl5_00.tddl_mg' doesn't exist |+-------------------+-------+----------+----------------------------------------+1 row in set (0.02 sec)
使用场景：
对数据表进行检查。
使用说明：
对于分表，检查底层表的一致性和完整性。对于所有表，检查表的存在性。
9) SHOW TABLE STATUS (LIKE ‘pattern’ | WHERE expr)
使用方式：
mysql> /*TDDL:scan='tddl5_users'*/show table status like 'tddl5_users';+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| tddl5_users_07 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_05 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_06 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_04 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_01 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 07:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_02 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 07:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_00 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 07:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_03 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 07:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_12 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_13 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_14 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_15 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:28 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_08 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_09 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_10 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx || tddl5_users_11 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 100000000193 | 2015-11-06 15:20:27 | NULL | NULL | utf8_general_ci | NULL | | xxx |+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+16 rows in set (0.08 sec)
使用场景：
对数据表进行检查。
使用说明：
该数据聚合了各个分片的数据。和DRDS的scan hint结合，还可以查看每个分片的数据量（注意，如果用mysql命令行，启动时需要加上-c参数，否则hint会被mysql命令行过滤掉），使用方式如上。
4 统计信息查询
1) SHOW STATS
使用方式：
mysql> show stats;+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+| QPS | RDS_QPS | SLOW_QPS | PHYSICAL_SLOW_QPS | ERROR_PER_SECOND | MERGE_QUERY_PER_SECOND | ACTIVE_CONNECTIONS | RT(MS) | RDS_RT(MS) | NET_IN(KB/S) | NET_OUT(KB/S) | THREAD_RUNNING |+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1 | 0.00 | 0.00 | 0.00 | 0.00 | 1 |+------+---------+----------+-------------------+------------------+------------------------+--------------------+--------+------------+--------------+---------------+----------------+1 row in set (0.00 sec)
使用场景：
查看整体的统计信息。
使用说明：
主要包含QPS 、RT、慢SQL统计 等信息，具体说明如下：QPS: 0.00 逻辑QPSRDS_QPS: 0.00 物理QPSERROR_PER_SECOND: 0.00 每秒的错误数，包含语法错误，主键冲突等等所有异常VIOLATION_PER_SECOND: 0.00 每秒的主键或者唯一键冲突MERGE_QUERY_PER_SECCOND: 0.00 通过分库分表，从多表中进行的查询ACTIVE_CONNECTIONS: 1 当前连接上的连接CONNECTION_CREATE_PER_SECCOND: 0.00 每秒创建的连接数RT(MS): 0.00 逻辑RT（响应时间）RDS_RT(MS): 0.00 物理RTNET_IN(KB/S): 0.00 DRDS收到的网络流量NET_OUT(KB/S): 0.00 DRDS发送的网络流量THREAD_RUNNING: 1 正在运行的线程数HINT_USED_PER_SECOND: 0.00 每秒带HINT的查询的数量HINT_USED_COUNT: 302 启动到现在带HINT的查询 总量AGGREGATE_QUERY_PER_SECCOND: 0.00 每秒走AGGREGATECURSOR的次数AGGREGATE_QUERY_COUNT: 17 启动到现在走AGGREGATECURSOR的次数TEMP_TABLE_CREATE_PER_SECCOND: 0.00 每秒创建的临时表的数量TEMP_TABLE_CREATE_COUNT: 6 启动到现在创建的临时表总数量MULTI_DB_JOIN_PER_SECCOND: 0.00 每秒跨库JOIN的数量MULTI_DB_JOIN_COUNT: 3 启动到现在跨库JOIN的总量
5 DRDS SEQUENCE 相关
1） SHOW SEQUENCES
使用方式：
mysql> show sequences;+----+----------------------------+----------+| id | name | value |+----+----------------------------+----------+| 1 | AUTO_SEQ_tddl5_users | 4800810 || 2 | AUTO_SEQ_tddl_users_search | 3600000 || 3 | aaa | 100000 || 4 | AUTO_SEQ_tddl5_users1 | 400100 || 5 | AUTO_SEQ_tddl_category | 200000 || 6 | aaaaa | 41677000 || 7 | xxxx | 0 || 12 | aa | 1119000 || 13 | test_sequence | 8130000 || 14 | test_sequence_wap | 0 || 15 | test_sequence_zongfei | 2000 || 16 | test_sequence_zongfei_new | 0 || 17 | test_sequence_zongfei_new1 | 0 || 18 | seq_flow_charge_order_new | 0 || 19 | AUTO_SEQ_tddl7_users | 300 || 20 | AUTO_SEQ_tddl8_users | 200000 || 21 | AUTO_SEQ_tddl111_users | 400000 || 22 | AUTO_SEQ_tddl12_users | 500000 || 23 | ae_ajaxone_rule | 0 || 24 | AUTO_SEQ_sequence | 0 || 25 | AUTO_SEQ_drds_users1 | 100000 || 26 | AUTO_SEQ_drds_users | 5300000 || 27 | dome_sequence | 0 || 28 | NULL | 0 || 29 | NULL | 0 || 30 | NULL | 0 || 31 | AUTO_SEQ_tddl_mgr_log | 800000 || 32 | AUTO_SEQ_barbecue_acc | 0 || 33 | AUTO_SEQ_ljh_test | 100000 || 34 | AUTO_SEQ_tddl88_users | 500000 || 35 | AUTO_SEQ_usermfrdtbl | 0 || 40 | teat1 | 100000 |+----+----------------------------+----------+32 rows in set (0.07 sec)
使用场景：
查看所有的DRDS SEQUENCE信息。
使用说明：
DRDS 提供了全局唯一的序列功能，通过SHOW SEQUENCE可以查看当前所有SEQUENCE信息，包含SEQUENCE 名称和当前值。
2） CREATE SEQUENCE [name] (START WITH [count] | [count])
使用方式：
mysql> create sequence test;Query OK, 1 row affected (0.42 sec)
使用场景：
建立一个SEQUENCE。
使用说明：
如果不提供初始值，默认当前值为0。对于sequence的理解，可以类比ORACLE 数据库的sequence 概念。START WITH 可以指定SEQUENCE 当前值，可选。
3） ALTER SEQUENCE [name] START WITH [count] | [count]
使用方式：
mysql> alter sequence test start with 100;Query OK, 2 rows affected (0.01 sec)
使用场景：
修改SEQUENCE 的当前值。
使用说明：
4） DROP SEQUENCE [name]
使用方式：
mysql> drop sequence test;Query OK, 1 row affected (0.01 sec)
使用场景：
删除对应名称的SEQUENCE。
使用说明：
最后更新：2016-11-23 18:56:58
上一篇： DRDS自定义注释__开发手册_分布式关系型数据库 DRDS-阿里云
下一篇： DRDS分库分表__开发手册_分布式关系型数据库 DRDS-阿里云
- 系统默认应用列表__应用管理_用户指南_容器服务-阿里云
- 用户签名验证__访问控制_API 参考_对象存储 OSS-阿里云
- 性能测试中级功能__中级课程_性能测试视频教程_性能测试-阿里云
- 查询域名配置__配置操作接口_API 手册_CDN-阿里云
- 阿里云与政府打造城市大脑，120一呼即到的日子来了
- 获取产品类型信息__接口列表_服务器端API_阿里云物联网套件-阿里云
- 删除标签__标签_用户指南_云服务器 ECS-阿里云
- OptionObject__跨域资源共享_API 参考_对象存储 OSS-阿里云
- 版本历史__产品简介_Quick BI-阿里云
- 测试指标__性能测试技术体系_性能测试体系_性能测试-阿里云
相关内容
- 常见错误说明__附录_大数据计算服务-阿里云
- 发送短信接口__API使用手册_短信服务-阿里云
- 接口文档__Android_安全组件教程_移动安全-阿里云
- 运营商错误码（联通）__常见问题_短信服务-阿里云
- 设置短信模板__使用手册_短信服务-阿里云
- OSS 权限问题及排查__常见错误及排除_最佳实践_对象存储 OSS-阿里云
- 消息通知__操作指南_批量计算-阿里云
- 设备端快速接入(MQTT)__快速开始_阿里云物联网套件-阿里云
- 查询API调用流量数据__API管理相关接口_API_API 网关-阿里云
- 使用STS访问__JavaScript-SDK_SDK 参考_对象存储 OSS-阿里云