怎樣學習MySQL索引原理？自己總結的索引心得

索引是什嗎？百度百科是這樣描述的:索引是為來加速對錶中資料行中的檢索而建立的一種分散的資料結果,時針對錶而建立的，它是由資料頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指標,以便加速檢索物理資料。MySQL索引原理如何學習，這篇文章將詳細解釋。

摘要： 就一起來聊一聊MySQL索引。 什麼是索引？ 百度百科是這樣描述的: 索引是為來加速對錶中資料行中的檢索而建立的一種分散的資料結果,時針對錶而建立的，它是由資料頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指標,以便加速檢索物理資料 其實，索引的概念大家都很清楚，也知道索引能夠提升查詢效率，但大部分童鞋在怎麼建索引，建在哪些欄位上有以下常見誤解: 建立表時不需要建索引,後續才添加索引 where條件後的欄位均建索引 簡單SQL不需要索引,聯集查詢才需要索引 聯合索引的順序是where條件後欄位的先後順序 對於區分度小的欄位上也建立索引,如狀態,性別等欄位等。

就一起來聊一聊MySQL索引。

什麼是索引？

百度百科是這樣描述的:

索引是為來加速對錶中資料行中的檢索而建立的一種分散的資料結果,時針對錶而建立的，它是由資料頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指標,以便加速檢索物理資料

其實，索引的概念大家都很清楚，也知道索引能夠提升查詢效率，但大部分童鞋在怎麼建索引，建在哪些欄位上有以下常見誤解:

建立表時不需要建索引,後續才添加索引

where條件後的欄位均建索引

簡單SQL不需要索引,聯集查詢才需要索引

聯合索引的順序是where條件後欄位的先後順序

對於區分度小的欄位上也建立索引,如狀態,性別等欄位等。

索引區分度

在說上述問題之前,我們先來看看另一個概念,就是區分度。

區分度: 指欄位在資料庫中的不重複比

區分度在建立索引時有著非常重要的參考價值,在MySQL中,區分度的計算規則如下:

欄位去重後的總數與全表總記錄數的商。

例如:

select count(distinct(name))/count(*) from t_base_user;

結果如下:

count(distinct(name))/count(*)
1.0000

其中區分度最大值為1.000,最小為0.0000,區分度的值越大,也就是資料不重複率越大，建立索引效果也越好,在主鍵以及唯一鍵上面的區分度是最高的,為1.0000。在狀態,性別等欄位上面的區分度值是最小的。 (這個就要看資料量了,如果只有幾條資料,這時區分度還挺高的,如果資料量多,區分度基本為0.0000。也就是在這些欄位上添加索引後,效果也不佳的原因。)

值得注意的是: 如果表中沒有任何記錄時,計算區分度的結果是為空白值，其他情況下,區分度值均分布在0.0000-1.0000之間。

如何建索引

(一) : 區分度
個人強烈建議, 建索引時,一定要先計算該欄位的區分度,原因如下:

1. 單列索引
可以查看該欄位的區分度,根據區分度的大小,也能大概知道在該欄位上的建立索引是否有效，以及效果如何。區分度越大,索引效果越明顯。

2.多列索引(聯合索引)
多列索引中其實還有一個欄位的先後順序問題,一般是將區分度較高的放在前面,這樣聯合索引才更有效,例如:

select * from t_base_user where name="" and status=1;

像上述語句,如果建聯合索引的話,就應該是:

alter table t_base_user add index idx_name_status(name,status);

而不是:

alter table t_base_user add index idx_status_name(status,name)；

(二) 最左首碼匹配原則
MySQL會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如

select * from t_base_user where type="10" and created_at<"2017-11-03" and status=1, (該語句僅作為示範)

在上述語句中,status就不會走索引,因為遇到<時,MySQL已經停止匹配,此時走的索引為:(type,created_at),其先後順序是可以調整的,而走不到status索引,此時需要修改語句為:

select * from t_base_user where type=10 and status=1 and created_at<"2017-11-03"

即可走status索引。

(三) 函數運算
不要在索引列上,進行函數運算,否則索引會失效。因為b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。

(四) 擴充優先
擴充優先,不要建立索引,盡量在已有索引中修改。如下:

select * from t_base_user where name="andyqian" and email="andytohome"

在表t_base_user表中已經存在idx_name索引,如果需要加入idx_name_email的索引,應該是修改idx_name索引,而不是建立一個索引。

誤解糾正

上面說了,如何建立索引,現在我們就可以來回答,在第一步中存在的誤解了。

誤解一: 建立表時不需要建立索引,後續才加索引

