全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
分布式关系型数据库 DRDS

SQL 调优类语句

更新时间:2017-08-14 14:42:15

SQL 调优类语句如下:

1. SHOW [FULL] SLOW [WHERE expr] [limit expr]

执行时间超过 1 秒的 SQL 语句是慢 SQL,逻辑慢 SQL 是指应用发送到 DRDS 的慢 SQL。如何排查慢 SQL 可以参考文档 排查 DRDS 慢 SQL

  • SHOW SLOW: 查看自 DRDS 启动或者上次执行CLEAR SLOW以来最慢的 100 条逻辑慢 SQL(注意,这里记录的是最慢的 100 个,缓存在 DRDS 系统中,当实例重启或者执行 CLEAR SLOW 时会丢失);
  • SHOW FULL SLOW: 查看实例启动以来记录的所有逻辑慢 SQL(持久化到 DRDS 的内置数据库中)。该记录数有一个上限(具体数值跟购买的实例规格相关),DRDS 会滚动删除比较老的慢 SQL 语句。实例的规格如果是 4C4G 的话,最多记录 10000 条慢 SQL 语句(包括逻辑慢 SQL 和 物理慢 SQL);实例的规格如果是 8C8G 的话,最多记录 20000 条慢 SQL 语句(包括逻辑慢 SQL 和 物理慢 SQL),其它规格依此类推。

重要列详解:

  • HOST:来源 IP;
  • START_TIME:执行开始时间;
  • EXECUTE_TIME:执行时间;
  • AFFECT_ROW:对于 DML 语句是影响行数;对于查询语句是返回的记录数。
  1. mysql> show slow where execute_time > 1000 limit 1;
  2. +-----------+---------------------+--------------+------------+-----------+
  3. | HOST | START_TIME | EXECUTE_TIME | AFFECT_ROW | SQL |
  4. +-----------+---------------------+--------------+------------+-----------+
  5. | 127.0.0.1 | 2016-03-16 13:02:57 | 2785 | 7 | show rule |
  6. +-----------+---------------------+--------------+------------+-----------+
  7. 1 row in set (0.02 sec)

2. SHOW [FULL] PHYSICAL_SLOW [WHERE expr] [limit expr]

执行时间超过1秒的 SQL 语句是慢 SQL,物理慢 SQL 是指 DRDS 发送到 RDS 的慢 SQL。如何排查慢 SQL 可以参考文档 排查 DRDS 慢 SQL

  • SHOW PHYSICAL_SLOW: 查看自 DRDS 启动或者上次执行CLEAR SLOW以来最慢的 100 条物理慢 SQL(注意,这里记录的是最慢的 100 个,缓存在 DRDS 系统中,当实例重启或者执行 CLEAR SLOW 时会丢失);
  • SHOW FULL PHYSICAL_SLOW: 查看实例启动以来记录的所有物理慢 SQL(持久化到 DRDS 的内置数据库中)。该记录数有一个上限(具体数值跟购买的实例规格相关),DRDS 会滚动删除比较老的慢 SQL 语句。实例的规格如果是 4C4G 的话,最多记录 10000 条慢 SQL 语句(包括逻辑慢 SQL 和 物理慢 SQL);实例的规格如果是 8C8G 的话,最多记录 20000 条慢 SQL 语句(包括逻辑慢 SQL 和 物理慢 SQL),其它规格依此类推。

重要列详解:

  • GROUP_NAME:数据库分组;
  • START_TIME:执行开始时间;
  • EXECUTE_TIME:执行时间;
  • AFFECT_ROW:对于 DML 语句是影响行数;对于查询语句是返回的记录数。
  1. mysql> show physical_slow;
  2. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  3. | GROUP_NAME | DBKEY_NAME | START_TIME | EXECUTE_TIME | SQL_EXECUTE_TIME | GETLOCK_CONNECTION_TIME | CREATE_CONNECTION_TIME | AFFECT_ROW | SQL |
  4. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  5. | TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 2016-03-16 13:05:38 | 1057 | 1011 | 0 | 0 | 1 | select sleep(1) |
  6. +----------------+-----------------------------------+---------------------+--------------+------------------+-------------------------+------------------------+------------+-----------------+
  7. 1 row in set (0.01 sec)

3. CLEAR SLOW

清空自 DRDS 启动或者上次执行CLEAR SLOW以来最慢的 100 条逻辑慢 SQL 和 最慢的 100 条物理慢 SQL。注意:SHOW SLOWSHOW PHYSICAL_SLOW展示的是最慢的 100 个 SQL,如果长时间未执行CLEAR SLOW,可能都是非常老的 SQL 了,一般执行过 SQL 优化之后,建议都执行下CLEAR SLOW,等待系统运行一段时间,再查看下慢 SQL 的优化效果。

  1. mysql> clear slow;
  2. Query OK, 0 rows affected (0.00 sec)

4. EXPLAIN SQL

查看指定 SQL 在 DRDS 层面的执行计划,注意这条 SQL 不会实际执行。关于该指令的更多案例请参考文档 SQL 优化基本概念SQL 优化方法

示例:

