技術文章 » MySQL索引給拿捏住了

MySQL索引給拿捏住了

2022-03-29 19:00:20
本篇文章給大家帶來了關於的相關知識，其中主要介紹了關於mysql索引的相關問題，包括了索引按照邏輯功能劃分、按照物理實現劃分、按照欄位個數劃分等索引型別問題，希望對大家有幫助。

推薦學習：

在 SQL 優化中，索引是至關重要的一環，能給查詢效率帶來質的飛躍，但是索引並不是萬能的，不合理的索引設計甚至會拖慢查詢效率。

索引定義

索引是一種專門用於幫助 SQL 高效獲取資料的資料結構，一個常用的例子是，索引類似於一本書的目錄，可以快速對特定值進行定位和查詢，從而大大加快資料查詢的效率。實際上，索引也是一張表，這張表儲存了主鍵與索引欄位，並指向實體表的記錄（類似指標）。

索引優缺點

優點

  • 索引大大減小了伺服器需要掃描的資料量
  • 索引可以幫助伺服器避免排序和臨時表
  • 索引可以將隨機IO變成順序IO
  • 索引對於InnoDB（對索引支援行級鎖）非常重要，InnoDB僅對需要存取的元組加鎖，而索引能夠減少InnoDB存取的元組數。如果查詢不能使用索引，MySQL會進行全表掃描，並鎖住每一個元組，不管是否真正需要。

缺點

  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度。因為更新表時，MySQL不僅要儲存資料，還要儲存索引檔案。因此，對應更新非常頻繁的欄位，通常不建議使用索引。
  • 建立索引會佔用磁碟空間。
  • 如果某個資料列包含許多重複的內容，為它建立索引效果就很差，這個性質稱為索引的選擇性：不重複的索引值和資料表中的記錄總數的比值。索引的選擇性越高則查詢效率越高。比如對性別欄位建立索引，一百萬條資料，只有男女兩種可能，索引選擇性為五十萬分之一，索引效果就很差
  • 對於非常小的表，索引意義不大，大部分情況下簡單的全表掃描更高效。

因此應該只為最經常查詢和最經常排序的資料列建立索引。MySQL裡同一個資料表裡的索引總數限制為16個。

索引型別

按功能邏輯劃分

從功能邏輯來劃分，索引主要分為 普通索引、唯一索引、主鍵索引和全文索引

普通索引

最基本的索引，它沒有任何限制。普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對資料的存取速度。因此，應該只為那些最經常出現在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的資料列建立索引。

普通索引的建立有三種方式。

# 建立索引CREATE INDEX idx_username ON user_tbl(username);# 對於字串欄位，可以手動指定長度，如 user_tbl(username(5))，表示只用前五個字元來做索引，可以進一步加快查詢效率，索引長度要小於欄位長度# 修改表結構ALTER TABLE user_tbl ADD INDEX idx_username (username)# 建立表的時候直接指定，如CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	INDEX idx_username (username) );

刪除索引

DROP INDEX idx_username ON user_tbl;

檢視索引

SHOW INDEX FROM user_tbl;

唯一索引

它與前面的普通索引類似，不同的就是：普通索引允許被索引的資料列包含重複的值。而唯一索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

唯一索引的建立跟普通索引類似：

#建立索引
CREATE UNIQUE INDEX idx_username ON user_tbl(username);

# 修改表結構
ALTER TABLE user_tbl ADD UNIQUE idx_username (username)

# 建立表的時候直接指定
CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	UNIQUE idx_username (username) 
);

主鍵索引

它是一種特殊的唯一索引，不允許有空值。一張表只能有一個主鍵，一般是在建表的時候同時建立。

CREATE TABLE user_tbl( 
	ID INT NOT NULL, 
	username VARCHAR(16) NOT NULL, 
	PRIMARY KEY(ID) 
);

與之類似的是外來鍵索引，如果為某個外來鍵欄位定義了一個外來鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外來鍵約束條件。

全文索引

在上一篇文章 MySQL 基礎語法 中，我們說過如果使用了 LIKE + % 開頭，就索引會失效，那麼當我們需要前後都模糊搜尋的需求（如 LIKE ‘%hello%’），就需要使用全文索引，需要注意的是，Innodb 只有在 5.6 版本之後才支援全文索引。

全文索引的建立和刪除：

# 建立的兩種方法
CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name);
ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name);

# 刪除的兩種方法
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

使用全文索引進行全模糊匹配的語法為：

SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx');
# 比如對 user_tbl 的 user_name 欄位加了全文索引
# 查詢結果等效於 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%';
SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');

使用 explain 檢查，可以發現 fulltext 索引生效。
在這裡插入圖片描述

按物理實現劃分

按物理實現方式來劃分，通常可以分為聚集索引和非聚集索引。

聚集索引（clustered index）

儲存內容是按照聚集索引排序的，聚集索引的順序和行記錄的順序一致,一張表只能有一個聚集索引。聚集索引的葉子節點直接儲存聚集索引指向的內容，因此查詢的時候只需要進行一次查詢。

聚集索引在建立主鍵時自動生成，如果沒有主鍵，則根據第一個不為空的唯一索引自動生成，如果還沒有，則自動生成一個隱式的聚集索引。

