本文實例講述了MySQL數(shù)據(jù)庫優(yōu)化之索引實現(xiàn)原理與用法。分享給大家供大家參考，具體如下：

索引

什么是索引

索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式保存。如果沒有索引，執(zhí)行查詢時MySQL必須從第一個記錄開始掃描整個表的所有記錄，直至找到符合要求的記錄。表里面的記錄數(shù)量越多，這個操作的代價就越高。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引，MySQL無需掃描任何記錄即可迅速得到目標記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。

索引的分類

主鍵索引

主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。如果你曾經(jīng)用過AUTO_INCREMENT類型的列，你可能已經(jīng)熟悉主鍵之類的概念了。主鍵一般在創(chuàng)建表的時候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我們也可以通過修改表的方式加入主鍵，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每個表只能有一個主鍵。

創(chuàng)建主鍵索引

主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。如果你曾經(jīng)用過AUTO_INCREMENT類型的列，你可能已經(jīng)熟悉主鍵之類的概念了。主鍵一般在創(chuàng)建表的時候指定，例如“CREATE TABLE tablename ( […], PRIMARY KEY (列的列表) ); ”。但是，我們也可以通過修改表的方式加入主鍵，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每個表只能有一個主鍵。

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

create table aaa
(id int unsigned primary key auto_increment ,
name varchar(32) not null default '');

這是id 列就是主鍵索引.

create table bbb (id int , name varchar(32) not null default '');

如果你創(chuàng)建表時，沒有指定主鍵索引，也可以在創(chuàng)建表后，在添加, 指令:

實例:

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

刪除主鍵索引

alter table articles drop primary key;

查詢索引

desc 表名; 不能顯示索引名稱
show index from 表名
show keys from 表名

全文索引

創(chuàng)建表結構

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
   )engine=myisam charset utf8;
INSERT INTO articles (title,body) VALUES
   ('MySQL Tutorial','DBMS stands for DataBase ...'),
   ('How To Use MySQL Well','After you went through a ...'),
   ('Optimizing MySQL','In this tutorial we will show ...'),
   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
   ('MySQL vs. YourSQL','In the following database comparison ...'),
   ('MySQL Security','When configured properly, MySQL ...');

錯誤用法:

select * from articles where body like '%mysql%'; 錯誤用法 索引不會生效

正確用法:

select * from articles where match(title,body) against ( 'database')

說明:

1.在mysql中fulltext 索引只針對 myisam生效
2.mysql自己提供的fulltext針對英文生效->sphinx (coreseek) 技術處理中文
3.使用方法是 match(字段名…) against('關鍵字')
4.全文索引：停止詞, 因為在一個文本中，創(chuàng)建索引是一個無窮大的數(shù)，因此，對一些常用詞和字符，就不會創(chuàng)建，這些詞，稱為停止詞.比如（a，b，mysql，the）

mysql> select match(title,body) against ('database') from articles;（輸出的是每行和database的匹配度）

唯一索引

這種索引和前面的“普通索引”基本相同，但有一個區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一。唯一性索引可以用以下幾種方式創(chuàng)建：

創(chuàng)建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；

創(chuàng)建表的時候指定索引，例如CREATE TABLE tablename ( […], UNIQUE [索引的名字] (列的列表) )；

創(chuàng)建表結構

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

注意

unique字段可以為NULL,并可以有多NULL, 但是如果是具體內(nèi)容，則不能重復，

但是不能存有重復的空字符串''

普通索引

普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對數(shù)據(jù)的訪問速度。因此，應該只為那些最經(jīng)常出現(xiàn)在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的數(shù)據(jù)列創(chuàng)建索引。只要有可能，就應該選擇一個數(shù)據(jù)最整齊、最緊湊的數(shù)據(jù)列（如一個整數(shù)類型的數(shù)據(jù)列）來創(chuàng)建索引。

create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);

索引的實現(xiàn)原理

數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結構，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用 B 樹及其變種 B+ 樹。
在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結構，這些數(shù)據(jù)結構以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結構上實現(xiàn)高級查找算法。這種數(shù)據(jù)結構，就是索引。
為表設置索引要付出代價的：一是增加了數(shù)據(jù)庫的存儲空間，二是在插入和修改數(shù)據(jù)時要花費較多的時間(因為索引也要隨之變動)。

上圖展示了一種可能的索引方式。左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快 Col2 的查找，可以維護一個右邊所示的二叉查找樹，每個節(jié)點分別包含索引鍵值和一個指向對應數(shù)據(jù)記錄物理地址的指針，這樣就可以運用二叉查找在 O(log2n)的復雜度內(nèi)獲取到相應數(shù)據(jù)。

創(chuàng)建索引可以大大提高系統(tǒng)的性能。

第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。
第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
第三，可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。
第四，在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

也許會有人要問：增加索引有如此多的優(yōu)點，為什么不對表中的每一個列創(chuàng)建一個索引呢？因為，增加索引也有許多不利的方面。

第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。
第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

