第24期：索引设计（多值索引的适用场景）

作者: 爱可生开源社区 | 来源:发表于2021-03-24 16:39 被阅读0次

第24期：索引设计（多值索引的适用场景）
第22期：索引设计（组合索引适用场景）
第23期：索引设计（组合索引不适用场景改造）
MySQL5.7从入门到精通(9-12章)--索引、存储过程、视
mysql索引笔记
ES
《数据库索引设计与优化》术语表
oracle表分区和索引分区
搜索，没那么难
ElasticSearch（基础）

image

说到这儿，可能有的人会有些疑问：多值索引不就是联合索引吗，还需要单独开一篇来讲？

多值索引和基于多个字段的联合索引完全不同，联合索引是基于多个一维字段，比如字段 r1 int, r2 int,r3 int，这三个字段的组合是联合索引。一般用于三个字段的联合查找，比如 r1 = 1 and r2 = 2 and r3 = 2 等等。

多值索引则不同，它是基于单个字段的，不同的是多值索引基于多维字段，比如数组：[1,2,3,4,5,6] ，基于这样的一个数组来建立索引，可以用来检索数组内任何一个元素值。比如我要查找元素 3 是不是在这个数组里等等。

多值索引是在 MySQL 8.0.17 后才推出的新功能，主要目的是突破 MySQL 单张表索引的硬性限制（不能超过 64 个）。

下面来讲几个例子介绍下多值索引的大致适用场景。先来看看对于多个字段都有可能参与的查询，基于这个条件，建立下面表 t1，包含主键 ID 以及剩余 6 个字段，表有 300W 行记录。

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `r1` int DEFAULT NULL,
  `r2` int DEFAULT NULL,
  `r3` int DEFAULT NULL,
  `r4` int DEFAULT NULL,
  `r5` int DEFAULT NULL,
  `r6` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  3087152 |
+----------+
1 row in set (1.03 sec)

相关示例数据如下：

mysql> select * from t1 limit 10;
+----+------+------+------+------+------+------+
| id | r1   | r2   | r3   | r4   | r5   | r6   |
+----+------+------+------+------+------+------+
|  1 |  460 |   81 |   24 |  876 |  308 |  911 |
|  2 |  632 |  424 |  225 |  854 |  593 |  402 |
|  3 |  232 |  951 |   60 |  445 |   47 |  898 |
|  4 |  350 |   56 |  230 |  981 |  213 |  122 |
|  6 |  972 |  493 |  547 |  256 |  639 |  428 |
|  7 |  221 |  823 |  449 |  774 |  526 |  306 |
|  8 |  953 |  847 |  372 |  322 |  493 |  496 |
|  9 |    2 |  523 |  606 |  460 |  482 |   30 |
| 13 |  701 |  418 |  984 |  665 |  374 |  876 |
| 14 |  256 |  650 |  481 |  454 |  828 |  779 |
+----+------+------+------+------+------+------+
10 rows in set (0.00 sec)

那现在有一个需求：检索除了主键 ID 列外，其他列包含了数字 650 的记录。SQL 可以这么写：

select count(*) from t1 where r1 = 650  or r2 = 650 or r3 = 650 or r4 = 650  or r5 = 650 or r6 = 650;

由于 6 个字段并集检索，为了检索效率，除了主键外，其他字段都分别单独加上索引，这样就能用上 MySQL 的 INDEX_MERGE 优化策略：

