1003
MySQL中的索引
MySQL中的索引
作者：Jason Lee @https://blog.csdn.net/jasonblog
日期：2010-06-04
聲明：本文發表在csdn博客，如有轉載，請注明出處
[引言]
如圖書館等存放大量數據的場合都需要設置索引以方便檢索。當數據庫中存儲的記錄逐漸海量化的時候，合理地采用索引能大大改善程序性能。
[使用索引]
首先，創建一個用於測試的表：
CREATE TABLE`test`.`books` (
`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE =MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
以上語句在建表的過程中同時指定了主鍵，這會創建一個主鍵索引，即數據庫中最常見的索引類型。
除了PRIMARY關鍵字，UNIQUE關鍵字也會形成索引。不同的是，每個表中主鍵索引隻能有一個，而唯一索引可以有多個。而二者都可以指定多字段索引：
DROP TABLE books;
CREATETABLE `test`.`books` (
`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
UNIQUE (title, author),
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATEutf8_general_ci
以上的UNIQUE關鍵字用書名和作者的組合形成了唯一索引。
如果經常性地需要對作者進行排序，那麼可以為該字段創建一個索引；或者，當作者有姓氏和姓名兩個不同字段，也可以建立多字段索引。
DROP TABLEbooks;
CREATE TABLE `test`.`books` (
`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
INDEX ( author ) ,
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATEutf8_general_ci
最後一種索引是使用FULLTEXT關鍵字建立的全文索引。MySQL為待搜索的文本進行分詞（默認忽略少於4個字符的單詞，可自定義），從而高效地在CHAR、VERCHAR或TEXT類型數據中搜索。
[理解索引]
要理解索引，首先需要了解數據如何存儲在硬盤上。不同的存儲引擎采取的措施可能不一樣，比如MySQL客戶端默認采用MyISAM，該引擎為每個表創建單獨的文件。
不管是否為每個表創建了單獨的文件，操作係統從硬盤讀取數據到內存中總是以頁為單位的。因此，如果要獲取特定記錄，就需要把包含該記錄的頁讀入。
MySQL在獲取一條記錄的時候，可以采取逐條掃描或者索引訪問兩種不同的方法。假設采取第一種方法，要獲取id為1234的記錄，就需要順序地、依次地訪問過前1233條記錄。不僅如此，還需要考慮每次讀入數據頁的IO開銷。而如果采取索引，則可以根據索引指向的頁以及記錄在頁中的位置，迅速地讀取目標頁進而獲取目標記錄。
除了在獲取特定行的情況下使用hash十分快捷，在其它情況下都（默認）采用B樹來構建索引。B樹是平衡多叉樹，每個節點存放多少個值取決於值所占的空間，這與每一張數據頁存放多少條記錄與記錄信息量有關同理。節點中的值是以非降序進行排列的，節點中的值總是小於等於指向它的結點中的值。
MySQL使用B樹構造索引的情況下，是由葉子指向具體的頁和記錄的。並且一個葉子有一個指針指向下一個葉子。
使用索引需要注意：
⑴隻對WHERE和ORDER BY需要查詢的字段設置索引，避免無意義的硬盤開銷；
⑵組合索引支持前綴索引；
⑶更新表的時候，如增刪記錄，MySQL會自動更新索引，保持樹的平衡；
最後更新：2017-04-02 06:51:19