本文汇总了PolarDB-X支持的SQL调优类语句。
- SHOW [FULL] SLOW [WHERE expr] [limit expr] 语句
- SHOW [FULL] PHYSICAL_SLOW [WHERE expr] [limit expr] 语句
- CLEAR SLOW 语句
- EXPLAIN SQL 语句
- EXPLAIN DETAIL SQL 语句
- EXPLAIN EXECUTE SQL 语句
- TRACE SQL 和 SHOW TRACE 语句
- CHECK TABLE tablename 语句
- SHOW TABLE STATUS 语句
1. SHOW [FULL] SLOW [WHERE expr] [limit expr]
执行时间超过 1 秒的 SQL 语句是慢 SQL,逻辑慢 SQL 是指应用发送到 PolarDB-X 的慢 SQL。关于如何排查慢SQL,详情请参见排查慢SQL。
-
SHOW SLOW
: 查看自 PolarDB-X 启动或者上次执行CLEAR SLOW
以来最慢的 100 条逻辑慢 SQL(注意,这里记录的是最慢的 100 个,缓存在 PolarDB-X 系统中,当实例重启或者执行CLEAR SLOW
时会丢失); -
SHOW FULL SLOW
: 查看实例启动以来记录的所有逻辑慢 SQL(持久化到 PolarDB-X 的内置数据库中)。该记录数有一个上限(具体数值跟购买的实例规格相关),PolarDB-X 会滚动删除比较老的慢 SQL 语句。实例的规格如果是 4C4G 的话,最多记录 10000 条慢 SQL 语句(包括逻辑慢 SQL 和物理慢 SQL);实例的规格如果是 8C8G 的话,最多记录 20000 条慢 SQL 语句(包括逻辑慢 SQL 和物理慢 SQL),其它规格依此类推。
重要列详解:
- HOST:来源 IP;
- START_TIME:执行开始时间;
- EXECUTE_TIME:执行时间;
- AFFECT_ROW:对于 DML 语句是影响行数;对于查询语句是返回的记录数。
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)
2. SHOW [FULL] PHYSICAL_SLOW [WHERE expr] [limit expr]
执行时间超过1秒的 SQL 语句是慢 SQL,物理慢 SQL 是指 PolarDB-X 发送到 RDS 的慢 SQL。关于如何排查慢SQL,详情请参见排查慢SQL。
-
SHOW PHYSICAL_SLOW
: 查看自 PolarDB-X 启动或者上次执行CLEAR SLOW
以来最慢的 100 条物理慢 SQL(注意,这里记录的是最慢的 100 个,缓存在 PolarDB-X 系统中,当实例重启或者执行CLEAR SLOW
时会丢失); -
SHOW FULL PHYSICAL_SLOW
: 查看实例启动以来记录的所有物理慢 SQL(持久化到 PolarDB-X 的内置数据库中)。该记录数有一个上限(具体数值跟购买的实例规格相关),PolarDB-X 会滚动删除比较老的慢 SQL 语句。实例的规格如果是 4C4G 的话,最多记录 10000 条慢 SQL 语句(包括逻辑慢 SQL 和物理慢 SQL);实例的规格如果是 8C8G 的话,最多记录 20000 条慢 SQL 语句(包括逻辑慢 SQL 和物理慢 SQL),其它规格依此类推。
重要列详解:
- GROUP_NAME:数据库分组;
- START_TIME:执行开始时间;
- EXECUTE_TIME:执行时间;
- AFFECT_ROW:对于 DML 语句是影响行数;对于查询语句是返回的记录数。
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)
3. CLEAR SLOW
清空自PolarDB-X启动或者上次执行CLEAR SLOW
以来最慢的100条逻辑慢SQL和最慢的100条物理慢 SQL。
SHOW SLOW
和SHOW PHYSICAL_SLOW
展示的是最慢的100个SQL,如果长时间未执行CLEAR SLOW
,可能都是非常老的SQL了。一般执行过SQL优化之后,建议都执行下CLEAR SLOW
,等待系统运行一段时间,再查看慢SQL的优化效果。
mysql> clear slow;
Query OK, 0 rows affected (0.00 sec)
4. EXPLAIN SQL
查看指定 SQL 在 PolarDB-X 层面的执行计划,注意这条 SQL 不会实际执行。关于该指令的更多案例,详情请参见SQL优化基本概念、SQL优化方法。
示例:
查看select * from doctest
这条 SQL 的执行计划(doctest 这张表是按照ID列进行分库的)。从执行计划可以看出该 SQL 会下推到每个分库,然后将执行结果聚合。
mysql> explain select * from doctest;
+------------------------------------------------+--------------------------------------+--------+
| GROUP_NAME | SQL | PARAMS |
+------------------------------------------------+--------------------------------------+--------+
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0000_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0002_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0003_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0004_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0005_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0006_RDS | select `doctest`.`id` from `doctest` | {} |
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0007_RDS | select `doctest`.`id` from `doctest` | {} |
+------------------------------------------------+--------------------------------------+--------+
8 rows in set (0.00 sec)
查看select * from doctest where id = 1
这条 SQL 的执行计划(doctest 这张表是按照ID列进行分库的)。从执行计划可以看出该 SQL 会根据拆分键(ID)计算出具体的分库,将 SQL 直接下推到该分库,然后执行结果聚合。
mysql> explain select * from doctest where id = 1;
+------------------------------------------------+-----------------------------------------------------------------+--------+
| GROUP_NAME | SQL | PARAMS |
+------------------------------------------------+-----------------------------------------------------------------+--------+
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | select `doctest`.`id` from `doctest` where (`doctest`.`id` = 1) | {} |
+------------------------------------------------+-----------------------------------------------------------------+--------+
1 row in set (0.01 sec)
5. EXPLAIN DETAIL SQL
查看指定 SQL 在 PolarDB-X 层面的执行计划。注意这条 SQL 不会实际执行。关于该指令的更多案例,详情请参见SQL优化基本概念、SQL优化方法。
mysql> explain detail select * from doctest where id = 1;
+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| GROUP_NAME | SQL | PARAMS |
+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | Query from doctest as doctest
keyFilter:doctest.id = 1
queryConcurrency:SEQUENTIAL
columns:[doctest.id]
tableName:doctest
executeOn:DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS
| NULL |
+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
1 row in set (0.02 sec)
6. EXPLAIN EXECUTE SQL
查看底层存储的执行计划,等同于 MYSQL 的 EXPLAIN 语句。关于该指令的更多案例,详情请参见SQL优化基本概念、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)
7. TRACE SQL 和 SHOW TRACE
查看具体 SQL 的执行情况。TRACE [SQL] 和 SHOW TRACE 要结合使用。注意 TRACE SQL 和 EXPLAIN SQL 的区别在于 TRACE SQL 会实际执行该语句。关于该指令的更多案例,详情请参见SQL优化基本概念、SQL优化方法。
例如查看 select 1
这条语句的执行情况。
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)
8. CHECK TABLE tablename
对数据表进行检查。主要用于 DDL 建表失败的情形,关于更多应用案例详情,请参见DDL常见问题处理。
- 对于拆分表,检查底层物理分表是否有缺失的情况,底层的物理分表的列和索引是否是一致;
- 对于单库单表,检查表是否存在。
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]
获取表的信息,该指令聚合了底层各个物理分表的数据。
重要列详解:
- NAME:表名称;
- ENGINE:表的存储引擎;
- VERSION:表的存储引擎的版本;
- ROW_FORMAT:行格式,主要是 Dynamic、Fixed、Compressed 这三种格式。动态(Dynamic)行的行长度可变,例如 VARCHAR 或 BLOB 类型字段;固定(Fixed)行是指行长度不变,例如CHAR和INTEGER类型字段;
- ROWS:表中的行数;
- AVG_ROW_LENGTH:平均每行包括的字节数;
- DATA_LENGTH:整个表的数据量(单位:字节);
- MAX_DATA_LENGTH:表可以容纳的最大数据量;
- INDEX_LENGTH:索引占用磁盘的空间大小 ;
- CREATE_TIME:表的创建时间;
- UPDATE_TIME:表的最近更新时间;
- COLLATION:表的默认字符集和字符排序规则;
- CREATE_OPTIONS:指表创建时的其他所有选项。
mysql> show table status like 'multi_db_multi_tbl';
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| 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 |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
| multi_db_multi_tbl | InnoDB | 10 | Compact | 2 | 16384 | 16384 | 0 | 16384 | 0 | 100000 | 2017-03-27 17:43:57.0 | NULL | NULL | utf8_general_ci | NULL | | |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.03 sec)
和 PolarDB-X 的 SCAN HINT 结合,还可以查看每个物理分表的数据量。详情请参见HINT语法。
mysql> /!TDDL:SCAN='multi_db_multi_tbl'*/show table status like 'multi_db_multi_tbl';
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| 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 | Block_format |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 16384 | 0 | 2 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
| multi_db_multi_tbl_0 | InnoDB | 10 | Compact | 1 | 16384 | 16384 | 0 | 16384 | 0 | 3 | 2017-03-27 17:43:57 | NULL | NULL | utf8_general_ci | NULL | | | Original |
+----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
16 rows in set (0.04 sec)