“高效索引技巧：索引的分类，MySQL索引的使用，最左匹配原则，回表查询，如何避免回表，索引下推，创建索引注意事项“

迈不过友情╰ 2024-03-25 23:21 9阅读 0赞

## 索引 ##

### Mysql索引的分类 ###

#### 1、普通索引 和 唯一索引 ####

普通索引：MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值  
唯一索引：要求索引列的值必须唯一，但允许有空值  
如果是组合索引，则列值的组合必须唯一  
主键索引是一种特殊的唯一索引，不允许有空值

#### 2、单列索引 和 组合索引 ####

单列索引：一个索引只包含单个列，一个表可以有多个单列索引  
组合索引：在表的多个字段组合上创建的索引  
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个 B+索引树，比较占用磁盘空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建组合索引！

#### 3、全文索引 ####

全文索引的类型为fulltext  
在定义索引的 列上支持值的全文查找，允许在这些索引列中插入 重复值和空值  
全文索引可以在char、varchar 和 text 类型的列上创建

#### 4、空间索引 ####

空间索引 是对 空间数据类型 的字段 建立的索引  
MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon  
MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引  
创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

#### 5、前缀索引 ####

在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度

### MySQL索引的使用 ###

#### 1. 普通索引 添加INDEX ####

`ALTER TABLE table_name ADD INDEX index_name ( column )`

#### 2. 主键索引 添加PRIMARY KEY ####

`ALTER TABLE table_name ADD PRIMARY KEY ( column )`

#### 3. 唯一索引 添加UNIQUE ####

`ALTER TABLE table_name ADD UNIQUE ( column )`

#### 4. 全文索引 添加FULLTEXT ####

`ALTER TABLE table_name ADD FULLTEXT ( column )`

#### 5. 添加多列索引 ####

`ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )`

#### 6. 删除索引 ####