mysql> alter table t1 add key idx_r1(r1), \
-> add key idx_r2(r2),  \
-> add key idx_r3(r3),  \
-> add key idx_r4(r4),  \
-> add key idx_r5(r5),  \
-> add key idx_r6(r6);
Query OK, 0 rows affected (1 min 29.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

来进行下查询，执行了 210 毫秒，时间也不快，不过已经很优化了。

mysql> select count(*) from t1 where r1 = 650
    -> or r2 = 650
    -> or r3 = 650
    -> or r4 = 650
    -> or r5 = 650
    -> or r6 = 650;
+----------+
| count(*) |
+----------+
|    18350 |
+----------+
1 row in set (0.21 sec)

来看看查询计划，MySQL 把这个几个索引都给用上了。

mysql> explain select count(*) from t1 where r1 = 650  or r2 = 650  or r3 = 650 or r4 = 650  or r5 = 650  or r6 = 650\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index_merge
possible_keys: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
          key: idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6
      key_len: 5,5,5,5,5,5
          ref: NULL
         rows: 18392
     filtered: 100.00
        Extra: Using union(idx_r1,idx_r2,idx_r3,idx_r4,idx_r5,idx_r6); Using where
1 row in set, 1 warning (0.00 sec)

那接下来看看如何简化这张表和这条 SQL：

建立一张表 t3，包含字段 ID 和一个 JSON 串，并且把表 t1 的数据处理后导入：

mysql> create table t3 (id int primary key, r_com json);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t3  \
> select id, json_object('id', json_array(r1,r2,r3,r4,r5,r6)) from t1;
Query OK, 3087152 rows affected (43.87 sec)
Records: 3087152  Duplicates: 0  Warnings: 0

导入后的示例数据如下，

mysql> select * from t3 limit 10;
+----+----------------------------------------+
| id | r_com                                  |
+----+----------------------------------------+
|  1 | {"id": [460, 81, 24, 876, 308, 911]}   |
|  2 | {"id": [632, 424, 225, 854, 593, 402]} |
|  3 | {"id": [232, 951, 60, 445, 47, 898]}   |
|  4 | {"id": [350, 56, 230, 981, 213, 122]}  |
|  6 | {"id": [972, 493, 547, 256, 639, 428]} |
|  7 | {"id": [221, 823, 449, 774, 526, 306]} |
|  8 | {"id": [953, 847, 372, 322, 493, 496]} |
|  9 | {"id": [2, 523, 606, 460, 482, 30]}    |
| 13 | {"id": [701, 418, 984, 665, 374, 876]} |
| 14 | {"id": [256, 650, 481, 454, 828, 779]} |
+----+----------------------------------------+
10 rows in set (0.00 sec)

那此时针对表 t3 加一个多值索引：(可以看到多值索引的基础是一个数组)

mysql>  alter table t3 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (58.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来可以把之前的 SQL 改成如下：

 select count(*) from t3 where 650 member of(r_com->"$.id");

来执行下这条 SQL：(执行时间 200 毫秒，和之前多个字段求并集的执行时间差不多)

mysql> select count(*) from t3 where 650 member of(r_com->"$.id");
+----------+
| count(*) |
+----------+
|    18350 |
+----------+
1 row in set (0.20 sec)

看下执行计划，直接走的我们创建的多值索引：

mysql> explain select count(*) from t3 where 650 member of(r_com->"$.id")\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: idx_r_com
          key: idx_r_com
      key_len: 9
          ref: const
         rows: 153
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
1 row in set (0.25 sec)

如果单单从上面的例子看不出来多值索引有啥值得炫耀的地方，无非就是把多个字段变为一个 JSON 字段而已，查询效率也没有高多少，如果理解到这儿，那就需要再多说几句了。MySQL 对于单表来说，开篇也说过，有硬性个数限制，这个个数为 64，也就是说一张表最多只能有 64 个索引。不过现实场景是，如果一张表需要建立 64 个索引，那这张表对于业务来讲，也就废了，基本上写入会巨慢。这会多值索引的用途就来了。

我来举个例子：比如建立一张新表 t2，501 个字段。（由于字段比较多，需要写脚本来处理后续 DDL）

mysql> create table t2(id int auto_increment primary key);
Query OK, 0 rows affected (0.03 sec)

这儿我分别写两个存储过程来构造表结构与表数据：

表结构构造存储过程代码：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_extend_t2_columns`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_extend_t2_columns`(
    IN f_num INT UNSIGNED
    )
BEGIN
    DECLARE i INT DEFAULT 1;
    SET @stmt = 'alter table t2 ';
    WHILE i <= f_num DO
      SET @stmt = CONCAT(@stmt,' add r',i,' int,');
      SET i = i + 1;
    END WHILE;
    SET @stmt = LEFT(@stmt,CHAR_LENGTH(@stmt)-1);
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
    SET @stmt = NULL;
    END$$

DELIMITER ;

造数据存储过程代码：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_generate_t2_data`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_generate_t2_data`(
    IN f_num INT UNSIGNED, 
    IN f_total_record INT UNSIGNED
    )
BEGIN
    DECLARE i ,j INT DEFAULT 1;
    SET @@autocommit = 0;
    WHILE i <= f_total_record DO
      SET @s1 = 'insert into t2 ( ';
      SET @s2 = ' values (';
      SET j = 1;
      WHILE j <= f_num DO
        SET @s1 = CONCAT(@s1,' r',j,',');
        SET @s2 = CONCAT(@s2,CEIL(RAND()*1000),',');
        SET j = j + 1;
      END WHILE;
      SET @s1 = CONCAT(LEFT(@s1,CHAR_LENGTH(@s1) - 1));
          SET @s2 = CONCAT(LEFT(@s2,CHAR_LENGTH(@s2) - 1));

```
  SET @s1 = CONCAT(@s1,')');
  SET @s2 = CONCAT(@s2,')');

  SET @stmt = CONCAT(@s1,@s2);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DROP PREPARE s1;
  IF (MOD(i,100) = 0 ) THEN
        COMMIT;   
  END IF;
  SET i = i + 1;
END WHILE;
COMMIT;
SELECT NULL,NULL,NULL INTO @s1,@s2,@stmt;
END$$
```

DELIMITER ;

分别调用这两个存储过程来创建表结构和数据，给表 t2 添加 10W 条记录。

mysql> call sp_extend_t2_columns(500);
Query OK, 0 rows affected (0.09 sec)

mysql> call sp_generate_t2_data(500,100000);
Query OK, 1 row affected (12 min 59.48 sec)

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.46 sec)

比如现在要进行之前的查询，那需要写的 SQL 就比较长了，需要 OR 500 个字段：

select count(*) from t2 where r1 = 650 or r2 = 650 ... or r500=650

SQL 长度也倒没有大关系，主要性能在这儿存在很大问题，这张表不允许建立这么多索引。（这儿省略验证过程，肯定巨慢）那为了实现这类需求，改造下表 t2，变为表 t4，原理和之前改造表 t1一样。

mysql> create table t4 (id int primary key, r_com json);
Query OK, 0 rows affected (0.02 sec)

插入表 t2 的数据到 t4，还得拼个 SQL，或者写个存储过程。我简单写个存储过程如下：

DELIMITER $$

USE `ytt`$$

DROP PROCEDURE IF EXISTS `sp_copy_t2_data_to_t4`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_copy_t2_data_to_t4`(
    )
BEGIN
    DECLARE i INT DEFAULT 1;
    SET @stmt = 'insert into t4 select id,json_object(''id'',json_array(';
    WHILE i <= 500 DO
      SET @stmt = CONCAT(@stmt,' r',i,',');
      SET i = i + 1;
    END WHILE;
    SET @stmt = LEFT(@stmt,CHAR_LENGTH(@stmt)-1);
    SET @stmt = CONCAT(@stmt,')) from t2');
    PREPARE s1 FROM @stmt;
    EXECUTE s1;
    DROP PREPARE s1;
    SET @stmt = NULL;
    END$$

DELIMITER ;

调用下这个存储过程，并且给表 t4 加上多值索引：

mysql> call sp_copy_t2_data_to_t4;
Query OK, 0 rows affected (23.41 sec)

mysql> alter table t4 add key idx_r_com ((cast(r_com->'$.id' as unsigned array)));
Query OK, 0 rows affected (2 min 15.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

速度也不敢恭维，不过相对简洁性来说，要好过单个列查询，并且速度肯定要优于之前的基于 t3 的全表扫。

mysql> select count(*) from t4 where 270 member of(r_com->'$.id');
+----------+
| count(*) |
+----------+
|    39501 |
+----------+
1 row in set (11.31 sec)

说到这里，想必对多值索引已经有一个很深刻的认知，特别是突破 MySQL 单表索引硬性限制方面，非常出色。

image

第24期：索引设计（多值索引的适用场景）
说到这儿，可能有的人会有些疑问：多值索引不就是联合索引吗，还需要单独开一篇来讲？多值索引和基于多个字段的联合索...
第22期：索引设计（组合索引适用场景）
建立在多个列上的索引即组合索引（联合索引），适用在多个列必须一起使用或者是从左到右方向部分连续列一起使用的业务场景...
第23期：索引设计（组合索引不适用场景改造）
上篇文章已经详细介绍 MySQL 组合索引的概念以及其适用场景，这篇主要介绍 MySQL 组合索引的不适用场景以及...
MySQL5.7从入门到精通(9-12章)--索引、存储过程、视
第9章索引 9.1 索引简介 9.1.1 索引的含义和特点 9.1.2 索引的分类 9.1.3 索引的设计原则 ...
mysql索引笔记
常见索引的类型 hash索引适用于等值查询的场景有序数组适用于等值查询和范围查询搜索数 InnoDB B+树索...
ES
ES 三大操作：构建索引、检索数据、聚合数据 ES 特性：分布式、倒排索引、近实时、相似精度适用场景：倒排...
《数据库索引设计与优化》术语表
索引设计方法中所涉及的术语设计最佳索引的算法三星索引：宽索引。覆盖所有所需列（第3颗星），所需扫描的区域尽可能...
oracle表分区和索引分区
㈠分区表技术概述㈡分区索引技术概述 ⑴ 本地前缀分区索引适用场景：如果历史数据整理非常频繁、而且不能承受全...
搜索，没那么难
1、顺序查询简单适用简单场景2、二分查找递增或者递减，列表3、杨氏矩陈4、分块查询5、索引与倒排索引
ElasticSearch（基础）
1.1 倒排索引倒排索引原理？？ ElasticSearch使用一种称为 ==倒排索引== 的结构，它适用于快...

第24期：索引设计（多值索引的适用场景）

相关文章

第24期：索引设计（多值索引的适用场景）

第22期：索引设计（组合索引适用场景）

第23期：索引设计（组合索引不适用场景改造）

MySQL5.7从入门到精通(9-12章)--索引、存储过程、视

mysql索引笔记

ES

《数据库索引设计与优化》术语表

oracle表分区和索引分区

搜索，没那么难

ElasticSearch（基础）

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SQL调优