第24期：索引设计（多值索引的适用场景）
发布时间：2023-02-08 12:54:41 所属栏目：MySql教程 来源：
导读： 说到这儿，可能有的人会有些疑问： 多值索引不就是联合索引吗，还需要单独开一篇来讲？
多值索引和基于多个字段的联合索引完全不同，联合索引是基于多个一维字段mysql索引表，比如字段 r1 int, r2 int,r
多值索引和基于多个字段的联合索引完全不同，联合索引是基于多个一维字段mysql索引表，比如字段 r1 int, r2 int,r
|
说到这儿，可能有的人会有些疑问： 多值索引不就是联合索引吗，还需要单独开一篇来讲？ 多值索引和基于多个字段的联合索引完全不同，联合索引是基于多个一维字段mysql索引表，比如字段 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 单表索引硬性限制方面，非常出色。 （编辑：百客网 - 百科网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