如何为MySQL创建高性能索引

共计 7490 个字符，预计需要花费 19 分钟才能阅读完成。

今天丸趣 TV 小编给大家分享一下如何为 MySQL 创建高性能索引的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

1 索引基础 1.1 索引作用

在 MySQL 中，查找数据时先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行，假如要运行下面查询语句：

SELECT * FROM USER WHERE uid = 5;

如果在 uid 在建有索引，则 MySQL 将使用该索引先找到 uid 为 5 的行，也就是说 MySQL 先在索引上按值进行查找，然后返回所有包含该值的数据行。

1.2 MySQL 索引常用数据结构

MySQL 索引是在存储引擎层面实现的，不是在服务器实现的。所以，没有统一的索引标准：不同存储引擎的索引工作方式不一样。

1.2.1 B-Tree

大多数的 MySQL 引擎都支持这种索引 B -Tree，即时多个存储引擎支持同一种类型的索引，其底层实现也可能不同。比如 InnoDB 使用的是 B +Tree。

存储引擎以不同的方式实现 B -Tree，性能也各有不同，各有优势。如，MyISAM 使用前缀压缩技术是的索引更小，当 InnoDB 则按照原数据格式进行存储，MyISAMy 索引通过数据的物理位置引用被索引的行，而 InnoDB 根据组件应用被索引的行。

B-Tree 所有值都是顺序存储的，并且每一个叶子页到根的距离相同。如下图大致反应了 InnoDB 索引是如何工作的，MyISAM 使用的结构有所不同。但基本实现是类似的。

实例图说明：

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为 16 和 34，P1 指针指向的子树的数据范围为小于 16，P2 指针指向的子树的数据范围为 16~34，P3 指针指向的子树的数据范围为大于 34。查找关键字过程：

根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】

比较关键字 28 在区间（16,34），找到磁盘块 1 的指针 P2。

根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】

比较关键字 28 在区间（25,31），找到磁盘块 3 的指针 P2。

根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】

在磁盘块 8 中的关键字列表中找到关键字 28。

缺点：

每个节点都有 key，同时也包含 data，而每个页存储空间是有限的，如果 data 比较大的话会导致每个节点存储的 key 数量变小；

当存储的数据量很大的时候会导致深度较大，增大查询时磁盘 io 次数，进而影响查询性能。

1.2.2 B+Tree 索引

B+ 树是对 B 树的变种。与 B 树区别：B+ 树只在叶子节点存储数据，非叶子节点只存储 key 值及指针。

在 B + 树上有两个指针，一个指向根叶子节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对 B + 树进行两种查找运算：一种是对于组件的范围查找，另一种是从根节点开始，进行随机查找。

B* 树与 B + 数类似，区别在于 B * 数非叶子节点之间也有链式环结构。

1.2.3 Hash 索引

哈希索引基于哈希表实现，只有精准匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code）, 哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 MySQL 中只有 Memory 默认索引类型就是使用的哈希索引，memory 也支持 B -Tree 索引。同时，Memory 引擎支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放多个指针相同一个哈希条目中。类似 HashMap。

优点：
索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，哈希所以查找的速度非常快。
缺点：

利用 hash 存储的话需要将所有的数据文件添加到内存，比较耗费内存空间；

哈希索引数据并不是按顺序存储的，所以无法用于排序；

如果所有的查询都是等值查询，那么 hash 确实很快，但是在企业或者实际工作环境中范围查找的数据更多，而不是等值查询，因此 hash 就不太适合了；

如果哈希冲突很多的话，索引维护操作的代价也会很高，这也是 HashMap 后期通过增加红黑树解决 Hash 冲突的问题；

2 高性能索引策略 2.1 聚簇索引与非聚簇索引聚簇索引

不是单独的索引类型，而是一种数据存储方式，在 InnoDB 存储引擎中聚簇索引实际在同一个结构中保存了键值和数据行。当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中。因为无法同时把数据行存放在不同的地方，所以一个表中只能有一个聚簇索引（索引覆盖可以模拟出多个聚簇索引的情况）。

聚簇索引优点：

可以把相关数据保存在一起; 数据访问更快，因为索引和数据保存在同一个树中; 使用覆盖索引扫描的查询可以直接使用页节点中的主键值;

缺点：

聚簇数据最大限度地提高了 IO 密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势；插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式；更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置；基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题；聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候；

非聚簇索引

数据文件跟索引文件分开存放

2.2 前缀索引

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指：不重复的索引值（也称为基数 cardinality）和数据表记录总数的比值，范围从 1 /#T 到 1 之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让 mysql 在查找的时候过滤掉更多的行。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应 BLOB,TEXT,VARCHAR 类型的列，必须要使用前缀索引，因为 mysql 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

举例

表结构及数据 MySQL 官网或 GItHub 下载。

city Table Columns

字段名含义 city_id 城市主键 IDcity 城市名 country_id 国家 IDlast_update: 创建或最近更新时间

