Basic usage and speed-up optimization of the altertable command in MySQL _ MySQL

This article mainly introduces the basic usage and speed-up optimization methods of the altertable command in MySQL, including the use of ALTERCOLUMN. For more information, see I. Basic usage

1. add columns

alter table tbl_name add col_name type

For example, add a weight column to the pet table,

mysql>alter table pet add weight int;

2. delete columns

alter table tbl_name drop col_name

For example, delete the weight column in the pet table.

mysql>alter table pet drop weight;

3. change columns

It can be divided into changing column attributes and changing column names.

Change the attribute of a column -- Method 1:

alter table tbl_name modify col_name type

For example, changing the weight type

mysql>alter table pet modify weight varchar(30);

Change the attribute of a column -- Method 2:

alter table tbl_name change old_col_name col_name type

For example, changing the weight type

alter table pet change weight weight varchar(30);

Change the column name:

alter table tbl_name change old_col_name col_name

For example, change the weight name in the pet table:

mysql>alter table pet change weight wei;

4. change the table name

alter table tbl_name rename new_tbl

For example, rename the pet table to animal

mysql>alter table pet rename animal;

II. optimize 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 ‘666666';

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, 0rows affected, 1 warning (0.01 sec)
CREATEPROCEDURE SampleProc()
BEGIN
DECLARE xINT;
SET x = 1;
WHILEx  <= 110000 DO
insert intodictionary (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: 0mysql> SHOW STATUS WHERE Variable_name LIKE'Handler%'  ->OR Variable_name LIKE 'Created%';+----------------------------+--------+| Variable_name       | Value |+----------------------------+--------+| Handler_read_rnd_next   | 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