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