mysql load 相關實驗

賀子_DBA時代發表於2021-02-12
MySql
一:load 的過程相當於是：先start transaction，然後再insert資料，最後commit
mysql 區別於oracle sqlldr,沒有後者的rows的引數來控制每次提交的資料行
但是mysql 是自己透過估算出一個值，來批次讀取 ，他不是 一條一條的 insert的
二：load 如果資料存在（主鍵或者唯一鍵），預設是跳過的，可以選擇replace存在就替換！
三：load 沒有類似於oracle的 sqlldr的rows引數來控制每次提交的行數，只能先透過linux命令來
切分（split）成小檔案來實現並行；
四：普通主從和PXC叢集的實驗結果不一樣，pxc叢集的是分批導進去的，但是普通主從是
一個事務進去的，
實驗一：load會不會鎖表（普通單節點）
session1
[root@beijing-fuli-hadoop-04 ~]# cat /data/t.txt
100,  liu ,18
102,  liu ,18
101,  liu, 18
root@localhost : (none) 11:50:05>start  transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost : (none) 11:51:08>LOAD DATA LOCAL INFILE '/data/t.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0
然後不commit！
session2
如下全部等待
root@localhost : liuwenhe 11:52:36>delete from t  where  id=101;
root@localhost : liuwenhe 11:52:36>delete from t  where  id=102;
root@localhost : liuwenhe 11:52:36>delete from t  where  id=103;

如下 不等待
delete from t  where  id=104
delete from t  where  id=100
結論：
load 在提交之前，會鎖定所有剛load的資料！！！也間接的說明這是一個事務把三個資料
都load進去了，會不會是 mysql 預設把N行資料作為一個事務呢？採用大資料量來做驗證

實驗二：load是不是一個事務 （普通單節點）
1.檔案/data/12.txt是26135101行資料的檔案
2.然後開始load
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/12.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
3.另開一個會話，查詢資料，發現再load完成之前一直是空，
root@localhost : liuwenhe 13:55:15>select  count(*) from  t;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.66 sec)

這就進一步說明 load操作是一個事務的！！！

實驗三：是否允許在同一個表上同時進行load?  只要沒有衝突是可以並行的！（普通單節點）
這裡所說的衝突是指: 已經load 處理了的資料中和另一個會話要處理的資料有衝突，具體實驗如下：
假如1.txt 檔案 是id從1到2147483647這個範圍的資料，而2.txt是id=2147483647的
一條資料，而3.txt是id從1到3的範圍並且還有id=2147483646這條資料
具體如下：
[root@beijing-fuli-hadoop-04 liuwenhe]# cat  2.txt
26293013,liu  ,18
[root@beijing-fuli-hadoop-04 liuwenhe]# cat  3.txt
1,  liu  ,18
26293013,liu  ,18

具體實驗過程：
實驗1）
會話1：
執行這個，因為資料量比較大，所以會執行一會
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/liuwenhe/1.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
會話2：
[root@beijing-fuli-hadoop-04 liuwenhe]# cat  2.txt
26293013,liu  ,18
然後會話1還沒有結束呢，執行如下操作，發現沒有等待！確實進去了，
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/liuwenhe/2.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
root@localhost : liuwenhe 17:33:18>select * from  t where id =26293013;
+----------+-------+------+
| id       | name  | num  |
+----------+-------+------+
| 26293013 | liu   |   18 |
+----------+-------+------+
1 row in set (0.12 sec)
說明：load順序執行,當執行到的id=1的資料到達innodb層，mysql就會把id=1的資料上鎖gap鎖，
這時候你再load=1的資料就會有鎖等待，但是你沒有執行到id=26293013的資料，也就沒有給這條資料上鎖，所以你並行執行另一個load （id=26293013）的資料就不會等待。
實驗2）
會話1：
執行這個，因為資料量比較大，所以會執行一會
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/liuwenhe/1.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
會話2：
在會話1還沒有結束的時候，執行如下發現等待，因為id=1的資料被會話1鎖定，所以下面的操作是需要等待的，因為load  3.txt是先處理id=1的資料，但是它已經被鎖定了，
[root@beijing-fuli-hadoop-04 liuwenhe]# cat  3.txt
1,  liu  ,18
26293013,liu  ,18
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/3.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
實驗3）load 產生死鎖：
會話1：
執行這個，因為資料量比較大，所以會執行一會；
root@localhost : liuwenhe 13:54:50>LOAD DATA LOCAL INFILE '/data/liuwenhe/1.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
會話2：
在會話1還沒有結束的時候，執行如下發現等待，因為id=1的資料被會話1鎖定，但是id=26293013的資料沒有被鎖定呢，所以說load  4.txt的時候，能把第一條資料（id=26293013）load進innodb引擎層並且鎖定，但是1這條資料卻被鎖定，進而會話1和會話2產生鎖等待！
[root@beijing-fuli-hadoop-04 liuwenhe]# cat  4.txt
26293013,liu  ,18
1,  liu  ,18
root@localhost : (none) 18:13:10>LOAD DATA LOCAL INFILE '/data/liuwenhe/4.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

註釋：為什麼會選擇回滾會話2的事務？因為我開啟了死鎖檢測，然後資料庫選擇插入更新或者刪除的行數最少的事務回滾
MySQL 如何處理死鎖？
MySQL有兩種死鎖處理方式：
  1. 等待，直到超時（innodb_lock_wait_timeout=50s）。
  2. 發起死鎖檢測，主動回滾一條事務，讓其他事務繼續執行（innodb_deadlock_detect=on）。
由於效能原因，一般都是使用死鎖檢測來進行處理死鎖。
死鎖檢測
死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。
回滾
檢測到死鎖之後， 選擇插入更新或者刪除的行數最少的事務回滾，基於 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 欄位來判斷。

實驗五：（PXC叢集）
會話1：
root@localhost : liuwenhe 21:33:58>LOAD DATA LOCAL INFILE '/data/backup/2.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;

會話2：在會話1沒有結束之前，查詢同樣的表，發現資料一直在增加；
root@localhost : (none) 21:34:56>select count(*) from liuwenhe.t;
+----------+
| count(*) |
+----------+
|  0 |
+----------+
1 row in set (3.31 sec)
root@localhost : (none) 21:34:56>select count(*) from liuwenhe.t;
+----------+
| count(*) |
+----------+
|  5710000 |
+----------+
1 row in set (3.31 sec)

root@localhost : (none) 21:35:11>select count(*) from liuwenhe.t;
+----------+
| count(*) |
+----------+
|  8480000 |
+----------+
1 row in set (5.11 sec)
證明：PXC叢集是不是一個事務，是分批匯入資料庫的，區別於前面的單節點資料庫的實驗結果！
實驗六：如果你start  transactiion,之後再load ,他就是一個事務了，但是還是分批的，如下已經報錯了；
會話1
root@localhost : liuwenhe 21:37:41>start  transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : liuwenhe 21:39:07>LOAD DATA LOCAL INFILE '/data/backup/2.txt' INTO TABLE liuwenhe.t fields terminated by ','  LINES TERMINATED BY '\n' ;
ERROR 1180 (HY000): wsrep_max_ws_rows exceeded
會話2：查詢始終是0
root@localhost : (none) 21:34:56>select count(*) from liuwenhe.t;
+----------+
| count(*) |
+----------+
|  0 |
+----------+
1 row in set (3.31 sec)
證明如果你開啟了事務，那麼就不是分批的了，是一個事務，但是單節點資料庫或者主從架構的資料庫，不管是是不是start transaction，他都是一個事務！


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-2757213/，如需轉載，請註明出處，否則將追究法律責任。

相關文章