# Mysql学习(五)——索引与查询
[TOC]
## 索引类型
Mysql常见的索引类型,可以按照以下几个方向来进行区分:
- 存储结构:可以分为B Tree(B+B-)索引、Hash 索引、Full Text 索引等
- 应用层次:普通索引、唯一索引、主键索引、复合索引
- 索引键值类型:主键索引、辅助索引(除去主键索引)
- 数据存储类型:聚集索引、非聚集索引
常用的索引这里就不做过多介绍,介绍下Full Text索引
### Full Text索引
平常在使用一些文本型的字段查询数据时,使用的是like进行模糊查询,但是这样对大文本类型查询时,效率很低。如果使用全文索引,那么查询速度会快很多,在5.6版本之前,innodb不支持全文索引,MyISAM支持,5.7版本以后,InnoDB也支持了全文索引
**创建全文索引的方法**:
- CREATE FULLTEXT INDEX index_name ON tablename(column)
- ALTER TABLE tablename ADD FULLTEXT index_name(column)
- CREATE TABLE tablename([...],FULLTEXT KEY index_name column)
**查询时使用全文索引的方法**:
```sql
select * from tablename where matche(column) against('aaa');
```
**使用全文索引需要注意的点**:
- 全文索引只能在字符串、文本类型建立
- 全文索引字段值必须在预置的参数值的上下限范围内才生效(默认的innodb为:3-84,MyISAM为:4-84),使用命令进行修改:`show variables like '%ft%'`

- 全文索引字段要进行切词处理,按照syntax进行切割,比如:abc:def,全文检索可以检索到abc、def两个值
- 全文索引默认是使用等值匹配进行处理的,比如值为abcd,你用abc来进行查询,是查询不出结果的,如果想使用模糊匹配,可以强制使用下列sql进行查询
```sql
select * from user where match(column) against('abc*' in boolean mode);
```
## 常见索引的原理
Mysql索引常见的常见模型分为三种:**有序数组**、**Hash 表**、**搜索树**
### 有序数组
有序数组在做范围查询和等值查询时性能非常优秀,做查询时,使用二分查找法,可以很快的在有序数组中定位到具体的索引值,缺点就是每次增删改数据时,整个数组索引都需要进行改动,性能效果过大,维护成本高。**如果只做查询的话,有序数组就是最好的索引模型**,适合保存数据之后不再改动的场景。
### Hash 表
Hash表底层使用键值<key,value>类型来存储数据的,非常使用使用key值来进行查询记录,就是使用单个值进行等值查询,效果非常好

Hash结构可以提供非常高效的等值查询,但是一旦使用范围查询,那么就会需要进行全表扫描,效率非常差,Hash索引主要用于Memory引擎下的原生Hash索引以及InnoDB自适应哈希索引
**自适应哈希索引**:
前面在介绍InnoDB的内存空间时,可以看到BP中有一部分空间是用于存储自适应哈希索引的,那么这个索引是用来做什么的?
在InnoDB中,InnoDB引擎会监控表上各个索引页的查询,当InnoDB注意到某些索引值访问的十分频繁,会在内存中基于B+Tree创建一个Hash索引,使得内存中的B+Tree也具有Hash索引的功能,可以快速的访问热点数据页。
自适应Hash索引只能由InnoDB自动检测并建立,无法人工干预,人为只能选择开启或者关闭。使用命令可以查看自适应Hash索引的状态。
```sql
show engine innodb status ;
show variables like '%innodb_adaptive%';
```

### B+Tree 结构
现在使用的最多的就是B+Tree索引结构了,InnoDB会为表里的每一个索引都建立一个B+Tree,B+Tree在B-Tree上进行了优化改造。
- **B-Tree树的结构**:
- 索引值和data数据分布在整棵树上
- 每个节点可以存放多个索引值及对应data数据
- 树节点的索引值按照从左往右进行升序排列