DROP index index\_name on table\_name;\`\`

#### 7. 查看索引 ####

SHOW INDEX FROM table\_name;

### 最左匹配原则 ###

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。  
**全列匹配时：**  
student表：

CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `gid` int(11) NOT NULL,
      `cid` int(11) DEFAULT NULL,
      `uid` int(11) DEFAULT NULL,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uni_Gid_Cid_SId` (`gid`,`cid`,`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

![d133e5011d6f44219a827ff6472ba36f.png][]

结论：修改查询列顺序，发现结果一样。是因为MySQL会通过优化器，自动优化索引顺序。

#ALTER TABLE index user_index on user(name,sex,age)
    #可以使用复合索引：索引中包含的最左侧字段，只是顺序不正确，在执行的时候可以动态调整为最前左缀，下列执行计划type为ref
    select * from user where sex = ? and age = ? and name = ?
    select * from user where age = ? and name = ?
    
    #不可以使用复合索引：因为缺少左侧字段
    select * from user where sex = ? and age = ? 
    select * from user where age = ?
    select * from user where sex = ? 
    #当缺少左侧字段时,不使用*，使用具体需要的复合索引字段时 依旧会走索引 此时执行计划的type为index
    select name,sex,age from user where sex = ? and age = ?

最左缀原则可以通过跳跃扫描的方式打破简单整理一下这方面的知识  
这个是在 8.0 进行的优化  
MySQL8.0版本开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。 比如我们使用的联合索引是 bcd 但是b中字段比较少 我们在使用联合索引的时候没有 使用 b 但是依然可以**使用联合索引 MySQL联合索引有时候遵循最左前缀匹配原则，有时候不遵循。**

### 回表查询 ###

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

*  聚集索引(clustered index)
 *  普通索引(secondary index)

InnoDB聚集索引和普通索引的差异：

**InnoDB聚集索引**的叶子节点存储了**行记录**，因此， InnoDB必须要有，且只有一个聚集索引

1.  如果表定义了PK，则**PK就是聚集索引**；
2.  如果表没有定义PK，则**第一个not NULL unique**列是聚集索引；
3.  否则，InnoDB会创建一个**隐藏的row-id**作为聚集索引；  
    **InnoDB普通索引**的叶子节点存储主键值。

聚集索引和普通索引数据结构都是一颗 b+树，其特点是所有数据都存放在叶子节点中，并且叶子节点之间有指针指向。

举个栗子，不妨设有表：  
t(id PK, name KEY, sex, flag);  
id是聚集索引，name是普通索引。  
表中有四条记录：

1, shenjian, m, A
    3, zhangsan, m, A
    5, lisi, m, A
    9, wangwu, f, B

该聚集索引和普通索引如图：  
![\[图片\]][53e5edc0514047f595bda593a45cc74f.png]

当通过普通索引去查找数据时，如果有除该索引外的其他列字段，则会去聚集索引处查找  
`select * from t where name='lisi'; # 该语句执行流程如下图`  
![在这里插入图片描述][58ff7199cdb14b56ba2dcb3d559f131d.png]

这就是所谓的**回表查询**，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

### 如何避免回表 ###

可以使用覆盖索引，举个例子：现有User表

CREATE TABLE `user`
    (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name`  int(11)     DEFAULT NULL,
      `sex`  char(3)     DEFAULT NULL,
      `address`  varchar(10) DEFAULT NULL,
      `hobby`  varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `i_name` (`name`)
    ) ENGINE = InnoDB;

有一个场景，需要经常查询name和sex  
**select id,name,sex from user where name = ‘zhangsan’;**  
这个语句在业务上经常需要用到，而user表中的其他字段使用率远远小于这几个字段。在这个情况下，如果我们在建立name字段的索引时，不是使用单一索引，而是使用 联合索引(name,sex)。这样的话再执行这个查询语句，根据这个辅助索引（name,sex）查询到的结果就包括了我们所需要的查询结果的所有字段的完整数据。

### 索引下推 ###

这个是在MySQL 5.6 之后提供的特性  
假设 我我们需要查询 select \* from table1 where b like ‘3%’ and c = 3.  
索引字段为index(b,c)  
**5.6 之前**

*  先通过 联合索引 查询到 开头为 3 的数据 然后拿到主键（上图中青色块为主键）
 *  然后通过主键去主键索引里面去回表查询 二级索引里面查询出来几个 3 开头的就回表几次

**5.6 之后**

*  先通过 二级索引 查询到开头为 3 的数据 然后 再找到 c = 3 的数据进行过滤 之后拿到主键
 *  通过主键进行回表查询  
    上面都会进行回表查询但是 5.6 之前没有完全去利用 二级缓存进行数据过滤 如果 3 开头的数据非常多 那就要一直回表 但是 5.6 之后去利用后续索引字段进行查询  
    怎么说呢 就是为什么索引下推要和联合索引进行使用 普通所以没有 索引下推就是充分利用 联合索引的字段进过滤 尽量减少需要回表的数据 来增加查询效率 感觉思路是很简单的

如何避免索引失效

*  使用组合索引时，遵循 最左匹配 原则
 *  不在索引列上进行任何操作，如：计算、函数、类型转换
 *  尽量使用覆盖索引
 *  索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
 *  字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）

### explain各列作用 ###

![在这里插入图片描述][2aaa68f2d79b4889ad56cd9f2600bbe5.png]  
![在这里插入图片描述][d8b31d37ea5d49e5b1e691e78c81a9ae.png]

（1）**id**：反映的是表的读取顺序或查询中执行select子句的顺序。  
① id相同，执行顺序是由上至下的。  
② id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。  
③ id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

（2）**select\_type**：表示select的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。  
① simple：简单的select查询，查询中不包含子查询或union。  
② primary：查询中若包含任何复杂的子部分，最外层查询标记为primary。  
③ subquery：select或where列表中的子查询。  
④ derived（衍生）：在from列表中包含的子查询，MySQL会递归执行这些子查询，把结果放在临时表里。  
⑤ union：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。  
⑥ union result：union后的结果集。

（3）**table**：显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是第几步执行的结果的简称。

（4）**type**：对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常见的访问类型有ALL、index、range、 ref、eq\_ref、const、system、NULL（从左到右，性能从差到好）。  
① ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行。  
② index:：Full Index Scan，index与ALL的区别为index类型只遍历索引树。  
③ range：索引范围扫描，返回一批只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现between、< 、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。  
④ ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体。  
⑤ eq\_ref：类似ref，区别在于使用的索引是唯一索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者unique key作为关联条件。  
⑥ const、system：当MySQL对查询某部分进行优化并转换为一个常量时，使用这些类型访问。如果查询条件用到常量，那么通过索引一次就能找到，常在使用primary key或unique的索引中出现。system是const类型的特例，当查询的表只有一行的情况下使用。  
⑦ NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

（5）**possible\_keys**：指出MySQL能使用哪个索引在该表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。

（6）**key**：显示MySQL实际决定使用的索引，如果没有选择索引，则显示是NULL。要想强制MySQL使用或忽视possible\_keys列中的索引，在查询中使用FORCE INDEX或者IGNORE INDEX。查询中若使用了覆盖索引（select后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中。

（7）**key\_len**：显示索引中使用的字节数。

（8）**ref**：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

（9）**rows**：显示MySQL根据表统计信息以及索引选用的情况，估算找到所需的记录要读取的行数。

（10）**Extra**：该列包含MySQL解决查询的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。  
① Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，发生在对表的全部请求列都是同一个索引部分的时候，表示MySQL服务器将在存储引擎检索行后再进行过滤。  
② Using temporary：表示MySQL需要使用临时表来存储结果集，MySQL在对查询结果排序时使用临时表，常见于排序（order by）和分组查询（group by）。  
③ Using filesort：当Query中包含order by操作而且无法利用索引完成的排序操作称为“文件排序”，创建索引时会对数据先进行排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化。  
④ Using join buffer：表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值，应该注意，根据查询的具体情况可能需要添加索引来改进。  
⑤ Using index：只使用索引树中的信息，而不需要进一步搜索读取实际的行来检索表中的列信息。相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好。覆盖索引：select后的数据列只从索引就能取得，不必读取数据行，且与所建索引的个数（查询列小于等于索引个数）、顺序一致。如果要用覆盖索引，就要注意select的列只取需要用到的列，不用select \*，同时如果将所有字段一起做索引会导致索引文件过大，性能会下降。  
⑥ Using Index Condition：表示进行了ICP优化。

**首先关注查询类型type列，如果出现all关键字，代表全表扫描，没有用到任何index；再看key列，如果key列是NULL，代表没有使用索引；然后看rows列，该列数值越大意味着需要扫描的行数越多，相应耗时越长；最后看Extra列，要避免出现Using filesort或Using temporary这样的字眼，这是很影响性能的。**

### 创建索引注意事项 ###

1、**限制表上的索引数目**。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。  
2、**避免在取值朝一个方向增长的字段**（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。  
3、对复合索引，**按照字段在查询条件中出现的频度建立索引**。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。  
4、**删除不再使用，或者很少被使用的索引**。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。  
5、对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；  
6、**不要在有大量相同取值的字段上，建立索引**。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度；  
7、对于那些**定义为text, image和bit数据类型的列不应该增加索引**。这是因为，这些列的数据量要么相当大，要么取值很少；  
8、**当修改性能远远大于检索性能时，不应该创建索引**。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

[d133e5011d6f44219a827ff6472ba36f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c2cbcdd74ad6419eb18e8cf8164a62a5.png
[53e5edc0514047f595bda593a45cc74f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/fb107d7d0e4043908ba775eb3b24ddb6.png
[58ff7199cdb14b56ba2dcb3d559f131d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/fe731dcd2dea4ce284d54d855dcf037d.png
[2aaa68f2d79b4889ad56cd9f2600bbe5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/68e4a61fba3541d18beda11e8eece2b7.png
[d8b31d37ea5d49e5b1e691e78c81a9ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/bc6cf67c95ff476295c050a2d09f97d3.png