mysql索引io次数_MySQL中的索引-优快云博客

索引介绍

索引是什么

索引是帮助MySQL高效获取数据的数据结构，数据库索引就像是一本书的目录，能够加快数据库的查询速度。

一般来说索引本身很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中，可能是单独存储也可能和数据一起存储。

索引的优缺点

优点：

可以提高数据查找的效率，降低数据库的IO成本。

索引列自动进行排序，可以降低排序操作的成本，降低CPU的消耗。

缺点：

索引会占用磁盘空间。

索引会降低更新表的效率。每一次的数据更新不仅要保存数据，还要保存或更新对应的索引文件。

索引类型

普通索引(INDEX)

普通索引是MySQL数据库最基本的索引类型，没有任何限制。

创建索引：ALTER TABLE table_name ADD INDEX index_name (column)

删除索引：ALTER TABLE table_name DROP INDEX index_name

唯一索引(UNIQUE)

索引列的值必须是唯一的，但是存在是空值。一般在创建表时同时创建主键索引。

创建索引：ALTER TABLE table_name ADD UNIQUE index_name (column)

删除索引：ALTER TABLE table_name DROP UNIQUE index_name

主键索引(PRIMARY KEY)

主键索引是一种特殊的唯一索引，不允许有空值。一般在创建表时同时创建主键索引。

创建索引：ALTER TABLE table_name ADD PRIMARY KEY index_name (column)

删除索引：ALTER TABLE table_name DROP PRIMARY KEY index_name

全文索引(FULLTEXT)

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。

创建索引：ALTER TABLE table_name ADD FULLTEXT index_name (column)

删除索引：ALTER TABLE table_name DROP FULLTEXT index_name

组合索引

创建索引：ALTER TABLE table_name ADD INDEX index_name (a,b,c)

删除索引：ALTER TABLE table_name DROP INDEX index_name

最左前缀匹配原则：创建(a,b,c)组合索引，查询条件where中必须包括a时才会用到索引，且如果字段包括范围查找，则范围查找的字段及其后面的字段的索引均不会使用。如where a>10 and b=3。

索引的数据结构

Hash表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

二叉树

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。这个特点就是为了保证每次查找都可以折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

平衡二叉树

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次IO。

虽然平衡二叉树相对二叉树有很多优点，但是仍然会存在很多问题：

时间复杂度树的高度有关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。在表数据量大时，查询性能就会很差。

平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树

MySQL的数据存储在磁盘文件中，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，要想提高查询效率就必须尽量减少磁盘IO操作。访问二叉树的每个节点就会发生一次IO操作，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节(8+4*2=16)。

因为在MySQL的InnoDB存储引擎一次IO会读取的一页(默认一页16K)的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引(16k/16=1000)，这样就将二叉树改造成了多叉树，通过增加树的叉树，降低树的高度。构建1百万条数据，树的高度只需要2层就可以(1000*1000=1百万)，也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图所示：

B树特点：

一个节点可以存储更多数据。

每个节点中包含键值和数据，节点中的键值从大到小排列。

父节点当中的元素不会出现在子节点中。

所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

举个例子，在b树中查询数据的情况：

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

第一次IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。

第二次IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。

第三次IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

缺点：

不支持范围查找

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树

B+树和B树的最大区别在于非叶子节点是否存储数据。B树叶子节点和非叶子节点都存储数据，B+树只有叶子节点才存储数据，非叶子节点只存储键值。另外B+树的叶子节点之间使用双向指针连接，形成一个双向有序链表。能够保证等值查询和范围查询的效率。

B+树的最底层叶子节点包含了所有的键值。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据只存放在叶子节点，因此每个非叶子节点能够存放更多的键值，相对于B树来说B+树的树高理论上要比B树矮。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

等值查询

假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

第一次IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。

第二次IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。

第三次IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。(这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。)

范围查询

假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。主键具备唯一性(后面不会有<=26的数据)，不需再向后查找，查询终止。将结果集返回给用户。