MySQL索引：InnoDB 的索引机制
索引的种类
- 数据结构：B+树、Hash、Full-text
- 物理存储：聚簇索引、非聚簇索引
- 字段特性：Key、Unique、Index、Fulltext
- 字段个数：单列索引、联合索引
索引数据结构
索引就是搜索，将同一类事物 / 字段进行数据结构排列，方便查找
比如：B树的二分查找、hash的桶位路由查找等等
索引数据结构有：BST、AVLT、红黑树、B树、B+树
- 是否二分 BST
- 是否平衡
- 时刻平衡 AVL
- 惰性平衡 红黑树
索引的优缺点
优点：
- 条件下推到索引，可以提高数据检索效率
- 唯一性索引可以保证字段不重复
- 加快数据的分组和排序
缺点： - 存索引，需要占硬盘空间
- 创建索引开销
- 增删改操作，需要维护索引，维护开销
- 数据量越多，维护开销越大
- 普通索引/辅助索引（index类型索引）会导致频繁回表
- 左模糊查询，或者一些条件查询，会导致索引失效
- 虽然增加了查询开销，但是维护/增删改的性能都会下降
表空间和B+树索引空间
在innodb中的表数据文件是.idb文件，用来描述表空间
表空间是由多个B+树索引空间组成
每建立一个索引就会在表空间中建立一个辅助索引，
每个辅助索引都会对当前字段在表空间中建立一个对应列的B+树索引数据结构，
非聚簇索引就是辅助索引
非聚簇索引就是辅助索引，它不包含完整的表字段，只包含索引字段和主键
辅助索引又叫二级索引，分为普通索引 Index 和唯一索引 Unique
聚簇索引就是主键
在innodb中，主键索引和所有行字段，都是放在一起的
而这种索引数据结构是一种索引和行数据混合的存储模式
这个就是innodb的模式
不要使用隐藏的主键
在innodb引擎创建的表中，如果不设计主键列，还是会有主键
而且是自动递增主键，只不过这个主键是系统自动分配的，叫做：row_id
但是这个主键不会提高检索效率
而且因为是自动递增，导致删除之后会留下外存碎片，降低外存空间使用率
Index 和 Unique
辅助索引分为普通索引 Index 和唯一索引 unique，他们一个是按照平衡二分排序，一个是在平衡二分的基础上保证唯一不重复
它们除了在唯一性上不一样，在性能上也有差异：
查效率对比
查询首先是层序遍历B+树的树节点，路由到叶子节点，磁盘读取对应页到内存，之后维护内存指针读取记录，之后：
- Index在读内存页的字段记录中，如果读到了目标记录，还会继续移动指针读页内下一个记录；
如果恰好指针移动到了当页的最后一条记录，则会磁盘IO加载下一页；
这个过程直到读到第一个值不一样的记录，才会停止 - Unique在读内存页的字段记录中，如果读到目标记录，就直接返回一个主键id，结束读取，不会读下一个记录
所以在查效率上，唯一索引Unique效率更高
增效率对比
增加也是先层序遍历到叶子节点，之后读取一页到内存，之后遍历页内记录：
第二步，看当前内存中是否有对应页，如果缓存了对应页：
- 内存中有缓存页：
- Index：会找到合适的位置插入数据；
- Unique：会找到合适的位置，然后判断是否有冲突，如果无冲突再插入；
- 内存无缓存的目标页：e
- Index：将待插入的数据放入 change buffer
- Unique：将数据页加载到内存作为缓存页，然后按照有缓存页的方式进行插入；
其他效率
其他操作上都是差不多的，都是修改B+树结构的模式
综上，Unique在查询上的效率高于Index，而且唯一性也意味着唯一次数的回表；
但是Unique在插入的效率会差一些；
Innodb不支持的索引
不支持虚拟列索引和表达式索引，因为表达式和虚拟列需要计算，对于B+树这种结构来说，维护的计算代价过大
联合索引
联合索引又称组合索引、复合索引
优缺点
优点：
- 条件过滤的高效性
可以用联合索引一次性过滤多个条件，避免多个二级索引树的查找开销 - 索引覆盖，联合索引实现索引覆盖可以不用回表
缺点： - 联合的列数过多，会导致维护开销、增删改的成本增大
- 如果查询条件是 ‘或’ 则不会走联合索引，而是部分走索引
- 索引失效问题
最左前缀和最左匹配原则
联合索引是有顺序的，默认是按联合的第一列进行排序，之后在相同第一列的情况下，才会对之后的列进行排序，也就是，联合索引按列从左到右的顺序分别进行排序。
最左匹配原则是说，根据where条件去匹配单列或联合索引过程中，如果是可以走联合索引，是从最左开始匹配选择最合适的索引(索引覆盖率最高的索引进行匹配)
联合索引失效
- or
- LIKE '%···'
- where条件，跳过联合索引的最左前缀
- 最左前缀列的 范围查询（不等于、大于、小于）
- 最左前缀列的 IN 列表值过多 (通常小于10个)
失效的原因：
如果范围过多，就算走索引找到的记录也不是完整的记录，还是需要回表再查询，当范围过大，要访问索引之后再频繁回表，执行器会认为全表扫描的执行计划比走索引的效率更高
回表与辅助索引
索引下推（ICP）
索引下推（Index Condition Pushdown，ICP）是MySQL5.6的优化，改进了二级索引的扫描策略，可以减少服务层访问存储引擎的次数，以及减少存储引擎访问基表的次数。
ICP适用于 InnoDB 和 MyISAM ，可以减少回表次数
MySQL分层架构
- 服务层，也叫Server层，是用来解析SQL的语法语义，以及生成查询计划、接管从存储引擎层上推的数据进行二次过滤的分层
- 存储引擎层是按照Server层的请求，通过索引或者全表扫描的方式获取并返回数据的分层
配置索引下推
-- 查看索引下推是否开启
select @@optimizer_switch
-- 开启索引下推
set optimizer_switch="index_condition_pushdown=on"
-- 关闭索引下推
set optimizer_switch="index_condition_pushdown=off"
流程：无索引下推
无索引下推时，优化器不会将and条件连接成联合索引，比如现在有索引：
idx_age_name(age, name)，查询条件是 age=18 and name="罗一"
此时执行器会根据最左前缀匹配原则，匹配到age字段，但是不会将name字段联合，导致在age_name索引树上匹配到合适的age，会忽略掉name字段，导致回表匹配name字段，最后返回的结果在服务层按照name="罗一"进行过滤
这会导致明明当前索引树有name字段，却还要回表查询name值，最后还在服务层进行条件过滤
流程：索引下推
会合并and条件，判断是否可以走联合索引，防止回表
索引下推失效
- 引用子查询条件，子查询不是存储引擎层执行的内容；
- 引用表达式条件，比如：
idx(col1+col2)，因为表达式和函数，也不是存储引擎层Innodb所可以维护的索引，所以会导致服务层执行器制定执行计划时，会放弃对应字段的索引； - 虚拟列上的索引，比如函数索引
idx(dosomething(col))
回表
对应字段的索引找到了记录，但是因为辅助索引只能查到对应字段
所以如果待查询的字段存在一个或以上，不是辅助索引对应的字段，那么需要根据查到的主键进行回表
所谓回表就是回到聚簇索引（主键索引）中查询回表的主键对应的其他需要的字段
当然MyISAM、Memory之类的存储引擎必须要回表，因为他们的表数据跟索引不聚簇；
索引覆盖
当然，通过回表的定义可知，其实如果定义的辅助索引或者联合索引包含了所有需求的字段，那么就不需要回表，这种情况就是索引覆盖
自适应Hash索引
用户无法手动创建hash索引；
如果一个加了Hash索引的字段的某一项被查询若干次后，那么这条Hash索引的记录就可以被加载到缓存中的散列表，当再次访问这个条件时，就会从缓存中直接返回索引对应的查询到的主键
ORDER BY
Order By是mysql中的排序方法，是对存储引擎返回的结果集进行排序的方法
在使用Order By时，如果排序的顺序跟联合索引顺序不一致，就会导致只能部分走索引
现在假设索引是：idx_age_name(age, name)：
1.部分走或失效
- 非最左匹配：
如果排序条件是：ORDER BY name, age，此时只能部分走索引，走的是idx_age_name索引中age字段的部分索引； - DESC序：
如果顺序是DESC序，会导致对应列的索引失效
2.完全走联合索引
如果排序条件是：ORDER BY age, name ，此时可以完全走索引。
索引失效问题
普通索引失效
- 大范围查询 或 过多值的 IN 条件
- 范围查询返回结果占总行数的比例在 20% 左右可以认为还能走索引
- LIKE % 左模糊查询
- 无最左前缀条件的联合索引
- 排序操作 走部分索引和完全失效（desc序、非最左匹配）
- 子查询结果列作为 IN 列表
具体走不走索引可以通过 explain 查看执行计划判断
唯一性索引失效
- IN 条件中有重复值，违反唯一性索引
- 跟普通索引一样的失效条件
不建议加索引的情况
- 查询条件中，很少使用的字段可以不加索引
- 如果当前字段是枚举值、或者值的类型很少，并且分布很均匀，那么没必要加索引；但是如果某种值很少，那么也可以加索引
- 举个例子，假如某个字段只有1、2、3
如果1、2、3分布很均匀，那么可以不加索引；
如果某个字段1几乎没有，但是2、3很常见，那么建议加索引
- 举个例子，假如某个字段只有1、2、3
