結(jié)論

1. 在where后面的過(guò)濾字段上建立索引(select/update/delete后面的where都是適用的)，使用索引加快過(guò)濾效率，不用進(jìn)行全表掃描
2. 在具有唯一要求的字段上添加唯一索引，加快查詢效率，查到即可直接返回
3. group by或者order by后面的字段添加索引，由于索引是排好序的，所以建立索引就等同于在查詢之前已經(jīng)是排好序了（這里需要注意建立的聯(lián)合索引建立中字段的順序，可以結(jié)合具體案例場(chǎng)景7進(jìn)行學(xué)習(xí)）
4. 在DISTINCT（去重字段）后面的字段添加索引，由于建立了索引，那么相同的數(shù)據(jù)就是挨在一起的，所以就可以進(jìn)行快速的去重操作，否則可能就需要將相同的數(shù)據(jù)找出來(lái)在進(jìn)行去重操作
5. 在多表連接join的時(shí)候在連接的字段上建立索引（小表驅(qū)動(dòng)大表）
6. 取字符串一定前綴建立索引（不是用整個(gè)字符串作為索引，否則將會(huì)占用太大的空間）
7. 在頻繁使用的列上建立索引（可以建立聯(lián)合索引，同時(shí)最頻繁使用的字段應(yīng)該在聯(lián)合索引的最左側(cè)，最左側(cè)原則）
8. 在區(qū)分度高的列上建立索引（主鍵的區(qū)分度最高，因?yàn)樗械逆I都是唯一的）

建立索引的場(chǎng)景

場(chǎng)景一：在where字段后面的字段建立索引

-- 描述：當(dāng)where中有多個(gè)條件需要進(jìn)行匹配的時(shí)候，那么可以創(chuàng)建聯(lián)合索引，這樣所有的條件都可以使用索引，大大提高了檢索的效率
select * from student_info where student_id = 1;
-- 當(dāng)然數(shù)據(jù)量比較大的時(shí)候給where后面的字段添加索引
create index student_id_index on student_info (student_id)

未添加索引前，耗費(fèi)0.383秒，基本遍歷整個(gè)表

添加索引后，耗費(fèi)0.001秒，使用了索引（但是創(chuàng)建索引的時(shí)候會(huì)耗費(fèi)一定時(shí)間）

在頻繁的查詢的業(yè)務(wù)中可以對(duì)where篩選的字段建立索引，如果where篩選的字段有多個(gè)還可以建立聯(lián)合索引

場(chǎng)景二：在具有唯一性約束的字段上建立唯一索引（查找到目標(biāo)即可返回不用繼續(xù)查找）

select * from student_info where id = 1001;
-- 因?yàn)閷W(xué)號(hào)是唯一的，所以可以在學(xué)號(hào)這個(gè)字段上添加唯一所用
create index id_unique on student_info(id);

具有唯一性約束的字段上就可以建立唯一索引，雖然建立了唯一索引對(duì)insert操作有一定的影響(需要判斷新增的數(shù)據(jù)是否已經(jīng)在表中)，但是建立唯一索引對(duì)于查詢的效率是顯著提升的，例如上面的例子，因?yàn)榻⒘宋ㄒ凰饕?，一旦查找到id為1001的學(xué)生信息之后就不需要判斷數(shù)據(jù)庫(kù)中是否還有id等于1001的學(xué)生（只有唯一一份），直接返回信息即可，如果沒(méi)有建立索引，那么就需要全表掃描

場(chǎng)景三：經(jīng)常group by和order by的字段上建立索引（因?yàn)樗饕旧砭褪桥藕眯虻?，相?dāng)于查詢之前就已經(jīng)進(jìn)行了排序）

select * from student_info order by name;
-- 這里就可以給name字段進(jìn)行索引的添加
select * from student_info group by class_id;
-- 這里就可以給class_id字段添加索引

建立索引前，耗時(shí)0.501秒，使用的是所有數(shù)據(jù)在內(nèi)存中排序

建立索引后，耗時(shí)0.01秒

場(chǎng)景四：在DISTINCT后面的字段添加索引（索引已經(jīng)將相同的字段排好序，去重效率更高）

select distinct(student_id) from student_info;
-- 這里就可以根據(jù)student_id字段建立索引
create index student_id_index on student_info;

建立了索引，那么默認(rèn)就是按照索引字段的升序排列的，那么相同值的字段也就排列在一起了，那么去重也就變得簡(jiǎn)單、高效

場(chǎng)景五：在join多表連接大表中的連接字段建立索引

SELECT s.course_id,NAME,s.student_id,c.course_name
FROM student_info s JOIN course c
ON s.`course_id` = c.`course_id`
WHERE NAME = 'xiaoyuanhao';
-- 根據(jù)大表驅(qū)動(dòng)小表的原則需要在student_info表的course_id字段上建立索引

