关于innodb:第21期索引设计函数索引

21次阅读

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

本篇次要介绍 MySQL 的函数索引（也叫表达式索引）。

通常来讲，索引都是基于字段自身或者字段前缀（第 20 篇），而函数索引是基于字段自身加上函数、操作符、表达式等计算而来。如果将表达式或者操作符也看做函数的话，简略来说，这样的索引就能够统称函数索引。

MySQL 的函数索引外部是基于虚构列（generated columns）实现，不同于间接定义虚构列，函数索引主动创立的虚构列自身实时计算结果，并不存储数据，只把函数索引自身存在磁盘上。

MySQL 8.0.13 之前不反对函数索引，所以老版本包含当初支流的 MySQL 5.7 也不反对函数索引，须要手工模仿创立或者改 SQL。

本章基于以下几点来讲函数索引：

1. 函数索引的应用场景

函数索引最最经典的应用场景莫过于就是对日期的解决，特地是表中只定义了一个字段，前期对这个字段的查问都是基于局部后果。比方“2100-02-02 08:09:09.123972”蕴含了日期“2100-02-02”，工夫“08:09:09”，小数位工夫“123972”，有可能会对这个值拆解后局部查问。

举个简略例子，表 t1 有两个字段，一个主键，另外一个工夫字段，总记录数不到 40W。

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


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

执行上面这条 SQL 1，把日期独自拿进去，执行了 0.09 秒。

# SQL 1
<localhost|mysql>select * from t1 where date(log_time) = '2100-02-02';
+--------+----------------------------+
| id     | log_time                   |
+--------+----------------------------+
| 524267 | 2100-02-02 08:09:09.123972 |
+--------+----------------------------+
1 row in set (0.09 sec)

看下它的执行打算，尽管走了索引，然而扫描行数为总记录数，相当于全表扫，这时候比全表扫还不现实，全表扫间接走聚簇索引还快点。

<localhost|mysql>explain select * from t1 where date(log_time) = '2100-02-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_log_time
      key_len: 9
          ref: NULL
         rows: 392413
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

这时最好的办法就是为列 log_time 加一新索引，基于函数 date 的函数索引。

<localhost|mysql>alter table t1 add key idx_func_index_1((date(log_time)));
Query OK, 0 rows affected (2.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次执行下面的 SQL 1，霎时执行结束。

<localhost|mysql>select * from t1 where date(log_time) = '2100-02-02';
+--------+----------------------------+
| id     | log_time                   |
+--------+----------------------------+
| 524267 | 2100-02-02 08:09:09.123972 |
+--------+----------------------------+
1 row in set (0.00 sec)

接下来查看执行打算，结果显示走函数索引 idx_func_index_1 扫描记录数只有一行，执行打算达到最优。

<localhost|mysql>explain select * from t1 where date(log_time) = '2100-02-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_func_index_1
          key: idx_func_index_1
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

如果想查看 MySQL 函数索引外部创立的列，间接 show create table 看是没有后果的，比方上面只看到一个新的索引。

<localhost|mysql>show create table t1\G
...
  KEY `idx_func_index_1` ((cast(`log_time` as date)))
) ENGINE=InnoDB AUTO_INCREMENT=524268 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

通过 MySQL 8.0 的新语句 show extended columns 查看暗藏的列，上面后果发现的确是新加了一个虚构列。

<localhost|mysql>show extended columns from t1;
...
| bbd3daff935e7a4d0991a8393ec03728 | date            | YES  | MUL | NULL    | VIRTUAL GENERATED |
...
5 rows in set (0.03 sec)

2. 函数索引在解决 JSON 类型的注意事项

比方须要遍历 JSON 类型的子串作为索引，间接用遍历操作符 ->> 报错。

<localhost|mysql>create table t2 (id int primary key, r1 json);
Query OK, 0 rows affected (0.09 sec)

<localhost|mysql>alter table t2 add key idx_func_index_2((r1->>'$.x'));
ERROR 3757 (HY000): Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.

