資料庫調優教程（五） 索引的作用和索引的種類，調優索引

資料庫調優教程（五） 索引的作用和索引的種類，調優索引
三、           索引

上一章我們學習了使用explain來產生一個查詢執行計畫（QEP），從而發現語句存在的問題。在explain返回的結果中，有三個是和索引有關的（possible key、key、extra），可見索引在改善查詢效率上的顯著地位。

這一章，我們將全方位講解如何使用索引來最佳化我們的資料庫。

1.      索引的作用

索引的原理是利用特殊的尋找演算法（如二叉樹演算法），限制訪問的行數，提高訪問效率。

另外，索引還可以高效地在表之間建立串連操作，高效的對結果進行排序等等

2.      基本索引種類及建立

索引可以分為主鍵索引、普通索引、唯一索引和全文索引。

1)    主鍵索引

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引

create table aaa(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’);

這是id 列就是主鍵索引.

如果你建立表時，沒有指定主鍵索引，也可以在建立表後再添加

alter table 表名 add primary key (列名);

2)    普通索引

一般來說，普通索引的建立，是先建立表，然後在建立普通索引

create index 索引名 on 表 (列名1,列名2);

當括弧中的列不止一個時，我們稱之為多列索引，多列索引在一定情況下可以進化為覆蓋索引（後面會介紹）。

這裡我們通過在emp表中建立普通索引來見識一下索引的威力。

在未建立索引前

我們執行以下語句

select count(1) from emp where ename like 'yu%';

接著我們建立索引

create index index_ename on emp(ename);

再次執行

select count(1) from emp where ename like 'yu%';

不可思議！

索引的威力可見一斑！

當然索引是有開銷的

這是我在未建立索引時mysql安裝目錄下data/bigtable/emp.ibd檔案的大小，對於innodb引擎的表來說，ibd檔案存放了表的資料和索引。

在我建立了索引之後

顯然，索引會佔用主機的空間，這也稱作索引的開銷。

3)    唯一索引

當表的某列被指定為unique約束時，這列就是一個唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

這時, name 列就是一個唯一索引

在建立表後，再去建立唯一索引

create unique index 索引名  on 表名 (列表..);

unique欄位可以為NULL,並可以有多NULL,即Null！=Null。但是如果是具體內容，則不能重複

4)    全文索引

全文索引，只對MyISAM引擎有用。主要是針對對檔案，文本的檢索, 比如文章或者段落,.

它會把某個資料表的某個資料列出現過的所有單詞產生一份清單

全文索引的建立

在建立表時建立

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;

或者在建立表後添加

ALTER TABLE articles ADD FULLTEXT (title,body);

全文索引正確的用法是:

select * from articles where match(title,body) against(‘要搜尋的單詞’);

另外，mysql自己提供的fulltext針對英文生效，想要搜尋中文需要使用sphinx全文檢索索引引擎或者使用加強版的模糊查詢。

注意：

全文索引不完全等同於模糊查詢

比如title欄位有這麼個資料’abcd20088ccaa’,使用模糊查詢select * from articleswhere title like’%2008%’可以尋找到，而使用全文檢索索引select * from articleswhere match(title) against(‘2008’);是檢索不到的，因為2008不是一個單詞！

本講結束，下一講將講解索引的相關操作。