MySQL索引創(chuàng)建原則的示例分析

小編給大家分享一下MySQL索引創(chuàng)建原則的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

一、適合創(chuàng)建索引

1、字段的數(shù)值有唯一性限制

根據(jù)Alibaba規(guī)范，指明在業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。

例如，學(xué)生表中的學(xué)號(hào)時(shí)具有唯一性的字段，為該字段建立唯一性索引可以快速查詢(xún)出某個(gè)學(xué)生的信息，如果使用姓名的話(huà)，可能存在同名的情況，從而降低查詢(xún)速度。

2、頻繁作為Where查詢(xún)條件的字段

某個(gè)字段在Select語(yǔ)句的Where條件中經(jīng)常被使用到，那么就需要給這個(gè)字段創(chuàng)建索引，尤其實(shí)在數(shù)據(jù)量大的情況下，創(chuàng)建普通索引就可以大幅提升查詢(xún)效率。

比如測(cè)試表student_info有100萬(wàn)數(shù)據(jù)，假設(shè)查詢(xún)student_id=112322的用戶(hù)信息，如果沒(méi)有對(duì)student_id字段創(chuàng)建索引，查詢(xún)結(jié)果如下：

select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花費(fèi)211ms

為student_id創(chuàng)建索引后，查詢(xún)結(jié)果如下：

alter table student_info add index idx_sid(student_id);
select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花費(fèi)3ms

3、經(jīng)常Group by和Order by的列

索引就是讓數(shù)據(jù)按照某種順序進(jìn)行存儲(chǔ)或檢索，因此當(dāng)使用Group by對(duì)數(shù)據(jù)進(jìn)行分組查詢(xún)或使用Order by對(duì)數(shù)據(jù)進(jìn)行排序的時(shí)候 ，就需要對(duì)分組或排序的字段進(jìn)行索引。如果待排序的列有多個(gè)，那可以在這些列上建立組合索引。

比如，按照student_id對(duì)學(xué)生選秀的課程進(jìn)行分組，顯示不同的student_id和課程的數(shù)量，顯示100條。如果不對(duì)student_id創(chuàng)建索引，查詢(xún)結(jié)果如下：

select student_id,count(*) as num from student_info group by student_id limit 100;#花費(fèi)2.466s

為student_id創(chuàng)建索引后，查詢(xún)結(jié)果如下：

alter table student_info add index idx_sid(student_id);
select student_id,count(*) as num from student_info group by student_id limit 100;#花費(fèi)6ms

對(duì)于既有g(shù)roup by又有order by的查詢(xún)語(yǔ)句，建議最好建立聯(lián)合索引，并且將group by中的字段放到order by字段的前邊，滿(mǎn)足‘最左前綴匹配原則’，這樣索引的利用率就會(huì)高，自然查詢(xún)的效率也就會(huì)高；同時(shí)8.0之后的版本支持降序索引，如果order by之后的字段時(shí)降序的，可以考慮直接創(chuàng)建降序索引，也會(huì)提高查詢(xún)效率。

4、Update、Delete的where條件列

對(duì)數(shù)據(jù)按照某個(gè)條件進(jìn)行查詢(xún)后再進(jìn)行Update或Delete的操作，如果對(duì)Where字段創(chuàng)建了索引，就能答復(fù)提升效率。原因是因?yàn)樾枰雀鶕?jù)Where條件列檢索出來(lái)這條記錄，然后再對(duì)他進(jìn)行更新或刪除。如果進(jìn)行更新的時(shí)候，更新的字段是非索引字段，提升效率會(huì)更明顯，這是因?yàn)橘M(fèi)索引字段更新不需要對(duì)所以進(jìn)行維護(hù)。

比如對(duì)student_info表中的name字段為sdfasdfas123123的數(shù)據(jù)修改student_id為110119，在沒(méi)有對(duì)name字段建立索引的情況下，執(zhí)行情況如下：

update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花費(fèi)549ms

添加索引后，執(zhí)行情況如下：

alter table student_info add index idx_name(name);
update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花費(fèi)2ms

