天天看点

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

作者:架构师成长历程

MySQL优化目标

✓减少磁盘IO

可从硬件,是否全表扫描,磁盘临时表,日志、数据块fsync,系统配置等方面着手

✓减少网络带宽

可从返回太多数据,交互次数过多,库表结构等方面考虑

✓降低CPU消耗

可从排序分组 order by, group by,SQL及索引,聚合函数:max,min,sum...等方面着手

优化流程及思路

关注的指标

➢CPU使用率

• sql查询关键资源指标

• 数据扫描、显式计算

➢IOPS

• 每秒io请求次数

• 物理读写关键资源指标

➢QPS/TPS

• 吞吐量

• 业务压力

➢会话数/活跃会话数

• 应用配置

• 执行效率

➢Innodb逻辑读/物理读

• 反映整体查询效率的引擎指标

➢临时表

• 导致SQL执行效率下降的特殊行为

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

MySQL优化流程

构建完备的监控体系

• 细致合理的告警

• 多维度图形化指标

• 暴露性能缺陷,掌控大规模资源

分析定位问题

• 异常时间区间

定性分析

监控你的系

• System log、DB Error Log 统

• Slow Log

• SQL执行统计

• session

SQL优化原则与方法

原则

⚫减少访问量:数据存取是数据库系统最核心功能,所以IO是数据库系统中最容易出现性能瓶颈,减少SQL访

问IO量是SQL优化的第一步;数据块的逻辑读也是产生CPU开销的因素之一。

• 减少访问量的方法:创建合适的索引、减少不必访问的列、使用索引覆盖、语句改写。

⚫减少计算操作:计算操作进行优化也是SQL优化的重要方向。SQL中排序、分组、多表连接操作等计算操作

都是CPU消耗的大户。

• 减少SQL计算操作的方法:排序列加入索引、适当的列冗余、SQL拆分、计算功能拆分。

方法

• 创建索引减少扫描量

• 调整索引减少计算量

• 索引覆盖(减少不必访问的列,避免回表查询)

• SQL改写

• 干预执行计划

原理剖析

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

B+ Tree index

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

MySQL SQL执行过程

1.客户提交一条语句

2.先在查询缓存查看是否存在对应的缓存数据,如

有则直接返回(一般有的可能性极小,因此一般建

议关闭查询缓存)。

3.交给解析器处理,解析器会将提交的语句生成一

个解析树。

4.预处理器会处理解析树,形成新的解析树。这一

阶段存在一些SQL改写的过程。

5.改写后的解析树提交给查询优化器。查询优化器

生成执行计划。

6.执行计划交由执行引擎调用存储引擎接口,完成

执行过程。这里要注意,MySQL的Server层和

Engine层是分离的。

7.最终的结果由执行引擎返回给客户端,如果开启

查询缓存的话,则会缓存。

MySQL SQL执行过程

SQL执行顺序:

