一文弄懂MySQL索引建立原則

一、適合建立索引

1、欄位的數值有唯一性限制

根據Alibaba規範，指明在業務上具有唯一特性的欄位，即使是組合欄位，也必須建成唯一索引。

例如，學生表中的學號時具有唯一性的欄位，為該欄位建立唯一性索引可以快速查詢出某個學生的資訊，如果使用姓名的話，可能存在同名的情況，從而降低查詢速度。

2、頻繁作為Where查詢條件的欄位

某個欄位在Select語句的Where條件中經常被使用到，那麼就需要給這個欄位建立索引，尤其實在資料量大的情況下，建立普通索引就可以大幅提升查詢效率。

比如測試表student_info有100萬資料，假設查詢student_id=112322的使用者資訊，如果沒有對student_id欄位建立索引，查詢結果如下：

select course_id, class_id, name, create_time,student_id from student_info where student_id = 112322;# 花費211ms

為student_id建立索引後，查詢結果如下：

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;# 花費3ms

3、經常Group by和Order by的列

索引就是讓資料按照某種順序進行儲存或檢索，因此當使用Group by對資料進行分組查詢或使用Order by對資料進行排序的時候 ，就需要對分組或排序的欄位進行索引。如果待排序的列有多個，那可以在這些列上建立組合索引。

比如，按照student_id對學生選秀的課程進行分組，顯示不同的student_id和課程的數量，顯示100條。如果不對student_id建立索引，查詢結果如下：

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

為student_id建立索引後，查詢結果如下：

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;#花費6ms

對於既有group by又有order by的查詢語句，建議最好建立聯合索引，並且將group by中的欄位放到order by欄位的前邊，滿足‘最左字首匹配原則’，這樣索引的利用率就會高，自然查詢的效率也就會高；同時8.0之後的版本支援降序索引，如果order by之後的欄位時降序的，可以考慮直接建立降序索引，也會提高查詢效率。

4、Update、Delete的where條件列

對資料按照某個條件進行查詢後再進行Update或Delete的操作，如果對Where欄位建立了索引，就能答覆提升效率。原因是因為需要先根據Where條件列檢索出來這條記錄，然後再對他進行更新或刪除。如果進行更新的時候，更新的欄位是非索引欄位，提升效率會更明顯，這是因為費索引欄位更新不需要對所以進行維護。

比如對student_info表中的name欄位為sdfasdfas123123的資料修改student_id為110119，在沒有對name欄位建立索引的情況下，執行情況如下：

update student_info set student_id = 110119 where name = 'sdfasdfas123123';#花費549ms

新增索引後，執行情況如下：

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

5、Distinct欄位需要建立索引

有時候需要對某個欄位進行去重，使用Distinct，那麼對這個建立索引也會提升查詢效率。

比如查詢課程表中不同student_id都有哪些，如果沒有為student_id建立索引，執行情況如下：

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

建立索引後，執行情況如下：

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

6、多表Join連線操作時，建立索引注意事項

首先，連線表的資料量儘量不超過3張，因為每增加一張表就相當於增加了一次巢狀的迴圈，數量級增長非常快，嚴重影響查詢效率。其次，對Where條件建立索引，因為Where才是對資料條件的過濾，如果再資料量非常大的情況下，沒有Where條件過濾時非常可怕的，最後，對於連線的欄位建立索引，並且改欄位再多張表中型別必須一致。

比如，只對student_id建立索引，查詢結果如下：

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'; #花費176ms

給name欄位建立索引後，查詢結果如下：

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'; #花費2ms

7、使用列的型別小的建立索引

這裡所說的型別小值意思是該型別表示的資料範圍的大小。比如在定義表結構的時候要顯示的指定列的型別，以整數型別為例，有TINYINT、MEDIUMINT、INT、BIGINT等，他們佔用的儲存空間依次遞增，能表示的資料範圍也是一次遞增。如果相對某個整數列建立索引的話，在表示的整數範圍允許的情況下，儘量讓索引列使用較小的型別，例如能使用INT不要使用BIGINT，能使用MEDIUMINT不使用INT，原因如下：

