服务化-mysql-05 索引优化及Explain实践详解

最新推荐文章于 2022-05-06 20:54:10 发布

旧梦昂志

最新推荐文章于 2022-05-06 20:54:10 发布

阅读量4.8k

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/csfun1/article/details/119986331

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Explain实践优化详解

一、Alibaba索引开发规约及解读
二、Explain概念及实践
三、索引实践
四、索引常用功能优化
五、索引设计原则

本篇实践文章基于mysql8.0
Explain是mysql提供的针对查询语句模拟优化的工具，可以针对输出的结果进行有效分析。
mysql8.0Explian官网地址
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

一、Alibaba索引开发规约及解读

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
解读：
1）单表可以建2-3个联合索引，其所占用的空间及插值时的速度损耗，相对于查询效率的提升可以忽略不计。
2）唯一索引可以避免应用层检验的漏洞，避免脏数据的出现。
【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
解读：
1）尽量使用单表查询，通过业务代码控制联合查询结果，不用多表关联。
2）必须用多表join关联时，查询的字段必须走优化到走索引，否则全表扫描效率极低。
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
解读：
1）当varchar字段走索引时，如果索引长度很长，那么在把索引树加载到内存时会很占用空间，并且mysql会对索引进行逐个字符的比对，这样会拖低索引比对的效率。
2）当字符串长度达到20时，他的区分度已经可以达到90%。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
解读：
1）索引树有最左前缀匹配原则，当使用name like “zhangsan%” 进行搜索时，其作用相当于name = “zhangsan%”，此时会走索引
2）当使用左侧模糊或全模糊时，该索引值无法排序，只能全局搜索，导致索引失效。
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
解读：
1）当order by的值不是索引时，要么是file_sort文件排序，要么走索引。当出现文件排序，就是走全表扫描排序了，order by文件比对的次数可是表行数*搜索出的结果集行数的乘积，非常占用资源。
2）不能用索引的范围查询，会导致后续索引失效。如：WHERE a>10 ORDER BY b; 索引a_b 无法排序
【推荐】利用覆盖索引来进行查询操作，避免回表。
解读：
1）假设test表中有a、b、c 3个索引add index a_b_c（a,b,c），还有其他信息d字段
select * from test where a=? and b=? ，此时a_b_c的索引树没有d字段信息，只能查到主键id后回表拿到所有信息。
select (a,b,c) from test where a=? and b=? ，而这时，直接从a_b_c的索引树中就能直接拿到所有数据，避免回表操作。
2）用 explain 的结果，extra 列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。
解读：
1）select * from test where a=? limit (10000,10) 此时并不是从第10000行开始取数据，拿到后续的10行数据。而是从0行开始取，取到10010行为止，放弃掉前10000行数据，再返回最后10行数据。可以想象，这其中数据页越大，需要提取和放弃的数据量就越多，那么越往后翻页越慢是必然的。
2）select * from (select id from test where a=? limit(10000,10)) 先从查询的表中找到对应主键集合，在从这些集合中查询所有的数据。
【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。
解读：
我们写好的sql语句，拿到Explain执行看下，如果type级别在ref以下，只有以下2个场景，查询效率基本都可以直接归为全表扫描，都需要尽量避免出现。
1）二级索引树下的全索引树扫描，此时type是index
2)全表扫描的ALL
【推荐】建组合索引的时候，区分度最高的在最左边。
解读：
1）很好理解，区分度高说明能够过滤掉的行就多，剩下的范围集很小，这样效率更高。
2）如：where c>? and d=?
那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。因为c的范围查询导致d索引失效。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【参考】创建索引时避免有如下极端误解：
1）索引宁滥勿缺。认为一个查询就需要建一个索引。
2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
3）抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决

二、Explain概念及实践

建几张表备用

