MySQL 正确建立索引-KeepStudy

MySQL 正确建立索引

在实际项目场景中，当SQL查询性能较慢时，我们常常会有一个疑惑：表中哪个字段建立一个索引能带来最大的性能收益呢？一般来说，判断字段是否要添加的索引的依据，是看这个字段是否被经常当做查询条件使用。

但也不能光依靠这一个依据来判断，比如用户表中的性别字段，就会经常被用做查询条件，但如果对性别字段建立一个索引，那对查询的性能提升并不大，因为性别就两个值：男/女，那对其建立索引，索引文件中就只会有两个索引节点。这种情况下，为性别建立一个索引，带来的性能收益显然不是太大。

### 索引查询时的回表问题
什么叫做回表呢？意思就是指一条SQL语句在MySQL内部，要经过两次查询过程才能获取到数据。

![](/uploads/20230215/2023021513492860471.png)

在上图用户表中，基于ID字段先建立了一个主键索引，然后又基于name字段建立了一个普通索引，此时MySQL默认会选用主键索引作为聚簇索引，将表数据和主键索引存在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据。而基于name字段建立的索引，其索引节点存放的则是指向聚簇索引的ID值。

在这种情况下，假设有一条下述SQL，其内部查询过程是啥样的呢？
```sql
SELECT * FROM `zz_user` WHERE name = "子子";
```
首先会走name字段的索引，然后找到对应的ID值，然后再基于查询到的ID值，再走ID字段的主键索引，最终得到一整条行数据并返回。

在这个案例中，一条查询SQL经历了两次查询才获取到数据，这个过程则被称之为回表。

回表动作会导致额外的查询开销，因此尽量可以基于主键做查询，如果实在需要使用非主键字段查询，那么尽量要写明查询的结果字段，而并非使用*。

实际情况中建立联合索引，利用索引覆盖特性，从而避免使用辅助索引，这样也能够消除回表动作。

### 建立索引时需要遵守的原则
当建立索引仅考虑一个字段是否被经常用于查询是不够的，往往一个合适的索引需要更为细致与长远的思考，例如使用多个字段建立是否会更好？创建其他类型的索引性能是否会更佳？下面我们就一起来看看建立索引时，需要遵守的一些原则：

- ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
- ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
- ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
- ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
- ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
- ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
- ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构。
- ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：
- ❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。
- ❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。
- ❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
- ❹一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
- ❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。
- ❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
- ❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

### 联合索引的最左前缀原则
>⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

基于X、Y、Z字段建立了一个联合索引，实际上也相当于建立了三个索引：X、X、Y、X、Y、Z，因此只要查询中使用了这三组字段，都可以让联合索引生效。

>但如若查询中这三个字段不以AND形式出现，而是单独作为查询条件出现，那单值索引性能会好一些，但三个不同的索引，维护的代价也会高一些。

其实联合索引的最左前缀原则，道理很简单的，就是组成联合索引的多个列，越靠左边优先级越高，同时也只有SQL查询条件中，包含了最左的字段，才能使用联合索引。

>❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

因为将查询频率越高的字段放首位，就代表着查询时命中索引的几率越大。同时，MySQL的最左前缀原则，才匹配到范围查询时会停止匹配，比如>、<、between、like这类范围条件，并不会继续使用联合索引。举个栗子：
```sql
SELECT * FROM tb WHERE X="..." AND Y > "..." AND Z="...";
```
当执行时，虽然上述SQL使用到X、Y、Z作为查询条件，但由于Y字段是>范围查询，因此这里只能使用X索引，而不能使用X、Y或X、Y、Z索引。

>SQL是否走索引查询跟where后的条件顺序无关，因为MySQL优化器会优化，对SQL查询条件进行重排序。

比如下面这个例子，如果建立了联合索引user(name,age)，都会利用到索引
```sql
select * from user where name = '子子' and age = 18;
select * from user where  age = 18 and name = '子子';
```

顶部

MySQL索引

目录

相关推荐