mysql慢查询要怎样优化

发布时间：2023-07-07 15:51:04 所属栏目：MySql教程来源：

导读：本篇内容主要讲解“mysql慢查询如何优化”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql慢查询如何优化”吧!

1 慢查询优化思路

当

本篇内容主要讲解“mysql慢查询如何优化”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql慢查询如何优化”吧!

1 慢查询优化思路

当发生慢查询的时候，优化的思路为：

利用慢查询日志定位慢查询 SQL

通过 explain 分析慢查询 SQL

修改 SQL，尽量让 SQL 走索引

2 慢查询日志

MySQL 提供了一个功能——慢查询日志，会记录查询时间超过指定时间阈值的 SQL 到日志中，便于我们定位慢查询并且优化对应的 SQL 语句。

首先查看 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)

这里主要关注三个变量：

long_query_time，慢查询的时间阈值，单位秒，如果一个 SQL 语句的执行时间超过这个值，那么 MySQL 就认定其为慢查询

slow_query_log，慢查询日志功能是否开启，默认关闭，开启后记录慢查询

slow_query_log_file，慢查询日志文件的存储位置

默认慢查询日志功能是关闭的，因此我们需要启动该功能

# 开启慢查询日志

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)

这样子设置后，MySQL 重启会丢失这些配置，需要在配置文件中修改才会永久有效。

3 explain

我们可以使用 explain 分析 SQL 语句的执行情况，例如：

mysql> explain select sum(1+2);

执行结果如下，可以看到有很多字段

我们主要看看一些重要的字段：

select_type 表示查询语句的查询类型，包括简单查询、子查询等等

table 表示查询的表，不一定是存在表，可能是本次查询中得到的临时表

type 表示检索类型，使用全表扫描、还是索引扫描等

possible_keys表示可能使用的索引列

keys表示查询中实际使用的索引列，由查询优化器决定

3.1 select_type 字段

3.2 type 字段

对于 InnoDB 存储引擎，type列通常都是all或者index。

关于 type 字段的值，其从上到下对应的 SQL 的执行性能逐渐变差。

3.3 extra 字段

4 慢查询例子

准备数据，数据表结构：

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 '组合索引'

);

随机生成 200w 条数据

mysql> select count(id) from user_info_large;

+-----------+

| count(id) |

+-----------+

|   2000000 |

+-----------+

1 row in set (0.38 sec)

执行以下 SQL 语句，没有使用任何索引字段：

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

Navicat 工具显示的查询时间如下，这并不是 MySQL 真正执行 SQL 的时间，这里面包含了网络传输等时间：

SQL 具体的查询时间可以查看慢查询日志：

# 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;

关于其中一些信息的说明：

Time：SQL 执行的开始时间

Query_time：SQL 语句查询花费的时间，可以看到花费了 10 秒钟

Lock_time：等待锁表的时间

Rows_sent：语句返回的记录数

Rows_examined：从存储引擎中返回的记录数

正在执行的慢查询是不会被记录到慢查询日志的，只有等待其执行完毕才会记录到日志中。

我们可以使用 show processlist 查看正在执行 SQL 的线程。

再执行以下语句，使用索引 account 字段：

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

查看慢查询日志，并没有被记录下来。

现在分别使用 explain 查看 SQL 语句的执行情况：

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

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

再看看这条 SQL 语句：

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

分析情况如下：

mysql慢查询如何优化

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;

mysql慢查询如何优化

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

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

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!