MYSQL进阶-索引的组合索引

待我称王封你为后i 2023-10-13 11:43 12阅读 0赞

回城传送–》[《100天精通MYSQL从入门到就业》][100_MYSQL]

#### 文章目录 ####

*  一、练习题目
 *  二、SQL思路
 *   *  SQL进阶-索引的组合索引
     *   *  初始化数据
         *  解法
         *   *  什么是组合索引
             *  为什么要使用组合索引
             *  如何使用组合索引
 *  答案
 *  参考：

## 一、练习题目 ##

<table> 
 <thead> 
  <tr> 
   <th>题目链接</th> 
   <th>难度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><a href="https://edu.csdn.net/skill/practice/mysql-ce1f863b4d254d2aadb6ce2331122d16/29660?typeId=80920&amp;language=mysql02&amp;materialId=80919" rel="nofollow">SQL进阶-索引的组合索引</a></td> 
   <td>★★★☆☆</td> 
  </tr> 
 </tbody> 
</table>

## 二、SQL思路 ##

### SQL进阶-索引的组合索引 ###

![在这里插入图片描述][10496ef1411042ef969e67711dd8096b.png]  
![在这里插入图片描述][684ad52f31db46f4bc613a48d5ad9920.png]

#### 初始化数据 ####

drop table if exists user_profile;
    CREATE TABLE `user_profile` (
    `id` int NOT NULL,
    `device_id` int NOT NULL,
    `gender` varchar(14) NOT NULL,
    `age` int ,
    `university` varchar(32) NOT NULL,
    `province` varchar(32)  NOT NULL);
    INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
    INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
    INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
    INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
    INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');

#### 解法 ####

要求处理：

*  选出正确答案

分析：

##### 什么是组合索引 #####

组合索引是创建索引时，包含表的多个字段（至少2个字段），且字段会有先后顺序，mysql支持索引的最左原则。  
还记得前面虚竹哥传授的索引失效口诀吗？再啰嗦一遍：**模型数或运最快**  
其中的**最** 代表当查询条件有组合索引时，如果不符合索引的最左原则，索引会失效。

##### 为什么要使用组合索引 #####

*  减少开销：创建一个组合索引(a,b,c) ,相当于创建了(a),(a,b),(a,c),(a,b,c) 四个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用组合索引会大大的减少开销！
 *  覆盖索引：创建一个组合索引(a,b,c) ,相当于创建了(a),(a,b),(a,c),(a,b,c) 四个索引。对索引列的覆盖面就扩大了，可直接通过组合索引命中数据，减少io操作。
 *  效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% \*10%=1w，效率提升可想而知！

##### 如何使用组合索引 #####

创建组合索引：

*  使用CREATE INDEX 创建组合索引：

> CREATE INDEX idx\_university\_province\_age ON user\_profile(university,province,age);

*  使用 ALTER TABLE 创建普通索引：

> ALTER TABLE user\_profile ADD INDEX idx\_university\_province\_age(university,province,age);

mysql创建组合索引时，会遵循索引的最左原则。最左优先：组合索引的第一个字段一定要出现在查询条件中，这个组合索引才会生效。  
示例：

explain select *
    from user_profile
    where university='北京大学'
    and age = 20
    and province='BeiJing'

![在这里插入图片描述][e642486e13d04a4fbed2419d6b903a69.png]  
不生效示例：

explain select *
    from user_profile
    where age = 20
    and province='BeiJing'

![在这里插入图片描述][dfa3c5b3bc0244438bdc18723512de86.png]  
创建一个组合索引(a,b,c) ,相当于创建了(a),(a,b),(a,c),(a,b,c) 四个索引，实战验证  
示例：  
创建一个组合索引(a,b,c) ,相当于创建了(a)索引

explain select *
    from user_profile
    where university='北京大学'

![在这里插入图片描述][463c2c99b9dc4fa8a46bf51b093c0927.png]

创建一个组合索引(a,b,c) ,相当于创建了(a,b)索引

explain select *
    from user_profile
    where university='北京大学'
    and province='BeiJing'

![在这里插入图片描述][c915fcf032044ad68efb05d7eb35fdae.png]

创建一个组合索引(a,b,c) ,相当于创建了(a,c)索引

explain select *
    from user_profile
    where university='北京大学'
    and age = 20

![在这里插入图片描述][b42a26e385a245e19cc50ef26ed77471.png]

创建一个组合索引(a,b,c) ,相当于创建了(a,b,c)索引,组合索引的字段在查询条件里顺序是任意的。

explain select *
    from user_profile
    where age = 20
    and  university='北京大学'
    and province='BeiJing'

![在这里插入图片描述][7f1b012e123b459eb2a674901ff70bc4.png]

## 答案 ##

嗯，这题的答案选。。评论区大声告诉虚竹哥。

## 参考： ##

[组合索引的最左优先原则][Link 1]

我是虚竹哥，我们明天见~

[100_MYSQL]: https://blog.csdn.net/shi_hong_fei_hei/category_11929488.html
[10496ef1411042ef969e67711dd8096b.png]: https://img-blog.csdnimg.cn/10496ef1411042ef969e67711dd8096b.png
[684ad52f31db46f4bc613a48d5ad9920.png]: https://img-blog.csdnimg.cn/684ad52f31db46f4bc613a48d5ad9920.png
[e642486e13d04a4fbed2419d6b903a69.png]: https://img-blog.csdnimg.cn/e642486e13d04a4fbed2419d6b903a69.png
[dfa3c5b3bc0244438bdc18723512de86.png]: https://img-blog.csdnimg.cn/dfa3c5b3bc0244438bdc18723512de86.png
[463c2c99b9dc4fa8a46bf51b093c0927.png]: https://img-blog.csdnimg.cn/463c2c99b9dc4fa8a46bf51b093c0927.png
[c915fcf032044ad68efb05d7eb35fdae.png]: https://img-blog.csdnimg.cn/c915fcf032044ad68efb05d7eb35fdae.png
[b42a26e385a245e19cc50ef26ed77471.png]: https://img-blog.csdnimg.cn/b42a26e385a245e19cc50ef26ed77471.png
[7f1b012e123b459eb2a674901ff70bc4.png]: https://img-blog.csdnimg.cn/7f1b012e123b459eb2a674901ff70bc4.png
[Link 1]: https://www.cnblogs.com/CKhomepage/p/10613618.html