4、索引及执行计划

作者: 一个反派人物 | 来源:发表于2020-11-20 14:36 被阅读0次

1 索引作用

提供了类似于书中目录的作用，目的是为了优化查询

2 索引的种类

B树索引（最常用）
Hash索引
Full text
GIS

3 B树基于不同的查找算法分类介绍

B-tree

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子。
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子。
所有叶子节点都在同一层，且不包含其它关键字信息。
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：
根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

B+tree

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：
1.非叶子节点只存储键值信息，键值是叶子节点存储的键值中的最小值。
2.所有叶子节点之间都有一个链指针，指向相邻的叶子节点。
3.数据记录都存放在叶子节点中。
4.由于相邻节点的指针，在范围查找（<、>、<=、>=、like）方面提供了更好的性能

B＊tree
在非叶子节点的相邻节点之间也添加了相互的指针，相比B+tree优化效果没那么明显。

4 在功能上的分类

4.1 辅助索引如何使用B树

命令：alter table 表名 add index 索引名(索引列);
1.取出索引列的所有值，自动排序。
2.申请索引叶子节点数据页，将有序的索引值存储到叶子节点的数据页中，键值为索引值，data部分为具体数据行对应的数据页的位置指针。
3.申请枝节点和根节点数据页，将下层节点的最小键值存储进去。

使用辅助索引查找时，能够找到数据行的数据页位置指针，因数据行存储到数据页时，并不一定是有序的，为找到指定的数据页位置，还需遍历数据页。

4.2 聚集索引如何使用B树

聚集索引会自动根据主键创建，MySQL里主键就是聚集索引
1.构建的前提是根据主键或唯一键，一般建表的时候我们建议创建PK，并且建议是数字列，例如ID自增长。
2.一旦表中确认有PK，数据在录入时，就按照PK的顺序，在磁盘数据页中有序存储数据行。
3.将数据页作为聚集索引B树的叶子节点（叶子节点直接存储数据行），再生成枝节点和根节点。

4.3 辅助索引配合聚集索引

辅助索引配合聚集索引使用时，辅助索引叶子节点的data部分存储的数据，变为数据行聚集索引的索引值。在进行数据查找时，先使用辅助索引，后使用聚集索引。

4.4 辅助索引和聚集索引的区别

聚集索引：
1.只能有一个，非空唯一，一般是主键。
2.叶子节点就是磁盘的数据行存储的数据页
3.MySql是根据聚集索引，组织存储数据，数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引：
1.辅助索引，可以有多个，是配合聚集索引使用的
2.辅助索引，叶子节点只保存了行的键值和指向对应行的 "书签"

5 辅助索引细分

1.普通的单列辅助索引
2.联合索引：多个列作为索引条件，生成索引树，可以减少大量的回表查询
3.唯一索引：索引列的值都是唯一的

6 索引树高度的影响因素

1.数据量级，解决方法：分表、分库、分布式
2.索引列值过长，解决方法：前缀索引
3.数据类型，变长长度的字符串，使用了char类型。解决方法：变长字符串使用varchar类型，能用enum类型的，使用enum类型。

7 索引的基本管理

7.1 查看当前表的索引

desc 表名；
key列代表当前表的索引，共有三种类型：
1.PRI 主键索引
2.UNI 唯一键索引
3.MUL 辅助索引

show index from 表名;

7.2 建立索引

7.2.1 列索引

alter table 表名 add index 索引名(索引列);

alter table city add index idx_name(name);

7.2.2 联合索引

alter table 表名 add index 索引名(索引列1，索引列2);

alter table city add index idx_cx(district,population);

1.联合索引以第一个字段来优先排序的，如果你的查询条件里没有第一个字段就没法通过索引比较来定位数据
2.是否走联合索引与where条件的顺序无关，只与字段有关

联合索引示意图

7.2.3 前缀索引

alter table 表名 add index 索引名(索引列(前缀数));

