MySQL索引給拿捏住了

本篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於mysql索引的相關問題，包括了索引按照邏輯功能劃分、按照物理實現劃分、按照字段個數劃分等索引類型問題，希望對大家有幫助。

推薦學習：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影片教學#

以上是MySQL索引給拿捏住了的詳細內容。更多資訊請關注PHP中文網其他相關文章！