关于MySQL索引的一些总结

基本原理

1、把创建了索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址
4、在查询的时候，先拿到倒排表的内容，在取出数据地址链，从而拿到具体数据；

B+树

在MySQL中，一个page（页）就等于16KB在查询的时候，每个查询的最小单位是一个page
B+树就是在B树基础上，叶子节点之间多了个指针，结构如下图：

B+树B树和B+树的区别：

1. 节点存储内容：再B树中，所有的节点都可以存储键值和数据，而再B+树中，只有叶子节点存储键和值数据，而非叶子节点只存储见值。
2. 链接方式：在B+树中，所有叶子节点通过指针相互连接，形成一个链表结构，这使得范围查询更加高效，而在B树中，节点之间没有这样的链接。
3. 分支因子：由于B+树的非叶子节点只存储键值，因此每个节点可以存储更多的键，从而使得树的高度耕地，查询效率更高；
4. 查询效率：在B树中，由于数据可以存储在内部节点，因此查询可能在非叶子节点就结束。而B+树中，所有数据都存储在叶子节点，因此每次查询都需要访问叶子节点，查询路径相对固定。

索引类型：

1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引（组合索引）
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
聚族索引、非聚族索引
其中唯一索引为聚族索引

组合索引的执行步骤是怎样的

如下图，查找 SELECT * FROM table WHERE b = 1 AND c = 1 AND d = 1 ,联合索引为bcd，主键为a。
叶子节点存储的不是完整的某条数据，而是存储索引字段以及这条数据的主键，当找到索引字段时，再根据主键回表查询出具体的数据。

最左匹配原则

还是以上的例子，联合索引为bcd，b在最左，那么查询的时候必须带上b字段，b在WHERE条件的顺序没有关系，但是必须带上b字段才能哥走索引，MySQL类似比较字符串大小一样，会从最左的字段开始比较，如果没有最左字段，无法找到下一层节点位置。

覆盖索引

还是以上例子，如果我查询的只有b字段，我的索引里面已经存了b字段，那么我就不需要再回表查询所有字段了

走索引就一定比全表扫描快吗

不一定，还是拿以上的例子，bcd的联合索引，如果我的条件是b > 1，那么如果走索引，MySQL会先找到b = 1的索引，然后根据指针找到后面大于1的所有叶子，每一个页，都需要回表一次，回表次数太多了，反而会降低效率，这种情况下，MySQL就会直接走全表扫描，而不是走我建立的联合索引。

关于索引失效

依然拿以上例子，如果table还有一个字段e，类型为varchar，当我sql如下时：
SELECT * FROM table WHERE e = 1
因为e字段是varchar类型，MySQL会先将1转换为字符串的 “1” 但是这么做，需要把B+树的所有节点都转换，而且转换以后的还很有可能会破坏原有的B+树（比如e中的数据有一个1 一个 a，如果1本来是排在a前面的，e转换为数字类型后，就会变成 0 （PS:字符串如果无法转换成数字，则会默认转换为0），1排在0前面明显就是不合适的），所以这种情况是不会走索引的。

创建索引

create [UNIQUE|primary|fulltext] index 索引名称 ON 表名(字段(长度))

查看索引

show index from 表名

删除索引

drop index[索引名称] on 表名
DROP INDEX emp_name_index ON employee;

聚族索引

聚族索引存储一整条记录

非聚族索引

非聚族索引只存储主键和索引信息

组合索引：

组合索引符合最左前缀，即组合索引abc，如果需要使用到索引，查询条件必须包含a字段（覆盖索引除外）

覆盖索引

当组合索引为abc时，只查询字段abc和主键时，不需要进行回表操作。

索引失效

1、不符合最左前缀
2、需要回表次数过多，导致比全表扫描更慢
3、类型转换导致索引失效

索引匹配原则

MySQL利用=、>=、> 来确定下边界（first key），利用最左原则，首先判断第一个索引键值在where条件中是否存在，如果存在，则判断比较符号，如果为(=,>=)中的一种，加入下边界的界定，然后继续判断下一个索引键，如果存在且是(>)，则将该键值加入到下边界的界定，停止匹配下一个索引键；如果不存在，直接停止下边界匹配。

MySQL索引的数据结构

1、hash索引
2、B+树索引：InnoDb默认使用B+树
在绝大多数需求为单条记录查询的时候可以选择hash索引；

hash和B+树

1、由于底层是使用hash表，以key-value存储，无法直接通过索引查询，只选择一个数据hash索引更快，但是如果选择N条数据，hash索引的时间复杂度是O(N)，由于B+树索引有序，且叶子节点有链表连接，查询效率比hash索引快
2、索引在硬盘保存，一般不会一次性保存到内存中，B+树可以设计允许数据分批加载，同时树的高度较低，查询速率较快
3、硬盘的I/O速度相比内存来说非常慢，而索引是用于加快查询速度的，需要减少I/O操作，内存和磁盘以页为单位交换数据，为了减少I/O，索引在新建节点的时候，是直接申请一个页的空间，存储分配是按页对齐，就实现了一个节点一次I/O。
4、B+ 树是平衡树，它查找任意节点所耗费的时间都是完全相同的，比较的次数就是 B+ 树的高度