**B-Tree的搜索逻辑**:从根节点开始查询,对节点内的索引值进行二分查找,如果命中就直接结束,否则进入子节点重复进行查找,直到查询到数据返回为止
- **B+Tree的结构**:
- 只有叶子节点上存储着索引值和data数据,其他节点只存储索引值
- 叶子节点上包含了所有的索引值和data数据
- 叶子节点之前用指针连接,提高区间的访问效率
**B+Tree的搜索逻辑**:B+树进行范围查找时,首先定位两个节点的索引值,然后利用叶子节点的指针进行遍历即可返回所有的数据,B树需要遍历范围内所有的节点和数据,所以B+Tree效率更高
### 聚集索引和辅助索引
**聚集索引和非聚集索引**:
B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。
**主键索引和辅助索引**:
B+Tree的叶子节点存放的是主键字段值就属于主键索引;如果存放的是非主键值就属于辅助索引(二级索引)。
在InnoDB引擎中,主键索引就是使用的聚集索引结构存储。
- **聚集索引**
聚集索引是一种数据存储方式,InnoDB引擎的聚集索引按照主键的顺序构建了B+Tree结构,B+Tree的叶子节点就是行记录,行记录与索引都关联在一起,因此InnoDB的主键索引其实就是整张数据表
InnoDB规定表中一定要有聚集索引:
- 如果表里定义了主键,那么主键索引就是聚集索引
- 如果表里没有定义主键,第一个非空唯一索引列就是聚集索引
- 如果表里没有上面两者,innoDB自行建立一个隐藏的rowid列作为聚集索引
- **辅助索引**
InnoDB的辅助索引也是用B+Tree结构存储的,辅助索引在B+Tree的叶子节点上只存储了索引列和主键的信息,因此二级索引占用的空间会小很多,而创建辅助索引就是为了提升效率。

## 常见索引分析与优化
### Explain命令
Mysql提供了Explain命令用于分析Select语句,并且输出具体的执行信息,供我们分析与优化
```sql
explain select * from sys_org where id > 164;
```
<img src="https://zhengyao.space/upload/2020/12/image-20201212165426701-f1b4b754bfdc47ca882a3558e0c3dabc.png" alt="image-20201212165426701" style="zoom: 67%;" />
这里可以看到,使用explain命令,会返回一些具体的类型值,这里介绍下这些值平常的使用方式和作用
- select_type
用于表示此次查询的类型
- SIMPLE:表示查询语句中不包含子查询或者Union语句
- PRIMARY:表示这是最外层的查询
- UNION:表示此查询是使用了UNION的后续查询
- DEPENDENT UNION:表示是UNION查询并使用了外层查询结果
- UNION RESULT:表示是UNION查询的结果
- SUBQUERY:select中的子查询语句
- DEPENDENT SUBQUERY:表示子查询中用到了外层的查询结果
这里我们使用一个union查询看一下效果`explain select * from sys_org where id > 164 UNION select * from sys_org where id <= 164;`

