MySQL索引功能

標籤：技術   innodb   索引類型   這一   多列   內容   頁碼   應該   col   

MySQL索引功能1.索引的作用

• 大大大加快資料的檢索速度和增加查詢的效率
• 降低磁碟I/O成本
• 保證資料記錄的唯一性
• 使得應用於表的SQL語句執行的更快

2.索引簡述

• 在關聯式資料庫中，索引是一種單獨的、物理的對資料庫表中一列或多列的值進行排序的一種儲存結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的資料頁的邏輯指標清單。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。
• 索引是一種特殊的檔案（InnoDB 資料表上的索引是資料表空間的一個組成部分），它們包含著對資料表裡所有記錄的引用指標。索引不是萬能的，索引可以加快資料檢索操作，但會使資料修改操作變慢。每修改資料記錄，索引就必須重新整理一次。為了在某種程度上彌補這一缺陷，許多 SQL 命令都有一個 DELAY_KEY_WRITE 項。這個選項的作用是暫時制止 MySQL 在該命令每插入一條新記錄和每修改一條現有之後立刻對索引進行重新整理，對索引的重新整理將等到全部記錄插入/修改完畢之後再進行。在需要把許多新記錄插入某個資料表的場合，DELAY_KEY_WRITE 選項的作用將非常明顯。另外，索引還會在硬碟上佔用相當大的空間。因此應該只為最經常查詢和最經常排序的資料列建立索引。注意，如果某個資料列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
• 從理論上講，完全可以為資料表裡的每個欄位分別建一個索引，但 MySQL 把同一個資料表裡的索引總數限制為16個。

InnoDB 資料表的索引

• 在 InnoDB 資料表上，索引對 InnoDB 資料表的重要性要大得多。在 InnoDB 資料表上，索引不僅會在搜尋資料記錄時發揮作用，還是資料行級鎖定機制的苊、基礎。“資料行級鎖定”的意思是指在事務操作的執行過程中鎖定正在被處理的個別記錄，不讓其他使用者進行訪問。這種鎖定將影響到（但不限於）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE 命令以及 INSERT、UPDATE 和 DELETE 命令。出於效率方面的考慮，InnoDB 資料表的資料行級鎖定實際發生在它們的索引上，而不是資料表自身上。顯然，資料行級鎖定機制只有在有關的資料表有一個合適的索引可供鎖定的時候才能發揮效力。

3.索引的限制

• 如果 WHERE 子句的查詢條件裡有不等號（WHERE coloum !=），MySQL 將無法使用索引。類似地，如果 WHERE 子句的查詢條件裡使用了函數（WHERE DAY（column）=），MySQL 也將無法使用索引。在 JOIN 操作中（需要從多個資料表提取資料時），MySQL 只有在主鍵和外鍵的資料類型相同時才能使用索引。
• 如果 WHERE 子句的查詢條件裡使用比較操作符 LIKE 和 REGEXP，MySQL 只有在搜尋模板的第一個字元不是萬用字元的情況下才能使用索引。比如說，如果查詢條件是 LIKE ‘abc%‘，MySQL 將使用索引；如果查詢條件是 LIKE ‘%abc’，MySQL 將不使用索引。
• 在 ORDER BY 操作中，MySQL 只有在排序條件不是一個查詢條件運算式的情況下才使用索引。（雖然如此，在涉及多個資料表查詢裡，即使有索引可用，那些索引在加快 ORDER BY 方面也沒什麼作用）。如果某個資料列裡包含許多重複的值，就算為它建立了索引也不會有很好的效果。比如說，如果某個資料列裡包含的淨是些諸如 “0/1” 或 “Y/N” 等值，就沒有必要為它建立一個索引。

4.索引的分類（1）普通索引

• 最基本的索引類型，沒有唯一性之類的限制。

（2）唯一索引

• 唯一索引是不允許其中任何兩行具有相同索引值的索引。

• 當現有資料中存在重複的索引值時，大多數資料庫不允許將新建立的唯一索引與表一起儲存。資料庫還可能防止添加將在表中建立重複索引值的新資料。例如，如果在 employee 表中職員的姓 (lname) 上建立了唯一索引，則任何兩個員工都不能同姓。

• 對某個列建立UNIQUE索引後，插入新紀錄時，資料庫管理系統會自動檢查新紀錄在該列上是否取了重複值，在CREATE TABLE 命令中的UNIQE約束將隱式建立UNIQUE索引。

（3）主鍵索引

• 簡稱為主索引，資料庫表中一列或列組合（欄位）的值唯一標識表中的每一行。該列稱為表的主鍵。

• 在資料庫圖表中為表定義主鍵將自動建立主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對資料的快速存取。

• 提示儘管唯一索引有助於定位資訊，但為獲得最佳效能結果，建議改用主鍵索引。

（4）候選索引

• 與主索引一樣要求欄位值的唯一性，並決定了處理記錄的順序。在資料庫和自由表中，可以為每個表建立多個候選索引。

（5）複合索引

• 兩個或更多個列上的索引被稱作複合索引。 利用索引中的附加列，您可以縮小搜尋的範圍，但使用一個具有兩列的索引不同於使用兩個單獨的索引。 不允許資料記錄出現重複值和空值。

（6）全文索引

• 全文索引是目前實現大資料搜尋的關鍵技術。 能夠利用分詞技術等多種演算法智能分析出文本文字中關鍵字詞的頻率及重要性，然後按照一定的演算法規則智能地篩選出我們想要的搜尋結果。 用於多個列值，允許資料記錄出現重複值和空值

• MySQL內建的全文索引只能用於資料庫引擎為MyISAM的資料表

（7）空間索引

• 在MySQL 5.7.4實驗室版本中， InnoDB儲存引擎新增了對於幾何資料空間索引的支援。在此之前，InnoDB將幾何資料存放區為BLOB（二進位大對象）資料，在空間資料上只能建立首碼索引，當涉及空間搜尋時非常低效，尤其是在涉及複雜的幾何資料時。在大多數情況下，獲得結果的唯一方式是掃描表。 新版本MySQL中，InnoDB支援空間索引，通過R樹來實現，使得空間搜尋變得高效。

5.索引的使用（1）建立索引

create [unique | fulltext | spatial] index 索引名 on 表名(欄位)

詳解

unique：唯一索引

fulltext：全文索引，InnoDB不支援FULLTEXT類型的索引

spatial：空間索引

案例

mysql> create index hehe_1 on hehe.user(user_name); //user表中user_name欄位建立普通索引

mysql> create unique index hehe_2 on hehe.user(user_passwd); //user表中user_passwd欄位建立唯一索引

mysql> create index hehe_3 on hehe.user(user_name,user_passwd);

//user表中user_name、user_passwd欄位組合為複合索引

（2）查看索引

show index from 資料庫名.表名;

（3）刪除索引

drop index 索引名 on 資料庫名.表名;

MySQL索引功能