5、Distinct字段需要?jiǎng)?chuàng)建索引

有時(shí)候需要對(duì)某個(gè)字段進(jìn)行去重，使用Distinct，那么對(duì)這個(gè)創(chuàng)建索引也會(huì)提升查詢(xún)效率。

比如查詢(xún)課程表中不同student_id都有哪些，如果沒(méi)有為student_id創(chuàng)建索引，執(zhí)行情況如下：

select distinct(student_id) from student_id;#花費(fèi)2ms

創(chuàng)建索引后，執(zhí)行情況如下：

alter table student_info add index idx_sid(student_id);
select distinct(student_id) from student_id;#花費(fèi)0.1ms

6、多表Join連接操作時(shí)，創(chuàng)建索引注意事項(xiàng)

首先，連接表的數(shù)據(jù)量盡量不超過(guò)3張，因?yàn)槊吭黾右粡埍砭拖喈?dāng)于增加了一次嵌套的循環(huán)，數(shù)量級(jí)增長(zhǎng)非?？?，嚴(yán)重影響查詢(xún)效率。其次，對(duì)Where條件創(chuàng)建索引，因?yàn)閃here才是對(duì)數(shù)據(jù)條件的過(guò)濾，如果再數(shù)據(jù)量非常大的情況下，沒(méi)有Where條件過(guò)濾時(shí)非?？膳碌?，最后，對(duì)于連接的字段創(chuàng)建索引，并且改字段再多張表中類(lèi)型必須一致。

比如，只對(duì)student_id創(chuàng)建索引，查詢(xún)結(jié)果如下：

select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花費(fèi)176ms

給name字段創(chuàng)建索引后，查詢(xún)結(jié)果如下：

alter table student_info add index idx_name(name);
select course_id, name, student_info.student_id,course_name
from student_info join course
on student_info.course_id = course.course_id
where name = 'aAAaAA'; #花費(fèi)2ms

7、使用列的類(lèi)型小的創(chuàng)建索引

這里所說(shuō)的類(lèi)型小值意思是該類(lèi)型表示的數(shù)據(jù)范圍的大小。比如在定義表結(jié)構(gòu)的時(shí)候要顯示的指定列的類(lèi)型，以整數(shù)類(lèi)型為例，有TINYINT、MEDIUMINT、INT、BIGINT等，他們占用的存儲(chǔ)空間依次遞增，能表示的數(shù)據(jù)范圍也是一次遞增。如果相對(duì)某個(gè)整數(shù)列建立索引的話(huà)，在表示的整數(shù)范圍允許的情況下，盡量讓索引列使用較小的類(lèi)型，例如能使用INT不要使用BIGINT，能使用MEDIUMINT不使用INT，原因如下：

• 數(shù)據(jù)類(lèi)型越小，在查詢(xún)時(shí)進(jìn)行的比較操作越快

• 數(shù)據(jù)類(lèi)型越小，索引占用的空間就越少，在一個(gè)數(shù)據(jù)頁(yè)內(nèi)就可以存下更多的記錄，從而減少磁盤(pán)I/O帶來(lái)的性能損耗，也就意味著可以存儲(chǔ)更多的數(shù)據(jù)在數(shù)據(jù)頁(yè)中，提高讀寫(xiě)效率。

上述對(duì)于主鍵來(lái)說(shuō)很合適，因?yàn)樵诰鄞厮饕屑却鎯?chǔ)了數(shù)據(jù)，也存儲(chǔ)了索引，可以很好的減少磁盤(pán)I/O；而對(duì)于二級(jí)索引來(lái)說(shuō)，還需要一次回表操作才能查到完整的數(shù)據(jù)，也就能加了一次磁盤(pán)I/O。

8、使用字符串前綴創(chuàng)建索引

根據(jù)Alibaba開(kāi)發(fā)手冊(cè)，在字符串上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)有必要對(duì)全字段建立索引。

比如有一張商品表，表中的商品描述字段較長(zhǎng)，在描述字段上建立前綴索引如下：

