回城傳送–》《100天精通MYSQL從入門到就業(yè)》

一、練習(xí)題目

二、SQL思路

SQL進(jìn)階-索引的組合索引

初始化數(shù)據(jù)

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,'北京大學(xué)','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'復(fù)旦大學(xué)','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學(xué)','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學(xué)','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學(xué)','Shandong');

解法

要求處理：

• 選出正確答案

分析：

什么是組合索引

組合索引是創(chuàng)建索引時，包含表的多個字段（至少2個字段），且字段會有先后順序，mysql支持索引的最左原則。
還記得前面虛竹哥傳授的索引失效口訣嗎？再啰嗦一遍：模型數(shù)或運最快
其中的最 代表當(dāng)查詢條件有組合索引時，如果不符合索引的最左原則，索引會失效。

為什么要使用組合索引

• 減少開銷：創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a),(a,b),(a,c),(a,b,c) 四個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數(shù)據(jù)的表，使用組合索引會大大的減少開銷！

• 覆蓋索引：創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a),(a,b),(a,c),(a,b,c) 四個索引。對索引列的覆蓋面就擴(kuò)大了，可直接通過組合索引命中數(shù)據(jù)，減少io操作。

• 效率高：索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁；如果是聯(lián)合索引，通過索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

如何使用組合索引

創(chuàng)建組合索引：

• 使用CREATE INDEX 創(chuàng)建組合索引：

CREATE INDEX idx_university_province_age ON user_profile(university,province,age);

• 使用 ALTER TABLE 創(chuàng)建普通索引：

ALTER TABLE user_profile ADD INDEX idx_university_province_age(university,province,age);

mysql創(chuàng)建組合索引時，會遵循索引的最左原則。最左優(yōu)先：組合索引的第一個字段一定要出現(xiàn)在查詢條件中，這個組合索引才會生效。
示例：

explain select *
from user_profile
where university='北京大學(xué)'
and age = 20
and province='BeiJing'

不生效示例：

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

創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a),(a,b),(a,c),(a,b,c) 四個索引，實戰(zhàn)驗證
示例：
創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a)索引

explain select *
from user_profile
where university='北京大學(xué)'

創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a,b)索引

explain select *
from user_profile
where university='北京大學(xué)'
and province='BeiJing'

創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a,c)索引

explain select *
from user_profile
where university='北京大學(xué)'
and age = 20

創(chuàng)建一個組合索引(a,b,c) ,相當(dāng)于創(chuàng)建了(a,b,c)索引,組合索引的字段在查詢條件里順序是任意的。

explain select *
from user_profile
where age = 20
and  university='北京大學(xué)'
and province='BeiJing'

答案

嗯，這題的答案選。。評論區(qū)大聲告訴虛竹哥。

參考：

組合索引的最左優(yōu)先原則

我是虛竹哥，我們明天見~