MySQL索引详细深入解读

mysql

一.什么是mysql索引

MySQL索引是一种数据结构，用于提高对MySQL数据库表中数据的检索速度。它类似于书籍的目录，可以帮助数据库系统更快地定位到存储在表中的特定行。索引是通过在一个或多个列上创建的数据结构，以提高对这些列数据的搜索和排序效率。 索引的工作原理类似于字典的目录。当你想要查找字典中某个单词的定义时，首先会翻开字典的目录，找到对应的页码，然后直接翻到该页码上查找单词的定义。而不是从第一页开始逐页查找，这样可以大大提高查找速度。

二.mysql索引的分类

MySQL支持两种存储引擎分别是innoDB和MyISAM，默认使用innoDB存储引擎，下面我们也是基于innodb来进行索引介绍。

从物理存储形式上可以分为两类

• 聚簇索引（clustered index）也称之为聚集索引，也称之为主键索引；
• 非聚簇索引（non clustered index ）也称为二级索引，辅助索引；

从索引的数据结构上看，可以进行如下分类

• 哈希索引, 适用于等值查询，但不适用于范围查询或排序。它使用哈希函数将索引键值转换为哈希值，然后将这些哈希值存储在哈希表中，通过哈希函数快速定位到对应的数据行。
• 全文索引, 用于全文搜索，可以在文本数据中进行高效的关键词搜索。它适用于包含大量文本数据的列。主要针对文本类型的字段(Mysql允许在char、varchar、text类型上建立全文索引)。它的作用是加速对文本数据的搜索，允许在文本中进行关键词的匹配和搜索，而不仅限于精确匹配。全文索引允许你在文本数据中执行模糊查询、关键词搜索、短语搜索等操作，从而实现更加灵活和高效的文本检索功能。这在需要搜索大量文本数据的应用场景中特别有用，比如文章、博客、论坛、商品描述等。MySQL的全文索引支持分词查询，即将搜索关键词拆分成多个词语，并在文本中匹配这些词语。这使得即使输入的搜索词语与文本中的词语不完全匹配，也能够找到相关的结果。MySQL内置的全文索引功能支持一些基本的分词功能，但相对于专业的全文搜索引擎如Elasticsearch，其功能可能相对简单。总的来说，MySQL的全文索引提供了一种方便快捷的方式来实现基本的全文搜索功能，但如果需要更复杂的分词查询和更高级的全文搜索功能，可能需要借助专业的全文搜索引擎。
• B树索引
• R树索引，也叫做空间索引，用于地理数据类型，如点、线、多边形等。它可以加速对空间数据的空间查询，例如查找某一范围内的所有点。

R树（R-tree）是一种多维数据结构，特别适用于索引空间数据，如地理信息系统（GIS）中的地理数据类型（如点、线、多边形等）。R树是由Antonn Guttman在1984年提出的，用于解决空间数据索引和查询的问题。因为对我来说基本用不到，详细的R树相关的知识点大家可以自行搜索相关文档。

对于一个服务端开发来说，我们更多的需要了解下面几种具体的mysql索引类型

• 主键索引
  • 主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行数据。每个表只能有一个主键索引。
  • 在InnoDB存储引擎中，MySQL中的数据是按照主键的顺序来存放的。因此，主键索引在InnoDB中也被称为聚簇索引。
  • 聚簇索引是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。
  • 由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。
  • 如果表没有设定主键，则InnoDB会按照以下规则来创建聚簇索引：
    1. 如果存在一个唯一且不为空的索引列，将该列作为主键，并成为表的聚簇索引。
    2. 如果不存在这样的索引列，则InnoDB会隐式定义一个主键来作为聚簇索引。

primary key(字段名)

• 普通索引

普通索引与主键索引的区别在于，普通索引的叶子节点存放的不是行数据，而是主键值。

CREATE INDEX 索引名 ON 表名(字段名); 
或
ALTER table 表名 ADD INDEX 索引名(字段名)

• 唯一索引

与普通索引类似，不同点在于唯一索引的索引列的值必须唯一，但允许有空值，这点与主键不同（主键索引列的值唯一，但不能为空）。如果是多个字段组成的联合唯一索引，则列值的组合必须唯一，创建方法与普通索引类似。

CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
或
ALTER table 表名 ADD UNIQUE INDEX 索引名(字段名)

• 组合索引

使用多个列字段建立的索引，称为联合索引，也叫组合索引。

CREATE INDEX 索引名 ON 表名(字段名1,字段名2);

三.索引原理详细分析

3.1 聚集索引(主键索引)结构

从上面的介绍，我们知道，聚簇索引就是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。(在innodb中，聚集索引也就是主键索引)也就是类似下面的结构：

和之前我们介绍B树时一样，典型的B树索引结构，只有叶子节点才存储实际的数据，索引节点只存了主键id，也就是关键字。另外，主键索引特殊的地方在于，叶子节点不单单只是存储了数据，而是存储了当前主键对应的表的整个行数据。 也就是说，当我们查询到指定的叶子节点的数据之后，就等于完成了整个查询操作，因为当前数据包含了所有字段。

