DDL

DDL的定义

• Data Definition Language的缩写。
• 通过DDL语句定义不同的数据段、数据库、表、列、索引等数据库对象的元数据信息。通常由数据库管理员（DBA）使用。
• 主要的语法有CREATE，ALTER，DROP。主要作用的对象有database，table，view，index，column，event，trigger等。

create database if not exists library;
use library;
create table authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       varchar(20) character set utf8mb64,
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;
create table library.books
(
    isbn             varchar(20) not null default "" primary key,
    name             varchar(50) not null default "",
    first_name       varchar(20) character set utf8mb64,
    last_name        varchar(20) character set utf8mb64,
    price            decimal(20, 2)       default 0,
    press            varchar(512),
    publication_date date comment 'publication date',
    stock            int                  default 0
);

DATABASE级DDL

• 创建

create database if not exists library default character set = utf8;

• 修改属性

alter database library character set = utf8mb64;

• 删除

drop database library;

• 查看

show databases;
show create database library;

DATABASE级DDL注意点

• 数据库需保证全局唯一

• 字符集的选择：utf8 VS utf8mb64

  • utf8实际上就是utf8mb3，支持编码最大字符长度为3字节；utf8mb64是4个字节，utf8mb64是utf8的超集，能够存储Emoji表情，生僻的汉字等
  • 字符集选择优先级：表>库>系统
  • MySQL8.0默认字符集为utf8mb64

• ALTER DATABASE语句影响整个数据库下面的新增表，谨慎操作

• DROP DATABASE同时会删除库下所有的表，属于高危操作

  • 可以考虑先DROP每个table，最后DROP DATABASE

TABLE级DDL

TABLE级DDL - 原子的DDL

• 原子的DDL

  • 一个DDL操作要么执行成功，要么执行都不成功
  • 不可能存在元数据和数据文件不一致的状态

• MySQL5.X不支持原子的DDL

  • 尽量保证DDL执行过程不出现系统崩溃
  • 万一遇到DDL崩溃带来的不一致，需要人工介入，甚至需要重建库

• MySQL8.0支持原子的DDL

  • 更稳定更安全
  • 推荐使用

TABLE级DDL - 背景

• 表的类型

  • 普通表/分区表/临时表

• RDS for MySQL默认使用InnoDB存储引擎

  • 也支持其他社区版提供的引擎，但是不建议使用

• 注意表名大小写敏感参数lower_case_table_names

• 对表的操作需要指定数据库和表，否则默认使用当前数据库

  • 显示切换数据库USE library;，访问authors表
  • 或者通过library.authors引用

• DDL语句也会记录到Binlog中

  • 采用satement格式

TABLE级DDL - CREATE

create table library.authors
(
    first_name   varchar(20) character set utf8mb64,
    last_name    varchar(20) character set utf8mb64,
    gender       enum ('Male','Female'),
    age          tinyint unsigned default 0,
    address      varchar(512),
    phone_number varchar(20)
)
    engine = innodb
    row_format = dynamic
    character set = utf8mb64;

• 字段：first_name，last_name，gender，age，address，phone_number
• 数据类型：varchar(20)，enum，tinyint，varchar(512)
• 类型描述：character set utf8mb64，default 0
• 表属性：engine = innodb，row_format = dynamic，character set = utf8mb64

create table library.books
(
    isbn             varchar(20) not null default "" primary key,
    name             varchar(50) not null default "",
    first_name       varchar(20) character set utf8mb64,
    last_name        varchar(20) character set utf8mb64,
    price            decimal(20, 2)       default 0,
    press            varchar(512),
    publication_date date comment 'publication date',
    stock            int                  default 0
);

• 字段：isbn，name，first_name，last_name，price，press，published，stock
• 数据类型：varchar(20)，decimal(20,2)，date，int
• 类型描述：character set utf8mb64，default 0，comment ‘publication date’
• 索引：primary key
• 创建临时表：create temporapy table

TABLE级DDL - CREATE之表拷贝

• 克隆表结构
  • CREATE TABLE authors_clone LIKE authors;
  • 只拷贝表定义，不拷贝数据
• 拷贝表数据
  • CREATE TABLE authors_copy SELECT * FROM authors;
  • 既拷贝表结构，也拷贝数据

TABLE级DDL - ALTER概述

