MySQL索引学习

最新推荐文章于 2023-07-21 15:12:50 发布

1162210866

最新推荐文章于 2023-07-21 15:12:50 发布

阅读量390

点赞数

分类专栏： MySQL 文章标签： mysql b树

本文链接：https://blog.csdn.net/qq_38533859/article/details/125951150

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL索引学习

终于又来编写博客了，拖了好久。学习虽然没有断，但是总结和博客还是断了。按照我下半年的计划，博客估计下半年也没有什么更新。之前还大言不惭，现在终于知道了人的精力始终是有限的，如果一个人要是每天48小时就好了。哈哈哈。

今天就来总结一下MySQL中的索引，讲一下我学习到的知识点，后续如果学习到了新的，也会慢慢补充。

数据库中的索引是比较重要的一个概念，如同一本书的目录一般重要。数据库中存储的大量的数据，就是通过索引才能快速找到用户需要的数据。我们本次先讲一下索引的种类，再详细讲一下我们常使用到的B+索引。最后说一下索引使用过程中的一些问题。

MySQL索引种类

MySQL中的索引有很多种，不仅仅是InnoDB引擎中才有，其他数据库引擎也有一些自己单独的索引。其实每一种索引都对应着一种数据结构，学习索引不仅仅是学习一个单独的数据库知识，也要了解这种数据结构的特点，才能在自己的开发中选择合适的索引。

B-Tree索引

我们平时说的MySQL的索引一般没有特别说明的话，指的都是B-Tree索引。说B树索引，需要先介绍一下B树这个数据结构。

在计算机科学中，B树（英语：B-tree）是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。B树，概括来说是一个一般化的二叉查找树（binary search tree）一个节点可以拥有2个以上的子节点。与自平衡二叉查找树不同，B树适用于读写相对大的数据块的存储系统，例如磁盘。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。这种数据结构常被应用在数据库和文件系统的实现上。具体的介绍不属于本次博客叙述的范围，不再详细介绍。网上有很多博客，这里只是列举维基百科上面的链接：B树wiki。大家如果感兴趣，也可以自己私下搜索一下这个数据结构。个人认为就是这个数据结构比较适合硬盘读取场景。

但是MySQL中并没有直接使用该数据结构，而是使用了其变种，B+树，这个数据结构后续会详细说一下，暂时不在这里讨论。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。在MysQL 中，只有Memory 引擎显式支持哈希索引。这也是Memory 引擎表的默认索引类型（历史原因），Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。（有点类似于Java中HashMap的实现）

小Tip：MySQL 中也有这种索引类型的一个变体，自适应哈希索引（InnoDB中使用），如果需要，它会根据运行时条件为表自动构建。（该选项不可控）

其他冷门索引

R-Tree索引

空间数据索引 (R-Tree)MyISAM 表支持空间索引，可以用作地理数据存储。和B-Tree 索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTANS（）等来维护数据。MySQL的 GIS 支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS 的解决方案做得比较好的是 PostgreSQL 的 PostGIS。

全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情，而不是简单的WHERE 条件匹配。在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突，全文素引适用于MATCH AGAINST 操作，而不是普通的WHERE 条件操作。这个让我联想到ElasticSearch。

MySQL中的B+索引和B树索引的区别

上面也说到了，MySQL中并没有直接使用B树这个数据结构来当索引，而是采用了B树的一个变种，B+树。我们先说一下这个数据结构和B树的不同点。

B+树是B树的变体，主要有两个变动点。

非叶子结点不再存储数据。原因也很简单，主要是数据读盘都是一块区域一起读，如果非叶子结点存储数据，肯定会导致读盘次数太多，会影响查询性能。读取数据的时间其实占了查询的很大比例。
每个叶子结点之间增加了访问上一页和下一页的偏移量。就是可以访问下一页的指针。这个其实也很好理解，增加下一页的指针，这样在范围查询时可以不必再折回查询非叶子结点。

B+树的具体数据结构可以参考官方文档，详细介绍了B+树的结构和具体字节存储的数据。MySQLB+树结构。

应用层中索引的种类

介绍完了索引的种类，也详细介绍了一些索引。但是没有说到我们平时常说的唯一索引、聚集索引这些概念，这些名称我认为是从应用层划分的，其实都是属于大的B+索引这个种类，但是又分别从应用层进行细分。下面就来介绍一下。

联合索引

包含多个列的索引。但是因为底层索引的实现是B+树，所以导致有最左匹配原则。这个原则其实就是：创建联合索引时，会按照联合索引的顺序来对数据进行排列，即先按照索引第一个字段排列，如果相同，再按照索引第二个字段排列。这样就导致如果查询条件中，没有第一列的值做限制，无法使用该索引进行查询优化。（并没有对查询条件中第一列出现的位置做限制，就算该字段在最后，MySQL优化器也会优化，使用对应的索引）

