效率

資料庫主要的功能是幫你儲存資料，而且要可以很方便的讓你隨時查詢或維護資料。但是在資料庫運作一段時間，尤其是裡面儲存了大量資料的時候，你常會發現在查詢或維護資料的時候，要等後比較長的時間。所以資料庫除了儲存資料外，效率的問題也是很重要的。資料庫在關於效率上的問題會比較複雜一些，跟軟、硬體還有網路都有關，這裡只會討論跟資料庫有關的部份，而且會是比較基礎的概念。

查詢資料算是資料庫中最常執行的工作，想要讓查詢資料的效率可以好一點，查詢敘述本身就很重要。另外也可以依照需求建立增加效率的索引，建立正確的索引可以提高查詢工作的效率；索引也可以在某些修改與刪除工作上看到效果。

儲存引擎在效率上也是一個很重要的因素，你會考慮資料庫的大小與種類，還有使用者的數量，然後選擇一個適合的儲存引擎。

1 索引

1.1 索引的種類

主索引鍵的應用很常見，而且一個表格通常會有一個，而且只能有一個。在一個表格中，設定為主索引鍵的欄位值不可以重複，而且不可以儲存「NULL」值。因為這樣的限制，所以很適合使用在類似編碼、代號或身份證字號這類欄位。

唯一索引也稱為「不可重複索引」，在一個表格中，設定為唯一索引的欄位值不可以重複，但是可以儲存「NULL」值。這種索引適合用在類似員工資料表格中儲存電子郵件帳號的欄位，因為員工不一定有電子郵件帳號，所以允許儲存「NULL」值，可以每一個員工的電子郵件帳號都不可以重複。

非唯一索引用來增加查詢與維護資料效率的索引。設定為非唯一索引的欄位值可以重複，也可以儲存「NULL」值。

「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」與「TEXT」型態的欄位，而且表格使用的儲存引擎必須是「MyISAM」，一般會稱為「全文檢索」，可以提高搜尋大量文字的效率。

「SPATIAL」索引是「SPATIAL」型態欄位專用的，而且表格使用的儲存引擎必須是「MyISAM」。「FULLTEXT」與「SPATIAL這兩種索引不會在這裡討論。

註：建立與管理索引的方式，在「表格與索引」中討論。

1.2 建立需要的索引

索引有兩個主要的用途：主索引鍵與唯一索引可以避免重複的資料；主索引鍵、唯一索引與非唯一索引都可以增加資料庫的效率。如果需要為了增加效率而建立索引的話，你可以使用下列最基本的原則：

除了使用在「WHERE」子句中判斷條件的欄位，還有「ORDER BY」與「GROUP BY」子句中指定的欄位，也都可以使用建立索引來增加效率。不過建立這樣的索引的前提，還是你的表格會儲存比較大量的資料，如果表格的資料量不大的話，建立索引反而會浪費儲存的空間，效率也增加不多，而且還會讓執行新增或修改時的效率變差。

如果想要為了增加效率而建立索引的話，你應該要考慮下列幾點：

最重要的，當然是不要建立沒有必要的索引，例如上列討論的情況
索引的欄位儘量不要有「NULL」值
雖然某個欄位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中，也不一定要建立索引。例如性別欄位的值只有兩種(使用ENUM(‘M’, ‘F’)型態)，建立索引所增加的效率也不多
主索引鍵與唯一索引的效率會比非唯一索引好

1.3 建立部份內容的索引

下列是一個用來示範用的表格，它可以儲存一般的個人資料，在建立表格的時候，就先把身份證字號的欄位設定為主索引鍵：

在使用這個表格一段時間以後，如果儲存的資料量很大，而且又很常使用姓名與地址欄位執行條件的判斷，你應該會幫它們建立下列的索引：

為姓名欄位建立索引是比較沒有問題的，不過地址欄位的長度有255個字元，這樣的索引是比較沒有效率的，而且你應該比較不會執行所有地址的條件判斷，如果比較經常執行的條件判斷，是類似「某某縣某某市」的話，其實你只要建立部份內容的索引就好了：

