MySQL索引原理是什么

這篇文章主要介紹MySQL索引原理是什么，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

我們提供的服務(wù)有：網(wǎng)站設(shè)計、網(wǎng)站制作、微信公眾號開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、安丘ssl等。為上1000家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的安丘網(wǎng)站制作公司

摘要： 就一起來聊一聊MySQL索引。 什么是索引？ 百度百科是這樣描述的: 索引是為來加速對表中數(shù)據(jù)行中的檢索而創(chuàng)建的一種分散的數(shù)據(jù)結(jié)果,時針對表而建立的，它是由數(shù)據(jù)頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指針,以便加速檢索物理數(shù)據(jù) 其實，索引的概念大家都很清楚，也知道索引能夠提升查詢效率，但大部分童鞋在怎么建索引，建在哪些字段上有以下常見誤解: 新建表時不需要建索引,后續(xù)才添加索引 where條件后的字段均建索引 簡單SQL不需要索引,聯(lián)合查詢才需要索引 聯(lián)合索引的順序是where條件后字段的先后順序 對于區(qū)分度小的字段上也新建索引,如狀態(tài),性別等字段等。

就一起來聊一聊MySQL索引。

什么是索引？

百度百科是這樣描述的:

索引是為來加速對表中數(shù)據(jù)行中的檢索而創(chuàng)建的一種分散的數(shù)據(jù)結(jié)果,時針對表而建立的，它是由數(shù)據(jù)頁面以外的索引頁面組成,每個索引頁中的行都含有邏輯指針,以便加速檢索物理數(shù)據(jù)

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

新建表時不需要建索引,后續(xù)才添加索引

where條件后的字段均建索引

簡單SQL不需要索引,聯(lián)合查詢才需要索引

聯(lián)合索引的順序是where條件后字段的先后順序

對于區(qū)分度小的字段上也新建索引,如狀態(tài),性別等字段等。

索引區(qū)分度

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

區(qū)分度: 指字段在數(shù)據(jù)庫中的不重復(fù)比

區(qū)分度在新建索引時有著非常重要的參考價值,在MySQL中,區(qū)分度的計算規(guī)則如下:

字段去重后的總數(shù)與全表總記錄數(shù)的商。

例如:

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

結(jié)果如下:

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

其中區(qū)分度最大值為1.000,最小為0.0000,區(qū)分度的值越大,也就是數(shù)據(jù)不重復(fù)率越大，新建索引效果也越好,在主鍵以及唯一鍵上面的區(qū)分度是最高的,為1.0000。在狀態(tài),性別等字段上面的區(qū)分度值是最小的。 (這個就要看數(shù)據(jù)量了,如果只有幾條數(shù)據(jù),這時區(qū)分度還挺高的,如果數(shù)據(jù)量多,區(qū)分度基本為0.0000。也就是在這些字段上添加索引后,效果也不佳的原因。)

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

如何建索引

(一)  : 區(qū)分度
個人強(qiáng)烈建議, 建索引時,一定要先計算該字段的區(qū)分度,原因如下:

1. 單列索引
可以查看該字段的區(qū)分度,根據(jù)區(qū)分度的大小,也能大概知道在該字段上的新建索引是否有效，以及效果如何。區(qū)分度越大,索引效果越明顯。

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

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

像上述語句,如果建聯(lián)合索引的話,就應(yīng)該是:

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已經(jīng)停止匹配,此時走的索引為:(type,created_at),其先后順序是可以調(diào)整的,而走不到status索引,此時需要修改語句為:

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

即可走status索引。

(三) 函數(shù)運(yùn)算
 不要在索引列上,進(jìn)行函數(shù)運(yùn)算,否則索引會失效。因為b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。

(四) 擴(kuò)展優(yōu)先
 擴(kuò)展優(yōu)先,不要新建索引,盡量在已有索引中修改。如下:

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

在表t_base_user表中已經(jīng)存在idx_name索引,如果需要加入idx_name_email的索引,應(yīng)該是修改idx_name索引,而不是新建一個索引。

誤解糾正

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

誤解一:   新建表時不需要建立索引,后續(xù)才加索引

