MySQL - 慢查询优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL - 慢查询优化

1. 慢查询定位

1.1 开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志的存储位置的命令如下:

show variables like 'slow_query_log%';

通过如下命令开启慢查询日志:

set global slow_query_log = on;
set global slow_query_log_file = 'oak-slow.log';
set global log_queries_not_using_indexes = on;
set long_query_time = 10;
  • long_query_time:指定慢查询的阀值,单位秒。如果 SQL 执行时间超过阀值,就属于慢查询记录到日志文件中。
  • log_queries_not_using_indexes:表示会记录没有使用索引的查询 SQL。前提是 slow_query_log 的值为 ON,否则不会奏效。

1.2 查询慢查询日志

  1. 查看慢查询日志:

直接使用文本编辑器打开slow.log日志即可:

  • time:日志记录的时间
  • User@Host:执行的用户及主机
  • Query_time:执行的时间
  • Lock_time:锁表时间
  • Rows_sent:发送给请求方的记录数,结果数量
  • Rows_examined:语句扫描的记录条数
  • SET timestamp:语句执行的时间点
  • select…:执行的具体的SQL语句
  1. 使用 mysqldumpslow 查看

MySQL 提供了一个慢查询日志分析工具 mysqldumpslow,可以通过该工具分析慢查询日志

内容。

在 MySQL bin 目录下执行下面命令可以查看该使用格式:

perl mysqldumpslow.pl --help

运行如下命令查看慢查询日志信息:

perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log

除了使用 mysqldumpslow 工具,也可以使用第三方分析工具,比如 pt-query-digest、

mysqlsla 等。

2. 慢查询优化

2.1 索引和慢查询

  1. 如何判断是否为慢查询?

MySQL 判断一条语句是否为慢查询语句,主要依据 SQL 语句的执行时间,它把当前语句的执行时间跟 long_query_time 参数做比较,如果语句的执行时间 > long_query_time,就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s,该参数值可以根据自己的业务需要进行调整。

  1. 如何判断是否应用了索引?

SQL 语句是否使用了索引,可根据 SQL 语句执行过程中有没有用到表的索引,可通过 explain 命令分析查看,检查结果中的 key 值,是否为NULL。

  1. 应用了索引是否一定快?

下面我们来看看下面语句的 explain 的结果,你觉得这条语句有用上索引吗?比如:

select * from user where id > 0;

虽然使用了索引,但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树,进行了全表扫描,此时索引就失去了意义。

而像 select * from user where id = 2; 这样的语句,才是我们平时说的使用了索引。它表示的意思是,我们使用了索引的快速搜索功能,并且有效地减少了扫描行数。

查询是否使用索引,只是表示一个 SQL 语句的执行过程;而是否为慢查询,是由它执行的时间决定的,也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

我们在使用索引时,不要只关注是否起作用,应该关心索引是否减少了查询扫描的数据行数,如果扫描行数减少了,效率才会得到提升。对于一个大表,不止要创建索引,还要考虑索引过滤性,过滤性好,执行速度才会快。

2.2 提高索引过滤性

假如有一个5000万记录的用户表,通过 sex = ‘男’ 索引过滤后,还需要定位3000万,SQL 执行速度也不会很快。其实这个问题涉及到索引的过滤性,比如1万条记录利用索引过滤后定位10条、100条、1000条,那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

下面我们看一个案例:

-- 建表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
  `sex` tinyint(4) NOT NULL DEFAULT '0' COMMENT '性别 1:男 2:女',
  `age` int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_sex` (`name`,`age`,`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
-- 插入数据
INSERT INTO `meta_demo`.`user` (`id`, `name`, `sex`, `age`) VALUES (1, 'javaboy001', 1, 18);
INSERT INTO `meta_demo`.`user` (`id`, `name`, `sex`, `age`) VALUES (1, 'javaboy002', 2, 18);
-- 查询语句 - 全表扫描
select * from user where age=18 and name like 'javaboy%';

优化1:

-- 添加索引
alter table user add index idx_name (name);

优化2:

-- 添加联合索引
alter table user add index idx_age_name (age, name);

优化3:

可以看到,index condition pushdown 优化的效果还是很不错的。再进一步优化,我们可以把名字的第一个字和年龄做一个联合索引,这里可以使用 MySQL 5.7 引入的虚拟列来实现。

