深入理解MySQL慢查询优化(3) -- 案例实操

2024-09-03 02:20

本文主要是介绍深入理解MySQL慢查询优化(3) -- 案例实操,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

经过前面内容的理解,我们以及了解了MySQL的优化策略,以及SQL语句的执行流程,接下来我们通过一个问题来学习,如何优化SQL

1. 如何构造一条慢SQL

前面我们介绍了,数据库实现高效查询的核心原则是:增量查询和索引加速,其中索引加速很大程度上也是在为增量查询服务。

要构造一条慢SQL,我们就可以从破坏增量查询入手:

1.1 利用join制造大量计算

select * from employees join salaries on employees.emp_no = salaries.emp_no 
limit 10;

优化思路:给内层表的join列(salaries.emp_no)加上索引,避免全表扫描内层表

1.2 添加where条件

select * from employees join salaries on salaries.emp_no = employees.emp_no 
where salaries.salary > 60000
limit 10;

优化思路:在salaries.salary和employees.emp_no上各建一个索引,实际执行的时候,MySQL就可以先利用salaries.salary上的索引,定位到大于60000的数据,再把salaries作为外层表和employees表join,同时employees表也可以使用 employees.emp_no 定位到需要的数据,这样就消除了where的难度。

1.3 添加多个where条件

select * from employees join salaries on salaries.emp_no = employees.emp_no 
where salaries.salary > 60000 and salaries.from_date > '1992-07-12'
limit 10;

优化思路:这里where条件都是同一个表的,可以创建salary和from_date的联合索引,查询时MySQL就可以更具salary和from_date上的联合索引找出 salary > 60000并且from_date > '1992-07-01' 的数据,然后再把salaries作为外层表和内存进行join;如果where条件过多或者并非同一个表的字段,则可以对每个where条件的字段都创建一个索引,使用实际效果好的那一个索引,不推荐使用多个不同的索引共同筛选,因为回表操作的开销无法忽略

1.4 使用left join限制join的顺序

select * from employees left join salaries on salaries.emp_no = employees.emp_no 
where salaries.salary > 60000and salaries.from_date > '1992-07-12'
limit 10;

优化思路这里使用了left join看似强行把employees指定为了外层表,但是,left join 仅比join多了内层表为null的数据,但上述SQL语句中,存在where条件 salaries.salary > 60000 ,null > 60000一定为false,于是这里的left join就和join每什么区别了,MySQL就会把这个left join优化为join,我们就可以继续使用上面第3点的优化思路

1.5 引入order by 破坏增量策略

select * from employees join salaries on salaries.emp_no = employees.emp_no 
where salaries.salary > 60000
order by salaries.from_date
limit 10;

这里的where和order by使用了不同的列,会涉及索引的选择问题:

  • 如果where筛选效果好,那么就使用where字段上的索引,先筛选得出结果再排序,比如只有几百条记录符合条件,那对几百条数据做个排序开销也不大。
  • 如果where筛选效果不好,就使用order by字段上的索引,那么就可以继续使用增量策略,按顺序查找符合条件的数据,收集到10条就返回
  • 如果where筛选效果不好不坏,并且表的数据量巨大,如果先筛选后排序,筛选后的数据量多,让 sort buffer放不下,排序开销就足够大;如果使用order by上的索引顺序搜索,搜索的很多数据都不符合where条件,需要大量搜索才能凑出10条数据,至此我们成功构造出了一条慢SQL

通过上面的分析我们可以看到,数据库的增量略是相当成功的, 想制造一条慢SQL是并不容易的。

这里再次提醒一点,MySQL 数据库通常不适合全量扫描的场景,特别是当表的数据量非常大时。全量扫描(即全表扫描)会导致高 I/O 负载和长时间的查询执行,这对性能和资源消耗都有较大影响,所以使用MySQL通常会使用分页查询。如果业务需要大量的全量扫描则可以考虑使用其他数据库,例如NoSQL。同时MySQL也不适合统计类的查询,比如 sum/avg等,这类查询天然和增量策略冲突

2. 案例练习 

相信有了前面的学习,我们已经对如何分析并优化一条SQL语句有了较深的了解,下面我们尝试分析几个案例:

2.1 案例一

-- cost 364ms
SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no AND salaries.from_date = '1992-08-04' 
WHERE employees.emp_no > 10005 AND salaries.salary > 70000 
ORDER BY salaries.salary 
LIMIT 10;  -- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)-> Limit: 10 row(s)-> Sort: salaries.salary, limit input to 10 row(s) per chunk-> Stream results  (cost=95578 rows=74834)-> Nested loop inner join  (cost=95578 rows=74834)-> Filter: (employees.emp_no > 10005)  (cost=29971 rows=149667)-> Index range scan on employees using PRIMARY over (10005 < emp_no)  (cost=29971 rows=149667)-> Filter: (salaries.salary > 70000)  (cost=0.338 rows=0.5)-> Single-row index lookup on salaries using PRIMARY (emp_no=employees.emp_no, from_date=DATE'1992-08-04')  (cost=0.338 rows=1)

这里的执行策略是:

  • 1. 通过employees.emp_no上的索引查询 10005 < emp_no,然后筛选出 10005 < emp_no的数据
  • 2. 对外层表的每条数据通过salaries.emp_no和salaries.from_data上的联合索引 查符合条件emp_no=employees.emp_no, from_date=DATE'1992-08-04'的数据,然后筛选出 salaries.salary > 70000的数据
  • 3. 把内外层表的数据做连接操作
  • 4. 匹配完所有数据后,把结果集按照salaries.salary字段进行排序,然后返回前10条