CREATE TABLE `test_idx` (
  `id` int NOT NULL,
  `a` char(4) NOT NULL,
  `b` varchar(10) NOT NULL,
  `c` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

随便执行一行查询语句，发现结果集中出现多列语义。id、select_type、table、type列等等。那其中各列的含义是什么？
在这里插入图片描述

2.1 id
id列的编号是select序列号，id序号越大，执行的优先级别越高。id序号相同则从上往下执行。
2.2 select_type
1）simple：简单查询。不包含子查询或union情况
2）primary：复杂查询中最外层的select
3）subquery：子查询
4）derived：派生表（mysql生成的临时表）from语句中的子查询。
对于2、3、4情况，用一条sql来解释
```
set session optimizer_switch='derived_merge=off'; #关闭mysql5.7以后对派生表的优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
```
2.3 table
表示当前explain行正在查询的表。

2.4 type

type列是Explain中最为关键的列。

查询性能排序依次是：
system > const > eq_ref > ref > range > index > ALL

1）Null：优化分解查询语句后，不需要访问表或索引，直接能取到
在这里插入图片描述
2）system：经过mysql优化后，表中只有1行数据与之匹配，相当于常量
3）const：使用主键或者unique key查询表单时，因为主键或唯一索引的限制，只会拿到1条结果匹配
4）eq_ref：使用唯一索引或主键索引进行的关联查询，关联主键的结果只有1条匹配。
5）ref：通过非主键索引或唯一索引的部分前缀进行查询，可能出现多个结果
6）range：使用了索引的范围查询，此时会扫描索引的指定范围区间。（该场景如果索引范围太大，最好要分页。）

explain select  * from test_idx WHERE id>1;

在这里插入图片描述
-----------------------------------------------------------------------------以下为不及格的使用----------------------------------------------------------------------------

7）index：扫描二级索引树下的所有叶子节点（下述场景中，film表中所有字段均被索引或主键覆盖，此时会出现覆盖索引查询）

explain select  a,b,c from test_idx;

在这里插入图片描述
8）All：全表扫描聚集索引的叶子节点（actor表中的所有数据都要查到，包括非索引字段，此时最优的查询方案就是聚簇索引的全表扫描）

2.5 possible_keys
该列显示可能使用哪些索引来查询，
1）当key为null,possible_keys有索引说明mysql基于数据库中的数据量计算，全表扫描cost优于走索引的cost，选择了全表扫描。
2）当possible_keys为null时，说明查询列没有索引，需要适当优化索引了。
2.6 key
这一列显示mysql实际采用哪个索引来优化对该表的访问。

2.7 key_len

sql语句使用了索引时，计算出走的索引长度。(索引最大长达768字节，过长会走左侧前缀原则压缩索引长度)看如下案例。有如下查询语句：

#KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE 索引条件
EXPLAIN SELECT * from test_idx where a='a' and b='text' and c=1;

在这里插入图片描述
此时key_len =62的结果是怎么计算的呢？char(a)=4×4 , varchar(b)=4×10+2, int（c) =4
那么他们的计算规则如果？

2.7.1 key_len计算规则

字符串 char(n)和varchar(n)，5.3版本以后，n代表字符数,utf8中一个字符占3个字节
1）char(n) 占用3n字节
2）varchar(n) 字符占用3n+2字节
数值类型
1）tinyint：1字节
2）smalint：2字节
3）int：4字节
4）bigint：8字节
时间类型
1）date：3字节
2）timestamp：4字节
3）datetime：8字节
not null 时需要另占1字节记录是否为null
CHARSET=utf8mb4，汉字表情需要4个字节表示。该字符集可用于存放表情符号，每个字符需要比utf8多占用1个字节。
2.8 ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
2.9 rows
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
2.10 filtered
通过条件过滤出的行数的百分比估计值。一般只针对索引或主键有用

2.10 Extra

该列展示的是额外信息。常见的值由如下几种：
1）Using index：使用覆盖索引
该种场景比较多，如果查询时使用二级索引树可以直接查到结果，不必再回表，既是using index。
在这里插入图片描述
2）Using where：使用 where 语句来处理结果
3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；比如：使用联合索引的部分前部索引进行范围查询。

