Mysql innodb儲存引擎的效能最佳化

ref：http://7567567.blog.51cto.com/706378/586925

Mysql innodb儲存引擎的效能最佳化

這個說得挺全，挺不錯！

[@more@]

一切都從應用程式設計開始

1. 通用應用程式的設計是至關重要的

1.1. 設計你的schema，索引和查詢，以及選擇正確的儲存引擎是常用的最佳化手段。

1.2. 在有些情況下儲存引擎的選擇會影響到schema和索引

1.3. 我們這裡不會覆蓋到一般的schema設計方法，但是會主要聚焦到Innodb儲存引擎。

2. 每個儲存引擎都是不同的

2.1. MySQl提供多種儲存引擎可供選擇

2.2. 它們每個都有不同的設計和操作屬性。

2.3. 一個給某個儲存引擎寫的應用程式可能在其它儲存引擎下表現良好。

2.4. 每個儲存引擎都有特定的最佳化方式，所以它們只對特定的設計模式有用。

2.5. 我們覆蓋所有對於InnoDB儲存引擎的做何不做。

3. 使用事務

3.1. InnoDB預設就是使用事務，甚至你不知道如何使用。 每句語句都將在自己的事務內（假設你執行“autocommit”模式 set autocommit=1），在每句語句後面 都會自動增加commit語句。

3.2. 把多條update語句包裝在同一個事務是更有效率的方式。（set autcommit=0;..comit;…commit;）。不能讓事務過長，這樣會造成死鎖和等待超時。

4. 不要使用鎖表(lock tables)

4.1. 鎖表（LOCK TABLES）是設計用來給那些表級鎖的儲存引擎。在行級鎖的儲存引擎中事務是更好的選擇。InnoDB的的鎖錶行為在不同的mysql 版本是不同的，如果你 從MySQL4.0或者更新的版本升級，那你依賴於innodb_table_locks這個選項會導致很多問題。

5. 主鍵簇

5.1. 主鍵是特殊的

5.1.1. 透過主鍵訪問資料比透過其它key訪問更快。無論是在記憶體還是磁碟透過主鍵查詢都是最快的。

5.1.2. 資料都由主鍵聚集的。連續的主鍵值很容易讓同一頁主鍵的資料進行排序同時首字查詢也會非常有效率。可用於把所有需要訪問資料集中在一 起。把使用者資訊儲存在一起可以使用（user_id,message_id）作為主鍵來儲存所有使用者的資訊在很多頁中。主鍵是一個替代索引對於任何欄位。

6. 主鍵的開銷

6.1. 主鍵在隨機排序是開銷比較大的，也會導致表產生碎片（主鍵的插入一般都是根據升序進行的）

6.1.1. 如果可以裝載資料都透過主鍵來進行排序的。

6.1.2. 有時候把主鍵設定成自動增長（auto_increment）是一個好主意

6.2. 如果你不指定，主鍵預設就是一個內在的聚集key。所以最好就定義一個並且使用它。

6.3. UPDATE PK開銷是非常大的

6.3.1. 行資料將會在索引中物理的從一個地方移動位置。

6.3.2. 通常這種需要在設計上進行避免。

7. 讓主鍵儘量短

7.1. 因為其它索引都是透過主鍵來構建索引的。

7.1.1. 使主鍵成為其它索引的一部分。

7.2. 長主鍵會讓你的索引變大和變慢

7.2.1. 你可以把主鍵變成唯一KEY，同時給主鍵新增自動增長。你不能簡單的讓InnoDB自己去建立它的內部主鍵，透過把主鍵變為唯一key因為MySQL會自動的轉換一個非空的的唯一key作為主鍵。

7.2.2. 如果一個表中只有主鍵，同時所有查詢都是透過主鍵進行，即使主鍵是比較長的，那透過主鍵進行查詢也是更快的。

8. InnoDB的索引

8.1. 讓唯一索引變的簡單

8.1.1. 不使用”insert buffer”會加快索引的更新。

8.2. 索引是不能做字首壓縮的

