MySQL DDL maintenance skills

During ddl maintenance of tables (such as adding fields), mysql locks the table. Unlike oracle, mysql only updates the dictionary table, which is extremely fast, this is because mysql uses the intermediate table method.

During ddl maintenance of tables (such as adding fields), mysql locks the table. Unlike oracle, mysql only updates the dictionary table, which is extremely fast, this is because mysql uses the intermediate table method.

Mysql ddl maintenance skills

During ddl maintenance of tables (such as adding fields), mysql locks the table. Unlike Oracle, mysql only updates the dictionary table, which is extremely fast, this is because mysql uses an intermediate table to implement

Ddl operation process:
A. Lock the table (the table is read-only at this time)
B. Copy the physical structure of the original table
C. Modify the physical structure of a table
D. Import the original table data to the intermediate table. After the data is synchronized, lock the intermediate table and delete the original table.
E. rename the intermediate table is the original table.
F. Refresh the data dictionary and release the lock.

The above process shows that the larger the table data volume, the longer the ddl maintenance process (the longer the table lock time ).

Table ddl Maintenance Method
1. Directly alter table operation
Eg:
Alter table tt4 add column age2 tinyint unsigned not null default 0;
Alter table tt4 modify column age2 tinyint unsigned not null default 0;
Alter table tt4 drop column age2;

However, this method is only suitable for tables that are not busy with services. It is faster for ddl operations and can change the environment variables of the session.
Increase the sort_buffer_size value at the SESSION level to accelerate the sorting operation required by the DDL process;
Increase the value of read_buffer_size at the SESSION level to increase the speed of sequential reading;
Increase the values of tmp_table_size and max_heap_table_size at the SESSION level to increase the memory cache data capability;
If the InnoDB engine is converted to MyISAM, we recommend that you increase the value of key_buffer_size online to increase the cache capability of the index data;

Add Field
1) adding a field must have a default value, non-NULL field attributes with a default value, which is conducive to improving the data retrieval performance and optimizing the index structure.
2). The order of the added fields cannot be specified and must be added at the end. Otherwise, more time is required for Data Reorganization and data disorder may occur during replication.

For example:
Alter table tt4 add column age2 tinyint unsigned not null default 0;

Absolutely prohibited: alter table tt4 add column age2 tinyint unsigned not null default 0 first;

Multiple fields are added to the same table, regardless of the number of statements. Only one change statement should be used (this method is also used when multiple indexes are created)

Alter table tt4
Add column age1 tinyint unsigned not null default 0,
Add column age2 tinyint unsigned not null default 0;

2. Add a dimension table to add fields
Without modifying the original table, you can add fields to the dimension table to read data by modifying the business logic or table Association. However, this method affects performance and daily maintenance. At the beginning of the design, adding a few reserved fields to the table is also a good idea, but it is limited after all.

3. Implemented through intermediate tables + triggers
For example, update Table.
1). lock Table a, create a trigger, and put the updated data (insert, update, delete) into the intermediate table
2). Release the lock
3). copy the physical structure a_bak of Table a, modify the ddl, and copy the data.
4) after the data is copied, lock Table. Synchronize the data in the intermediate table to a_bak, and lock a_bak.
5). Rename a_bak as the original table.
6) Delete the original table a after checking that it is correct.

4. Notes for ddl operations for table maintenance for Applications
1) when optimizing indexes, the program should be released first, and additional indexes should be deleted after testing.
2) Add a field or expand a field value. The program must be released first.
3). When changing the field data type, it is released at the same time with the program because it is a maintenance operation that requires downtime.
4). If there are downtime conditions, stop the maintenance. If there are no downtime conditions, select idle service maintenance ddl.

5. Principle Analysis and Operation Skills of drop table
When the InnoDB buffer pool is large and full of pages, dropping the table will hold the entire system hang. This is because mysql will traverse the LRU linked list of the buffer pool twice during this period, the system hang will be locked during traversal, and the larger the buffer pool, the longer the hang time.