Shulou(Shulou.com)06/01 Report--

Foreword:

DDL statements in MySQL, that is, data definition language, are used to create, delete, modify, library or table structures, and to manipulate the structure of a database or table. Common ones are create,alter,drop and so on. Such statements are usually costly, especially for large tables. This article will reveal the details of the execution of various DDL statements.

Introduction to 1.Online DDL

In earlier versions of MySQL, DDL operations greatly reduced concurrency because of lock conflicts between locking tables and DML operations. In the earlier version, most DDL operations were performed by rebuilding the table, because the original table data was copied, so the table was locked for a long time, and there was a serious conflict between the DDL operation and the DML operation. Starting from MySQL5.6, many DDL operations have been improved, and Online DDL has emerged to support the parallel operation of DML statements during DDL execution and improve database throughput.

MySQL online DDL is divided into INPLACE and COPY, which are specified by the ALGORITHM parameter in the ALTER statement.

ALGORITHM=INPLACE, which can avoid the IO and CPU consumption caused by rebuilding the table, and ensure good performance and concurrency during ddl. ALGORITHM=COPY, you need to copy the original table, so concurrent DML writes are not allowed and can be read. This copy approach is still not as efficient as inplace because the former needs to record undo and redo log, and performance is affected in a short period of time because of the temporary occupation of buffer pool.

The above is just the internal implementation of Online DDL, and there is also the LOCK option to control whether to lock the table, which varies according to the type of DDL operation: the default MySQL does not lock the table as much as possible, but expensive operations such as changing the primary key have to lock the table.

LOCK=NONE, that is, the tables involved in concurrent reading and writing are allowed during DDL. For example, in order to ensure that the registration or payment of users will not be affected during ALTER TABLE, you can specify clearly. The advantage is that if unfortunately the alter statement does not support continued writing to the table, it will prompt a failure and will not be sent directly to the library for execution. LOCK=SHARED, that is, writes on DDL interval tables are blocked, but do not affect reads. LOCK=DEFAULT, let mysql judge the mode of lock himself, and the principle is that mysql does not lock the table LOCK=EXCLUSIVE as much as possible, that is, the table is not available during DDL, blocking any read and write requests. If you want the alter operation to be completed in the shortest possible time, or if the table is not available in a short period of time and acceptable, you can specify it manually.

However, it is important to note that in any mode, an exclusive lock (exclusive) is required to prepare the environment before Online DDL starts, so after the alter command is issued, it will first wait for other operations on the table to complete, and requests after the alter command will wait for waiting meta data lock. Similarly, before the end of the DDL, you have to wait for all transactions to be completed during the alter, which will be blocked for a short period of time. So try to make sure there are no big transactions before ALTER TABLE, or there will be chained tables as well.

two。 Operation details of different kinds of DDL

The specific execution of different types of DDL statements is different. The following table lists the specific execution details of common DDL statements, including whether reading and writing are allowed and whether the table is locked. I hope you can compare this table in detail, and pay special attention to the DDL operations that require copy table.

Operation support mode Allow add/create indexonline W indicates that add/create indexonline allows reading and writing when there is an FULLTEXT index on the table, the table needs to be locked, blocking and writing drop indexonline allows read and write metadata, and does not involve table data. So soon, you can rest assured that optimize tableonline allows reading and writing when tables with fulltext index are in copy table mode and blocking writing alter table...engine=innodbonline allows reading and writing when tables with fulltext index are in copy table mode and blocking writing add columnonline allows reading and writing (except adding self-incrementing columns) 1. Add auto_increment columns to lock tables, block writes 2. Although online is adopted, the table data needs to be reorganized, so adding columns is still an expensive operation. Drop columnonline allows reading and writing (except adding self-increasing columns) to reorganize table data with add column, and expensive operation Rename a columnonline allows reading and writing metadata. Cannot change the type of column, otherwise lock table Reorder columnsonline allows read and write to reorganize table data, expensive operation Make column NOT NULLonline allows read and write to reorganize table data, expensive operation Change data type of columncopy table only supports read, block write to create temporary table, copy table data, expensive operation Set default value for a columnonline allows read and write operation metadata, because default value is stored in frm file and does not involve table data. So soon, you can rest assured that manipulating alter table xxx auto_increment=xxonline allows you to read and write to manipulate metadata without involving table data. So soon, you can rest assured that Add primary keyonline allows you to read and write expensive operations Convert character setcopy table only supports read, blocking writes if the new character set is different, you need to rebuild the table, expensive operation 3.DDL best practices

Although Online DDL operations are provided in MySQL versions 5.6 and 5.7, Online DDL still has the following problems:

Master-slave replication delay, which will be written to binlog only if the DDL on the master database is executed successfully, while DDL operations cannot be executed concurrently on the slave database, so even if the master database allows concurrent DML operations when performing DDL, it will still cause serious replication delays for large table operations. When the main library executes Online DDL, the DDL operation cannot be paused according to the load. For DDL executed in Inplace mode, it takes a certain time to roll back when an error occurs or is KILL. The longer the execution time, the longer the rollback time. The DDL executed by Copy needs to record the undo and redo logs in the process, and consumes the resources of buffer pool. It has the advantage of being inefficient and can be stopped quickly. Online DDL is not an Online for all time periods, and metadata locks or other locks are required for specific time periods. DDL that allows concurrent DML may cause Duplicate entry problems.

For DDL, the following practical information suggestions are sorted out, which can be referred to when executing the DDL statement:

Before executing DDL, check to see if the table is occupied by transactions to prevent MDL locks. Ensure that the datadir,tmpdir disk space is sufficient before performing the DDL. DDL that can operate during the business trough period is arranged as far as possible during the business trough period. For large and larger tables, if you are sensitive to replication latency and the performance of the main library, it is recommended to change to the gh-ost or pt-osc tool. For tables with high concurrent operations, no matter how much data the table has, it cannot be operated at the peak of the business. Multiple DDL statements of the same table can be merged to avoid the consumption of multiple table rebuild. But also pay attention to grouping, such as the need for copy table and only inplace can be done, should be divided into two alter statements.

Reference:

Https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlhttps://www.cnblogs.com/rayment/p/7762520.html

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.