查看select * from doctest这条 SQL 的执行计划(doctest 这张表是按照 id 列进行分库的)。从执行计划可以看出该 SQL 会下推到每个分库,然后将执行结果聚合。

  1. mysql> explain select * from doctest;
  2. +------------------------------------------------+--------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+--------------------------------------+--------+
  5. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0000_RDS | select `doctest`.`id` from `doctest` | {} |
  6. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | select `doctest`.`id` from `doctest` | {} |
  7. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0002_RDS | select `doctest`.`id` from `doctest` | {} |
  8. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0003_RDS | select `doctest`.`id` from `doctest` | {} |
  9. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0004_RDS | select `doctest`.`id` from `doctest` | {} |
  10. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0005_RDS | select `doctest`.`id` from `doctest` | {} |
  11. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0006_RDS | select `doctest`.`id` from `doctest` | {} |
  12. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0007_RDS | select `doctest`.`id` from `doctest` | {} |
  13. +------------------------------------------------+--------------------------------------+--------+
  14. 8 rows in set (0.00 sec)

查看select * from doctest where id = 1这条 SQL 的执行计划(doctest 这张表是按照 id 列进行分库的)。从执行计划可以看出该 SQL 会根据拆分键(id)计算出具体的分库,将 SQL 直接下推到该分库,然后执行结果聚合。

  1. mysql> explain select * from doctest where id = 1;
  2. +------------------------------------------------+-----------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+-----------------------------------------------------------------+--------+
  5. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | select `doctest`.`id` from `doctest` where (`doctest`.`id` = 1) | {} |
  6. +------------------------------------------------+-----------------------------------------------------------------+--------+
  7. 1 row in set (0.01 sec)

5. EXPLAIN DETAIL SQL

查看指定 SQL 在 DRDS 层面的执行计划。注意这条 SQL 不会实际执行。关于该指令的更多案例请参考文档 SQL 优化基本概念SQL 优化方法

  1. mysql> explain detail select * from doctest where id = 1;
  2. +------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  3. | GROUP_NAME | SQL | PARAMS |
  4. +------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  5. | DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS | Query from doctest as doctest
  6. keyFilter:doctest.id = 1
  7. queryConcurrency:SEQUENTIAL
  8. columns:[doctest.id]
  9. tableName:doctest
  10. executeOn:DOCTEST_1488704345426RCUPDOCTEST_CAET_0001_RDS
  11. | NULL |
  12. +------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
  13. 1 row in set (0.02 sec)

6. EXPLAIN EXECUTE SQL

查看底层存储的执行计划,等同于 MYSQL 的 EXPLAIN 语句。关于该指令的更多案例请参考文档 SQL 优化基本概念SQL 优化方法

  1. mysql> explain execute select * from tddl_mgr_log limit 1;
  2. +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
  5. | 1 | SIMPLE | tddl_mgr_log | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
  6. +----+-------------+--------------+------+---------------+------+---------+------+------+-------+
  7. 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这条语句的执行情况。

  1. mysql> trace select 1;
  2. +---+
  3. | 1 |
  4. +---+
  5. | 1 |
  6. +---+
  7. 1 row in set (0.03 sec)
  8. mysql> show trace;
  9. +------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+
  10. | ID | TYPE | GROUP_NAME | DBKEY_NAME | TIME_COST(MS) | CONNECTION_TIME_COST(MS) | ROWS | STATEMENT | PARAMS |
  11. +------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+
  12. | 0 | Optimize | DRDS | DRDS | 3 | 0.00 | 0 | select 1 | NULL |
  13. | 1 | Query | TDDL5_00_GROUP | db218249098_sqa_zmf_tddl5_00_3309 | 7 | 0.15 | 1 | select 1 | NULL |
  14. +------+----------+----------------+-----------------------------------+---------------+--------------------------+------+-----------+--------+
  15. 2 rows in set (0.01 sec)

8. CHECK TABLE tablename

对数据表进行检查。主要用于 DDL 建表失败的情形,应用案例请参考文档 DDL 常见问题处理

  • 对于拆分表,检查底层物理分表是否有缺失的情况,底层的物理分表的列和索引是否是一致;
  • 对于单库单表,检查表是否存在。
  1. mysql> check table tddl_mgr_log;
  2. +------------------------+-------+----------+----------+
  3. | TABLE | OP | MSG_TYPE | MSG_TEXT |
  4. +------------------------+-------+----------+----------+
  5. | TDDL5_APP.tddl_mgr_log | check | status | OK |
  6. +------------------------+-------+----------+----------+
  7. 1 row in set (0.56 sec)
  8. mysql> check table tddl_mg;
  9. +-------------------+-------+----------+----------------------------------------+
  10. | TABLE | OP | MSG_TYPE | MSG_TEXT |
  11. +-------------------+-------+----------+----------------------------------------+
  12. | TDDL5_APP.tddl_mg | check | Error | Table 'tddl5_00.tddl_mg' doesn't exist |
  13. +-------------------+-------+----------+----------------------------------------+
  14. 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:指表创建时的其他所有选项。
  1. mysql> show table status like 'multi_db_multi_tbl';
  2. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  3. | 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 |
  4. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  5. | 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 | | |
  6. +--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-----------------------+-------------+------------+-----------------+----------+----------------+---------+
  7. 1 row in set (0.03 sec)

和 DRDS 的 SCAN HINT 结合,还可以查看每个物理分表的数据量。具体请参考 HINT 语法

  1. mysql> /!TDDL:SCAN='multi_db_multi_tbl'*/show table status like 'multi_db_multi_tbl';
  2. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  3. | 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 |
  4. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  5. | 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 |
  6. | 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 |
  7. | 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 |
  8. | 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 |
  9. | 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 |
  10. | 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 |
  11. | 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 |
  12. | 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 |
  13. | 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 |
  14. | 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 |
  15. | 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 |
  16. | 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 |
  17. | 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 |
  18. | 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 |
  19. | 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 |
  20. | 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 |
  21. +----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+--------------+
  22. 16 rows in set (0.04 sec)
本文导读目录