聚集索引

聚集索引一个表只有一个，索引中叶子结点存储数据。这个索引也叫聚簇索引，其实都是一个概念。相当于该表的主要索引，叶子节点存储数据，如果使用聚集索引查询，就可以查询一次就直接返回结果。和后面的二级索引相反。

唯一索引

非聚集索引的一种。只是索引中不能存在重复值。一般主键都会设置为唯一索引。需要注意，唯一索引中可以存储null值，而且null值也是唯一的，但是主键可以设置为非null。

二级索引

一种表示表列子集的InnoDB 索引。一个InnoDB表可以有零个、一个或多个二级索引。二级索引可用于满足只需要索引列中的值的查询。对于更复杂的查询，它可用于识别表中的相关行，然后使用聚集索引通过查找来检索这些行。就是说，使用二级索引查询到对应的数据在聚集索引中的位置，再次查询聚集索引，最后得到需要的数据。这个过程也叫回表。回表的次数减少或者没有回表能大幅减少查询的时间。

需要注意的点

主键只有一个，聚集索引也只有一个。但是主键列并不一定是聚集索引列。因为创建时没有指定，MySQL默认主键列为聚集索引列。
一个表也可以有多个唯一索引，但是主键不能为null，唯一索引可以为null。
聚集索引可以是唯一索引，也可以是非唯一索引。
当表格中没有主键时，MySQL会找一列为聚集索引。具体规则如下：
- 如果一个主键被定义了，那么这个主键就是作为聚集索引。
- 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。
- 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

索引的一些使用场景

下面就说一下一些具体的索引使用细节和场景，这里有一个SQL，创建了一个表，方便演示使用。

create table blog_test
(
    id int primary key,
    a  int,
    b  int
);

delimiter ;;
create procedure insert_blog_test()
begin
    declare i int;
    start transaction;
    set i = 1;
    while(i <= 1000000)
        do
            insert into blog_test values (i, i, i);
            set i = i + 1;
        end while;
    commit;
end;;
delimiter ;

call insert_blog_test();

select count(1)
from blog_test;

这个SQL创建一个测试表，有三个字段，id、a、b。里面插入了100万的数据。

独立的列

这个其实算是一个注意点，就是不要对列进行运算。例如不能写这样的SQLselect * from blog_test where id + 1 < 6;需要改写为select * from blog_test where id < 5。这是因为MySQL没有这么智能，第一个SQL只能读全表，然后一行一行计算，找到符合条件的。两个SQL的explain如图所示：效果还是很明显的。

所以就告诉我们，查询时where后面列要单独放出来。还有就是不要在where条件中对列做函数计算。

覆盖索引

因为二级索引中叶子结点没有存储数据，而是存储对应的主键值。所以查询二级索引时，找到对应的主键值，再去聚集索引中查询对应的数据。这个过程就叫回表。这个概念上面也说了一遍，这里再说一下。本次的使用场景就利用到了这个机制。下面我们先创建一个索引。

create index blog_test_a_b_index
	on blog_test (a, b);
# 因为该表只有三个字段，创建a、b索引后，该索引包含id值，无法形成对照，所以添加了一个字段。
alter table blog_test
	add c int default 1 null;

我们现在有一个需求，需要查询一定范围内a的值对应的b的值。很多人的查询SQL如下：select * from blog_test where a<1000;这样看好像没有什么问题，但是其实这个查询有一个回表的操作，因为该SQL查询的结果需要所有列，所以最后会再次查询聚集索引，拿到所有的列的值，但是其实我们只需要b字段，所以可以修改为:select b from blog_test where a<1000;这样查询的时候不会回表。减少读取的次数。

这个是两次查询的explain结果。

最后有一个Using index就代表使用到了覆盖索引。大家开发过程中应该知道一个SQL的建议：查询不要直接返回*，使用哪些字段就返回哪些字段。现在是不是对这个建议有了更深的理解。

最左匹配原则

最左匹配原则上面也介绍了一下，其实就是因为多列索引在B+树中的存储形式导致了非第一列索引在查询中无法使用索引进行查询优化。

我们现在有一个需求，查询b为1000的记录。SQL很好写，select * from blog_test where b=1000;，虽然我们创建了一个a，b联合索引，但是我们执行explain可以发现并没有使用索引，而是走了全表查询。这个就是因为最左匹配原则，b不是a,b索引的最左列，导致查询的时候无法利用a,b索引。我们可以结合表的特点，因为a的值和b相同（具体表要结合具体业务分析）。可以改写为select * from blog_test where a=1000 and b=1000;,这样就可以走a,b索引了，同时也达到我们的需要。

这个是两个查询的explain结果。