MySQL索引的使用(1)最左匹配原则

建表语句：

DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `index1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `index2` int(0) NULL DEFAULT NULL,
  `index3` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `code` int(0) NULL DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_index123`(`index1`, `index2`, `index3`) USING BTREE,
  INDEX `idx_code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

添加索引

ALTER TABLE index_test ADD index idx_index123 (index1, index2, index3);
ALTER TABLE index_test ADD index idx_code (code);

添加数据

INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('101', 1, 'a', 1, 'name1');
INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('202', 2, 'b', 3, 'name2');
INSERT INTO `index_test` (`index1`, `index2`, `index3`, `code`, `name`) VALUES ('303', 3, 'c', 5, 'name3');

不满足最左匹配原则

EXPLAIN
SELECT * from index_test where index1 = '101' and index2 = 1 and index3 = 'a';  
#走索引

EXPLAIN
SELECT * from index_test where index1 = '101' and index2 = 1; 
#走索引

EXPLAIN
SELECT * from index_test where index1 = '101' ; 
#走索引

EXPLAIN
SELECT * from index_test where index1 = '101' and index3 = 'a'; 
#走索引，只是用上了index1字段索引

EXPLAIN
SELECT * from index_test where index3 = 'a' and index1 = '101'; 
#走索引

EXPLAIN
SELECT * from index_test where index2 = 1 and index3 = 'a';  
#不走索引

因为联合索引的情况下，数据是按照索引第一列排序，在第一列数据相同才会按照第二列排序，第三列又是根据前面两列排序后再来排序 中间断层这种，其实他只是走了最左边的字段索引，剩下的没用上 查询条件中，只要有最左边的索引字段，就会走索引；和where条件字段顺序无关，即使中间断了层也能走上索引