http://ipfw.info/mt/archives/000067.html
MySQL 最佳化 #1
MySQL 資料庫
運行一段時日之後
資料表裡面的資料應該累積不少了吧...
一般建議建立個 shell script
放在凌晨的時候自行做最佳化的動作
以維持一定程度效能
通常兩種作法
本篇先講最簡易的方式
舉 ibf 的例子
只需要針對 ibf 通常最大的兩塊資料表進行最佳化即可
既不浪費時間
又可得到不錯結果
#!/bin/sh
/usr/local/bin/mysql -uroot -ppassword cliews_ibf << EOF
optimize table ibf_members;
optimize table ibf_posts;
由於 optimize 的指令必須在 mysql 介面下執行
所以需要回應 << EOF 來對 mysql 進行答覆
由 LEi 發表於 May 19, 2005 02:38 PM
MySQL 最佳化
【分享】MySQL索引分析和最佳化+JOIN的分類
http://www.helzone.com/vbb/archive/inde ... 30003.html
join :
左右合併
inner join : 只顥示符合修件的資料列 (左右互相比對)
left join : 顥示符合條件的右資料列及左邊不符合條件的資料列 (此時右邊的資料會以 NULL 顯示)
right join : 顥示符合條件的左資料列及左邊不符合條件的資料列 (此時左邊的資料會以 NULL 顯示)
full join : 顥示符合條件的料列及左邊+右邊不符合條件的資料列 (此時缺乏資料的資料列會以 NULL 顯示)
cross join : 直接將一個資料表的每一筆資料列和另一個料表的每一筆資料列搭配成新的資料列
seif-joins : 自己join 自己
更多 :
union : 合併多個查尋結果 (上下垂直合併)
subquery : 子查尋
index :
索引可以加快查尋速度，以平衡樹結構存放索引資料 ．
例如：
當我們要找編碼N的記錄時,可從根節點開始往下找,
假設有M層則只要找M次,但是如果不設索引則要找N次 !!
唯一索引(非叢集索引 : UNIQUE INDEX) :
會依單一欄位以順序的方式做排序放在前一個記錄的後面
資料表中的任何索引值都不可以相同.有點像PRIMARY KEY .
復合索引(叢集索引 : COMPOSITE INDEX) :
如果是唯一索引又是復合索引則多個欄位組合起來的值
不可以重復而單一欄位則可以重復.
###############################
MySQL索引分析和優化 (完整介紹)
--------------------------------------------------------------------------------
作者:松下客 來源:賽迪網
一、什麼是索引？
索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式保存。如果沒有索引，執行查詢時MySQL必須從第一個記錄開始掃瞄整個表的所有記錄，直至找到符合要求的記錄。表裡面的記錄數量越多，這個操作的代價就越高。如果作為搜尋條件的列上已經建立了索引，MySQL無需掃瞄任何記錄即可迅速得到目標記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃瞄記錄快100倍。
假設我們建立了一個名為people的表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
對於索引中的每一項，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 <W> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
建立表的時候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
這種索引和前面的「普通索引」基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一。唯一性索引可以用以下幾種方式建立：
建立索引，例如CREATE UNIQUE INDEX <W> 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) );
這個資料片段中有四個名字為「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只有對以下操作符才使用索引：<，<A>，>=，BETWEEN，IN，以及某些時候的LIKE。可以在LIKE操作中使用索引的情形是指另一個操作數不是以通配符（%或者_）開頭的情形。例如，「SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';」這個查詢將使用索引，但「SELECT peopleid FROM people WHERE firstname LIKE '%ike';」這個查詢不會使用索引。
六、分析索引效率
現在我們已經知道了一些如何選擇索引列的知識，但還無法判斷哪一個最有效。MySQL提供了一個內建的SQL命令幫助我們完成這個任務，這就是EXPLAIN命令。EXPLAIN命令的一般語法是：EXPLAIN <SQL>。你可以在MySQL文檔找到有關該命令的更多說明。下面是一個例子：
EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';
這個命令將返回下面這種分析結果：
tabletypepossible_keyskeykey_lenrefrowsExtra
people ref fname_lname_age fname_lname_age 102 const,const,const 1 Where used
下面我們就來看看這個EXPLAIN分析結果的含義。
table：這是表的名字。
type：連接操作的類型。下面是MySQL文檔關於ref連接類型的說明：
「對於每一種與另一個表中記錄的組合，MySQL將從當前的表讀取所有帶有匹配索引值的記錄。如果連接操作只使用鍵的最左前綴，或者如果鍵不是UNIQUE或PRIMARY KEY類型（換句話說，如果連接操作不能根據鍵值選擇出唯一行），則MySQL使用ref連接類型。如果連接操作所用的鍵只匹配少量的記錄，則ref是一種好的連接類型。」
在本例中，由於索引不是UNIQUE類型，ref是我們能夠得到的最好連接類型。
如果EXPLAIN顯示連接類型是「ALL」，而且你並不想從表裡面選擇出大多數記錄，那麼MySQL的操作效率將非常低，因為它要掃瞄整個表。你可以加入更多的索引來解決這個問題。預知更多信息，請參見MySQL的手冊說明。
possible_keys：
可能可以利用的索引的名字。這裡的索引名字是建立索引時指定的索引暱稱；如果索引沒有暱稱，則預設顯示的是索引中第一個列的名字（在本例中，它是「firstname」）。預設索引名字的含義往往不是很明顯。
Key：
它顯示了MySQL實際使用的索引的名字。如果它為空（或NULL），則MySQL不使用索引。
key_len：
索引中被使用部分的長度，以字節計。在本例中，key_len是102，其中firstname占50字節，lastname占50字節，age占2字節。如果MySQL只使用索引中的firstname部分，則key_len將是50。
ref：
它顯示的是列的名字（或單詞「const」），MySQL將根據這些列來選擇行。在本例中，MySQL根據三個常量選擇行。
rows：
MySQL所認為的它在找到正確的結果之前必須掃瞄的記錄數。顯然，這裡最理想的數字就是1。
Extra：
這裡可能出現許多不同的選項，其中大多數將對查詢產生負面影響。在本例中，MySQL只是提醒我們它將用WHERE子句限制搜尋結果集。
七、索引的缺點
到目前為止，我們討論的都是索引的優點。事實上，索引也是有缺點的。
首先，索引要佔用硬碟空間。通常情況下，這個問題不是很突出。但是，如果你建立每一種可能列組合的索引，索引文件體積的增長速度將遠遠超過資料文件。如果你有一個很大的表，索引文件的大小可能達到操作系統允釭熙怳j文件限制。
第二，對於需要寫入資料的操作，比如DELETE、UPDATE以及INSERT操作，索引會降低它們的速度。這是因為MySQL不僅要把改動資料寫入資料文件，而且它還要把這些改動寫入索引文件。
【結束語】在大型資料庫中，索引是提高速度的一個關鍵因素。不管表的結構是多麼簡單，一次500000行的表掃瞄操作無論如何不會快。如果你的網站上也有這種大規模的表，那麼你確實應該花些時間去分析可以採用哪些索引，並考慮是否可以改寫查詢以優化應用。要瞭解更多信息，請參見MySQL manual。另外注意，本文假定你所使用的MySQL是3.23版，部分查詢不能在3.22版MySQL上執行。
join :
左右合併
inner join : 只顥示符合修件的資料列 (左右互相比對)
left join : 顥示符合條件的右資料列及左邊不符合條件的資料列 (此時右邊的資料會以 NULL 顯示)
right join : 顥示符合條件的左資料列及左邊不符合條件的資料列 (此時左邊的資料會以 NULL 顯示)
full join : 顥示符合條件的料列及左邊+右邊不符合條件的資料列 (此時缺乏資料的資料列會以 NULL 顯示)
cross join : 直接將一個資料表的每一筆資料列和另一個料表的每一筆資料列搭配成新的資料列
seif-joins : 自己join 自己
更多 :
union : 合併多個查尋結果 (上下垂直合併)
subquery : 子查尋
index :
索引可以加快查尋速度，以平衡樹結構存放索引資料 ．
例如：
當我們要找編碼N的記錄時,可從根節點開始往下找,
假設有M層則只要找M次,但是如果不設索引則要找N次 !!
唯一索引(非叢集索引 : UNIQUE INDEX) :
會依單一欄位以順序的方式做排序放在前一個記錄的後面
資料表中的任何索引值都不可以相同.有點像PRIMARY KEY .
復合索引(叢集索引 : COMPOSITE INDEX) :
如果是唯一索引又是復合索引則多個欄位組合起來的值
不可以重復而單一欄位則可以重復.
###############################
MySQL索引分析和優化 (完整介紹)
--------------------------------------------------------------------------------
作者:松下客 來源:賽迪網
一、什麼是索引？
索引用來快速地尋找那些具有特定值的記錄，所有MySQL索引都以B-樹的形式保存。如果沒有索引，執行查詢時MySQL必須從第一個記錄開始掃瞄整個表的所有記錄，直至找到符合要求的記錄。表裡面的記錄數量越多，這個操作的代價就越高。如果作為搜尋條件的列上已經建立了索引，MySQL無需掃瞄任何記錄即可迅速得到目標記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃瞄記錄快100倍。
假設我們建立了一個名為people的表：
CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );
對於索引中的每一項，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 <W> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
建立表的時候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
唯一性索引
這種索引和前面的「普通索引」基本相同，但有一個區別：索引列的所有值都只能出現一次，即必須唯一。唯一性索引可以用以下幾種方式建立：
建立索引，例如CREATE UNIQUE INDEX <W> 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) );
這個資料片段中有四個名字為「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只有對以下操作符才使用索引：<，<A>，>=，BETWEEN，IN，以及某些時候的LIKE。可以在LIKE操作中使用索引的情形是指另一個操作數不是以通配符（%或者_）開頭的情形。例如，「SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';」這個查詢將使用索引，但「SELECT peopleid FROM people WHERE firstname LIKE '%ike';」這個查詢不會使用索引。
六、分析索引效率
現在我們已經知道了一些如何選擇索引列的知識，但還無法判斷哪一個最有效。MySQL提供了一個內建的SQL命令幫助我們完成這個任務，這就是EXPLAIN命令。EXPLAIN命令的一般語法是：EXPLAIN <SQL>。你可以在MySQL文檔找到有關該命令的更多說明。下面是一個例子：
EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';
這個命令將返回下面這種分析結果：
tabletypepossible_keyskeykey_lenrefrowsExtra
people ref fname_lname_age fname_lname_age 102 const,const,const 1 Where used
下面我們就來看看這個EXPLAIN分析結果的含義。
table：這是表的名字。
type：連接操作的類型。下面是MySQL文檔關於ref連接類型的說明：
「對於每一種與另一個表中記錄的組合，MySQL將從當前的表讀取所有帶有匹配索引值的記錄。如果連接操作只使用鍵的最左前綴，或者如果鍵不是UNIQUE或PRIMARY KEY類型（換句話說，如果連接操作不能根據鍵值選擇出唯一行），則MySQL使用ref連接類型。如果連接操作所用的鍵只匹配少量的記錄，則ref是一種好的連接類型。」
在本例中，由於索引不是UNIQUE類型，ref是我們能夠得到的最好連接類型。
如果EXPLAIN顯示連接類型是「ALL」，而且你並不想從表裡面選擇出大多數記錄，那麼MySQL的操作效率將非常低，因為它要掃瞄整個表。你可以加入更多的索引來解決這個問題。預知更多信息，請參見MySQL的手冊說明。
possible_keys：
可能可以利用的索引的名字。這裡的索引名字是建立索引時指定的索引暱稱；如果索引沒有暱稱，則預設顯示的是索引中第一個列的名字（在本例中，它是「firstname」）。預設索引名字的含義往往不是很明顯。
Key：
它顯示了MySQL實際使用的索引的名字。如果它為空（或NULL），則MySQL不使用索引。
key_len：
索引中被使用部分的長度，以字節計。在本例中，key_len是102，其中firstname占50字節，lastname占50字節，age占2字節。如果MySQL只使用索引中的firstname部分，則key_len將是50。
ref：
它顯示的是列的名字（或單詞「const」），MySQL將根據這些列來選擇行。在本例中，MySQL根據三個常量選擇行。
rows：
MySQL所認為的它在找到正確的結果之前必須掃瞄的記錄數。顯然，這裡最理想的數字就是1。
Extra：
這裡可能出現許多不同的選項，其中大多數將對查詢產生負面影響。在本例中，MySQL只是提醒我們它將用WHERE子句限制搜尋結果集。
七、索引的缺點
到目前為止，我們討論的都是索引的優點。事實上，索引也是有缺點的。
首先，索引要佔用硬碟空間。通常情況下，這個問題不是很突出。但是，如果你建立每一種可能列組合的索引，索引文件體積的增長速度將遠遠超過資料文件。如果你有一個很大的表，索引文件的大小可能達到操作系統允釭熙怳j文件限制。
第二，對於需要寫入資料的操作，比如DELETE、UPDATE以及INSERT操作，索引會降低它們的速度。這是因為MySQL不僅要把改動資料寫入資料文件，而且它還要把這些改動寫入索引文件。
【結束語】在大型資料庫中，索引是提高速度的一個關鍵因素。不管表的結構是多麼簡單，一次500000行的表掃瞄操作無論如何不會快。如果你的網站上也有這種大規模的表，那麼你確實應該花些時間去分析可以採用哪些索引，並考慮是否可以改寫查詢以優化應用。要瞭解更多信息，請參見MySQL manual。另外注意，本文假定你所使用的MySQL是3.23版，部分查詢不能在3.22版MySQL上執行。
Re: MySQL 最佳化
http://www.mysqlperformanceblog.com/200 ... tallation/
key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.
key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time – it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.
innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.
innodb_additional_mem_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.
innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.
innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.
innodb_flush_log_at_trx_commit Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.
thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.
query_cache_size If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.
Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.