3.2 非聚集索引(普通索引)

当一个查询使用普通索引时，MySQL会首先使用非聚集索引（普通索引）找到满足查询条件的行的主键（或者叫做记录指针），然后再使用这些主键值去聚集索引（主键索引）中查找对应的数据行。这个过程被称为“回表”（也叫“二次查找”），因为查询首先使用非聚集索引进行一次查找，然后通过找到的主键值再在聚集索引中进行第二次查找，以获取完整的数据行。 也就是说如果一个查询走的是普通索引，那么需要先通过非聚集索引找到对应数据的主键id，然后再使用主键id值走聚集索引，查到相应的数据。当然，如果我们查询的字段就只是普通索引的字段，那么mysql会避免再次查询聚集索引，因为非聚集索引中已经包含当前需要的数据。

下面我画了一张结构图，表示聚集索引回表查询的过长。

3.3 非聚集索引(组合索引)

组合索引(联合索引)的数据结构也是B+树，不同的是，二叉树的非叶子节点不是单个数值作为关键字，而是键值对。假设我们的组合索引是(a,b,c) 那么每个节点的数据就是（1，2，4）或者（2，3，6）或者（5，3，2）.类似的形式。 下面我画了一张组合索引的大致数据结构图： 可以看到，键值对数据的排序也是有序的，遵循BST的插入规则，对于每个节点，a值是有序插入的，另外，在a值相同的前提下，b值也是有序的。

最左匹配原则

• 为什么一定要最左匹配？

假设表的组合索引是(a,b,c)，下面我列举了相应的查询和索引利用情况：

#利用到索引（a,b,c）,用上a,b,c
select * from t where a=1 and b=1 and c =1;   
#利用到定义的索引（a,b,c）,用上a,b
select * from t where a=1 and b=1;     
#利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器，会调整where条件匹配顺序）
select * from t where b=1 and a=1;     
#利用到定义的索引（a,b,c）,用上a
select * from t where a=1;     
##不可以利用到定义的索引（a,b,c）
select * from t where b=1 and c=1;     
#可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到
select * from t where a=1 and c=1;     

从上面的分析其实不难看出，所谓最左匹配原则，其实就是，当执行查询时，如果查询条件只涉及到组合索引的左前缀列，MySQL会使用这个索引来加速查询。这里的左前缀其实就是查询条件式a,b或者a,b,c或者a。另外如果查询条件是a,c,b mysql优化器会将查询条件顺序调整为a,b,c. 上面这种情况是可以利用(a,b,c)这个组合索引的。 但是如果查询条件是b,c 这样就无法利用索引了，因为不符合左前缀。

其实从上面我们画的索引结构也能看出来，索引的第一个值一定需要作为条件，因为只有第一个索引值才是全局有序的，如果第一个索引值不在查询条件中，那么就无法进行BST查询。

• 哪些情况下也会终止索引匹配？

在使用组合索引进行查询的时候，如果遇到范围查询(>、<、between、like)就会停止后续所有的索引匹配了。

like 后的关键字的前面不能使用%（例如：name like "%三"），只能在关键字的后面加上%，因为索引是从左至右匹配的，如果在前面加上%就无法进行大小匹配了，等于第一个就是范围匹配，后续的数据都是无序的了，下面也会详细解释。

假设已经建立了组合(a,b,c,d)，那么下面的查询就会出现索引匹配终止：

..... where a = 10 and b = 20 and c >100 and d = 5

上面的where查询条件，只有a, b, c能使用到联合索引(a,b,c,d)，d的条件无法使用索引，因为c>100属于范围查询，将后面d的索引匹配给中断了。 从索引结构上来看，如果匹配到b=20，找到了明确的几个节点数据，然后需要判断c>100,这个时候，因为查询条件是一个范围，所以我们无法在b=20的键值数据中，使用二分查找快速匹配定位到数据(如果是c=40,那么就可以基于索引来匹配了)，并且，当匹配完a和b之后，根据前面我们说的在a值相同的前提下，b值也是有序的。那么因为c是范围条件，所以可能是10，20，30等等，所以此时匹配后剩余的数据中，d一定是无序的，所以无法使用二分查找对非叶子节点进行索引数据查询了，只能对剩下的数据进行全表扫描，查出符合剩余条件的数据。

首先一定要搞明白，其实组合索引的索引检索过程和普通索引是类似的，只是对于组合索引，需要先匹配第一个键值，也就是第一个查询条件，根据BST进行查询，然后等于确定了第一次查询后筛选的范围数据了，然后在这个基础上进行第二个键值的数据筛选，以此类推。

关于索引失效的场景，参考下面这篇腾讯云的技术博客，我这里就不一一列举了。

本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名,转载请标明出处
最后编辑时间为: 2024/05/03 23:28