什么是pt-online-schema-change

什么是pt-online-schema-change，針對(duì)這個(gè)問題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡(jiǎn)單易行的方法。

成都創(chuàng)新互聯(lián)公司是一家專注于網(wǎng)站建設(shè)、網(wǎng)站制作與策劃設(shè)計(jì),嘉峪關(guān)網(wǎng)站建設(shè)哪家好?成都創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十載,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:嘉峪關(guān)等地區(qū)。嘉峪關(guān)做網(wǎng)站價(jià)格咨詢:028-86922220

一、pt-online-schema-change 原理簡(jiǎn)析

首先要說明pt-online-schema-change工具并不是說修改表結(jié)構(gòu)的時(shí)候不上鎖，通常我們說的鎖一般包含innodb 行鎖和MDL lock。而pt-online-schema-change工具就是將某些使用COPY算法的DDL操作使用DML操作來代替，換句話說就是使用Innodb row鎖來代替MDL lock，因?yàn)镸ySQL原生的COPY算法的DDL會(huì)在MDL lock SNW這個(gè)類型保護(hù)下完整個(gè)表復(fù)制操作，整個(gè)復(fù)制過程中是不允許DML操作，因此造成了我們COPY算法的DDL堵塞線程正常的現(xiàn)象，當(dāng)然哪些DDL可以online進(jìn)行可以參考官方文檔online ddl一節(jié)。整個(gè)pt-online-schema-change工具修改過程中，只會(huì)在rename階段才會(huì)上MDL LOCK的X鎖，但是rename操作一般非?？焖?。

我們大概看一下pt-online-schema-change的工作方式，這個(gè)實(shí)際上開啟genrnal log就能看出來下面是重點(diǎn)步驟（我的表名叫做testpt_osc）：

  • 首先定義出新表

CREATE TABLE test._testpt_osc_new (
 id int(11) NOT NULL,
 name varchar(20) DEFAULT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
ALTER TABLE test._testpt_osc_new add index name_index(name)

  • 定義三個(gè)觸發(fā)器

delete 觸發(fā)器：
CREATE TRIGGER pt_osc_test_testpt_osc_del
AFTER DELETE ON test.testpt_osc
FOR EACH ROW
DELETE IGNORE FROM test._testpt_osc_new
WHERE test._testpt_osc_new.id <=> OLD.id

update 觸發(fā)器：
CREATE TRIGGER pt_osc_test_testpt_osc_upd
AFTER UPDATE ON test.testpt_osc
FOR EACH ROW
BEGIN
DELETE IGNORE FROM test._testpt_osc_new
WHERE !(OLD.id <=> NEW.id)
AND test._testpt_osc_new.id <=> OLD.id;
REPLACE INTO test._testpt_osc_new (id, name) VALUES
(NEW.id, NEW.name);
END

insert 觸發(fā)器：
CREATE TRIGGER pt_osc_test_testpt_osc_ins
AFTER INSERT ON test.testpt_osc
FOR EACH ROW
REPLACE INTO test._testpt_osc_new (id, name) VALUES
(NEW.id, NEW.name)

  • 使用分塊（chunk）拷貝的方式

首先需要插入數(shù)據(jù)的確認(rèn)上界：
SELECT /!40001 SQL_NO_CACHE / id FROM test.testpt_osc FORCE INDEX(PRIMARY)
WHERE ((id >= ‘1’)) ORDERBY id LIMIT 1999, 2 /next chunk boundary/

然后插入：
INSERT LOW_PRIORITY IGNORE INTO test._testpt_osc_new (id, name)
SELECT id, name FROM test.testpt_osc FORCE INDEX(PRIMARY)
WHERE ((id >= ‘1’)) AND ((id <= ‘2000’)) LOCK IN SHARE MODE

  • 最終進(jìn)行表的重新命名

    使用RENAME TABLE test.tp1 TO test._tp1_old, test._tp1_new TO test.tp1
    進(jìn)程重新命名。

從整個(gè)過程來講需要注意的幾個(gè)地方：

  1. 對(duì)于delete和update觸發(fā)器來講，delete數(shù)據(jù)均使用了IGNORE進(jìn)行修飾，因此即便數(shù)據(jù)還沒有拷貝到新表也不會(huì)引發(fā)錯(cuò)誤。

  2. 對(duì)于update和insert觸發(fā)器來講，均使用了replace這種操作來進(jìn)行，因此如果數(shù)據(jù)還沒有拷貝到新表那么將插入到新表中，如果數(shù)據(jù)已經(jīng)拷貝到新表那么將會(huì)修改其中的值。因此新表中總是保留的最新的數(shù)據(jù)。

  3. 對(duì)于分塊拷貝數(shù)據(jù)而言，使用是insert ignore 新表 select 老表 LOCK S 的方式，因此對(duì)于觸發(fā)器插入的最新值，是不會(huì)進(jìn)行修改的也不會(huì)報(bào)錯(cuò)。打個(gè)比方chunk為200 當(dāng)前拷貝數(shù)據(jù)到了1000行，但是我們手動(dòng)修改了第2000行的數(shù)據(jù)，那么第2000行將會(huì)在update觸發(fā)器的作用下提前插入到新表中，當(dāng)拷貝數(shù)據(jù)來到這一行的時(shí)候因?yàn)槭褂昧薸gnore則不會(huì)重復(fù)行的錯(cuò)誤，并且數(shù)據(jù)是最新的。其次每次insert select操作是一個(gè)單獨(dú)的事務(wù)。

  4. insert ignore 新表 select 老表 LOCK S 的方式 操作存在對(duì)新表中加自增鎖的可能，這取決于你的參數(shù)設(shè)置。

  5. 對(duì)于觸發(fā)器而言，原始語句和觸發(fā)語句被包裹在一個(gè)事務(wù)里面，也就是說對(duì)于任何一個(gè)DML語句而言，修改老表和新表的數(shù)據(jù)需要的行鎖將會(huì)在一個(gè)事務(wù)中存在。

  6. pt-online-schema-change 生成的binlog和redo都會(huì)比online DDL大得多，效率上講應(yīng)該低于online DDL。

  7. 由于replace操作的存在，因此pt-online-schema-change將會(huì)依賴主鍵或者唯一鍵，否則將不能工作。

