您好,登錄后才能下訂單哦!
這篇文章主要介紹了MySQL索引設計原則有哪些的相關知識,內容詳細易懂,操作簡單快捷,具有一定借鑒價值,相信大家閱讀完這篇MySQL索引設計原則有哪些文章都會有所收獲,下面我們一起來看看吧。
哪些情況適合創建索引?
字段的數值有唯一性的限制。索引本身可以起到約束的作用,比如唯一索引,主鍵索引都是可以起到唯一性約束的,因此在我們的數據表中如果某個字段是唯一性的,就可以直接創建唯一性索引,或者主鍵索引。這樣可以更快速地通過該索引來確定某條記錄。
業務上具有唯一特性的字段,即使是組合字段,也必須建成唯一索引。
說明:不要以為唯一索引影響了 insert 速度,這個速度損耗可以忽略,但提高查找速度是明顯的。
頻繁作為 WHERE 查詢條件的字段
某個字段在SELECT語句的 WHERE 條件中經常被使用到,那么就需要給這個字段創建索引了。尤其是在數據量大的情況下,創建普通索引就可以大幅提升數據查詢的效率。
經常 GROUP BY 和 ORDER BY 的列
索引就是讓數據按照某種順序進行存儲或檢索,因此當我們使用 GROUP BY 對數據進行分組查詢,或者使用 ORDER BY 對數據進行排序的時候,就需要對分組或者排序的字段進行索引。如果待排序的列有多個,那么可以在這些列上建立組合索引。
如果既有GROUP BY又有ORDER BY,可以考慮聯合索引,由于GROUP BY先執行,聯合索引中GROUP BY使用的字段排列在前面。
UPDATE、DELETE的WHERE條件列
當我們對某條數據進行UPDATE或者DELETE操作的時候,是否也需要對WHERE條件列創建索引呢?
對數據按照某個條件進行查詢后再進行 UPDATE 或 DELETE 的操作,如果對 WHERE 字段創建了索引,就能大幅提升效率。原理是因為我們需要先根據 WHERE 條件列檢索出來這條記錄,然后再對它進行更新或刪除。如果進行更新的時候,更新的字段是非索引字段,提升的效率會更明顯,這是因為非索引字段更新不需要對索引進行維護。
DISTINCT字段需要創建索引
有時候我們需要對某個字段進行去重,使用 DISTINCT,那么對這個字段創建索引,也會提升查詢效率。索引會對數據按照某種順序進行排序,所以在去重的時候也會快很多。
多表 JOIN 連接操作時,創建索引注意事項
首先,連接表的數量盡量不要超過 3 張,因為每增加一張表就相當于增加了一次嵌套的循環,數量級增長會非常快,嚴重影響查詢的效率。
其次,對 WHERE 條件創建索引,因為 WHERE 才是對數據條件的過濾。如果在數據量非常大的情況下,沒有 WHERE 條件過濾是非常可怕的。
最后,對用于連接的字段創建索引,并且該字段在多張表中的類型必須一致。
使用列的類型小的創建索引
我們這里所說的類型大小指的就是該類型表示的數據范圍的大小。
我們在定義表結構的時候要顯式的指定列的類型,以整數類型為例,有TINYINT
、MEDIUMINT
、INT
、BIGINT
等,它們占用的存儲空間依次遞增,能表示的整數范圍當然也是依次遞增。如果我們想要對某個整數列建立索引的話,在表示的整數范圍允許的情況下,盡量讓索引列使用較小的類型,比如我們能使用INT就不要使用 BIGINT ,能使用 MEDIUMINT 就不要使用 INT。這是因為:
數據類型越小,在查詢時進行的比較操作越快;數據類型越小,索引占用的存儲空間就越少,在一個數據頁內就可以放下更多的記錄 ,從而減少磁盤 I/O 帶來的性能損耗,也就意味著可以把更多的數據頁緩存在內存中,從而加快讀寫效率。
這個建議對于表的主鍵來說更加適用 ,因為不僅是聚簇索引中會存儲主鍵值,其他所有的二級索引的節點處都會存儲一份記錄的主鍵值,如果主鍵使用更小的數據類型,也就意味著節省更多的存儲空間和更高效的I/O。
使用字符串前綴創建索引
假設我們的字符串很長,那存儲一個字符串就需要占用很大的存儲空間。在我們需要為這個字符串列建立索引時,那就意味著在對應的B+樹中有這么兩個問題:
B+樹索引中的記錄需要把該列的完整字符串存儲起來,更費時,而目字符串越長,在索引中占用的存儲空間越大。如果B+樹索引中索引列存儲的字符串很長,那在做字符串比較時會占用更多的時間。
我們可以通過截取字段的前面一部分內容建立索引,這個就叫前綴索引。這樣在查找記錄時雖然不能精確的定位到記錄的位置,但是能定位到相應前綴所在的位置,然后根據前綴相同的記錄的主鍵值回表查詢完整的字符串值。既節約空間,又減少了字符串的比較時間,還大體能解決排序的問題。
例如,TEXT和BLOG類型的字段,進行全文檢索會很浪費時間,如果只檢索字段前面的若干字符,這樣可以提高檢索速度。
示例:創建一張商戶表,因為地址字段比較長,在地址字段上建立前綴索引
CREATE TABLE shop(address VARCHAR(120) NOT NULL); ALTER TABLE shop ADD INDEX idx_address(address(12));
問題是,截取多少呢?截取得多了,達不到節省索引存儲空間的目的;截取得少了,重復內容太多,字段的散列度(選擇性)會降低。怎么計算不同的長度的選擇性呢?
先看一下字段在全部數據中的選擇度:
SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;
通過不同長度去計算,與全表的選擇性對比:
公式:
COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*)
例如:
SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) AS sub10, -- 截取前10個字符串的選擇度 COUNT(DISTINCT LEFT(address,15)) / COUNT(*) AS sub15, -- 截取前15個字符串的選擇度 COUNT(DISTINCT LEFT(address,20)) / COUNT(*) AS sub20, -- 截取前20個字符串的選擇度 COUNT(DISTINCT LEFT(address,25)) / COUNT(*) AS sub25 -- 截取前25個字符串的選擇度 FROM shop;
引申另一個問題:索引列前綴對排序的影響
如果使用了索引列前綴,比方說前邊只把address列的 前12個字符 放到了二級索引中,下邊這個查詢可能就有點兒尷尬了:
SELECT * FROM shop ORDER BY address LIMIT 10;
因為二級索引中不包含完整的address列信息,所以無法對前12個字符相同,后邊的字符不同的記錄進行排序,也就是使用索引列前綴的方式,無法支持使用索引排序,只能使用文件排序。
【強制】在 varchar 字段上建立索引時,必須指定索引長度,沒必要對全字段建立索引,根據實際文本區分度決定索引長度。
說明:索引的長度與區分度是一對矛盾體,一般對字符串類型數據,長度為20的索引,區分度會高達 90%以上,可以使用COUNT(DISTINCT LEFT(列名, 索引長度)) / COUNT(*)
的區分度來確定。
區分度高(散列性高)的列適合作為索引
列的基數指的是某一列中不重復數據的個數,比方說某個列包含值2, 5, 8, 2, 5, 8, 2, 5, 8,雖然有9條記錄,但該列的基數卻是3。也就是說,在記錄行數一定的情況下,列的基數越大,該列中的值越分散;列的基數越小,該列中的值越集中。這個列的基數指標非常重要,直接影響我們是否能有效的利用索引,最好為列的基數大的列建立索引,為基數太小列的建立索引效果可能不好。
可以使用公式:
SELECT COUNT(DISTINCT 列名) / COUNT(*) FROM 表名;
計算區分度,越接近1越好,一般超過33%就算是比較高效的索引了。
拓展:聯合索引把區分度高(散列性高)的列放在前面。
使用最頻繁的列放到聯合索引的左側
這樣也可以較少的建立一些索引。同時,由于"最左前綴原則",可以增加聯合索引的使用率。
在多個字段都要創建索引的情況下,聯合索引優于單值索引 限制索引的數目
在實際工作中,我們也需要注意平衡,索引的數目不是越多越好。我們需要限制每張表上的索引數量,建議單張表索引數量不超過6個。
原因:每個索引都需要占用 磁盤空間,索引越多,需要的磁盤空間就越大。索引會影響 INSERT、DELETE、UPDATE等語句的性能,因為表中的數據更改的同時,索引也會進行調整和更新,會造成負擔。優化器在選擇如何優化查詢時,會根據統一信息,對每一個可以用到的索引來進行評估,以生成出一個最好的執行計劃,如果同時有很多個索引都可以用于查詢,會增加MySQL優化器生成執行計劃時間,降低查詢性能。 哪些情況不適合創建索引 在where中使用不到的字段,不要設置索引
WHERE 條件(包括 GROUP BY、ORDER BY)里用不到的字段不需要創建索引,索引的價值是快速定位,如果起不到定位的字段通常是不需要創建索引的。
數據量小的表最好不要使用索引
如果表記錄太少,比如少于 1000 個,那么是不需要創建索引的。表記錄太少,是否創建索引對查詢效率的影響并不大。甚至說,查詢花費的時間可能比遍歷索引的時間還要短,索引可能不會產生優化效果。
結論:在數據表中的數據行數比較少的情況下,比如不到1000行,是不需要創建索引的。
有大量重復數據的列上不要建立索引
在條件表達式中經常用到的不同值較多的列上建立索引,但字段中如果有大量重復數據,也不用創建索引。
比如在學生表的"性別"字段上只有“男”與“女”兩個不同值,因此無須建立索引。如果建立索引,不但不會提高查詢效率,反而會嚴重降低數據更新速度。
示例:要在 100 萬行數據中查找其中的 50 萬行(比如性別為男的數據),一旦創建了索引,你需要先訪問 50萬次索引,然后再訪問 50 萬次數據表,這樣加起來的開銷比不使用索引可能還要大。
索引的價值是幫你快速定位。如果想要定位的數據有很多,那么索引就失去了它的使用價值,比如通常情況下的性別字段。
結論:當數據重復度大,比如 高于10% 的時候,也不需要對這個字段使用索引。
避免對經常更新的表創建過多的索引
第一層含義:頻繁更新的字段不一定要創建索引。因為更新數據的時候,也需要更新索引,如果索引太多,在更新索引的時候也會造成負擔,從而影響效率。
第二層含義:避免對經常更新的表創建過多的索引,并且索引中的列盡可能少。此時,雖然提高了查詢速度,同時卻會降低更新表的速度。
不建議用無序的值作為索引
例如身份證、UUID(在索引比較時需要轉為ASCII,并且插入時可能造成頁分裂)、MD5、HASH、無序長字符串等。
刪除不再使用或者很少使用的索引
表中的數據被大量更新,或者數據的使用方式被改變后,原有的一些索引可能不再需要。數據庫管理員應當定期找出這些索引,將它們刪除,從而減少索引對更新操作的影響。
不要定義冗余或重復的索引 冗余索引
有時候有意或者無意的就對同一個列創建了多個索引,比如:index(a,b,c)相當于index(a)、index(a,b)、index(a,b,c)。
示例:
CREATE TABLE person_info ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR (100) NOT NULL, birthday DATE NOT NULL, phone_number CHAR (11) NOT NULL, country VARCHAR (100) NOT NULL, PRIMARY KEY (id), KEY idx_name_birthday_phone_number (name(10), birthday, phone_number), KEY idx_name (name(10)) );
我們知道,通過idx_name_birthday_phone_number 索引就可以對 name 列進行快速搜索,再創建一個專門針對 name 列的索引就算是一個冗余索引,維護這個索引只會增加維護的成本,并不會對搜索有什么好處。
重復索引
另一種情況,我們可能會對某個列重復建立索引:
CREATE TABLE repeat_index_demo ( col1 INT PRIMARY KEY, co12 INT, UNIQUE uk_idx_c1 (col1), INDEX idx_c1 (col1) );
我們看到,col1既是主鍵、又給它定義為一個唯一索引,還給它定義了一個普通索引。可是主鍵本身就會生成聚簇索引,所以定義的唯一索引和普通索引是重復的,這種情況要避免。
關于“MySQL索引設計原則有哪些”這篇文章的內容就介紹到這里,感謝各位的閱讀!相信大家對“MySQL索引設計原則有哪些”知識都有一定的了解,大家如果還想學習更多知識,歡迎關注億速云行業資訊頻道。
免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。