优化思路:上面执行策略显然没有使用增量策略,是匹配完所有的数据后再进行排序后返回,于是我们可以在salaries.salary字段上添加索引,使查询时,可以把salaries作为外层表进行查询,就可继续使用增量策略,查询到10条后返回

优化后的执行策略:

-> Limit: 10 row(s)  (cost=443853 rows=10)-> Nested loop inner join  (cost=443853 rows=1.42e+6)-> Index range scan on salaries using idx_salary over (70000 < salary), with index condition: ((salaries.from_date = DATE'1992-08-04') and (salaries.emp_no > 10005) and (salaries.salary > 70000)) (cost=284192 rows=1.42e+6)-> Single-row index lookup on employees using PRIMARY (emp_no=salaries.emp_no)  (cost=0.25 rows=1)

这里的执行策略是:

  • 1. 根据索引 salaries.salary字段上的索引查询 70000 < salary的数据,然后更具条件(salaries.from_date = DATE'1992-08-04') and (salaries.emp_no > 10005) and (salaries.salary > 70000))进行筛选
  • 2. 根据employees.emp_no 上的索引,查找符合emp_no=salaries.emp_no 的数据
  • 3. 进行join操作,当收集到10条数据后会直接返回

 2.2 案例二

-- 耗时4200ms
select * from employeesleft join salaries on employees.emp_no = salaries.emp_noleft join dept_emp on dept_emp.emp_no = employees.emp_no 
where dept_emp.dept_no not in (select dept_no from departments where dept_name > 'AAA')
order by employees.hire_date 
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)
-- departments: UNIQUE KEY (dept_name)

尝试使用增量策略 -- 4500ms 

 给 employees.hire_date加索引:

-> Limit: 10 row(s)  (cost=851920 rows=10)-> Filter: <in_optimizer>(dept_emp.dept_no,dept_emp.dept_no in (select #2) is false)  (cost=851920 rows=10.4)-> Nested loop left join  (cost=851920 rows=10.4)-> Nested loop left join  (cost=135044 rows=9.42)-> Index scan on employees using idx_hire_date  (cost=776e-6 rows=1)-> Index lookup on salaries using PRIMARY (emp_no=employees.emp_no)  (cost=0.451 rows=9.42)-> Index lookup on dept_emp using PRIMARY (emp_no=employees.emp_no)  (cost=0.254 rows=1.1)-> Select #2 (subquery in condition; run only once)-> Filter: ((dept_emp.dept_no = `<materialized_subquery>`.dept_no))  (cost=2.9..2.9 rows=1)-> Limit: 1 row(s)  (cost=2.8..2.8 rows=1)-> Index lookup on <materialized_subquery> using <auto_distinct_key> (dept_no=dept_emp.dept_no)-> Materialize with deduplication  (cost=2.8..2.8 rows=9)-> Filter: (departments.dept_name > 'AAA')  (cost=1.9 rows=9)-> Covering index scan on departments using dept_name  (cost=1.9 rows=9)

可以看到索引生效了,但是耗时确没减少还略有增加,这是因为,符合条件的数据,没有10条,这种情况下,增量和全量没区别,走索引还会增加回表的开销所以,走索引不一定快,需要具体分析执行逻辑。

拆分sql 

在上面的sql的执行策略中,对于每个可能的结果都是最后判断是否满足,not in,并且所有满足条件的数据又不超过10条,就会大致大量的数据都会去使用dept_emp.dept_no和not in中的值比较,又因为dept_emp在第三层循环,所以回导致同一个dept_emp.dept_no会去not in中比较很多次,这里我们可以通过把dept_emp当作外层表,先满足 not in 条件再向后匹配。当not in中包含的是具体的常量值,MySQL通常就会在查询中首先处理这些常量值

-- 耗时2ms,返回 'd009','d005','d002','d003','d001','d004','d006','d008','d007'
select dept_no from departments where dept_name > 'AAA';-- 把第一步返回的结果直接做为第二步查询的条件,耗时3ms
select * from employeesleft join salaries on employees.emp_no = salaries.emp_noleft join dept_emp on dept_emp.emp_no = employees.emp_no 
where dept_emp.dept_no not in ('d009','d005','d002','d003','d001','d004','d006','d008','d007')
order by employees.hire_date 
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)    
-- departments: UNIQUE KEY (dept_name)

3. 常用优化技巧

  • 1. 添加合适的索引:尽量保证order by,join,group by,where有合适的索引可以用
  • 2. 在列上调用函数或者做计算会导致索引用不了:
    -- 列上做计算,耗时400ms
    select * from salaries where emp_no - 10 = 11111;
    -- where条件值上做计算,耗时3ms
    select * from salaries where emp_no = 11121;
  • 3. 使用limit限制返回条数:有利于发挥增量查询的优势
  • 4. 避免使用select *,而是明确列出实际需要的列:减少MySQL需要抓取的列,提升性能;有可能消除回表的开销 如果查询走了二级索引,用select * 必须回表;有时候,实际需要的列,索引上都有,就可以节省掉回表的开销

这篇关于深入理解MySQL慢查询优化(3) -- 案例实操的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1131746

相关文章

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;