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

╰半夏微凉° 2022-10-13 01:55 181阅读 0赞

建表语句

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)就会停止匹配  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70]

**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 * from`user`where AA='A2' and BB='B2' and CC='C2';`  
![在这里插入图片描述][20210705211826683.png]  
由key=index\_comp和key\_len=606表示，会用到都会用到索引  
2.执行`explain select * from`user`where AA='A2' and BB>'B2' and CC='C2';`  
![在这里插入图片描述][20210705212152355.png]  
由key=index\_comp和key\_len=404表示,只会用到A和B的索引

**3.执行`explain select * from`user`where AA='A2' and BB<>'B2';`**  
![在这里插入图片描述][20210705212402992.png]  
由key=index\_comp和key\_len=404表示,只会用到A和B的索引  
**4.执行`explain select * from`user`where AA='A2' and BB<>'B2';`**

![在这里插入图片描述][20210705212501647.png]  
由key=null和key\_len=null表示,都不会用到索引  
**5.explain select \* from `user` where AA=‘A2’ and BB=‘B2’ ;**  
![在这里插入图片描述][20210705212947888.png]  
由key=index\_comp和key\_len=404表示,只会用到A和B的索引  
**6.explain select \* from `user` where AA=‘A2’ and CC=‘C2’;**  
![在这里插入图片描述][20210705213025568.png]  
由key=index\_comp和key\_len=202表示,只会用到A的索引  
**7.explain select \* from `user` where BB=‘B2’ and CC=‘C2’;**  
![在这里插入图片描述][20210705213130859.png]  
由key=null和key\_len=null表示,都不会用到索引  
**8.explain select \* from `user` where BB=‘B2’ ;**  
![在这里插入图片描述][20210705213159912.png]  
由key=null和key\_len=null表示,都不会用到索引  
**9.explain select \* from `user` where CC=‘C2’;**  
![在这里插入图片描述][20210705213235939.png]  
由key=null和key\_len=null表示,都不会用到索引

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMTk4MDA0_size_16_color_FFFFFF_t_70]: /images/20221005/6618535fe3e84dc194795d7bc98f46b3.png
[20210705211826683.png]: /images/20221005/35d1faf2ccba49469c82a7284afe5dad.png
[20210705212152355.png]: /images/20221005/a1a36f859e6f434f893be20409290c21.png
[20210705212402992.png]: /images/20221005/9faeab8d015e4be5a16f7a9099e22d1d.png
[20210705212501647.png]: /images/20221005/6e6b46a54dcd4a8b9ba9cf015862af5a.png
[20210705212947888.png]: /images/20221005/6c0dae1162fd422b935e39e1e1d82f32.png
[20210705213025568.png]: /images/20221005/bd21227a78944bc9a96f73a8dae2cc87.png
[20210705213130859.png]: /images/20221005/db3aa963758949f4a3da69c08964d0d8.png
[20210705213159912.png]: /images/20221005/c1a4d0545cd948c4a31c79e722e5ce15.png
[20210705213235939.png]: /images/20221005/cea0661b1a3443b5a2e2fb95007c3347.png