#以name列的前5位前缀建立索引
alter table city add index idx_name(name(5));

7.2.4 唯一索引

alter table 表名 add unique index 索引名(索引列);

索引列的数值必须各不相同，且建立后再插入的数值也得符合唯一性，如果存在重复值，需要清洗冗余数据。
可以借助主键列来查询其他列是否有重复数据，下面是一个例子：

需求: 找到world下,city表中 name列有重复值的行。
[world]>select name,count(id) from city group by name having count(id)>1 order by count(id);

7.3 删除索引

alter table 表名 drop index 索引名;

alter table city drop index idx_name;

8 执行计划获取及分析

(1)获取到的是优化器选择完成的,他认为代价最小的执行计划.
作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题。如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。
(2) select 获取数据的方法：
1.全表扫描(应当尽量避免,因为性能低)
2.索引扫描
3.获取不到数据

压力测试命令mysqlslap

[root@db01 ~]$ mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t_100w where k2='780P'" engine=innodb \
> --number-of-queries=2000 -uroot -p123 -verbose

--concurrency 并发线程数
--iterations  重复次数
--create-schema 库名
--query 执行的查询语句
--number-of-queries 每个线程查询次数

8.1 执行计划获取

desc/explain select语句;

8.2 执行计划分析

重点关注以下参数：
table 查询的表
type 查询的类型：全表，索引
possible_keys 可能用到的索引
key 实际使用的索引
extra 附加信息

8.2.1 type详解

8.2.1.1 ALL

全表扫描，不走索引，出现的条件：
1.查询的条件列，没有索引
2.辅助索引的查询条件出现如下语句：<>（不等于）、NOT IN ('x','y')

8.2.1.2 INDEX

全索引扫描，出现的条件：
1.查询需要获取整个索引列的值
2.联合索引中，任何一个非最左列作为查询条件时

8.2.1.3 RANGE

索引范围扫描：
辅助索引（<、>、<=、>=、LIKE、IN('x','y')、OR）
主键索引（<>、NOT IN）

例子：
1.select * from city where id<5;
2.select * from city where countrycode like 'CH%';
3.select * from city where countrycode IN ('CHN','USA');
注意：
1和2例子能享受B+树叶子之间指针的优势，3不能。
例子3可以将IN语句改为UNION ALL，从而由range变成ref查询提升性能。

8.2.1.4 ref

非唯一性索引，等值查询(=)

8.2.1.5 eq_ref

在多表连接时，连接条件ON使用了唯一索引(uk、pk)

8.2.1.6 system/const

唯一索引的等值查询(=)

8.2.1.7 NULL

查不到数值

8.2.2 extra解释

filesort 文件排序，由order by、group by、distinct触发

例如：
countrycode已经建立了单列辅助索引，无论population建不建立索引，此条查询语句均只能使用countrycode的索引，order by部分会触发filesort。

desc select * from city where countrycode='chn' order by population;

使用countrycode和population建立联合索引后，可以使用联合索引，不会触发filesort

alter table city add index idx_c_p(countrycode,population);

结论：
1.当看到执行计划extra位置出现filesort时，说明有文件排序出现
2.观察需要排序（order by、group by、distinct）的条件，有没有索引
3.根据子句的执行顺序，去创建联合索引

8.2.3 语句中有having时的处理

查询语句中有having时，having后面的所有条件不走索引。针对这种情况，可以使用临时表来解决。

例如：
原始查询语句

#a、b可以走索引，c、d不能走索引
select x from 
where a 
group by b
having c
order by d

使用临时表

create temporary table xxx as
select x from 
where a
group by b

select  x from xxx
where c
order by d

8.2.4 联合索引的使用

1.建立联合索引

alter table t1 add index idx_a_b(a,b);

查询时：

select * from table t1 where a=  and b=   ;
select * from table t1 where b=  and a=   ;

两个查询语句都能使用索引，优化器会自动调整where的条件顺序。
这种情况下建立联合索引时，需要考虑哪个列的唯一值更多，哪个放在索引左边。
2.如果where条件中出现不等值查询条件