我們可以看到整個(gè)過程中有如下的重點(diǎn)知識(shí)點(diǎn)：

  • 觸發(fā)器和事務(wù)

  • Insert ignore/replace語法

  • 自增死鎖的發(fā)生

其次對(duì)于第4和第5點(diǎn)來講，有出現(xiàn)死鎖的可能。下面我們分別討論。

二、觸發(fā)器與事務(wù)

在pt-online-schema-change中，觸發(fā)器占據(jù)了重要的地位，我們需要了解一下觸發(fā)器和事務(wù)之間的關(guān)系。我們常用的觸發(fā)器包含了before和after觸發(fā)器，代表著對(duì)原表進(jìn)行DML操作前或者后進(jìn)行其它的操作，下面是我定義的兩個(gè)測(cè)試的觸發(fā)器如下：

CREATE  TRIGGER testbef BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2 values(new.id);
END;
CREATE TRIGGER testaft after INSERT ON t1
FOR EACH ROW
BEGIN
INSERT INTO t3 values(new.id);
END;

顯然如果對(duì)t1表進(jìn)行數(shù)據(jù)插入，那么會(huì)在之前向t2表插入一條數(shù)據(jù)，然后在之后向t3插入一條數(shù)據(jù)，這一點(diǎn)可以通過函數(shù)調(diào)用trace進(jìn)行驗(yàn)證如下：

[root@ora12ctest mysql]#  cat -n tri2.trace |grep row_ins
   970  T@3: | | | | | | | | | | | >row_ins
   971  T@3: | | | | | | | | | | | | row_ins: table: test/t2 向t2表插入數(shù)據(jù)
...
  1406  T@3: | | | | | | | | >row_ins
  1407  T@3: | | | | | | | | | row_ins: table: test/t1 向t1表插入數(shù)據(jù)
...
  1779  T@3: | | | | | | | | | | | | >row_ins
  1780  T@3: | | | | | | | | | | | | | row_ins: table: test/t3 向t3表插入數(shù)據(jù)
...

這里就能夠看到順序了，其次我們還需要知道這些所有的操作會(huì)包裹在一個(gè)事務(wù)里面，這一點(diǎn)也可以通過函數(shù)調(diào)用trace進(jìn)行驗(yàn)證，還可以使用binlog進(jìn)行驗(yàn)證，下面是一次調(diào)用的binlog信息：