索引是建立在數(shù)據(jù)庫表中的某些列的上面。在創(chuàng)建索引的時候，應該考慮在哪些列上可以創(chuàng)建索引，在哪些列上不能創(chuàng)建索引。一般來說，應該在這些列上創(chuàng)建索引：在經(jīng)常需要搜索的列上，可以加快搜索的速度；在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結構；在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間；在經(jīng)常使用在 WHERE 子句中的列上面創(chuàng)建索引，加快條件的判斷速度。

同樣，對于有些列不應該創(chuàng)建索引。一般來說，不應該創(chuàng)建索引的的這些列具有下列特點：

第一，對于那些在查詢中很少使用或者參考的列不應該創(chuàng)建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求。
第二，對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。
第三，對于那些定義為 text, image 和 bit 數(shù)據(jù)類型的列不應該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。
第四，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。

根據(jù)數(shù)據(jù)庫的功能，可以在數(shù)據(jù)庫設計器中創(chuàng)建三種索引：唯一索引、主鍵索引和聚集索引。

唯一索引

唯一索引是不允許其中任何兩行具有相同索引值的索引。
當現(xiàn)有數(shù)據(jù)中存在重復的鍵值時，大多數(shù)數(shù)據(jù)庫不允許將新創(chuàng)建的唯一索引與表一起保存。數(shù)據(jù)庫還可能防止添加將在表中創(chuàng)建重復鍵值的新數(shù)據(jù)。例如，如果在 employee 表中職員的姓(lname)上創(chuàng)建了唯一索引，則任何兩個員工都不能同姓。主鍵索引數(shù)據(jù)庫表經(jīng)常有一列或列組合，其值唯一標識表中的每一行。該列稱為表的主鍵。在數(shù)據(jù)庫關系圖中為表定義主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對數(shù)據(jù)的快速訪問。聚集索引在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個表只能包含一個聚集索引。
如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。與非聚集索引相比，聚集索引通常提供更快的數(shù)據(jù)訪問速度。

局部性原理與磁盤預讀

由于存儲介質的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁盤 I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節(jié)，磁盤也會從這個位置開始，順序向后讀取一定長度的數(shù)據(jù)放入內(nèi)存。這樣做的理論依據(jù)是計算機科學中著名的局部性原理：當一個數(shù)據(jù)被用到時，其附近的數(shù)據(jù)也通常會馬上被使用。程序運行期間所需要的數(shù)據(jù)通常比較集中。
由于磁盤順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對于具有局部性的程序來說，預讀可以提高 I/O 效率。
預讀的長度一般為頁（page）的整倍數(shù)。頁是計算機管理存儲器的邏輯塊，硬件及操作系統(tǒng)往往將主存和磁盤存儲區(qū)分割為連續(xù)的大小相等的塊，每個存儲塊稱為一頁（在許多操作系統(tǒng)中，頁得大小通常為 4k），主存和磁盤以頁為單位交換數(shù)據(jù)。當程序要讀取的數(shù)據(jù)不在主存中時，會觸發(fā)一個缺頁異常，此時系統(tǒng)會向磁盤發(fā)出讀盤信號，磁盤會找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁或幾頁載入內(nèi)存中，然后異常返回，程序繼續(xù)運行。

B-/+Tree 索引的性能分析

到這里終于可以分析 B-/+Tree 索引的性能了。
上文說過一般使用磁盤 I/O 次數(shù)評價索引結構的優(yōu)劣。先從 B-Tree 分析，根據(jù) B-Tree 的定義，可知檢索一次最多需要訪問 h 個節(jié)點。數(shù)據(jù)庫系統(tǒng)的設計者巧妙利用了磁盤預讀原理，將一個節(jié)點的大小設為等于一個頁，這樣每個節(jié)點只需要一次 I/O 就可以完全載入。為了達到這個目的，在實際實現(xiàn) B-Tree 還需要使用如下技巧：
每次新建節(jié)點時，直接申請一個頁的空間，這樣就保證一個節(jié)點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現(xiàn)了一個 node 只需一次 I/O。
B-Tree 中一次檢索最多需要 h-1 次 I/O（根節(jié)點常駐內(nèi)存），漸進復雜度為 O(h)=O(logdN)。一般實際應用中，出度 d 是非常大的數(shù)字，通常超過 100，因此 h 非常?。ㄍǔ２怀^ 3）。
而紅黑樹這種結構，h 明顯要深的多。由于邏輯上很近的節(jié)點（父子）物理上可能很遠，無法利用局部性，所以紅黑樹的 I/O 漸進復雜度也為 O(h)，效率明顯比 B-Tree 差很多。

綜上所述，用 B-Tree 作為索引結構效率是非常高的。

應該花時間學習 B-樹和 B+ 樹數(shù)據(jù)結構

1）B 樹