答: 一個好的資料表設計,在一開始就要考慮索引的建立,而不是等到後續出問題了,影響業務使用了,才建立索引來救場,而且後續建立索引的成本也相對高很多。(這就是給生產事故留下生根發芽的機會呀)

誤解二: where條件後的欄位均建索引

答: 這個誤解比較常見,但where條件後的欄位不需要全部建立索引,過多的索引,也會導致索引檔案劇增,也還達不到期望中的效果。詳細請參考上述建立索引的小節。

誤解三: 簡單SQL不需要建立索引,聯集查詢采建立索引

答: 這個誤解就得好好說說了,現在互連網公司特別是B/S架構下,商務邏輯均剝離在代碼邏輯層,到最後SQL層面,其實都是一些簡單的SQL,只有些許串連查詢,更多的還是單表操作,(C/S架構中有很多在SQL層面的寫邏輯的)，你說這些語句簡不簡單。

誤解四: 聯合索引的順序是where條件後欄位的先後順序

答: 我們剛才說過,聯合索引的順序,是根據最左首碼原則,以及區分度來區分的，和where條件後欄位的先後順序無關。

誤解五： 對於區分度較小的欄位建立索引

答: 在區分度較小的欄位上建立索引,基本無效,還會增加大量的索引檔案,你說是不是得不償失。

索引重不重要？

上面介紹了MySQL索引的概念，建立索引時的一些技巧。這麼理論的東西，對於平時沒有使用或使用比較少的童鞋，此時對索引的重要性可能還沒那麼直觀，那麼，我就來說說我在索引上吃過的虧，踩過的坑！同時也是未建索引常見問題！

0. 導致慢查詢
這個問題可是未建索引的常客哦，（這裡也還有很多細節呢，如: 隱式類型轉換等等）

1. 導致服務逾時

情境 :
在某次上線時，作為服務提供者，提供服務給業務方使用。一開始以為就提供一個簡單的服務，也已經測試完成，心裡還在竊喜，今天總算可以早早回家了！

描述 :
實際一上線，在生產環境中導致業務方請求調用時，而且每次請求均逾時，資料也已落地，此時只能review代碼，最後發現生產中有個慢查詢導致，活活的花費了10多秒，這個語句有多簡單呢，你絕對想不到，其實就是一個單表的where條件查詢語句。你說這種原因導致服務不可用，你說冤不冤，氣不氣！（這也是我為什麼說，一個好的資料表設計，從一開始就要考慮建立索引了）。

2. 資料庫伺服器CPU 100%

在查詢頻率比較高的SQL上，如果由於未建索引，導致慢查詢的話，那可是會導致資料庫伺服器CPU 100%，影響可是整個系統哦。

小結
上面說了好幾類，由於沒建立索引而導致的問題，輕則導致慢查詢，影響系統效率，重則，導致CPU 100%，影響整個系統的使用，看到這裡，你說索引重不重要？

最後

上面簡單說了，索引是什嗎？有什麼用,以及建立索引時的一些技巧，還著重說了，索引的重要性。那麼索引這麼重要,在平時編碼時如何避免呢？以下是我個人的建議:

1.在建表時就應該考慮添加索引，如：外鍵欄位,等等。

2.在寫完SQL後,一定要查看執行計畫。盡量避免全表掃描。

3.如果是已有表中添加索引,一定要先計算該欄位的區分度。

4.聯合索引，將區分度大放在前面。

5.遵從MySQL左列首碼優先原則\

[2]H. Berenson, P. Bernstein, J. Gray, J.Melton, E. O’Neil,and P. O’Neil. A critique of ANSI SQL isolation levels. InProceedings of the SIGMOD International Conference on Management of Data, pages1–10, May 1995.

[3]Michael J. Cahill, Uwe Röhm, and Alan D.Fekete. 2008. Serializable isolation for snapshot databases. In SIGMOD ’08:Proceedings of the 2008 ACM SIGMOD international conference on Management of data, pages 729–738, New York, NY, USA. ACM.[4]Michael James Cahill. 2009. Serializable Isolation for Snapshot Databases. Sydney Digital Theses. University of Sydney, School of Information Technologies[5] A. Fekete, D. Liarokapis, E. O’Neil, P.O’Neil, andD. Shasha. Making snapshot isolation serializable. www.codexueyuan.com In ACM transactions on database systems, volume 39(2), pages 492–528, June 2005.

相關文章:

mysql索引--(mysql學習二)_MySQL

Mysql-索引學習（一）_MySQL

相關視頻：

索引簡單介紹-六天帶你玩轉MySQL視頻教程