当前位置：首页 > news >正文

Mysql的索引详解

news 文章来源：https://blog.csdn.net/m0_69057918/article/details/132390680 2024/11/16 2:27:09

零. 索引类型概述

1. 实际开发中使用的索引种类

主键索引
唯一索引
普通索引
联合索引
全文索引
空间索引

2. 索引的格式类型

BTree类型
Hash类型
FullText类型（全文索引)
RTree类型（空间索引)

MySQL 的索引方法，主要包括 BTREE 和 HASH。
顾名思义，BTREE 方法，就是通过构建 B+ 树的方法来组织索引结构；而 HASH 方法，就是通过构建哈希表的方法来组织索引结构。

3. 以索引数据存储方式划分

聚集索引
非聚集索引

4. 以技巧划分

覆盖索引
前缀索引

也有人把聚集索引叫做聚簇索引

一、索引介绍

索引是一种数据结构，索引的作用主要是为了提高检索效率，添加一个索引需要选择一个字段作为索引依据

索引数据格式主要有两种

Hash
B+Tree

在实际开发中，其实用的最多的是B+Tree数据类型，因为hash数据格式是不支持范围查询的，而基于二叉树数据结构的B+Tree可以支持范围查询（注：B+Tree是B树的进化版）

拓展

（1）Mysql索引为什么使用B+Tree数据结构？

从计算机原理出发，是因为索引本身消耗就很大，不可能全部储存到内存中，因此索引往往索引文件的形式存储在磁盘上，那这样话，磁盘IO效率就会起决定性的作用。

（2）全文索引和空间索引是什么？

二、索引类型

1、普通索引

普通索引是最基本的索引，也是最常用的索引，创建只是为了提高查询效率，值可以为空，没有任何限制

alter table 表名 add index 索引名（添加索引的字段）

2、唯一索引

唯一索引和普通索引类似，值也可以为空，唯一区别是添加的字段是唯一性，列值不允许重复

alter table 表名 add unique 索引名（添加索引的字段）

3、主键索引

主键索引是一种特殊的唯一索引， 值不允许为空 和 列值不允许重复，且一张表只允许有一个主键索引

alter table 表名 add primary key (添加索引的字段)

4、联合索引

联合索引，顾名思义，就是多个索引联合在一起组成的一种索引类型。

//索引名可以是index_xx_xx
alter table 表名 add index 索引名(索引1,索引2,索引3)

5. 关于联合索引的拓展

（1）最左前缀匹配原则

建立联合索引时会遵循最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

例如🌰

在user表中为 name、address、phone 三个字段添加联合索引

ALTER TABLE user ADD INDEX index_three(name,address,phoen)

下面三条sql语句都能命中索引

SELCET * FROM user WHERE address =’ 北京’ AND phone = ‘12345’ AND name = ‘张三’;SELCET * FROM user WHERE name = ‘张三’ AND address = ‘北京’;SELCET * FROM user WHERE name = ‘张三’;

这三条sql语句都会匹配联合索引，按顺序是 (name,address,phone) 、(name,address) 、（name）,其实使用联合索引时可以不用按照建索引时候的顺序，如使用and条件，因为执行sql语句时，MYSQL的优化器会自动帮我们调整where条件中的顺序，但是，使用联合索引必须使用建索引的第一个字段name，即最左边的字段，否则索引无效，如下：

SELCET * FROM user WHERE address = ‘北京’;SELCET * FROM user WHERE address = ‘北京’’ AND phone = ‘12345’ ;

以上联合索引都会失效，可以总结一句话：用到最左边的字段，就算成功

（2）联合主键索引

上面说到了主键索引，其实主键也可以由多个字段组成

🌰借丁奇大佬的例子

DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：


CREATE TABLE `user` ('a' int(11) NOT NULL,'b' int(11) NOT NULL,'c' int(11) NOT NULL,'d' int(11) NOT NULL,PRIMARY KEY ('a','b'),KEY 'c' ('c'),KEY 'ca' ('c','a'),KEY 'cb' ('c','b')
) ENGINE=InnoDB;

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”、“cb”这两个索引？同事告诉他，是因为他们的业务里面有这样的两种语句：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

