[MySQL]索引02

MyISAM中的索引方案

B树索引适用存储引擎有InnoDB,MyISAM,Memory.

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索引是Btree索引；而Memory默认的索引是Hash索引。

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为 数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并 没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。
• 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 +数据记录地址的组合。
• 
  同样也是一棵B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为:首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

① 在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在
MyISAM 中却需要进行一次 回表 操作(根据内存再寻找,也称为回表))，意味着MyISAM中建立的索引相当于全部都是 二级索引 。

② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数
据记录的地址。.

③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说，
InnoDB的所有非聚簇索引都引用主键作为data域。

④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通
过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个
可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐
含字段作为主键，这个字段长度为6个字节，类型为长整型。

MyISAM和InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别：

哪些情况适合创建索引(以及注意事项)

• 字段的数值有唯一性的限制.业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
• 频繁作为 WHERE 查询条件的字段
• 经常 GROUP BY 和 ORDER BY 的列:
  • 索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者
    使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多
    个，那么可以在这些列上建立 组合索引 。
• UPDATE、DELETE 的 WHERE 条件列
  • 对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。
• DISTINCT 字段需要创建索引
  • 这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
• 多表 JOIN 连接操作时，创建索引注意事项
  • 首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
  • 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
  • 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致
• 使用列的类型小的创建索引
• 使用字符串前缀创建索引
  • **在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。**索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
  • 区分度高(散列性高)的列适合作为索引
  • 使用最频繁的列放到联合索引的左侧
  • 在多个字段都要创建索引的情况下，联合索引优于单值索引

哪些情况不适合创建索引

• 在where中使用不到的字段，不要设置索引
• 数据量小的表最好不要使用索引
• 有大量重复数据的列上不要建立索引
• 避免对经常更新的表创建过多的索引
• 不建议用无序的值作为索引
  • 例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
  • 删除不再使用或者很少使用的索引。
• 不要定义冗余或重复的索引
  • 例如已经有了包含name的联合索引,就没有必要再建立name的普通索引了,维护这个索引只会增加维护的成本，并不会对搜索有什么好处。
  • 另一种情况，我们可能会对某个列 重复建立索引 ，比方说对主键再定义一个唯一索引和普通索引,可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

Hash结构

Hash 本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。

Hash 算法是通过某种确定性的算法(比如 MD5、SHA1、SHA2、SHA3)将输入转变为输出。 相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

加速查找速度的数据结构,常见的有两类::

• 树 ,例如平衡二叉搜索树,查询/插入/修改/删除的平均时间复杂度都是O(log2n)
• 哈希,查询/插入/修改/删除的平均时间复杂度都是O(1)

Hash结构效率高，那为什么索引结构要设计成树型呢

• 原因1: Hash 索引仅能满足(=)(<>)和 IN 查询。如果进行 范围査询，哈希型的索引，时间复杂度会退化为0(n);而树型的“有序”特性，依然能够保持0(log2N) 的高效率。
• 原因2:Hash 索引还有一个缺陷，数据的存储是 没有顺序的，在ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。
• 原因3:对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。
• 原因4:对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是 索引列的重复值如果很多，效率就会降低 。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

Hash索引适用存储引擎有Memory,而MyISAM,InnoDB不支持Hash索引

Hash 索引存在着很多限制，相比之下在数据库中 B+ 树索引的使用面会更广，不过也有一-些场景采用 Hash 索引效率更高，比如在键值型(Key-Value)数据库中，Redis 存储的核心就是 Hash 表。

MySQL 中的 Memory 存储引擎支持 Hash 存储，如果我们需要用到查询的临时表时，就可以选择 Memory 存储引擎，把某个字段设置为 Hash 索引，比如字符串类型的字段，进行 Hash 计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值査询的时候，采用 Hash 索引是个不错的选择。

另外，InnoDB本身不支持 Hash 索引，但是提供自适应 Hash 索引(Adaptive Hash Index)。什么情况下才会使用自适应 Hash 索引呢?如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash 表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让 B+ 树也具备了 Hash 索引的优点。

B树

B树是Balance Tree,也就是多路平衡查找树.

B 树的结构如下图所示：
一个 M 阶的 B 树（M>2）有以下的特性：

1. 根节点的儿子数的范围是 [2,M]。
2. 每个中间节点包含k-1个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为[ceil(M/2), M]。
3. 叶子节点包括k-1个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
4. 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]<Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于(Key[i-1], Key[i])的子树，P[k]指向关键字大于 Key[k-1] 的子树。
5. 所有叶子节点位于同一层。

B+树和B树的差异

• B+树有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数+1。
• B+树非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
• B+树非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非叶子节点既保存索引，也保存数据记录 。
• B+树所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B+树的中间节点并不直接存储数据,这样的好处有什么?

• 首先,B+树查询效率更稳定,因为B+ 树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
• 其次，B+树树的查询效率更高。这是因为通常 B+ 树比B树更矮胖(阶数更大，深度更低)，查询所需要的磁盘I/O也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。

思考题：为了减少IO，索引树会一次性加载吗？

• 数据库索引是存储在磁盘上的,如果数据量很大,必然导致索引的大小很大,超过几个G
• 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO

• InnoDB 存储引擎中页的大小为 16KB，一般表的主键类型为INT(占用4个字节)或 BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree 中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值，为方便计算，这里的K取值为1000。也就是说一个深度为3的B+Tree 索引可以维护1000*1000*1000=10亿条记录。(这里假定一个数据页也存储10^3条行记录数据)
• 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说査找某一键值的行记录时最多只需要 1~3 次磁盘I/O操作。.

思考题：为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

• B+树的磁盘读写代价更低
  • B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
• B+树的查询效率更加稳定
  • 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

思考题：Hash 索引与 B+ 树索引的区别

• Hash 索引不能进行范围査询 ，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
• Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而 B+ 树可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
• Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+树索引数据是有序的，可以起到对该字段 ORDER BY排序优化的作用。同理，我们也无法用 Hash索引进行 模糊査询，而 B+ 树使用LIKE进行模糊査询的时候(比如 %结尾)就可以起到优化作用。
• InnoDB 不支持哈希索引