以 MySQL 為例的資料庫優化

MySQL Optimization

資料庫運行在作業系統之上，運算的處理能力由中央處理器的運算能力決定，資料的快取速度由記憶體的速度及大小決定，而最終資料是存放在磁碟的檔案中。

想進行資料庫優化，首先要了解最能有效支援你的資料庫的作業系統是什麼？而你又是用什麼作業系統在執行？

更甚，你應該要知道你的CPU每秒可以處理多少個指令集，存放在記憶體的快取每秒可以處理多少資料量，而每秒鐘硬碟的I/O可以都取多少資料量。

我們能以系統化的方式看待資料庫效能管理，分為下列四個步驟：

一、找出潛在問題（Identify）

對於影響服務水準（SLA）的資料庫效能衡量指標（KPI），要清楚地建立起來，以便挖掘潛在問題。

透過這些衡量指標，可以顯示系統的健康狀態，並且找出問題在哪裡，對 DBA 而言，他們能指出執行時間過久的 SQL 查詢、緩衝共用區與 I/O 狀態、記憶體耗用量與資料鎖定的衝突點；而對開發人員來說，他們關切的是在耗用過多時間的資料存取作業，分析應用程式的計算時間，以及等待資料庫回應的時間。

建立好指標後，必須有效地追蹤，以便在發生問題時，透過系統發送警告通知。

二、分析問題（Diagnose）

三、解決問題（Solve）

四、預防問題再度發生（Prevent）

持續進行資料庫效能監控及優化（Optimization），才能使企業所投入的 IT資產發揮最大效益，而非不斷編列硬體擴充預算，增加維運人力。

下列會以幾個部分來討論 MySQL 上的優化方式：

1. 監看工具
2. SQL 調校
3. 初始化設定檔
4. 緩衝區和高速緩存
5. 資料一致性和性能之間的平衡
6. 網路系統應用
7. 整體考慮（系統 I/O、Memory）

監看工具

監看工具能幫助你了解資料庫的性能及潛在問題。

• MySQL Performance Reports
• MySQL Enterprise Monitor
• MySQL Plugin for Oracle Enterprise Monitor — Cacti
• Zabbix
• Nagios
• sys schema

sys schema 是一組 views、functions 和 procedures，目地在於簡化 Information Schema 和 Performance Schema 的使用。

https://github.com/mysql/mysql-sys

https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html

但是，也要注意過度監看可能會為系統帶來額外的負荷，例如：

SHOW PROCESSLIST
SHOW ENGINE INNODB STATUS

在 MySQL 5.6 之後，可用 performance_schema.thread 取代 SHOW PROCESSLIST。

SQL 調校

SQL 慢查詢

知道如何紀錄系統中較慢的 SQL 語法，並對其做語法的改善。

• 可以使用執行計畫 (EXPLAIN) 來檢視查詢
• 慢查詢日誌
• MySQL Enterprise Monitor Query Analyzer
• performance_schema.events_statements_summary_by_digest
• sys.statements_with_runtimes_in_95th_percentile

SQL 語法與儲存引擎

是否具備全文檢索的查詢功能。

避免在 LIKE 子查詢中比對 * 號開頭的字串

因為，使用 *，優化器則不會使用索引，而是掃表。如果確實有需求，應該使用全文檢索來提升效能。

索引數量

索引的目的在建立一個資料較小的索引樹，當應用程式在查詢資料時，可以先利用這顆樹取得資料定址，減少磁碟I/O，加速資料的存取。但是，使用者仍然需要避免使用過多的索引。

索引的數目越多，在新增或更新資料時，連帶更新的索引也會越多，導致資料寫入的時間變長，寫入鎖定（Write Lock）的時間也變長。

儘管你在實際場景中的應用程式，其讀取頻率遠大於寫入頻率。因索引太多所造成的寫入鎖定，也會連帶影響到該資料讀取的全部線程，整體效能會降低許多。

所以，適當的索引數目應該依照使用者的使用頻率、資料分佈的大小來決定索引的建立與否。一般來說鑑別度高的欄位才需要建立索引，且不要在重複的欄位建立索引。

分割資料表

MySQL 5.1 之後，我們可以使用 Partition 將同一個表格的資料，分成不同的小檔案儲存，分散在不同磁碟，增加存取速率。

初始化設定檔

設定檔路徑

• datadir: 指定資料預設要放在哪

非InnoDB表的資料和索引

InnoDB file_per_table 的表空間，各種日誌包含通用、系統、和重做日誌

• innodb_data_home_dir: innodb 預設系統表空間 (ibdata1) 的位置
• innodb_log_group_home_dir: 指向 InnoDB 重做日誌檔的路徑
• innodb_undo_directory: 指向 InnoDB 復原表空間的目錄
• log_bin: 兩個功能，開啟二元日誌且設定路徑/檔的前綴
• log_error: 確認您知道它放在哪

將設定檔放到不同的磁碟系統可減少衝突，例如將較為繁忙的檔放到比較快的磁碟。

使用 innodb_flush_log_at_trx_commit = 1 在大量交易提交時，可將 InnoDB 的重做日誌放到 SSD 以達到較高的 flush 速率。

避免 InnoDB 表空間成為一個單一大檔案，可在 my.cnf 中設定 innodb_file_per_table，讓不同的資料表有獨立的空間。其表空間和通用表空間，可在建立時放到 datadir 之外。

開啟額外的監看

• 如果您用 InnoDB，可開啓所有的 INNODB_METRICS 計數器

全域變數 innodb_monitor_enable = ‘ALL’

