Paip. Solve the Problem of adding a column field to mysql. Adding a field is slow. # The environment is as follows: mysql5.6 data is only 3waltertablexxxaddcolumnyyyintdefault0; adding a field is slow, but it is not good in several copies .. # Cause and solution [SQL] altertablegrejx_defaddcolumnflag12intdefault0; affected
Paip. it is slow to add columns in mysql. # The environment is as follows: mysql5.6 only 3 w alter table xxx add column yyy int default 0; it is slow to add fields, but it is not good in several copies .. # Cause and solution [SQL] alter table grejx_def add column flag12 int default 0; affected
Paip. Solve the Problem of slow adding column fields to mysql database
# The environment is as follows:
Only 3 w of mysql5.6 data
Alter table xxx add column yyy int default 0;
It is slow to add fields ..
# Cause and Solution
[SQL] alter table grejx_def add column flag12 int default 0;
Affected rows: 0
Not the lock -- copoy -- rewrite method. effeic row sh 0. Why is it so slow ??
Oracle 11g's quick field adding function is good ..
Data Structure disorder, as long as no table is optimized for hanging ..
Word rumors 0.3s go OK LAN ..
Author old wow's paw Attilax iron, EMAIL: 1466519819@qq.com
Reprinted please indicate Source: http://blog.csdn.net/attilax
# The process of adding a field or modifying a structure in the mysql database is as follows:
Because mysql online ddl (operations such as adding fields and adding indexes to modify the table structure) are as follows:
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.
Slow field addition
Alter table grejx_def add column flag int default 0;
# Big Data Solutions
# Stop the mysql server to modify the table structure. Then perform rolling update.
It takes several months for Facebook to add indexes to thousands of MySQL servers (later they developed the tools mentioned later, only a few days)
# Test whether mysql copies a table before adding a field to reduce the risk of application freezing:
After the command is complete, check whether the value of "rows affected" is displayed. For example, you may see that different types of DDL operations are performed:
Copy the table structure and insert a small amount of data. Modify the table structure. View the affected rows. If the value is 0, it indicates that the intermediate table will not be copied.
# Online-schema-change
Is a mysql online ddl tool launched by percona.
# New Ideas (manual mysql execution)
1: manually create the modified table structure NEW_TAB. (For example, adding a field and modifying the table field type. You can delete table fields at one time)
For MyISAM tables, you can set the myisam_sort_buffer_size system variable to a high value to speed up index re-creation (this operation is the slowest part of the change process.
2: You can selectively Insert the OLD_TAB data of the old table to the modified table. (The processing of massive data is important here. Table records can be inserted in batches according to the primary key. In fact, if Table shards are processed, they can also be modified in the Business Code .)
3: rename TABLE name. (The lock table in the RENAME process can be processed in the early morning when the service is idle ).
# Reference
Risks and solutions for modifying the table structure of large data tables online in mysql
This document describes the online-schema-change tool,
MYSQL online DDL operation copying data-zuoxingyu-bokyue.htm
