MYSQL之索引（基本操作及底层原理）_数据库索引有多少个效率反而降低呢-CSDN博客

本文详细介绍了MYSQL中的索引，包括什么是索引、优缺点、分类（如普通索引、唯一性索引、全文索引等）、创建与删除方法，以及B+树和哈希索引的底层原理。通过索引，可以提高数据查询速度，但也可能影响插入速度。了解索引分类和设计原则有助于优化数据库性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MYSQL中很重要的一个知识点，也是面试的常考点：索引。本文将主要从索引的概念，优缺点，分类，基本操作以及底层原理五个方面为大家进行介绍，至于索引的优化问题将在下一篇文章中进行介绍。

什么是索引

索引是创建在表上的，是对数据库表中的一列或多列的值进行排序的一种结构。索引可以提高查询速度，通过索引，查询数据时可以不必读完记录的所有信息，只是查询索引列，从而有效提高数据库系统的性能。

索引的优缺点

优点

索引的优点在于可以提高检索数据的速度：对于有依赖关系的子表和父表之间的联合查询，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

缺点

索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加：索引需要占用物理空间，每一个索引要占用一定的物理空间；增加，删除和修改数据时，要动态维护索引，造成索引的维护速度降低了。

因此，在创建索引时，要综合考虑索引的优缺点。索引可以提高查询效率但是影响插入记录的速度（向有索引的表中插入记录，数据库会按照索引进行排序，从而降低插入记录的速度），这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

索引的分类

普通索引

在创建普通索引时，不需要附加任何限制条件。可以创建在任何数据类型中；

唯一性索引

使用UNIQUE参数可以将索引设置为唯一性索引。创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引，可以更快地确定某条记录。主键就是一种特殊的唯一性索引。

全文索引

使用FULLTEXT参数可以将索引设置为全文索引。全文索引只能创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。全文索引只有MyISAM存储引擎支持。在默认情况下，全文索引的搜索方式不区分大小写，但索引的列使用二进制排序后，可以执行区分大小写的全文索引。

单列索引

在表中的单个字段上创建索引。单列索引可以是普通索引，全文索引，唯一性索引，只要满足该索引对应一个字段。

多列索引

在表中的多个字段创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但，只有查询条件使用了这些字段中的第一个字段是，索引才会被使用。

空间索引

使用SPATIAL参数将索引设置为空间索引，只能建立在空间数据上。只有MyISAM存储引擎支持空间索引且索引的字段不能为空值，较少使用。

索引的创建与删除

1、在创建表是设置索引

create table table_name (

属性名数据类型 [完整性约束条件],

......

属性名数据类型 [完整性约束条件],

[UNIQUE | FULLTEXT | SPATIAL ] index | key [别名] (属性名)

);

2、创建表后设置索引

create [unique | fulltext | spatial] index 索引名(idx_id) on 表名(属性名);

alter table 表名 add [unique | fulltext | spatial] index 索引名(属性名);

3、删除索引

drop index 索引名 on 表名；

4、索引的执行过程

索引的执行过程可以通过explain语句查看，举例进行说明。

创建一个Student表，表里字段分别为：Student(SID,Sname,Sage,Ssex)

--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

插入基础数据后查看表中信息：

mysql>select * from Student;
+-----+----------+------+------+
| SID | Sname    | Ssex | Sage |
+-----+----------+------+------+
|   1 | zhaolei  | nan  |   20 |
|   2 | qiandian | nan  |   20 |
|   3 | sunfen   | nan  |   21 |
|   4 | wulan    | nv   |   18 |
+-----+----------+------+------+

查询姓名为"zhaolei"的数据记录，通过explain分析SQL执行过程：

mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.01 sec)

可以通过possible_keys、key没有用到索引，并且通过rows: 4可以看出直接全表扫描一遍才找到"zhaolei"的信息，假设表中4百万数据，那么查找"zhaolei"同学就需要扫描4百万的数据，效率比较低。

以名字创建索引：

mysql>create index idx_name on Student (Sname);
mysql> explain select * from Student where Sname = 'zhaolei'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Student
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 27
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.01 sec)

注意：这次查询"zhaolei"同学的信息，只在表中查询一行数据就可以得到（rows: 1）

记住：explain命令，可以查看SQL执行计划，分析SQL语句是否正确使用索引

