关于innodb:第20期索引设计前缀索引

38次阅读

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

这里次要介绍 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 的前缀索引。

10px;">`<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)` </pre>

那这里能够看到，为何会抉择用 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,'/pre>,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 前缀索引的定义规定以及简略应用场景，欢送大家批评指正。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
innodb
发表至： innodb
2021-01-20
 0
关于innodb:故障分析-MySQL-临时表空间数据过多导致磁盘空间不足的问题排查
关于innodb:InnoDB-的并发控制锁与-MVCC
关于innodb:mysql数据库innodb的索引特点
关于innodb:技术分享-怎么找到上锁的-SQL-语句