簡述mysql問題處理_ZenDei技術網路在線

最近，有一位同事，咨詢我mysql的一點問題，具體來說，是如何很快的將一個mysql導出的文件快速的導入到另外一個mysql資料庫。我學習了很多mysql的知識，使用的時間卻並不是很多，對於mysql導入這類問題，我更是頭一次碰到。詢問我的原因，我大致可以猜到，以前互相之間有過很多交流，可能 ...

最近，有一位同事，咨詢我mysql的一點問題，具體來說，是如何很快的將一個mysql導出的文件快速的導入到另外一個mysql資料庫。我學習了很多mysql的知識，使用的時間卻並不是很多，對於mysql導入這類問題，我更是頭一次碰到。詢問我的原因，我大致可以猜到，以前互相之間有過很多交流，可能覺得我學習還是很認真可靠的。

首先，我瞭解了一下大致的情況，（1）這個文件是從mysql導出的，文件是運維給的，具體如何生成的，他不知道，可以詢問運維（2）按照當前他寫的代碼來看，每秒可以插入幾百條數據（3）按照他們的要求，需要每秒插入三四千條數據。（4）他們需要插入的數據量達到幾億條數據，當前有一個上千萬的實驗數據

根據我學習的知識，在《高性能mysql》上面有過描述， load data的速度，比插入資料庫快得多，所以，我先通過他們從運維那裡獲取了生成數據的代碼，通過向他們尋求了大約幾千條數據。運維生成數據的方式是：select ... into outfile, 根據mysql官方文檔上的說明，正好可以通過load data載入回資料庫，load data使用的fields和lines等參數，可以通過運維給出的語句得到，測試一次，成功。在我的推薦下，我們先使用innodb引擎根據測試的結果顯示，大約每秒1千多。這個，我是通過date; mysql -e "load data ..."; date; 來大致得到。考慮到innodb中存儲帶索引的數據，插入速度會隨著數據量的增加而變慢，那麼我們使用幾千條數據測試的結果，應該超過1千多。我們又在目標電腦上進行測試，得到的結果基本一致，速度會略快，具體原因當時沒有細查。向遠程mysql導入數據，需要進行一定的配置，具體配置的說明，在這裡省略。因為目標電腦有額外的用處，所以，我們決定現在本機電腦上進行測試。

進行了初步的準備工作之後，我決定獲取更多的數據，這次我打算下拉10萬條數據。可是我的那位同事，通過對csv文件進行剪切，得到的結果，不能在我的mysql上進行很好的載入（load data)。於是，我們打算從有上千萬條數據的測試mysql伺服器上進行拉取。可是SELECT ... INTO OUTFILE只會將文件下拉到本地，我們沒有本地的許可權，除非找運維。為此，我們換了一種思路，使用select語句，模擬select ... into outfile語句，具體做法如下：

(1) 將數據select到本地，這裡需要註意一件事，order by後面應該跟一個索引，這樣可以避免排序操作。我進行過測試，如果不顯示制定order by index, 下拉所用的時間會明顯增加。 mysql -h 192.168.9.10 -u root -p -e "SELECT * FROM edu.olog order by idd LIMIT 100000;" > /home/sun/data.txt (2) 使用sed命令移除輸出中的第一行 sed -i '1d' /home/sun/data.txt (3) 對遠程資料庫表格調用show create table指令得到表格的創建指令。在本機mysql的一個資料庫中調用上述的創建表格的指令，這裡可能需要做修改引擎一類的操作, 例如使用MYISAM作為表格的引擎，這樣對於我們的載入任務來說，load data的速度明顯更快。然後，對生成的文件調用load data記載到本機資料庫，不過沒有FIELDS, lines一類的尾碼。這樣，我們就可以對本機表格調用select ... into outfile生成對應的csv文件。我們對十萬條數據進行了測試，測試的結果顯示，載入速度大約每秒1千多，速度比之前幾千條時略慢。我們已經知道了目標，也知道了當前的狀態，那麼下一步就是優化。首先，我查找了一些資料，這裡以mysql8.0為例。首先查看了load data的文檔，具體網頁為：https://dev.mysql.com/doc/refman/8.0/en/load-data.html。考慮到load data類似於insert，我又在優化的相關模塊中進行查找，大的目錄為：https://dev.mysql.com/doc/refman/8.0/en/optimization.html（優化），緊接著查找的網頁是：https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html（插入優化），根據這個網頁的提示，最後查看的網頁是：https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html（優化innodb表格的載入大量數據）。根據這裡面的提示，我對我自己構建的測試數據進行了測試，使用的優化方法如下：（1）臨時修改自動提交的方式：

SET autocommit=0;
... SQL import statements ...
COMMIT;

(2)臨時取消unique索引的檢查：

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

這裡簡單介紹一下，我的測試使用表格和數據，我的create table如下：

