Logo
SQL Management

SQL Management #

SQL Slow Query #

Background #

The slow query log feature is used to log SQL statements that take longer to execute, making it easy for DBAs and developers to identify potentially problematic SQL statements. It is an important reference for database and SQL management, providing insights into performance optimization and identifying areas for improvement.

Configuration and Parameters #

The slow query log feature is available only for SphereEx-DBPlusEngine scenarios where the Agent technology is enabled. This feature logs SQL statements that take longer to execute, helping DBAs and developers identify potentially problematic SQL statements.

Since the slow query log is agent-based, the relevant configuration is located in agent.yaml:

plugins:
Logging:
props:
slow_query_log: true
long_query_time: 5000
  • slow_query_log: A boolean value that determines whether to enable the slow query log feature. The default value is true.
  • long_query_time: The slow query time threshold, in milliseconds (ms), at which SQL statements that take longer to execute are recorded in the slow query log. The default value is 5000 ms. This value can be adjusted based on the specific needs of the database environment.

Slow Query Log Format #

timestamp: {time} db: {db database user: {user} host: {host} query_time: {query time}
{sql}
  • timestamp: The time at which the log records were generated;
  • db: Database name.
  • user: The name of the user used in the current connection;
  • host: Client access address;
  • query_time: SQL execution time, the unit is ms;
  • sql: An SQL statement that is sent by the client when a slow query occurs.

Slow Query Log Example #

timestamp: 2022-07-01 00:00:00.000 db: sharding_db user: root host: 127.0.0.1 query_time: 159
CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)

SQL Execution Plan #

SphereEx-DBPlusEngine currently does not support displaying SQL execution plans. However, it does support viewing the data routing situation in distributed scenarios. To view this information, you can use the PREVIEW command, which displays the specific SQL being executed and the corresponding data shardings.

  • EXPLAIN

EXLAIN is simple to use. Just run it directly on the command line. For the storage node using the MySQL database, this command can directly call the corresponding command of the underlying database; for the storage node using the openGauss database, you can use the EXPLAIN PERFORMANCE command to obtain the execution plan.

mysql> explain select * from t_single0;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_single0 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    100.0 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+

mysql> explain select * from t_shard1;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_shard1_0 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    100.0 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+

Note: For the shard table, the above command will display the execution plan on a particular shard, and the complete distributed execution plan is not yet available.

  • PREVIEW

View through the PREVIEW command, which can display the specific executed SQL and the corresponding data fragmentation.

mysql> preview select * from t_shard1;
+------------------+-------------------------------------------------------------+
| data_source_name | actual_sql                                                  |
+------------------+-------------------------------------------------------------+
| test1_3307       | select * from t_shard1_0 UNION ALL select * from t_shard1_2 |
| test1_3308       | select * from t_shard1_1 UNION ALL select * from t_shard1_3 |
+------------------+-------------------------------------------------------------+

SQL Hint #

SQL Hint is a hinting method that enables forced routing by adding annotations to SQL statements. It reduces the cost of code modification for users and allows SQL to be sent directly to a specified database for execution, regardless of other sharding logic.

For example, in a multi-tenant scenario, users no longer need to configure complex sharding logic or modify business logic. They can simply add the specified database to the comment information.

SQL Hint annotations use the /* */ format and must begin with the prefix SHARDINGSPHERE_HINT.

Term of Use #

  • Comments

    The use of SQL Hint is very simple.

    The first step is to enable configurations of parsing annotations in advance and set sqlCommentParseEnabled to true.

    The second step is to add annotation to the SQL statement. Currently, SQL Hint supports specifying data source routing and primary database routing.

  • Data source routing

Currently, it only supports routing to one data source. The content needs to start with ShardingSphere hint:, and the attribute name is dataSourceName.

/* ShardingSphere hint: dataSourceName=ds_0 */
SELECT * FROM t_order;
  • SQL Rewrite Routing

Support enabling or disabling SQL rewriting routing. The content needs to start with ShardingSphere hint:, and the attribute name is SKIP_SQL_REWRITE.

/* ShardingSphere hint: SKIP_SQL_REWRITE=true */
SELECT * FROM t_order;
  • Primary database routing

It supports routing to the primary database. The content needs to start with ShardingSphere hint:, and the attribute name is WRITE_ROUTE_ONLY.

/* SHARDINGSPHERE_HINT: WRITE_ROUTE_ONLY=true */
SELECT * FROM t_order;

  • Sharding routing It supports routing to a specific shard. Optional properties include:

  • {table}.SHARDING_DATABASE_VALUE: used to add data source sharding key value corresponding to {table}. Multiple properties are separated by a comma.

  • {table}.SHARDING_TABLE_VALUE: used to add table sharding key value corresponding to {table}. Multiple properties are separated by a comma. When database sharding is required and table sharding is not, while mandatory routing is carried out to a sub-database, you can use SHARDING_DATABASE_VALUE to add shards without specifying {table}.

  • Skip data desensitization

Supports skip data desensitization, and query results return plain text. The content needs to start with ShardingSphere hint:, and the attribute name is SKIP_SQL_MASK.

/* ShardingSphere hint: SKIP_SQL_MASK=true */
SELECT * FROM t_order;