沒(méi)有建立索引之前，耗時(shí)0.697s，沒(méi)有使索引

建立索引后，使用了索引，耗時(shí)0.003s

小表驅(qū)動(dòng)大表：

通過(guò)對(duì)小表進(jìn)行逐一遍歷，同時(shí)在大表中的連接字段建立索引即可加快查詢，本案例中，每次取出課程表中course_id和學(xué)生表中學(xué)生的course_id進(jìn)行連接操作，在學(xué)生表中對(duì)course_id建立索引即可

場(chǎng)景六：使用字符串的前綴建立索引

create table shop(address varchar(120) not null);
alter table shop add index(address(12));
--這里只是對(duì)表中的address的前12個(gè)字符建立了索引，而不是整個(gè)字符串建立索引

前綴建立索引的原因：

• 由于有些字符串很長(zhǎng)，如果為整個(gè)字符串建立索引，那么索引將占用很大的空間
• 由于需要存儲(chǔ)整個(gè)字符串，那么數(shù)據(jù)項(xiàng)就會(huì)很大，那么索引樹(shù)的深度就會(huì)加深，檢索速度下降
• 雖然可能出現(xiàn)在索引中兩個(gè)字符串相同，但是再根據(jù)主鍵進(jìn)行回表操作效率依然比較高

如何確定前綴索引中前綴的長(zhǎng)度呢？（也就是如果前綴的長(zhǎng)度太短，那么索引的區(qū)分度就很低，從多個(gè)字符串截取的前綴數(shù)據(jù)可能都是一樣的，但是如果前綴索引的前綴過(guò)長(zhǎng)，那么前綴索引的優(yōu)點(diǎn)就消失了）

引入了區(qū)別度的概念，select count(distinct left(索引字段,前綴索引長(zhǎng)度) / count(*) from xxx)，該值越接近1，那么區(qū)分度就越明顯，那么該索引長(zhǎng)度就是所求的前綴索引長(zhǎng)度

場(chǎng)景七：在頻繁使用的列上建立索引或聯(lián)合索引（頻繁使用的字段應(yīng)該在索引的左側(cè)）

select * from xiaoyuanhao where age = 18;
select * from xiaoyuanhao where age = 19 and sex = 'man';
select * from xiaoyuanhao where age = 10 and sex = 'man' and password = '123456';
-- 在這里實(shí)際上就可以建立age,sex,password的聯(lián)合索引，只需要建立一個(gè)索引，這三個(gè)查詢都是可以使用的
create index age_sex_password_index on xiaoyuanhao(age,sex,password);
select * from student_info group by class_id order by name;
-- 在這里可以建立class_id和name的聯(lián)合索引，但是一定要注意索引的順序，一定是要class_id在前，name在后，因?yàn)樵趕elect語(yǔ)句中執(zhí)行的順序是先group by 之后才是 order by 索引如果索引的字段順序是相反的，那么就無(wú)法使用索引
create index class_id_name_index on student(class_id,name);

索引建立需要符合順序的原因：

索引字段的順序如果是錯(cuò)誤的，那么索引就會(huì)失效，因?yàn)樗饕龑?shí)際上是排好序的，如果索引建立的時(shí)候是現(xiàn)根據(jù)name排好序之后在根據(jù)class_id進(jìn)行排序，那么在面對(duì)需要先根據(jù)class_id排序再根據(jù)name排序的業(yè)務(wù)就無(wú)法進(jìn)行使用

補(bǔ)充：

在select * from xxx where age = 19 and sex = ‘man’ and password = '123456’這里索引建立的順序不一定是(age,sex,password)因?yàn)樵趯?shí)際執(zhí)行的過(guò)程中，優(yōu)化器會(huì)優(yōu)化執(zhí)行步驟會(huì)按照索引的順序進(jìn)行查詢，但是group by 和 order by的執(zhí)行順序是無(wú)法改變的，索引必須嚴(yán)格的按照順序建立索引，否則索引失效

小結(jié)

• 以上是適合建立索引的幾種情況，但是實(shí)際上是否會(huì)使用索引，還是由優(yōu)化起決定的，優(yōu)化器會(huì)根據(jù)具體的查詢以及數(shù)據(jù)量進(jìn)行分析決定的
• 當(dāng)建立了索引但是卻沒(méi)有使用的時(shí)候有可能是數(shù)據(jù)索引失效或者經(jīng)過(guò)優(yōu)化器分析沒(méi)有必要使用索引
• 建立了索引也是存在失效的可能，下面的文章關(guān)于索引失效的案例，可以一起學(xué)習(xí)討論：索引優(yōu)化：MySQL索引優(yōu)化之不適合構(gòu)建索引及索引失效的幾種情況詳解

到此這篇關(guān)于MySQL索引優(yōu)化之適合構(gòu)建索引的幾種情況詳解的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論