4）Using temporary：使用临时表来存储结果。例如，需要查询出test_inx表中某个字段的所用不同结果，此时如果d字段非索引，那么就需要建临时表来存储distinct的结果。这种场景可以通过优化字段为索引解决，因为索引树已经排序，去重的工作直接在索引树中完成。
在这里插入图片描述
5）Using filesort；
本文4.2节详细补充。

三、索引实践

3.1 全值匹配与最左前缀原则

如果索引了多个列，需要遵守建表是idx索引的顺序进行查询，不能跳过前列的索引、不能在前列的索引进行范围查询。
构想索引树的顺序。
在这里插入图片描述
1）顺序查询
叶子节点中，索引树是按照字段顺序排序好了的。在查询过程中，也必须按照该顺序查询，否则索引树就失效了。可以你看下面这条语句（已知idx(a_b_c)是按照a、b、c的顺序组织的），我们查询的时候特意按照b=? and =？的顺序查，为什么也走了索引呢？
在这里插入图片描述
其实是mysql在server层执行词法分析优化时，把查询语句的顺序进行了调整。我们可以通过开启OPTIMIZER_TRACE(trace)验证这一点。

set session optimizer_trace="enabled=on",end_markers_in_json=on;
explain select  a,b,c from test_idx WHERE b='zhangsan' and a='a';
SELECT * FROM information_schema.OPTIMIZER_TRACE;

