TXSQL Parallel DDL: Design, Implementation, and Performance

DDL (Data Definition Language) operations are critical but can be extremely slow, especially in cloud database scenarios where large tables cause multi‑day execution times and lock contention, making DDL efficiency a key indicator of database maturity.

TXSQL is a Tencent‑customized MySQL branch (Tencent‑MySQL) that adds enterprise features such as auditing, KMS encryption, and thread pools. It powers many internal services (QQ Space, WeChat Red Packet) and external customers (Pinduoduo, Bilibili).

To improve DDL efficiency, TXSQL implements a series of deep optimizations called Parallel DDL, covering Copy DDL, Inplace DDL, and Instant DDL.

1. DDL Types Overview

MySQL classifies DDL into three algorithm categories: Inplace DDL, Copy DDL, and Instant DDL. Online DDL refers to DDL that does not block concurrent DML.

1.1 Copy DDL

Copy DDL creates a temporary table with the same schema, locks the original table (no concurrent DML), copies rows one‑by‑one, then renames the temporary table.

mysql> ALTER TABLE xxx, ALGORITHM = COPY;

Copy DDL is simple but incurs high I/O because each row is read and inserted separately, and the table remains locked for the whole duration.

1.2 Inplace DDL

Inplace DDL avoids copying full table data; it may rebuild the table in place, holds a brief exclusive metadata lock, and usually allows concurrent DML.

mysql> ALTER TABLE xxx, ALGORITHM = INPLACE;

Inplace DDL operates at the storage‑engine layer, creating new index pages directly on the existing .ibd file, which reduces I/O and execution time, but it cannot handle operations that require data‑type conversion.

1.3 Instant DDL

Instant DDL modifies only metadata in the data dictionary; no data copy or table rebuild is needed, and concurrent DML is fully supported.

mysql> ALTER TABLE xxx, ALGORITHM = INSTANT;

Only a limited set of operations (e.g., adding/dropping virtual columns, changing index options) are supported.

2. Industry Parallel DDL Support

MySQL 8.0.27 introduced Parallel DDL for index creation (scan, sort, single‑thread merge). Other products such as PolarDB, GaussDB, TiDB, and OceanBase also provide parallel index creation, each with its own limitations.

3. TXSQL Parallel Inplace DDL

Launched in 2022, Parallel Inplace DDL parallelizes the three classic phases of Inplace DDL (scan, sort, B‑tree build) using configurable parameters scan_parallel_num and parallel_ddl_threads . The design partitions data by quantile points, performs parallel external sorting, and builds sub‑B‑trees concurrently before merging them into a final index.

// Example of quantile sampling structures
static void do_field_string(Copy_field *, const Field *from_field,
                            Field *to_field) { ... }

Performance tests on a 5‑billion‑row (114 GB) sysbench table show up to 24× speedup compared with the traditional single‑threaded approach and outperform MySQL’s official Parallel DDL.

4. TXSQL Parallel Copy DDL

Copy DDL is optimized by pushing the data‑copy loop into InnoDB and using a Parallel Reader framework. The workflow creates a temporary table, scans data ranges in parallel, converts rows using server‑side copy functions, and inserts them concurrently via InnoDB’s insert interface.

mysql_alter_table
  if (use_inplace) {
      mysql_inplace_alter_table() // Inplace path
  }
  // Fallback to copy path
  ha_create_table();
  lock_tables();
  copy_data_between_tables();
  wait_while_table_is_used();
  mysql_rename_table();

Benchmarks altering a column from INT to CHAR(10) on the same 5‑billion‑row table reduced execution time from ~1.5 hours to under 20 minutes.

5. Summary

TXSQL’s Parallel DDL suite (Parallel Inplace DDL, Parallel Copy DDL, and Instant DDL) dramatically accelerates most DDL operations, covering index creation, table rebuilds, and column modifications, while maintaining compatibility with existing MySQL syntax and tools.