索引的底层原理

MYSQL支持2种索引，B-树索引和哈希索引，这两种索引的查询小比较高，MYSQL INNODB存储引擎基于B-树（MYSQL实际采用是B+树）结构。

B-树

B-树的特性：

1.关键字集合分布在整颗树中；

2.任何一个关键字出现且只出现在一个结点中；

3.搜索有可能在非叶子结点结束；

4.其搜索性能等价于在关键字全集内做一次二分查找；

5.自动层次控制；

由于B-Tree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。B-Tree上查找算法的伪代码如下：

BTree_Search(node, key) {
     if(node == null) return null;
foreach(node.key)
   {
     if(node.key[i] == key) return node.data[i];
     if(node.key[i] > key) return BTree_Search(point[i]->node);
   }
  return BTree_Search(point[i+1]->node);
}
   data = BTree_Search(root, my_key);

可以看出，使用索引是有能提高查询效率，但是给表创建过多的索引，效率反而会降低。

效率分析（索引的效率主要与磁盘IO次数有关）

由于磁盘的读取（磁盘往往不是按需读取，一般是磁盘预读）按照block块操作（内存按照page页来操作的，每次读取一般是页的整数倍，页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。），因此B-树的节点大小一般设置和磁盘大小一致，这样一个B-树的节点，就可以通过一次磁盘IO操作将数据全部全部读取/存储下来，所以使用B-树作为索引的时候，磁盘IO的次数是最少的（MSYQL读写效率，主要集中在磁盘IO上）。

B+树

B+的特性：

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

2.不可能在非叶子结点命中；

3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

4.更适合文件索引系统；

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：每个节点的指针上限为2d而不是2d+1；内节点不存储data，只存储key；叶子节点不存储指针。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

如图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

B*树

是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针；

三者总结：

B（B-）树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

为什么MYSQL最终使用B+树而不是B-树，他们在存储结构上有什么不同？

1、B-树的每一个节点，存储的是关键字和对应的数据地址，而B+树的非叶子节点存储关键字，因此B+树的每一个非叶子节点存储的关键字数量远远多于B-树，B+树叶子节点存储关键字和数据。因此：从树的高度来说，B+树的高度要小于B-树，使用磁盘的次数少；

2、B-树由于每一个节点存储关键字和数据，因此离根节点进的数据查询比较快，离根节点比较远的数据，查询就比较慢，B+树上所有的数据都存储在叶子节点上，因此B+树查询关键字，找到对应数据的时间上是比较均匀的，没有快慢之分；

3、在B-树如果做区间查找，遍历的节点比较多的，B+树所有的叶子节点被连接成有序链表结构，因此做整表遍历和区间查找是非常容易的。

哈希索引是由哈希表实现，哈希表无法对数据做到排序，因此无法做区间查找，效率非常低，需要查询整个哈希结构

主键索引、辅助索引、聚集索引、非聚集索引

1、MyISAM存储引擎-主键索引

MyISAM引擎使用B+树作为索引结构、叶节点的data域存放的是数据记录地址

下图是MyISAM主键索引的原理图

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

根据上面两张图，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。可以看出：MYISAM存储引擎，索引结构叶子节点存储关键字和数据地址，也就是说索引关键字和数据没有在一起存放，体现在磁盘上，就是索引在一个文件存储，数据在另一个文件存储。例如一个user表，会在磁盘上存储三个文件：user.frm(表结构文件) user.MYD(表的数据文件) user.MYI(表的索引文件)

MYISAM的索引方式也叫做非聚集索引，之所以这么称呼为了与InNoDB的聚集索引区分。

2、INNODB存储引擎-主键索引

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。INNODB存储引擎的主键索引，叶子节点中，索引关键字和数据是在一起存放的，如图：

InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

3、INNODB存储引擎-辅助索引

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。INNODB的辅助索引，叶子节点上存放的是索引关键字和对应的主键，如图：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：先根据关键字找到对应的主键，再去主键索引树上找到对应的行记录数据。

从索引树上可以看到，INNODB的索引关键字和数据都是在一起存放的，体现在磁盘存储上。例如创建一个user表，在磁盘上只存储两种结构，user.frm(存储表的结构),user.idb（存储索引和数据）

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。