MySql索引原理與使用大全

 

一、索引介紹

索引是對數據庫表中一列或多列的值進行排序的一種結構。在關系數據庫中，索引是一種與表有關的數據庫結構，它可以使對應於表的SQL語句執行得更快。索引的作用相當於圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。當表中有大量記錄時，若要對表進行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量數據庫系統時間，並造成大量磁盤I/O操作；第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。
索引是一個單獨的、物理的數據庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引提供指向存儲在表的指定列中的數據值的指針，然後根據您指定的排序順序對這些指針排序。數據庫使用索引的方式與您使用書籍中的索引的方式很相似：它搜索索引以找到特定值，然後順指針找到包含該值的行。在數據庫關系圖中，可以在選定表的“索引/鍵”屬性頁中創建、編輯或刪除每個索引類型。當保存索引所附加到的表，或保存該表所在的關系圖時，索引將保存在數據庫中。

Mysql索引概述

所有MySQL列類型可以被索引。對相關列使用索引是提高SELECT操作性能的最佳途徑。根據存儲引擎定義每個表的最大索引數和最大索引長度。所有存儲引擎支持每個表至少16個索引，總索引長度至少為256字節。大多數存儲引擎有更高的限制。

在MySQL 5.1中，對於MyISAM和InnoDB表，前綴可以達到1000字節長。請注意前綴的限制應以字節為單位進行測量，而CREATE TABLE語句中的前綴長度解釋為字符數。當為使用多字節字符集的列指定前綴長度時一定要加以考慮。

還可以創建FULLTEXT索引。該索引可以用於全文搜索。只有MyISAM存儲引擎支持FULLTEXT索引，並且只為CHAR、VARCHAR和TEXT列。索引總是對整個列進行，不支持局部(前綴)索引。也可以為空間列類型創建索引。只有MyISAM存儲引擎支持空間類型。空間索引使用R-樹。默認情況MEMORY(HEAP)存儲引擎使用hash索引，但也支持B-樹索引。

btree索引與hash索引

對於BTREE和HASH索引，當使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。Hash索引還有一些其它特征：它們只用於使用=或<=>操作符的等式比較(但很快)。優化器不能使用hash索引來加速ORDER BY操作。(該類索引不能用來按順序搜索下一個條目）。MySQL不能確定在兩個值之間大約有多少行(這被范圍優化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。只能使用整個關鍵字來搜索一行。(用B-樹索引，任何關鍵字的最左面的前綴可用來找到行)。

對於BTREE索引，當使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符開始)操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。“常量值”系指：查詢字符串中的常量、同一聯接中的const或system表中的列、無關聯子查詢的結果、完全從前面類型的子表達式組成的表達式。

下面是一些WHERE子句中有范圍條件的查詢的例子。

下列范圍查詢適用於 btree索引和hash索引：

SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20); 下列范圍查詢適用於btree索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';

Mysql如何使用索引

索引用於快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然後讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要看所有數據。如果一個表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問大部分行，順序讀取要快得多，因為此時我們避免磁盤搜索。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，並且MEMORY表還支持hash索引。

二、使用方法

首先先創建一個表：

CREATE TABLE
    t_student
    (
        STU_ID INT NOT NULL,
        STU_NAME CHAR(10) NOT NULL,
        STU_CLASS INT NOT NULL,
        STU_SEX CHAR(2) NOT NULL,
        STU_AGE INT NOT NULL,
        PRIMARY KEY (STU_ID)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.1 普通索引

創建索引
這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：

 

CREATE INDEX index_id  ON t_student(STU_ID); 

 

如果是CHAR，VARCHAR類型，length可以小於字段實際長度；如果是BLOB和TEXT類型，必須指定 length。
修改表結構

 

ALTER TABLE t_student ADD INDEX  index_name(STU_NAME(4)) ;

 

創建表的時候直接指定

CREATE TABLE
    t_student1
    (
        STU_ID INT NOT NULL,
        STU_NAME CHAR(10) NOT NULL,
        STU_CLASS INT NOT NULL,
        STU_SEX CHAR(2) NOT NULL,
        STU_AGE INT NOT NULL,
        PRIMARY KEY (STU_ID),
        INDEX index_name (STU_NAME(5))
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

刪除索引的語法

DROP INDEX index_id  ON t_student;
DROP INDEX index_name  ON t_student;

結果

1.2、唯一索引

主鍵就是唯一索引的一種，主鍵要求建表時指定，一般用auto_increment列，關鍵字是primary key。它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：
創建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

修改表結構

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) 

創建表的時候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
); 

1. 3、多列索引

創建索引

CREATE  INDEX indexName ON mytable(username1(length),username2(length)) 

 

CREATE INDEX index_age_aex ON t_student(STU_AGE,STU_SEX);

修改表結構

ALTER mytable ADD  [indexName] ON (username1(length),username2(length)) 

創建表的時候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username1(length),username2(length))
); 