• 資料型別越小，在查詢時進行的比較操作越快
• 資料型別越小，索引佔用的空間就越少，在一個資料頁內就可以存下更多的記錄，從而減少磁碟I/O帶來的效能損耗，也就意味著可以儲存更多的資料在資料頁中，提高讀寫效率。

上述對於主鍵來說很合適，因為在聚簇索引中既儲存了資料，也儲存了索引，可以很好的減少磁碟I/O；而對於二級索引來說，還需要一次回表操作才能查到完整的資料，也就能加了一次磁碟I/O。

8、使用字串字首建立索引

根據Alibaba開發手冊，在字串上建立索引時，必須指定索引長度，沒有必要對全欄位建立索引。

比如有一張商品表，表中的商品描述欄位較長，在描述欄位上建立字首索引如下：

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

區分度的計算可以使用count(distinct left(列名, 索引長度))/count(*)來確定。

9、區分度高的列適合作為索引

列的基數值得時某一列中不重複資料的個數，比如說某個列包含值2，5，3，6，2，7，2，雖然有7條記錄，但該列的基數卻是5，也就是說，在記錄行數一定的情況下，列的基數越大，該列中的值就越分散；列的基數越小，該列中的值就越集中。這裡列的基數指標非常重要，直接影響是否能有效利用索引。最好為列的基數大的列建立索引，為基數太小的列建立索引效果反而不好。

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

10、使用最頻繁的列放到聯合索引的左側

這條就是通常說的最左字首匹配原則。 通俗來講就是將Where條件後經常使用的條件欄位放在索引的最左邊，將使用頻率相對低的放到右邊。

11、在多個欄位都要建立索引的情況下，聯合索引由於單值索引

二、不適合建立索引

1、在where中使用不到的欄位不要設定索引

通常索引的建立是有代價的，如果建立索引的欄位沒有出現在where條件（包括group by、order by)中，建議一開始就不要建立索引或將索引刪除，因為索引的存在也會佔用空間。

2、資料量小的表最好不要使用索引

3、有大量重複資料的列上不要建立索引

在條件表示式中經常用到的不同值較多的列上建立索引，但欄位中如果有大量重複資料，也不用建立索引。比如學生表中的性別欄位，只有男和女兩種值，因此無需建立索引。如果建立索引，不但不會提高查詢效率，反而會嚴重降低資料更新速度。

4、避免對經常更新的表建立過多的索引

• 頻繁更新的欄位不一定要建立索引，因為更新資料的時候，索引也要跟著更新，如果索引太多，更新的時候會造成伺服器壓力，從而影響效率。
• 避免對經常更新的表建立過多的索引，並且索引中的列儘可能少。此時雖然提高了查詢速度，同時也會降低更新表的速度。

5、不建議用無序的值作為索引

例如身份證、UUID（在索引比較時需要轉為ASCII，並且插入時可能造成頁分裂）、MD5、HASH、無序長字串等。

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

表中的資料被大量更新或者資料的使用方式被改變後，原有的一些索引可能不會被使用到。DBA應定期找出這些索引並將之刪除，從而較少無用索引對更新操作的影響。

7、不要定義冗餘或重複的索引

例如身份證、UUID（在索引比較時需要轉為ASCII，並且插入時可能造成頁分裂）、MD5、HASH、無序長字串等。

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

表中的資料被大量更新或者資料的使用方式被改變後，原有的一些索引可能不會被使用到。DBA應定期找出這些索引並將之刪除，從而較少無用索引對更新操作的影響。

9、不要定義冗餘或重複的索引

總結

到此這篇關於MySQL索引建立原則的文章就介紹到這了,更多相關MySQL索引建立原則內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！