**目錄**
[TOC]
數據庫主要的功能是幫你存儲資料,而且要可以很方便的讓你隨時查詢或者維護數據。但是在數據庫運行一段時間,尤其是里面存儲了大量數據的時候,你常發現在查詢或維護數據的時候,要等待比較長的時間。所以數據庫除了儲存數據外,效率的問題也是很重要的。數據庫在關于效率上的問題會比較復雜一些,跟軟、硬體還有網路都有關,這裡只會討論跟數據庫有關的部份,而且會是比較基礎的概念。
查詢數據算是資料庫中最常執行的工作,想要讓查詢數據的性能可以好一點,查詢敘述本身就很重要。另外也可以依照需求建立增加效率的索引,建立正確的索引可以提高查詢工作的效率;索引也可以在某些修改與刪除工作上看到效果。
儲存引擎在性能上也是一個很重要的因素,你會考慮資料庫的大小與種類,還有使用者的數量,然后選擇一個適合的儲存引擎。
# 1 索引
## 1.1 索引的種類
主索引鍵的應用很常見,而且一個表格通常會有一個,而且只能有一個。在一個表格中,設定為主索引鍵的欄位值不可以重復,而且不可以儲存「NULL」值。因為這樣的限制,所以很適合使用在類似編碼、代號或身份證字號這類欄位。
唯一索引也稱為「不可重復索引」,在一個表格中,設定為唯一索引的欄位值不可以重複,但是可以儲存「NULL」值。這種索引適合用在類似員工資料表格中儲存電子郵件帳號的欄位,因為員工不一定有電子郵件帳號,所以允許儲存「NULL」值,可以每一個員工的電子郵件帳號都不可以重復。
非唯一索引用來增加查詢與維護資料效率的索引。設定為非唯一索引的欄位值可以重復,也可以儲存「NULL」值。
「FULLTEXT」索引只能用在「CHAR」、「VARCHAR」與「TEXT」型態的欄位,而且表格使用的儲存引擎必須是「MyISAM」,一般會稱為「全文檢索」,可以提高搜索大量文字的性能。
「SPATIAL」索引是「SPATIAL」型態欄位專用的,而且表格使用的儲存引擎必須是「MyISAM」。「FULLTEXT」與「SPATIAL」這兩種索引不會在這裡討論。
注:建立與管理索引的方式,在「表格與索引」中討論。
## 1.2 建立需要的索引
索引有兩個主要的用途:主索引鍵與唯一索引可以避免重復的資料;主索引鍵、唯一索引與非唯一索引都可以增加資料庫的效率。如果需要為了增加效率而建立索引的話,你可以使用下列最基本的原則:
[](http://box.kancloud.cn/2015-07-17_55a91a2eb0413.png)
除了使用在「WHERE」子句中判斷條件的欄位,還有「ORDER BY」與「GROUP BY」子句中指定的欄位,也都可以使用建立索引來增加效率。不過建立這樣的索引的前提,還是你的表格會儲存比較大量的資料,如果表格的資料量不大的話,建立索引反而會浪費儲存的空間,效率也增加不多,而且還會讓執行新增或修改時的效率變差。
如果想要為了增加效率而建立索引的話,你應該要考慮下列幾點:
* 最重要的,當然是不要建立沒有必要的索引,例如上列討論的情況
* 索引的欄位儘量不要有「NULL」值
* 雖然某個欄位很常使用在「WHERE」、「ORDER BY」或「GROUP BY」子句中,也不一定要建立索引。例如性別欄位的值只有兩種(使用ENUM(‘M’, ‘F’)型態),建立索引所增加的效率也不多
* 主索引鍵與唯一索引的效率會比非唯一索引好
## 1.3 建立部份內容的索引
下列是一個用來示范用的表格,它可以儲存一般的個人資料,在建立表格的時候,就先把身份證字號的欄位設定為主索引鍵:
[](http://box.kancloud.cn/2015-07-17_55a91a32506ec.png)
在使用這個表格一段時間以后,如果儲存的資料量很大,而且又很常使用姓名與地址欄位執行條件的判斷,你應該會幫它們建立下列的索引:
[](http://box.kancloud.cn/2015-07-17_55a91a3e99b2b.png)
為姓名欄位建立索引是比較沒有問題的,不過地址欄位的長度有255個字元,這樣的索引是比較沒有效率的,而且你應該比較不會執行所有地址的條件判斷,如果比較經常執行的條件判斷,是類似「某某縣某某市」的話,其實你只要建立部份內容的索引就好了:
[](http://box.kancloud.cn/2015-07-17_55a91a3f33d4a.png)
雖然建立部份內容的索引可以減少索引的大小,不過你還要注意之前討論的原則,就是建立索引的欄位值不應該有太多重復的值。以上列建立的索引來說,為地址欄位的前六個字元建立索引的話,應該就會有很多重復的值。所以你應該先「分析」表格中的資料:
[](http://box.kancloud.cn/2015-07-17_55a91a41cbde9.png)
上列的敘述可以知道地址欄位是不是有很多重復的資料,為了建立部份內容的索引前,你也可以先使用下列的查詢敘述來確認:
[](http://box.kancloud.cn/2015-07-17_55a91a42ca398.png)
如果上列的查詢結果,確認地址欄位的前六個字元有很多重復的資料,你可以增加字元的數量后再查詢,直到你可以接受的數量后,再使用這個數量來建立部份內容的索引。
# 2 判斷條件的設定
如果想要查詢一個表格所有的資料,你就不會使用「WHERE」設定查詢條件,那就只能請資料庫讀取表格中所有的資料后傳回來,有沒有索引就不會有效率上的影響。不過如果使用「WHERE」子句設定查詢條件的話,就要儘量使用索引來增加查詢的效率。以下列的表格來說:
[](http://box.kancloud.cn/2015-07-17_55a91a438ef5e.png)
雖然你為生日欄位建立了索引,如果你在索引欄位使用函式或運算式的話:
[](http://box.kancloud.cn/2015-07-17_55a91a45b59b5.png)
下列的敘述就會使用索引,雖然比較長一些,不過它執行的效率會比上列的敘述好一些:
[](http://box.kancloud.cn/2015-07-17_55a91a492e536.png)
MySQL資料庫在下列的情況下,都會自動幫你執行轉換的工作:
[](http://box.kancloud.cn/2015-07-17_55a91a49b0e90.png)
雖然上列的查詢敘述在執行后也可以傳回你想要的資料,不過MySQL在處理每一筆資料的時候,都要幫你執行一次轉換的工作,這樣的寫法是很沒有效率的。所以你要盡可能避免這樣的情形:
[](http://box.kancloud.cn/2015-07-17_55a91a4a2155c.png)
另外在關聯式數據庫的設計下,你應該會很常執行類似下列敘述的結合查詢:
[](http://box.kancloud.cn/2015-07-17_55a91a556da45.png)
結合查詢是一種很沒有效率的查詢,因為數據庫要比對兩個表格中,結合條件所設定的字段值,如果資料數量很多的話,這樣的比對工作就會花很多時間。所以你通常會幫結合條件中的字段建立索引,以上列的查詢來說,國家表格的“Code”字段已經是主索引鍵;而城市表格的“CountryCode”并沒有建立索引,為了增加結合查詢的效率,你可以建立下列的索引:
[](http://box.kancloud.cn/2015-07-17_55a91a5632a6c.png)
如果經常使用國家名稱執行條件判斷的話,你可能會幫它建立一個索引:
[](http://box.kancloud.cn/2015-07-17_55a91a56eac46.png)
使用完整的國家名稱執行條件判斷的話,因為使用索引執行搜尋,所以效率會比較好一些。可是如果使用字串樣式執行條件判斷的話,就不一定會使用索引了:
[](http://box.kancloud.cn/2015-07-17_55a91a5776d1c.png)
有一些索引可能會包含多個字段:
[](http://box.kancloud.cn/2015-07-17_55a91a5d202e3.png)
在查詢的條件中,如果跟多個字段的索引有關的話,MySQL會依照索引字段的順序來決定是否使用索引。以上列的例子來說,主索引鍵的順序是CountryCode字段在前面,Language字段在后面,如果你的查詢條件只有使用Language字段的話,這個索引就不會生效:
[](http://box.kancloud.cn/2015-07-17_55a91a6210c60.png)
# 4 EXPLAIN與查詢敘述
MySQL數據庫提供“EXMPLIN”指令,可以讓你分析一個查詢敘述。以下列的查詢來說,你可以清楚的知道數據庫在執行這個查詢時后發生“full table scan”:
[](http://box.kancloud.cn/2015-07-17_55a91a6e2d44e.png)
下列的查詢敘述可以看出數據庫使用索引來傳回資料:
[](http://box.kancloud.cn/2015-07-17_55a91a6ef41fa.png)
如果是包含有子查詢的查詢敘述,“EXPLAIN”也會分別幫你執行分析的工作:
[](http://box.kancloud.cn/2015-07-17_55a91a7faa933.png)
使用“EXPLAIN”來檢查在這章討論索引的查詢敘述:
[](http://box.kancloud.cn/2015-07-17_55a91a860c195.png)
換成下列的查詢敘述后,“EXPLAIN”會告訴你數據庫使用索引來傳回資料:
[](http://box.kancloud.cn/2015-07-17_55a91a88c54fb.png)
# 5 資料維護
當你使用“INSERT”、“UPDATE”或“DELETE”敘述執行資料維護的工作時,也要注意效率上的問題。在執行修改或刪除資料的時候,除了要修改或刪除表格中所有的資料以外,你都會加入條件的設定。在“UPDATE”和“DELETE”敘述中使用“WHERE”子句設定條件時,跟查詢時候該注意的地方都一樣,除了盡量使用索引來增加執行的效率,也要避免不必要的資料轉換。
MySQL提供的“EXPLAIN”敘述,只可以為你分析一個查詢敘述,它不可以使用在“SELECT”以外的敘述。不過你也可以這樣作:
[](http://box.kancloud.cn/2015-07-17_55a91a8a07734.png)
MySQL提供使用一個“INSERT”敘述新增多筆資料的語法,如果你一次要新增多筆資料的話,使用這樣的方式新增資料會是比較有效率的:
[](http://box.kancloud.cn/2015-07-17_55a91a8acf48d.png)
# 6 LIMIT子句
在查詢和維護資料的時候,都有可能會使用“LIMIT”子句設定查詢或維護資料的數量。“LIMIT”子句在某些應用上是非常方便的,不過要特別注意在效率上的問題。以下列的例子來說:
[](http://box.kancloud.cn/2015-07-17_55a91a8c5ff09.png)
雖然這個查詢敘述只有傳回五筆資料,可以數據庫總共讀取了105筆資料,這樣的查詢會是比較沒有效率的。你可以使用索引與“ORDER BY”子句來增加效率:
[](http://box.kancloud.cn/2015-07-17_55a91a8cd0ddd.png)
# 7 使用暫時表格
在執行比較復雜的查詢工作時,在一個查詢敘述中,可能會有結合查詢、子查詢和其它復雜的判斷條件。一個看起來比較長而且比較復雜的查詢,效率并一定比較不好。不過你可以使用一些比較特別的方式,進一步改善查詢的復雜度與效率。以下列的查詢來說:
[](http://box.kancloud.cn/2015-07-17_55a91a8e9e10c.png)
上列的查詢是一個不算太復雜的結合查詢,如果還要在加上其它的條件判斷的話,看起來就會更長一些:
[](http://box.kancloud.cn/2015-07-17_55a91a8f24e6c.png)
如果還要再結合另外一個表格的話,這個查詢看起來就真的很復雜了:
[](http://box.kancloud.cn/2015-07-17_55a91a9077a82.png)
上列的查詢看起來雖然復雜,不過如果都有可以使用的索引,它執行的效率也會是不錯的。如果在查詢工作中,很常使用第一個查詢的結果,再加上不同的條件或結合,你就可以考慮使用下列的敘述,先建立好一個暫時的表格:
[](http://box.kancloud.cn/2015-07-17_55a91aa5227e4.png)
因為查詢的結果已經儲存在“countrycapital”表格中,所以要加入其它的條件就變得簡單多了:
[](http://box.kancloud.cn/2015-07-17_55a91aa697bac.png)
如果要再結合另外一個表格的話,也會比較容易:
[](http://box.kancloud.cn/2015-07-17_55a91aaa70824.png)
在“第九章、子查詢、FROM子句與子查詢”討論到可以把一個查詢放在“FROM”子句中:
[](http://box.kancloud.cn/2015-07-17_55a91aad0dc29.png)
使用這樣的方式雖然可以得到一樣的查詢結果,不過在你很常使用上列子查詢來增加條件的情況下,每次執行不同條件的查詢,數據庫都要重新執行子查詢敘述;先建立暫時的表格,再使用暫時表格執行查詢的作法會是比較有效率的。
# 8 儲存引擎
MySQL數據庫是一種允許多個用戶端同時使用的數據庫管理系統,在多用戶端的的運作環境下,數據庫就使用“鎖定、Locking”來避免資料的混亂:
[](http://box.kancloud.cn/2015-07-17_55a91ab1415d4.png)
MySQL提供的“MyISAM”和“InnoDB”兩種儲存引擎,使用不同的鎖定方式來處理上列的情況。MyISAM使用的是“table-level”的鎖定方式:
[](http://box.kancloud.cn/2015-07-17_55a91ac32d074.png)
MyISAM儲存引擎使用的“table-level”鎖定方式,適合使用在查詢工作非常多,資料維護比較少的數據庫,這樣的數據庫運作起來的效率會比較好。
InnoDB儲存引擎使用的是“row-level”的鎖定方式:
[](http://box.kancloud.cn/2015-07-17_55a91aca7906b.png)
InnoDB儲存引擎使用的“row-level”鎖定方式,適合使用在查詢與資料維護工作都差不多的數據庫,這樣的數據庫運作起來的效率會比較好。