# at 194 （這里是GTID EVENT事務(wù)開始）
#200212 17:23:16 server id 1903313  end_log_pos 259 CRC32 0x4ff6735e    GTID    last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '92008a52-4b7d-11ea-9ec6-000c29c8aca8:202'/*!*/;
# at 259
#200212 17:23:16 server id 1903313  end_log_pos 331 CRC32 0x1ebd3446    Query   thread_id=3     exec_time=0     error_code=0
...
BEGIN
/*!*/;
# at 331
#200212 17:23:16 server id 1903313  end_log_pos 384 CRC32 0xe748dc3a    Rows_query
# INSERT INTO t2 values(new.id)
# at 384
#200212 17:23:16 server id 1903313  end_log_pos 429 CRC32 0x093c5fe3    Table_map: `test`.`t1` mapped to number 108
# at 429
#200212 17:23:16 server id 1903313  end_log_pos 474 CRC32 0x92691238    Table_map: `test`.`t2` mapped to number 110
# at 474
#200212 17:23:16 server id 1903313  end_log_pos 519 CRC32 0x5b9a710f    Table_map: `test`.`t3` mapped to number 111
# at 519
#200212 17:23:16 server id 1903313  end_log_pos 559 CRC32 0xe41b1119    Write_rows: table id 110
# at 559
#200212 17:23:16 server id 1903313  end_log_pos 599 CRC32 0x36c3511c    Write_rows: table id 108
# at 599
#200212 17:23:16 server id 1903313  end_log_pos 639 CRC32 0xa68b9ae6    Write_rows: table id 111 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
###   @1=11000 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `test`.`t1`
### SET
###   @1=11000 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `test`.`t3`
### SET
###   @1=11000 /* INT meta=0 nullable=0 is_null=0 */
# at 639 （這里是XID EVENT事務(wù)提交）
#200212 17:23:16 server id 1903313  end_log_pos 670 CRC32 0xbbb6547b    Xid = 19
COMMIT/*!*/;

這里我們使用binlog不僅驗(yàn)證了執(zhí)行順序并且還驗(yàn)證了所有操作都包含在一個(gè)事務(wù)里面。既然所有的語句都包裹在一個(gè)事務(wù)里面，那么加鎖的范圍就更大了，這不僅關(guān)系到本身的DML操作表，并且還關(guān)系到觸發(fā)語句的相關(guān)表，需要額外注意。

其次所有語句不僅包裹在一個(gè)事務(wù)里面，并且共享一個(gè)錯(cuò)誤返回接口，那么如下的錯(cuò)誤：

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
mysql> insert into t1 values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>

我們驚訝的發(fā)現(xiàn)t1表一條數(shù)據(jù)都沒有，但是居然返回重復(fù)的行。原因就在于雖然t1表沒有數(shù)據(jù)，但是t2或者t3表有違反唯一性檢查的可能，因此返回了錯(cuò)誤，錯(cuò)誤由統(tǒng)一的接口返回給客戶端。

最后觸發(fā)器會(huì)導(dǎo)致處理邏輯混亂，盡量避免使用觸發(fā)器。

三、Insert ignore/replace語法

關(guān)于ignore語法我們以insert ignore語法為例，一般來講如果遇到重復(fù)行insert ignore語法會(huì)通過忽略重復(fù)值錯(cuò)誤的方式進(jìn)行跳過，這實(shí)際上和replace的處理方式一致，但是replace不同的是如果遇到重復(fù)行不是進(jìn)行忽略，而是執(zhí)行的delete然后執(zhí)行insert操作。換句話說他們的觸發(fā)形式一致，但是觸發(fā)后執(zhí)行的行為是不同的，下面我們就來看看。

首先對(duì)于insert語句來講我們需要定位到需要插入的位置，這部分略過。

1、進(jìn)行重復(fù)行判斷操作

這一步對(duì)于主鍵/唯一索引 而言需要判斷是否已經(jīng)有重復(fù)的行。其判斷標(biāo)準(zhǔn)基本都是通過插入的值進(jìn)行索引定位，然后判斷定位游標(biāo)的值是否和需要插入值相同，下面是棧幀：

主鍵：