(8) SELECT (9) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP <group_by_list> (6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition> (10) ORDER BY <order_by_list> (11) LIMIT <limit_number> 18           

MySQL优化器与执行计划

查询优化器

⚫负责生成 SQL 语句的有效执行计划的数据库组件

⚫优化器是数据库的核心价值所在,它是数据库的“大脑”

⚫优化SQL,某种意义上就是理解优化器的行为

⚫优化的依据是执行成本(CBO)

⚫优化器工作的前提是了解数据,工作的目的是解析SQL,生成执行计划

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

MySQL优化器与执行计划

查询优化器工作过程

1.词法分析、语法分析、语义检查

2.预处理阶段(查询改写等)

3.查询优化阶段(可详细划分为逻辑优化、物

理优化两部分)

4.查询优化器优化依据,来自于代价估算器估

算结果(它会调用统计信息作为计算依据)

5.交由执行器执行

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路
(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

查看和干预执行计划

执行计划:

➢ explain [extended] SQL_Statement 优化器开关:

➢ show variables like 'optimizer_switch'

processlist

➢ show [full] processlist

➢ information_schema.processlist copy to tmp table:出现在某些alter table语句的copy table操作

Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表

从内存存储转为磁盘存储以此节省内存

converting HEAP to MyISAM:线程正在转换内部MEMORY临时表到磁盘MyISAM临

时表

Creating sort index:正在使用内部临时表处理select查询

Sorting index:磁盘排序操作的一个过程

Sending data :正在处理SELECT查询的记录,同时正在把结果发送给客户端

Waiting for table metadata lock: 等待元数据锁

...

常规优化策略

SELECT优化-order by

order by查询的两种情况:

• Using index。MySQL直接通过索引返回有序记录,不需要额外的排序操作,操作效率较高

• Using filesort。无法只通过索引获取有序结果集,需要额外的排序,某些特殊情况下,会出现

Using temporary

优化目标:尽量通过索引来避免额外的排序,减少CPU资源的消耗

✓where条件和order by使用相同的索引

✓order by的顺序和索引顺序相同

✓order by的字段同为升序或降序

注:当where条件中的过滤字段为覆盖索引的前缀列,而order by字段是第二个索引列时,只有

where条件是const匹配时,才可以通过索引消除排序,而between...and或>?、<?这种range匹配

都无法避免filesort操作

SELECT优化-order by

当无法避免filesort操作时,优化思路就是让filesort的操作更快

排序算法:

• 两次扫描算法。两次访问数据,第一步获取排序字段的行指针信息,在内存中排序,第二步根据

行指针获取记录

• 一次扫描算法。一次性取出满足条件的所有记录,在排序区中排序后输出结果集。是采用空间换

时间的方式

注:需要排序的字段总长度越小,越趋向于第二种扫描算法,MySQL通过

max_length_for_sort_data参数的值来进行参考选择

优化策略:

1、适当调大max_length_for_sort_data这个参数的值,让优化器更倾向于选择第二种扫描算法

2、只使用必要的字段,不要使用select *的写法

3、适当加大sort_buffer_size这个参数的值,避免磁盘排序的出现(线程参数,不要设置过大)

SELECT优化-Subquery

➢子查询会用到临时表,需尽量避免

➢可以使用效率更高的join查询来替代

优化策略:

等价改写、反嵌套

如下SQL:

select * from customer where customer_id not in (select customer_id from payment)           

改写形式:

select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null 26           

SELECT优化-limit

➢分页查询,就是将过多的结果在有限的界面上分好多页来显示。

➢其实质是每次查询只返回有限行,翻页一次执行一次。

优化目标:

1、消除排序

2、避免扫描到大量不需要的记录

SQL场景(film_id为主键):

select film_id,description from film order by title limit 10000,20           

此时MySQL排序出前10020条记录后仅仅需要返回第10001到10020条记录,前

10000条记录造成额外的代价消耗

SELECT优化-limit

优化策略一:

覆盖索引

alter table film add index idx_lmtest(title,description);           

• 记录直接从索引中获取,效率最高

• 仅适合查询字段较少的情况

优化策略二:

SQL改写

select a.film_id,a.description from film a inner join (select film_id from film order by title limit 1000,20) b on a.film_id=b.film_id;           

• 优化的前提是title字段有索引

• 思路是从索引中取出20条满足条件记录的主键值,然后回表获取记录

SELECT优化-or/and condition

• and结果集为关键字前后过滤结果的交集

• or结果集为关键字前后分别查询的并集

OR

• and条件可以在前一个条件过滤基础上过滤

• or条件被处理为UNION,相当于两个单独条件的查询

• 复合索引对于or条件相当于一个单列索引

处理策略:

✓and子句多个条件中拥有一个过滤性较高的索引即可

✓or条件前后字段均要创建索引

✓为最常用的and组合条件创建复合索引

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路
(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

join优化

• Nested-Loop Join算法
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
}           

• 关联字段索引:每层内部循环仅获取需要关心的数据

• 引申算法:Bloack Nested-Loop

• 小表驱动原则:减少循环次数

• 小表:返回结果集较少的表

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

join优化

• 关联字段索引的必要性

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路
(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

join优化

• 小表驱动原则

忽略b表的索引,使b表作为驱动表:

(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路
(干货)MySQL 查询性能优化以及SQL诊断调优原则、原理及思路

忽略a表的索引,使a表作为驱动表:

insert优化

优化策略一:

减少交互次数

如批量插入语句:

insert into test values(1,2,3); insert into test values(4,5,6); insert into test values(7,8,9);           

可改写为如下形式:

insert into test values(1,2,3),(4,5,6),(7,8,9) ...;            

优化策略二:

文本装载方式

通过LOAD DATA INFILE句式,从文本装载数据,通常比insert语句快20倍

06 小结

MySQL查询优化

✓优化的目的是让资源发挥价值

✓SQL和索引是调优的关键,往往可以起到“四两拨千斤”的效果

✓充分了解核心指标,并构建完备的监控体系,这是优化工作的前提

✓SQL优化的原则是减少数据访问及计算

✓常用的优化方法主要是调整索引、改写SQL、干预执行计划

✓innodb的表是典型的IOT,数据本身是B+ tree索引的叶节点

✓扫描二级索引可以直接获取数据,或者返回主键id

✓优化器是数据库的大脑,我们要了解优化器,并观测以及干预MySQL的行为

继续阅读