mysql--(索引）

索引的分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

聚簇索引的 B+Tree 和二级索引的 B+Tree 区别如下：

• 聚簇索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；（一张表最多只有一个主键索引，索引列的值不允许有空值）
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

回表：

1. 先检查二级索引中的 B+Tree 的索引值，找到对应的叶子节点，然后获取主键值，
2. 再通过主键索引中的 B+Tree树查询到对应的叶子节点，然后获取整行数据。

索引覆盖

• 当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引，这种在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」。

总结：在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

• B+Tree vs B Tree：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
• B+Tree vs 二叉树：对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。
• B+Tree vs Hash：Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询。

主键索引:一张表最多只有一个主键索引，索引列的值不允许有空值
唯一索引:一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引:就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE
前缀索引:是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
联合索引::通过将多个字段组合成一个索引，该索引就被称为联合索引。使用联合索引时，存在最左匹配原则，如果不遵循「最左匹配原则」，联合索引会失效

什么时候需要 / 不需要创建索引？

• 字段中存在大量重复数据，不需要创建索引。
• 表数据太少的时候，不需要创建索引。因为索引是会占用物理空间的。
• 经常更新的字段不用创建索引。

有什么优化索引的方法？

• 前缀索引优化：为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。
• 覆盖索引优化：从二级索引中查询得到记录，而不需要通过聚簇索引查询获得
• 主键索引最好是自增的；
• 防止索引失效

索引失效

• 使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

select * from t_user where name like '%林';

• 如果查询条件中对索引字段使用函数，就会导致索引失效。

select * from t_user where length(name)=6;

• 在查询条件中对索引进行表达式计算，也是无法走索引的。

explain select * from t_user where id + 1 = 10;

• 联合索引非最左匹配
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

举个例子，比如下面的查询语句，id 是主键，age 是普通列
select * from t_user where id = 1 or age = 18;