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

This article is to share with you about the practice of MySQL DDL operation, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

According to the DDL tree diagram on the Internet, I copy a copy: https://cache.yisu.com/upload/information/20200310/35/76997.jpg?_=1547952296662

This article is well written about the handling of different versions of add index: https://www.jb51.net/article/75217.htm

--

Related practice

Table structure:

CREATE TABLE `tt` (`id` int (11) DEFAULT NULL, `age` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8row in set (0.01 sec)

Number of rows:

Data_migration@192.168.7.21:3306 test > select count (*) from tt;+-+ | count (*) | +-+ | 12582912 | +-+ data_migration@192.168.7.21:3306 test > alter table tt add index idx_id (id); Query OK, 0 rows affected (27.49 sec) Records: 0 Duplicates: 0 Warnings: 0

Add id index

Temporary table: only # sql-1e65_68.frm added, no idb added

Data volume change: 448m-> 676m

Data_migration@192.168.7.21:3306 test > alter table tt add name1 varchar (12); Query OK, 0 rows affected (44.72 sec) Records: 0 Duplicates: 0 Warnings: 0

New field

Temporary table: add # sql-1e65_68.frm, # sql-ib231-4246726206.ibd, add # sql-ib255-4246726207.ibd without tt.ibd when completed, and finally generate tt.idb

Change in the amount of data: the temporary table has been increased to the same amount of data as the new table, and finally the new table has been replaced, the new table 676m-> 740m

-rw-r- 1 mysql mysql 740m January 8 11:30 # sql-ib231-4246726206.ibd mysql mysql r-1 mysql mysql 8.4K January 8 11:06 tt.frm-rw-r- 1 mysql mysql 676m January 8 11:08 tt.ibd [root @ dbtest-21 test] # ll-ah-rw-r- 1 mysql mysql 8.5K January 8 11:30 # sql-1e65_68 .frm-rw-r- 1 mysql mysql 740m January 8 11:30 # sql-ib231-4246726206.ibdMohamrw mysql mysql 8.4K January 8 11:06 tt.frm-rw-r- 1 mysql mysql 676m January 8 11:08 tt.ibd [root @ dbtest-21 test] # ll-ah-rw-r- 1 mysql mysql 8.5K January 8 11:30 # sql-1e65 _ 68.frmRwmurr-1 mysql mysql 740M January 8 11:30 # sql-ib231-4246726206.ibdWhen RWMUR-1 mysql mysql 676M January 8 11:08 # sql-ib255-4246726207.ibdKuhr-1 mysql mysql 8.4K January 8 11:06 tt.frm [root@dbtest-21 test] # ll-ah-rw-r- 1 mysql mysql 8.5K January 8 11:30 tt.frm-rw-r- 1 mysql mysql 740M January 8 11:30 tt.ibddata_migration@192.168.7.21:3306 test > alter table tt modify age varchar (20) Query OK, 12582912 rows affected (2 min 36.80 sec) Records: 12582912 Duplicates: 0 Warnings: 0

Modify field

Temporary table: add # sql-1e65_68.frm, # sql-ib231-4246726206.ibd, add # sql-ib255-4246726207.ibd without tt.ibd when completed, and finally generate tt.idb

Change in the amount of data: the temporary table is increased to the same amount of data as the new table, and the replacement of the new table is finally completed.

Time increase

TIPS:

1. Add an empty field, and the disk will take up extra space for the new field.

two。 Modify the new empty field type, which can be done in an instant.

--

Algorithm = copy/inplace

Copy creates temporary tables all the time, and inplace creates temporary tables in different scenarios (rebuild, no-rebuild).

Copy: copy to tmp table, DML is not allowed during this period

Inplace: altering table allows concurrent DML, using the parameter innodb_online_alter_log_max_size to cache new writes

The above is what the practice of MySQL DDL operation is, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.