- table:表示使用的表名
- type
表示存储引擎查询数据使用的方式,是一个非常重要的属性,可以通过这个字段来判断出全表扫描还是索引扫描等。常用的属性如下,效率一般依次增强
- ALL:表示全表扫描
- index:表示基于索引的全表扫描,先扫描索引再扫描全表数据
- range:表示使用索引范围查询,一般使用>,<=,in 等会出现此类型
- ref:表示使用非唯一索引进行等值查询
- eq_ref:一般是在多表联查时出现,表示主表与连接表的数据都是1对1的查询
- const:表示使用主键或者常量查询
- NULL:表示不访问表,例如使用`explain select now();`
- possible_keys
查询引擎预估能使用到的索引名称,但是结果不一定会使用上
- key
表示真正使用的索引名称
- rows
mysql查询优化器根据统计信息,估算sql需要查询到结果预期要扫描多少数据行,原则上rows数值越小越好,证明扫描的数据越来越少
- key_len
表示索引使用的字节量
- extra
extra也是一个重要的属性,可以表示很多额外的信息,常见的几种如下:
- Using where
表示查询时会使用到**回表**(后面介绍)
- Using index
表示查询时索引就满足了所有的数据
- Using filesort
表示使用到了排序,如果数据量小就是内存中排序,否则就在磁盘上排序,因此这种都尽量建议优化
- Using temprorary
查询使用到了临时表,一般用于去重、分组查询时出现
### 回表查询
前面介绍了InnoDB的索引结构,主键索引的叶子节点上,存储的是所有的索引值和data数据,而辅助索引只存储了索引值和主键值,那么如果使用辅助索引进行查询时,我们就需要从辅助索引的叶子节点上,拿到所有的主键值,再使用主键值去主键索引上获取到对应的数据,因此我们需要先通过辅助索引定位主键,在通过主键获取记录,这样就称为**回表查询**
### 覆盖索引
既然有了回表查询,那么没有回表的索引查询又称为**覆盖索引**,例如我们通常会使用用户编码去查询用户名称,在用户编码上增加对应索引,但是每次使用用户编码去查询时,都会造成回表
```sql
select user_name from sys_user where user_code = '123';
```
首先先去user_code列上进行查询获得主键值,再使用主键值去获取到对应行数据的user_name字段,这样相当于查询两次了。
而此时我们使用联合索引,将(user_code,user_name)放在一棵索引树上,那么就可以直接在索引数上获得具体的数据了,无需回表,这样就称为**覆盖索引**
### 最左前缀原则
上面介绍了如果建立了一个(user_code,user_name)的联合索引,这样可以解决回表的问题,那么如果我就是要通过user_code查询某些其他字段,又要单独建立一个user_code索引,这样看起来又很浪费,因此InnoDB做了优化
复合索引使用时遵循最左前缀原则,最左前缀顾名思义,就是最左优先,即查询中使用到最左边的列,那么查询就会使用到索引,如果从索引的第二列开始查找,索引将失效。
这里不仅使用字段有效,MySQL在使用Like模糊查询时,索引是可以被使用的,只有把%字符写在后面才会使用到索引
```sql
select * from sys_user where user_code like '%123%'; //不起作用
select * from sys_user where name like '123%'; //起作用
select * from sys_user where name like '%123'; //不起作用
```

### 索引下推
在使用最左前缀原则时,满足条件的可以直接在索引中定位记录,那么不满足条件的该如何过滤呢?
```sql
select user_name from sys_user where user_code like '123%' and user_name like '张%';
```
在5.6版本之前,我们只能通过user_code 索引定位出来的数据,再一个个进行回表查询判断是否满足user_name like'张%'这个条件,那么在有了索引下推后,Mysql可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
### 索引与排序
Mysql排序是支持filesort和index两种方式排序的,前面也介绍了,使用explain命令时,可以根据extra查看使用了哪种方式进行排序,filesort排序是先把结果查询出,然后在缓存或者磁盘中进行排序操作,这样效率比较低。index方式是使用索引实现自动排序,可以直接得出结果,无需再次排序,效率比较高。
filesort的排序也有两种算法:
- **双路排序**:需要两次磁盘扫描读取,最终得到用户数据。第一次将排序字段读取出来,然后排序;第二次去读取其他字段数据
- **单路排序**:从磁盘查询所需的所有列数据,然后在内存排序将结果返回。如果查询数据超出缓存
sort_buffer,会导致多次磁盘读取操作,并创建临时表,最后产生了多次IO,反而会增加负担。解决方案:少使用select *;增加sort_buffer_size容量和max_length_for_sort_data容量
下面拿我建立的表测试一下效果:
执行sql:`explain select * from sys_org where id > 100 order by org_code desc;`使用id查询数据,使用org_code进行排序

执行sql:`explain select org_code from sys_org where org_code > 100 order by org_code desc;`使用org_code进行过滤和排序,这样在一棵索引树上就完成了排序的目的