8.2.1. 所以它會比MyISAM引擎佔用更多的空間

8.2.2. 要儘量避免過多的索引

8.3. 對所有需要更新的列進行索引

8.3.1. 不然你將看到不願看到的lock問題。 DELETE FROM users WHERE name=’peter’, 如果沒有對name列進行索引的話就會鎖住表中所有的行。

9. 自動增加將限制可擴充套件性

9.1. 自動增長的插入可能會用到表級鎖（但只會在insert語句的最後部分，沒有事務的情況下）。即使你指定了自動增長列的值。

9.2. 對於併發插入將限制可可擴充套件性。

9.3. 會導致在執行中出現困難。

9.4. 導致超出MySQL所能分配的值。要特別小心那種非常長和隨機的主鍵。

10. 多版本

10.1. 只對需要的行進行lock將會獲得更好的併發效能。

10.2. 普通的SELECT不會進行lock操作，只會去讀適當的行。

10.2.1. Lock在共享模式下，UPDATE做更新操作時會對讀進行Lock。

10.3. 甚至長時間的select查詢不會阻止對於表的update或者select操作。

10.4. 過度的慢查詢（通常在事務內）對於效能是不好的，如導致版本的不一致性。READ COMMITTED能夠 減輕這種問題。

10.4.1. InnoDB只能隔離一個行版本當讀取這行的時候沒有事 務在執行。

11. 在共享模式下的…FOR UPDATE and LOCK

11.1. 在read commited模式下會進行select lock。因為不能不能lock一個不存在的行。所以這個跟普通的select是不同的。

11.2. SELECT…FOR UPDATE總是不得不訪問行資料頁進行lock，所以不能進行對這些查詢進行索引，就會減慢查詢的速度。

12. 減少死鎖

12.1. 在事務的資料庫中死鎖是普遍存在的。

12.1.1. 在你InnoDB中沒有鎖的select語句是不會導致死鎖的。

12.1.2. 在你的應用需要控制好你的死鎖時間。

12.2. 如果可能的話確認在事務中鎖住的資料就是你請求的那些。

12.3. 讓update資料變小（分離你的事務）

12.4. 使用SELECT…FOR UPDATE如果你想更新大部分你所選擇的行。

12.5. 使用外部鎖可以避免死鎖這個問題—應用程式級別的鎖，SELECT GET_LOCK(‘mylock’)等 等。

13. 隔離級別是如何影響效能的

13.1. InnoDB支援很多種的隔離級別。這些隔離級別可以設定為全域性有效 也可以針對每個連線和每個事務。

13.1.1. READ UMCOMMITED（不提交讀）— 這個是很少使用。如果你不想有髒資料產生那就可以很好的使用這個，但是會影響效能。

13.1.2. READ COMMITED（提角度）—所有提 交事務的結果對於下一條語句都是顯而易見的。可能比其他更高的隔離級別效能更高。允許老的內容更快的更新。在mysql5.1，InnoDB會有一些間歇鎖在這個級別上：使用行級複製 和binlog可以避免這個問題。

13.1.2.1. REPEATABLE READ（可重複讀）— 預設的隔離級別。事務內的讀都是完全可重複的，沒有幽靈行的產生。

13.1.2.2. SERIALIZABLE（序列化）– 讓所有select都鎖住select，儘可能避免使用這個隔離級別。

14. 外來鍵效能

14.1. 當更新行時候InnoDB都會檢查外來鍵，而且不會進行批處理或者當事務提交時候檢查延遲。外來鍵通常都有很多效能上的開銷，但 是這也保證了資料庫的連續性。

14.2. 外來鍵增加了很多行級鎖，這將會影響到很多其它表不光是自己直接更新的那張表。

14.3. 外來鍵會鎖住子表，當父表在更新的時候。（select … for update在父表上這樣執行就不會鎖住子表）

15. 執行中的事務中的約束數量

15.1. 在一定數量內的執行中的事務和執行查詢，InnoDB效能表現良好

15.1.1. 多個執行中查詢可能導致互相之間干擾。Innodb_thread_concurrency能夠被用作在InnoDB核心中限制執行緒數量。

