Post

Mysql DDL

Posted Updated
By Xiong Ding 1 min read

The literature seems have different meaning for DDL. Some refers it as Data Definition Language. Others may refer it data dictionary language.

Online DDL

Online DDL refers to do db migration without shutting down read/write traffic. This post provides a great summary. Basically, most online DDL requires two metadata lock. One at the initialization stage. And the other at the final commit stage.

metadata lock state

When a DDL query is in the metadata lock state then any query that we run, be it DML or SELECT, will also be in metadata lock state.

TODO: read source code to verify it.

Questions:

  • How select query hold a metadata lock?

Foreign keys

Foreign keys are used to model a parent -> child relationship. It is naturally a one-to-many model: the child table has a column containing the parent’s primary key. Standard SQL requires this one-to-many relationship to be strict, meaning that the referencing columns in the parent table can only be the primary key or a unique index. However, Mysql/Innodb is different. Its foreign key can model the many-to-many relationship. Innodb only requires the referencing columns in the parent table to be a prefix an index. See code and code.

database, mysql
mysql
This post is licensed under CC BY 4.0 by the author.