• 当现有表的结构不满足业务需求时，需要对表结构进行调整

• 修改前需要重点评估ALTER对现有业务的影响？

  • ALTER语句涉及的表有多大？
  • ALTER语句需要运行多久？
  • 当前的系统负载有多高，能够支持ALTER TABLE？
  • ALTER语句会不会影响DML？

• ALTER语句通常涉及到表元数据（和表记录）的修改

  • 有的ALTER只修改元数据
  • 有的ALTER也需要修改表数据
  • 影响到修改表的执行复杂度

TABLE级DDL - ALTER不改数据

• 目前一般有以下ALTER操作是指修改元数据不修改表数据

  • RENAME TABLE(ALTER TABLE…RENAME TO…)
  • SET DEFAULT
  • DROP DEFAULT
  • MODIFY COLUMN
  • CHANGE COLUMN(虚拟列生成算法)
  • Change index option
  • ADD virtual column, DROP virtual column
  • ADD COLUMN(非生成列，8.0)

• 其他操作，一般都涉及数据的修改

TABLE级DDL - ALTER的算法

• ALTER TABLE…,ALGORITHM=DEFAULT|COPY|INPLACE|INSTANT;

  • 如果不指定，或者指定DEFAULT，会自动选择最合适的算法
  • 算法优先级INSTANT>INPLACE>COPY

• 选取算法之前要了解它们的差异

算法	支持版本	是否修改数据	磁盘空间占用	并发DML	执行效率
COPY	5.6+	建立一张全新的表	大约额外一倍的表空间	不允许	低
INPLACE	5.6+	某些操作不改数据，某些操作会要建立全新的表，某些操作只会在原有的表上进行修改	对于要建立新表的操作，大约额外一倍的表空间，对于在原表修改的，新增的数据本身	大多数操作允许，极少部分不允许	如果新建表，中低效率，但一般都好于COPY，如果不新建表，效率中上
INSTANT	8.0	不修改数据	0	允许	最高

TABLE级DDL - ALTER改字段

• 加字段

  • ALTER TABLE authors ADD COLUMN title VARCHAR(20) ALTER address;
  • 新字段可以指定位置

• 改字段

  • ALTER TABLE authors CHANGE COLUMN status new_status VARCHAR(20) DEFAULT ‘zzzz’;
  • 很可能开销不大

• 删字段

  • ALTER TABLE authors DROP COLUMN title, DROP COLUMN new_status;
  • 总是个开销大的操作

TABLE级DDL - ALTER改索引

• 加索引

  • ALTER TABLE authors ADD INDEX (phone_number);
  • CREATE INDEX phone_number_idx ON authors(phone_number);

• 删索引

  • ALTER TABLE authors DROP INDEX phone_number;
  • DROP INDEX phone_number_idx ON authors;

• 需要的考量

  • 索引会加大DML的开销
  • 增加合适的索引加速查询
  • 不用的索引可以删除
  • 添加或修改主键索引的开销会很大

• 8.0上如何更好的评估索引-INVISIBLE INDEX

  • ALTER TABLE authors ADD INDEX (phone_number) INVISIBLE;
  • ALTER TABLE authors ADD INDEX (phone_number) VISIBLE;

TABLE级DDL - TRUNCATE

• 语法
  • TRUNCATE TABLE authors;
• TRUNCATE TABLE被看作是DDL，而不是DML
• 本质上它删除表，再重建一张表
• 清空表内所有的行，重置AUTO_INCREMENT
• 版本间行为差异
  • 5.6/5.7，崩溃之后，操作一般是重做到提交
  • 8.0根据操作是否成功决定是提交还是回滚

TABLE级DDL - DROP

• 语法

  • DROP TABLE [IF EXISTS] authors;
  • DROP TABLE [IF EXISTS] authors,books;
  • DROP TEMPORARY TABLE …;

• 'IF EXISTS’能够避免表不存在的报错

• 谨慎使用删表操作，数据将无法轻易找回

其他常见DDL

其他常见DDL - VIEW

• 视图（VIEW）是关联查询的虚表

  • 定义会持久化，只和创建时的表快照相关
  • 其查询出来的数据仍在原表中
  • 原表可以是普通表或临时表

• 比如书籍价格的视图

  • CREATE VIEW books_price_view AS SELECT name,price FROM books;