雖然建立部份內容的索引可以減少索引的大小，不過你還要注意之前討論的原則，就是建立索引的欄位值不應該有太多重複的值。以上列建立的索引來說，為地址欄位的前六個字元建立索引的話，應該就會有很多重複的值。所以你應該先「分析」表格中的資料：

上列的敘述可以知道地址欄位是不是有很多重複的資料，為了建立部份內容的索引前，你也可以先使用下列的查詢敘述來確認：

如果上列的查詢結果，確認地址欄位的前六個字元有很多重複的資料，你可以增加字元的數量後再查詢，直到你可以接受的數量後，再使用這個數量來建立部份內容的索引。

2 判斷條件的設定

如果想要查詢一個表格所有的資料，你就不會使用「WHERE」設定查詢條件，那就只能請資料庫讀取表格中所有的資料後傳回來，有沒有索引就不會有效率上的影響。不過如果使用「WHERE」子句設定查詢條件的話，就要儘量使用索引來增加查詢的效率。以下列的表格來說：

雖然你為生日欄位建立了索引，如果你在索引欄位使用函式或運算式的話：

下列的敘述就會使用索引，雖然比較長一些，不過它執行的效率會比上列的敘述好一些：

MySQL資料庫在下列的情況下，都會自動幫你執行轉換的工作：

雖然上列的查詢敘述在執行後也可以傳回你想要的資料，不過MySQL在處理每一筆資料的時候，都要幫你執行一次轉換的工作，這樣的寫法是很沒有效率的。所以你要儘可能避免這樣的情形：

另外在關聯式資料庫的設計下，你應該會很常執行類似下列敘述的結合查詢：

結合查詢是一種很沒有效率的查詢，因為資料庫要比對兩個表格中，結合條件所設定的欄位值，如果資料數量很多的話，這樣的比對工作就會花很多時間。所以你通常會幫結合條件中的欄位建立索引，以上列的查詢來說，國家表格的「Code」欄位已經是主索引鍵；而城市表格的「CountryCode」並沒有建立索引，為了增加結合查詢的效率，你可以建立下列的索引：

如果經常使用國家名稱執行條件判斷的話，你可能會幫它建立一個索引：

使用完整的國家名稱執行條件判斷的話，因為使用索引執行搜尋，所以效率會比較好一些。可是如果使用字串樣式執行條件判斷的話，就不一定會使用索引了：

有一些索引可能會包含多個欄位：

在查詢的條件中，如果跟多個欄位的索引有關的話，MySQL會依照索引欄位的順序來決定是否使用索引。以上列的例子來說，主索引鍵的順序是CountryCode欄位在前面，Language欄位在後面，如果你的查詢條件只有使用Language欄位的話，這個索引就不會生效：

4 EXPLAIN與查詢敘述

MySQL資料庫提供「EXMPLIN」指令，可以讓你分析一個查詢敘述。以下列的查詢來說，你可以清楚的知道資料庫在執行這個查詢時後發生「full table scan」：

下列的查詢敘述可以看出資料庫使用索引來傳回資料：

如果是包含有子查詢的查詢敘述，「EXPLAIN」也會分別幫你執行分析的工作：

使用「EXPLAIN」來檢查在這章討論索引的查詢敘述：

換成下列的查詢敘述後，「EXPLAIN」會告訴你資料庫使用索引來傳回資料：

5 資料維護

當你使用「INSERT」、「UPDATE」或「DELETE」敘述執行資料維護的工作時，也要注意效率上的問題。在執行修改或刪除資料的時候，除了要修改或刪除表格中所有的資料以外，你都會加入條件的設定。在「UPDATE」和「DELETE」敘述中使用「WHERE」子句設定條件時，跟查詢時候該注意的地方都一樣，除了儘量使用索引來增加執行的效率，也要避免不必要的資料轉換。

MySQL提供的「EXPLAIN」敘述，只可以為你分析一個查詢敘述，它不可以使用在「SELECT」以外的敘述。不過你也可以這樣作：