- 以下几种情况,会使用index方式的排序:
- order by与查询数据满足覆盖索引的需求
```sql
select org_code from sys_org order by org_code desc;
```
- order by 、where和查询数据满足覆盖索引的要求
```sql
select user_name from sys_user order by user_code desc;
```
- 以下几种常用场景,会使用filesort方式排序:
- 对索引列同时使用了ASC和DESC
```sql
select user_name from sys_user order by user_code desc,org_name asc;
```
- 在索引列中使用函数
```sql
select user_name from sys_user order by concat(user_code,'') desc;
```
- 使用了两个不同的索引列
```sql
select user_name from sys_user order by othercolumn,user_code desc;
```
## 如何定位慢查询?
**开启慢查询日志**
首先查看数据库是否开启了慢查询
```sql
show variables like '%slow_query_log%';
```

查看慢查询的阈值:
```sql
show variables like '%long_query_time%';
```

查询时间大于1秒的sql都会被记录到慢查询当中
如果未开启,可以使用命令开启:
```sql
SET global slow_query_log = ON;//开启慢查询记录
SET global slow_query_log_file = '';//慢查询日志存储文件
SET global log_queries_not_using_indexes = ON;//是否记录未使用索引的sql
SET long_query_time = 10;//设置慢查询阈值
```
查看慢查询的方式:
- 文本查看
进入到数据库服务器中,下载好对应的日志文件,本地直接使用文本文件打开

- time:日志记录的时间
- User@Host:执行的用户及主机
- Query_time:执行的时间
- Lock_time:锁表时间
- Rows_sent:发送给请求方的记录数,结果数量
- Rows_examined:语句扫描的记录条数
- SET timestamp:语句执行的时间点
- select....:执行的具体的SQL语句
- 使用mysqldumpslow查看
使用命令:
```sql
perl mysqldumpslow.pl -t 5 -s at /data/slowlog/slow.log;
```
## 常见的慢查询优化
当执行时间超过我们`long_query_time`设定的阈值时,就被定义为慢查询,而我们一般会进行判断,是否使用了索引,但是即使使用了索引是否一定就不是慢查询?
即使使用了索引,也不一定,因为索引只是提升了查询的效率,如果查找的范围过大,那么即使使用了索引也会导致变成慢查询,因此面对数据表,不仅要考虑建立索引,也要考虑索引的过滤性
**索引过滤性**:
例如一张用户表,使用性别字段sex作为索引列,我们使用sex='男'时,发现过滤出来了一半的数据,那么这个索引的过滤性就不好,我们希望的是索引的过滤性越强越好。
**过滤性案例**:
```sql
表:student
字段:id,name,sex,age
造数据:insert into student (name,sex,age) select name,sex,age from
student;
查询年龄=18岁,并且姓张的学生
SQL案例:select * from student where age=18 and name like '张%';(全表扫
描)
```
- 优化一:
```sql
alter table student add index(name);
```
在名称上面建立索引,这样根据like也可以使用索引的原则,可以变成回表查询
- 优化二:
```sql
alter table student add index(age,name)
```
建立联合索引,可以在一棵索引树上就完成了数据过滤,即前面介绍的索引下推
- 优化三:
```sql
alter table student add first_name varchar(2) generated always as(left(name, 1)), add index(first_name, age);
explain select * from student where first_name='张' and age=18;
```
将姓氏的第一个字建立一个虚拟列,并使用虚拟列与age列建立联合索引,那么我们就可以直接使用等值查询,效率相对于联合索引的索引下推做到了进一步的提升。
**慢查询的常见场景**:
- 全表扫描:explain分析的type为All,这样如果查询效率低是需要进行优化的
- 索引扫描:explain分析出来type是index,那么尽量也要对这种进行优化
- 优化索引的过滤性:枚举类字段或者重复性较强字段、不用于查询过滤的字段、频繁更新的字段尽量不建议建立索引
- 减少回表:查询数据时,如无必要,尽量只查询同一颗索引树上的字段,这样可以减少回表扫描的消耗,更多的使用覆盖索引。
Mysql学习(五)——索引与查询