mysql千萬級數據庫插入速度和讀取速度的調整記錄

一般情況下mysql上百萬數據讀取和插入更新是沒什麼問題了，但到了上千萬級就會出現很慢，下面我們來看mysql千萬級數據庫插入速度和讀取速度的調整記錄吧。

 

（1）提高數據庫插入性能中心思想：盡量將數據一次性寫入到Data File和減少數據庫的checkpoint 操作。這次修改了下面四個配置項：
1）將 innodb_flush_log_at_trx_commit 配置設定為0；按過往經驗設定為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 調整到 128M

此配置項作用主要是當tablespace 空間已經滿了後，需要MySQL系統需要自動擴展多少空間，每次tablespace 擴展都會讓各個SQL 處於等待狀態。增加自動擴展Size可以減少tablespace自動擴展次數。

3）將 innodb_log_buffer_size 配置由於默認1M 調整到 16M

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

4）將 innodb_log_file_size 配置由於默認 8M 調整到 128M

此配置項作用設定innodb 數據庫引擎UNDO日志的大小；從而減少數據庫checkpoint操作。

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

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

數據庫 MySQL分區前准備：在MySQL中，表空間就是存儲數據和索引的數據文件。
將S11數據庫由於同享tablespace 修改為支持多個tablespace;

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

經過以上調整，暫時沒能體現出系統讀取速度提升；基本都是在 2~3秒完成5K數據更新。

MySQL數據庫插入速度調整補充資料：

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

根據以上四點內容，作為一個業余DBA對MySQL服務進行了下面調整：
修改負責收錄記錄MySQL服務器配置，提升MySQL整體寫速度；具體為下面三個數據庫變量值：innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size；此三個變量默認值分別為 5M、8M、8M，根據服務器內存大小與具體使用情況，將此三只分別修改為：128M、16M、128M。同時，也將原來2個 Log File 變更為 8 個Log File。此次修改主要滿足第一和第二點，如：增加innodb_autoextend_increment就是為了避免由於頻繁自動擴展Data File而導致 MySQL 的checkpoint 操作；
將大表轉變為獨立表空並且進行分區，然後將不同分區下掛在多個不同硬盤陣列中。

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

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