添加索引優化器更高效率地執行語句
假設我們有兩個資料表t1和t2，每個有1000行，包含的值從1到1000。下面的查詢查找出兩個表中值相同的資料行：
mysql> select t1.i1, t2.i2 from t1, t2 where t1.i1 = t2.i2;
+------+------+
| i1 | i2 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
兩個表都沒有索引的時候，explain産生下面的結果：
mysql> explain select t1.i1, t2.i2 from t1, t2 where t1.i1 = t2.i2\g
*************************** 1. row ***************************
id: 1
select_type: simple
table: t1
type: all
possible_keys: null
key: null
key_len: null
ref: null
rows: 1000
extra:
*************************** 2. row ***************************
table: t2
extra: using whe
類型列中的all表明要進行檢查所有資料行的全表掃描。可能鍵列中的null表明沒有找到用于提高查詢速度的備選索引（鍵、鍵長度和參考列都是null也是因為缺少合适的索引）。using where表明使用where子句中的資訊來識别合格的資料行。 這段資訊告訴我們，優化器沒有為提高執行查詢的效率找到任何有用的資訊：
它将對t1表進行全表掃描。
對于t1中的每一行，它将執行t2的全表掃描，使用where子句中的資訊識别出合格的行。
行數值顯示了優化器估計的每個階段查詢需要檢查的行數。t1的估計值是1000，因為1000可以完成全表掃描。相似地，t2的估計值也是1000，但是這個值是對于t1的每一行的。換句話說，優化器所估計的處理該查詢所需要檢查的資料行組合的數量是1000×1000，也就是一百萬。這會造成很大的浪費 ，因為實際上隻有1000個組合符合where子句的條件。
為了使這個查詢的效率更高，給其中一個聯結列添加索引 并重新執行explain語句：
mysql> alter table t2 add index (i2);
type: ref
possible_keys: i2
key: i2
key_len: 5
ref: sampdb.t1.i1
rows: 10
extra: using where; using index
我們可以看到性能提高了。t1的輸出沒有改變（表明還是需要進行全表掃描），但是優化器處理t2的方式就有所不同了：
類型從all改變為ref，意味着可以使用參考值（來自t1的值）來執行索引查找，定位t2中合格的資料行。
參考值在參考（ref）字段中給出了：sampdb.t1.i1。
數值從1000降低到了10，顯示出優化器相信對于t1中的每一行，它隻需要檢查t2中的10行（這是一個悲觀的估計值。實際上，在t2中隻有一行與 t1中資料行比對。我們在後面會看到如何幫助優化器改善這個估計值）。資料行組合的全部估計值使1000×10＝10000。它比前面的沒有索引的時候估 計出來的一百萬好多了。
對t1進行索引有價值嗎？實際上，對于這個特定的聯結操作，掃描一張表是必要的，是以沒有必要對t1建立索引。如果你想看到效果，可以索引t1.i1并再次運作explain：
mysql> alter table t1 add index (i1);
type: index
possible_keys: i1
key: i1
extra: using index
上面的輸出與前面的explain的輸出相似，但是添加索引對t1的輸出有一些改變。類型從null改成了index，附加（extra）從空的改成了 using index。這些改變表明，盡管對索引的值仍然需要執行全表掃描，但是優化器還是可以直接從索引檔案中讀取值，根據不需要使用資料檔案。你可以從 myisam表中看到這類結果，在這種情況下，優化器知道自己隻詢問索引檔案就能夠得到所有需要的資訊。對于innodb 和bdb表也有這樣的結果，在這種情況下優化器可以單獨使用索引中的資訊而不用搜尋資料行。
我們可以運作analyze table使優化器進一步優化估計值。這會引起伺服器生成鍵值的靜态分布。分析上面的表并再次運作explain得到了更好的估計值：
mysql> analyze table t1, t2;
rows: 1
extra: using where; using inde
在這種情況下，優化器估計在t2中與t1的每個值比對的資料行隻有一個。
mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b where a . catid = b . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | a | all | null | null | null | null | 46585 | |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
2 rows in set ( 0.00 sec )
這個是我們經常使用的一種查詢方式，對b表的聯接類型使用了eq_ref，索引使用了primary，但是對于a表，卻沒有使用任何索引，這可能不是我們想要的。
檢視以上sql語句，我們可能會想到，有必要給a.catid加個索引了。
mysql > alter table jos_content add index idx_catid ( ` catid ` ) ;
query ok , 46585 rows affected ( 0.75 sec )
records : 46585 duplicates : 0 warnings : 0
| 1 | simple | a | all | idx_catid | null | null | null | 46585 | |
這樣表a便使用了idx_catid索引。下面我們做一次三個表的聯合查詢
mysql > explain select a . id , a . title , b . title from jos_content a , jos_categories b , jos_sections c where a . catid = b . id and a . sectionid = c . id ;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | c | index | primary | primary | 4 | null | 2 | using index |
| 1 | simple | a | all | idx_catid | null | null | null | 46585 | using where ; using join buffer |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
3 rows in set ( 0.00 sec )
這裡顯示了mysql先将c表讀入查詢，并使用primary索引，然後聯合a表進行查詢，這時候type顯示的是all，可以用的索引有idx_catid，但是實際沒有用。
原因非常明顯，因為使用的連接配接條件是a.sectionid=c.id，是以我們給a.sectionid加個索引先。
mysql > alter table jos_content add index idx_section ( ` sectionid ` ) ;
query ok , 46585 rows affected ( 0.89 sec )
+----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | c | index | primary | primary | 4 | null | 2 | using index |
| 1 | simple | a | ref | idx_catid , idx_section | idx_section | 4 | joomla_test . c . id | 23293 | using where |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | using where |
這時候顯示結果告訴我們，效果很明顯，在連接配接a表時type變成了ref，索引使用了idx_section，如果我們注意看後兩列，對a表的查詢結果後一次明顯少了一半左右，而且沒有用到join buffer。
這個表讀入的順序是mysql優化器幫我們做的，可以得知，用記錄數少的表做為基礎表進行聯合，将會得到更高的效率。
對于上面的語句，我們換一種寫法
mysql > explain select a . id , a . title , b . title from jos_content a left join jos_categories b on a . catid = b . id left join jos_sections c on a . sectionid = c . id ;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
| 1 | simple | a | all | null | null | null | null | 46585 | |
| 1 | simple | b | eq_ref | primary | primary | 4 | joomla_test . a . catid | 1 | |
| 1 | simple | c | eq_ref | primary | primary | 4 | joomla_test . a . sectionid | 1 | using index |
mysql讀入表的順序被改變了，這意味着，如果我們用left join來做連接配接查詢，mysql會按sql語句中表出現的順序讀入，還有一個有變化的地方是聯接b和c的type都變成了eq_ref，前邊我們說過， 這樣說明mysql可以找到唯一的行，這個效率是比ref要高的。
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)