对于一般不太了解索引数据结构的人来说，会觉得上面的建的索引是没有问题的，但实际上面有一个错误，索引 “ca” 是多余的。

这是为什么？查询 where c = N order by a 难道不需要 “ca” 索引吗？

答案是需要的，不过需要明白的是，索引C是普通索引，C索引树上存放的是联合主键（ab）的值，所以C索引树叶节点存放的值是（cab），根据最左前缀原则，就没必要再建（ca）索引了。但是（cb）索引还是需要的，在cb索引中，实际上是(cba)。

使用到了最左前缀原则和聚集索引相关的知识点

三、聚集索引和非聚集索引

1、非聚集索引

索引和表数据分开存储，就是非聚集索引（也有叫二级索引或辅助索引）。

非聚集索引一般就是指MyIsam引擎的索引，因为MyIsam引擎的索引和数据是分开储存的，如下图显示，MyIsam的索引B+Tree下data域储存的是数据地址，每次查询索引时，都是先从索引树上获取数据地址，在通过地址获取对应的数据。所以，MyIsam表会有三个文件，分别是索引文件、表结构文件、数据文件。

2、聚集索引

索引和表数据存储在一起，就是聚集索引。

以InnoDB引擎举例，InnoDB引擎的主键是和表数据存储在一起，每个树节点储存每行一行的数据。如果有看过InnoDB引擎的表文件，就会发现，其实InnoDB表数据文件就是主索引文件，如下图的Innodb表数据二叉树显示，B+Tree数结构的主键索引，每个树节点主键下的data域，都是存放对应该主键的行数据。所以，InnoBD表数据文件本身就是一个B+树文件。InnoBD表一共有两个文件，分别是索引文件、表结构文件。

需要注意，InnoDB引擎除了主键索引，其他的索引的data域都是存主键索引的值，使用辅助查询时，是先通过辅助索引获取到主键，在通过主键获取主键索引B+树里的data域里的数据。总结：Innodb引擎表主键索引树叶节点存储：每行的数据；辅助索引树叶节点存的是：字段值和主键值。

3、实际开发聚集索引的不同

🌰先建个Innodb引擎的user表，表中有两个索引，一个是主键索引（聚集索引）ID，和另一个普通索引（非聚集索引）index_cardId 身份证ID

CREATE TABLE `user` ( `ID` INT NOT NULL AUTO_INCREMENT,  `cardId` VARCHAR(50) NOT NULL,  `name` VARCHAR(20) NOT NULL,`age` VARCHAR(10) NOT NULL,PRIMARY KEY (`ID`),KEY 'index_cardId' ('cardId') USING BTREE
)engine=InnoDB;

假如有一条语句：

SELECT * FROM user WHERE ID = 100;

即用主键的查询方式，搜索了ID这个索引的B+树，只需直接从这颗B+树中找到对应的ID获取到对应行的数据就可以。

如果用 index_cardId 普通索引查询：

SELECT * FROM user WHERE cardId = ‘43212341234’;

则需要搜索可 index_cardId 这个索引树，从index_cardId 这颗索引树上获取到主键索引的值，在通过值从主键索引的B+树上获取对应的数据，也就是说，非主键索引的查询需要多扫描了一棵索引树，这个过程我们称为回表

所以，在实际开发中，我们应尽量使用主键查询

四、覆盖索引

首先要明白，索引的使用过程，假如查询一条sql语句：

select col1 from test where col1=1;

假如test表有1000W条数据，在col1字段建了一个普通索引，然后sql查询时，会通过col1索引筛选出符合条件的200W条数据，而且能在col1索引上获取col1字段的数据，无需回表查询，直接返回，这就是覆盖索引。

反之，如果在上面的sql语句查询的字段在加一个col2字段

select col1,col2 from test where col1=1;

