What is the practice of MySQL DDL operation
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.