操作符 ->> 示意从 JSON 串中遍历指定门路的 value，在 MySQL 外部转换为 json_unquote(jso_extract(…))，而函数 json_unquote 返回后果具备以下个性：

  • 数据类型为 longtext，在 MySQL 里 longtext 类型只反对前缀索引，必须用函数 cast 来转换类型。
  • json_unquote 调用后果的排序规定为 utf8mb4_bin，cast 调用后果的排序规定和以后 session 的排序规定一样，有可能不是 utf8mb4_bin，所以函数索引中要显式定义排序规定。

所以针对 JSON 字段来建设新的函数索引：

<localhost|mysql>alter table t2 add key idx_func_index_2((cast(r1->>'$.x' as char(1)) collate utf8mb4_bin));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

看下表构造，操作符 ->> 被转换为 json_unquote(json_extract(…))，并且排序规定为 utf8mb4_bin。

<localhost|mysql>show create table t2\G
*************************** 1. row ***************************
      Table: t2
...
 KEY `idx_func_index_2` (((cast(json_unquote(json_extract(`r1`,_utf8mb4'$.x')) as char(1) charset utf8mb4) collate utf8mb4_bin)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

接下来插入几条记录，看看这个函数索引的应用。

<localhost|mysql>select * from t2;
+----+---------------------+
| id | r1                  |
+----+---------------------+
|  1 | {"x": "1", "y": 10} |
|  2 | {"x": "2", "y": 20} |
|  3 | {"x": "a", "y": 20} |
|  4 | {"x": "A", "y": 20} |
+----+---------------------+
4 rows in set (0.00 sec)

执行下 SQL 2，并且看下执行打算，间接走了方才创立的函数索引。

# SQL 2
<localhost|mysql>select * from t2 where r1->>'$.x'='a';
+----+---------------------+
| id | r1                  |
+----+---------------------+
|  3 | {"x": "a", "y": 20} |
+----+---------------------+
1 row in set (0.00 sec)

<localhost|mysql>explain select * from t2 where r1->>'$.x'='a'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t2
  partitions: NULL
        type: ref
possible_keys: idx_func_index_2
         key: idx_func_index_2
     key_len: 7
         ref: const
        rows: 1
    filtered: 100.00
       Extra: NULL
1 row in set, 1 warning (0.00 sec)

这里其实应该有个疑难，对函数索引的调用，必须要依照之前定义好的函数来执行，否则不会用到索引，那 SQL 2 怎么能够间接到用索引？
MySQL 在这块儿其实外部曾经转换为正确的语句。查看下方才 EXPLAIN 的 WARNINGS 信息。能够看到 SQL 2 被 MySQL 转换为恪守函数索引规定的正确语句。

<localhost|mysql>show warnings\G
*************************** 1. row ***************************
 Level: Note
  Code: 1003
Message: /* select#1 */ select `ytt`.`t2`.`id` AS `id`,`ytt`.`t2`.`r1` AS `r1` from `ytt`.`t2` where ((cast(json_unquote(json_extract(`ytt`.`t2`.`r1`,_utf8mb4'$.x')) as char(1) charset utf8mb4) collate utf8mb4_bin) = 'a')
1 row in set (0.00 sec)

3. 函数索引代替前缀索引？

之前讲过前缀索引，可能会有这样的疑难。前缀索引能不能被函数索引代替？当然是不行的！函数索引要求查问条件严格依照函数索引的定义匹配，尽管有的场景下 MySQL 能够外部转换，然而 MySQL 无奈为每个函数都替换为最优化的写法。比方函数 substring,left,right 等。

上面例子用来模仿下是否能够用函数索引代替前缀索引。示例表 t3，一个前缀索引和两个函数索引实现的目标一样，然而理论查问的时候 SQL 语句并不一样。

<localhost|mysql>show create table t3\G
*************************** 1. row ***************************
      Table: t3
Create Table: CREATE TABLE `t3` (
 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
 `r1` char(36) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `idx_r1_prefix` (`r1`(8)),
 KEY `idx_func_index_3` ((left(`r1`,8))),
 KEY `idx_func_index_4` ((substr(`r1`,1,8)))
) ENGINE=InnoDB AUTO_INCREMENT=249 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
以下 SQL 3、SQL 4、SQL 5 写法不一样，查问后果一样，走的索引不一样。# SQL 3
select * from t3 where r1 like 'de45c7d9%';

# SQL 4
select * from t3 where left(r1,8) ='de45c7d9';

# SQL 5
select * from t3 where substring(r1,1,8) ='de45c7d9';

<localhost|mysql>select * from t3 where r1 like 'de45c7d9%';
+-----+--------------------------------------+
| id  | r1                                   |
+-----+--------------------------------------+
| 178 | de45c7d9-935c-11ea-8421-08002753f58d |
+-----+--------------------------------------+
1 row in set (0.00 sec)

<localhost|mysql>select * from t3 where left(r1,8) ='de45c7d9';
+-----+--------------------------------------+
| id  | r1                                   |
+-----+--------------------------------------+
| 178 | de45c7d9-935c-11ea-8421-08002753f58d |
+-----+--------------------------------------+
1 row in set (0.00 sec)

<localhost|mysql>select * from t3 where substring(r1,1,8) ='de45c7d9';
+-----+--------------------------------------+
| id  | r1                                   |
+-----+--------------------------------------+
| 178 | de45c7d9-935c-11ea-8421-08002753f58d |
+-----+--------------------------------------+
1 row in set (0.00 sec)

各自的查问打算，每条 SQL 走的不同的索引。

<localhost|mysql>explain select * from t3 where r1 like 'de45c7d9%'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t3
  partitions: NULL
        type: range
possible_keys: idx_r1_prefix
         key: idx_r1_prefix
     key_len: 33
         ref: NULL
        rows: 1
    filtered: 100.00
       Extra: Using where
1 row in set, 1 warning (0.00 sec)

<localhost|mysql>explain select * from t3 where left(r1,8) ='de45c7d9'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t3
  partitions: NULL
        type: ref
possible_keys: idx_func_index_3
         key: idx_func_index_3
     key_len: 35
         ref: const
        rows: 1
    filtered: 100.00
       Extra: Using where
1 row in set, 1 warning (0.00 sec)

<localhost|mysql>explain select * from t3 where substring(r1,1,8) ='de45c7d9'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t3
  partitions: NULL
        type: ref
possible_keys: idx_func_index_4
         key: idx_func_index_4
     key_len: 35
         ref: const
        rows: 1
    filtered: 100.00
       Extra: Using where
1 row in set, 1 warning (0.00 sec)

此时删除掉函数索引 idx_func_index_3, SQL 4 就无奈走正确的索引。

<localhost|mysql>alter table t3 drop key idx_func_index_3;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

<localhost|mysql>explain select * from t3 where left(r1,8) ='de45c7d9'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: t3
  partitions: NULL
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 128
    filtered: 100.00
       Extra: Using where
1 row in set, 1 warning (0.00 sec)

查看 warnings，发现 MySQL 优化器转换后的 SQL，LEFT 函数还是放弃原样，然而表里没有基于 LEFT 函数的索引，只能全表扫。

<localhost|mysql>show warnings\G
*************************** 1. row ***************************
 Level: Note
  Code: 1003
Message: /* select#1 */ select `ytt`.`t3`.`id` AS `id`,`ytt`.`t3`.`r1` AS `r1` from `ytt`.`t3` where (left(`ytt`.`t3`.`r1`,8) = 'de45c7d9')
1 row in set (0.00 sec)

4. 老版本如何实现函数索引

函数索引是 MySQL 8.0.13 才有的。那在老的版本如何实现呢？

MySQL 5.7 自持虚构列，只须要在虚构列上创立一个一般索引就行。

MySQL 5.6 以及 MySQL 5.5 等，则须要本人定义一个冗余列，而后定期更新这列内容。当然最外围的是如何布局好定期更新内容这块。这块如果探讨起来，内容十分多，能够参考我之前写的对于表样例数据更新收集这块内容，MySQL 外部的做法。

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

正文完
innodb
发表至： innodb
2021-02-03
 0
关于innodb:第32问innodblogbuffersize-到底有什么作用
关于innodb:第21期索引设计函数索引
关于innodb:InnoDb事物的几个特性总结
关于innodb:故障分析-MySQL-临时表空间数据过多导致磁盘空间不足的问题排查