-- 计算完整列的选择性
select count(distinct left(city,3))/count(*) as sel3,
 count(distinct left(city,4))/count(*) as sel4,
 count(distinct left(city,5))/count(*) as sel5,
 count(distinct left(city,6))/count(*) as sel6,
 count(distinct left(city,7))/count(*) as sel7,
 count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到当前缀长度到达 7 之后，再增加前缀长度，选择性提升的幅度已经很小了。由此最佳创建前缀索引长度为 7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表时设置的主键索引，如果没有设置 MySQL 自动将第一个非空唯一值作为索引，如果还是没有 InnoDB 会创建一个隐藏的 row-id 作为索引（oracle 数据库 row-id 显式展示，可以用于分页）；普通索引就是给普通列创建的索引。普通列索引在叶子节点中存储的并不是整行数据而是主键，当按普通索引查找时会先在 B + 树中查找该列的主键，然后根据主键所在的 B + 树中查找改行数据，这就是回表。

2.4 覆盖索引

覆盖索引在 InnoDB 中特别有用。MySQL 中可以使用索引直接获取列的数据，如果索引的叶子节点中已经包含要查询的数据，那么就没必要再回表查询了，如果一个索引包含（覆盖）所有需要查询的字段的值，那么该索引就是覆盖索引。简单的说：不回表直接通过一次索引查找到列的数据就叫覆盖索引。

表信息

CREATE TABLE `t_user` ( `uid` int(11) NOT NULL AUTO_INCREMENT,
 `uname` varchar(255) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

举例

-- 将 uid 设置成主键索引后通过下面的 SQL 查询   在 explain 的 Extra 列可以看到“Using index”explain select uid from t_user where uid = 1;

覆盖索引在组合索引中用的比较多，举例

explain select age,uname from t_user where age = 10 ;

当不建立组合索引时，会进行回表查询

设置组合索引后再次查询

create index index_user on t_user(age,uname);

2.5 索引匹配方式 2.5.1 最左匹配

在使用组合索引中，比如设置 (age,name) 为组合索引，单独使用组合索引中最左列是可以匹配索引的，如果不使用最左列则不走索引。例如下面 SQL

-- 走索引
explain select * from t_user where age=10 and uname= zhang

下面的 SQL 不走索引

explain select * from t_user where uname= zhang

2.5.2 匹配列前缀

可以匹配某一列的值的开头部分，比如 like abc%。

2.5.3 匹配范围值

可以查找某一个范围的数据。

explain select * from t_user where age

2.5.4 精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

explain select * from t_user where age=18 and uname like  zhang%

2.5.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select age,uname,update_time from t_user 
 where age=18 and uname=  zhang  and update_time= 123

3 索引优化最佳实践

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

-- 推荐
select uid,age,uname from t_user where uid=1;
-- 不推荐
select uid,age,uname from t_user where uid+9=10;

2. 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引参考 2.2 前缀索引
4. 使用索引扫描排序 mysql 有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果 explain 出来的 type 列的值为 index, 则说明 mysql 使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机 IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
mysql 可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方式都一样时，mysql 才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当 orderby 子句引用的字段全部为第一张表时，才能使用索引做排序。order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql 都需要执行顺序操作，而无法利用索引排序。
举例表结构及数据 MySQL 官网或 GItHub 下载。

CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT,
 `rental_date` datetime NOT NULL,
 `inventory_id` mediumint(8) unsigned NOT NULL,
 `customer_id` smallint(5) unsigned NOT NULL,
 `return_date` datetime DEFAULT NULL,
 `staff_id` tinyint(3) unsigned NOT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`rental_id`),
 UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
 KEY `idx_fk_inventory_id` (`inventory_id`),
 KEY `idx_fk_customer_id` (`customer_id`),
 KEY `idx_fk_staff_id` (`staff_id`),
 CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
 CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental 表在 rental_date,inventory_id,customer_id 上有 rental_date 的索引。使用 rental_date 索引为下面的查询做排序

-- 该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
explain select rental_id,staff_id from rental 
where rental_date= 2005-05-25  order by inventory_id desc
-- 下面的查询不会利用索引
explain select rental_id,staff_id from rental 
where rental_date 2005-05-25  order by rental_date,inventory_id

5. union all,in,or 都能够使用索引，但是推荐使用 in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范围列可以用到索引范围条件是：、=、、=、between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引

更新会变更 B + 树，更新频繁的字段建议索引会大大降低数据库性能；

类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；

一般区分度在 80% 以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

8. 创建索引的列，不允许为 null，可能会得到不符合预期的结果

9. 当需要进行表连接的时候，最好不要超过三张表，如果需要 join 的字段，数据类型必须一致

10. 能使用 limit 的时候尽量使用 limit

11. 单表索引建议控制在 5 个以内

12. 单索引字段数不允许超过 5 个（组合索引）

13. 创建索引的时候应该避免以下错误概念

索引越多越好

过早优化，在不了解系统的情况下进行优化

4 索引监控

show status like  Handler_read%

参数说明 Handler_read_first 读取索引第一个条目的次数 Handler_read_key 通过 index 获取数据的次数 Handler_read_last 读取索引最后一个条目的次数 Handler_read_next 通过索引读取下一条数据的次数 Handler_read_prev 通过索引读取上一条数据的次数 Handler_read_rnd 从固定位置读取数据的次数 Handler_read_rnd_next 从数据节点读取下一条数据的次数

以上就是“如何为 MySQL 创建高性能索引”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合！