Detailed explanation of the general steps for SQL statement optimization

Preface

This article mainly shares with you the general steps for SQL statement optimization. It is shared for your reference and learning. Without further ado, let’s take a look at the detailed introduction.

1. Use the show status command to understand the execution frequency of various SQL statements

After the mysql client is connected successfully, show [session|global] status command can provide server status information, or you can use mysqladmin extend-status command on the operating system to obtain these messages.

show status command can be added with the session (default) or global options:

session (current connection)
global (data since last startup)

# Com_xxx indicates the number of times each xxx statement is executed.
mysql> show status like 'Com_%';

We are usually more concerned about the following statistical parameters:

Com_select: The number of times the select operation is executed. Only 1 is accumulated for each query.
Com_insert: The number of times the insert operation is executed. For batch insert operations, it is accumulated only once.
Com_update: The number of update operations performed.
Com_delete: The number of times the delete operation is performed.

The above parameters are accumulated for table operations of all storage engines. The following parameters are only for InnoDB, and the accumulation algorithm is slightly different:

Innodb_rows_read : The number of rows returned by a select query.
Innodb_rows_inserted : The number of rows inserted by the insert operation.
Innodb_rows_updated: The number of rows updated by the update operation.
Innodb_rows_deleted: The number of rows deleted by the delete operation.

Through the above parameters, you can easily understand whether the current database application is mainly based on insert updates or query operations, and the approximate execution ratio of various types of SQL. The count of update operations is the count of the number of executions, which is accumulated regardless of whether it is committed or rolled back.

For transactional applications, Com_commit and Com_rollback can be used to understand the transaction commit and rollback situations. For databases with very frequent rollback operations, it may mean that there is a problem with the application writing.

In addition, the following parameters help users understand the basic situation of the database:

Connections: The number of attempts to connect to the MySQL server.
Uptime: Server working time.
Slow_queries : The number of slow queries.

2. Define SQL statements with low execution efficiency

1. Use the slow query log to locate SQL statements with low execution efficiency. When started with --log-slow-queries[=file_name] option, mysqld writes a log file containing all SQL statements whose execution time exceeds long_query_time seconds.

2. The slow query log is recorded only after the query is completed. Therefore, when the application reflects a problem with execution efficiency, the slow query log cannot locate the problem. You can use the show processlist command to view the current MySQL threads, including the thread status, whether the table is locked, etc. You can view the execution status of SQL in real time and optimize some table locking operations.

3. Analyze the execution plan of inefficient SQL through explain

Test database address: https://downloads.mysql.com/docs/sakila-db.zip (local download)

To count the total amount paid by a certain email for renting movie copies, you need to associate the customer table customer and the payment table payment, and perform a sum operation on the amount field. The corresponding execution plan is as follows:

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='[email protected]'\G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

select_type: indicates the select type. Common values are:
simple: simple table, and no table join or subquery is used
primary: main query, that is, the outer query
union: The second or subsequent query statement in a union
subquery: The first select in the subquery
table: table to output the result set
type: Indicates the way MySQL finds the required row in the table, or the access type. Common types, from worst to best, are: all, index, range, ref, eq_ref, const, system, null:

1. type=ALL , full table scan, mysql traverses the entire table to find matching rows:

mysql> explain select * from film where rating > 9 \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 33.33
 Extra: Using where
1 row in set, 1 warning (0.01 sec)

2. type=index , index full scan, MySQL traverses the entire index to search for matching rows

mysql> explain select title form film\G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: index
possible_keys: NULL
  key: idx_title
 key_len: 767
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

3. type=range , index range scan, commonly used in <, <=, >, >=, between and other operations:

mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: range
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: NULL
 rows: 1350
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.07 sec)

4. type=ref , use a non-unique index scan or a prefix scan of a unique index to return rows that match a single value, for example:

mysql> explain select * from payment where customer_id = 350 \G 
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: const
 rows: 23
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.01 sec)

The index idx_fk_customer_id is a non-unique index. The query condition is the equal value query condition customer_id = 350 , so the type of the scan index is ref. Ref also often appears in join operations:

mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 599
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.b.customer_id
 rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5. type=eq_ref , similar to ref, the difference is that the index used is a unique index. For each index key value, only one record in the table needs to match; in simple terms, primary key or unique index is used as the join condition in multi-table joins.

mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: sakila.b.film_id
 rows: 1
 filtered: 100.00
 Extra: Using where
2 rows in set, 1 warning (0.03 sec)

6. type=const/system , there is at most one matching row in a single table, and the query is very fast, so the values of other columns in this matching row can be treated as constants by the optimizer in the current query, for example, querying based on the primary key key or unique index .

mysql> create table test_const (
 -> test_id int,
 -> test_context varchar(10),
 -> primary key (`test_id`),
 -> );
 
insert into test_const values(1,'hello');

explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: test_const
 partitions: NULL
 type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 4
  ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 1 warning (0.00 sec)

7. type=null , mysql can get the result directly without accessing the table or index:

mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: NULL
 partitions: NULL
 type: NULL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
 Extra: No tables used
1 row in set, 1 warning (0.00 sec)

Type type has other values, such as ref_or_null (similar to ref, except that the condition includes a query for null), index_merge (index merge optimization), unique_subquery (in is followed by a subquery that queries the primary key field), index_subquery (similar to unique_subquery, except that in is followed by a subquery that queries the non-unique index field), etc.

possible_keys : Indicates the possible indexes that can be used during the query.
key: indicates the actual index used
key-len: The length of the index field used.
rows: the number of scan rows
Extra: Description and description of the execution, including additional information that is not suitable for display in other columns but is very important for the execution plan.

show warnings command

After executing explain, execute show warnings to see what SQL the optimizer rewrites before the SQL is actually executed:

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = '[email protected]'\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = '[email protected]')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

From the warning message field, we can see that the optimizer automatically removes the condition that 1=1 always holds. In other words, the optimizer automatically removes the condition that always holds when rewriting SQL.

The explain command also has support for partitions.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT '1',
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: customer_part
 partitions: p2
  type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: const
  rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warnings (0.00 sec)

You can see that the partition accessed by sql is p2.

4. Analyze SQL performance through performance_schema

Older versions of MySQL can use profiles to analyze SQL performance. I use version 5.7.18, which no longer allows the use of profiles. It is recommended to use
performance_schema analyzes SQL.

5. Use trace to analyze how the optimizer chooses the execution plan.

MySQL 5.6 provides a trace function for SQL statements, which can help us better understand why the optimizer chooses execution plan A instead of execution plan B, and help us better understand the optimizer's behavior.

Usage: First open the trace, set the format to json, and set the maximum memory size that the trace can use to avoid the incomplete display during the parsing process due to the default memory being too small.

MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

Next, execute the SQL statement you want to trace, for example, to find out the records of the movie copy with inventory number inventory_id 4466 in the rental table rental that was rented out between the rental date rental_date of 2005-05-25 4:00:00 ~ 5:00:00:

mysql> select rental_id from rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

6. Identify the problem and take appropriate optimization measures

After the above steps, the cause of the problem can be basically confirmed. At this time, appropriate measures can be taken according to the situation to optimize and improve execution efficiency.

Summarize

The above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in: