資料庫 mysql 最佳化器原理

標籤：

 

 

　　MySQL查詢最佳化工具有幾個目標，但是其中最主要的目標是儘可能地使用索引，並且使用最嚴格的索引來消除儘可能多的資料行。

　　你的最終目標是提交SELECT語句尋找資料行，而不是排除資料行。最佳化器試圖排除資料行的原因在於它排除資料行的速度越快，那麼找到與條件匹配的資料行也就越快。

 

　　如何 更好的 利用索引：

　　　　1：盡量比較資料類型相同的資料列。當你在比較操作中使用索引資料列的時候，請使用資料類型相同的列。相同的資料類型比不同類型的效能要高一些。

　　　　　　例如，INT與BIGINT是不同的。CHAR(10)被認為是CHAR(10)或VARCHAR(10)，但是與CHAR(12)或VARCHAR(12)不同。如果你所比較的資料列的類型不同，那麼可以使用ALTER TABLE來修改其中一個，使它們的類型相匹配。

 

 

mysql最佳化，包括表資料類型選擇，sql語句最佳化，系統配置與維護最佳化

 

1、  表資料類型選擇

（1）能小就用小。表資料類型第一個原則是：使用能正確的表示和儲存資料的最短類型。這樣可以減少對磁碟空間、記憶體、cpu緩衝的使用。

（2）避免用NULL，這個也是網上最佳化技術博文傳的最多的一個。理由是額外增加位元組，還有使索引，索引統計和值更複雜。很多還忽略一

    個count(列)的問題，count(列)是不會統計列值為null的行數。更多關於NULL可參考：http://www.phpben.com/?post=71

（3）字串如何選擇char和varchar？一般phper能想到就是char是固定大小，varchar能動態儲存資料。這裡整理一下這兩者的區別：

屬性	Char	Varchar
範圍大小	最長字元數是255（不是位元組），不管什麼編碼，超過此值則自動截取255個字元儲存並沒有報錯。	65535個位元組，開始兩位儲存長度，超過255個字元，用2位儲存長度，否則1位，具體字元長度根據編碼來確定，如utf8 則字元最長是21845個
如何處理字串末尾空格	去掉末尾空格，取值出來比較的時候自動加上進行比較	Version<=4.1，字串末尾空格被刪掉，version>5.0則保留
儲存空間	固定空間，比喻char(10)不管字串是否有10個字元都分配10個字元的空間	Varchar內節約空間，但更新可能發生變化，若varchar(10),開始若儲存5個字元，當update成7個時有myisam可能把行拆開，innodb可能分頁，這樣開銷就增大
適用場合	適用於儲存很短或固定或長度相似字元，如MD5加密的密碼char(33)、暱稱char(8)等	當最大長度遠大於平均長度並且發生更新的時候。

注意當一些英文或資料的時候，最好用每個字元用位元組少的類型，如latin1

 

（4）整型、整形優先原則

Tinyint、smallint、mediumint、int、bigint，分別需要8、16、24、32、64。

範圍範圍：-2^(n-1)~ 2^(n-1)-1

很多程式員在設計資料表的時候很習慣的用int，壓根不考慮這個問題

筆者建議：能用tinyint的絕不用smallint

誤區：int(1) 和int(11)是一樣的，唯一區別是mysql用戶端顯示的時候顯示多少位。

int(2)  unsigned  => 存入2 即為 2
int(2) unsigned zerofil => 存入2 即為 02 

整形優先原則：能用整形的不用其他類型替換，如ip可以轉換成整形儲存，如商品價格‘50.00元’則儲存成50

（5）精確度與空間的轉換。在儲存相同數值範圍的資料時，浮點數類型通常都會比DECIMAL類型使用更少的空間。FLOAT欄位使用4位元組儲存

資料。DOUBLE類型需要8 個位元組並擁有更高的精確度和更大的數值範圍，DECIMAL類型的資料將會轉換成DOUBLE類型。

 

 

2、  sql語句最佳化

1. mysql> create table test (
2. id smallint(10) not null auto_increment primary key,
3. username char(8) not null,
4. password char(4) not null,
5. `level` tinyint (1) default 0,
6. last_login char(15) not null,
7. index(username,password,last_login))engine=innodb;

這是test表，其中id是主鍵，多列索引(username,password,last_login),裡面有10000多條資料.

(1)    最左首碼原則

定義：最左首碼原則指的的是在sql where 字句中一些條件或運算式中出現的列的順序要保持和多索引的一致或以多列索引順序出現，只要出現非順序出現、斷層都無法利用到多列索引。

舉例說明：上面給出一個多列索引(username,password,last_login)，當三列在where中出現的順序如(username,password,last_login)、(username,password)、(username)才能用到索引，如下面幾個順序(password,last_login)、(passwrod)、(last_login)—這三者不從username開始，(username,last_login)—斷層，少了password，都無法利用到索引。

