MySQL优化理论学习指南
文章目录
前言
SQL优化策略如下图所示:
系统性能状态
常见的系统性能状态如下:
状态 | 说明 |
---|---|
connections | 连接MySQL服务器的次数 |
uptime | MySQL服务器的上线时间 |
slow_queries | 慢查询的次数 |
innodb_rows_read | select语句返回的行数 |
Innodb_rows_inserted | insert语句插入的行数 |
Innodb_rows_updatedupdate | 语句更新的行数 |
Innodb_rows_deleted | delete语句删除的行数 |
com_select | 查询操作的次数 |
com_insert | 插入操作的次数 |
com_update | 更新操作的次数 |
com_delete | 删除操作的次数 |
last_query_cost | 查询用到的页数 |
慢查询日志
慢查询日志用于记录运行时间超过long_quert_time
以及扫描行数超过min_examined_row_limit
的SQL。默认情况下MySQL没有开启慢查询日志,只建议在调优的时候打开:
set global slow_query_log:='ON';
拥有慢查询日志后可以使用慢查询日志分析工具mysqldumpslow
进行分析:
mysqldumpslow [options] [logName]
- a #不将数字抽象成N,字符串抽象成S
- s <order> #排序方式
c #访问次数
l #锁定时间
r #返回记录
t #查询时间
al #平均锁定时间
ar #平均返回记录数
at #平均返回查询时间
ac #平均查询次数
- t #返回前多少条的数据
Explain
使用Explain关键字可以模拟优化器执行SQL查询语句,从而获取SQL的执行计划,由此可以分析查询语句或者表结构的性能瓶颈。
explain <DQL>
id
查询语句中每有一个select
子句就会有一个id,它表示查询中执行select
子句的顺序:
- id如果相同,可以认为是一组,从上往下顺序执行
- 在所有组中,id值越大,优先级越高,越先执行
- id号每个号码,表示一趟独立的查询, 一个sql的查询趟数越少越好
select_type
select
子句对应的查询类型,该字段指示了select
子句在整个查询中对应的角色。
值 | 说明 |
---|---|
simple | 简单的查询,不包含子查询和联合查询 |
primary | 包含子查询的最外层的或包含联合查询的最左边的select 子句对应的查询类型 |
union | 在联合查询中除最左边select 子句剩下的select 子句对应的查询类型 |
union result | 联合查询时对临时表的查询类型 |
subquery | 如果包含子查询的语句不能被优化为连接查询,并且子查询是不相关子查询,那么子查询的第一个select 子句就是该类型 |
dependent subquery | 如果包含子查询的语句不能被优化为连接查询,并且子查询是相关子查询,那么子查询的第一个select 子句就是该类型 |
dependent union | 在包含联合查询的大查询中,如果各个小查询都依赖于外层查询,那么除了最左边的那个查询之外,其余小查询都为该类型 |
derived | 包含派生表的查询,该派生表对应的查讯类型 |
materialized | 当查询优化器执行包含子查询的语句时,将子查询物化之后与外层查询进行连接查询时,该子查询对应的类型 |
table
table表示正在查询的表名
type
不论查询语句有多复杂,里边包含了多少个表 ,到最后也是需要对每个表进行单表访问的,所以MySQL规定explain语句输出的每条记录都对应着某个单表的访问方法:
名称 | 说明 |
---|---|
system | 表中只有一行记录并且该表使用的存储引擎的统计数据是精确的 |
const | 通过主键或唯一索引与常数的等值比较时的访问类型,如果唯一索引查询列为null值时,由于唯一索引不限制null值数量,所以此时的访问方式为ref而不是const |
eq_ref | 在连接查询时,如果被驱动表是通过主键或唯一索引等值匹配的方式进行访问的(如果是联合索引的话所有索引列都必须等值匹配),那么被驱动表的访问方式就是该类型 |
ref | 将普通的二级索引与常数进行比较时的访问类型(对于包含多个列的二级索引来说,只要左边的列连续的等值比较就可以) |
ref_or_null | 再ref的基础上,如果索引列的值可以为null时的访问方法 |
index_merge | 索引合并时的访问方法 |
unique_subquery | 在包含in 子查询的查询语句中,如果优化器决定将in 子查询转换为exists 子查询,而且子查询可以使用主键进行等值匹配的话,那么该子查询的访问方式就是unique_subquery |
range | 使用索引时,对应的扫描空间为若干个单点扫描区间或者范围扫描区间。 |
index | 可以使用索引覆盖但需要扫描全部索引记录时的访问方法。 |
all | 全表扫描 |
possible_keys
查询中可能使用的索引。
key
查询中实际使用的索引,
key_len
实际使用的索引的字节数,主要针对联合索引,值越大越好。
ref
当使用索引列等值查询时,与索引列进行等值匹配的对象信息。
rows
根据表统计信息及索引情况大致估算出要读取的行数。
filtered
某个表经过搜索条件过滤后剩余记录条数的百分比。
extra
extra用于提供一些额外信息,通过这些额外信息可以更准确的理解MySQL是如何执行SQL的。
value | 说明 |
---|---|
using_where | 当使用全表扫描并且where子句有针对该表的条件时或使用索引进行查询但where子句中还有索引列之外的列时 |
using_filesort | 使用了文件排序(排序操作无法使用索引) |
using_temporary | 使用了临时表 |
using_index | 使用了覆盖索引 |
Using index condition | 使用了索引下推 |
索引优化
索引创建原则
- 如果某个字段是唯一性的,那么就可以直接创建一个唯一索引。不要以为唯一索引影响了
insert
速度,这个速度损耗可以忽略,但提高查找速度是明显的。 - 频繁作为
where
查询条件的字段。 - 经常
group by
和order by
的字段。 -
distinct
字段需要创建索引。 - 多表连接时,对连接条件创建索引。
- 使用类型小的列建立索引
- 对
varchar
字段创建前缀索引,前缀索引的长度根据count(distinct left(列名, 索引长度))/count(*)
公式选择选择性大的长度。 - 使用最频繁的列放到联合索引的左侧
- 在多个字段都要创建索引的情况下,联合索引优于单值索引
- 数据量小的表最好不要使用索引
- 有大量重复数据的列上不要建立索引
- 避免对经常更新的表创建过多的索引
- 不要定义冗余或重复的索引
索引失效情况
- 只能从多列索引的最左列开始使用,并且不能跳过索引列。索引列的条件只能使用
and
连接。 - 计算、函数、类型转换(自动或手动)会导致索引失效。
- 范围条件右边的多列索引列会失效。
- 不等于号索引失效。
-
is null
可以使用索引,is not null
无法使用索引。 -
like
以通配符%
开头索引失效。 -
or
前后存在非索引的列,索引失效。
覆盖索引
索引只能搜索一个列然后根据这个列进行回表,如果查询的列只包含索引列那么就不需要回表操作了,因此一个索引包含了满足查询结果的数据就叫做覆盖索引。在使用覆盖索引时就用不上覆盖索引对查询性能的优化了。
索引下推
索引下推是一种在存储引擎层使用索引过滤数据的一种优化方式。它可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。在不使用索引下推的数据扫描过程如下:
- 存储引擎:将where中能够使用索引查找的数据回表返回给服务器。
- 服务器:将取出的数据通过where的其它条件再次过滤。
在使用索引下推的数据扫描过程如下:
- 存储引擎:将where中能够使用索引的区间确定,在区间内根据where条件对索引列进行过滤,过滤后再回表取数据返回给服务器。
索引下推的使用条件如下:
- 针对于多列索引才有意义。
- where条件字段必须在索引列。
- 当SQL使用覆盖索引时,不支持索引下推优化方法。
索引合并
一般而言不能混合使用多个索引,而索引合并是指多个索引之间满足某些条件时的合并行为,这就提高了查询效率。
交集合并
交集合并是指对从不同索引中获取到的记录取交集,只为交集中的值进行回表操作,如果每个使用到的索引都是二级索引,则要求从每个索引中取到的记录都是按主键值排列的。
并集合并
并集合并是指对从不同索引中获取到的记录取并集,只为并集中的值进行回表操作,如果每个使用到的索引都是二级索引,则要求从每个索引中取到的记录都是按主键值排列的。
排序并集合并
排序并集合并在并集合并的基础上,如果每个使用到的索引都是二级索引,并且从每个索引中取到的记录不是按主键值排列的,那么就先进行一次排序,然后再进行合并。
查询优化
连接查询的原理及优化
多表访问通过连接实现,从本质上来说,连接就是把各个表中的记录都取出来依次进行比较,并把匹配后的组合发送给客户端。连接查询的步骤如下:
- 首先确定第一个需要查询的表,这个表称为驱动表。
- 从驱动表中获取的每一条记录,都需要到被驱动表中找到匹配的记录然后加入结果集。
- 若果有多个表连接,那么上两个表的结构集作为驱动表连接下一个表,就这样往复执行。
也就是说驱动表只会访问一次,而被驱动表会访问多次。不同连接类型的查询区别如下:
- 内连接:内连接的驱动表和被驱动表由优化器决定,若驱动表中的记录在被驱动表中没有找到记录,则不会添加到结果集中。
- 外连接:即使驱动表中的记录在被驱动表中没有找到记录,也会添加到结果集中。
- 左外连接右外连接的区别是选取哪张表作为驱动表
- where子句在内连接和外连接中相同,只要不满足条件就无法加入结果集。
- on子句在内连接中被当作where字句看待,在外连接中,如果在被驱动表中无法找到on子句中的过滤条件,那么驱动表的记录仍会添加到结果集,对应的被驱动表的各个字段使用null值填充。
连接查询的优化策略如下:
- 使用小表驱动大表,可以减少连接次数。
- 如果只能创建一个索引则要在被驱动表的连接字段创建。
子查询的原理及优化
不相关的标量子查询、行子查询的执行方式:
- 首先单独执行子查询
- 然后将子查询得到的结果作为外层查询的参数
相关的标量子查询、行子查询的执行方式:
- 先从外层查询获取一条记录
- 然后从这条记录找出子查询相关的值,然后执行子查询
- 最后根据子查询的查询结果来检测外层查询
where
子句是否成立,成立就将记录加入结果集,否则就丢弃。 - 往复循环,直到外层查询获取不到记录为止。
子查询的优化策略如下:
- 执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询。子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。因此尽量使用连接查询替代子查询。
order by和group by优化
- 在
order by
子句避免使用 FileSort 排序。 - 尽量使用索引完成
order by
排序。如果where
和order by
后面是相同的列就使用单索引列,如果不同就使用联合索引。 - 使用多列索引时顺序错、方向反都会导致索引失效。
- 当
where
与group by
字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。 - 无法使用索引时需要对 FileSort 方式进行调优。
FileSort有两种排序方式:
- 双路排序(4.1之前):先将排序字段从磁盘加载到sort_buffer进行排序,排序完成后再根据排序列表将完成数据从磁盘读取到内存。
- 单路排序:从磁盘读取查询需要的所有列 ,按照排序字段在sort_buffer进行排序,然后扫描排序后的列表进行输出。
因此FileSort的优化策略如下:
- 通过
sort_buffer_size
变量提高排序缓存的大小。 - 通过
max_length_for_sort_data
变量选择排序方式,如果返回列的总长度值大于该变量,则使用双路排序,否则使用单路排序。 - 不要使用
select *
,否则就会全部使用双路排序,双路排序I/O次数多。
group by
的优化策略几乎和order by
一致。
limit优化
- limit不会使用索引,要想使用索引可以根据主键排序分页,然后根据主键关联回原表查询所需要的其他列内容。
- 如果主键是自增的,可以把分页查询转换成某个位置的查询 。
exists和in优化
如果子查询的表相较于外表来说是一个小表,那么就使用in
,否则就是用exists
改造。
count(常量)和count(具体字段)优化
-
count(常量)
是一个统计行数的行为,InnoDB会扫描表但不会取数据。 -
count(具体字段)
是一个统计非空字段的行为,InnoDB会扫描表然后取数据,并且会判断数据是否为空。 - 如果要使用索引尽量使用二级索引,因为二级索引加载到内存的数据比聚簇索引小。
- 如果没有指定具体索引字段并且表中存在二级索引的情况下,
count(常量)
会自动选择一个二级索引使用。
select(*)优化
在表查询中,建议明确字段,不要使用 *
作为查询的字段列表,因为MySQL 在解析的过程中,会通过查询数据字典将*
按序转换成所有列名,这会大大的耗费资源和时间,并且无法使用无法使用覆盖索引。
数据库结构优化
冷热数据分离
MySQL一个表最多存储4096列,并且每一行的数据大小不能超过65535字节。可以将一个包含多个字段的表分成两个或多个较小的表,一些表包含经常使用的数据,另一些表包含不经常使用的数据,这样可以减少磁盘I/O,也可以更有效的利用缓存,较少冷数据的读入。
增加中间表
对于经常联合查询的表,可以建立中间表提高查询效率,通过建立中间表把经常需要联合查询的数据插入到中间表,然后将原来的联合查询改为对中间表的查询,以此来提高效率。
增加冗余字段
设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅。但是,合理地加入冗余字段可以提高查询速度。表的规范化程度越高,表与表之间的关系就越多,需要连接查询的情况也就越多。尤其在数据量大,而且需要频繁进行连接的时候,为了提升效率,我们也可以考虑增加冗余字段来减少连接。
优化数据类型
- 遇到整数类型的字段可以用 INT 型,对于非负型的数据来说,要优先使用无符号整型 UNSIGNED 来存储。
- 既可以使用文本类型也可以使用整数类型的字段,要选择使用整数类型。
- 避免使用TEXT、BLOB数据类型,MySQL内存临时表不支持这样的大型数据类型,如果查询中含有这种数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行,并且MySQL还会进行二次查询,使SQL性能变差。如果非要使用,可以把这种数据单独放到一张表中。
- 避免使用enum类型,修改enum值需要使用alter语句,并且order by操作效率低。
- 使用timestamp存储时间,该类型占用空间小并且具有自动赋值和自动更新的特性。
- 使用decimal代替float和double。
使用非空约束
在设计字段的时候,如果业务允许,建议尽量使用非空约束。
大表优化
限定查询的范围
禁止不带任何限制数据范围条件的查询语句。
读写分离
主库负责写,从库负责读。
- 一主一从模式:
- 双主双从模式:
垂直拆分
当数据量级达到 千万级 以上时,有时候我们需要把一个数据库切成多份,放到不同的数据库服务器上,减少对单一数据库服务器的访问压力。如果数据库中的表比较多,可以采用垂直分库的方式将关联的表部署在同一个数据库上。如果表中的数据比较多,可以采用垂直分表的方式,将一张表拆分为多张表。
水平拆分
当但表数据过大时可以采用水平分表的方式将大表分为小表。但分片事务难以解决,跨节点Join性能较差。如果非要分片,建议使用客户端代理的方式而不是中间件代理的方式。