#0  row_ins_duplicate_error_in_clust (flags=0, cursor=0x7fffec4347d0, entry=0x7367c00, thr=0x7362980, mtr=0x7fffec433fa0)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:2273
#1  0x0000000001ae47d4 in row_ins_clust_index_entry_low (flags=0, mode=2, index=0x73674f0, n_uniq=1, entry=0x7367c00, n_ext=0, thr=0x7362980, dup_chk_only=false)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:2555
#2  0x0000000001ae697a in row_ins_clust_index_entry (index=0x73674f0, entry=0x7367c00, thr=0x7362980, n_ext=0, dup_chk_only=false)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3293
#3  0x0000000001ae6e88 in row_ins_index_entry (index=0x73674f0, entry=0x7367c00, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3429
#4  0x0000000001ae73e2 in row_ins_index_entry_step (node=0x7362710, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3579
#5  0x0000000001ae7749 in row_ins (node=0x7362710, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3717
#6  0x0000000001ae7bae in row_ins_step (thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3853
#7  0x0000000001b05f73 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x736e7a0 "\375\002", prebuilt=0x7362170)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1738
#8  0x0000000001b06484 in row_insert_for_mysql (mysql_rec=0x736e7a0 "\375\002", prebuilt=0x7362170) at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1859
#9  0x00000000019adca9 in ha_innobase::write_row (this=0x736e4b0, record=0x736e7a0 "\375\002") at /mysql/mysql-5.7.26/storage/innobase/handler/ha_innodb.cc:7598
#10 0x0000000000f67dc0 in handler::ha_write_row (this=0x736e4b0, buf=0x736e7a0 "\375\002") at /mysql/mysql-5.7.26/sql/handler.cc:8062
#11 0x00000000017cf173 in write_record (thd=0x6dd5660, table=0x735afa0, info=0x7fffec435b50, update=0x7fffec435ad0) at /mysql/mysql-5.7.26/sql/sql_insert.cc:1873
#12 0x00000000017cc24a in Sql_cmd_insert::mysql_insert (this=0x6debbc8, thd=0x6dd5660, table_list=0x6deb638) at /mysql/mysql-5.7.26/sql/sql_insert.cc:769
#13 0x00000000017d2d7f in Sql_cmd_insert::execute (this=0x6debbc8, thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_insert.cc:3118
#14 0x000000000159a70c in mysql_execute_command (thd=0x6dd5660, first_level=true) at /mysql/mysql-5.7.26/sql/sql_parse.cc:3596
#15 0x00000000015a06c0 in mysql_parse (thd=0x6dd5660, parser_state=0x7fffec437610) at /mysql/mysql-5.7.26/sql/sql_parse.cc:5570
#16 0x0000000001595283 in dispatch_command (thd=0x6dd5660, com_data=0x7fffec437d80, command=COM_QUERY) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1484
#17 0x00000000015940bc in do_command (thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1025
#18 0x00000000016cbf91 in handle_connection (arg=0x6dda360) at /mysql/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:306
#19 0x0000000001921c64 in pfs_spawn_thread (arg=0x6d7d0f0) at /mysql/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#20 0x0000003823e079d1 in start_thread () from /lib64/libpthread.so.0
#21 0x0000003823ae8b6d in clone () from /lib64/libc.so.6

二級(jí)索引唯一鍵

#0  row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7366e10, entry=0x7367ca8, thr=0x7362980, s_latch=false, mtr=0x7fffec434020, offsets_heap=0x7389038)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:2050
#1  0x0000000001ae5d35 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7366e10, offsets_heap=0x7389038, heap=0x7368538, entry=0x7367ca8, trx_id=0, 
    thr=0x7362980, dup_chk_only=false) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3033
