一: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有兩種死鎖處理方式：

等待，直到超時（innodb_lock_wait_timeout=50s）。
發起死鎖檢測，主動回滾一條事務，讓其他事務繼續執行（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，他都是一個事務！

mysql load 相關實驗

相關文章