因為B+tree多列索引儲存的順序是按照索引建立的順序，檢索索引時按照此順序檢索

測試：以下測試不精確，這裡只是說明如何才能正確按照最左首碼原則使用索引。還有的是以下的測試用的時間0.00sec看不出什麼時間區別，因為資料量只有20003條，加上沒有在實體機上運行，很多未可預知的影響因素都沒考慮進去。當在大資料量，高並發的時候，最左首碼原則對與提高效能方面是不可否認的。

Ps：最左首碼原則中where字句有or出現還是會遍曆全表

(1.1)能正確的利用索引

l  Where子句運算式順序是(username)

1. mysql> explain select * from one where username=‘abgvwfnt’;
2. +—-+————-+——-+——+—————+———-+———+——-+——+————-+
3. | id | select_type | table | type | possible_keys | key      | key_len | ref   |rows | Extra       |
4. +—-+————-+——-+——+—————+———-+———+——-+——+————-+
5. |  1 | SIMPLE      | one   | ref  | username      | username | 24      | const |5 | Using where |
6. +—-+————-+——-+——+—————+———-+———+——-+——+————-+
7. 1 row in set (0.00 sec)

l  Where子句運算式順序是(username,password)

1. mysql> explain select * from one where username=‘abgvwfnt’ and password=’123456′;
2. +—-+————-+——-+——+—————+———-+———+————-+——+————-+
3. | id | select_type | table | type | possible_keys | key      | key_len | ref | rows | Extra       |
4. +—-+————-+——-+——+—————+———-+———+————-+——+————-+
5. |  1 | SIMPLE      | one   | ref  | username      | username | 43      | const,const |    1 | Using where |
6. +—-+————-+——-+——+—————+———-+———+————-+——+————-+
7. 1 row in set (0.00 sec)

l  Where子句運算式順序是(username,password, last_login)

1. mysql> explain select * from one where username=‘abgvwfnt’ and password=’123456′andlast_login=’1338251170′;
2. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
3. | id | select_type | table | type | possible_keys | key      | key_len | ref| rows | Extra       |
4. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
5. |  1 | SIMPLE   | one   | ref  | username     | username | 83      | const,const,const |    1 | Using where|
6. +—-+————-+——-+——+—————+———-+———+——————-+——+————-+
7. 1 row in set (0.00 sec)

上面可以看出type=ref 是多列索引，key_len分別是24、43、83，這說明用到的索引分別是(username), (username,password), (username,password, last_login );row分別是5、1、1檢索的資料行都很少，因為這三個查詢都按照索引首碼原則，可以利用到索引。

 

(2)    Order by 最佳化

(3)    隔離列：如id+1=2 => id = 1

(4)    OR、IN、UNION ALL，可以嘗試用UNION ALL

 

　　(4.1)or會遍曆表就算有索引

　　(4.2)對於in，這個是有爭議的，網上很多最佳化方案中都提到盡量少用in，這不全面，其實在in裡面如果是常量的話，可一大膽的用in

　　(4.3)UNION All 直接返回並集，可以避免去重的開銷。之所說“嘗試”用UNION All 替代 OR來最佳化sql語句，因為這不是一直能最佳化的了，這裡只是作為一個方法去嘗試

(5)    索引選擇性

索引選擇性是不重複的索引值也叫基數（cardinality）表中資料行數的比值，索引選擇性=基數/資料行，基數可以通過“show index from 表名”查看。

高索引選擇性的好處就是mysql尋找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1。

那麼對於非唯一索引或者說要被建立索引的列的資料內容很長，那就要選擇索引首碼。這裡就簡單說明一下：

1. mysql> select count(distinct(username))/count(*)  from one;
2. +————————————+
3. | count(distinct(username))/count(*) |
4. +————————————+
5. |                             0.2047 |
6. +————————————+
7. 1 row in set (0.09 sec)

count(distinct(username))/count(*)就是索引選擇性的值，這裡0.2太小了。

　　

(6)    重複或多餘索引

很多phper開始都以為建索引相對多點效能就好點，壓根沒考慮到有些索引是重複的，比如建一個(username),(username,password), (username,password,last_login),很明顯第一個索引是重複的，因為後兩者都能滿足其功能。

要有個意識就是，在滿足功能需求的情況下建最少索引。對於INNODB引擎的索引來說，每次修改資料都要把主鍵索引，輔助索引中相應索引值修改，這可能會出現大量資料移轉，分頁，以及片段的出現。

 

3、系統配置與維護最佳化

(1)    重要的一些變數

l  key_buffer_size索引塊緩衝區大小, 針對MyISAM儲存引擎,該值越大,效能越好.但是超過作業系統能承受的最大值,反而會使mysql變得不穩定. —-這是很重要的參數

l  sort_buffer_size 這是索引在排序緩衝區大小，若排序資料大小超過該值，則建立臨時檔案，注意和myisam_sort_buffer_size的區別—-這是很重要的參數