-- 为user表添加first_name虚拟列,以及联合索引(first_name,age) 
alter table user add first_name varchar(2) generated always as (left(name, 1)), add index idx_fname_age (first_name, age); 
-- 分析 select
explain select * from user where first_name='张' and age=18;

2.3 慢查询原因总结

  1. 全表扫描: explain 分析 type 属性 all
  2. 全索引扫描: explain 分析 type 属性 index
  3. 索引过滤性不好: 靠索引字段选型、数据量和状态、表设计
  4. 频繁的回表查询开销: 尽量少用 select *,使用覆盖索引

3. 分页查询优化

3.1 一般性分页

一般的分页查询使用简单的 limit 子句就可以实现。limit 格式如下:

SELECT * FROM table_name LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量,注意从0开始;

第二个参数指定返回记录行的最大数目;

如果只给定一个参数,它表示返回最大的记录行数目;

思考1:如果偏移量固定,返回记录量对执行时间有什么影响?

select * from user limit 10000,1; 
select * from user limit 10000,10; 
select * from user limit 10000,100; 
select * from user limit 10000,1000; 
select * from user limit 10000,10000;

结果:在查询记录时,返回记录量低于100条,查询时间基本没有变化,差距不大。随着查询记录量越大,所花费的时间也会越来越多。

思考2:如果查询偏移量变化,返回记录数固定对执行时间有什么影响?

select * from user limit 1,100; 
select * from user limit 10,100; 
select * from user limit 100,100; 
select * from user limit 1000,100; 
select * from user limit 10000,100;

结果:在查询记录时,如果查询记录量相同,偏移量超过100后就开始随着偏移量增大,查询时间急剧的增加。(这种分页查询机制,每次都会从数据库第一条记录开始扫描,越往后查询越慢,而且查询的数据越多,也会拖慢总查询速度。)

3.2 分页优化方案

第一步:利用覆盖索引优化

select * from user limit 10000,100; 
-- >
select id from user limit 10000,100;

第二步:利用子查询优化

select * from user limit 10000,100; 
-- >
select * from user where id >= (select id from user limit 10000, 1) limit 100;

原因:使用了 id 做主键比较(id >=),并且子查询使用了覆盖索引进行优化。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
4月前
|
SQL 缓存 关系型数据库
MySQL 慢查询是怎样优化的
本文深入解析了MySQL查询速度变慢的原因及优化策略,涵盖查询缓存、执行流程、SQL优化、执行计划分析(如EXPLAIN)、查询状态查看等内容,帮助开发者快速定位并解决慢查询问题。
161 0
|
4月前
|
SQL 监控 关系型数据库
MySQL慢查询攻略
本文详细介绍了MySQL慢查询优化的全流程,从定位性能瓶颈到具体优化策略,再到高级调优与预防监控。首先通过开启慢查询日志和分析工具(如pt-query-digest)找到问题SQL,接着从索引优化(如最左前缀原则、覆盖索引)、SQL语句重构(如避免全表扫描)及EXPLAIN执行计划解析等方面进行核心优化。随后深入参数调优和架构升级,如调整innodb_buffer_pool_size、实施分库分表等。最后,通过实时监控工具(如PMM、Prometheus+Grafana)建立长效机制,并以电商订单查询为例,展示优化前后性能大幅提升的实战效果。
427 0
|
2月前
|
缓存 关系型数据库 MySQL
降低MySQL高CPU使用率的优化策略。
通过上述方法不断地迭代改进,在实际操作中需要根据具体场景做出相对合理判断。每一步改进都需谨慎评估其变动可能导致其他方面问题,在做任何变动前建议先在测试环境验证其效果后再部署到生产环境中去。
91 6
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
8月前
|
SQL 关系型数据库 MySQL
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
|
3月前
|
存储 SQL 关系型数据库
MySQL 动态分区管理:自动化与优化实践
本文介绍了如何利用 MySQL 的存储过程与事件调度器实现动态分区管理,自动化应对数据增长,提升查询性能与数据管理效率,并详细解析了分区创建、冲突避免及实际应用中的关键注意事项。
128 0
|
5月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
5月前
|
缓存 JSON 关系型数据库
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
|
7月前
|
存储 关系型数据库 MySQL
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
500 19

推荐镜像

更多