翻遍了三本书，我总结了这些MySQL索引高频知识

MySQL索引高频总结

当谈论索引的时候，我们在谈论什么？

“索引”是一种由存储引擎实现的，用于快速查找记录的一种数据结构，举例来说，就像大部书的目录。复杂的ORM工具也只能创建主键相关的索引查询，在数据库的查询优化方面，对索引的优化举足轻重，因此了解和学习索引，依然十分重要。

1. 使用索引优势劣势

1. 优势：

   • 使用索引，很大程度地减少了需要对数据库数据的扫描量，对数据的检索不必全盘扫描，减少了对数据库的I/O次数。
   • B-Tree索引不但可以在叶子节点中存储数据，并且按照顺序存储，因此对于常见的分组和排序，这意味着实现这些只需要查询索引。
   • 使用唯一索引（Unique Index）可以维护每行数据的唯一性（数据不重复）。
   • 将随机IO变成顺序IO，从而提升效率，B-Tree中对索引列采取顺序组织存储，所以对索引进行查询，实际是进行连续的顺序查询。

2. 劣势：

   索引一直是优化数据库查询的最优方案吗？

   • 索引的使用本身存在代价，对数据量非常小的表来说，使用索引带来的物理空间开销及索引维护的时间成本可能远远高于全表扫描的代价。
   • 维护索引需要时间，例如以B+树为数据结构的索引，在进行数据插入更新删除时，需要维护该N叉树的特性，这无疑延长了操作的实际反映时间。

​

2. 索引中数据结构

1. Hash表

   • 我们知道，哈希算法是一种时间复杂度**O(1)**级别的算法。而哈希表是一种以 key-value 形式存储数据的结构。

   • 使用哈希的思路：把值放在数组里，用一个哈希函数（数据库中一般使用除法散列）把key 换算成一个确定的位置，然后把 value 放在数组的这个位置。当出现多个 key 值换算为同一个值的情况，在该Hash值出拉出一个链表，每次读取该Hash值。

   

   • 使用Hash表的缺点：

     • Hash表是一种键值对查询，对于等值数据的查询来说效率相当高，但当Hash表中存入的key值非有序的情况下，我们对某个范围内key值的查询，就要遍历整个Hash表，时间成本相当高，因此我们得出结论，哈希表适用于等值查询。

2. 有序数组

   • 有序数组很好地兼容了等值查询和范围查询两种查询方式，但从查询的时间效率上来看，都能高效完成。

   • 然而有序数组的数据更新成本过高，需要移动或遍历数组中已有元素，因此这种数据结构只适用于不会再修改的静态数据，如历史账单等等。

3. 二分查找树->平衡二叉树->B+树

   • 二叉查找树：左子树的键值<根键值<右子树，二叉查找树的平均查询效率要高于顺序查询。

   • 对值8进行查询，对比如下图这两课二叉查找树时，我们发现，第一棵树的查询次数为：5次，而第二棵树仅仅为3次，不难发现，即使是对于同样一种数据结构，不同的树形态也导致不同的查找效率，因此引出平衡二叉树的使用。

     

   • 平衡二叉树（AVL）：

     • 在满足二叉查找树的定义基础上。
     • 还要满足任一节点的两个子树高度差<=1

   • B+树：

     • B+树首先是N叉树，这样设计的原因在于，树的每一层对于硬盘来说是一块数据，树高过高时，意味着数据读写要使用多个数据块，所以设计成N叉树减少了树高，减少了磁盘的IO次数。

     • B+树是为磁盘等直接存储辅助设备设计的一种平衡查找树，这样描述是因为B+树不但具有AVL树的性质，并且由于它摆脱了二叉树的限制，因此具有高扇出性质，即N叉树高度低，因此减少了磁盘的IO读写次数。

     • B+树的所有记录都按照键值大小的顺序，存放在叶子节点上，每个叶子结点到根的距离都相同。下图给出一棵高度为2，扇出为5 的B+树的实例：可以看出各个叶子节点间使用双向链表指针连接。

       

     • B+树通过旋转，拆分与合并页，填充因子等操作，维持了树的增删改查等数据操作。

3. InnoDB与MyISAM引擎索引实现与对比

InnoDB引擎的索引实现

1. 聚簇索引
   1. 聚簇索引的实现：
      • 按照每张表的主键构造一棵B+树，叶子节点存放的是整张表的数据，因此叶子节点被成为数据页，数据页同样通过双向链表来链接。
      • 实际上，一张数据表上只能按照一棵B+树进行排序，所以一张表只能拥有一个聚簇索引。
      • InnoDB由于聚簇索引的要求，因此必须存在主键（MyISAM可以没有），如果没有显示指定主键，则选取首个为唯一且非空的列作为主键索引，再否则，MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
      • 非叶子节点存储的是键值和指向叶子节点（数据页）的偏移量。
   2. 聚簇索引的优势：
      • 查询效率高：查询数据不需要通过全表扫描，只需要访问聚簇索引的节点，最终在叶子节点处获得结果。
      • 适用于范围查找和排序查找：上面我们说到，B+树类型的索引，叶子节点存储值具有顺序存储的特点。而对于范围查找，我们只需要通过叶子节点上层的节点，就可以获得页的范围了。
   3. 要注意的点：
      • 聚簇索引的连续存储，指的并不是物理地址上的连续，而是通过双向链表维护地址指向的逻辑连续！
2. 辅助索引（非聚簇索引）:
   1. 辅助索引的实现：
      • 辅助索引的叶子节点不包括行数据，而是主键值。
      • 一个数据表中可以拥有多个辅助索引，并且通过其叶子节点上指向聚簇索引的主键，找到对应主键，再通过该主键对应的索引来找到一个数据行记录。我们可以发现，通过辅助索引查找行数据，需要进行2次查找。也就是所谓的回表操作。
3. 哈希索引
   1. 上面提到，InnoDB引擎中的哈希索引为自适应的哈希索引，无法人为进行干预，索引的创建由引擎本身进行判断。可以通过参数 innode_adaptive_hash_index来禁用/开启这一特性，默认开启。
   2. 哈希索引在InnoDB引擎数据库中，使用表空间中的space_id+offset来对应查找数据行位置。
4. 全文检索：
   1. InnoDB引擎从1.2X版本开始支持全文检索技术，使用倒排索引实现，即在辅助表中实现key与key所在的一或多个位置间的映射，通常通过关联数组实现
   2. 存在限制：
      • 目前每张表中只能有一个全文检索的索引
      • 多列组合的索引中索引列必须使用同样的字符集和排序规则。
      • 不支持无单词界定符的语言，如中文。

MyISAM引擎的索引实现

1. 主键索引：

   1. 非聚簇索引： 叶子节点的存放的是数据记录的地址，索引没有和数据行保存在一起。
   2. MyISAM的数据文件和索引文件是分开存储的 。

2. 辅助索引：

   1. 与主键索引结构相同， 主键索引是根据主关键字来构建的B+树存储结构，辅助索引则是根据辅助键来构造的B+树存储结构，相互独立。