Tutorial on sql statement optimization

There are many tutorials on SQL optimization on the Internet, but they are quite messy. I have sorted them out recently when I have time, and written them down to share with you. The following article mainly introduces the general steps about sql statement optimization. Friends who need it can For reference, let’s learn with the editor below.

Preface

This article mainly shares with you the general steps for sql statement optimization. It is shared for your reference and learning. There is not much to say below. Having said that, let’s take a look at the detailed introduction.

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

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

show status You can add the option session (default) or global:

• session (current connection)

• global (since the data was last started)

# Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like 'Com_%';

We usually care about the following statistical parameters ：

• Com_select: The number of times the select operation is performed. Only 1 will be accumulated for one query.

• Com_insert: The number of times the insert operation is performed. For batch insert insert operations, only one is accumulated.

• Com_update: Number of times to perform update operations.

• Com_delete: The number of times the delete operation is performed.

The above parameters will be accumulated for all storage engine table operations. The following parameters are only for innodb, and the accumulation algorithm is slightly different:

• Innodb_rows_read: The number of rows returned by the 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: Number of rows deleted by delete operation.

Through the above parameters, you can easily understand whether the current database application is mainly based on insert and update or query operations, as well as the approximate execution ratio of various types of SQL how many. The count of update operations is a count of the number of executions, and will be accumulated regardless of submission or rollback.

For transactional applications, you can learn about transaction commit and rollback through Com_commit and Com_rollback . For databases with very frequent rollback operations, It may mean 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. Locate those with low execution efficiency through slow query logs Lower sql statements, when started with the --log-slow-queries[=file_name] option, mysqld writes a log file containing all sql statements that take more than long_query_time seconds to execute.

2. The slow query log is recorded after the query is completed, so when the application reflects execution efficiency problems, the slow query log cannot locate the problem. You can use the show processlist command to view the current MySQL threads in progress, including You can check the status of the thread, whether to lock the table, etc. in real time, and optimize some table lock 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 a movie copy, you need to associate the customer table customer and the payment table payment, and do the payment amount field Sum operation, 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='JANE.BENNETT@sakilacustomer.org'\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 does not use table joins or subqueries
                                                                                                                                                                                                                                                                                      primary query, that is, the outer query
  Union: The second or subsequent query statement in union
  subquery: The first select in the subquery

• table: The table of the output result set

• type: Indicates that mysql finds all the results in the table The required method, or access type, the performance of common types from worst to best is: 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, 索引全扫描，mysql 遍历整个索引来查询匹配的行

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,索引范围扫描，常见于d2714fbb0e49a95306c2048bc19e4f2b、>=、between等操作：

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, 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：

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)

索引 idx_fk_customer_id 是非唯一索引，查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中：

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,类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。

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,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 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 不用访问表或者索引，直接就能够得到结果：

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 还有其他值，如 ref_or_null (与 ref 类似，区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。

• possible_keys : 表示查询时可能使用的索引。

• key ：表示实际使用索引

• key-len : 使用到索引字段的长度。

• rows ： 扫描行的数量

• extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写：

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\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` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件，也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

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)

可以看到 sql 访问的分区是 p2。

四、通过 performance_schema 分析 sql 性能

旧版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已经不允许使用 profiles 了，推荐用
performance_schema 分析sql。

五、通过 trace 分析优化器如何选择执行计划。

mysql5.6 提供了对 sql 的跟踪 trace，可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划，帮助我们更好的理解优化器的行为。

使用方式：首先打开 trace ，设置格式为 json，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

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)

接下来执行想做 trace 的 sql 语句，例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录：

mysql> select rental_id from rental where 1=1 and rental_date >= &#39;2005-05-25 04:00:00&#39; and rental_date <= &#39;2005-05-25 05:00:00&#39; 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)

六、 确定问题并采取相应的优化措施

经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。

The above is the detailed content of Tutorial on sql statement optimization. For more information, please follow other related articles on the PHP Chinese website!