select * from table t1 where a<  and b=  ;

建索引时，需要按照先b后a的顺序，同时修改where语句，将不等值查询放在后面，来获得更好的性能

alter table t1 add index idx_b_a(b,a);
select * from table t1 where b=  and a<  ;

3.如果查询出现多子句，要按照子句的执行顺序建立联合索引。

8.3 explain/descd的使用场景（面试题）

从数据库的角度，分析业务慢的问题：
1.突发性的慢，突然夯住
突发情况：数据库卡住了，系统资源耗尽
处理过程:
(1)show [full] processlist：获取导致数据库夯住的语句，找到Query类型、Time时间长的语句

(2)explain分析SQL的执行计划，有没有走索引，索引的类型情况
(3)建索引，改语句
(4)临时处理kill 线程id号。
2.固定的时间慢，持续性的
(1)记录慢日志slowlog，分析slowlog
(2)explain分析SQL的执行计划，有没有走索引，索引的类型情况
(3)建索引，改语句

9 索引应用规范

9.1 建立索引的原则

9.1.1 建表时一定要有主键，一般是个无关列

建表时会根据主键自动创建聚集索引

9.1.2 选择唯一性索引

唯一性索引的值是唯一的，可以更快的通过该索引确定到某条记录。
例如：学生表的学号具有唯一性，使用学号查询性能比用姓名查询好，因为姓名可能重名。

优化方案：
(1)如果非得使用重复值较多的列作为查询条件，可以将表逻辑拆分
(2)将重复值较多的列和唯一索引做联合索引，唯一索引放在联合索引左边

9.1.3 为经常需要where、order by、group by、join on等操作的字段建立索引

为使用多个查询条件的语句，建立联合索引

9.1.4 尽量使用前缀索引

如果索引字段的值很长，最好使用前缀索引，减少索引树的高度

9.1.5 限制索引的数目

索引的数目不是越多越好，可能会产生问题：
(1)每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
(2)修改表时，对索引的重构和更新很麻烦。越多的索引，越会使更新表变得慢。
(3)优化器负担会很重，有可能会影响到优化器的选择。
percona-toolkit中有个工具，专门分析索引是否有用。

9.1.6 删除不再使用或者很少使用的索引(percona-toolkit)

pt-duplicate-key-checker

9.1.7 大表加索引，要在业务不繁忙期间操作

9.1.8 尽量少在经常更新值的列上建索引

9.1.9 or或in 语句尽量改写成union all

能够将type由range变为ref，提升查找的性能

9.2 不走索引的情况

9.2.1 没有查询条件，或者查询条件没有建立索引

尽量避免全表扫描的操作

9.2.2 查询结果集是原表中的大部分数据（25%以上）

查询的结果集，超过了总行数的25%，优化器觉得就没必要走索引了。
解决方案：
(1)如果业务允许，可以使用limit控制
(2)如果没有更好的改写方案，推荐使用redis里面存储，放弃mysql

9.2.3 索引本身失效

索引有自我维护能力，对于表内容变化比较频繁的情况下，有可能会出现索引失效。
解决方案：
(1)删除重建索引

9.2.4 查询条件使用函数在索引列上，或者对索引列进行运算（+、-、*、/、！等）

如
select * from t1 where id-1=9;
可改写为
select * from t1 where id=10;

9.2.5 查询字符串时使用数字

如果索引列用字符串存储数字，查询条件要写字符串，不要写数字。写数字也能够查到结果，但是会调用内部数字转字符串的函数，而不走索引。
正确写法：
select * from t1 where tel='110';
错误写法：
select * from t1 where tel=110;

9.2.6 <>、NOT IN不走索引（辅助索引）

9.2.7 like '%_ '百分号在最前面不走

%必须放在最前面的搜索需求，如搜索殷勤，建议使用elasticsearch+mongodb专门做搜索服务的数据库产品