InnoDB-索引

四、索引

mysql支持的常见索引：B+，全文、hash

1.B+树索引

B+树索引可以分为聚簇索引和非聚簇索引。不管是聚簇还是非聚簇，内部都是B+树，即高度平衡的，叶子存放着所有的数据，聚簇和非聚簇不同的是叶子节点存放的是否是一整行的数据。
聚簇索引就是按照每张表的主键构造一颗B+，叶子节点存放整行数据，每个数据页通过双向链表连接。聚簇索引的存储在物理上并不是连续的，他们一是通过页之间的双向链表，二是页中的记录也通过双向链表维护。聚簇的好处就是对于范围查找和对于主键的排序查找非常快。
辅助索引（非聚簇索引），叶子并不包含行的全部记录，它除了存储辅助索引的value，每个叶子节点中的索引中还包含一个书签，书签指明去哪里找索引对应的行数据，也就是聚簇索引的健。一张表可以有多个辅助索引。（尽量不要用select *，避免回表）

不适合建B+的情况

关于什么时候适合➕B+索引，不是所有要查询的字段都适合，比如对于性别这种字段，就两个值，这样就完全没有必要，B+适合高选择性字段。Cardinality表示索引中不重复记录的预估值，应该让Cardinality/row尽可能接近1。
这个Cardinality你用show index的时候可以显示出来，但是这只是一个预估的值，他也不是每次修改索引都会更新的，所以你可以选择在数据库空闲的时间做analyze table操作，为了让优化器更好的为你工作，因为可能Cardinality非常小，查询优化器就不愿意走这个索引了。

B+索引叶子节点因为是链表链起来的（叶子节点里面的行数据也是链表链起来的），所以没办法做二分，那么，每个叶子节点有个page directionry，它是专门用来给叶子节点做二分的。

⚠️B+索引并不能找到一个给定健值所在的具体行，B+索引能找到的只是被查找数据所在的页，然后数据库通过把页读到内存，再在内存中进行查找，最后得到要查找的数据。

1.1 B+索引的使用

（1）.联合索引
联合索引也是一棵B+，联合索引除了特定查询需要，第二个好处是在某些场景下可以当作排序，比如我们给（a，b）建立了联合索引，所以下列语句优化器就会选择联合索引,因为b是在每一段a中排序的
select ... from table where a=xxx order by b;
同样，对于（a，b，c）下面语句同样可以得到结果：
select ... from table where a=xxx order by b；
select ... from table where a=xxx and b =yyy order by c；
但是
select ... from table where a=xxx order by c不行，因为在a里面c不是排序的。
（2）.覆盖索引
就是从辅助索引就可以查到数据，就不需要再走聚簇索引了。而且还有一种 情况是比如有个表table。
select count(*) from table。如果这个表有辅助索引是会通过辅助索引去做count的，因为辅助索引小，肯定IO少。
（3）.优化器选择不使用索引的情况
有时候在进行查询的时候，直接走B+索引，也就是全表的一个扫描，多发生的范围查询和join连接。
比如用select * 对订单id进行某个范围的查询，如果走辅助索引还要去聚簇索引读取整行数据，这种情况就会直接使用聚簇索引。另外，索引查询优化器会帮我们做选择，但它去计算选择哪个索引更好的时候本身也会浪费时间，可以用use index强制走哪个索引。
（4）.MRR优化
Multi-range-read优化的目的就是减少磁盘的随机访问，把随机访问转换成较为顺序的访问，减少IO。
对于range类型的查询，使用mrr的好处：
在查找辅助索引时，首先根据得到的查询结果，按照主键进行排序，按照主键的顺序进行书签查找。（比如上个例子select * ，我用辅助索引，那要根据主键再去查聚簇索引，所以这个时候，如果给主键索引排个序，可能会减少IO），
减少缓冲池中页被替换的次数，
批量处理对键值的查询。

对于范围查询和join，mrr的优化方式是：
将查询得到的辅助索引放到一个缓存里，这时缓存里的辅助索引是键值排序的；将缓存中的键值用rowId进行排序，根据rowId的排序顺序来访问实际的数据文件。

1.2 Fast Index Creation

在MySQL5.5版本之前，一个很糟糕的创建和删除索引的方式是：
当你要对索引进行DDL操作，会先创建一张临时表，然后把原表中的数据倒入进去，再把原表删除然后把临时表重命名为原来的表。
那么，如果是对一张大表的索引进行这样的操作，是非常耗时的，这段时间数据库是不对外提供服务的。InnoDB1.0之后的版本支持FIC，在创建辅助索引的时候可以对表加个S锁就行，但是这个时间段内也是不能修改的，只能读。

1.3 Cardinality

前面也说了这是查询优化器会重要参考的一个参数。但是Cardinality不是实时更新的，比如一个数据量非常大的表，不可能在每次更新索引的时候都去更新Cardinality，那么InnoDB存储引擎对Cardinality的更新策略为：
1.表中1/16的数据已经发生过变化
2.表中发生变化的次数 > 2000000000
那么，Cardinality到底是怎么统计出来的呢？
1.取B+叶子节点的数量
2.随机拿8个叶子节点统计每个叶不同的记录个数。
3.然后（P1+...+P8）*A/8
这个数据只是采样得到的，所以可能你数据没变每一次算出来的结果也不同。

2.哈希索引

O (1)时间的算法，自适应hash不是DBA能干预的，hash只适用于=的情况，范围就不行了。

3.全文索引

B+树索引也可以根据索引的前缀查找，比如
select * from blog where content like ‘xxx%’，但很多情况是需要‘%xxxx%’。
倒排索引
全文索引使用倒排来实现，倒排是一种结构，和B+索引一样。
Innodb支持全文检索，并且是full inverted index的形式，就是单词对应的不仅有文档id，还显示在文档中第几个单词（DocumentId，Position）看做一个ilist，所以在全文检索的表中有两个列，一个是word字段，一个是ilist字段，并且word字段上有索引。
倒排索引需要把word放在一张表中，这个表成为辅助表，在Innodb引擎中，为了提高全文检索并行性能，共有6张辅助表。辅助表是持久表，放在磁盘上，然而Innodb存储引擎的全文索引中。
一张表只能有一个全文检索的索引。