MySQL索引優(yōu)化之適合構(gòu)建索引的幾種情況詳解
結(jié)論
- 在where后面的過濾字段上建立索引(select/update/delete后面的where都是適用的),使用索引加快過濾效率,不用進行全表掃描
- 在具有唯一要求的字段上添加唯一索引,加快查詢效率,查到即可直接返回
- group by或者order by后面的字段添加索引,由于索引是排好序的,所以建立索引就等同于在查詢之前已經(jīng)是排好序了(這里需要注意建立的聯(lián)合索引建立中字段的順序,可以結(jié)合具體案例場景7進行學(xué)習(xí))
- 在DISTINCT(去重字段)后面的字段添加索引,由于建立了索引,那么相同的數(shù)據(jù)就是挨在一起的,所以就可以進行快速的去重操作,否則可能就需要將相同的數(shù)據(jù)找出來在進行去重操作
- 在多表連接join的時候在連接的字段上建立索引(小表驅(qū)動大表)
- 取字符串一定前綴建立索引(不是用整個字符串作為索引,否則將會占用太大的空間)
- 在頻繁使用的列上建立索引(可以建立聯(lián)合索引,同時最頻繁使用的字段應(yīng)該在聯(lián)合索引的最左側(cè),最左側(cè)原則)
- 在區(qū)分度高的列上建立索引(主鍵的區(qū)分度最高,因為所有的鍵都是唯一的)
建立索引的場景
場景一:在where字段后面的字段建立索引
-- 描述:當(dāng)where中有多個條件需要進行匹配的時候,那么可以創(chuàng)建聯(lián)合索引,這樣所有的條件都可以使用索引,大大提高了檢索的效率 select * from student_info where student_id = 1; -- 當(dāng)然數(shù)據(jù)量比較大的時候給where后面的字段添加索引 create index student_id_index on student_info (student_id)
未添加索引前,耗費0.383秒,基本遍歷整個表
添加索引后,耗費0.001秒,使用了索引(但是創(chuàng)建索引的時候會耗費一定時間)
在頻繁的查詢的業(yè)務(wù)中可以對where篩選的字段建立索引,如果where篩選的字段有多個還可以建立聯(lián)合索引
場景二:在具有唯一性約束的字段上建立唯一索引(查找到目標(biāo)即可返回不用繼續(xù)查找)
select * from student_info where id = 1001; -- 因為學(xué)號是唯一的,所以可以在學(xué)號這個字段上添加唯一所用 create index id_unique on student_info(id);
具有唯一性約束的字段上就可以建立唯一索引,雖然建立了唯一索引對insert操作有一定的影響(需要判斷新增的數(shù)據(jù)是否已經(jīng)在表中),但是建立唯一索引對于查詢的效率是顯著提升的,例如上面的例子,因為建立了唯一索引,一旦查找到id為1001的學(xué)生信息之后就不需要判斷數(shù)據(jù)庫中是否還有id等于1001的學(xué)生(只有唯一一份),直接返回信息即可,如果沒有建立索引,那么就需要全表掃描
場景三:經(jīng)常group by和order by的字段上建立索引(因為索引本身就是排好序的,相當(dāng)于查詢之前就已經(jīng)進行了排序)
select * from student_info order by name; -- 這里就可以給name字段進行索引的添加 select * from student_info group by class_id; -- 這里就可以給class_id字段添加索引
建立索引前,耗時0.501秒,使用的是所有數(shù)據(jù)在內(nèi)存中排序
建立索引后,耗時0.01秒
場景四:在DISTINCT后面的字段添加索引(索引已經(jīng)將相同的字段排好序,去重效率更高)
select distinct(student_id) from student_info; -- 這里就可以根據(jù)student_id字段建立索引 create index student_id_index on student_info;
建立了索引,那么默認(rèn)就是按照索引字段的升序排列的,那么相同值的字段也就排列在一起了,那么去重也就變得簡單、高效
場景五:在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ū)動小表的原則需要在student_info表的course_id字段上建立索引
沒有建立索引之前,耗時0.697s,沒有使索引
建立索引后,使用了索引,耗時0.003s
小表驅(qū)動大表:
通過對小表進行逐一遍歷,同時在大表中的連接字段建立索引即可加快查詢,本案例中,每次取出課程表中course_id和學(xué)生表中學(xué)生的course_id進行連接操作,在學(xué)生表中對course_id建立索引即可
場景六:使用字符串的前綴建立索引
create table shop(address varchar(120) not null); alter table shop add index(address(12)); --這里只是對表中的address的前12個字符建立了索引,而不是整個字符串建立索引
前綴建立索引的原因:
- 由于有些字符串很長,如果為整個字符串建立索引,那么索引將占用很大的空間
- 由于需要存儲整個字符串,那么數(shù)據(jù)項就會很大,那么索引樹的深度就會加深,檢索速度下降
- 雖然可能出現(xiàn)在索引中兩個字符串相同,但是再根據(jù)主鍵進行回表操作效率依然比較高
如何確定前綴索引中前綴的長度呢?(也就是如果前綴的長度太短,那么索引的區(qū)分度就很低,從多個字符串截取的前綴數(shù)據(jù)可能都是一樣的,但是如果前綴索引的前綴過長,那么前綴索引的優(yōu)點就消失了)
引入了區(qū)別度的概念,select count(distinct left(索引字段,前綴索引長度) / count(*) from xxx),該值越接近1,那么區(qū)分度就越明顯,那么該索引長度就是所求的前綴索引長度
場景七:在頻繁使用的列上建立索引或聯(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'; -- 在這里實際上就可以建立age,sex,password的聯(lián)合索引,只需要建立一個索引,這三個查詢都是可以使用的 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在后,因為在select語句中執(zhí)行的順序是先group by 之后才是 order by 索引如果索引的字段順序是相反的,那么就無法使用索引 create index class_id_name_index on student(class_id,name);
索引建立需要符合順序的原因:
索引字段的順序如果是錯誤的,那么索引就會失效,因為索引實際上是排好序的,如果索引建立的時候是現(xiàn)根據(jù)name排好序之后在根據(jù)class_id進行排序,那么在面對需要先根據(jù)class_id排序再根據(jù)name排序的業(yè)務(wù)就無法進行使用
補充:
在select * from xxx where age = 19 and sex = ‘man’ and password = '123456’這里索引建立的順序不一定是(age,sex,password)因為在實際執(zhí)行的過程中,優(yōu)化器會優(yōu)化執(zhí)行步驟會按照索引的順序進行查詢,但是group by 和 order by的執(zhí)行順序是無法改變的,索引必須嚴(yán)格的按照順序建立索引,否則索引失效
小結(jié)
- 以上是適合建立索引的幾種情況,但是實際上是否會使用索引,還是由優(yōu)化起決定的,優(yōu)化器會根據(jù)具體的查詢以及數(shù)據(jù)量進行分析決定的
- 當(dāng)建立了索引但是卻沒有使用的時候有可能是數(shù)據(jù)索引失效或者經(jīng)過優(yōu)化器分析沒有必要使用索引
- 建立了索引也是存在失效的可能,下面的文章關(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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Linux下MySQL5.7.18 yum方式從卸載到安裝過程圖解
這篇文章主要介紹了Linux下MySQL5.7.18 yum方式從卸載到安裝過程圖解,需要的朋友可以參考下2017-06-06Linux操作系統(tǒng)操作MySQL常用命令小結(jié)
本文給大家分享Linux操作系統(tǒng)操作MySQL常用命令小結(jié),需要的朋友參考下吧2017-07-07MySQL數(shù)據(jù)庫分區(qū)功能的使用教程
這篇文章主要介紹了MySQL數(shù)據(jù)庫分區(qū)功能的使用教程,文中特別講解了MySQL分表和分區(qū)的區(qū)別以及聯(lián)系,需要的朋友可以參考下2016-05-05詳細介紹基于MySQL的搜索引擎MySQL-Fullltext
這篇文章主要詳細介紹基于MySQL的搜索引擎MySQL-Fullltext,需要用到C和C#以及JavaScript的知識,屬于MySQL的高階應(yīng)用,需要的朋友可以參考下2015-04-04