它的負荷變得很小，所以值得爲瞭解詳情而開啓它

• 確認 Performance Schema 是開啓的

會有一點負荷，但能提供性能優化的有用訊息

考慮在必要時開啓更多的 consumers 和 instruments，在表 setup_consumers、setup_instruments

The Performance Schema 也可以在開機時動態改變設定

Buffer Size

可參考 percona online tools，需用整個系統的角度來看待。

MyISAM

key_buffer_size=128M             # default is 8M
bulk_insert_buffer_size=4194304  # default is 8M

InnoDB

innodb_buffer_pool_size=32M  # default 8M
innodb_log_buffer_size=8M    # default 1M

innodb_buffer_pool_size

「在一個專用資料庫的伺服器上，您可以將 innodb_buffer_pool_size 設定為主機記憶體的 80%。」

你應該考慮：

• 主機有多少記憶體？
• 刪減操作系統及其他程序所需的容量
• 刪減 MySQL 除了 InnoDB buffer pool 之外的部分
• 選擇以上的值和工作資料組中較小的值

InnoDB redo log

總重做日誌的大小決定於：

• innodb_log_file_size
• innodb_log_files_in_group

Total size = innodb_log_file_size * innodb_log_files_in_group

不同版本中，最大的總 redo log size 可達：

• MySQL 5.5 以前最大爲 4G
• MySQL 5.6 以後最大爲 512G
• 應該大到不會出現太多的 checkpointing
• 重做越大，關機所需要的時間可能越久

InnoDB redo log 夠大嗎？

• 在一個異步 flush 啓動時，使用率達 75%

非常集中的 I/O 使得其他的工作都停頓

主 InnoDB 可能處於狀態消息： flushing buffer pool pages

• 確認您有足够的空間來應付尖峰時期

例如目標放在最多用重做日志的 60% 或 70%

• 您所採用的監看方案對 redo log 使用狀况的監看能力很重要

緩衝區和高速緩存

• 開啟高速緩存區未必有好處
• 緩衝區和高速緩存也未必是越大越好

Query Cache

大部分的狀況 (以單一 mutex 保證)，開啟不會比較好，預設為關閉：

query_cache_type=0

• 寫得越多效益越差
• 越多的資料落在 buffer pool 中，效果越差
• 越複雜的查詢，越大的掃表，效果越好

join_buffer_size

• 在 MySQL 5.5 之前，只用於簡單的索引掃瞄、區間索引掃瞄，和沒用到索引的 joins

沒有理由大於各對應的行之長度

• 在 MySQL 5.6+ 也用於 Batch Key Access (BKA)

用 BKA 的查詢可受惠於較大的 join buffer

• 一開始給最小
• 通常設一個小的全域值 (每個連結的預設值)，最好爲 32k 至 256k
• 在當下對話需要時才增加其值

sore_buffer_size

• 和 join_buffer_size 一樣，一開始設個較小的全域值，最好為 32k 至 256k
• 在繁忙的伺服器上留意，使每秒的值儘量小
• 在必要時適度的增加

為何要給一個小的全域值？

• 有些緩存每當它一用到，就會占用所給定的值
• 一個查詢可能用到多個緩存，大的緩存可能會使記憶體不敷使用
• 相對記憶體的使用成本較高
• 在跨線程時，Linux glibc malloc 改變記憶體分派的運算法 (通常是 256k 或 512k).

對記憶體分派較大的運算法，可能比較小分派的演算法要慢40倍

資料的一致性和性能之間的平衡

1. innodb_flush_log_at_trx_commit
2. sync_binlog

sync_binlog = 0 可能帶來最好的性能，但是 sync_binlog = 1 卻能 帶來最可預測的性能。

網路系統應用

IP

inet_aton()   -- 將字串轉換成整數
inet_ntoa()   -- 將整數轉換成字串

MAC

hex()

整體考慮

操作系統的 I/O

• I/O 排程器

有一些 Linux 預設使用 CFQ 排程器

• NOOP 和 deadline 通常對 MySQL 的負荷較佳

Deadline 是 Oracle Linux 預設的 I/O 排程器

• 查看現行的排程器

$ cat /sys/block/sda/queue/scheduler

• 動態更換排程器

$ echo deadline > /sys/block/sda/queue/scheduler
$ cat /sys/block/sda/queue/scheduler

• 在開機時設定，使用 elevator=deadline 的開機選項。

記憶體分派的程序庫

• Linux 所用的 glibc malloc 可能造成瓶頸
• 最好用 malloc 以外的 library

tcmalloc

jemalloc

• 設定

mysqld_safe

systemd distributions

總結

• 瞭解您的系統及資料
• 瞭解您的需求
• 不成熟的優化是有反效果的
• 一次只調一個，不要做太多、太大的更動
• 不要見樹不見林，要從大處著手
• 同一套設定不要套用到所有的狀况
• 根據測量的結果下决定
• 在變動一個選項前要瞭解它的作用是什麽
• 整體考慮

參考

• MySQL 與 MariaDB 學習手冊 (ISBN 978–086–476–383–2)
• Effective SQL 中文版 (ISBN 978–986–476–435–8)

性能優化

MySQL 性能優化六大心法 | Oracle Community

MySQL 效能調校 | CodeData

MYSQL性能優化的最佳20+條經驗 | CoolShell

調校資料庫效能的實務方法 | iThome

改善資料庫效能的關鍵作法與技術 | iThome

大牛的經驗總結：資料庫查詢速度優化技巧及解決方案 | 滴滴頭條