#2  0x0000000001ae6cea in row_ins_sec_index_entry (index=0x7366e10, entry=0x7367ca8, thr=0x7362980, dup_chk_only=false)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3382
#3  0x0000000001ae6ea6 in row_ins_index_entry (index=0x7366e10, entry=0x7367ca8, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3431
#4  0x0000000001ae73e2 in row_ins_index_entry_step (node=0x7362710, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3579
#5  0x0000000001ae7749 in row_ins (node=0x7362710, thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3717
#6  0x0000000001ae7bae in row_ins_step (thr=0x7362980) at /mysql/mysql-5.7.26/storage/innobase/row/row0ins.cc:3853
#7  0x0000000001b05f73 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x736e7a0 "\375\003", prebuilt=0x7362170)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1738
#8  0x0000000001b06484 in row_insert_for_mysql (mysql_rec=0x736e7a0 "\375\003", prebuilt=0x7362170) at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1859
#9  0x00000000019adca9 in ha_innobase::write_row (this=0x736e4b0, record=0x736e7a0 "\375\003") at /mysql/mysql-5.7.26/storage/innobase/handler/ha_innodb.cc:7598
#10 0x0000000000f67dc0 in handler::ha_write_row (this=0x736e4b0, buf=0x736e7a0 "\375\003") at /mysql/mysql-5.7.26/sql/handler.cc:8062
#11 0x00000000017cf173 in write_record (thd=0x6dd5660, table=0x735afa0, info=0x7fffec435b50, update=0x7fffec435ad0) at /mysql/mysql-5.7.26/sql/sql_insert.cc:1873
#12 0x00000000017cc24a in Sql_cmd_insert::mysql_insert (this=0x6debbc8, thd=0x6dd5660, table_list=0x6deb638) at /mysql/mysql-5.7.26/sql/sql_insert.cc:769
#13 0x00000000017d2d7f in Sql_cmd_insert::execute (this=0x6debbc8, thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_insert.cc:3118
#14 0x000000000159a70c in mysql_execute_command (thd=0x6dd5660, first_level=true) at /mysql/mysql-5.7.26/sql/sql_parse.cc:3596
#15 0x00000000015a06c0 in mysql_parse (thd=0x6dd5660, parser_state=0x7fffec437610) at /mysql/mysql-5.7.26/sql/sql_parse.cc:5570
#16 0x0000000001595283 in dispatch_command (thd=0x6dd5660, com_data=0x7fffec437d80, command=COM_QUERY) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1484
#17 0x00000000015940bc in do_command (thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1025
#18 0x00000000016cbf91 in handle_connection (arg=0x6dda360) at /mysql/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:306
#19 0x0000000001921c64 in pfs_spawn_thread (arg=0x6d7d0f0) at /mysql/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#20 0x0000003823e079d1 in start_thread () from /lib64/libpthread.so.0
#21 0x0000003823ae8b6d in clone () from /lib64/libc.so.6

如果存在重復(fù)的行，這需要進(jìn)行判斷了：

  • 如果重復(fù)的行正在被其他事務(wù)持有，那么需要進(jìn)行進(jìn)行隱試鎖轉(zhuǎn)換，主鍵的轉(zhuǎn)換操作如下：

    ->lock_clust_rec_read_check_and_lock
 ->lock_rec_convert_impl_to_expl
    ->lock_rec_convert_impl_to_expl_for_trx

因?yàn)槲覀冎劳ǔnsert鎖并不會(huì)建立顯示的鎖。對(duì)于如果出現(xiàn)了重復(fù)的行，持有重復(fù)行數(shù)據(jù)的事務(wù)并沒有提交或者回滾，需要其事務(wù)完成提交或者回滾，然后再進(jìn)行相應(yīng)的拋錯(cuò)或者繼續(xù)插入。需要注意的是對(duì)于replace/insert on dup 在進(jìn)行唯一性檢查的時(shí)候，通常加的LOCK_S鎖，而其他操作通常加的是 LOCK_X。

  • 如果重復(fù)的行沒有其他事務(wù)持有，那么拋出重復(fù)行錯(cuò)誤，但是注意這里的錯(cuò)誤不是返回給客戶端的錯(cuò)誤，是內(nèi)部錯(cuò)誤HA_ERR_FOUND_DUPP_KEY，這個(gè)錯(cuò)誤在Innodb層叫做DB_DUPLICATE_KEY（convert_error_code_to_mysql）。如何處理這個(gè)錯(cuò)誤就和相應(yīng)的語法有關(guān)了。

當(dāng)然如果沒有重復(fù)的行，那么接下來就可以繼續(xù)進(jìn)行insert插入操作了，Insert ignore/replace實(shí)現(xiàn)都是進(jìn)行insert操作。如果有重復(fù)行呢？那么接下來進(jìn)行分析。

