MySql索引原理與使用大全，mysql索引大全

MySql索引原理與使用大全，mysql索引大全

   林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka    

一、索引介紹

     索引是對資料庫表中一列或多列的值進行排序的一種結構。在關聯式資料庫中，索引是一種與表有關的資料庫結構，它可以使對應於表的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和<>操作

著作權聲明：本文為博主林炳文Evankaka原創文章，未經博主允許不得轉載。