即使col1索引获取col1字段的值，但是因为没有col2字段的值，所以还需要回表查询，再获取200W条数据字段col2的值，才返回。那这样就不是覆盖索引了。

实际开发中覆盖索引的价值

实际开发中，优化查询的时候是否回表，是优化的重要目标，所以使用索引的时候，应该尽量使sql语句可以从索引中获取到需要的数据，例如上面的例子，可以增加col2字段的索引，这样就可以避免回表查询。

但是，如果查询的字段很多，难道就建很多的索引吗？

要知道，索引也是有开销的，首先，索引是以空间换取时间的，如果索引建的越多，占用的磁盘空间就越多，其次，每次对数据库进行写操作，索引也会进行相应的更新，也会影响数据库写操作的效率，总的来说，浪费了很多服务器的资源，所以索引需要合理的使用，以上的例子为例，我们可以建（co1,col2）的联合索引，这样查询的时候就可以通过（col1,col2）联合直接获取col1和col2字段的数据，直接返回。

覆盖索引是优化索引的重要方式之一

课外拓展-前缀索引

🌰举个例子，先建个表

CREATE TABLE `user` ( `ID` INT NOT NULL AUTO_INCREMENT,  `Name` VARCHAR(50) NOT NULL,  `City` VARCHAR(50) NOT NULL,  `Age` INT NOT NULL,  PRIMARY KEY (`ID`)  
);

为了加快查询速度，我们给这个表加一个联合索引

ALTER TABLE  user ADD INDEX 'name_city_name' (Name(10),City,Age)

仔细看就会发现在Name字段加了10长度限制，这就是前缀索引。Name字段长度是50，为什么用10呢？因为一般情况下名字长度不会超过10，这样不仅加快索引查询速度，还会减少索引文件的大小提高更新数据的效率。

五、索引使用建议

对较小的数据列使用索引
上面有提到过，索引文件会存储数据，所以选择数据量少的字段使用索引可以节省不必要的磁盘空间和内存的消耗
对 where、on、group by、order by 使用索引
对较长的字符串使用前缀索引
不要创建过多索引
多使用联合索引
在阿里云开发手册里，有一条要求是建表索引第一优先是使用联合索引。主要有一下原因：

（1）减少开销
例如，建立一个联合索引（col1,col2,col3），实际相当于建(col1)，(col2)，(col3)三个索引。索引并不建的越多越好，每次对数据库进行写操作的时候，都需要对索引进行更新，所以建一个合理联合索引，可以节省数据库写和磁盘空间的开销。

（2）覆盖索引
建了一个联合索引（col1,col2,col3），假如查询sql：select col1,col2,col3 from test where col1=1 and col2=2 ，那么数据库可以通过索引直接获取到数据，无需回表查询，减少了io操作。所以在实际开中，多使用联合索引以达到覆盖索引的效果，是很有效的优化手段之一。

（3）增加查询效率
索引的利用效率更高，假如数据表有1000W条数据，有一条sql查询语句：select * from test where col1=1 and col2=2 and col3=3 ，假设可以通过每个索引查询条件筛选10%的数据

第一种，如果只有一个普通索引col1，只能通过索引筛选出1000W * 10%=100W 条数据，然后在回表查找符合 col2 = 2 和 col3 =3 的数据，这样结果是回表扫描100W条数据。

第二种，如果是一个联合索引（col1,col2,col3），能通过索引筛选出 1000W*10%*10%*10% = 1W 条数据，然后在回表查找表其他字段数据，效率的差别可想而知。

六、索引使用的要避免的坑

隐式转换问题（索引字段类型使用不准确，mysql会自动转换字段类型，从而导致索引失效）
对索引进行计算（例如 age + 10 = 30 ）
对索引使用函数运算（例如 LEFT(date,4) ）
正则表达式不使用索引（例如模糊查询%xx ，注意， xx% 是走索引的）
or条件必须全部字段建立索引（少使用 or 条件）

七、索引的弊端

不要盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。但是，在互联网应用中，查询的语句远远大于DML的语句，甚至可以占到80%~90%，所以也不要太在意，只是在大数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。