理解联合索引(a,b,c)

建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `TID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AA` varchar(50) NOT NULL DEFAULT '',
  `BB` varchar(50) NOT NULL DEFAULT '',
  `CC` varchar(50) NOT NULL DEFAULT '',
  `DD` varchar(50) NOT NULL DEFAULT '',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TID`),
  KEY `index_comp` (`AA`,`BB`,`CC`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

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

执行的查询语句

explain select * from `user` where AA='A2' and BB='B2' and CC='C2';

explain select * from `user` where AA='A2' and BB>'B2' and CC='C2';

explain select * from `user` where AA='A2' and BB<>'B2';

explain select * from `user` where AA>'A2' and BB>'B2';

explain select * from `user` where AA='A2' and BB='B2' ;
explain select * from `user` where AA='A2' and CC='C2';
explain select * from `user` where BB='B2'  and CC='C2';

explain select * from `user` where BB='B2'  ;
explain select * from `user` where  CC='C2';

原理图：组合索引 有“最左前缀”原则，遇到范围查询(>、<、between、like)就会停止匹配

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型（ ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好））
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
1.执行explain select * fromuserwhere AA='A2' and BB='B2' and CC='C2';

由key=index_comp和key_len=606表示，会用到都会用到索引
2.执行explain select * fromuserwhere AA='A2' and BB>'B2' and CC='C2';

由key=index_comp和key_len=404表示,只会用到A和B的索引

3.执行explain select * fromuserwhere AA='A2' and BB<>'B2';

由key=index_comp和key_len=404表示,只会用到A和B的索引
4.执行explain select * fromuserwhere AA='A2' and BB<>'B2';

由key=null和key_len=null表示,都不会用到索引
5.explain select * from user where AA=‘A2’ and BB=‘B2’ ;

由key=index_comp和key_len=404表示,只会用到A和B的索引
6.explain select * from user where AA=‘A2’ and CC=‘C2’;

由key=index_comp和key_len=202表示,只会用到A的索引
7.explain select * from user where BB=‘B2’ and CC=‘C2’;

由key=null和key_len=null表示,都不会用到索引
8.explain select * from user where BB=‘B2’ ;

由key=null和key_len=null表示,都不会用到索引
9.explain select * from user where CC=‘C2’;

由key=null和key_len=null表示,都不会用到索引