**目錄** [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”鎖定方式，適合使用在查詢與資料維護工作都差不多的數據庫，這樣的數據庫運作起來的效率會比較好。