MySQL 索引类型

主键索引

按照主键查询的话，我们称为使用主键索引来查询数据

聚集索引（聚簇索引）

数据行和相邻的键值紧凑的存储在一起，一般一张表只有一种存储方式。

例如InnoDB使用的就是聚集索引，它通过B+树在叶子节点存储的是数据页，数据页中存储了连续的行信息，可以使用偏移查询行信息

非聚集索引

数据行和相邻的键值不是紧凑的存储在一起

例如MyISAM 使用的是非聚集索引，它在叶子节点中直接存储的是行数据的地址信息，这样行与行之间并非是紧密关联的。

辅助索引

创建辅助索引

CREATE INDEX email_idx ON t_user(email);
-- 指定索引名称
ALTER TABLE t_user ADD INDEX(email);
-- 使用默认字段名作为索引名称

1. 索引页存储 key (email字段值)， 主键（id值），子节点所在数据页页号
2. 数据页中，存储的是key(email字段值)及其所在行的主键值(id字段值)

辅助索引和主键索引的区别

1. 主键索引树上的叶子节点存储的是主键及其对应的行记录数据，而辅助索引树上的节点存储的是索引字段值及其所在行的主键值

2. 主键索引树的key(主键)不能重复

   而辅助索引树的key 可以重复

   这种key可以重复的索引，叫普通索引

3. 主键索引是聚集索引，相邻的主键对应的行记录是相邻的

   而辅助索引是非聚集索引，相邻的key对应的行记录不一定相邻

基于辅助索引的查询过程

SELECT * FROM t_user WHERE email= '90@163.com';

1. 到email 索引树上找到email = ‘90@163.com’ 的记录，得到主键id = 100
2. 再回到主键索引树查找id = 100对应的记录
3. 在email索引树上找到email = ‘91@163.com’的记录 ，不符合条件，结束

在查找过程中，从辅助索引树回到主键索引树搜索的过程，称为回表

Extra

Using Index
-- 直接使用索引完成数据查询
Using Index Condition
-- 使用ICP，条件下推，将服务层需要进行的过滤直接下发给存储引擎，减少全表扫描，
-- 而是由数据引擎层直接筛选符合要求的数据返回给服务层
Using Where               
-- 使用了全表扫描，最终由服务层根据筛选条件获取的目标数据

唯一索引

使用唯一字段 创建的索引自动转换为唯一索引

与普通索引的区别

1. 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到不满足的记录
2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止检索。

type字段

type = ALL 
-- 全表扫描，MySQL扫描全表来找到匹配的行
type = index
-- 索引全扫描，MySQL遍历整个索引来查找匹配的行
type = range 
-- 表示索引范围查询，
type = ref
-- 使用普通索引或唯一索引的前缀扫描
type = eq_ref 
-- 使用唯一索引或者主键进行关联查询
type = const 
-- 根据主键或唯一索引进行的查询
type = NULL
-- 不用访问表，直接可以得到结果的查询

联合索引

将多个字段作为key创建索引，本质上依然以首个字段作为排序依据，当首字段相等时，保证第二个字段有序。

当 进行查询时，优先选择 规模较小的索引 进行查询（意味着一次加载页中包含更多的行信息）

增加索引的时机

索引选择性

在访问表中很少一部分数据时，使用B+树索引才有意义

索引的选择性：不重复的索引值的个数和数据表的记录总数的比值

索引选择性的值范围是(0 , 1] ， 值越高，那么在索引查询时过滤掉的行越多，查询效率越高，

索引顺序

在设置联合索引时，将选择性更高的字段放在前面

索引失效

1. 表达式计算
2. 使用函数

覆盖索引

如果一个索引包含所有需要查询的字段的值，就称之为覆盖索引

查询数据量少了很多

避免回表

对某些统计问题而言，可以提高性能

即通过覆盖索引能够在保证查询目标完整的情况下减少整体的查询数据规模达到提高查询效率的目的

B-Tree的 优点和缺点

优点：

索引大大减少mysqld需要扫描的数据量

索引可以帮mysqld避免排序

索引可以将随机I/O变为顺序I/O

缺点：

索引需要额外占用磁盘空间

新增和删除记录时，需要额外维护索引树，引起额外消耗。

前缀索引

在不影响整体性能的情况下，使用字段的前缀设置为索引，可以减少整体的页结构大小，提高整体效率

主键选择

UUID 和 自增主键

相比之下， 自增主键占用的空间更小，更能提升检索效率，同时在插入时效率更高不会产生页分裂问题，导致数据迁移