MySQL 前缀索引

Gan_1314

已于 2022-07-21 17:39:43 修改

阅读量342

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-07-21 17:26:59 首次发布

本文链接：https://blog.csdn.net/Gan_1314/article/details/125916238

版权

MySQL 专栏收录该内容

33 篇文章 6 订阅

订阅专栏

什么是前缀索引

所谓前缀索引：说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时指定），这样建立起来的索引更小，所以查询更快。这有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数

那么为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性

这里又涉及到一个概念，什么是索引选择性？

什么是索引选择性

关于索引的选择性，它是指不重复的索引值和数据表的记录总数的比值，取值范围在 [0,1] 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行

那是不是选择性越高的索引越好呢？当然不是！索引选择性最高为 1，如果索引选择性为 1，就是唯一索引了，搜索的时候就能直接通过搜索条件定位到具体一行记录！这个时候虽然性能最好，但是也是最费空间的，这不符合我们创建前缀索引的初衷

我们一开始之所以要创建前缀索引而不是唯一索引，就是希望能够在索引的性能和空间之间找到一个平衡，我们希望能够选择足够长的前缀以保证较高的选择性（这样在查询的过程中就不需要扫描很多行），但是又希望索引不要太过于占用存储空间

那么我们该如何选择一个合适的索引选择性呢？索引前缀应该足够长，以便前缀索引的选择性接近于索引的整个列，即前缀的基数应该接近于完整列的基数

首先可过如下 SQL 得到全列选择性

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

然后再通过如下 SQL 得到某一长度 prefix_length

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

在上面这条 SQL 执行的时候，我们要注意选择合适的 prefix_length，直至计算结果最接近于全列选择性的时候，就是最佳结果了，然后使用这个 prefix_length 就可以创建前缀索引了

创建前缀索引

获取前缀长度 prefix_length

首先我们通过如下 SQL 来获取一下 city 全列的索引选择性，结果如下

SELECT COUNT(DISTINCT order_id) / COUNT(*) FROM pay_product_orders;

在这里插入图片描述

可以看到，结果为 1。全列选择性为 1 说明这一列的值没有重复的

接下来我们获取前缀长度 prefix_length，这里一共测试了 8 个不同的 prefix_length，来看看各自的选择性

SELECT 
	COUNT(DISTINCT LEFT(order_id,1))/COUNT(*) AS pref1,
	COUNT(DISTINCT LEFT(order_id,2))/COUNT(*) AS pref2,
	COUNT(DISTINCT LEFT(order_id,3))/COUNT(*) AS pref3,
	COUNT(DISTINCT LEFT(order_id,4))/COUNT(*) AS pref4,
	COUNT(DISTINCT LEFT(order_id,5))/COUNT(*) AS pref5,
	COUNT(DISTINCT LEFT(order_id,6))/COUNT(*) AS pref6,
	COUNT(DISTINCT LEFT(order_id,7))/COUNT(*) AS pref7
FROM pay_product_orders;

在这里插入图片描述

很明显，这里的前缀长度 prefix_length 是 4，接下来创建前缀索引

创建前缀索引

ALTER TABLE pay_product_orders ADD INDEX idx_order (order_id ( 4 ));

前缀索引测试

使用如下 SQL 查看其执行计划，结果如下

EXPLAIN SELECT * FROM	pay_product_orders WHERE order_id = '4a8ea6747fee11ec817d000000000002';

在这里插入图片描述

可以看到，这个前缀索引已经用上了，具体搜索流程是这样

从 idx_order 索引中找到第一个值为 4a8e 的记录（order_id 的前 4 个字符）
由于 idx_order 是普通索引，叶子结点保存的是主键值，所以此时拿到了主键值，如 ID1
到主键索引树上查到 ID1 这一行，判断 order_id 的值满不满足 where 后的条件，不满足这一行丢弃
继续回到 idx_order 这个索引树上查下一条记录，发现如果还是 4a8e，取出 ID2，再回到主键索引树上进行判断，如果值正确，将结果返回结果集中
重复执行以上流程，直到从 idx_order 索引树上取出的数据不是 4a8e，循环结束

如果我们建立了前缀索引并且前缀索引的选择性为 1，那么就不需要第 5 步了，如果前缀索引选择性小于 1，就需要第 5 步。既节省了空间，又提高了搜索效率

前缀索引与覆盖索引

使用了前缀索引后，我们来看一个查询 SQL，字段order_id另设置了普通索引

CREATE TABLE `pay_product_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '订单号'
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_order` (`order_id`(4)),
  KEY `order_id` (`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='订单表';

EXPLAIN SELECT order_id FROM	pay_product_orders WHERE order_id = '4a8ea6747fee11ec817d000000000002';

在这里插入图片描述

如上的 SQL 查询的列是 order_id，在前缀索引中 B+Tree 里保存的根本就不是完整的 order_id 字段的值，必须要回表才能拿到需要的数据。所以，用了前缀索引，就用不了覆盖索引了

如果order_id是唯一索引呢

CREATE TABLE `pay_product_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `order_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '订单号',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `order_id` (`order_id`) USING BTREE,
  KEY `idx_order` (`order_id`(4))
) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='订单表';

EXPLAIN SELECT order_id FROM	pay_product_orders WHERE order_id = '4a8ea6747fee11ec817d000000000002';

在这里插入图片描述

很明显，相比前缀索引，优先使用了唯一索引

小结

前缀索引是一种能使索引占用空间更小，查询速度更快的有效办法，但另一方面也有其缺点：mysql 无法使用其前缀索引做 ORDER BY 和 GROUP BY，用了前缀索引，就用不了覆盖索引了
要明确使用前缀索引的目的与优势
- 大大节约索引的占用空间，从而提高索引效率
- 对于 BOLB 、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度
真正的难点在于：要选择足够长的前缀以保证较高的选择性，同时又不能太长，前缀的长度应该使前缀索引的选择性接近索引整个列，即前缀的基数应该接近于完整列的基数