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

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 配置由于默認(rèn)8M 調(diào)整到 128M

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

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

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

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

此配置項(xiàng)作用設(shè)定innodb 數(shù)據(jù)庫引擎UNDO日志的大??；從而減少數(shù)據(jù)庫checkpoint操作。

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

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

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

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

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

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

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

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

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

獲取測試結(jié)果：
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|
+------------+
從上面結(jié)果來看，數(shù)據(jù)量增加會(huì)對(duì)插入性能有一定影響。不過，整體速度還是非常面議。一天不到時(shí)間，就可以完成4億數(shù)據(jù)正常處理。預(yù)計(jì)數(shù)據(jù)庫瓶頸已經(jīng)被巧妙解決，結(jié)果變成程序“猿”苦逼地向我埋怨，大哥不用這么狠啊。

最新評(píng)論