其他常见DDL - TRIGGER

• 触发器（Trigger）可以定义某个表上某个事件的关联操作

  • 同一个表上触发器的类型和个数没有限制
  • 可以定义触发器发生的顺序
  • 常用的触发事件包括INSERT/UPDATE/DELETE

• 比如更新authors后同步更新books表

DROP TRIGGER IF EXISTS auto_update_books_trig;

DELIMITER  $$
CREATE TRIGGER auto_update_books_trig
    AFTER UPDATE
    ON library.authors
    FOR EACH ROW
BEGIN
    IF (Old.first_name <> New.first_name or Old.last_name <> New.last_name) THEN
        UPDATE library.books
        SET first_name=New.first_name,
            last_name=New.last_name
        WHERE first_name = Old.first_name
          AND last_name = Old.last_name;
    END IF;
END $$
DELIMITER ;
SHOW TRIGGERS;

其他常见DDL - PROCEDURE

• 存储过程（Procedure）支持整合和抽取业务逻辑到一个函数执行

  • 支持参数传入传出
  • 执行使用CALL procedure_name();

• 比如对一定库存的书籍进行打折

DROP TRIGGER IF EXISTS discount_books_proc;
DELIMITER $$
CREATE PROCEDURE discount_books_proc(IN max_stock INT, IN discount FLOAT)
BEGIN
    UPDATE library.books SET price=price * discount WHERE stock > max_stock;
END $$
DELIMITER ;

其他常见DDL - EVENT

• 定时器（Event）支持指定任务的定时调度

  • 可以定义定时器发生时间，频率，有效期
  • 可以定义定时器，但是先不让它执行-设置为DISABLE
  • 定时器不支持参数传都，但是可以调用存储过程
  • 可以使用ALTER EVENT来修改定时器的定义
  • 需设置event_scheduler=on;来开启所有生效的定时器

• 比如每周对库存超过10本的书籍打9折

DROP EVENT IF EXISTS discount_books_weekly_event;
CREATE EVENT discount_books_weekly_event
    ON SCHEDULE
        EVERY 1 WEEK
    ON COMPLETION PRESERVE DISABLE
    DO
    CALL discount_books_proc(10, 0.9);
ALTER EVENT discount_books_weekly_event ON COMPLETION PRESERVE ENABLE;
SHOW EVENTS;

DML

DML的定义

