回城传送–》《32天SQL筑基》

零、前言

今天是学习 SQL 打卡的第 12 天，每天我会提供一篇文章供群成员阅读（ 不需要订阅付钱 ）。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

​ 我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-索引的组合索引

一、练习题目

二、SQL思路

SQL进阶-索引的组合索引

初始化数据

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');


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
 

解法

要求处理：

• 选出正确答案

分析：

什么是组合索引

组合索引是创建索引时，包含表的多个字段（至少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’

不生效示例：

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

创建一个组合索引(a,b,c) ,相当于创建了(a),(a,b),(a,c),(a,b,c) 四个索引，实战验证
示例：
创建一个组合索引(a,b,c) ,相当于创建了(a)索引

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

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

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

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

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

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

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

答案

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

参考：

组合索引的最左优先原则：https://www.cnblogs.com/CKhomepage/p/10613618.html

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

文章来源: xiaoxuzhu.blog.csdn.net，作者：小虚竹，版权归原作者所有，如需转载，请联系作者。

原文链接：xiaoxuzhu.blog.csdn.net/article/details/126814972