分享每一天-MySQL 使用索引提高查询性能

MySQL 使用索引提高查询性能

Mysql 2138 0 创建时间：2020-09-03 22:51:39

mysql

#### 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 可能会认为全表扫描的效率更高，从而放弃使用索引。此时合理的使用覆盖索引，避免回表，将会使索引重新命中。当然还得特别注意，需要根据实际业务来建立合适且不冗余的索引。

MySQL 使用索引提高查询性能

商品推荐

广告推荐

推荐文章