答: 一個好的數(shù)據(jù)表設(shè)計,在一開始就要考慮索引的創(chuàng)建,而不是等到后續(xù)出問題了,影響業(yè)務(wù)使用了,才新建索引來救場,而且后續(xù)創(chuàng)建索引的成本也相對高很多。(這就是給生產(chǎn)事故留下生根發(fā)芽的機(jī)會呀)

誤解二:   where條件后的字段均建索引

答: 這個誤解比較常見,但where條件后的字段不需要全部建立索引,過多的索引,也會導(dǎo)致索引文件劇增,也還達(dá)不到期望中的效果。詳細(xì)請參考上述新建索引的小節(jié)。

誤解三:   簡單SQL不需要建立索引,聯(lián)合查詢采建立索引

答: 這個誤解就得好好說說了,現(xiàn)在互聯(lián)網(wǎng)公司特別是B/S架構(gòu)下,業(yè)務(wù)邏輯均剝離在代碼邏輯層,到最后SQL層面,其實都是一些簡單的SQL,只有些許連接查詢,更多的還是單表操作,(C/S架構(gòu)中有很多在SQL層面的寫邏輯的)，你說這些語句簡不簡單。

誤解四:    聯(lián)合索引的順序是where條件后字段的先后順序

答: 我們剛才說過,聯(lián)合索引的順序,是根據(jù)最左前綴原則,以及區(qū)分度來區(qū)分的，和where條件后字段的先后順序無關(guān)。

誤解五： 對于區(qū)分度較小的字段新建索引

答: 在區(qū)分度較小的字段上新建索引,基本無效,還會增加大量的索引文件,你說是不是得不償失。

索引重不重要？

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

0. 導(dǎo)致慢查詢
這個問題可是未建索引的?？团?，（這里也還有很多細(xì)節(jié)呢，如: 隱式類型轉(zhuǎn)換等等）

1. 導(dǎo)致服務(wù)超時

場景 :
 在某次上線時，作為服務(wù)提供者，提供服務(wù)給業(yè)務(wù)方使用。一開始以為就提供一個簡單的服務(wù)，也已經(jīng)測試完成，心里還在竊喜，今天總算可以早早回家了！

描述 :
 實際一上線，在生產(chǎn)環(huán)境中導(dǎo)致業(yè)務(wù)方請求調(diào)用時，而且每次請求均超時，數(shù)據(jù)也已落地，此時只能review代碼，最后發(fā)現(xiàn)生產(chǎn)中有個慢查詢導(dǎo)致，活活的花費(fèi)了10多秒，這個語句有多簡單呢，你絕對想不到，其實就是一個單表的where條件查詢語句。你說這種原因?qū)е路?wù)不可用，你說冤不冤，氣不氣?。ㄟ@也是我為什么說，一個好的數(shù)據(jù)表設(shè)計，從一開始就要考慮新建索引了）。

2. 數(shù)據(jù)庫服務(wù)器CPU 100%

在查詢頻率比較高的SQL上，如果由于未建索引，導(dǎo)致慢查詢的話，那可是會導(dǎo)致數(shù)據(jù)庫服務(wù)器CPU 100%，影響可是整個系統(tǒng)哦。

小結(jié)
上面說了好幾類，由于沒建立索引而導(dǎo)致的問題，輕則導(dǎo)致慢查詢，影響系統(tǒng)效率，重則，導(dǎo)致CPU 100%，影響整個系統(tǒng)的使用，看到這里，你說索引重不重要？

最后

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

1.在建表時就應(yīng)該考慮添加索引，如：外鍵字段,等等。

2.在寫完SQL后,一定要查看執(zhí)行計劃。盡量避免全表掃描。

3.如果是已有表中添加索引,一定要先計算該字段的區(qū)分度。

4.聯(lián)合索引，將區(qū)分度大放在前面。

5.遵從MySQL左列前綴優(yōu)先原則\

[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索引原理是什么的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

當(dāng)前名稱：MySQL索引原理是什么
網(wǎng)站URL：http://muchs.cn/article36/jehspg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、域名注冊、小程序開發(fā)、網(wǎng)站營銷、品牌網(wǎng)站設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)