All Products
Search
Document Center

SQL optimization statements

Last Updated: Sep 01, 2020

This topic lists the statements for SQL optimization supported by DRDS.

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

The SQL statements that take more than 1 second to execute are slow SQL statements. Slow logical SQL statements are the slow SQL statements sent from an application to a Distributed Relational Database Service (DRDS) instance.

  • SHOW SLOW: You can execute this SQL statement to view the 100 slowest logical SQL statements since the DRDS instance is started or CLEAR SLOW is last executed. Note that the records on the 100 slowest logical SQL statements are cached in the DRDS system and are lost when the instance is restarted or CLEAR SLOW is executed.
  • SHOW FULL SLOW: You can execute this SQL statement to view all the slow logical SQL statements that are recorded and persisted to the built-in database of the DRDS instance since the DRDS instance is started up. The maximum number of the records is specified in the specifications of the DRDS instance you bought. The DRDS instance scrolls to delete the earliest slow SQL statements. If the instance specifications are 4-core 4 GB, a maximum of 10,000 slow SQL statements can be recorded, including slow logical and physical SQL statements. If the instance specifications are 8-core 8 GB, a maximum of 20,000 slow SQL statements can be recorded, including slow logical and physical SQL statements. Other instance specifications follow the same rule.

The following describes the meanings of important columns:

  • HOST: the IP address of the host from which the SQL statement is sent.
  • START_TIME: the time when the SQL statement started to be executed.
  • EXECUTE_TIME: the time that the DRDS instance takes to execute the SQL statement.
  • AFFECT_ROW: For data manipulation language (DML) statements, this parameter indicates the number of affected rows. For query statements, this parameter indicates the number of returned records.
  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]

The SQL statements that take more than 1 second to execute are called slow SQL statements. Slow physical SQL statements are the slow SQL statements sent from DRDS instances to ApsaraDB RDS for MySQL instances.

  • SHOW PHYSICAL_SLOW: You can execute this SQL statement to view the 100 slowest physical SQL statements since the DRDS instance is started or CLEAR SLOW is last executed. Note that the records on the 100 slowest logical SQL statements are cached in the DRDS system and are lost when the instance is restarted or CLEAR SLOW is executed.
  • SHOW FULL PHYSICAL_SLOW: You can execute this SQL statement to view all the slow physical SQL queries that are recorded and persisted to the built-in database of the DRDS instance since the DRDS instance is started up. The maximum number of the records is specified in the specifications of the DRDS instance you bought. The DRDS instance scrolls to delete the earliest slow SQL statements. If the instance specifications are 4-core 4 GB, a maximum of 10,000 slow SQL statements can be recorded, including slow logical and physical SQL statements. If the instance specifications are 8-core 8 GB, a maximum of 20,000 slow SQL statements can be recorded, including slow logical and physical SQL statements. Other instance specifications follow the same rule.

The following describes the meanings of important columns:

  • GROUP_NAME: the name of the group to which the database that executes the SQL statement belongs.
  • START_TIME: the time when the SQL statement started to be executed.
  • EXECUTE_TIME: the time that the DRDS instance takes to execute the SQL statement.
  • AFFECT_ROW: For DML statements, this parameter indicates the number of affected rows. For query statements, this parameter indicates the number of returned records.
  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

You can execute this SQL statement to clear the 100 slowest logical SQL statements and the 100 slowest physical SQL statements that have been recorded since the DRDS instance was started up or the last time when CLEAR SLOW was executed.Note: Both SHOW SLOW and SHOW PHYSICAL_SLOW can be executed to display the 100 slowest SQL statements. If CLEAR SLOW has not been executed for a long time, these SQL statements might have been recorded a long time ago. Therefore, we recommend that you execute CLEAR SLOW after SQL optimization statements are executed, and check the optimized results of slow SQL statements after the system runs for a while.

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

4. EXPLAIN SQL

You can execute this SQL statement to view the execution plan of a specified SQL statement in DRDS. Note that this SQL statement is not actually executed.

Example:

You can execute this SQL statement to view the execution plan of the SQL select * from doctest statement. The doctest table is stored in database shards according to values in the id column. According to the execution plan, the SQL statement will be routed to each database shard for execution, and the execution results will be aggregated.

  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)

You can execute this SQL statement to view the execution plan of the SQL select * from doctest where id = 1 statement. The doctest table is stored in database shards according to values in the id column. According to the execution plan, the DRDS instance will calculate a specified database shard based on the shard key, which is id, directly route the SQL statement to the database shard, and aggregate the execution results.

  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

You can execute this SQL statement to view the execution plan of a specified SQL statement in DRDS. Note that this SQL statement is not actually executed.

  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

You can execute this SQL statement to view the execution plan of underlying storage. This statement is equivalent to the MYSQL EXPLAIN statement.

  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 and SHOW TRACE

You can execute these SQL statements to view the execution results of an SQL statement. Note that you must use TRACE [SQL] and SHOW TRACE together. The difference between TRACE SQL and EXPLAIN SQL is that TRACE SQL is actually executed.

For example, you can execute these statements to view the execution results of the select 1 statement.

  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

You can execute this SQL statement to check a data table. This is mainly used when data definition language (DDL) table creation fails.

  • If the data table is a table shard, this SQL statement allows you to check whether any underlying physical table shard is missing and whether the column and index of the underlying physical table shard are consistent.
  • If the data table is a single-database non-partition table, this SQL statement allows you to check whether this table exists.
  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]

You can execute this SQL statement to obtain the information about a table. This command aggregates the data of all underlying physical table shards.

The following describes the meanings of important columns:

  • NAME: the name of the table.
  • ENGINE: the storage engine of the table.
  • VERSION: the version of the storage engine of the table.
  • ROW_FORMAT: the format of the rows in the table. Valid values include Dynamic, Fixed, and Compressed. The value Dynamic indicates that the row length is variable, for example, a VARCHAR or BLOB field. The value Fixed indicates that the row length is constant, for example, a CHAR or INTEGER field.
  • ROWS: the number of rows in the table.
  • AVG_ROW_LENGTH: the average number of bytes in each row.
  • DATA_LENGTH: the data volume of the entire table. Unit: bytes.
  • MAX_DATA_LENGTH: the maximum volume of data that can be stored in the table.
  • INDEX_LENGTH: the size of the disk space occupied by indexes.
  • CREATE_TIME: the time when the table was created.
  • UPDATE_TIME: the time when the table was last updated.
  • COLLATION: the default character set and character sorting rule of the table.
  • CREATE_OPTIONS: all the other options specified when the table was created.
  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)

This statement can be used with SCAN HINT of DRDS to display the data volume of each physical table shard.

  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)