CREATE TABLE `tick` (
  `id` int(11) NOT NULL,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

行數為1775232，插入時間大約在十幾秒。在測試過程中，因為設計頻繁的清空表格，清空表格需要花好幾秒鐘的時間，建議做類似操作的可以考慮，先drop table，然後再create table，這樣速度會明顯更快。按照在我的電腦上的測試結果，兩者基本沒有什麼區別，大約都在18秒左右。使用的mysql語句為：

date; mysql -u root -pmysql -e "load data infile './tick.txt' into table test.tick;";date;

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0;load data infile '/home/sun/tick.txt' into table test.tick;set unique_checks=1;set autocommit=1;";date;

為了確保我的設置生效，我檢查了status輸出：

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0; show variables like '%commit';set unique_checks=1;set autocommit=1;";date;

date; mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0; show variables like 'unique_checks';set unique_checks=1;set autocommit=1;";date;

輸出結果如下：

autocommit為OFF, unique_checks為OFF。

其實我還設置過很多配置，例如插入緩衝區大小一類的，其中比較有效的是innodb_flush_log_at_trx_commit設置為2，對於我們這個問題，這個設置是可以考慮的。

我一直有一個疑問，就是說，load data是否為多線程運行的，按照我以往使用mysql的經驗，和《高性能mysql》對load data的論斷（遠比insert快），如果多線程載入，不至於速度如此之慢。後來，我嘗試使用mysqlimport，經過查看mysqlimport上面指定的--use-threads為多線程讀取文件，以及在使用mysqlimport載入時，CPU利用率最多只有百分之兩百多一點，這個讓我覺得很有可能是單線程執行的。通過在運行load data的同時，調用show full processlist，可以清楚的看到，load data是單線程運行的。結果如下：

*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 2835
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 17
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
*************************** 3. row ***************************
     Id: 39
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 13
  State: executing
   Info: load data infile '/home/sun/tick.txt' into table test.tick
3 rows in set (0.00 sec)

由上可以看出，load data是單線程執行的。

我知道，使用多線程執行，會是一個很好的辦法，只是，我還是有想法提高單線程的效率，我想到了set profiling. 通過在一個session中設置set profiling = 1; 可以查看一個語句詳細的時間消耗。我使用瞭如下的mysql語句：

set profiling=1;
load data infile '/home/sun/tick.txt' into table test.tick;
show profiles;
show profile for query 1;

可以得到如下的結果：

+----------------------+-----------+
| Status               | Duration |
+----------------------+-----------+
| starting             | 0.000102 |
| checking permissions | 0.000016 |
| Opening tables       | 0.001377 |
| System lock          | 0.000024 |
| executing            | 17.476604 |
| query end            | 0.233398 |
| closing tables       | 0.000045 |
| freeing items        | 0.000039 |
| cleaning up          | 0.000047 |
+----------------------+-----------+
可見執行時間占據了最長的時間，而執行時間應該主要是CPU密集型的（這個我沒有足夠的把握），從CPU的使用來看，這個論斷應該是比較合理的。對於一些比較複雜的問題，我不建議這樣分析，當然，這裡也可以考慮不這樣分析，可以借鑒《高性能mysql》中的做法，參考如下的腳本：

#!/bin/sh

INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/sun/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
    file=$(date +%F_%I)
    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
    echo $ts
done
echo Exiting because $RUNFILE does not exist

調整中間的時間，列印出詳細的信息，進行分析。我這裡就沒有做這件事。

下麵考慮了多線程處理，查看select ... into outfile的文件，即tick.txt可知，文件中的每行對應於表中的一條數據，想把文件切分，可以使用wc -l得出行數，然後使用head與tail得到兩個文件，然後對這兩個文件再次使用head和tail，得到測試用的四個文件。進行這個測試，我寫了很簡單的兩個shell腳本：

#!/bin/bash
date;mysql -u root -pmysql -e "load data infile '/home/sun/$1' into table test.tick;";date;
exit 0;

#!/bin/bash
./mysql-load.sh data1 &
./mysql-load.sh data2 &
./mysql-load.sh data3 &
./mysql-load.sh data4 &
exit 0;

通過測試，可以發現載入這些文件所用的時間由18秒降低到10秒。我還測試了載入兩個文件，所用時間由18秒降低到12.5秒，可見多線程載入可以明顯提高載入速度。因為我的電腦是4線程的，所以我決定使用真正的測試數據進行測試。

按照我們之間出現的一個小插曲，發現使用MYISAM的插入速度會明顯快於InnoDB，使用MYISAM插入速度可以達到每秒八千左右。我的同事認為這個可以滿足他的需求，所以就採用了MYISAM。那時，我的同事發現他的插入速度明顯我之前測試的要快，希望我能找出原因，基於沒有mysql沒有進行什麼特殊的配置，他的電腦配置和我的電腦配置基本一致，我覺得應該是表格的問題，可能性最大的就是引擎，後來發現他無意間使用了MYISAM作為存儲引擎。關於兩個電腦配置的差別，可以通過show variables將配置變數導入到文件中進行對比得到，這個屬於後話。既然我的同事覺得那樣可行，我就沒有進一步測試多線程的效果，只是這個經歷可以記錄下來，留以後借鑒。