這里我們也很明白了，對(duì)于了insert ignore/replace是通過主鍵/唯一鍵進(jìn)行判斷是否重復(fù)行的，具體點(diǎn)來說就是如何處理錯(cuò)誤HA_ERR_FOUND_DUPP_KEY。 如果表中一個(gè)能夠判斷唯一性的索引都沒有，那么即便2條數(shù)據(jù)一模一樣也不會(huì)標(biāo)記為重復(fù)行，視為2條不同的數(shù)據(jù)，當(dāng)然insert on dup 這里也是同樣的邏輯。
在進(jìn)行唯一性檢測(cè)的時(shí)候，會(huì)先檢查主鍵的唯一性，然后依次檢查各個(gè)唯一索引的唯一性是否滿足。

2、可能的回滾操作

首先對(duì)于多行插入和insert select來講，每次innodb層插入的行數(shù)為1行，我們應(yīng)該牢牢樹立以行為單位的處理流程，我們可以在函數(shù)Sql_cmd_insert::mysql_insert 中找到 一個(gè)大的while 循環(huán)，這就是處理的循環(huán)。

我們也需要明白，進(jìn)行判斷唯一性的時(shí)候是先判斷主鍵的唯一性，如果滿足則插入主鍵數(shù)據(jù)，然后依次判斷二級(jí)唯一索引，如果滿足則進(jìn)行插入。這里涉及到一個(gè)問題，如果主鍵數(shù)據(jù)插入了，但是二級(jí)唯一索引由于違法唯一性那么，前面主鍵插入的數(shù)據(jù)是需要回滾的。再或者我們執(zhí)行的insert select操作，其中前面的一些行不違反唯一性插入了，但是隨后的某行違法了唯一性，那么前面插入的數(shù)據(jù)也是需要回滾的。函數(shù)row_insert_for_mysql_using_ins_graph 中進(jìn)行這種邏輯處理。

回滾棧幀：

#0  row_undo_ins (node=0x73685c0, thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/row/row0uins.cc:466
#1  0x0000000001b5d918 in row_undo (node=0x73685c0, thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/row/row0undo.cc:327
#2  0x0000000001b5dbae in row_undo_step (thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/row/row0undo.cc:411
#3  0x0000000001ab1847 in que_thr_step (thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/que/que0que.cc:1047
#4  0x0000000001ab1a63 in que_run_threads_low (thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/que/que0que.cc:1111
#5  0x0000000001ab1c25 in que_run_threads (thr=0x73671e8) at /mysql/mysql-5.7.26/storage/innobase/que/que0que.cc:1151
#6  0x0000000001bc622b in trx_rollback_to_savepoint_low (trx=0x7fffedc5b8c0, savept=0x7fffec434b60) at /mysql/mysql-5.7.26/storage/innobase/trx/trx0roll.cc:118
#7  0x0000000001bc64d2 in trx_rollback_to_savepoint (trx=0x7fffedc5b8c0, savept=0x7fffec434b60) at /mysql/mysql-5.7.26/storage/innobase/trx/trx0roll.cc:159
#8  0x0000000001b03b92 in row_mysql_handle_errors (new_err=0x7fffec434b5c, trx=0x7fffedc5b8c0, thr=0x7362980, savept=0x7fffec434b60)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:767
#9  0x0000000001b05ff2 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x736e7a0 "\375\n", prebuilt=0x7362170)
    at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1752
#10 0x0000000001b06484 in row_insert_for_mysql (mysql_rec=0x736e7a0 "\375\n", prebuilt=0x7362170) at /mysql/mysql-5.7.26/storage/innobase/row/row0mysql.cc:1859
#11 0x00000000019adca9 in ha_innobase::write_row (this=0x736e4b0, record=0x736e7a0 "\375\n") at /mysql/mysql-5.7.26/storage/innobase/handler/ha_innodb.cc:7598
#12 0x0000000000f67dc0 in handler::ha_write_row (this=0x736e4b0, buf=0x736e7a0 "\375\n") at /mysql/mysql-5.7.26/sql/handler.cc:8062
#13 0x00000000017cf173 in write_record (thd=0x6dd5660, table=0x735afa0, info=0x7fffec435b50, update=0x7fffec435ad0) at /mysql/mysql-5.7.26/sql/sql_insert.cc:1873
#14 0x00000000017cc24a in Sql_cmd_insert::mysql_insert (this=0x6debbc8, thd=0x6dd5660, table_list=0x6deb638) at /mysql/mysql-5.7.26/sql/sql_insert.cc:769
#15 0x00000000017d2d7f in Sql_cmd_insert::execute (this=0x6debbc8, thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_insert.cc:3118
#16 0x000000000159a70c in mysql_execute_command (thd=0x6dd5660, first_level=true) at /mysql/mysql-5.7.26/sql/sql_parse.cc:3596
#17 0x00000000015a06c0 in mysql_parse (thd=0x6dd5660, parser_state=0x7fffec437610) at /mysql/mysql-5.7.26/sql/sql_parse.cc:5570
#18 0x0000000001595283 in dispatch_command (thd=0x6dd5660, com_data=0x7fffec437d80, command=COM_QUERY) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1484
#19 0x00000000015940bc in do_command (thd=0x6dd5660) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1025
#20 0x00000000016cbf91 in handle_connection (arg=0x6dda360) at /mysql/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:306
#21 0x0000000001921c64 in pfs_spawn_thread (arg=0x6d7d0f0) at /mysql/mysql-5.7.26/storage/perfschema/pfs.cc:2190
#22 0x0000003823e079d1 in start_thread () from /lib64/libpthread.so.0
#23 0x0000003823ae8b6d in clone () from /lib64/libc.so.6

