MySQL 索引

MySQL 索引

1. 什么是索引

索引就是数据库中一种加快数据检索的数据结构。

2.索引的分类。

1. 逻辑上分为

   1. 功能
      1. 主键索引
      2. 唯一索引
      3. 普通索引
      4. 全文索引
   2. 列数
      1. 单行索引
      2. 复合索引

2. 物理分类

   1. 聚簇索引：聚簇索引树携带全部数据。
      1. 创建一个表的时候，如果有主键，主键就是聚簇索引，如果没有主键，那么就用唯一索引做聚簇索引，如果唯一索引都没有，那么以隐式主键作为聚簇索引。 非叶子节点上存放聚簇索引值以及指向下一页的指针，叶子节点上存放聚簇索引值和这个索引值对应的那一列的全部数据。
   2. 非聚簇索引：非聚簇索引树的携带的是当前这一行的数据。
      1. 当有主键列作为聚簇索引的时候，其他索引都是非聚簇索引
      2. 非聚簇索引树的非叶子节点上，存放的是非聚簇索引值以及指向下一页的指针。叶子节点上存放的是聚簇索引值，以及当前行的数据。

3. B+树和B-树对比。
   在InnoDB引擎下，MySQL采用B+树作为索引树。b+树相对于b-树来说，b+树的非叶子节点上存储的都是索引，以及指向下一页的指针。非叶子节点上存放的是索引，以及这张表或者是这行的数据。而b-树存放数据的策略是，所有节点上都存放数据。MySQL每一页能存放16KB的数据，如果存数据，肯定会产生更多页分裂，就会导致树的高度变高，树的高度每增加一层，就会多一次磁盘IO，这样会很耗费资源。存放索引的话，也就意味着，这一页，能够指向更多页，树的高度就会大幅度降低，就可以减少磁盘IO的次数。这也就意味着b+树的性能更优异。所以InnoDB引擎下采用的是B+树。

   b+Tree叶子节点分裂规则：将这个叶子结点分裂成左右两个叶子结点，左叶子结点包含前 m/2 个（2个）记录，右结点包含剩下的记录，将中间的 key 进位到父结点中。注意：中间的 key 仍会保留在叶子节点一份
   非叶子节点分裂规则：左子结点包含前 (m-1)/2 个 key，将中间的 key 进位到父结点中（不保留），右子节点包含剩余的 key

MySQL在INnoDB引擎下，推荐使用主键自增长。因为在进行数据添加的时候，b+树为了保持树的结构，会出现页分裂的情况，出现这种情况是十分耗时和耗费性能的，而采用主键自增长，在添加新数据的时候，直接在叶子节点的最后添加数据。这样就会很快。能够提升性能。

4. 覆盖索引和回表
   覆盖索引：当我们在非聚簇索引树上查询数据的时候，如果我们需要的数据在非聚簇索引树上都能查到，不需要到聚簇索引树上查询。这就叫覆盖索引。
   回表 ： 当我们在非聚簇索引树上查询数据的时候，如果非聚簇索引树叶子节点下的数据不能够满足此次查询，需要回到聚簇索引树上查询表中其他行的数据，这种行为叫做回表。

# 注意：age不是主键。
SELECT age FROM tb_student WHERE age = ?; # 这种情况就会出现覆盖索引，因为以age是当前行的索引，直接就能在当前非聚簇索引树下拿到数据
SELECT * FROM tb_student WHERE age = ?;#这种情况下，age所在的索引树下，不能满足此次查询的所需要的数据，就需要回到聚簇索引树下查询，这种情况就叫回表。

5.最左前缀原则
当一张表有一个联合索引，那么任何以左边开头的索引，都能匹配到索引。

# a,b,c 三列组成复合索引。
SELECT * FROM t1 WHERE a = ?;
SELECT * FROM t1 WHERE a = ? AND b = ?;
SELECT * FROM t1 WHERE a = ? AND b = ? AND c = ?;
以上这种情况都会走复合索引。

6. 索引失效
   索引失效出现在需要回表的情况下
   1. 使用 ！= 运算
   2. 走二级索引扫描全表，这种情况下有回表操作。
   3. 模糊查询，以%开头
   4. 字符串不加引号
   5. or的条件不是索引列。
   6. 不符合左前缀原则
   7. 使用IS NULL 或者 IS NOT NULL ，判断，MySQL推荐使用非空列做判断。
   8. 函数及计算问题，一旦在字段上应用了计算或函数，都会造成索引失效
      实际以优化器结果为主。