preface

Insert into SELECT please use with caution.

Today XXX received A request to migrate the data from table A to table B as A backup. I wanted to find out through the program first query and then batch insert.

However, XXX felt that this was a bit slow and needed to consume a lot of network I/O, so he decided to adopt other methods to implement it.

By cruising Baidu, he discovered that he could use insert into SELECT to avoid network I/O and use SQL to rely on database I/O, which is not too good.

And then he was fired.

How the accident happened.

Due to the large amount of order_today data in the database, it seemed to be 700W at that time and was increasing by 30W every day.

So the boss ordered XXX to migrate some of the data in order_today to order_RECORD and delete the data in order_today.

This reduces the amount of data in the ORDER_TODAY table.

Considering that the database I/O will be occupied, in order not to affect the business, the plan is to start the migration after 9:00, but XXX tried to migrate a small part of the data (1000 pieces) at 8:00, thought there was no problem, and began to consider mass migration.

In the process of migration, the emergency group responds to the payment failure of a small number of users, and then responds to the payment failure of a large number of users, as well as the initial order failure. Meanwhile, Tencent also starts to alarm.

Then XXX panicked and immediately stopped the migration.

We thought that by stopping the migration we would be able to recover, but we didn’t. You can imagine what happens next.

The accident reduction

A compact version of the database was set up locally and 100W of data was generated. Simulate what happens on the line.

Create table structure

The order sheet

CREATE TABLE `order_today` (    
  `id` varchar(32) NOT NULL COMMENT 'primary key'.`merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Merchant No.'.`amount` decimal(15.2) NOT NULL COMMENT 'Order Amount'.`pay_success_time` datetime NOT NULL COMMENT 'Payment success time'.`order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Payment status S: Payment succeeded, F: order payment failed'.`remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'note'.`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time'.`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Change time -- updates automatically when you change',    
  PRIMARY KEY (`id`) USING BTREE,    
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT 'Merchant No.'    
) ENGINE=InnoDB DEFAULT CHARSET=utf8;    
Copy the code

Order sheet

CREATE TABLE order_record like order_today;

Order table data today

To simulate the migration

Migrate all data before 8th to order_RECORD table.

INSERT INTO order_record SELECT    
    *     
FROM    
    order_today     
WHERE    
    pay_success_time < '2020-03-08 00:00:00';    
Copy the code

Run the migrated SQL in Navicat, at the same time open another window to insert data, simulate the order

It can be seen from the above that the insertion was normal at the beginning, but then it suddenly stuck, and it took 23s to succeed, and then it could continue to insert. At this point, the migration has been successful, so you can insert normally.

Cause of occurrence

Insert into ORDER_RECORD SELECT * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY insert into ORDER_RECORD select * from order_TODAY

Analyze the execution process.

Order_today is a full table scan, which means that mysql scans the records in order_today from top to bottom and locks them, just as it would lock the table directly.

This can explain why only a small number of users failed to pay at the beginning, followed by a large number of users failed to pay, failed to initialize the order, etc.

Because only a small amount of data is locked at the beginning, data that is not locked can still be changed to the normal state.

As more and more data is locked, there are a lot of payment failures. Finally, they were all locked, so the order could not be inserted and the order failed to be initialized.

The solution

Since the query condition causes an ORDER_TODAY full table scan, what can avoid a full table scan?

It’s as simple as adding an idx_PAY_SUC_time index to the PAY_SUCCESS_time field.

Because of the index query, there will not be a full table scan and lock table, only the eligible records will be locked.

For more details on how to use MySQL indexes, see my previous index optimization series.

The final SQL

INSERT INTO order_record SELECT    
    *     
FROM    
    order_today FORCE INDEX (idx_pay_suc_time)    
WHERE    
    pay_success_time <= '2020-03-08 00:00:00';    
Copy the code

Implementation process

conclusion

Insert into tablA select * from tableB; insert into tablA select * from tableB; insert into tablA select * from tableB;

At the end

If you think it’s helpful, you can click “Like”. Thank you.

Source: juejin. Im/post / 6844904086173646862