3、對(duì)重復(fù)錯(cuò)誤HA_ERR_FOUND_DUPP_KEY 的處理操作

如果有重復(fù)的行并且產(chǎn)生了錯(cuò)誤HA_ERR_FOUND_DUPP_KEY ，那么就不能進(jìn)行insert 操作了，這里就會(huì)根據(jù)不同的語法進(jìn)行不同的操作了。我們?cè)诤瘮?shù)（write_record ）中可以找到這種分支處理邏輯。

實(shí)際上在處理重復(fù)行錯(cuò)誤的時(shí)候，在內(nèi)部分為了3種方式如下：

enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_UPDATE };

  • DUP_ERROR：這個(gè)代表的就是普通的insert/insert ignore語句
    這是我們普通的操作，如果是insert操作則進(jìn)行拋錯(cuò)給客戶端，如果是insert ignore操作則不進(jìn)行報(bào)錯(cuò)，僅僅做一個(gè)警告，如下：
    ```
    /
      If IGNORE option is used, handler errors will be downgraded
      to warnings and don’t  have to stop the iteration. /

mysql> insert  ignore into tpk2 values(5,’g’,’m’);
Query OK, 0 rows affected, 1 warning (6 min 3.60 sec)

mysql> show warnings
   -> ;
+————-+———+———————————————————-+
| Level   | Code | Message                               |
+————-+———+———————————————————-+
| Warning | 1062 | Duplicate entry ‘5’ for key ‘PRIMARY’ |
+————-+———+———————————————————-+
1 row in set (0.00 sec)

但是需要注意的是，當(dāng)前版本報(bào)錯(cuò)后，自增值并不會(huì)回退。
- DUP_REPLACE：這里代表的是replace操作，也就是使用新的插入數(shù)據(jù)完全代替違反唯一性約束的行數(shù)據(jù)。我們通常理解的replace是delete/insert的結(jié)合，但是實(shí)際上并不完全是這樣，拋開外鍵觸發(fā)器等因素，如果違反的唯一性是最后一個(gè)檢查的唯一索引的時(shí)候，還是會(huì)使用update代替，其他情況下就是delete/insert的結(jié)合了。如下我們可以看看：

if (last_uniq_key(table,key_nr) &&//是否是檢測(cè)的最后一個(gè)唯一索引
       !table->file->referenced_by_foreign_key() &&
           (!table->triggers || !table->triggers->has_delete_triggers()))
       {
         if ((error=table->file->ha_update_row(table->record[1],
                           table->record[0])) && //調(diào)用了是update接口
             error != HA_ERR_RECORD_IS_THE_SAME)
         if (error != HA_ERR_RECORD_IS_THE_SAME)
           info->stats.deleted++; //影響行數(shù)+1
         goto after_trg_n_copied_inc;
       }
       else
       {
       …
         if ((error=table->file->ha_delete_row(table->record[1])))//刪除接口 delete
           goto err;
         info->stats.deleted++; //影響行數(shù)+1
        …
         / Let us attempt do write_row() once more ///這里會(huì)進(jìn)行一次循環(huán)進(jìn)行普通的insert操作
       }

- DUP_UPDATE：這個(gè)代表我們的語法insert into on dup，這里就完全等待于update語句了。需要注意的是，這里不是完全替代，會(huì)使用語句中的update進(jìn)行字段的更新，其他字段并不會(huì)更新，如下：
      if ((error=table->file->ha_update_row(table->record[1],
                                            table->record[0])) &&  //調(diào)入update接口
          error != HA_ERR_RECORD_IS_THE_SAME)
      {

…

      if (error != HA_ERR_RECORD_IS_THE_SAME)
        info->stats.updated++;//影響行數(shù)+1
      else
        error= 0;
再說對(duì)于普通的insert操作而言，影響的行數(shù)通常為1。replace/insert into on dup如果遇到了**重復(fù)行更改后**（注意不是直接插入成功的狀態(tài)），通常返回影響的行數(shù)為2如下：

mysql> replace testpri2(id,a,b) values(7,’b’,’k’);
Query OK, 2 rows affected (2.74 sec)

mysql> insert into testpri2(a,b) values(‘mmmmnb’,’ffhhh’) on DUPLICATE KEY UPDATE b=’bj’;
Query OK, 2 rows affected (3.81 sec)

- replace：如果是調(diào)用update接口則，不會(huì)真正執(zhí)行insert操作，會(huì)在info->stats.deleted++進(jìn)行影響行數(shù)+1，最后在insert接口中還會(huì)+1。如果是調(diào)用delete/insert操作會(huì)真正執(zhí)行insert操作，正常的info->stats.deleted++進(jìn)行+1操作，最后在insert接口中還會(huì)+1。因此它們結(jié)果還是2。
- insert into on dup：肯定調(diào)用update接口，不會(huì)真正執(zhí)行insert操作，會(huì)在 info->stats.updated++;進(jìn)行影響行數(shù)+1，然后會(huì)直接進(jìn)行info->stats.copied++進(jìn)行影響行數(shù)+1，因此為2。
因此不管怎么看起來都是影響行數(shù)為2，也不要奇怪。
###其他：
DML回執(zhí)接口：

0  my_ok (thd=0x6ddc080, affected_rows=1, id=0, message=0x7fffec4357d0 “Rows matched: 1  Changed: 1  Warnings: 0”) at /mysql/mysql-5.7.26/sql/sql_class.h:4753

1  0x0000000001652574 in mysql_update (thd=0x6ddc080, fields=…, values=…, limit=18446744073709551614, handle_duplicates=DUP_ERROR, found_return=0x7fffec435d28,

updated_return=0x7fffec435d20) at /mysql/mysql-5.7.26/sql/sql_update.cc:1092

2  0x00000000016580ea in Sql_cmd_update::try_single_table_update (this=0x6df2350, thd=0x6ddc080, switch_to_multitable=0x7fffec435dcf)

at /mysql/mysql-5.7.26/sql/sql_update.cc:2891

3  0x0000000001658637 in Sql_cmd_update::execute (this=0x6df2350, thd=0x6ddc080) at /mysql/mysql-5.7.26/sql/sql_update.cc:3018

4  0x000000000159a7ba in mysql_execute_command (thd=0x6ddc080, first_level=true) at /mysql/mysql-5.7.26/sql/sql_parse.cc:3606

5  0x00000000015a06c0 in mysql_parse (thd=0x6ddc080, parser_state=0x7fffec437610) at /mysql/mysql-5.7.26/sql/sql_parse.cc:5570

6  0x0000000001595283 in dispatch_command (thd=0x6ddc080, com_data=0x7fffec437d80, command=COM_QUERY) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1484

7  0x00000000015940bc in do_command (thd=0x6ddc080) at /mysql/mysql-5.7.26/sql/sql_parse.cc:1025

8  0x00000000016cbf91 in handle_connection (arg=0x6dd9480) at /mysql/mysql-5.7.26/sql/conn_handler/connection_handler_per_thread.cc:306

9  0x0000000001921c64 in pfs_spawn_thread (arg=0x6da4430) at /mysql/mysql-5.7.26/storage/perfschema/pfs.cc:2190

10 0x0000003823e079d1 in start_thread () from /lib64/libpthread.so.0

11 0x0000003823ae8b6d in clone () from /lib64/libc.so.6

關(guān)于什么是pt-online-schema-change問題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

當(dāng)前題目：什么是pt-online-schema-change
文章地址：http://bm7419.com/article48/psoghp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、微信小程序、外貿(mào)建站、云服務(wù)器、軟件開發(fā)、網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)