多列索引的一個優點，它通過稱為最左前綴（Leftmost Prefixing）的概念體現出來。繼續考慮前面的例子，現在我們有一個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜索條件是以下各種列的組合時，MySQL將使用fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
從另一方面理解，它相當於我們創建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的索引。下面這些查詢都能夠使用這個fname_lname_age索引：
Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan' AND age='17';
Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan';
Select peopleid FROM people Where firstname='Mike';
下面這些查詢不能夠使用這個fname_lname_age索引：
Select peopleid FROM people Where lastname='Sullivan';
Select peopleid FROM people Where age='17';
Select peopleid FROM people Where lastname='Sullivan' AND age='17';

 

1.4、全文索引

全文索引(也稱全文檢索)是目前搜索引擎使用的一種關鍵技術。它能夠利用「分詞技術「等多種算法智能分析出文本文字中關鍵字詞的頻率及重要性，然後按照一定的算法規則智能地篩選出我們想要的搜索結果。在這裡，我們就不追根究底其底層實現原理了，現在我們來看看在MySQL中如何創建並使用全文索引。
在MySQL中，創建全文索引相對比較簡單。例如，我們有一個文章表(article)，其中有主鍵ID(id)、文章標題(title)、文章內容(content)三個字段。現在我們希望能夠在title和content兩個列上創建全文索引，article表及全文索引的創建SQL語句如下：
--創建article表

CREATE TABLE article (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
 title VARCHAR(200),
 content TEXT,
 FULLTEXT (title, content)
 )ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

看看索引

上面就是在創建表的同時建立全文索引的SQL示例。此外，如果我們想要給已經存在的表的指定字段創建全文索引，同樣以article表為例，我們可以使用如下SQL語句進行創建：
--給現有的article表的title和content字段創建全文索引
--索引名稱為fulltext_article

ALTER TABLE article ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中創建全文索引之後，現在就該了解如何使用了。我們必須使用特有的語法才能使用全文索引進行查詢。例如，我們想要在article表的title和content列中全文檢索指定的查詢字符串，可以如下編寫SQL語句：

SELECT * FROM article WHERE MATCH(title, content) AGAINST ('查詢字符串');

 

注意事項

搜索必須在類型為fulltext的索引列上，match中指定的列必須在fulltext中指定過
僅能應用在表引擎為MyIsam類型的表中（MySQL 5.6以後也可以用在Innodb表引擎中了）
僅能再char、varchar、text類型的列上面創建全文索引
像普通索引一樣，可以在定義表時指定，也可以在創建表後添加或者修改
對於一個大數量級記錄插入，向沒有索引的表中插入數據後創建索引比向有索引的數據表中插入的過程要快很多
搜索字符串必須是一個常量字符串，不能是表的列名
在搜索記錄的選擇性超過50%的時候，認為沒有匹配（只在自然搜索中限制）

1.5、驗證是否使用是索引

些處接1.3.這裡可以接上面多列索引，在這裡我已經加了一些數據進去，如下

可以用語句 EXPLAIN SELECT * FROM t_student WHERE STU_AGE = 12;來驗證是否使用到了索引

下面說明用到了索引

如果沒用到索引，結果應該是如下：

上面只是驗證是否使用了索引，接下來看來看看使用了索引和沒使用索引的結果：

使用了索引的結果：（注意，這裡添加了CREATE INDEX index_age_aex ON t_student(STU_AGE,STU_SEX);）

結果按SEX和AGE來進行排序

把索引給刪除了，執行同樣的語句：

結果直接按ID進行排序

1.6、使用ALTER 命令添加和刪除索引

有四種方式來添加數據表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出現多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):該語句指定了索引為 FULLTEXT ，用於全文索引。

以下實例為在表中添加索引。

 ALTER TABLE testalter_tbl ADD INDEX (c);

你還可以在 ALTER 命令中使用 DROP 子句來刪除索引。嘗試以下實例刪除索引:

ALTER TABLE testalter_tbl DROP INDEX (c);

使用 ALTER 命令添加和刪除主鍵
主鍵只能作用於一個列上，添加主鍵索引時，你需要確保該主鍵默認不為空（NOT NULL）。實例如下：

 ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令刪除主鍵：

 ALTER TABLE testalter_tbl DROP PRIMARY KEY;

刪除指定時只需指定PRIMARY KEY，但在刪除索引時，你必須知道索引名。
顯示索引信息
你可以使用 SHOW INDEX 命令來列出表中的相關的索引信息。可以通過添加 \G 來格式化輸出信息。
嘗試以下實例:

SHOW INDEX FROM table_name\G

 

三、索引的不足之處

上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：

1.雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。

2.建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。

索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

四、使用索引的注意事項

使用索引時，有以下一些技巧和注意事項：

1.索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為NULL。

2.使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

3.索引列排序

MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。

4.like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

5.不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成:

select * from users where adddate<‘2007-01-01';

6.不使用NOT IN和<>操作