High-performance MySql Darwin (V): accelerating AlterTable

High-performance MySql evolution (5): during routine maintenance of the system, it is often necessary to update the table structure, such as adding or deleting a field and changing the length of a VARCHAR field. To modify the table structure, MySQL creates a new table, and then inserts the content of the old table

High-performance MySql evolution (5): during routine maintenance of the system, you often need to update the Table structure, such as adding/deleting a field and changing the length of a VARCHAR field. To modify the table structure, MySQL creates a new table, and then inserts the content of the old table

High-performance MySql evolution (V): accelerating Alter Table

During routine maintenance of the system, you often need to update the table structure, such as adding/deleting a field and changing the length of a VARCHAR field. To modify the table structure, MySQL creates a new table, inserts the content of the old table into the new table by executing the Insert statement, and deletes the entire old table. This processing method does not have any problems when the data volume is small, but it may take a lot of time to process this process when the data volume is large.

It took several hours to execute an update table structure operation, which is intolerable. If you are using a version earlier than 5.1, you will also execute the table structure update function, which usually stops the service. Fortunately, this problem has been improved in the latest version.

If you use the proper method when updating the table structure, not all update operations will take a long time.

For example, if you want to update the user table's default password to "666666 ",

Mysql> alter table user

-> Modify column pwd varchar not null default '20140901 ';

Using show status, you can find that a large number of Insert operations are performed during this operation. When the number of users is large, such as millions or tens of millions of data records, it will inevitably consume a lot of time.

However, if you use the following method to update, the time will be greatly shortened.

Mysql> alter table user

-> Alter column pwd varchar not null SETDEFAULT 5;

Execute the show status operation to find that a large number of insert operations do not exist, and the time is greatly shortened (you need to first perform flush status)

The possible reason is that

(1) The default value of the table field is placed in the table's frm (. frm: table structure file. MYD: Table data file. MYI: Table index) file.

(2) alter column updates the frm file instead of the table content.

(3) modify column involves table data.

We can see from the column above that if the operation only involves changes to the frm file, the update efficiency of the table structure will be greatly improved, however, mysql also recreates the table if it is not necessary. If you are willing to take risks, you can modify the frm file to speed up the modification of the table knot.

Not all table structure changes can be improved by modifying the frm file. The following changes can be updated by modifying the frm file:

(1) Change the default value of a field

(2) Add/Delete the field's AUTO_INCREMENT attribute

(3) Add/delete/modify the constant value of ENUM. For the delete operation, if a field references this constant value, the structure of the query after deletion is a null string.

The following uses the default attribute update field as an example to improve the efficiency of modifying the table structure by using alter column and modifying the frm file.

1. Execute ALTER COLUMN
1.1 first prepare a dictionary table

Createtable if not exists dictionary (

Id int (10) unsigned NOT NULLAUTO_INCREMENT,

Word varchar (100) not null,

Mean varchar (300) not null,

Primary key ('id ')

);

1.2 Insert some test data

Mysql> DELIMITER $

Mysql> drop procedure if exists SampleProc $

Query OK, 0 rows affected, 1 warning (0.01 sec)

CREATEPROCEDURE SampleProc ()

BEGIN

DECLARE xINT;

SET x = 1;

WHILEx <= 110000 DO

Insert into Dictionary (word, mean) values (concat ('A', x), concat ('a means ', x ));

SET x = x + 1;

End while;

END

Mysql> DELIMITER;

Mysql> call SampleProc ();

1.3 differences between the Modify Column and Alter Column observed by SHOW STATUS

Use MODIFY COLUMN

Mysql> flush status;

Query OK, 0 rows affected (0.00 sec)

Mysql> alter table dictionary

-> Modify column mean varchar (20) NOT null default 'default1 ';

Query OK, 110002 rows affected (3.07 sec)

Records: 110002 Duplicates: 0 Warnings: 0

Mysql> show status where Variable_name LIKE 'handler %'

-> OR Variable_name LIKE 'created % ';

+ ---------------------------- + -------- +

| Variable_name | Value |

+ ---------------------------- + -------- +

| Handler_read_rnd_next | 1, 110003 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 110002 |

+ ---------------------------- + -------- +

When using alter column

Mysql> flush status;

Mysql> alter table dictionary

-> Alter column mean set default 'default2 ';

Query OK, 0 rowsaffected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql> show statuswhere Variable_name LIKE 'handler %'

-> OR Variable_name LIKE 'created % ';

+ ---------------------------- + ------- +

| Variable_name | Value |

+ ---------------------------- + ------- +

| Handler_read_rnd_next | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 0 |

2. Modify the frm File

To improve the efficiency of modifying the table structure by modifying the frm file, follow these steps:

1. Back up related database files

2. Create a table with the same structure as the old table

Mysql> create table dictionary_new like dictionary;

3. Execute flush tables with read lock. All TABLES are disabled.

Mysql> alter table dictionary_new

-> Modify column mean varchar (30) default 'defaulr #';

Mysql> flush table with read lock;

5. Rename the dictionary_new.frm file dictionary. frm

6. Run the unlock tables command.

Mysql> unlock tables;

Mysql> insert into dictionary (word) values ('random ');

Mysql> select * from dictionarywhere word = 'random ';

The following results show that the default value has been changed and does not involve content changes.

+ -------- + ---------- +

| Id | word | mean |

+ -------- + ---------- +

| 110004 | Random | DEFAULR # |

+ -------- + ---------- +

7. Drop dictionary_new