B 樹中每個節(jié)點包含了鍵值和鍵值對于的數(shù)據(jù)對象存放地址指針，所以成功搜索一個對象可以不用到達樹的葉節(jié)點。
成功搜索包括節(jié)點內(nèi)搜索和沿某一路徑的搜索，成功搜索時間取決于關鍵碼所在的層次以及節(jié)點內(nèi)關鍵碼的數(shù)量。
在 B 樹中查找給定關鍵字的方法是：首先把根結點取來，在根結點所包含的關鍵字 K1,…,kj 查找給定的關鍵字（可用順序查找或二分查找法），若找到等于給定值的關鍵字，則查找成功；否則，一定可以確定要查的關鍵字在某個 Ki 或 Ki+1 之間，于是取 Pi 所指的下一層索引節(jié)點塊繼續(xù)查找，直到找到，或指針 Pi 為空時查找失敗。

2）B+ 樹

B+ 樹非葉節(jié)點中存放的關鍵碼并不指示數(shù)據(jù)對象的地址指針，非也節(jié)點只是索引部分。所有的葉節(jié)點在同一層上，包含了全部關鍵碼和相應數(shù)據(jù)對象的存放地址指針，且葉節(jié)點按關鍵碼從小到大順序鏈接。如果實際數(shù)據(jù)對象按加入的順序存儲而不是按關鍵碼次數(shù)存儲的話，葉節(jié)點的索引必須是稠密索引，若實際數(shù)據(jù)存儲按關鍵碼次序存放的話，葉節(jié)點索引時稀疏索引。
B+ 樹有 2 個頭指針，一個是樹的根節(jié)點，一個是最小關鍵碼的葉節(jié)點。
所以 B+ 樹有兩種搜索方法：
一種是按葉節(jié)點自己拉起的鏈表順序搜索。
一種是從根節(jié)點開始搜索，和 B 樹類似，不過如果非葉節(jié)點的關鍵碼等于給定值，搜索并不停止，而是繼續(xù)沿右指針，一直查到葉節(jié)點上的關鍵碼。所以無論搜索是否成功，都將走完樹的所有層。
B+ 樹中，數(shù)據(jù)對象的插入和刪除僅在葉節(jié)點上進行。
這兩種處理索引的數(shù)據(jù)結構的不同之處：
a，B 樹中同一鍵值不會出現(xiàn)多次，并且它有可能出現(xiàn)在葉結點，也有可能出現(xiàn)在非葉結點中。而 B+ 樹的鍵一定會出現(xiàn)在葉結點中，并且有可能在非葉結點中也有可能重復出現(xiàn)，以維持 B+ 樹的平衡。
b，因為 B 樹鍵位置不定，且在整個樹結構中只出現(xiàn)一次，雖然可以節(jié)省存儲空間，但使得在插入、刪除操作復雜度明顯增加。B+ 樹相比來說是一種較好的折中。
c，B 樹的查詢效率與鍵在樹中的位置有關，最大時間復雜度與 B+ 樹相同(在葉結點的時候)，最小時間復雜度為 1(在根結點的時候)。而 B+ 樹的時候復雜度對某建成的樹是固定的?？梢話呙?的次方。

索引的代價

占用磁盤空間

對DML(update、delete、insert)語句的效率影響

增刪改會對索引影響，因為索引要重新整理。

存儲引擎 允許的索引類型
myisam btree
innodb btree
memory/yeap Hash,btree

那些列上適合添加索引

①查詢作為查詢條件字段應該創(chuàng)建索引
②唯一性太差的字段不適合單獨創(chuàng)建索引,即使頻繁

Select * from emp where sex='男'

③頻繁更新字段，也不要定義索引。
④不會出現(xiàn)在where語句的字段不要創(chuàng)建索引

總結:滿處一下條件的字段，才應該創(chuàng)建索引

①肯定在where條件經(jīng)常使用
②該字段的內(nèi)容不是唯一的幾個值
③字段內(nèi)容不是頻繁變化

索引的注意事項

創(chuàng)建一張表

新增dept 數(shù)據(jù)

create PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
 declare i int DEFAULT 0;
 set autocommit=0;
 REPEAT
 set i=i+1;
 insert into dept values ((start+i),rand_string(10),rand_string(8));
 UNTIL i =max_num
 end REPEAT;
 commit;
END
執(zhí)行
call insert_dept(100,10);

創(chuàng)建主鍵索引

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

創(chuàng)建一個聯(lián)合索引

alter table dept add index my_ind (dname,loc); // dname 左邊的列,loc就是右邊的列

注意:

1.對于創(chuàng)建的多列索引，如果不是使用第一部分，則不會創(chuàng)建索引。
explain select * from dept where loc='aaa'\G
就不會使用到索引
2.模糊查詢在like前面有百分號開頭會失效。
3. 如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有字段，都必須建立索引, 我們建議大家盡量避免使用or 關鍵字
4.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引。(添加時,字符串必須''), 也就是，如果列是字符串類型，就一定要用 '' 把他包括起來.
5.如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

查詢所用使用率

show status like 'handler_read%';

大家可以注意：

handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數(shù)。
handler_read_rnd_next:這個值越高，說明查詢低效。

更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關技巧匯總》

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。