Summary and sharing of mysql slow query optimization ideas

This article brings you relevant knowledge about mysql, which mainly introduces issues related to slow query optimization, including using slow query logs to locate slow query SQL and analyzing slow query through explain. Query SQL and modify SQL so that SQL can be indexed as much as possible. Let’s take a look at it together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1 Slow query optimization ideas

When a slow query occurs , the optimization idea is:

• Use slow query logs to locate slow query SQL

• Use explain to analyze slow query SQL

• Modify SQL and try to make SQL indexed

2 Slow query log

MySQL provides a function-slow query log, which will record queries SQL whose time exceeds the specified time threshold is recorded in the log, allowing us to locate slow queries and optimize the corresponding SQL statements.

First check the global variables related to slow query in MySQL:

mysql> show global variables like '%quer%';
+----------------------------------------+-------------------------------+
| Variable_name                          | Value                         |
+----------------------------------------+-------------------------------+
| binlog_rows_query_log_events           | OFF                           |
| ft_query_expansion_limit               | 20                            |
| have_query_cache                       | YES                           |
| log_queries_not_using_indexes          | OFF                           |
| log_throttle_queries_not_using_indexes | 0                             |
==========================================================================
| long_query_time                        | 10.000000                     |【1】慢查询的时间阈值
==========================================================================
| query_alloc_block_size                 | 8192                          |
| query_cache_limit                      | 1048576                       |
| query_cache_min_res_unit               | 4096                          |
| query_cache_size                       | 16777216                      |
| query_cache_type                       | OFF                           |
| query_cache_wlock_invalidate           | OFF                           |
| query_prealloc_size                    | 8192                          |
==========================================================================
| slow_query_log                         | OFF                           |【2】慢查询日志是否开启
| slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查询日志文件存储位置
==========================================================================
+----------------------------------------+-------------------------------+
15 rows in set (0.00 sec)

Here we mainly focus on three variables:

• long_query_time, the time of slow query Threshold, in seconds. If the execution time of a SQL statement exceeds this value, MySQL will determine it as a slow query.

• slow_query_log, whether the slow query log function is turned on, is turned off by default. After it is turned on, Record slow query

• slow_query_log_file, the storage location of the slow query log file

The slow query log function is turned off by default, so we need to enable it Function

# 开启慢查询日志
mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)
# 设置慢查询时间阈值
mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

After setting like this, MySQL will lose these configurations when it restarts, and they need to be modified in the configuration file to be permanently effective.

3 explain

We can use explain to analyze the execution of SQL statements, for example:

mysql> explain select sum(1+2);

The execution results are as follows, you can see that there are many fields

We mainly look at some important fields:

• select_type represents the query type of the query statement, including simple queries, subqueries, etc.

• table represents the query table, which may not necessarily exist. It may be a temporary table obtained in this query.

• type represents the retrieval type, use the full table Scan, index scan, etc.

• possible_keys indicates the index columns that may be used

• keys indicates the index columns actually used in the query, which are optimized by the query The processor decides

3.1 select_type field

3.2 type field

For the InnoDB storage engine, the type column is usually all or index.

Regarding the value of the type field, the execution performance of the corresponding SQL gradually becomes worse from top to bottom.

3.3 extra field

4 Slow Query example

Prepare data, data table structure:

create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`account` VARCHAR(20) NOT NULL COMMENT '用户账号',
`name` VARCHAR(20) NOT NULL COMMENT '用户名',
`password` VARCHAR(20) not null COMMENT '用户密码',
`area` VARCHAR(20) NOT NULL COMMENT '用户地址',
`signature` VARCHAR(50) not null COMMENT '个性签名',
PRIMARY KEY (`id`) COMMENT '主键',
UNIQUE (`account`) COMMENT '唯一索引',
KEY `index_area_signture` (`area`,  `signature`)  COMMENT '组合索引'
);

Randomly generate 2 million pieces of data

mysql> select count(id) from user_info_large;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+
1 row in set (0.38 sec)

Intercept part of the data:

Execute the following SQL statement without using any index fields:

SELECT name from user_info_large ORDER BY name desc limit 0,100000;

The query time displayed by the Navicat tool is as follows. This is not the time when MySQL actually executes SQL. It includes network transmission and other times. ：

SQL specific query time can be viewed in the slow query log:

# Time: 2022-09-26T13:44:18.405459Z
# User@Host: root[root] @  [ip]  Id:  1893
# Query_time: 10.162999  Lock_time: 0.000113 Rows_sent: 100000  Rows_examined: 2100000
SET timestamp=1664199858;
SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Explanation on some of the information:

• Time: The start time of SQL execution

• Query_time: The time spent on SQL statement query, you can see that it took 10 seconds

• Lock_time: The time to wait for the lock table

• Rows_sent: The number of records returned by the statement

• Rows_examined: The number of records returned from the storage engine

The slow query being executed will not be recorded in the slow query log. It will only be recorded in the log after it is completed.

We can use show processlist to view the thread executing SQL.

Execute the following statement again and use the index account field:

SELECT account from user_info_large ORDER BY account desc limit 0,100000;

View the slow query log and it is not recorded.

Now use explain to view the execution of SQL statements:

explain SELECT name from user_info_large ORDER BY name desc limit 0,100000;

The analysis is as follows:

可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra  也是外部排序。

再看看这条 SQL 语句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情况如下：

type 为 index，使用了索引，使用的索引字段为 account，Extra 显示为使用索引排序。

因此，在实际开发中，我们可以针对慢查询的 SQL，使用 explain 分析语句，根据分析情况以及索引的设计，重新设计 SQL 语句，让 SQL 语句尽量走索引，走合适的索引。

5 优化器与索引

在执行 SQL 时，MySQL 的优化器会根据情况选择索引，但并不能保证其执行时间一定最短，我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。

例如，以下语句执行后，key 字段为 account，并没有走主键索引。

explain SELECT count(id) from user_info_large;

如果使用 force key，就可以强制令语句走主键索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);

6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

• 通过慢查询日志定位 SQL

• 使用 explain 分析 SQL

• 修改 SQL，令其走合适的索引

 在使用 explain 时，我们主要关注这些字段：

• type

• key

• Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

• 模糊查询不要使用通配符 % 开头，例如 like '%abc'

• 使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or

• 使用复合索引遵循最左原则

• 索引字段不要参加表达式运算、函数运算

推荐学习：mysql视频教程

The above is the detailed content of Summary and sharing of mysql slow query optimization ideas. For more information, please follow other related articles on the PHP Chinese website!