l  read_rnd_buffer_size當排序後按排序後的順序讀取行時，則通過該緩衝區讀取行，避免搜尋硬碟。將該變數設定為較大的值可以大大改進ORDER BY的效能。但是，這是為每個用戶端分配的緩衝區，因此你不應將全域變數設定為較大的值。相反，只為需要運行大查詢的用戶端更改會話變數

l  join_buffer_size用於表間關聯(join)的緩衝大小

l  tmp_table_size緩衝表的大小

l  table_cache允許 MySQL 開啟的表的最大個數，並且這些都cache在記憶體中

l  delay_key_write針對MyISAM儲存引擎,延遲更新索引.意思是說,update記錄時,先將資料up到磁碟,但不up索引,將索引存在記憶體裡,當表關閉時,將記憶體索引,寫到磁碟

更多參數查看http://www.phpben.com/?post=70

(2)    optimize、Analyze、check、repair維護操作

l  optimize 資料在插入，更新，刪除的時候難免一些資料移轉，分頁，之後就出現一些片段，久而久之片段積累起來影響效能，這就需要DBA週期性最佳化資料庫減少片段，這就通過optimize命令。

如對MyisAM表操作：optimize table 表名

對於InnoDB表是不支援optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當然也可以通過命令：alter table one type=innodb; 來替代。

l  Analyze用來分析和儲存表的關鍵字的分布，使得系統獲得準確的統計資訊，影響 SQL 的執行計畫的產生。對於資料基本沒有發生變化的表，是不需要經常進行表分析的。但是如果表的資料量變化很明顯，使用者感覺實際的執行計畫和預期的執行計畫不同的時候，執行一次表分析可能有助於產生預期的執行計畫。

Analyze table 表名

l  Check檢查表或者視圖是否存在錯誤，對 MyISAM 和 InnoDB 儲存引擎的表有作用。對於 MyISAM 儲存引擎的表進行表檢查，也會同時更新關鍵字統計資料

l  Repair optimize需要有足夠的硬碟空間，否則可能會破壞表，導致不能操作，那就要用上repair，注意INNODB不支援repair操作

以上的操作出現的都是如下這是check

1. +———-+——-+————–+————-+
2. | Table  | Op  | Msg_type| Msg_text |
3. +———-+——-+————–+————-+
4. | test.one | check | status  | OK     |
5. +———-+——-+————–+————-+

其中op是option 可以是repair check optimize，msg_type 表示資訊類型，msg_text 表示資訊類型，這裡就說明表的狀態正常。如在innodb表使用repair就出現note | The storage engine for the table doesn’t support repair

注意：以上操作最好在資料庫訪問量最低的時候操作，因為涉及到很多表鎖定，掃描，資料移轉等操作，否則可能導致一些功能無法正常使用甚至資料庫崩潰。

(3)表結構的更新與維護

l  改表結構。當要在資料量千萬級的資料表中使用alter更改表結構的時候，這是一個棘手問題。一種方法是在低並發低訪問量的時候用平常的alter更改表。另外一種就是建另一個與要修改的表，這個表除了要修改的結構屬性外其他的和原表一模一樣，這樣就能得到一個相應的.frm檔案，然後用flush with read lock 鎖定讀，然後覆蓋用建立的.frm檔案覆蓋原表的.frm，最後unlock table 釋放表。

l  建立新的索引。一般方法這裡不說。

1、  建立沒索引的a表，匯入資料形成.MYD檔案。

2、  建立包括索引b表，形成.FRM和.MYI檔案

3、  鎖定讀寫

4、  把b表的.FRM和.MYI檔案改成a表名字

5、  解鎖

6、  用repair建立索引。

這個方法對於大表也是很有效。這也是為什麼很多dba堅持說“先導資料庫在建索引，這樣效率更快”

l  定期檢查mysql伺服器

定期使用show status、show processlist等命令檢查資料庫。這裡就不細說，這說起來也篇幅是比較大的，筆者對這個也不是很瞭解

第四部分：圖說mysql查詢執行流程

1、  查詢快取，判斷sql語句是否完全符合，再判斷是否有許可權，兩個判斷為假則到解析器解析語句，為真則提取資料結果返回給使用者。

2、  解析器解析。解析器先詞法分析，文法分析，檢查錯誤比如引號有沒閉合等，然後產生解析樹。

3、  預先處理。預先處理解決解析器無法決解的語義，如檢查表和列是否存在，別名是否有錯，產生新的解析樹。

4、  最佳化器做大量的最佳化操作。

5、  產生執行計畫。

6、  查詢執行引擎，負責調度引擎擷取相應資料

7、  返回結果。

 

 

轉載地址：http://ourmysql.com/archives/1171

 

http://blog.chinaunix.net/uid-25311424-id-3957863.html

 

資料庫 mysql 最佳化器原理