联合索引的应用

构建表

创建表

CREATE TABLE `jianghu`.`test_index`  (
  `id` int(11) NULL DEFAULT NULL,
  `a` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `b` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `c` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `d` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  INDEX `IDX_ABCD`(`a`, `b`, `c`, `d`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

插入数据

INSERT INTO test_index
VALUES(1, 'aa', '中国', 'aaaa', '中国你好'),
(2, 'bb', '美国', 'bbbb', '美国你好'),
(3, 'cc', '朝鲜', 'cccc', '朝鲜你好'),
(4, 'dd', '韩国', 'dddd', '韩国你好');

创建联合索引

ALTER TABLE test_index ADD INDEX IDX_A_B_C_D(a, b, c, d);

索引覆盖长度验证

DESC SELECT * FROM test_index WHERE a = 'aa' AND b = '中国' AND c = 'aaaa' AND d = '中国中国';