mysql慢查询如何优化

发布时间:2023-03-13 17:12:16 作者:iii
来源:亿速云 阅读:879

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

1 慢查询优化思路

当发生慢查询的时候,优化的思路为:

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)

这里主要关注三个变量:

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

# 开启慢查询日志
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);

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

mysql慢查询如何优化

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

3.1 select_type 字段

mysql慢查询如何优化

3.2 type 字段

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

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

mysql慢查询如何优化

3.3 extra 字段

mysql慢查询如何优化

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)

截取部分数据:

mysql慢查询如何优化

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

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

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

mysql慢查询如何优化

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;

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

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

我们可以使用 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;

分析情况如下:

mysql慢查询如何优化

可以看到没有使用到索引,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);

mysql慢查询如何优化

到此,相信大家对“mysql慢查询如何优化”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

推荐阅读:
  1. mysql慢sql优化
  2. Python查询MySQL慢日志

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:Mysql怎么处理大数据表

下一篇:node.js的后端路由自动加载怎么实现

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》