MySQL——索引

最近在准备面试，疏于管理博客，趁此期间，发点这阵子的一部分笔记

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。InnoDB存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在B+树中的。

使用方式：

ALTER TABLE students
ADD INDEX idx_score (score);//创建了一个名称为idx_score,使用列score的索引。

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，对该列创建索引就没有意义。

索引的优点：

• 大大减少了服务器需要扫描的数据量
• 帮助服务器减少排序和临时表（group by 和 order by都可以使用索引，因为索引有序）
• 可以将随机IO变为顺序IO（覆盖索引）

索引缺点：

• 创建索引要花费时间，占用存储空间
• 减慢数据修改速度

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

B+树

定义：

1. 树中每个非叶节点最多有m个孩子
2. 根节点至少有2个孩子
3. 除根节点外，每个非叶节点至少有ceil(m/2)个孩子（返回大于或者等于指定表达式的最小整数）
4. 有 n 个孩子的结点有 n-1 个键值
5. 所有叶节点在同一层，包含了所有键值和指向相应数据对象的指针，键值升序
6. 每个叶节点中的孩子数允许大于 m。假设叶节点可容纳的最多键值数为 m1，则指向数据对象的指针数为 m1，孩子数 n 应满足 ceil(m1/2) < n < m1

通常在 B+树上有两个头指针，一个指向根结点（进行随机搜索），一个指向关键字最小的叶结点（进行顺序搜索）。
随机查找 key 时每次所需要的磁盘 I/O 次数等于 B+树的高度

为什么底层数据结构使用B+树，而不是B树？

1. **B+树的查询效率更加稳定。**B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点，所以任何关键字查找必须走一条从根节点到叶子节点的路；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
2. **B+树的磁盘读写代价更低。**B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。
3. 树的遍历效率更高。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树这样的操作效率太低。

聚簇索引和非聚簇索引

聚簇索引

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

   • 页内的记录是按照主键的大小顺序排成一个单向链表。
   • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
   • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2. B+树的叶子节点存储的是完整的用户记录。

   所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

聚簇表示数据行和相邻的键值紧紧地存储在一起。因为无法同时把数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB通过主键聚簇索引。每张表都会有一个聚簇索引。聚簇索引是一级索引。
聚簇索引一般是主键；没有主键，就是第一个唯一键；没有唯一键，就是隐藏ID。

聚簇索引以外的所有索引都称为二级索引。在 InnoDB 中，二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。 InnoDB 使用这个主键值来搜索聚簇索引中的行。

优点

1. 可以将相关数据保存在一起，只需一次IO就可以取出相邻的数据
2. 数据访问更快，因为索引和数据保存在同一个B+树中
3. 使用覆盖索引扫描的查询可以直接使用叶节点的主键值

缺点

1. 插入速度严重依赖于插入顺序。
2. 更新聚簇索引列的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置
3. 插入新行或者更新主键导致需要移动行的时候，可能面临页分裂的问题。**当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。**页分裂会导致表占用更多的磁盘空间。
4. 可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时
   候
5. 二级索引（非聚簇索引）可能会更大， 因为在二级索引的叶子节点包含了引用行的主键值。
6. 二级索引访问需要两次 B 树索引查找，而不是一次。因为二级索引中叶子节点保存的是行的主键值，要找到数据行，还需要拿主键值到聚簇索引中进行一次查找。

非聚簇索引

这个B+树与上边介绍的聚簇索引有几处不同：

• 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：
  • 页内的记录是按照c2列的大小顺序排成一个单向链表。
  • 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
• B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
• 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下：

1. 确定目录项记录页

   根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）。

2. 通过目录项记录页确定用户记录真实所在的页。

   在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。

3. 在真实存储用户记录的页中定位到具体的记录。

   到页34和页35中定位到具体的记录。

4. 但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

各位各位，看到步骤4的操作了么？我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树！！！

为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不就好了么？你说的对，如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地方了呀～相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为为c2列建立的索引。

总结

聚簇索引的树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引只能按照一种方法进行排序。

非聚簇索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引。

MySQL回表

• 如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索 主键索引树。
• 如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为回表。

索引覆盖

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

覆盖索引的优点：
1）索引条目远少于数据行大小，如果只需要读取索引，则 MySQL 就会极大地减少数据访问了，这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对 IO 密集型应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
2）因为索引是按照列值顺序存储的，对于 IO 密集型的范围查询会比随机从磁盘读取每一行数据的 IO 次数会少得多。
3）InnoDB 的二级索引在叶节点中保存了行的主键值，如果二级索引是覆盖索引，则可以避免对主键聚簇索引的二次查询

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以 MySQL 只能使用 B 树索引做覆盖索引。

MyISAM中的索引方案简单介绍

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。

MyISAM记录也需要记录头信息来存储一些额外数据，我们以上边唠叨过的index_demo表为例，看一下这个表中的记录使用MyISAM作为存储引擎在存储空间中的表示：

• 由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

  这一点和InnoDB是完全不相同的，在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引！

• 如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和InnoDB中的索引差不多，不过在叶子节点处存储的是相应的列 + 行号。这些索引也全部都是二级索引。

索引的最左前缀原则：

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

索引下推：

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

Hash 索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
只有精确匹配索引所有列的查询才有效！
在 MySQL 中，只有 Memory 引擎显式支持 Hash 索引。
限制：
1）哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（无法使用覆盖索引）。不过，访问内存中的行的速度很快。
2）哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
3）哈希索引不支持部分索引列匹配查找。比如建立复合哈希索引(A,B)，无法仅使用 A 使用哈希索引去查询
4）不支持范围查询，仅支持等值查询
5）哈希冲突严重时，索引维护的代码很高。

B 树索引与 Hash 索引比较

1）如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
2）哈希索引也没办法利用索引完成排序，以及 like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
3）哈希索引也不支持多列联合索引的最左匹配规则；
4）B+树索引的关键字检索效率比较平均，在有大量重复键值情况下，哈希索引的效率是极低的，因为存在所谓的哈希碰撞问题。

适合建索引的情况

1. 主键
2. 连接中频繁使用的列
3. 在某一范围内频繁搜索的列和按排列顺序频繁搜索的列

不适合建索引的情况

1. 很少或从来不在查询中引用的列
2. 只有两个或很少几个值得列
3. 以bit text image数据类型定义的列
4. 数据行数很少的小表

索引优点

1. 大大减少了服务器需要扫描的数据量
2. 帮助服务器减少排序和临时表
3. 可以将随机IO变为顺序IO（覆盖索引）

索引缺点

1. 创建索引要花费时间，占用存储空间
2. 减慢数据修改速度

索引失效

---