MySQL 超新手入門(20)效率 by Michael | CodeData
top

MySQL 超新手入門(20)效率

分享:

專欄作者新書出版:Android App程式開發剖析 第三版(適用Android 8 Oreo與Android Studio 3)

MySQL 超新手入門(19)匯入與匯出資料 << 前情

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

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

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

1 索引

1.1 索引的種類

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

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

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

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

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

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

1.2 建立需要的索引

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

mysql_20_snap_01

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

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

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

1.3 建立部份內容的索引

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

mysql_20_snap_02

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

mysql_20_snap_03

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

mysql_20_snap_04

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

mysql_20_snap_05

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

mysql_20_snap_06

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

2 判斷條件的設定

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

mysql_20_snap_07

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

mysql_20_snap_08

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

mysql_20_snap_09

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

mysql_20_snap_10

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

mysql_20_snap_11

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

mysql_20_snap_12

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

mysql_20_snap_13

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

mysql_20_snap_14

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

mysql_20_snap_15

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

mysql_20_snap_16

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

mysql_20_snap_17

4 EXPLAIN與查詢敘述

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

mysql_20_snap_18

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

mysql_20_snap_19

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

mysql_20_snap_20

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

mysql_20_snap_21

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

mysql_20_snap_22

5 資料維護

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

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

mysql_20_snap_30

MySQL提供使用一個「INSERT」敘述新增多筆資料的語法,如果你一次要新增多筆資料的話,使用這樣的方式新增資料會是比較有效率的:

mysql_20_snap_31

6 LIMIT子句

在查詢和維護資料的時候,都有可能會使用「LIMIT」子句設定查詢或維護資料的數量。「LIMIT」子句在某些應用上是非常方便的,不過要特別注意在效率上的問題。以下列的例子來說:

mysql_20_snap_32

雖然這個查詢敘述只有傳回五筆資料,可以資料庫總共讀取了105筆資料,這樣的查詢會是比較沒有效率的。你可以使用索引與「ORDER BY」子句來增加效率:

mysql_20_snap_33

7 使用暫時表格

在執行比較複雜的查詢工作時,在一個查詢敘述中,可能會有結合查詢、子查詢和其它複雜的判斷條件。一個看起來比較長而且比較複雜的查詢,效率並一定比較不好。不過你可以使用一些比較特別的方式,進一步改善查詢的複雜度與效率。以下列的查詢來說:

mysql_20_snap_23

上列的查詢是一個不算太複雜的結合查詢,如果還要在加上其它的條件判斷的話,看起來就會更長一些:

mysql_20_snap_24

如果還要再結合另外一個表格的話,這個查詢看起來就真的很複雜了:

mysql_20_snap_25

上列的查詢看起來雖然複雜,不過如果都有可以使用的索引,它執行的效率也會是不錯的。如果在查詢工作中,很常使用第一個查詢的結果,再加上不同的條件或結合,你就可以考慮使用下列的敘述,先建立好一個暫時的表格:

mysql_20_snap_26

因為查詢的結果已經儲存在「countrycapital」表格中,所以要加入其它的條件就變得簡單多了:

mysql_20_snap_27

如果要再結合另外一個表格的話,也會比較容易:

mysql_20_snap_28

在「第九章、子查詢、FROM子句與子查詢」討論到可以把一個查詢放在「FROM」子句中:

mysql_20_snap_29

使用這樣的方式雖然可以得到一樣的查詢結果,不過在你很常使用上列子查詢來增加條件的情況下,每次執行不同條件的查詢,資料庫都要重新執行子查詢敘述;先建立暫時的表格,再使用暫時表格執行查詢的作法會是比較有效率的。

8 儲存引擎

MySQL資料庫是一種允許多個用戶端同時使用的資料庫管理系統,在多用戶端的的運作環境下,資料庫就使用「鎖定、Locking」來避免資料的混亂:

mysql_20_snap_34

MySQL提供的「MyISAM」和「InnoDB」兩種儲存引擎,使用不同的鎖定方式來處理上列的情況。MyISAM使用的是「table-level」的鎖定方式:

mysql_20_snap_35

MyISAM儲存引擎使用的「table-level」鎖定方式,適合使用在查詢工作非常多,資料維護比較少的資料庫,這樣的資料庫運作起來的效率會比較好。

InnoDB儲存引擎使用的是「row-level」的鎖定方式:

mysql_20_snap_36

InnoDB儲存引擎使用的「row-level」鎖定方式,適合使用在查詢與資料維護工作都差不多的資料庫,這樣的資料庫運作起來的效率會比較好。

分享:
按讚!加入 CodeData Facebook 粉絲群

相關文章

留言

留言請先。還沒帳號註冊也可以使用FacebookGoogle+登錄留言

kevin7001110/08

請問一下 6的LIMIT子句有個問題
原式:select*from country limit 100,5是從原本TABLE裡面抓取101筆到105筆
新式:select *from country order by Code limit 100,5
此為排序後抓第101筆到105筆.
這樣的兩種抓到的資料因該不一樣巴.

熱門論壇文章

熱門技術文章