Schema Changes


Welcome back.

Recent versions of MySQL have improved support for making schema changes, or DDL. For example, you can add indexes online, which means that while the index is being added, other users of the system can still read and write to the table. There are some limitations to this - for example, not all DDL changes are online. If you wanted to change a column from an integer to a bigint, that would require the table to be locked so that no write operations can occur during this operation.

Third party tools exist to address this limitation of MySQL DDL not being online. One of the most popular is gh-ost by GitHub.

But if we ignore for a second that not all changes are online, a second issue exists with DDL when you are using a sharded MySQL system, or MySQL with read replicas, and that is that the DDL change does not propagate everywhere at the same time. Each MySQL server could take a different length of time to make the change, and thus your application will need to correctly understand multiple versions of the schema. What we are describing here is a synchronization problem.

So now that we've talked about DDL in MySQL, you may remember that I mentioned earlier DDL is a major advantage of TiDB. In the next video, I will describe how and why TiDB is able to solve both of these two requirements for DDL. That is to say that in TiDB:

  1. All DDL changes are online
  2. Despite being distributed, all DDL changes appear everywhere at the same time