MySQL联合索引笔记

什么是联合索引?

联合索引指的是在一个表上，多个列加起来组成的一个索引。使用联合索引匹配的时候，首先会匹配第一个，如果匹配成功，会继续匹配第二个，依次类推。

最左前缀原则?

我们要使用联合索引，就必须要遵守最左前缀规则，最左前缀指的是查询的时候会从最左的索引开始匹配。比如现在age和name字段是一个联合索引，现在使用select id,name from user where name = ‘张三’进行查询,因为这条SQL没有对age进行条件查询，而age又是联合索引的第一个。没有满足最左前缀原则，所以这条SQL绝对不会使用联合索引。

实战测试

MySQL版本为5.7

创建一张测试表，插入AA、BB、CC列的联合索引

-- ----------------------------
-- Table structure for index_test
-- ----------------------------
DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test`  (
  `TID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AA` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `BB` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `CC` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `DD` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TID`) USING BTREE,
  INDEX `INDEX_COMP`(`AA`, `BB`, `CC`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of index_test
-- ----------------------------
INSERT INTO `index_test` VALUES (1, 'A1', 'B1', 'C1', 'D1', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (2, 'A2', 'B2', 'C2', 'D2', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (3, 'A3', 'B3', 'C3', 'D3', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (4, 'A4', 'B4', 'C4', 'D4', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (5, 'A5', 'B5', 'C5', 'D5', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (6, 'A6', 'B6', 'C6', 'D6', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (7, 'A7', 'B7', 'C7', 'D7', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (8, 'A8', 'B8', 'C8', 'D8', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (9, 'A9', 'B9', 'C9', 'D9', '2022-07-28 18:55:59');
INSERT INTO `index_test` VALUES (10, 'A10', 'B10', 'C10', 'D10', '2022-07-28 18:55:59');

SET FOREIGN_KEY_CHECKS = 1;

使用6条SQL语句进行测试

EXPLAIN
SELECT * FROM index_test WHERE AA = 'A1';

EXPLAIN
SELECT * FROM index_test WHERE AA = 'A1' AND BB = 'B1';

EXPLAIN
SELECT * FROM index_test WHERE AA = 'A1' AND BB = 'B1' AND CC = 'C1';

EXPLAIN
SELECT * FROM index_test WHERE CC = 'A1' AND AA = 'B1' AND BB = 'C1';

EXPLAIN
SELECT * FROM index_test WHERE AA = 'A1' AND CC = 'B1';

EXPLAIN
SELECT * FROM index_test WHERE BB = 'A1';