MySQL(三)--关于索引的一些

MySQL(三)–关于索引的一些

1，什么是索引？

“索引”是为了能够更快地查询数据。比如一本书的目录，就是这本书的内容的索引，读者可以通过在目录中快速查找自己想要的内容，然后根据页码去找到具体的章节。

数据库也是一样，如果查询语句使用到了索引，会先去索引里面查询，取得数据所在行的物理地址，进而访问数据。

2，索引的优缺点：

优势：以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间。索引的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。

3，MySQL中有哪几种索引？

常见的有4种：

• ①主键索引：是一种唯一性索引，但它必须指定“Parimary key”
• ②唯一索引：索引的所有值都只能出现一次，即必须唯一（unique）
  • 主键索引是唯一索引的特殊类型
  • 表中创建主键时自动创建的索引，一个表中只能建立一个索引
  • 一个表的主键只能有一个，而唯一索引可以建立多个
  • 主键不可为null，唯一索引可以为空
• ③普通索引：没有唯一性之类的限制（主要是对数据表中的数据进行精确查找）
• ④全文索引：它的主要作用是：搜索数据表中的字段是不包含我们搜索的关键字（模糊查询）

以上是常见的四大索引类型，还有比如组合索引、聚簇索引…等等。

对于组合索引：其中，组合索引又称为多列索引，上述代码中最后一个例子就是建立了3列的索引。MySQL在根据索引查询时，会遵循“最左匹配”原则，即先根据col1的条件查，再根据col2的条件查，然后再根据col3的条件去查。

如果跳过了一个列直接查后面的列，比如下面的语句，就不能使用上面创建的索引了：

对于某列如果是字符串且比较长（比如UUID），推荐使用前缀索引，即匹配前n个字符。具体这个n取值多少是根据你的数据来的，《高性能MySQL》里提供了一个技巧：通过使用LEFT函数查询，从1开始，不断增加n的值，直到查询结果的行数接近完整列的查询结果的行数，就是合适的n的值。

4，索引的数据结构：

MySQL的索引是由存储引擎来实现的。由于存储引擎不同，所以具有不同的索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等。这里由于主要介绍BTree索引和B+Tree索引，我们平时使用最多的InnoDB引擎就是基于B+Tree索引的。

从二叉搜索树聊起：

了解过数据结构的朋友应该知道一种叫二叉树的数据结构。二叉树根据用途不同，衍生了不同的变种，比如堆，比如二叉搜索树。

而二叉搜索树中，为了防止极端情况树的高度过大影响查询效率 ，所以衍生出了一些平衡二叉查找树，最典型的就是AVL和红黑树。

但二叉树在数据量较大时，深度过深，不太适合数据库的查询 所以数据库使用了多叉树。

特点：
右边的支树>父节点
左边的支树<父节点

缺点：若索引是递增的，根据二叉树的特点，二叉树结构就变成了链表结构。所以，二叉树不适合用于递增的字段。

红黑树（二叉平平衡树）：

特点：

• 包含二叉树的特点
• 在二叉树特点的基础上，当单边树的高度>3时会自动平衡
• 一个节点只能放一个数据

缺点：红黑树存储大量数据时，树的高度不可控（磁盘IO次数不可控）

可以看到同样的递增列，若结构为二叉树，查找6的元素要经过6次磁盘IO，而红黑树3次，所以，红黑树效率要比二叉树效率高。

B-Tree：

BTree（又称为B-Tree）是一个平衡搜索多叉树。BTree的结构如下图：

设树的度为2d（d>1），高度为h，那么B-Tree有以下性质：

• 每个叶子结点的高度一样，等于h；
• 每个非叶子结点由n-1个key和n个指针组成，key和指针相互隔离，结点两端一定是key；
• 叶子结点指针为null；
• 非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的其它列的数据；
• 在B-Tree中，对索引列是顺序存储的，所以很适合查找范围数据和ORDER BY操作。

缺点：MySQL对一个大节点推荐给的总容量大概为16kb，若在大节点总容量固定的情况下，索引表列多的情况下，会导致一个大节点下横向能存的索引个数比较少；表数据量过大的情况，树的高度也不可控。

B+Tree：

特点：

• B+Tree中的数据表中的记录都存放在叶子节点中，非叶子节点中不存放记录，只存放key（索引）
• B+Tree的叶子节点中除了存放记录，还有一个指向下一个节点的指针，这是为了提高区间访问，方便扫库，直接从叶子节点往下扫一遍即可。B+Tree对范围查找非常高效。
• B+Tree是链表+平衡二叉树
• 叶子节点高度都是一样的。

B+Tree是B-Tree的一种变种。B+Tree和BTree的不同主要在于：

• B+Tree中的非叶子结点不存储数据，只存储键值；
• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
• B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

结构图：

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，不用从头再查询一次，极大提到了区间查询效率。

B+Tree对比BTree的优点：

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构。

磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找。

已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

Hash表：

特点：

• hash基于哈希表实现，只有精准匹配索引所有列的查询才有效，即不支持任何范围查询
• hash索引数据并不是按照索引数据顺序存储的，所以无法用于排序
• 不能避免全局扫描
• 哈希索引值包含哈希值和行指针，而不存储字段值（所以，等值查询时，对索引值进行hash计算得到结果值，根据结果值在hash表中快速定位到索引所对应数据在磁盘文件中的磁盘指针（无关数据表大小））

感谢并参考：

https://www.javazhiyin.com/42676.html（MySQL索引原理）