• A data manipulation language(DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML
  comprising some of the operators in the language. Read-only selecting of data is sometimes distinguished as being part of a separate data query language(DQL), but it is closely related and sometimes also considered a component of a DML;
  some operators may perform both selecting (reading) and writing.
• DML主要涉及对用户数据的增删改
• 有些DML语句会涉及到数据的查询加修改
• MySQL的DML主要包括INSERT/UPDATE/DELETE三类
• DML语句都要记录binlog，针对每一行的修改

INSERT

TABLE级的DML - INSERT概述

• INSERT语句插入新的记录到指定表当中

  • INSERT …VALUES/INSERT …SET插入指定的数据
  • INSERT …SELECT插入查询出来的数据
  • INSERT …ON DEPLICATE KEY UPDATE在唯一索引或者主键索引冲突时可以更新原有行

• 插入数据的限制和转换，基于SQL_MODE不做检查

  • 插入NULL值到NOT NULL字段，数值一般转换成字段的默认值
  • 插入数值型范围之外的值，会截断到最近的范围值
  • 插入比如’10.34a’到数值字段，非数值部分会被截断
  • 插入字符串类型，如果长度超过，会被截断到合理长度

TABLE级的DML - INSERT语句

• 插入单条记录

  • INSERT INTO authors VALUES(‘John’,‘Smith’,‘Male’,30,‘Room X, Building Y’,‘12345678901’);

• 插入多条记录

  • INSERT INTO authors VALUES(‘Robert’,‘White’,‘Male’,33,‘Room U,Building I’,‘12345678902’),(‘Linda’,‘White’,‘Female’,36,‘Room P,Building O’,‘12345678903’);

• 插入查询结果集

  • CREATE TABLE authors_bak LIKE authors;
  • INSERT INTO authors_bak SELECT * FROM authors;

• 重复主键或唯一键执行更新操作

  • INSERT INTO authors VALUES(‘John’,‘Smith’,‘Male’,30,‘Room X,Building Y’,‘1234’) ON DUPLICATE KEY UPDATE age=age+5;

• 替换或插入

  • REPLACE INTO…
  • REPLACE INTO authors VALUES(‘Robert’,‘White’,‘Male’,33,‘Room U,Building I’,‘86-1234’),(‘Linda’,‘White’,‘Female’,36,‘Room P,Building I’,'86-2345);

TABLE级的DML - INSERT与AUTO_INCREMENT

• 对于使用AUTO_INCREMENT自增字段作为主键索引的表，它和DML的交互需要仔细考虑

• AUTO_INCREMENT自动递增字段可以建表的时候指定也可以动态添加

  • ALTER TABLE authors ADD COLUMN id INT AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id),DROP PRIMARY KEY;

• 建表时要考虑可能插入的数据量，避免字段值越界导致无法插入

• INSERT可以不需要指定该字段的值，它会自动递增生成，保证唯一

• 如果INSERT指定该字段的值，则之后的INSERT会从表内该字段的最大值继续递增

• INSERT INTO … SELECT会产生空洞，但不影响唯一

UPDATE

TABLE级的DML - UPDATE概述

• UPDATE用来更新表内的一行或者若干行
• 可以指定更新条件-WHERE
• 可以一次更新一张表或者多张表
  • 单表更新，可以指定LIMIT限制更新的行数
  • 多表更新无法指定LIMIT
• 注意UPDATE的语义（和标准SQL不一样的地方）
  • UPDATE t1 SET col1=col1+1,col2=col1;
  • col1和col2将会有相同的值
• 注意UPDATE的性能
  • 修改了二级索引包含的列会导致索引更新
  • 修改了主键会导致所有索引都要更新

TABLE级的DML - UPDATE语句

• 更新所有记录：年龄增加一岁

  • UPDATE authors SET age=age+1;

• 更新单条记录：将指定作者的电话进行更新

  • UPDATE authors SET phone_number = ‘86-123456’ WHERE first_name = ‘John’;

TABLE级的DML - UPDATE与AUTO_INCREMENT

ID	first_name	last_name	gender	age	address	phone_number
1	John	Smith	Male	30	Room X,Building Y	123456
2	Robert	Smith	Male	45	Room Q,Building W	123456
3	Robert	White	Male	33	Room U,Building I	123456

5.6,5.7	8.0
UPDATE authors SET id=5 WHERE id=3;	UPDATE authors SET id=5 WHERE id=3;
SELECT auto_increment FROM information_schema.tables WHERE table_name LIKE ‘%authors%’;–返回4	SELECT auto_increment FROM information_schema.tables WHERE table_name LIKE ‘%authors%’;–返回6
INSERT INTO authors VALUES(0,‘Linda’,‘White’,‘Female’,36,‘Room P’,12345);–成功，id=4	INSERT INTO authors VALUES(0,‘Linda’,‘White’,‘Female’,36,‘Room P’,12345);–成功，id=6
INSERT INTO authors VALUES(‘Alice’,‘Baker’,‘Female’,28,‘Room Y’,12345);–ERROR 1062(23000):Duplicate entry ‘5’ for key ‘PRIMARY’	INSERT INTO authors VALUES(‘Alice’,‘Baker’,‘Female’,28,‘Room Y’,12345);–成功，id=7

DELETE

TABLE级的DML - DELETE概述

• 删除表记录当记录不再需要

  • 按条件删除：DELETE … WHERE …;
  • 全表删除：DELETE FROM authors;
  • 指定删除数量：DELETE … LIMIT n;

• 可以支持删除单张表或者多张表的记录

• 删除一条记录会影响到所有二级索引

• InnoDB存储引擎删除全表记录后，AUTO_INCREMENT值不会重置

• 删除单条记录

  • DELETE FROM authors WHERE first_name = ‘Robert’;

TABLE级的DML - DELETE优化

• 全表删除应该用TRUNCATE TABLE来替代

  • 性能更好
  • 系统开销更少，尤其是日志IO和存储开销

• 当要删除一个InnoDB大表中绝大部分数据

  • INSERT INTO t_copy SELECT * FROM t WHERE …;
  • RENAME TABLE t TO t_old,t_copy TO t;
  • DROP TABLE t_old;