MySQL DDL 深度解析：从语法到底层实现

最新推荐文章于 2025-04-30 15:39:56 发布

云艺编程

最新推荐文章于 2025-04-30 15:39:56 发布

阅读量859

点赞数 29

分类专栏： MYSQL基础文章标签： mysql adb 数据库

本文链接：https://blog.csdn.net/weixin_51786043/article/details/147364907

版权

MYSQL基础专栏收录该内容

20 篇文章

订阅专栏

MySQL DDL 深度解析：从语法到底层实现

一、DDL 概述

数据定义语言(Data Definition Language, DDL)是 SQL 中用于定义和管理数据库对象的语言。在 MySQL 中，DDL 主要包括以下操作：

与 DML(数据操作语言)不同，DDL 有以下特点：

自动提交事务，无法回滚
通常需要更高的权限
执行时会获取元数据锁(MDL)
对大型表操作可能引起长时间阻塞

二、CREATE 语句详解

1. 创建数据库

基本语法：

CREATE DATABASE [IF NOT EXISTS] db_name
    [CHARACTER SET charset_name]
    [COLLATE collation_name];

底层操作：

在数据目录下创建同名文件夹
创建 db.opt 文件存储字符集和排序规则
在内存中更新数据字典

示例：

CREATE DATABASE shop 
    CHARACTER SET utf8mb4 
    COLLATE utf8mb4_0900_ai_ci;

2. 创建表

完整语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

核心组件：

列定义
索引定义
约束条件
表选项(存储引擎、字符集等)

底层实现流程：

示例：

CREATE TABLE products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    category_id INT UNSIGNED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_category (category_id),
    CONSTRAINT fk_category FOREIGN KEY (category_id) 
        REFERENCES categories (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  ROW_FORMAT=DYNAMIC;

3. 创建索引

语法变体：

-- 创建表时定义
CREATE TABLE ... (
    ...,
    INDEX index_name (column_list),
    UNIQUE INDEX index_name (column_list)
);

-- 单独创建
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    ON tbl_name (column_list)
    [index_type]
    [algorithm_option | lock_option]

底层实现：

B+树结构(默认)
页分裂与合并机制
自适应哈希索引(AHI)

三、ALTER TABLE 深度解析

1. 修改列定义

常见操作：

-- 添加列
ALTER TABLE tbl_name ADD COLUMN col_name column_definition
    [FIRST|AFTER existing_column];

-- 修改列
ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition
    [FIRST|AFTER existing_column];

-- 重命名列
ALTER TABLE tbl_name CHANGE COLUMN old_col_name new_col_name column_definition;

底层实现：

创建临时表
复制数据
重命名交换表
清理旧表

性能影响：

大表操作可能非常耗时
会阻塞所有DML操作
在MySQL 8.0中某些操作支持INSTANT算法

2. 索引操作

语法示例：

-- 添加索引
ALTER TABLE tbl_name ADD INDEX index_name (column_list);

-- 删除索引
ALTER TABLE tbl_name DROP INDEX index_name;

在线DDL(MySQL 5.6+)特性：

允许并发DML操作
使用INPLACE算法避免表重建
进度监控

3. 分区表操作

分区维护：

-- 添加分区
ALTER TABLE tbl_name ADD PARTITION (PARTITION pn VALUES ...);

-- 重组分区
ALTER TABLE tbl_name REORGANIZE PARTITION p1 INTO (
    PARTITION p1 VALUES LESS THAN (100),
    PARTITION p2 VALUES LESS THAN (200)
);

底层机制：

每个分区对应独立的.ibd文件
分区裁剪优化查询

四、DROP 语句分析

1. 删除表

语法：

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

底层操作：

获取元数据锁
删除数据文件和索引文件
刷新缓冲池
更新数据字典

2. 删除数据库

危险操作：

DROP DATABASE [IF EXISTS] db_name

实际影响：

递归删除所有表文件
不可恢复(除非有备份)

五、TRUNCATE 与 RENAME

1. TRUNCATE TABLE

特点：

比DELETE更快
重置AUTO_INCREMENT值
无法触发触发器

实现机制：

对于InnoDB，实际是删除并重建表

TRUNCATE TABLE tbl_name;

2. RENAME TABLE

原子操作：

RENAME TABLE old_name TO new_name;

批量重命名：

RENAME TABLE t1 TO t2, t3 TO t1;

六、MySQL 8.0 DDL 新特性

1. 原子DDL

所有DDL操作完全成功或完全失败
崩溃安全
使用数据字典事务

2. INSTANT 算法

支持即时添加列(不重建表)：

ALTER TABLE tbl_name ADD COLUMN col_name INT DEFAULT 1, ALGORITHM=INSTANT;

3. 不可见列

CREATE TABLE t (
    id INT,
    name VARCHAR(100) INVISIBLE
);

七、DDL 性能优化建议

大表操作：
- 使用pt-online-schema-change工具
- 选择低峰期执行
- 考虑主从架构先修改从库
索引管理：
- 批量操作优于单个操作
- 使用INPLACE算法

监控进度：

SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

八、DDL 与存储引擎

不同存储引擎的DDL行为差异：

操作	InnoDB	MyISAM
添加列	需要重建表(8.0+支持INSTANT)	快速
删除列	需要重建表	需要重建表
添加索引	在线DDL可用	锁表
表空间管理	支持表空间加密	不支持高级表空间特性