一篇文章带你读懂MySQL索引结构和查询优化！

MySQL官方文档中有这样一段描述：

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. But unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

就是说提高查询性能最直接有效的方法就是建立索引，但是不必要的索引会浪费空间，同时也增加了额外的时间成本去判断应该走哪个索引，此外，索引还会增加插入、更新、删除数据的成本，因为做这些操作的同时还要去维护(更新)索引树。因此，应该学会使用最佳索引集来优化查询。

由于篇幅有限，这里只展示一部分，有需要完整版的朋友可以点一点下方链接免费领取

链接：1103806531暗号：CSDN

索引结构

什么是索引

在MySQL中，索引(Index)是帮助高效获取数据的数据结构。这种数据结构MySQL中最常用的就是B+树(B+Tree)。

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

就好比给你一本书和一篇文章标题，如果没有目录，让你找此标题对应的文章，可能需要从第一页翻到最后一页；如果有目录大纲，你可能只需要在目录页寻找此标题，然后迅速定位文章。

这里我们可以把书(book)看成是MySQL中的table，把文章(article)看成是table中的一行记录，即row，文章标题(title)看成row中的一列column，目录自然就是对title列建立的索引index了，这样根据文章标题从书中检索文章就对应sql语句select * from book where title = ?，相应的，书中每增加一篇文章(即insert into book (title, …) values (‘华山论剑’, …))，都需要维护一下目录，这样才能从目录中找到新增的文章华山论剑，这一操作对应的是MySQL中每插入(insert)一条记录需要维护title列的索引树(B+Tree)。

为什么使用B+Tree

首先需要澄清的一点是，MySQL跟B+树没有直接的关系，真正与B+树有关系的是MySQL的默认存储引擎InnoDB，MySQL中存储引擎的主要作用是负责数据的存储和提取，除了InnoDB之外，MySQL中也支持比如MyISAM等其他存储引擎作为表的底层存储引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

提到索引，我们可能会立马想到下面几种数据结构来实现。

(1) 哈希表
哈希虽然能够提供O(1)的单数据行的查询性能，但是对于范围查询和排序却无法很好支持，需全表扫描。

(2) 红黑树
红黑树(Red Black Tree)是一种自平衡二叉查找树，在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。

一般来说，索引本身也很大，往往不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗远远高于内存，所以评价一个数据结构作为索引的优劣最重要的指标就是查找过程中磁盘I/O次数。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的次数。

在这里，磁盘I/O的次数取决于树的高度，所以，在数据量较大时，红黑树会因树的高度较大而造成磁盘IO较多，从而影响查询效率。

(3) B-Tree
B树中的B代表平衡(Balance)，而不是二叉(Binary)，B树是从平衡二叉树演化而来的。

为了降低树的高度(也就是减少磁盘I/O次数)，把原来瘦高的树结构变得矮胖，B树会在每个节点存储多个元素(红黑树每个节点只会存储一个元素)，并且节点中的元素从左到右递增排列。如下图所示：

B-Tree在查询的时候比较次数其实不比二叉查找树少，但在内存中的大小比较、二分查找的耗时相比磁盘IO耗时几乎可以忽略。 B-Tree大大降低了树的高度，所以也就极大地提升了查找性能。

(4) B+Tree
B+Tree是在B-Tree基础上进一步优化，使其更适合实现存储索引结构。InnoDB存储引擎就是用B+Tree实现其索引结构。

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的，如果data值较大时将会导致每个节点能存储的key的数量很小，这样会导致B-Tree的高度变大，增加了查询时的磁盘I/O次数，进而影响查询性能。在B+Tree中，所有data值都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以增大每个非叶子节点存储的key值数量，降低B+Tree的高度，提高效率。

最后

希望这篇文章对大家有帮助！

我这边也整理了一份 架构师全套视频教程 和关于java的系统化资料，包括java核心知识点、面试专题和20年最新的互联网真题、电子书等都有。有需要的朋友可以点一点下方链接免费领取！

链接：1103806531暗号：CSDN