第20期：索引设计（前缀索引）

于由杨涛涛发布

这里主要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储结构不能是 HASH，HASH 不支持前缀索引。

先看下面这两行示例数据：

你是中国人吗？是的是的是的是的是的是的是的是的是的是的

确定是中国人？是的是的是的是的是的是的是的是的是的是的

这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？

大致有以下 3 种方法：

  1. 拿整个串的数据来做索引

    这种方法来的最简单直观，但是会造成索引空间极大的浪费。重复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源消耗。

  2. 将字符拆开，将一部分做索引

    把数据前面几个字符和剩余的部分字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建立索引。如果排除掉表结构更改这块影响，那这种方法无疑是最好的。

  3. 把前面 6 个字符截取出来的子串做一个索引

能否不拆分字段，又能避免太多重复值的冗余？我们今天讨论一下前缀索引。

前缀索引

前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。

MySQL 基本上大部分存储引擎都支持前缀索引，目前只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/VARCHAR、TEXT/BLOB、BINARY/VARBINARY。

  • 字符类型基于前缀字符长度，f1(10) 指的前 10 个字符；
  • 二进制类型基于字节大小，f1(10) 指的是前 10 个字节长度；
  • TEXT/BLOB 类型只支持前缀索引，不支持整个字段建索引。

举个简单例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的普通二级索引，另外一个是基于字段r1的前缀索引。

<localhost|mysql>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `r1` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_r1` (`r1`),
  KEY `idx_r1_p` (`r1`(6))
) ENGINE=InnoDB AUTO_INCREMENT=32755 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


<localhost|mysql>select count(*) from t1;
+----------+
| count(*) |
+----------+
|    24576 |
+----------+
1 row in set (0.04 sec)

下面分别是表 t1 只有 idx_r1 和 idx_r1_p 的表空间文件大小，很明显，前缀索引非常有优势。

# idx_r1
root@debian-ytt1:/var/lib/mysql/3306/ytt# du -sh
26M     .

# idx_r1_p
root@debian-ytt1:/var/lib/myzsql/3306/ytt# du -sh
20M     .

接下来查询以 sample 关键词开头的记录条数。


<localhost|mysql>select count(*) from t1 where r1 like 'sample%';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

对应的执行计划。可以看出，MySQL 选择了体积较小的前缀索引 idx_r1_p。

<localhost|mysql>explain select count(*) from t1 where r1 like 'sample%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: idx_r1,idx_r1_p
          key: idx_r1_p
      key_len: 27
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

那这里可以看到，为何会选择用 r1(6) 来做前缀，而不是 r1(5) 或者其他的？下面的 SQL 语句列出了所有基于关键词 sample 的可选值，SQL 1 – SQL 6 基于关键词的前缀长度不同。

SQL 1 – SQL 6 的前缀长度依次为 6 – 1 个字符。

# SQL 1
select count(*) from t1 where r1 like 'sample%'；

# SQL 2
select count(*) from t1 where r1 like 'sampl%'；

# SQL 3
select count(*) from t1 where r1 like 'samp%'；

# SQL 4
select count(*) from t1 where r1 like 'sam%'；

# SQL 5
select count(*) from t1 where r1 like 'sa%'；

# SQL 6
select count(*) from t1 where r1 like 's%';

那可否设计一个合适的前缀索引来让以上 6 条 SQL 的执行都能够达到最优呢？答案是肯定的。前提是计算出在当前记录下，被索引字段每个前缀对比整个字段的分散比率值，也叫前缀索引的可选择性（索引字段的可选性，我有另外一篇文章专门介绍），这个值选择的合适与否，直接影响到前缀索引的运行效率。

以下把字段 r1 可选择性查出来，结果为 0.0971，之后只需要计算每个前缀对应的数据分散比率是否和这个值相等或者无限接近即可。

<localhost|mysql>SELECT TRUNCATE(COUNT(DISTINCT r1) / COUNT(r1),4) 'taotal_pct' FROM t1;
+------------+
| taotal_pct |
+------------+
|     0.0971 |
+------------+
1 row in set (0.13 sec)

为了找到最合适的索引前缀长度， 我写了一个简单的函数，用来依次返回字段 r1 每个前缀长度的数据分散比率。函数 func_calc_prefix_length 返回一个 JSON 对象，对象的 KEY 和 VALUE 分别记录了前缀长度以及对应的分散比率。

DELIMITER $$

USE `ytt`$$

DROP FUNCTION IF EXISTS `func_calc_prefix_length`$$

CREATE DEFINER=`ytt`@`%` FUNCTION `func_calc_prefix_length`() RETURNS JSON
BEGIN
      DECLARE v_total_pct DECIMAL(20,4);
      DECLARE v_prefix_pct DECIMAL(20,4);
      DECLARE v_result JSON DEFAULT '[]';
      DECLARE i TINYINT DEFAULT 1;
      
  SELECT TRUNCATE(COUNT(DISTINCT r1) / COUNT(r1),4) INTO v_total_pct FROM t1;
  label1:LOOP
    SELECT TRUNCATE(COUNT(DISTINCT LEFT(r1,i)) / COUNT(r1),4) INTO v_prefix_pct FROM t1; 
    SET v_result = JSON_ARRAY_APPEND(v_result,'$',JSON_OBJECT(i,v_prefix_pct));       
    IF v_prefix_pct >= v_total_pct THEN
      LEAVE label1;        
    END IF;        
    SET i = i + 1;
  END LOOP;
  RETURN v_result;
END$$
DELIMITER ;

调用下这个函数：

<localhost|mysql>SELECT func_calc_prefix_length() AS prefix_length\G
*************************** 1. row ***************************
prefix_length: [{"1": 0.0003}, {"2": 0.0005}, {"3": 0.0008}, {"4": 0.0013}, {"5": 0.0093}, {"6": 0.0971}]
1 row in set (0.32 sec)

函数结果汇总了每个不同的前缀对应的数据分散比率。由此数据可以看到，在当前数据的分布范围内，前缀为 6 是最合适的，6 最接近于字段 r1 的全部数据分布比率。所以以上 SQL 1 – SQL 6 都可以基于前缀为 6 的索引很好的运行。

执行下 SQL 6，

<localhost|mysql>select count(*) from t1 where r1 like 's%';
+----------+
| count(*) |
+----------+
|       29 |
+----------+
1 row in set (0.00 sec)

那前缀索引有没有可能用于如下 SQL？

# SQL 7

select count(*) from t2 where r1 like '%sample'；

表 t2 和表 t1 结构一致，数据分布有些不同。针对 SQL 7 这样的查询，过滤条件左边是通配符 %，没有具体的值，此时无法使用索引，SQL 7 只能全表扫描，查询时间 0.1 秒。

<localhost|mysql>select count(*) from t2 where r1 like '%sample';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.10 sec)

查看下 sample 为后缀的表记录样例。

<localhost|mysql>select * from t2 where r1 like '%sample' limit 1\G
*************************** 1. row ***************************
id: 14
r1: mysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmyssample
1 row in set (0.13 sec)

针对此种情形，有两种优化方法：

第一，可以把数据按照后缀做一个拆分，后缀部分单独为一个字段，然后给这个字段加一个索引。除了要加字段，此方法很完美~

建一个表 t3，把表 t2 的数据导进去。

CREATE TABLE `t3` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `r1` varchar(300) DEFAULT NULL,
  `suffix_r1` varchar(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_suffix_r1` (`suffix_r1`)
) ENGINE=InnoDB