15.1.2. 許多執行中事務會導致更多的鎖，同時造成機器負載增加。

15.1.3. 如果有可能，在同一時間內限制一定數量的查詢，在應用程式端做好佇列。

16. 注意不要有太多的表

16.1. InnoDB自己的表定義（字典）快取依賴於MySQL的table_cache變數值。

16.2. 只要開啟一次，InnoDB就不會從快取中移除這個表。

16.3. 每張表大概要消耗4KB以上的空間。MySQL 5.1 的 InnoDB已經將這個空間減少了50% 到 75%

16.4. 當重啟時，每個表的統計將會被重新計算。所以第一次操作會是非常耗資源的。MySQL的table_cache將會序列執行這些操作。

17. Insert…Select

17.1. Insert…Select語句執行時會對select進行鎖

17.2. 語句級別複製要求更新都是序列化的。在MysQL5.1 行級別更新在 READ COMMITED已經沒有問題了。

17.3. 無論什麼時候你啟用或者不啟用log-bin，都需要保持一致性。

17.4. Innodb_locks_unsafe_for_binlog在MySQL5.0是有幫助的，但是你的複製有可能會被中斷，同時會禁止next-key的鎖。

17.5. SELECT…INTO OUTFILE + LOAD DATA INFILE經常被用作non-blocking的安全替代。

18. Next key lock（間隙鎖）

18.1. Innodb不光會鎖使用到行，也會鎖這些行之間的行（稱為間隙 行）。

18.2. 這個是為了防止幽靈行的出現。 設定 “REPEATABLE READ”確實會讓InnoDB可重複的。

18.3. 對於MySQL語句級別的複製是很有必要的。

18.4. 會讓一些寫負載大的機器上增加鎖的情況。

18.5. 如果你沒有設定和使用二進位制log（用作複製和恢復的），那可以禁止這個間隙鎖。

18.6. 在MySQL5.1中，如果你使用行級複製就可以安全的進行修改。

19. Count（*）的事實和傳說

19.1. InnoDB不能很好的控制count(*)的查詢–這個只是傳聞。在所有引擎中大部分count(*)查詢都用相同的方式進行查詢。 select count(*) from articles Where user_id=5

19.2. 在缺少where字句的情況下，InnoDB不對count(*)查詢進行最佳化–這個是事實。如select count(*) from users; InnoDB不能簡單儲存行的計數，每個事務都有自己的表的檢視。因為有重要的工作還要去實現。你可以使用觸發 器和計數器。SHOW TABLE STATUS LIKE ”USERS” 可以顯示錶近似的行數。

20. InnoDB和集體提交

20.1. 集體提交–提交多個事務透過單個日誌寫。這個可以提高非常多的效能，特別是沒有做RAID的情況下。

20.2. 在MySQL5.0下，集體提交不能在有二進位制log的情況下工作。由於XA（分散式事務）方法被實現，特別要小心從MysqL4.1進行的升級。

回到基本的伺服器效能調優

1. 一切都從記憶體開始

1.1. InnoDB_buffer_pool_size

1.1.1. 詳細指定了主要InnoDB快取–資料和索引頁，插入快取，鎖都會存在這裡。

1.1.2. 在大資料集的情況下對於效能非常重要

1.1.3. 比OS級別的快取更有效的多，特別對於寫操作。InnoDB不得不去繞過OS的buffer去寫。

1.1.4. 最好使用70%–80%的系統記憶體作為InnoDB的buffer使用。

1.1.5. 預設值是8M，可用的獨立記憶體，要好好確認如何去配置。

1.2. InnoDB_additional_mem_pool

1.2.1. 僅僅儲存字典，它會自動增長，不用設定的太大。

2. InnoDB日誌

2.1. Innodb_log_file_size

2.1.1. 對於寫效能有非常重要的影響。要保持非常大。

2.1.2. 高的數值會增加你回覆的時間。檢查一下你能設定的最大的大小。

2.1.3. 最大的限制是4G。

2.2. Innodb_log_files_in_group

