外贸网站建设公司方案,青海网站设计企业,wordpress获取title,网站制作 深圳大家好#xff0c;我是 方圆。这篇主要介绍对慢 SQL 优化的一些手段#xff0c;而在讲解具体的优化措施之前#xff0c;我想先对 EXPLAIN 进行介绍#xff0c;它是我们在分析查询时必要的操作#xff0c;理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读我是 方圆。这篇主要介绍对慢 SQL 优化的一些手段而在讲解具体的优化措施之前我想先对 EXPLAIN 进行介绍它是我们在分析查询时必要的操作理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读在下文中规定类似 key1 的表示二级索引key_part1 表示联合索引的第一部分unique_key1 则表示唯一二级索引primary_key 表示主键索引。高性能MySQL实战一表结构 和 高性能MySQL实战二索引 是本文的前置知识欢迎大家阅读。原文收录在我的 Github: enthusiasm 中欢迎Star和获取原文。
1. Explain 详解
Explain 是我们在对慢 SQL 进行优化前常用语句它能分析具体的查询计划从而让我们有目的地去进行优化。本节则主要是让大家看懂 Explain 查询结果的每一列是干啥用的我们先简要的来看一下各个列的作用
列名描述id在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id。在连接查询中记录的 id 值都是相同的在多个 SELECT 关键字的查询中查询优化器可能会对子查询进行优化使得多条 SELECT 记录的 id 值相同select_type查询类型table表名partitions匹配的分区信息type针对单表的访问方法possible_keys可能用到的索引key实际使用的索引key_len实际使用的索引长度ref当使用索引列等值查询时与索引列进行等值匹配的对象信息rows预估的需要读取的记录条数filtered针对预估的需要读取的记录经过搜索条件过滤后剩余记录条数的百分比。在单表查询中没什么意义在连表查询中可以计算出在驱动表执行完查询后还需要对被驱动表执行多少次查询Extra额外的备注信息
其中大部分列在描述信息中已经解释的足够清楚下面我们主要对一些必要的列进行详述
1.1 select_type SIMPLE: 查询语句中 不包含 UNION 或者子查询 的查询 PRIMARY: 对于包含 UNION、UNION ALL 或者子查询的大查询来说它是由几个小查询组成的其中最左边查询的 select_type 是 PRIMARY UNION: 对于包含 UNION 和 UNION ALL 的大查询来说它是由几个小查询组成的其中除了最左边的那个小查询以外其余小查询的 select_type 都是 UNION UNION RESULT: MySQL 选择使用临时表来完成 UNION 查询的去重针对该临时表的查询的 select_type 是 UNION RESULT DEPENDENT UNION: UNION 查询相关的类型 SUBQUERY, DEPENDENT SUBQUERY, MATERIALIZED: 子查询相关的类型 DERIVED: 在包含派生表的查询中以物化派生表的方式执行的查询
1.2 type const: 通过 主键 或 唯一二级索引 与常数的等值比较来定位 一条记录如果是联合索引则只有在索引列的每一个列都与常数进行等值比较时这个 const 访问才有效 ref: 通过 二级索引 与常数进行等值比较形成的扫描区间为单点扫描区间的访问 ref_or_null: 相比于 ref 多扫描了一些值为 NULL 的二级索引列 range: 使用索引执行查询时对应的扫描区间为 若干个单点扫描区间或者范围扫描区间 的访问 index: 使用覆盖索引并扫描全部二级索引的访问。另外当通过全表扫描对使用 InnoDB 引擎的表执行查询时如果添加了 ORDER BY 主键 的语句那么该语句在执行时也会被认为是 index 访问 fulltext: 全文索引访问 all: 全表扫描 eq_ref: 执行 连接查询 时如果被驱动表是通过 主键或者不允许为 NULL 的唯一二级索引 等值匹配的方式进行访问 在外连接中ON 语句是专门为 “驱动表中的记录在被驱动表中找不到匹配纪录时对应的被驱动表记录的各个字段使用 NULL 来填充” 场景提出的在内连接中ON 和 WHERE 的作用一致 unique_subquery: 针对的是一些包含 IN 子查询的查询语句如果查询优化器决定将 IN 子查询转换成 EXISTS 子查询而且子查询在转换之后 可以使用主键或者为允许为 NULL 的唯一二级索引进行等值匹配 index_subquery: 与 unique_subquery 类似只不过在访问时使用的是 普通二级索引 index_merge: 存在索引合并 system: 当表中只有一条记录并且使用的存储引擎的统计数据是精确的如 MyISAM 和 MEMORY
1.3 ref
当访问方法是 const、ref、ref_or_null、eq_ref、unique_subquery 和 index_subquery 其中之一时ref 列展示的是与索引列进行 等值匹配 的东西是啥 const: 表示是一个常数 func: 表示是一个函数 DBName.TableName.columnName: 表示某个数据库某个表中的某个列
1.4 Extra No Table used: 查询语句中没有 FROM 子句 Impossible WHERE: 查询语句中的 WHERE 条件始终为 FALSE No matching min/max row: 当查询中有 min 或 max 聚合函数时但是没有记录符合 WHERE 条件 Using Index: 使用了覆盖索引 Using Index condition: 在执行查询语句时使用了索引条件下推特性 索引条件下推它是针对 二级索引 查询条件做的优化在对二级索引条件进行判断时会将所有该索引相关列的条件都判断完成后符合条件再执行回表操作不符合条件则不再执行回表这样做减少了回表操作的次数从而减少了 I/O。 如下例子 select * from specific_table where key1 ‘a’ and key1 like ‘%b’; 索引条件下推会将 key1 所有条件判断完而不是只判断完 key1 ‘a’ 就去回表。 Using join buffer(Block Nested Loop): 表示在执行连接查询时被驱动表不能有效地利用索引加快访问速度而是使用内存块来加快查询 Using intersect(index_name, …)、Using union(index_name, …) 和 Using sort union(index_name, …): 表示使用 Intersection 索引合并、Union 索引合并或 Sort-Union 索引合并执行查询下文有介绍 Using filesort: 文件排序排序无法使用到索引只能在内存或者磁盘中进行排序 Using temporary: 查询时使用到了内部临时表
2. 优化考虑点
基于访问类型优化
在前文中我们已经详细介绍了 EXPLAIN 语句中的访问类型type如果一个查询的访问类型并不是我们预期的那么最简单直接的解决办法是为搜索条件列 增加合适的索引。
减少扫描行数的优化
在有些情况下简单地增加索引并不能解决问题比如执行如下 SQL
select name, count(name) from specific_table group by key1;这条 SQL 执行完毕后可能只返回几行数据但是因为有 COUNT 聚合函数需要扫描的数据可能会有成千上万行这取决于表中数据量总数。对于这种 扫描大量数据却只返回少数行 的情况通常可以通过 增加单独的汇总表 进行优化当然这需要在应用层增加相应的逻辑对汇总表的数据进行维护。
除此之外还可以通过 重写复杂查询 的方式来优化下面我们对重写查询时需要考虑的方向进行介绍
一个复杂查询还是多个简单查询
这是一个值得考虑的问题。将复杂查询拆成多个简单查询尽可能地减少数据库的工作并将一些处理逻辑拿到应用层处理因为 MySQL 处理简单查询很高效所以通常情况下这么做能够提高效率。
切分处理
在实际工作中对数据量较大的数据库表进行结转或删除时通常会采用 切分处理 的方法将一个大查询分成小查询每个查询的作用是一样的只不过操作的数据量不同各个小查询执行完毕后大查询的任务也就处理完成了。
一次性结转大量数据可能会锁住很多数据、占满整个事务日志、耗尽系统资源和阻塞很多小的查询等为了避免这种情况通常在一次数据结转任务中只操作 一万条左右 数据这样对服务器影响最小而且可以在每次结转完成时都 暂停一会儿 再去执行下一次任务这样做可以将压力分散到一个比较长的时间段中大大降低对服务器的影响和减少持有锁的时间。
优化联结查询
阿里巴巴开发手册中提到过一点联表查询时联表的数量不超过 3 个。如果联表过多我们需要将其拆成多个查询或多个单表查询单表查询的 缓存效率会更高查询被分解后查询间的锁竞争会减少。除此之外联表查询还需要注意以下两点 确保 ON 或者 USING 子句中的列上有索引 确保任何 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列这样 MySQL 才有可能使用索引来优化这个查询
IN() 条件与 OR 条件
一般情况下我们认为 IN() 完全等价于多个 OR 条件但是在 MySQL 中这两者是有区别的。MySQL 在处理 IN() 条件时会将列表中的数据先进行排序然后通过二分查找的方式来确定列表中的值是否满足条件这是一个时间复杂度为 O(logn) 的操作如果等价地转换成 OR 查询它的时间复杂度为 O(n)所以在 IN() 条件中有大量取值时MySQL 的处理速度会更快。
查询时索引是否失效 如果不是按照索引的最左列开始查找则无法使用索引 如果跳过了联合索引中的列则无法使用索引或只能使用部分索引。有如下 SQL其中 key_part1、key_part2 和 key_part3 是按顺序的联合索引 select key_part1, key_part2, key_part3 from specific_table
where key_part1 1 and key_part3 3;在查询条件中略过了 key_part2那么只能使用到索引的第一列如果略过的是 key_part1 那么就无法使用到这个联合索引了 如果查询中有某列的范围查询则其右边所有列都无法使用索引优化查询或排序。针对这种情况如果范围查询列值的数量有限那么可以通过 使用 OR 连接的多个等值匹配来替代范围查询 如果在搜索条件中列名不以列名的形式单独出现而是使用了表达式或者函数那么无法使用索引如下 SQL 所示key1 列以 key1 * 2 的形式出现不会使用到索引 select * from specific_table where key1 * 2 4;如果针对变长字段使用 % 开头的模糊查询时则不会使用索引。这个比较好理解因为 MySQL 对字符串的排列是按照一个个字符排序的在开头使用 % 则无法完成比较只能使用全表扫描了
排序时索引是否失效 如果 ORDER BY 语句后面的列的顺序没有按照联合索引的列顺序给出则无法使用索引 如果发生 ASC、DESC 混用则无法使用索引 有如下 SQL其中 key_part1 和 key_part2 是按顺序的联合索引执行时不能使用索引 select key_part1, key_part2 from specific_table
order by key_part1, key_part2 desc;在 MySQL 8.0 版本可以支持 ASC 和 DESC 混用使用索引 如果排序列包含非同一索引的列则无法使用到索引如下 SQL 所示 select id, key1, key2 from specific_table order by key1, key2;因为它们非同一索引在 key1 相同的情况下是不会按照 key2 列进行排序的所以用不到索引 如果排序列是某个联合索引的索引列但是这些排序列在联合索引中并不连续那么也无法使用到索引。如下 SQL 所示因为该联合索引在按照 key_part1 排序后是没有再按照 key_part3 进行排序的所以无法使用索引 select key_part1, key_part3
from specific_table
order by key_part1, key_part3;如果排序列不是以单独列名的形式出现在 ORDER BY 语句中则无法使用索引。如下 SQL 所示在排序时使用了函数所以无法使用索引 select id, key1, key2 from specific_table order by upper(key1)索引列不为空的优化
当需要 Min() 和 Max() 操作时索引列不为空可以让它们更高效。比如要找到某一列的最小值只需要查询对应 B-Tree 索引的最左端记录查询优化器会将这个表达式看做一个常数对待而且能够在 ESPLAIN 结果的 Extra 列中发现 “Select tables optimized away”。
重复索引和冗余索引
重复索引指的是在相同的列上按照相同顺序创建的相同类型的索引如下 SQL 所示
create table specific_table (id int not null primary key,unique key(id)
)engineInnoDB;它在 id 列上创建了两个相同的索引需要将其中的唯一索引移除。
冗余索引通常发生在为表添加新的索引时比如在已有索引column_a再添加一个索引column_a, column_b这就是发生了冗余索引的情况因为第二个联合索引能够发挥和单列索引一样的作用。
大多数情况下都不需要冗余索引我们应该尽量扩展已有的索引而不是创建新的索引。
是否存在索引合并
在多列上独立地创建多个单列索引大部分情况下并不能提高 MySQL 的查询性能。
MySQL 中有一种 “索引合并” 的策略它可以 使用表中的多个单列索引 来定位指定的数据行并将扫描结果进行合并。索引合并的策略有时候非常不错但更多的时候它说明了表中的 索引建的比较糟糕 当查询优化器需要对多个索引合并时通常意味着需要一个包含所有相关列的联合索引而不是多个独立的单列索引 当优化器需要对多个索引做合并操作时通常需要在算法的缓存、排序和合并操作上耗费大量 CPU 和内存资源尤其是当其中有些索引列值的选择性不高且需要合并扫描返回的大量数据时 优化器不会将这些操作算在查询成本中这会使得查询的成本被“低估”导致执行计划还不如进行全表扫描
通常来说我们需要考虑 重建索引 或者 使用 UNION 改写查询。除此之外可以通过修改 optimizer_switch 参数来关闭索引合并功能如下 SQL
SELECT optimizer_switch;-- 改成 index_mergeoff
set optimizer_switch index_mergeoff, ...;还可以使用 IGNORE INDEX 语法让优化器来忽略到某些索引从而避免优化器使用包含该索引的索引合并执行计划
select * from specific_table ignore index(index_name)
where column_name #{value};除了在发生索引合并时考虑忽略索引也需要在执行查询时因无法形成合适的扫描区间达不到减少扫描记录的数量的目的时考虑忽略索引而使用全表扫描。
下面我们介绍三种索引合并的类型让大家对索引合并有一个更加充分的了解它们分别是 Intersection 索引合并、Union 索引合并 和 Sort-Union 索引合并。
Intersection 索引合并
我们看如下查询
select * from specific_table where key1 a and key2 b;我们都能清楚的是在索引列值相同的情况下二级索引记录是按照主键值的大小排序的那么可以将 key1 筛选出的主键值和 key2 筛选出的主键值 取交集根据结果再去执行回表操作这相比于分别对 key1 和 key2 筛选出的主键值都去做回表的开销要低这种情况使用的是 Intersection 索引合并策略。
Union 索引合并
我们看如下查询
select * from specific_table where key1 a or key2 b;将 key1 筛选出的主键值和 key2 筛选出的主键值 取并集再根据结果去做回表操作这种做法被称为 Union 索引合并它可能相比于直接做全表扫描的开销要低。需要注意的是Union 索引合并要求二级索引筛选出的主键值是有序的如果主键值无序则需要考虑 Sort-Union 索引合并。
Sort-Union 索引合并
有如下查询
select * from specific_table where key1 a or key2 b;我们将上述查询条件更改成了范围查询条件现在各索引筛选出的主键值是无序的所以无法使用 Union 索引合并而 Sort-Union 索引合并正是在 Union 索引合并的基础上添加了排序操作将 key1 筛选出的主键值和 key2 筛选出的主键值 进行排序这样就能够继续使用 Union 索引合并了。
优化 COUNT()
当我们需要 统计有值的结果 时需要在 COUNT() 条件内指定列名或 COUNT(0)当我们需要 统计所有的行数 时需要指定为 COUNT(*)它会忽略所有列而直接统计所有行数。明白了这两点之后我们做数据统计能够更清晰的传达意图。
通常来说COUNT() 查询需要扫描大量的数据行才能获得精确的结果所以比较难优化。如果业务场景不要求完全精确我们可以 使用 EXPLAIN 估算的行数 rows 来代替或者我们去掉一些查询条件中的约束删除 DISTINCT 来避免排序操作这些做法都可能使统计查询性能提高。
优化 UNION 查询
在我们使用 UNION 查询时如果不需要消除重复的行一定要使用 UNION ALL因为如果没有 ALL 关键字MySQL 会给临时表加上 DISTINCT这会对数据做去重代价比较高。此外我们可以将 WHERE、LIMIT 和 ORDER BY 语句应用到每个查询中这样能够让 MySQL 对它们更好地进行优化。
优化 OFFSET
在分页查询中OFFSET 会导致 MySQL 扫描大量不需要的行然后再抛弃掉比如 LIMIT 1000, 20 这个表达式它会查询 1020 条数据然后将前 1000 条抛弃掉这样做的代价非常高。
我们可以通过采用 书签 的方式记录上次读取数据的“位置”那么下次查询就能直接从该位置开始扫描避免使用 OFFSET。比如说每页展示 20 条数据我们记录下来当前所在页面的数据 ID 值为 200那么我们看下一页的数据时查询 SQL 如下
select * from specific_table
where id 180
limit 20;不过这种情况也有不足它没有办法指定页码进行查询比如说我现在想看第 5 页的数据我们没办法计算对应页具体的 ID 值范围。除非我们能够保证 ID 值是单调递增且没有删除过数据的这样的话 ID 值是连续的我们就能轻易的计算出第 5 页的数据的 ID 值是从 120 开始的。这样做的好处是无论翻页到多么靠后它的性能都很好。
使用 WITH ROLLUP 优化 GROUP BY
我们通常使用 GROUP BY 做分组聚合查询如果还要对分组后的结果再次求和可以使用 WITH ROLLUP 操作但是更好的办法还是将 WITH ROLLUP 的处理拿到应用层去做。
OPTIMIZE TABLE
如果我们 删除了很多数据或者在插入数据时不是按照主键的递增顺序插入的很可能会因此产生很多内存碎片影响数据查询的效率。这是因为在删除数据时MySQL 并不会立即将它们清除并整理空间而是将它们标记为删除通过 OPTIMIZE TABLE 可以将空间进行整理减少内存碎片。
InnoDB 引擎并不支持 OPTIMIZE TABLE 操作它会提示如下信息
OPTIMIZE TABLE specific_table;-- Table does not support optimize, doing recreate analyze instead我们可以通过不做任何操作的 ALTER 命令来重建表达到以上目的
alter table specific_table engineInnoDB;执行完成后我们通过如下 SQL 查看执行情况如果 data_free 列为 0说明我们空间碎片整理成功
show table status from specific_db like specific_table;不过多数情况下不需要执行该操作。
找到并修复损坏的表
可能因硬件问题、MySQL 本身的缺陷或者操作系统的问题导致索引损坏当然这种问题非常少见我们可以通过如下 SQL 来检查大多数表和索引的错误
check table specific_table;如果发现异常的话可以通过如下 SQL 进行修复
repair table specific_table;-- 如果存储引擎不支持上述操作的话也可通过表重建来完成
alter table specific_table engineInnoDB;巨人的肩膀 《高性能MySQL 第四版》第七、八章 《MySQL 是怎样运行的》第七、十、十一、十四、十五章 MySQLoptimizer_switch 8.9.4 Index Hints mysql进阶optimize table命令
