（1）提高數(shù)據(jù)庫插入性能中心思想：盡量將數(shù)據(jù)一次性寫入到Data File和減少數(shù)據(jù)庫的checkpoint 操作。這次修改了下面四個配置項：
1）將 innodb_flush_log_at_trx_commit 配置設定為0；按過往經(jīng)驗設定為0，插入速度會有很大提高。

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.
1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file
2：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it
2）將 innodb_autoextend_increment 配置由于默認8M 調(diào)整到 128M

此配置項作用主要是當tablespace 空間已經(jīng)滿了后，需要MySQL系統(tǒng)需要自動擴展多少空間，每次tablespace 擴展都會讓各個SQL 處于等待狀態(tài)。增加自動擴展Size可以減少tablespace自動擴展次數(shù)。

3）將 innodb_log_buffer_size 配置由于默認1M 調(diào)整到 16M

此配置項作用設定innodb 數(shù)據(jù)庫引擎寫日志緩存區(qū)；將此緩存段增大可以減少數(shù)據(jù)庫寫數(shù)據(jù)文件次數(shù)。

4）將 innodb_log_file_size 配置由于默認 8M 調(diào)整到 128M

此配置項作用設定innodb 數(shù)據(jù)庫引擎UNDO日志的大?。粡亩鴾p少數(shù)據(jù)庫checkpoint操作。

經(jīng)過以上調(diào)整，系統(tǒng)插入速度由于原來10分鐘幾萬條提升至1秒1W左右；注：以上參數(shù)調(diào)整，需要根據(jù)不同機器來進行實際調(diào)整。特別是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要謹慎調(diào)整；因為涉及MySQL本身的容災處理。

（2）提升數(shù)據(jù)庫讀取速度，重數(shù)據(jù)庫層面上讀取速度提升主要由于幾點：簡化SQL、加索引和分區(qū)； 經(jīng)過檢查程序SQL已經(jīng)是最簡單，查詢條件上已經(jīng)增加索引。我們只能用武器：表分區(qū)。

數(shù)據(jù)庫 MySQL分區(qū)前準備：在MySQL中，表空間就是存儲數(shù)據(jù)和索引的數(shù)據(jù)文件。
將S11數(shù)據(jù)庫由于同享tablespace 修改為支持多個tablespace;

將wb_user_info_sina 和 wb_user_info_tx 兩個表修改為各自獨立表空間；（Sina：1700W數(shù)據(jù)，2.6G 大數(shù)據(jù)文件，Tencent 1400W，2.3G大數(shù)據(jù)文件）；
分區(qū)操作：
將現(xiàn)有的主鍵和索引先刪除
重現(xiàn)建立id,uid 的聯(lián)合主鍵
再以 uid 為鍵值進行分區(qū)。這時候到/var/data/mysql 查看數(shù)據(jù)文件，可以看到兩個大表各自獨立表空間已經(jīng)分割成若干個較少獨立分區(qū)空間。（這時候若以uid 為檢索條件進行查詢，并不提升速度；因為鍵值只是安排數(shù)據(jù)存儲的分區(qū)并不會建立分區(qū)索引。我非常郁悶這點比Oracle 差得不是一點半點。）
再以 uid 字段上進行建立索引。再次到/var/data/mysql 文件夾查看數(shù)據(jù)文件，非常郁悶地發(fā)現(xiàn)各個分區(qū)Size竟然大了。MySQL還是老樣子將索引與數(shù)據(jù)存儲在同一個tablespace里面。若能index 與 數(shù)據(jù)分離能夠更加好管理。

經(jīng)過以上調(diào)整，暫時沒能體現(xiàn)出系統(tǒng)讀取速度提升；基本都是在 2~3秒完成5K數(shù)據(jù)更新。

MySQL數(shù)據(jù)庫插入速度調(diào)整補充資料：

MySQL 從最開始的時候 1000條/分鐘的插入速度調(diào)高至 10000條/秒。 相信大家都已經(jīng)等急了相關介紹，下面我做調(diào)優(yōu)時候的整個過程。提高數(shù)據(jù)庫插入性能中心思想：
1、盡量使數(shù)據(jù)庫一次性寫入Data File
2、減少數(shù)據(jù)庫的checkpoint 操作
3、程序上盡量緩沖數(shù)據(jù)，進行批量式插入與提交
4、減少系統(tǒng)的IO沖突

根據(jù)以上四點內(nèi)容，作為一個業(yè)余DBA對MySQL服務進行了下面調(diào)整：
修改負責收錄記錄MySQL服務器配置，提升MySQL整體寫速度；具體為下面三個數(shù)據(jù)庫變量值：innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size；此三個變量默認值分別為 5M、8M、8M，根據(jù)服務器內(nèi)存大小與具體使用情況，將此三只分別修改為：128M、16M、128M。同時，也將原來2個 Log File 變更為 8 個Log File。此次修改主要滿足第一和第二點，如：增加innodb_autoextend_increment就是為了避免由于頻繁自動擴展Data File而導致 MySQL 的checkpoint 操作；
將大表轉(zhuǎn)變?yōu)楠毩⒈砜詹⑶疫M行分區(qū)，然后將不同分區(qū)下掛在多個不同硬盤陣列中。

完成了以上修改操作后；我看到下面幸福結果：

獲取測試結果：
Query OK, 2500000 rows affected (4 min 4.85 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (4 min 58.89 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 25.91 sec)
Records: 2500000 Duplicates: 0 Warnings: 0
Query OK, 2500000 rows affected (5 min 22.32 sec)

Records: 2500000 Duplicates: 0 Warnings: 0
最后表的數(shù)據(jù)量：
+------------+
| count(*) |
+------------+
| 10000000|
+------------+
從上面結果來看，數(shù)據(jù)量增加會對插入性能有一定影響。不過，整體速度還是非常面議。一天不到時間，就可以完成4億數(shù)據(jù)正常處理。預計數(shù)據(jù)庫瓶頸已經(jīng)被巧妙解決，結果變成程序“猿”苦逼地向我埋怨，大哥不用這么狠啊。

最新評論