<localhost|mysql>insert into t3 select id,r1,right(r1,6) from t2;
Query OK, 24576 rows affected (19.05 sec)
Records: 24576  Duplicates: 0  Warnings: 0

再次执行 SQL 7，查询瞬间出来结果。

<localhost|mysql>select count(*) from t3 where suffix_r1 = 'sample';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
第二，可以把数据反转过来后建立前缀索引查询记录。对表 t2 克隆一张表 t4。
<localhost|mysql>insert into t4 select id,reverse(r1) from t2;
Query OK, 24576 rows affected (5.25 sec)
Records: 24576  Duplicates: 0  Warnings: 0

查询关键词进行反转查询，

<localhost|mysql>select count(*) from t4  where r1 like 'elpmas%';
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

再看下查询计划，走了前缀索引。不过这样的缺点是查询记录的时候需要在 SQL 层处理记录数据，加上反转函数。

<localhost|mysql>explain select count(*) from t4  where r1 like 'elpmas%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t4
   partitions: NULL
         type: range
possible_keys: idx_r1_p
          key: idx_r1_p
      key_len: 27
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

总结

今天大致讲了下 MySQL 前缀索引的定义规则以及简单使用场景，欢迎大家批评指正。

文章推荐：

第19期：索引设计（哈希索引数据分布与使用场景）
第18期：索引设计（认识哈希表）
第17期：索引设计（主键设计）

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

分类： MySQL 调优
标签：

相关文章

MySQL 调优

第46期：EXPLAIN：解说一条简单 SQL 语句的执行计划

在MySQL里，当通过各种手段抓取到影响系统性能的慢SQL语句后，此刻 阅读更多…

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…