需要注意的是，在進行查詢操作的時候，聚集索引的效率更高，因為少了一次查詢；但是進行修改操作的時候，效率比非聚集索引低，因為直接修改了資料內容，為了標準資料內容的順序和聚集索引順序一致，會對資料頁重新排序。

非聚集索引（non-clustered index）

非聚集索引雖然索引項是順序儲存的，但是索引項對應的內容是隨機儲存的，系統會維護單獨的索引表來儲存索引。

非聚集索引的葉子節點儲存的是資料的地址，查詢非聚集索引的時候，系統會進行兩次查詢，先查詢索引，再查詢索引對應位置的資料。因此非聚集索引也叫二級索引或者輔助索引。

按欄位個數劃分

按欄位個數可以把索引分為單一索引和聯合索引。

單一索引

索引欄位只有一列時為單一索引，上述所有索引都是單一索引。

聯合索引

將多個欄位組合在一起建立的索引叫聯合索引。如下：

ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);

最左匹配原則

建立這樣的聯合索引，其實是相當於分別建立了下面三組聯合索引：

usernname,city,age
usernname,city
usernname

為什麼沒有 city，age 這樣的聯合索引呢？這是因為MySQL聯合索引的最左匹配原則，只會按照最左優先的順序進行索引匹配，也就是說，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用聯合索引中的欄位查詢，聯合索引也有可能失效。

對於 (x,y,z)，只有在以下查詢條件聯合索引會生效：

WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1

對於其他情況，比如 WHERE y = 1 、WHERE y = 1 AND z = 1 等，就不會匹配聯合索引，索引失效，注意對於 WHERE x = 1 AND z = 1，聯合索引會對 x 生效，但是對 z 不生效。

可以擴充套件瞭解一下，理論上最左匹配原則中索引對 where 中子句的順序也是敏感的，但是由於MySQL的查詢優化器會自動調整 where 子句的條件順序以使用適合的索引，所以實際上 where 子句順序不影響索引的效果。

要注意的是，如果聯合索引查詢過程中有範圍查詢，就會停止匹配，比如下面的語句中， z 欄位不能使用到索引：

WHERE x = 1 AND y > 2 AND z = 3

順便提一下，可以用 explain 命令來檢視在某個查詢語句中索引是否生效，具體用法請參考官網檔案。

如果分別在 x, y, z 上建立單列索引，讓該表有3個單列索引，索引效率也會大不一樣，在聯合索引生效的情況下，單個索引的效率遠遠低於聯合索引。這是由 MySQL 查詢優化器的執行順序決定的，在執行一條查詢 sql 時，針對索引的選擇大致有如下步驟：

  1. MySQL 優化器根據搜尋條件，找出所有可能使用的索引
  2. 計算全表掃描的代價
  3. 計算使用不同索引執行查詢的代價
  4. 對比各種執行方案的代價，找出成本最低的那一個

因此，雖然有多個單列索引，但 MySQL 只能用到其中的那個系統認為似乎是最有效率的，其他的就會失效。

按索引結構劃分

不同的 mysql 資料引擎支援不同結構的索引，按結構劃分，常用的索引為 B+樹索引、Hash 索引、FULLTEXT索引 等，將在下一篇文章 MySQL 索引結構 中介紹。

使用總結

接下來我們來簡單總結一下在什麼場景下推薦使用索引。

推薦使用

  • WHERE, GROUP BY, ORDER BY 子句中的欄位

  • 多個單列索引在多條件查詢是隻會有一個最優的索引生效，因此多條件查詢中最好建立聯合索引。

    聯合索引的時候必須滿足最左匹配原則，並且最好考慮到 sql 語句的執行順序，比如 WHERE a = 1 GROUP BY b ORDER BY c, 那麼聯合索引應該設計為 (a,b,c)，因為在上一篇文章 MySQL 基礎語法 中我們介紹過，mysql 查詢語句的執行順序 WHERE > GROUP BY > ORDER BY。

  • 多張表 JOIN 的時候，對錶連線欄位建立索引。

  • 當 SELECT 中有不在索引中的欄位時，會先通過索引查詢出滿足條件的主鍵值，然後通過主鍵回表查詢出所有的 SELECT 中的欄位，影響查詢效率。因此如果 SELECT 中的內容很少，為了避免回表，可以把 SELECT 中的欄位都加到聯合索引中，這也就是寬索引的概念。但是需要注意，如果索引欄位過多，儲存和維護索引的成本也會增加。

不推薦使用或索引失效情況

  • 資料量很小的表

  • 有大量重複資料的欄位

  • 頻繁更新的欄位

  • 如果對索引欄位使用了函數或者表示式計算，索引失效

  • innodb OR 條件沒有對所有條件建立索引，索引失效

  • 大於小於條件 < >，索引是否生效取決於命中的數量比例，如果命中數量很多，索引生效，命中數量很小，索引失效

  • 不等於條件 != <>，索引失效

  • LIKE 值以 % 開頭，索引失效

推薦學習：

以上就是MySQL索引給拿捏住了的詳細內容，更多請關注TW511.COM其它相關文章！