2.2.1. 這些檔案指定了對於Log所能使用的大小。

2.2.2. 通常不需要改變其預設值。

3. InnoDB日誌

3.1. Innodb_log_buffer_size

3.1.1. 不要設定超過2-9M，除非你使用大量的超大檔案，日誌檔案都會被重新整理在每秒執行完畢後。

3.1.2. 檢查innodb_os_log_written的增長來看你的日誌檔案的寫入。

3.1.3. Innodb日誌是物理邏輯的，不是基於頁的，所以他們是非常緊湊 的。

3.2. Innodb_flush_logs_at_trx_commit

3.2.1. 預設日誌被重新整理到磁碟上在每次事務提交後。這個是為了保證ACID（原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation，又稱獨立性）、永續性（Durability））， 所以開銷非常大。

3.2.2. 可以設定2和0，如果你能接受丟失最後一次的事務。

4. InnoDB日誌重新設定大小

4.1. 這個不是簡單修改選項和重啟下能夠完成的。

4.2. 首先要關閉MysQL伺服器

4.3. 確認它是正常關閉的（檢查有沒有錯誤日誌）

4.4. 移動所有InnoDB日誌檔案到其它地方

4.5. 修改配置檔案然後重新啟動MySQL伺服器

4.6. 檢查錯誤日誌並檢視是否產生新的日誌檔案。

5. InnoDB_flush_method

5.1. 指定一個方法讓innodb跟OS檔案系統一起工作

5.2. Windows: 總是使用沒有buffer的IO方法

5.3. Unix:可以使用fsync()或者O_SYNC/O_DSYNC進行重新整理檔案。

5.3.1. Fsync()通常是更快的，允許累計多個寫操作然後併發執行。

5.3.2. 一些作業系統允許關閉OS級別的緩衝針對InnoDB的資料檔案。這樣非常好，你總不希 望資料被緩衝2次吧。

5.4. Linux：O_DIRECT 使用直接的非緩衝IO。避免雙重緩衝，可能會讓寫更慢。

6. Innodb_file_per_table

6.1. InnoDB可以儲存每個表在單獨的檔案中。

6.2. 對於系統需要的主表空間還是需要的。

6.3. 能夠幫助拆封不同的表到多個磁碟上。

6.4. 如果表被刪除允許回收空間。

6.5. 有時候使用連續的fsync()寫會更慢。

6.6. 當有大量的表的時候會增加啟動和關閉的時間。

7. 其它檔案IO設定

7.1. Innodb_autoextend_increment–這個引數指 定了對於共享表空間的增量（不是為了單獨表空間的）。大的值可以有效的減少碎片。

7.2. Innodb_file_io_thread–改變IO執行緒的數量，只對windows有效，所有4個執行緒可以做不同的事情。

7.3. Innodb_open_file–這個值是用作指定每個表空間所允許 開啟檔案的數量。如果你有很多表那就要增加它。

7.4. Innodb_support_x–把這個值設定定為0的時候能夠減少innodb的工作在事務提交時。Binlog 能夠透過非同步的方式 同步。

8. 最小化重啟時間

8.1. Innodb快取池可能有一些未寫入到磁碟的資料。所以關閉的時候需要 非常的時間。

8.2. 如果你想最小化關閉時間，那需要如下設定：

8.2.1. SET GLOBAL innodb_max_dirty_pages_pct=0

8.2.2. 執行show status後觀察 innodb_buffer_pool_pages_dirty

8.2.3. 當這個值變為0的時候就可以關閉伺服器了

8.3. 在執行這個操作時候會讓效能降低，因為InnoDB將立刻要將髒資料頁寫入到磁碟上。

9. 排錯逃離清除

9.1. InnoDB不會透過delete來移除一行（和在更新時候舊的行），因為這些行可能會被其他事務使用。

9.2. 清除執行緒被用在清除這些沒有用到的行。

9.3. 在一些工作負載，清除執行緒可能不能保持這些表空間無限的增長。透過show innodb status觀察transactions部分。

