MySQL之索引

前言

 索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

---

一、.为什么使用索引会加快查询？？

 数据库文件是存储在磁盘上的，磁盘 I/O 是数据库操作中最耗时的部分之一。没有索引时，数据库会进行全表扫描，这意味着它必须读取表中的每一行数据来查找匹配的行（时间效率为 O(n)）。当表的数据量非常大时，就会导致大量的磁盘 I/O 操作。有了索引，就可以直接跳到索引指示的数据位置，而不必扫描整张表，从而大大减少了磁盘 I/O 操作的次数。
 MySQL 的 InnoDB 存储引擎默认使用 B+ 树来作为索引的数据结构，而 B+ 树的查询效率非常高，时间复杂度为 O(logN)。索引文件相较于数据库文件，体积小得多，查到索引之后再映射到数据库记录，查询效率就会高很多。索引就好像书的目录，通过目录去查找对应的章节内容会比一页一页的翻书快很多。

索引的创建，如下所示：

create index idx_name on students(name);

二、索引对的分类

 其中，B+树索引是最常见的索引类型，它一种将索引值按照一定的算法，存入一个树形的数据结构中（二叉树），每次查询都从树的根节点开始，一次遍历叶子节点，找到对应的值。查询效率是 O(logN)。
 B+ 树索引也是 InnoDB 存储引擎的默认索引类型。B+ 树中的非叶子节点都不存储数据，只存储索引。叶子节点中存储了所有的数据，并且构成了一个从小到大的有序双向链表，使得在完成一次树的遍历定位到范围查询的起点后，可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。这在处理大范围的查询时特别高效。

三、创建索引的注意事项

尽管索引能提高查询性能，但不当的使用也会带来一系列问题。在加索引时需要注意以下几点：

1. 选择合适的列作为索引；
2. 避免过多的索引；
3. 利用前缀索引和索引列的顺序

索引哪些情况下可能会失效

1. 在索引列上使用函数或表达式（因为数据库无法预先计算出函数或表达式的结果）；
2. 使用 LIKE 操作符，但是通配符在最前面（如果 LIKE 的模式串是以“%”或者“_”开头的，那么索引也无法使用）；
3. OR 操作符：如果查询条件中使用了 OR，并且 OR 两边的条件分别涉及不同的索引，那么这些索引可能都无法使用；
4. 如果 MySQL 估计使用全表扫描比使用索引更快时（通常是小表或者大部分行都满足 WHERE 子句），也不会使用索引；
5. 联合索引不满足最左前缀原则时，索引会失效。

四、为什么 MongoDB 索引用 B树，而 MySQL 用 B+ 树？

B树的特点是每个节点都存储数据，相邻的叶子节点之间没有指针链接

• 那么在查找单条数据时，B 树的查询效率可能会更高，因为每个节点都存储数据，所以最好情况就是 O(1)。
• 但由于 B 树的节点之间没有指针链接，所以并不适合做范围查询，因为范围查询需要遍历多个节点。
• 而 B+ 树的叶子节点之间有指针链接，所以适合做范围查询，因为可以直接通过叶子节点间的指针顺序访问整个查询范围内的所有记录，而无需对树进行多次遍历。
• MySQL 属于关系型数据库，所以范围查询会比较多，所以采用了 B+树；但 MongoDB 属于非关系型数据库，在大多数情况下，只需要查询单条数据，所以 MongoDB 选择了 B 树。

五、为什么要用B+树，而不用普通二叉树、平衡的二叉树、B树？

5. 1 不用普通二叉树

1. MySQL 的默认存储引擎是 InnoDB，它采用的是 B+树索引
2. 普通二叉树存在退化的情况，如果它退化成链表，就相当于全表扫描。

5.2 为什么不用平衡二叉树呢？

1. 读取数据的时候，是从磁盘先读到内存。平衡二叉树的每个节点只存储一个键值和数据，而 B+ 树可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就会下降，查询效率就快。
2. 普通二叉树存在退化的情况，如果它退化成链表，就相当于全表扫描。

5.3 为什么用B+树为不用B树？

B+ 树相比较 B 树，有这些优势：

1. 更高的查询效率 B+树的所有值（数据记录或指向数据记录的指针）都存在于叶子节点，并且叶子节点之间通过指针连接， 形成一个有序链表。
   
   这种结构使得 B+树非常适合进行范围查询，一旦到达了范围的开始位置，接下来的元素可以通过遍历叶子节点的链表顺序访问，而不需要回到树的上层。如 SQL 中的 ORDER BY 和 BETWEEN 查询。
    而 B 树的数据分布在整个树中，进行范围查询时可能需要遍历树的多个层级。
   
2. 更高的空间利用率
   在 B+树中，非叶子节点不存储数据，只存储键值，这意味着非叶子节点可以拥有更多的键，从而有更多的分叉。这导致树的高度更低，进一步降低了查询时磁盘 I/O 的次数，因为每一次从一个节点到另一个节点的跳转都可能涉及到磁盘 I/O 操作。
3. 查询效率更稳定
   B+树中所有叶子节点深度相同，所有数据查询路径长度相等，保证了每次搜索的性能稳定性。而在 B 树中，数据可以存储在内部节点，不同的查询可能需要不同深度的搜索。