Shulou(Shulou.com)05/31 Report--

This article shows you how MySQL NDB updates 1 million rows of data in batches, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

A large table with more than 8 million rows in the production NDB database needs to update some fields. In order to reduce the impact on database performance, a stored procedure is written to implement:

1. Export the primary key fields of this table, with more than 1 million pieces of data

Select MSISDN from TEST where LastAccessTimeStamp=0

Into outfile'/ tmp/TEST_out.txt' fields terminated by','

two。 In one of the SQL nodes, create two temporary tables

Create the first temporary table to associate with the source table primary key of the production database to update some fields

Create table tmp_Subscribers_01 (id int not null auto_increment primary key,MSISDN char (20)) engine=innodb

-- Import previous export data to the first temporary table

Load data infile'/ tmp/Subscribers_out.txt' into table tmp_Subscribers_01 fields terminated by','(MSISDN)

-- create a second temporary table to record the progress of data updates

Create table tmp_Subscribers_02 (id int, MSISDN char (20), cdate datetime) engine=innodb

3. Write stored procedures for data updates

Drop procedure proc_Subscribers_update

Delimiter $$

Create procedure proc_Subscribers_update (IN v_fetch_cnt INT, IN v_sleep_secs INT)

Begin

DECLARE v_count INT

DECLARE v_times INT DEFAULT 1

DECLARE v_max_value INT

/ * compute the times that the loop runs*/

Select ceil (count (MSISDN) / v_fetch_cnt) into v_count from tmp_Subscribers_01

/ * compute the maximum rows that have been already updated*/

WHILE v_times

< v_count DO select ifnull(max(id),0) into v_max_value from tmp_Subscribers_02; if v_max_value < v_fetch_cnt * v_count then SET v_times = 1 + floor(v_max_value/v_fetch_cnt); update TEST s,tmp_Subscribers_01 t set s.LastAccessTimeStamp=1420066800 where s.MSISDN=t.MSISDN and t.id >

V_max_value and t.id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.