9.4. Innodb_max_purge_lag–這個是用來限制事務每次所 能更新或者刪除最大的行數。將會延遲insert/update操作，所以清除執行緒會被保持。

9.5. 為什麼我們不用多個清除執行緒呢？

10. 併發控制設定

10.1. 設定可以幫助InnoDB適應於控制大量的併發事務。

10.2. Innodb_thread_concurrency–InnoDB內 核中同時可以允許最大的線上程數量（0表示沒有限制），2*（CPU核數+磁碟數量）在理論上是一個合理的值，在實際應用中設定 的更小一點可能會讓效能更好一點。

10.3. Innodb_commit_concurrency–允許同一時間 內事務提交的最大執行緒數。

10.4. Innodb_concurrency_tickets–在不得不退 出核心空間和等待之前能執行執行緒的數量。

10.5. Innodb_thread_sleep_delay

10.6. Innodb_sync_spin_loops

11. 獲得高效能的不安全方式

11.1. Innodb有一些檢查和方法是資料不被最小化丟失和錯誤。

11.2. Innodb_doublewrite–這個值是用來保護部分頁的修 改，只是禁止假如OS保證它不會發生。

11.3. Innodb_checksums–在頁中的資料校驗碼，幫助發現文 件系統錯誤，記憶體損壞和其它問題。

11.3.1. 導致一部分大工作負載的機器會過載。

11.3.2. 當效能是第一的情況下可以禁止這個引數。

12. Innodb SHOW STATUS部分

12.1. Mysql5.0有一些效能計數資訊透過show status能夠顯示出來。

12.1.1. 它們都是全域性的，雖然大部分其它技術資訊都是針對每個執行緒的。

12.1.2. 它們大部分都是從show innodb status中獲取的。

12.2. 下面將顯示其中的一些指標。

12.3. Innodb_buffer_pool_pages_misc–其他 快取頁需要的在innodb buffer中所使用到的頁。

12.4. Innodb_buffer_pool_read_ahead_rnd–innodb處理的隨機預讀的數量。

12.5. Innodb_buffer_pool_read_request, innodb_buffer_pool_reads 這2個值是用來計算快取讀的命中率的。

13. Show innodb status

13.1. 這個是innodb故障處理的工具。當發生問題時就輸入“show innodb status”

13.2. 這時候就會顯示一些比show status更詳細的資訊。

13.3. 一些關於現在正在執行中的事務的資訊（列入它們的鎖等等）

13.4. 最新的一個死鎖和外來鍵的資訊等等

13.5. 一些關於latches,spinlocks(自旋鎖)，作業系統等待資訊

13.6. 更詳細的參考http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/

14. Show mutex(互斥量) status

14.1. 一個用來顯示在你的工作負載下哪些熱門的互斥量的工具

14.2. 顯示了哪些是真正發生的互斥量，自旋鎖的細節，以及作業系統等待。

14.3. Timed_mutexes – 跟蹤作業系統等待了多久

硬體和作業系統的選擇

15. 硬體和作業系統選擇的檢查列表

15.1. 使用什麼CPU，以及多少個？

15.2. 使用多大的記憶體？

15.3. 如何建立IO子系統（如RAID）？

15.4. 使用哪個檔案系統是最好的選擇？

16. 選擇CPU

16.1. 不同的CPU/架構對於innodb的擴充套件效能是不同的。

16.2. 老的“netburst”基於intel的xeon的擴充套件能力是比較弱的。

16.3. 新的酷睿基於xeon和 AMD的opterons具有更好的可擴張性

16.4. X86_64是非常必須的。

16.5. 使用多核處理器會讓Innodb工作的更好

16.6. 每個系統擁有8核是一個比較合理的限制。

16.6.1. 必須根據實際的工作負載來進行調配

16.6.2. Innodb必須要根據未來的預期負載進行

16.7. 外部擴充套件，使用多臺效能 較低的終端伺服器。

16.8. 32位的CPU馬上就要被淘汰了，所以請不要再使用32位的作業系統。

17. 使用多大的記憶體

