1.單列索引與組合索引執(zhí)行效率有差別么？

在firstname、lastname、age這三個列上分別創(chuàng)建單列索引，效果是否和創(chuàng)建一個firstname、lastname、age的多列索引一樣呢？當然不是的，兩者完全不同。

當我們執(zhí)行查詢的時候，MySQL只能使用一個索引。如果你有3個單列的索引，MySQL會試圖選擇一個限制最嚴格的索引。但即使是限制最嚴格的單列索引，它的限制能力也肯定遠遠低于firstname、lastname、age這3個列上的多列索引。

由于索引文件以B-樹格式保存，MySQL能夠立即轉(zhuǎn)到合適的firstname，然后再轉(zhuǎn)到合適的lastname，最后轉(zhuǎn)到合適的age。

在沒有掃描數(shù)據(jù)文件任何一個記錄的情況下，MySQL就正確地找出了搜索的目標記錄！

2.組合索引有什么好處？最左前綴是什么？

多列索引還有另外一個優(yōu)點，它通過稱為最左前綴（Leftmost Prefixing）的概念體現(xiàn)出來。

繼續(xù)考慮前面的例子，現(xiàn)在有一個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜索條件是以下各種列的組合時，MySQL將使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

它相當于我們創(chuàng)建了(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’;

3.怎樣選擇索引列？

在性能優(yōu)化過程中，選擇在哪些列上創(chuàng)建索引是最重要的步驟之一。可以考慮使用索引的主要有兩種類型的列：

• 在WHERE子句中出現(xiàn)的列；

• 在join子句中出現(xiàn)的列。

1）通常在where和join的判斷字段上，都建立索引。以方便查詢速度，在判斷條件列上使用索引，方便快定位記錄。

2）可以在一列或者多列創(chuàng)建索引。

• 如果經(jīng)常同時搜索兩列或多列或按兩列或多列排序時，建立組合索引會很大提高查詢速度。例如，如果經(jīng)常在同一查詢中為姓和名兩列設(shè)置判據(jù)，那么在這兩列上創(chuàng)建多列索引將很有意義。

3）在經(jīng)常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；

4）在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；

SELECT age ## 不使用索引

FROM people WHERE firstname=‘Mike’ ## 考慮使用索引

AND lastname=‘Sullivan’ ## 考慮使用索引

這個查詢與前面的查詢略有不同，但仍屬于簡單查詢。由于age是在SELECT部分被引用，MySQL不會用它來限制列選擇操作。

因此，對于這個查詢來說，創(chuàng)建age列的索引沒有什么必要。下面是一個更復雜的例子：

SELECT people.age, ##不使用索引

town.name ##不使用索引

FROM people LEFT JOIN town ON
people.townid=town.townid ##考慮使用索引

WHERE firstname=‘Mike’ ##考慮使用索引

AND lastname=‘Sullivan’ ##考慮使用索引

與前面的例子一樣，由于firstname和lastname出現(xiàn)在WHERE子句中，因此這兩個列仍舊有創(chuàng)建索引的必要。除此之外，由于town表的townid列出現(xiàn)在join子句中，因此我們需要考慮創(chuàng)建該列的索引。

那么，我們是否可以簡單地認為應該索引WHERE子句和join子句中出現(xiàn)的每一個列呢？差不多如此，但并不完全。我們還必須考慮到對列進行比較的操作符類型。MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。

可以在LIKE操作中使用索引的情形是指另一個操作數(shù)不是以通配符（%或者_）開頭的情形。例如，“SELECT peopleid FROM people WHERE firstname LIKE ‘Mich%’;”這個查詢將使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE ‘%ike’;”這個查詢不會使用索引。

4.哪些字段不適合加索引？

1、查詢中很少使用的列不應該創(chuàng)建索引；

2、對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度；

3、對于那些定義為text, image和bit數(shù)據(jù)類型的列不應該增加索引。這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少,不利于使用索引；

4、當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。

5.索引的返回結(jié)果是什么？

結(jié)果是一個中間結(jié)果集，數(shù)據(jù)庫根據(jù)中間結(jié)果集再去查找數(shù)據(jù)庫中的具體表的記錄。

6.具體查詢時會選擇使用哪個索引文件？

每次查詢只能使用一個索引，默認數(shù)據(jù)庫會選擇限制條件最嚴格的索引。

7.怎樣判斷是否使用了索引？及查看索引的使用性能？

使用explain命令。

EXPLAIN
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactlyasI expected)
NOTICE:QUERY PLAN:
Index Scan using mytable_categoryid_userid on
mytable(cost=0.00…2.02 rows=1 width=16)
EXPLAIN

以上是 postgres的數(shù)據(jù)，可以看到該數(shù)據(jù)庫在查詢的時候使用了一個索引，而且它使用的是創(chuàng)建的第二個索引?？吹缴厦婷暮锰幜税?，馬上就知道它使用適當?shù)乃饕恕?/p>

8.排序分組是怎么使用索引的？

索引默認是已經(jīng)自動排序的，所以在使用排序或者分組時候，用索引去查詢速度會很快，產(chǎn)生排序或分組的中間結(jié)果集，然后根據(jù)中間結(jié)果集定位具體的表中的記錄。

SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;

很簡單，就像為where子句中的字段建立一個索引一樣，也為ORDER BY的子句中的字段建立一個索引：
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);

注意:“mytable_categoryid_userid_adddate"將會被截短為"mytable_categoryid_userid_addda”

最后

最后祝愿你能成為一名優(yōu)秀的工程師！