高性能MySQL实战(三):性能优化
共 13729字,需浏览 28分钟
·
2023-10-08 09:35
列名 |
描述 |
|
|
|
|
|
|
|
|
|
|
|
|
key |
|
|
|
|
|
|
|
|
|
|
|
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: 表示是一个常数 -
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: 查询时使用到了内部临时表
基于访问类型优化
减少扫描行数的优化
select name, count(name) from specific_table group by key1;
一个复杂查询还是多个简单查询?
切分处理
优化联结查询
-
确保 ON 或者 USING 子句中的列上有索引 -
确保任何 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列,这样 MySQL 才有可能使用索引来优化这个查询
IN() 条件与 OR 条件
查询时索引是否失效
-
如果不是按照索引的最左列开始查找,则无法使用索引 -
如果跳过了联合索引中的列,则无法使用索引或只能使用部分索引。有如下 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;
-
如果查询中有某列的范围查询,则其右边所有列都无法使用索引优化查询或排序。针对这种情况,如果范围查询列值的数量有限,那么可以通过 使用 OR 连接的多个等值匹配来替代范围查询 -
如果在搜索条件中列名不以列名的形式单独出现,而是使用了表达式或者函数,那么无法使用索引,如下 SQL 所示,key1 列以 key1 * 2 的形式出现,不会使用到索引
select * from specific_table where key1 * 2 > 4;
-
如果针对变长字段使用 % 开头的模糊查询时,则不会使用索引。这个比较好理解,因为 MySQL 对字符串的排列是按照一个个字符排序的,在开头使用 % 则无法完成比较只能使用全表扫描了
排序时索引是否失效
-
如果 ORDER BY 语句后面的列的顺序没有按照联合索引的列顺序给出,则无法使用索引 -
如果发生 ASC、DESC 混用,则无法使用索引
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;
-
如果排序列是某个联合索引的索引列,但是这些排序列在联合索引中并不连续,那么也无法使用到索引。如下 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)
索引列不为空的优化
重复索引和冗余索引
create table specific_table (
id int not null primary key,
unique key(id)
)engine=InnoDB;
是否存在索引合并
-
当查询优化器需要对多个索引合并时,通常意味着需要一个包含所有相关列的联合索引,而不是多个独立的单列索引 -
当优化器需要对多个索引做合并操作时,通常需要在算法的缓存、排序和合并操作上耗费大量 CPU 和内存资源,尤其是当其中有些索引列值的选择性不高且需要合并扫描返回的大量数据时 -
优化器不会将这些操作算在查询成本中,这会使得查询的成本被“低估”,导致执行计划还不如进行全表扫描
SELECT @@optimizer_switch;
-- 改成 index_merge=off
set optimizer_switch = 'index_merge=off, ...';
select * from specific_table ignore index(index_name)
where column_name = #{value};
Intersection 索引合并
select * from specific_table where key1 = 'a' and key2 = 'b';
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';
优化 COUNT()
优化 UNION 查询
优化 OFFSET
select * from specific_table
where id <= 180
limit 20;
使用 WITH ROLLUP 优化 GROUP BY
OPTIMIZE TABLE
OPTIMIZE TABLE specific_table;
-- Table does not support optimize, doing recreate + analyze instead
alter table specific_table engine=InnoDB;
show table status from specific_db like specific_table;
找到并修复损坏的表
check table specific_table;
repair table specific_table;
-- 如果存储引擎不支持上述操作的话,也可通过表重建来完成
alter table specific_table engine=InnoDB;
参考资料:
[1]《高性能MySQL 第四版》:第七、八章
[2] 《MySQL 是怎样运行的》:第七、十、十一、十四、十五章
[3] MySQL:optimizer_switch
[4] 8.9.4 Index Hints
[5] mysql进阶:optimize table命令
-end-