MySQL數(shù)據(jù)庫(kù)優(yōu)化之索引實(shí)現(xiàn)原理與用法分析

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

索引

什么是索引

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

索引的分類

主鍵索引

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

創(chuàng)建主鍵索引

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

當(dāng)一張表，把某個(gè)列設(shè)為主鍵的時(shí)候，則該列就是主鍵索引

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)建表時(shí)，沒(méi)有指定主鍵索引，也可以在創(chuàng)建表后，在添加, 指令:

實(shí)例:

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

刪除主鍵索引

alter table articles drop primary key;

查詢索引

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

全文索引

創(chuàng)建表結(jié)構(gòu)

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 ...');

錯(cuò)誤用法:

select * from articles where body like '%mysql%'; 錯(cuò)誤用法 索引不會(huì)生效

正確用法:

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

說(shuō)明:

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

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

唯一索引

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

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

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

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

創(chuàng)建表結(jié)構(gòu)

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

注意

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

但是不能存有重復(fù)的空字符串''

普通索引

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

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

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

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

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

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

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

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

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

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

同樣，對(duì)于有些列不應(yīng)該創(chuàng)建索引。一般來(lái)說(shuō)，不應(yīng)該創(chuàng)建索引的的這些列具有下列特點(diǎn)：

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

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

唯一索引

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

局部性原理與磁盤(pán)預(yù)讀

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

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

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

綜上所述，用 B-Tree 作為索引結(jié)構(gòu)效率是非常高的。

應(yīng)該花時(shí)間學(xué)習(xí) B-樹(shù)和 B+ 樹(shù)數(shù)據(jù)結(jié)構(gòu)

1）B 樹(shù)

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

2）B+ 樹(shù)

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

索引的代價(jià)

占用磁盤(pán)空間

對(duì)DML(update、delete、insert)語(yǔ)句的效率影響

增刪改會(huì)對(duì)索引影響，因?yàn)樗饕匦抡怼?/p>

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

那些列上適合添加索引

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

Select * from emp where sex='男'

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

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

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

索引的注意事項(xiàng)

創(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)建一個(gè)聯(lián)合索引

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

注意:

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

查詢所用使用率

show status like 'handler_read%';

大家可以注意：

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

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

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