{
  /* group_index_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_a_b_c",
                        "ranges": [
                          "a <= a <= a AND zhangsan <= b <= zhangsan"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": true,
                        "rows": 1,
                        "cost": 0.36,
                        "chosen": false,
                        "cause": "cost"
                      }
              }
          },
}

2）其他错误场景

#无法使用索引或只能使用部分索引的场景
select  a,b,c from test_idx WHERE b='zhangsan' and c=1; #跳过第一个字段后，后续b、c字段无法直接排序
select  a,b,c from test_idx WHERE a='a' and c=1; #跳过第二个字段b后，c字段无法使用索引
select  a,b,c from test_idx WHERE c=1; #跳过前2个字段后，c字段无法使用索引

3.2 不在索引列做函数计算

#不要在索引列做任何函数操作，否则索引失效，转全表扫描
select  a,b,c from test_idx WHERE a='a';  #正确用法
select  a,b,c from test_idx WHERE left(a,3)='a';  #错误用法

在这里插入图片描述

3.2 不在前、中部使用范围查询（可以选择在最后列范围查询）

EXPLAIN select  * from test_idx WHERE a='a' and  b='zhangsan' and c>1;  #索引查询时，每个字段都可以通过索引树查询
EXPLAIN select  * from test_idx WHERE a='a' and  b>'zhangsan' and c=1;  #查询时，只有前2个字段走索引
EXPLAIN select  * from test_idx WHERE a>'a' and  b='zhangsan' and c=1;  #只有第一个字段走索引

在这里插入图片描述

3.3 尽量使用覆盖索引

select a,b,c from test_idx where a=? #此时二级索引数就能解决问题

3.4 不使用！= 或 is null 等语句

mysql会根据计算cost耗费，选择耗费小的方式进行查询，有可能会走到ALL全表扫描

3.5 不使用左模糊或全模糊

前面阿里规约已经详述

3.6 常见索引场景

3.6.1 索引下推

mysql5.6版本之前，首字段的where a like ‘zw%’ and b=‘teacher’ and c=28的流程如下图
在这里插入图片描述
5.6版本之后，索引下推后，提前过滤后续索引字段，减少大量的回表操作。

我们看到右侧模糊查询时，select * from text_idx where a=‘a’ and b like ‘zhangsan%’ ;可以当做等值查询做索引查询。
这是因为mysql本身也有这样字符串字段截取当索引的查询。
在这里插入图片描述

四、索引常用功能优化

4.1 Order by和Group by排序

group by实际是order by的一种，其实现相同，且在mysql8.0已不再支持group by，此处只探究order by的过程。

4.1.1 排序走索引using index

#虽然有字段c存在，但mysql优化为将a过滤后，以b排序
EXPLAIN select  * from test_idx WHERE a='a' and  c=18  ORDER BY b;  
EXPLAIN select  * from test_idx WHERE a='a' ORDER BY b,c;  #顺序排序
EXPLAIN select  * from test_idx WHERE a='a' and b='dev' ORDER BY c,b;  #a,b已排序完成，排序c

key_len表示只有字段a走索引（这里在表中注入了10w条数据，调整了a的长度为8个字符）
在这里插入图片描述

4.1.2 排序不走索引using filesort

#中间跳过了b字段，c无法使用索引排序
EXPLAIN select  * from test_idx WHERE a='a' ORDER BY c;  
EXPLAIN select  * from test_idx WHERE a='a' ORDER BY c,b;  #倒置的排序
EXPLAIN select  * from test_idx WHERE a='a' ORDER BY b ASC,c DESC; #不同的排序规则
EXPLAIN select  * from test_idx WHERE a in('a1','a2') ORDER BY b,c;  #对于排序来说，in中的结果集是范围查询

在这里插入图片描述

4.1.3 filesort文件排序原理

排序时，using index表示使用耳二级索引树排序，只需要将二级索引树加载到内存进行排序即可。using filesort表示使用聚簇索引进行排序，加载的是主键索引表。

单路排序
一次性取出满足条件行的所有字段，然后再sort buffer中进行排序。trace工具中可以看到sort_mode信息显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
双路排序
先根据条件取出二级索引树中的排序字段和行id，在内存（sort buffer）中进行排序，排序完成后再到主键索引中取出完整的数据。trace是看到是< sort_key, rowid >

什么时候使用单路排序？什么时候走双路排序？
Mysql通过比较排序字段的总字节数与 max_length_for_sort_data（默认1024字节）进行比较，大于该值走双路排序，小于走单路排序。
双路排序是为了节约内存使用的方式，当字段长度已经超过1K，百万级别表行数时，直接load数据到内存，所占用空间将达到1Gb的内存空间占用。所以必须选择更优的方式进行排序，也就是这里提到的双路排序。提取一部分数据排序，释放，再提取一部分数据排序释放。

4.2、limit分页查询优化

我们依次来看下面的4句sql。

explain select * from test_idx LIMIT 10000,5;
explain select * from test_idx ORDER BY id LIMIT 10000,5;
explain select * from test_idx where id>10 LIMIT 10000,5;
explain select * from test_idx where id=10 LIMIT 10000,5;

在这里插入图片描述
1）第一句sql,直接limit10000,5。此时没有任何约束条件，走全表扫描，效率最差
2）Order By id（id是主键）。此时使用主键作为索引进行索引树扫描（此时主键没有范围区间，实际是覆盖索引的场景）
3）where id>10 LIMIT 10000,5。此时主键id有指定范围，可以使用主键索引的range查询。
4）where id=10 LIMIT 10000,5。此时主键id=10，其实limit已经失效，只需要1次回表就能查到具体值。
分析上述4条语句，实际是效率越来越高的，当业务中能提供的信息越明确，查询效率越好。
看下面语句，即使查询条件不是主键，但只要能合理的使用索引信息，查询的效率依然很高。
在这里插入图片描述
注意：千万不要对非索引字段作为查询条件进行排序，filesort效率非常差。

4.3、join表关联优化

关联表查询的效率也并不推荐使用，但其中的算法需要明白。建2张表用于大小表嵌套关联使用

 CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`a` int(11) DEFAULT NULL, 
`b` int(11) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_a` (`a`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

drop procedure if exists insert_t1; 
delimiter ;; 
create procedure insert_t1() 
begin 
 declare i int; 
 set i=1; 
 while(i<=10000)do 
  insert into t1(a,b) values(i,i); 
	set i=i+1; 
 end while; 
end;; 
delimiter ;

call insert_t1();

drop procedure if exists insert_t2; 
delimiter ;; 
create procedure insert_t2() 
begin 
declare i int; 
set i=1; 
while(i<=100)do 
insert into t2(a,b) values(i,i); 
set i=i+1; 
end while; 
end;; 
delimiter ; 
call insert_t2();

概念：驱动表和被驱动表。
1）当关联表语句使用join或inner join时，mysql的优化器会分析关联表的数据量大小，把数量小的表作为驱动表，数据量大的作为被驱动表。
2）使用left join时，left左表作为被驱动表，右手作为被驱动表。
3）当使用right join时，右手作为驱动表，左表为被驱动表。因为left、right已经明确了表关系。

4.3.1 Nested-Loop Join 嵌套循环连接算法

当查询的列是索引列时（t1、t2表中a字段是索引idx_a(a)）

EXPLAIN SELECT * from t1 inner JOIN t2 on t1.a = t2.a;

在这里插入图片描述

概念：每次从驱动表（看语句设定区别哪个是驱动表）中取出一行数据，在这行中找到关联字段到被驱动表中比对取出满足的所有行，最终得到两张表的结果合集。
过程解析：正如下表所示，首先从驱动表T2表中，逐行获取关联字段a=x，通过t2.a=x 查询t1.a=x的值，此时t1表中a字段是索引字段，那么实际就是查询t1中a=x的行数据。（select * from t1 where a=x），得益于t1表a是索引。所以每行t2数据关联T1表时，t1表只需要扫描1次就能获取到数据。
结果：T2全表扫描，扫描100次。T1表使用索引扫描100次，累计200次扫描完成。

4.3.2 Block Nested-Loop Join 基于块的嵌套循环连接算法

EXPLAIN SELECT * from t1 inner JOIN t2 on t1.b = t2.b;

当关联的字段不是索引字段呢？假设仍然使用上述的NLJ算法，我们来看看扫描的次数是多少？答案是100万+100次。

这样的扫描次数，无疑是个无底的黑洞，这仅仅是一张1万条数据*100条数据的表。
那么mysql是怎样优化的呢？答案是标题中的：Block Nested-Loop Join
概念：先扫描整张驱动表，将数据读取到join buffer中。再从被驱动表中读取数据（join buffer默认256k，如果放不下就分段区T1表数据）拿到内存中，做数据比对。获取到满足条件的结果集。
过程解析：很明显，耗时会出现在2个方面。1、扫描耗时，t1、t2分别全表扫描，需要共计10100次扫描。2、比对数据，t2表每行数据的b字段都需要与t1表的b字段做对比，那么比对的次数是100*10000=100万次。

在这里插入图片描述

4.4、count(*)查询优化

我们通常会认为count(*)查询所有数据，执行效率会比较低。从而计算count时，直接count(primary key)。实际他们的执行效率几乎相差无几。

EXPLAIN SELECT COUNT(1) from t1;
EXPLAIN SELECT COUNT(id) from t1;
EXPLAIN SELECT COUNT(a) from t1;
EXPLAIN SELECT COUNT(*) from t1;

执行计划来看，完全一致。可以发现他们非常默契的使用了a字段的索引树做二级索引树扫描。
如果把索引都删掉呢？依然扫描索引树，只是此时扫描的是聚簇索引树，但并不扫描叶子节点。实际效果和上面扫描二级索引树不回表是相同的效果。

五、索引设计原则

旧梦昂志

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
服务化-mysql-05 索引优化及Explain实践详解

Explain详解一级目录二级目录三级目录Explain是mysql提供的用于模拟优化器执行sql语句，分析语句性能的高效工具。mysql8.0Explian官网地址https://dev.mysql.com/doc/refman/8.0/en/explain-output.html在这里插入代码片一级目录二级目录三级目录...
复制链接

扫一扫