create table product(id int, desc varchar(120) not null);
alter table product add index(desc(12));

區(qū)分度的計(jì)算可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)來(lái)確定。

9、區(qū)分度高的列適合作為索引

列的基數(shù)值得時(shí)某一列中不重復(fù)數(shù)據(jù)的個(gè)數(shù)，比如說(shuō)某個(gè)列包含值2，5，3，6，2，7，2，雖然有7條記錄，但該列的基數(shù)卻是5，也就是說(shuō)，在記錄行數(shù)一定的情況下，列的基數(shù)越大，該列中的值就越分散；列的基數(shù)越小，該列中的值就越集中。這里列的基數(shù)指標(biāo)非常重要，直接影響是否能有效利用索引。最好為列的基數(shù)大的列建立索引，為基數(shù)太小的列建立索引效果反而不好。

可以使用公式select count(distinct col)/count(*) from table 來(lái)計(jì)算區(qū)分度，越接近1區(qū)分度越好。

10、使用最頻繁的列放到聯(lián)合索引的左側(cè)

這條就是通常說(shuō)的最左前綴匹配原則。 通俗來(lái)講就是將Where條件后經(jīng)常使用的條件字段放在索引的最左邊，將使用頻率相對(duì)低的放到右邊。

11、在多個(gè)字段都要?jiǎng)?chuàng)建索引的情況下，聯(lián)合索引由于單值索引

二、不適合創(chuàng)建索引

1、在where中使用不到的字段不要設(shè)置索引

通常索引的建立是有代價(jià)的，如果建立索引的字段沒(méi)有出現(xiàn)在where條件（包括group by、order by)中，建議一開(kāi)始就不要?jiǎng)?chuàng)建索引或?qū)⑺饕齽h除，因?yàn)樗饕拇嬖谝矔?huì)占用空間。

2、數(shù)據(jù)量小的表最好不要使用索引

3、有大量重復(fù)數(shù)據(jù)的列上不要建立索引

在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引，但字段中如果有大量重復(fù)數(shù)據(jù)，也不用創(chuàng)建索引。比如學(xué)生表中的性別字段，只有男和女兩種值，因此無(wú)需建立索引。如果建立索引，不但不會(huì)提高查詢(xún)效率，反而會(huì)嚴(yán)重降低數(shù)據(jù)更新速度。

4、避免對(duì)經(jīng)常更新的表創(chuàng)建過(guò)多的索引

• 頻繁更新的字段不一定要?jiǎng)?chuàng)建索引，因?yàn)楦聰?shù)據(jù)的時(shí)候，索引也要跟著更新，如果索引太多，更新的時(shí)候會(huì)造成服務(wù)器壓力，從而影響效率。

• 避免對(duì)經(jīng)常更新的表創(chuàng)建過(guò)多的索引，并且索引中的列盡可能少。此時(shí)雖然提高了查詢(xún)速度，同時(shí)也會(huì)降低更新表的速度。

5、不建議用無(wú)序的值作為索引

例如身份證、UUID（在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁(yè)分裂）、MD5、HASH、無(wú)序長(zhǎng)字符串等。

6、刪除不在使用或很少使用的索引

表中的數(shù)據(jù)被大量更新或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不會(huì)被使用到。DBA應(yīng)定期找出這些索引并將之刪除，從而較少無(wú)用索引對(duì)更新操作的影響。

7、不要定義冗余或重復(fù)的索引

例如身份證、UUID（在索引比較時(shí)需要轉(zhuǎn)為ASCII，并且插入時(shí)可能造成頁(yè)分裂）、MD5、HASH、無(wú)序長(zhǎng)字符串等。

8、刪除不在使用或很少使用的索引

表中的數(shù)據(jù)被大量更新或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不會(huì)被使用到。DBA應(yīng)定期找出這些索引并將之刪除，從而較少無(wú)用索引對(duì)更新操作的影響。

9、不要定義冗余或重復(fù)的索引

以上是“MySQL索引創(chuàng)建原則的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！