17.1. 記憶體經常是對於很好的應用程式調優的效能瓶頸。

17.2. Innodb可以很有效的使用大量的記憶體。

17.3. 工作設定必須合理設定記憶體

17.3.1. 因為資料頁是最常被訪問

17.3.2. 不要透過行進行計數：100byte的行大概是1億行，隨機100萬行的工作設定能夠產生大部分的資料頁。

17.4. 是總資料庫大小的5%能夠導致50%空間佔用。

17.5. 確定使用64位的平臺，作業系統和mysql版本。

18. 如何建立IO子系統

18.1. Innodb可以很好的載入大部分的磁碟驅動。每個節點有6-8個是一個看上去最優的配置。

18.2. 直接能夠訪問到的儲存通常工作的最好

18.3. SAN–會增加恢復時間，而且價格昂貴

18.4. NAS–可以避免很多資料錯誤的風險

18.5. ISCSI–在大部分業務中都是非常適合的，也會增加恢復時間

18.6. RAID–電池備份cache是非常重要的。一定要確認啟動了在寫入cache的時候有電池備份。

18.7. 硬碟自身的快取一般都是需要關閉。或者確認使用fsync()寫入資料到磁碟或者當作業系統崩潰的時候 能夠產生資料腐壞。

19. 本地儲存的配置

19.1. 日誌最好放到單獨的raid1中。這個是非常有幫助的，在很多情況比放在跟資料一起更好。

19.2. 二進位制日誌最好放到單獨的卷中–能夠很好的幫助備份和恢復。

19.3. RAID10對於表空間非常好。比預期下降的效能更多。

19.4. RAID5對於特定的工作負載時好的。僅僅需要確認是否需要降低性 能。

19.5. 大的RAID條帶大小（128K+）在理論是最好的，但是很多RAID控制器不能非常好的去控制。

19.6. 軟RAID也是不錯的，特別是RAID1。

20. 操作的選擇也有會有問題？

20.1. 需要考慮效能，工具的有效性，社群等等。

20.2. Windows–試用於開發環境，很低的擴充套件性的WEB/企業專案上。

20.3. Solaris–提供了一些非常好的工具，也能對Mysql提供很好的支援，但是缺乏社群支援。

20.4. FreeBSD–歷史上Mysql有很多問題在這個平臺上，現在是好多了，但是隻有很少的工具可用，很少在生產環境中使用。

20.5. Linux–在生產環境中和開發環境中最常使用的平臺。有一些像LVM,JFS這樣的工具可以使用。

21. 檔案系統的選擇

21.1. 主要是linux環境中有很多檔案系統可以選擇

21.2. EXT3–很多Linux發行版的預設檔案系統，工作的非常好對於終端安裝。

21.3. ReiserFS–很多Linux支援這個遷移。通常沒有打的問題在標準的Mysql工作負載下。

21.4. XFS–被用在有RAID驅動的情況下，能夠提供不錯的效能提升。

21.5. JFS–很少被使用

21.6. Raw分割槽（原始分割槽）針對innodb表空間—很少使用

21.7. 通常非常高的效能提升預期都是透過更改檔案檔案系統來獲得。

最近InnoDB的效能開發

22. InnoDB可伸縮性的補丁

22.1. 減少了buffer pool也中競爭。在5.0中直接可用，在4.1中需要自己移植。

22.2. 在MySQL5.1提升了sync_array的實現。

22.3. 基於工作負載，硬體環境，併發上的效能提升跟原來有所不同。

22.4. 從很少的百分比到很多次的進行排序。

22.5. 效能還是會在很大數量的併發執行緒的情況下有所下降。

22.6. 未來可擴充套件實現補丁的原型都是來源於社群的。

23. 其它改進

23.1. 在Mysql5.1中行級別複製減輕了間隙鎖的問題。

23.2. 在沒有auto_increment”talbe locks”能夠繼續工作。

23.3. 資料庫頁中支援ZIP壓縮

23.4. 更快的索引建立

23.4.1. 不再需要全表重建

23.4.2. 可以根據物理分片的索引進行排序。