# 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学习（五）——索引与查询