This guide explains MySQL DDL mechanisms, compares COPY, INPLACE, and INSTANT algorithms across versions, details Online DDL execution phases and risks, and provides practical production‑grade indexing practices and interview Q&A for handling large tables without blocking reads or writes.
This article details Xiaohongshu's RedSQL MySQL kernel project, describing three major solutions—high‑throughput seckill optimization, a Binlog Server‑based zero‑data‑loss replication scheme, and second‑level DDL column addition—along with additional kernel enhancements that together delivered multi‑fold performance gains and improved stability.
When a core order table with tens of millions of rows needs a new business field, the article explores why a direct ALTER TABLE can lock the table, evaluates master‑slave switching, online DDL tools, extension tables, JSON fields, and a clever reuse of an existing redundant column, providing practical steps and performance insights.
When a core order table with tens of millions of rows needs a new business field, executing a plain ALTER TABLE can lock the table and disrupt services, so the article explores master‑slave switching, online schema‑change tools, extension tables, JSON fields, and a clever reuse of an existing redundant column to achieve the change with minimal risk.
When a core order table with tens of millions of rows needs a new field, a naive ALTER TABLE can lock the table and disrupt services, so this article explores master‑slave switching, online DDL tools, schema‑less extensions, and other low‑cost strategies to add the column safely.
When a core order table with tens of millions of rows needs a new column, naïve ALTER TABLE can lock the table and disrupt services, so this article explores safe alternatives such as master‑slave switching, online DDL tools, extension tables, JSON‑based schema‑less designs, and clever reuse of existing redundant fields, complete with practical code snippets and lessons learned.
Learn the differences between MySQL’s copy and in‑place (online) DDL algorithms, how they manage locks, the steps of each process, the new Instant Add Column feature in MySQL 8, and practical strategies—including master‑slave switching—to safely alter tables in production.
A production migration from Oracle to OceanBase caused a column‑length change to trigger offline DDL, leading to connection errors, INSERT latency spikes, and complete table blockage; the article reproduces the fault, analyzes the OMS conversion and OceanBase DDL rules, and provides a two‑step remediation and a method to verify online DDL execution.
A production OceanBase Oracle‑mode database experienced excessive Incr‑Sync delay due to a DDL timeout, and the article walks through log analysis, the oracleSocketTimeout parameter issue, configuration changes, verification steps, and final recommendations to resolve the blockage.
After a normal DDL change on OneDBA, the table size grew by nearly 100 % and some queries became slow because InnoDB’s B+Tree page‑splitting logic mishandles rows larger than 8 KB, causing each page to hold only one record and corrupting statistics.
After a normal DDL change on the OneDBA platform, a table's storage grew nearly 100% and some queries slowed down, revealing a MySQL InnoDB B+Tree page‑split flaw that stores only one row per 16KB page when large records are inserted, which also corrupts statistics and leads to slow SQL.
When a core order table with tens of millions of rows needs a new column, naïve ALTER TABLE can lock the table and cause outages, so this article explores master‑slave switching, online schema‑change tools, JSON or redundant fields, and practical tips for low‑risk, production‑grade schema evolution.
Learn how to create, execute, and troubleshoot a simple MySQL stored procedure using MySQL 8.0, covering delimiter changes, basic SQL syntax, result verification, common beginner mistakes, and extending the concept with parameterized examples.
An experienced DBA explains why adding a new column in MySQL can unexpectedly raise duplicate entry errors during online DDL, detailing the role of primary and unique keys, the innodb_online_alter_log_max_size setting, and how concurrent DML operations cause the conflict.
This article walks through the background, reproduction steps, investigation methods, and concrete solution for MySQL metadata lock (MDL) issues that cause DDL statements to hang, and provides practical tips to prevent similar lock problems in production environments.
This article explains InnoDB lock types, their characteristics, and how MySQL 5.6 and 8.0 handle adding a column without fully locking the table, detailing online DDL, atomic DDL, and performance considerations for large tables.
This article explains foreign key concepts in relational databases, showing how to create, modify, and drop foreign key constraints with SQL code, provides hands‑on exercises, discusses the pros and cons of using foreign keys, and outlines when they are appropriate or should be avoided.
MySQL 9.1.0, released on October 15, 2024, introduces DDL atomicity, improved audit handling, new JavaScript defaults, VECTOR type support, enhanced GROUP REPLICATION logging, expanded EXPLAIN output, and numerous bug fixes across SQL syntax, performance, and security, while the 8.4.3 LTS patch remains feature‑free.
This guide provides a comprehensive collection of MySQL command‑line scripts covering database and table export/import, creation, deletion, schema inspection, data manipulation, user privilege management, and common DDL operations, all with practical examples.
While MySQL 5.7 native DDL methods (copy, inplace rebuild, index build, metadata‑only) vary in speed, space use, lock impact, and binlog output, GH‑OST consistently offers the fastest execution, lowest lock blocking, real‑time replication, albeit at roughly double storage cost, making it ideal for risk‑averse online schema changes.
This article provides a comprehensive guide to MySQL's DQL, DML, and DDL sections, explaining execution order, core clauses, and practical code examples for creating, altering, and managing databases and tables.
This article explains InnoDB lock concepts, differentiates read and write locks, discusses table‑level versus row‑level locking, and examines how MySQL 5.6 and 8.0 handle adding columns without fully locking tables, highlighting online DDL, atomic DDL, and performance considerations.
This article thoroughly explains how the Percona pt-osc tool performs online schema changes in MySQL, analyzes trigger design, deadlock scenarios, data‑consistency issues, and provides detailed test cases, operational tips, and best‑practice recommendations for safe usage.
A detailed post recounts a production outage caused by improper index handling in a massive MySQL table, walks through MySQL lock diagnostics, compares index behavior on MySQL and TiDB, and distills practical recommendations for safe DML, DDL, and index management.
A Java developer recounts a production MySQL outage caused by a faulty batch insert, walks through failed delete and lock‑free attempts, then details a series of table‑recreation, truncate, and index‑rebuilding steps that finally restored data integrity and performance.
When a user reported errors, I discovered a MySQL table flooded with millions of bad rows from a new group, causing slow queries and crashes; I detail the step‑by‑step investigation, failed attempts with DELETE and lock‑free changes, and the final successful rebuild using temporary tables, DROP, TRUNCATE, and index reconstruction.
An in‑depth tutorial walks through the five core SQL language families—DDL, DML, DQL, DCL, and TCL—explaining their purposes, key commands, and practical examples, while highlighting differences and usage tips for database schema definition, data manipulation, querying, permission control, and transaction management.
This article explains MySQL index structures, covers covered and back‑table queries, details InnoDB transaction isolation levels, classifies various lock types (table, row, gap, next‑key, intention, auto‑inc, metadata), analyzes lock behavior in multiple scenarios, discusses deadlock causes, and provides DDL and online schema‑change recommendations.
This article analyzes a large MySQL table that caused SLA alerts due to master‑slave lag, examines slow‑query logs with pt‑query‑digest, identifies index and delete inefficiencies, proposes index redesign and batch‑delete strategies, and validates the improvements with testing and DDL tools.
This guide compiles essential MySQL command‑line scripts covering database and table export, import, creation, alteration, permission granting, and common DDL/DML operations, providing clear examples for managing schemas, data, and user privileges in a MySQL environment.
This article explains the differences between MySQL DDL and DML, describes how online DDL works—including copy and inplace algorithms, execution phases, common pitfalls, limitations, and recent MySQL 8.0 enhancements—providing practical guidance for safely altering large tables.
This article explains how to identify when a MySQL DDL operation is blocked, locate the blocking session using the sys.schema_table_lock_waits view, and resolve the issue by enabling necessary instruments and killing the appropriate connection, with examples for MySQL 5.7 and 8.0.
This article explains the challenges of slow DDL operations in MySQL, introduces TXSQL’s Parallel DDL framework—including Copy, Inplace, and Instant algorithms—details their design, implementation, code examples, and performance evaluations, and compares them with other database solutions.
This article details a comprehensive approach to optimizing a large MySQL table, including slow‑query analysis with pt‑query‑digest, index redesign, backup and restore using mydumper, online DDL versus pt‑osc, and batch deletion techniques to reduce latency and storage fragmentation.
This guide walks you through Oracle's storage architecture, explains when and why to use partitioned tables, details each partition type, and provides step‑by‑step SQL examples for creating, managing, and modifying range, list, hash, and composite partitions.
This guide compiles a comprehensive set of MySQL command‑line scripts for exporting whole databases, individual tables or schemas, importing data, and performing common database and table operations such as creation, selection, alteration, deletion, granting privileges, and viewing structure, all illustrated with ready‑to‑use code examples.
This article analyzes performance problems of a massive MySQL table, demonstrates how to use pt‑query‑digest and EXPLAIN to identify inefficient composite indexes, proposes index redesign, compares online DDL with pt‑osc for index rebuilding, and presents batch‑delete techniques to reduce SLA‑impacting latency.
This article details a comprehensive performance investigation of a massive MySQL table, using pt‑query‑digest to analyze slow queries, exposing index inefficiencies, proposing index redesign, demonstrating backup/restore with mydumper, comparing online DDL and pt‑osc for index rebuilding, and recommending small‑batch deletions to reduce latency and maintenance overhead.
This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.
This article details a comprehensive analysis of a large MySQL table's performance issues, including slow query diagnostics with pt‑query‑digest, index evaluation, backup and restore procedures, online DDL versus pt‑osc, and practical strategies for index redesign and batch deletions to reduce latency and maintenance overhead.
The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.
Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.
This article introduces and compares three MySQL online schema‑change solutions—pt‑online‑schema‑change, gh‑ost, and native MySQL ONLINE DDL—detailing their workflows, limitations, risks, monitoring features, and practical considerations for safely altering large tables in production environments.
This article analyses why physical backups using Xtrabackup fail when MySQL 5.7 performs online DDL operations that skip redo logging, demonstrates the issue with real‑world logs, and presents four practical solutions—including lock‑DDL parameters, per‑table MDL locks, and disabling online DDL—to ensure consistent backups.
The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
This article evaluates the open‑source SQLE SQL auditing platform against MySQL 8.0.28, demonstrating how DDL, DML, DCL statements and their contextual relationships are checked, highlighting successful detections, mismatches caused by new MySQL 8.0 features, and concluding on overall compatibility.
This article explains how DTLE supports pt-osc table schema changes by describing pt-osc's underlying mechanism, DTLE's DDL capabilities, and step‑by‑step instructions for deploying DTLE, preparing MySQL data, configuring a DTLE job, running pt‑online‑schema‑change, and verifying successful synchronization.
This guide walks through SQL’s language categories—DDL, DML, DQL, DCL, and TCL—then demonstrates practical database and table management commands such as creating and dropping databases, defining tables with various constraints (not‑null, default, primary/foreign keys, unique, auto‑increment), and modifying or copying fields and tables.
MySQL 8.0 replaces duplicated server‑level and engine‑level metadata with a unified data dictionary stored in InnoDB, introduces a two‑level cache (local and shared) built on templated hash maps, and provides atomic DDL operations, dramatically improving metadata consistency, performance, and management simplicity.
This article offers a thorough overview of relational database fundamentals and SQL syntax, detailing core concepts, clause structures, DDL/DML/TCL/DCL commands, functions, transaction handling, user privileges, stored procedures, cursors, and triggers with practical code examples.
This article provides a detailed tutorial on relational database fundamentals, covering key terminology, SQL syntax, DDL/DML/DCL/TCL commands, query clauses, joins, set operations, functions, aggregation, indexing, constraints, transactions, permission management, stored procedures, cursors, and triggers with practical code examples.
This article provides a comprehensive collection of MySQL command-line instructions for exporting databases or tables, importing data, managing databases and tables, performing DDL and DML operations, handling field types, and configuring user privileges, all illustrated with practical code examples.
The release of MyBatis‑Mate 1.0.1 introduces enterprise‑grade features such as dictionary binding, field encryption, data desensitization, automatic DDL maintenance, and dynamic multi‑datasource sharding, accompanied by code examples, future roadmap, and Maven dependencies for seamless integration into Spring Boot projects.
This article demonstrates how to use a GDB script to monitor the exact moments MySQL checks for a killed DDL operation, identifies the internal InnoDB functions involved, and explains why kill may not take effect immediately.
This article provides a thorough overview of relational database fundamentals, SQL syntax structures, data definition and manipulation languages, transaction control, permission management, as well as detailed examples of CRUD operations, subqueries, joins, unions, functions, constraints, stored procedures, cursors, and triggers, all illustrated with practical code snippets.
This article examines the pain points of MySQL DDL operations for large tables, analyzes the limitations of online DDL, compares tools like gh‑ost and pt‑osc, and outlines a comprehensive workflow and implementation details for an automated online DDL work‑order system.
This article provides a thorough overview of relational database fundamentals, covering basic concepts, SQL syntax, DML and DDL commands, functions, joins, unions, grouping, ordering, transactions, permission management, stored procedures, cursors, and triggers, complete with illustrative code examples.
The article explains the three main types of SQL statements—DDL for defining database objects, DML for manipulating data, and DCL for controlling access—detailing their purposes, common keywords, and typical usage scenarios in relational database systems.
This article introduces the classification of SQL statements and provides practical DDL and DML commands for creating, altering, and querying databases and tables, including examples of creating databases, showing database information, and modifying table structures.
This guide provides step‑by‑step instructions for exporting entire MySQL databases or individual tables, importing data, performing common DDL and DML operations such as creating, altering, renaming, and dropping databases and tables, managing fields, and configuring user permissions, all illustrated with concrete command‑line examples.
This article provides a comprehensive overview of fundamental MySQL concepts, covering DDL, DML, DCL, and TCL commands, detailed explanations of first to third normal forms, common anomalies, and practical examples of converting between horizontal and vertical table structures using SQL scripts.
This article provides a comprehensive overview of relational database fundamentals and SQL syntax, covering basic concepts, statement categories (DDL, DML, TCL, DCL), CRUD operations, subqueries, joins, unions, functions, ordering, grouping, constraints, transactions, permission management, stored procedures, cursors, and triggers with illustrative code examples.
This article provides a concise overview of relational database fundamentals, covering key terminology, the structure of SQL statements, essential DDL/DML/TCL/DCL commands, query operators, joins, subqueries, functions, indexing, constraints, transactions, permissions, stored procedures, cursors, and triggers, with practical code examples throughout.
This article outlines a systematic approach to diagnosing and resolving DDL execution hangs in Dble, covering log inspection, context analysis, identifying problematic data nodes and MySQL connections, and confirming lock contention as the root cause.
This article explains how to create a JetBrains IntelliJ plugin that parses MySQL DDL statements, performs lexical analysis to extract table and column information, and automatically generates corresponding Python ORM model classes, illustrating the underlying state‑machine lexer, recursive parsing logic, and sample code.
This article introduces the gh-ost tool for performing online schema changes on large MySQL tables, explains its architecture and operation modes, provides detailed command‑line parameters and practical examples, and compares its features and performance with the traditional pt‑osc solution.
This article explains MySQL's Instant Add Column feature, describing the traditional costly column‑addition process, the instant method’s internal mechanics, how data is read and written before and after the change, and the practical constraints that limit its usage.
This article explains how MySQL 8.0 InnoDB handles DDL statements, describes the five classification dimensions (Instant, In‑Place, Rebuild Table, Concurrent DML, Metadata‑only), provides examples of each cost level, and offers operational recommendations for safely applying schema changes.
This article explains the three MySQL DDL algorithms (COPY, INPLACE, INSTANT), compares third‑party tools like pt‑online‑schema‑change and gh‑ost, and provides practical guidance on selecting algorithms, using ALGORITHM clauses, monitoring progress, and handling performance, space, replication lag, and metadata lock issues.
This article explains the challenges of MySQL DDL on large tables, details the built‑in COPY, INPLACE, and INSTANT algorithms, compares third‑party tools like pt‑online‑schema‑change and gh‑ost, and provides practical guidance on minimizing impact, monitoring progress, and handling metadata locks.
This guide provides a concise review of fundamental database concepts—including definitions of databases, RDBMS, SQL, and tables—and walks through essential SQL query techniques, data manipulation commands, schema definition statements, and control language basics, offering practical examples for data analysts.
This article provides a concise introduction to essential SQL concepts, covering database creation, manipulation, table design, constraints, indexes, and common DDL statements, making it a useful reference for beginners and experienced developers alike.
This article details Ele.me's practical experience building a cross‑region active‑active database system, covering latency challenges, architectural design, extensive database refactoring, DBA operational hurdles, consistency verification tools, and future scalability plans.
This guide explains Oracle SEQUENCE objects—how they generate numeric IDs, the full CREATE SEQUENCE syntax, practical usage with NEXTVAL and CURRVAL, alteration options, querying via data‑dictionary views, and safe removal—complete with runnable SQL examples.
This article provides a clear overview of the four main SQL query language categories—DDL, DML, DCL, and TCL—explaining their purposes, typical commands, practical examples, and why each is essential for effective database design and manipulation.
This guide walks you through installing MariaDB via binary packages, explains its configuration file hierarchy, demonstrates essential client and server commands for data definition and manipulation, and details how to create users and manage their privileges effectively.
A rare Oracle bug caused object IDs to exceed their maximum value, preventing creation of tables, sequences, and indexes; the article analyzes the error, shows how rapid DDL activity exhausts IDs, demonstrates reproduction steps, and offers short‑ and long‑term mitigation and monitoring recommendations.
This article compiles frequently used MySQL SQL commands—including data migration, multi‑table updates, conditional updates, aggregation inserts, and various ALTER TABLE operations such as adding indexes, dropping and adding columns—to serve as a practical reference for database developers.
This article walks through Oracle’s massive SQL reference, explains essential database concepts, details the original set of commands in version 6, and then chronicles the new DDL/DML features introduced in each subsequent release up to 12c, helping readers master Oracle’s syntax evolution.
This article details MySQL performance enhancements covering Group Commit redesign, XA recovery handling, DDL fast‑fail implementation, GTID allocation optimizations, and a fix for InnoDB auto‑increment duplication, each supported by concrete test data and code changes.
This article explains MySQL's Group Commit mechanism, DDL fast‑fail enhancements, GTID allocation optimizations, fixes for InnoDB auto‑increment duplication after restart, and the evolution of replication performance from 5.5 to 5.7, including detailed code snippets and benchmark results.
This article presents a comprehensive technical overview of MySQL performance tuning and feature enhancements—including group‑commit redesign, DDL fast‑fail, GTID handling, persistent auto‑increment, replication parallelism, and key‑partition hash changes—backed by real‑world test data from Taobao's large‑scale deployment.
