阅读 1130

MySQL 使用索引提高查询性能

下面所有内容都是以 InnoDB 为例,要说为什么的话,因为咱只用过这个存储引擎。。。

什么是索引

MySQL 中索引是一种数据结构(InnoDB 是 B+ 树),为了能够更高效的找到想要的数据,从而提高查询的性能而存在。就像字典的目录一样,想要从字典中查找某个 字/词 都可以在目录中根据拼音或部首找到指定的位置。

既然索引是以数据结构的形式存在,那么它也必然会占用空间。索引是有序存储在磁盘中的,在添加、更新数据时,都可能会涉及到索引的排序以及树的分裂、合并等维护操作。所以索引虽然查询效率高,但是也不能滥用哦~

索引的类型

为了应对各种使用场景,MySQL 提供很多种类型的索引,常用的有如下几种:

  • 主键索引:如果指定了主键字段,MySQL 会自动创建该类型索引,一张表中只能有一个主键索引,且主键索引字段值必须唯一
  • 唯一索引:使用该索引的字段必须具备唯一性,它会强制要求该字段的值必须是唯一的
  • 普通索引:为单个字段创建的普通索引,其值可以重复,没有什么特别的限制
  • 联合索引:多个字段绑定在一起创建的普通索引

这里需要明白,上面的几种特性是可能存在交叉的,比如唯一索引也可以指定多个字段来做索引(联合索引)

如何使用索引

要想通过索引来提高查询的效率,首先需要了解索引的工作方式,大概了解 MySQL 是如何通过索引来提高查询性能的,而不管是什么类型的索引,最后都需要跟 聚簇索引 打交道。

聚簇索引

大家也许听过 索引即数据 这个说法,InnoDB 中就是通过聚簇索引来实现的,聚簇索引实际上并不是一种索引类型,而是一种组织存储数据的方式,它将索引和数据存储在一起。InnoDB中一张表有且仅有一个聚簇索引,它默认使用主键来创建 主键 -> 数据 的索引关系,如果没有定义主键的话,将会使用一个唯一非空的字段或是一个隐式的主键来创建聚簇索引。之所以这么做是因为数据需要有序的存储在磁盘中,这也是为什么 MySQL 会推荐使用 自增int/bigint 类型字段来做主键,自增数值类型天生就已经排序,有利于聚簇索引的维护。

之前接手过一个老项目,其中所有表都是使用 UUID 随机字符串来作为主键,说实话不是很能懂这样做的意义-_-

由于一张表中只有一个聚簇索引,理所当然的,其他类型索引都可以称之为非聚簇索引。他们和聚簇索引的区别就是聚簇索引是索引即数据,而非聚簇索引中存储的则是 索引值 -> 主键 格式。查询时通过索引值获取到主键值,最后通过主键从聚簇索引中拿到真正的数据本身。

普通索引

普通索引是理解起来最简单的一种索引,通常用于简单的查询条件和排序字段,可以通过如下语句创建普通索引:

t_user

id name age
1 张三 18
2 李四 20
3 王二麻子 22
ALTER TABLE `t_user` ADD INDEX `age_idx` (`age`);
复制代码

创建 age 字段索引后,所用此字段作为 WHERE 条件的查询语句将会通过该索引提高查询效率:

SELECT * FROM `t_user` WHERE `age` = 18;
复制代码

如果使用 age 字段来做排序的话,也会通过索引来省去排序操作(索引本身是有序的)

SELECT * FROM `t_user` WHERE `age` > 18 ORDER BY `age` DESC;
复制代码

联合索引

联合索引和普通索引差不多,区别在于联合索引主要用于多条件检索,如下表:

t_user

id name age type
1 张三 18 1
2 李四 20 2
3 王二麻子 22 3

当经常使用如下查询来获取数据时,可以使用联合索引来提高查询效率:

SELECT * FROM `t_user` WHERE `age` > 18 AND `type` = 1;
复制代码

为查询条件字段添加索引:

ALTER TABLE `t_user` ADD INDEX `type_age_idx` (`type``age`);
复制代码

该语句会为 t_user 表创建两个索引:type(type, age)

覆盖索引

覆盖索引并不是一种索引类型,倒不如说是一种为了提高查询效率的索引使用技巧。前面说过,使用非聚簇索引的查询首先会通过索引找到主键,然后通过主键从聚簇索引中拿到真正的数据本身,这个过程一般称之为 回表

回表是为了通过主键拿到完整的数据,而如果我们使用的索引已经包含了查询需要的所有字段,则第一次索引查询就能拿到查询结果,可以避免回表查询,这种情况就是 覆盖索引 指索引内容覆盖了查询需要的所有字段。当数据量较大时,这种优化方案尤其重要,一般来说索引数据的量级会比真实数据的量级小很多,覆盖索引能够大大的减少从磁盘加载的数据量。

如下索引和查询将不会回表:

ALTER TABLE `t_user` ADD INDEX `type_age_name_idx` (`type``age``name`);
SELECT `name``type``age` FROM `t_user` WHERE `type` = 1;
复制代码

为什么索引失效了?

并不是说创建了索引,查询就一定会使用索引,MySQL 会自行判断当前查询是否适合使用某个索引,某些情况下,MySQL 会认为使用索引查询的效率不如全表扫描,从而放弃使用索引。

索引字段通用规则

在未满足以下情况时,MySQL 将无法命中索引:

  • 不能在索引字段上使用计算函数、类型转换等操作
  • 索引字段需要尽量保证 NOT NULL
  • LIKE 不能以通配符(%)开头,如:%XXX
  • 尽量使用粒度较细、重复度较低的字段作为索引

多表关联字段索引

多表关联字段索引需要注意以下几点:

  • 字段类型必须一致
  • 字符集必须一致

最左原则

联合索引查询时将使用 最左原则 匹配索引,即从最左边的索引开始匹配,如上面的例子:

ALTER TABLE `t_user` ADD INDEX `type_age_idx` (`type``age`);
复制代码

该语句会为 t_user 表创建两个索引:type(type, age)

如下查询将会命中索引:

SELECT * FROM `t_user` WHERE `type` = 1;
SELECT * FROM `t_user` WHERE `age` > 18 AND `type` = 1;
SELECT * FROM `t_user` WHERE `type` = 1 AND `age` > 18;
复制代码

合理使用覆盖索引

当查询索引数据过多时,由于需要回表,MySQL 可能会认为全表扫描的效率更高,从而放弃使用索引。此时合理的使用覆盖索引,避免回表,将会使索引重新命中。当然还得特别注意,需要根据实际业务来建立合适且不冗余的索引。

本文首发于我的博客,欢迎来我的博客玩耍,也欢迎友链~

渣渣的粗浅理解,如有错误,还望指正