本文主要講述了如何加速動態網站(的)MySQL索引分析和優化..
一、什麼是索引？
索引用來快速(地)尋找那些具有特定值(的)記錄,,所有MySQL索引都以B-樹(的)形式保存..如果沒有索引,,執行查詢時MySQL必須從第一個記錄開始掃描整個表(的)所有記錄,,直至找到符合要求(的)記錄..表裡面(的)記錄數量越多,,這個操作(的)代價就越高..如果作為搜索條件(的)列上已經創建了索引,,MySQL無需掃描任何記錄即可迅速(得)到目標記錄所在(的)位置..如果表有1000個記錄,,通過索引查找記錄至少要比順序掃描記錄快100倍..
假設我們們創建了一個名為people(的)表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL,
name CHAR(50) NOT NULL );
然後,,我們們完全隨機把1000個不同name值插入到people表..在數據文件中name列沒有任何明確(的)次序..如果我們們創建了name列(的)索引,,MySQL將在索引中排序name列,對於索引中(的)每一項,,MySQL在內部為它們保存一個數據文件中實際記錄所在位置(的)“指針”..因此,,如果我們們要查找name等於“Mike”記錄(的)peopleid（SQL命令為“SELECT peopleid FROM people WHERE name='Mike';”）,,MySQL能夠在name(的)索引中查找“Mike”值,,然後直接轉到數據文件中相應(的)行,,准確(地)返回該行(的)peopleid（999）..在這個過程中,,MySQL只需處理一個行就可以返回結果..如果沒有“name”列(的)索引,,MySQL要掃描數據文件中(的)所有記錄,,即1000個記錄！顯然,,需要MySQL處理(的)記錄數量越少,,則它們完成任務(的)速度就越快..
二、索引(的)類型
MySQL提供多種索引類型供選擇：
普通索引 :
這是最基本(的)索引類型,,而且它們沒有唯一性之類(的)限制..普通索引可以通過以下幾種方式創建：
創建索引,,例如CREATE INDEX <索引(的)名字> ON tablename (列(的)列表)；
修改表,,例如ALTER TABLE tablename ADD INDEX [索引(的)名字] (列(的)列表)；
創建表(的)時候指定索引,,例如CREATE TABLE tablename ( [...], INDEX [索引(的)名字] (列(的)列表) )；
唯一性索引:
這種索引和前面(的)“普通索引”基本相同,,但有一個區別：索引列(的)所有值都只能出現一次,,即必須唯一..唯一性索引可以用以下幾種方式創建：
創建索引,,例如CREATE UNIQUE INDEX <索引(的)名字> ON tablename (列(的)列表)；
修改表,,例如ALTER TABLE tablename ADD UNIQUE [索引(的)名字] (列(的)列表)；
創建表(的)時候指定索引,,例如CREATE TABLE tablename ( [...], UNIQUE [索引(的)名字] (列(的)列表) )；
主鍵 :
主鍵是一種唯一性索引,,但它們必須指定為“PRIMARY KEY”..如果你們曾經用過AUTO_INCREMENT類型(的)列,,你們可能已經熟悉主鍵之類(的)概念了..主鍵一般在創建表(的)時候指定,,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列(的)列表) ); ”..但是,,我們們也可以通過修改表(的)方式加入主鍵,,例如“ALTER TABLE tablename ADD PRIMARY KEY (列(的)列表); ”..每個表只能有一個主鍵..
全文索引:
MySQL從3.23.23版開始支持全文索引和全文檢索..在MySQL中,,全文索引(的)索引類型為FULLTEXT..全文索引可以在VARCHAR或者TEXT類型(的)列上創建..它們可以通過CREATE TABLE命令創建,,也可以通過ALTER TABLE或CREATE INDEX命令創建..對於大規模(的)數據集,,通過ALTER TABLE（或者CREATE INDEX）命令創建全文索引要比把記錄插入帶有全文索引(的)空表更快..本文下面(的)討論不再涉及全文索引,,要了解更多信息,,請參見MySQL documentation..
三、單列索引與多列索引
索引可以是單列索引,,也可以是多列索引..下面我們們通過具體(的)例子來說明這兩種索引(的)區別..假設有這樣一個people表：
CREATE TABLE people ( peopleid
SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );
下面是我們們插入到這個people表(的)數據：
這個數據片段中有四個名字為“Mikes”(的)人（其中兩個姓Sullivans,,兩個姓McConnells）,,有兩個年齡為17歲(的)人,,還有一個名字與眾不同(的)Joe Smith..
這個表(的)主要用途是根據指定(的)用戶姓、名以及年齡返回相應(的)peopleid..例如,,我們們可能需要查找姓名為Mike Sullivan、年齡17歲用戶(的)peopleid（SQL命令為SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17；）..由於我們們不想讓MySQL每次執行查詢就去掃描整個表,,這裡需要考慮運用索引..
首先,,我們們可以考慮在單個列上創建索引,,比如firstname、lastname或者age列..如果我們們創建firstname列(的)索引（ALTER TABLE people ADD INDEX firstname (firstname)；）,,MySQL將通過這個索引迅速把搜索范圍限制到那些firstname='Mike'(的)記錄,,然後再在這個“中間結果集”上進行其他們條件(的)搜索：它們首先排除那些lastname不等於“Sullivan”(的)記錄,,然後排除那些age不等於17(的)記錄..當記錄滿足所有搜索條件之後,,MySQL就返回最終(的)搜索結果..
由於建立了firstname列(的)索引,,與執行表(的)完全掃描相比,,MySQL(的)效率提高了很多,,但我們們要求MySQL掃描(的)記錄數量仍舊遠遠超過了實際所需要(的)..雖然我們們可以刪除firstname列上(的)索引,,再創建lastname或者age列(的)索引,,但總(地)看來,,不論在哪個列上創建索引搜索效率仍舊相似..
為了提高搜索效率,,我們們需要考慮運用多列索引..如果為firstname、lastname和age這三個列創建一個多列索引,,MySQL只需一次檢索就能夠找出正確(的)結果！下面是創建這個多列索引(的)SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)；
由於索引文件以B-樹格式保存,,MySQL能夠立即轉到合適(的)firstname,,然後再轉到合適(的)lastname,,最後轉到合適(的)age..在沒有掃描數據文件任何一個記錄(的)情況下,,MySQL就正確(地)找出了搜索(的)目標記錄！
那麼,,如果在firstname、lastname、age這三個列上分別創建單列索引,,效果是否和創建一個firstname、lastname、age(的)多列索引一樣呢？答案是否定(的),,兩者完全不同..當我們們執行查詢(的)時候,,MySQL只能使用一個索引..如果你們有三個單列(的)索引,,MySQL會試圖選擇一個限制最嚴格(的)索引..但是,,即使是限制最嚴格(的)單列索引,,它們(的)限制能力也肯定遠遠低於firstname、lastname、age這三個列上(的)多列索引..
四、最左前綴
多列索引還有另外一個優點,,它們通過稱為最左前綴（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';
The following querIEs cannot use the index at all:
SELECT peopleid FROM people WHERE lastname='Sullivan';
SELECT peopleid FROM people WHERE age='17';
SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';
五、選擇索引列
在性能優化過程中,,選擇在哪些列上創建索引是最重要(的)步驟之一..可以考慮使用索引(的)主要有兩種類型(的)列：在WHERE子句中出現(的)列,,在join子句中出現(的)列..請看下面這個查詢：
SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考慮使用索引
AND lastname='Sullivan' ## 考慮使用索引
這個查詢與前面(的)查詢略有不同,,但仍屬於簡單查詢..由於age是在SELECT部分被引用,,MySQL不會用它們來限制列選擇操作..因此,,對於這個查詢來說,,創建age列(的)索引沒有什麼必要..下面是一個更復雜(的)例子：
SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考慮使用索引
WHERE firstname='Mike' ##考慮使用索引
AND lastname='Sullivan' ##考慮使用索引
與前面(的)例子一樣,,由於firstname和lastname出現在WHERE子句中,,因此這兩個列仍舊有創建索引(的)必要..除此之外,,由於town表(的)townid列出現在join子句中,,因此我們們需要考慮創建該列(的)索引..那麼,,我們們是否可以簡單(地)認為應該索引WHERE子句和join子句中出現(的)每一個列呢？差不多如此,,但並不完全..我們們還必須考慮到對列進行比較(的)操作符類型.. MySQL只有對以下操作符才使用索引：<,,<=,,=,,>,,>=,,BETWEEN,,IN,,以及某些時候(的)LIKE..可以在LIKE操作中使用索引(的)情形是指另一個操作數不是以通配符（%或者_）開頭(的)情形..例如,,“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”這個查詢將使用索引,,但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”這個查詢不會使用索引..
