# 存储引擎

MySQL 常见的几种存储引擎：
- MyISAM ：一个传统引擎。不支持外键、事务、行级锁，只支持表级锁。
- InnoDB ：从 MySQL 5.5 开始成为默认引擎，支持外键、事务、行级锁。
MySQL 同一个数据库中的不同数据表，可采用不同的存储引擎。

# 事务

InnoDB 引擎支持事务，可以将连续执行的多个 SQL 命令声明为一个事务：

BEGIN;           -- 开始一个事务，也可写作 START TRANSACTION
...
ROLLBACK;        -- 回滚到 BEGIN 时的状态
...
SAVEPOINT p1;    -- 声明一个回滚点
...
ROLLBACK TO p1;  -- 回滚到指定位置
...
COMMIT;          -- 提交当前事务

# 数据库锁

关于表级锁的命令：

LOCK TABLES [<tb> <lock_type>],...    -- 给指定表加锁，使当前客户端会话获得表锁，例如 LOCK TABLES tb1 WRITE, tb2 READ;
UNLOCK TABLES                         -- 释放当前会话的所有表锁

FLUSH TABLES                          -- 强制关闭所有被打开的表，并将写缓存中的数据写入磁盘
            tb,...                    -- 只处理指定的表
            WITH READ LOCK            -- flush 之后加上全局只读锁。这是全局锁，因此不需要获得 lock tables 的表锁

SHOW STATUS LIKE 'Table_locks%';      -- 统计获得表锁的耗时

客户端执行 LOCK TABLES 或 START TRANSACTION 时，都会隐式地释放已获得的表锁。
- 客户端断开会话时，服务器会释放其获得的表锁。

关于行级锁的命令：
```
SELECT * FROM ... WHERE ... LOCK IN SHARE MODE      -- 给查询到的所有数据行加行级共享锁
SELECT * FROM ... WHERE ... FOR UPDATE              -- 加行级排它锁

SHOW STATUS LIKE 'InnoDB_row_lock%';                -- 统计获得行锁的耗时
SELECT * FROM performance_schema.data_locks;        -- 显示所有获取行锁的请求，包括已经获得的、等待获得的
SELECT * FROM performance_schema.data_lock_waits;   -- 显示 data_lock_waits 中的哪些请求在被哪些请求阻塞
```
- InnoDB 的行锁是通过给索引中的索引键加锁来实现的。
  - 如果不使用索引进行查询，则行锁不起作用，只能使用表锁。
  - 如果针对不同的数据行加行锁，却使用相同的索引键，则也会发生锁冲突。
- InnoDB 在申请行锁时，会先隐式地申请该表的意向锁（intention lock），类型也为共享或排它。
  - 意向锁是一种特殊的表锁，表示意图对该表加行锁。
    - 意向锁不与意向锁冲突。
    - 意向共享锁与表级共享锁不冲突，其它意向锁与表级锁都冲突。
  - 意向锁由 InnoDB 自动获得、释放，客户端不能控制。
    - 使用意向锁，InnoDB 能更快地发现表级锁是否冲突。
  - 例：
    1. 事务 A 执行 SELECT * FROM tb1 WHERE id=1 FOR UPDATE; ，先请求获得对 tb1 表的意向排它锁，成功之后再请求获得对 id=1 的数据行的排它锁。
    2. 事务 B 执行 SELECT * FROM tb1 WHERE id=2 FOR UPDATE; ，与事务 A 不冲突，能够获得意向排它锁、行级排它锁。
    3. 事务 C 执行 LOCK TABLES tb1 READ; ，请求获得表级只读锁，但 tb1 表已有意向排它锁，因此阻塞等待。
- InnoDB 提供的行锁属于悲观锁，用户可以自己编程实现乐观锁。如下：
```
SELECT name FROM tb1 WHERE id = 1;                    -- 先查询下修改之前的值，这里假设此时 name 的值为 'one'
UPDATE tb1 SET name='two' WHERE id=1 AND name='one';  -- 执行之后，根据返回值判断是否修改成功
```
  - 可以根据 timestap 等字段来判断数据是否被修改。

# DDL

MySQL 修改数据表的 DDL 语句，有多种实现算法：
- copy
  - ：拷贝一个新表，步骤如下：
    1. 新建一个新表。
    2. 锁定原表，禁止 DML ，允许 DQL 。
    3. 将原表的数据逐行拷贝到新表。
    4. 删除原表，将新表 RENAME 为原表。
  - 缺点：
    - 需要两倍的存储空间。
    - 会阻塞 DML 操作，可能中断客户端业务。
- inplace
  - ：在原表中进行修改，分为两种方式：
    - rebuild-table ：需要重建数据表，因此会消耗大量时间、磁盘 IO 。
    - not-rebuild-table ：默认尽量采用这种。
  - 缺点：
    - 会锁定原表，阻塞 DML 操作。
  - 于 MySQL v5.5 加入。
- instant
  - ：于 MySQL v8.0 加入，只支持少量 DDL 操作。
Online DDL ：用于在不中断客户端业务的情况下执行 DDL 操作。
- MySQL v5.6 给 InnoDB 引擎增加了 Online DDL 功能，并默认启用。
- 可以主动指定 Online DDL 策略，如下：
```
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
```
  - ALGORITHM 表示 DDL 算法，可取值：
    - DAFAULT ：自动选择最高效的算法。
    - COPY
    - INPLACE
  - LOCK 表示加锁类型：
    - DAFAULT ：尽量选择最低的锁，从而允许并发 DML 。
    - NONE ：不加锁。
    - SHARED ：共享锁。
    - EXCLUSIVE ：排它锁。
  - 如果指定的策略不支持当前的 DDL 操作，则会报错：
```
ALTER TABLE tb1 RENAME TO tb2, LOCK=NONE;
> 1845 - LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
```

# innodb_data

InnoDB 引擎的大部分数据都保存在系统表空间（System Tablespace）中，包括：
- 每个表的数据页、索引
- change buffer
- data dictionary
- doublewrite buffer
- undo log
系统表空间在磁盘保存为一个或多个文件。
- 早期的 InnoDB 引擎，将系统表空间都保存在 $datadir/ibdata1 文件中。
  - InnoDB 引擎不会减小 ibdata1 文件的体积。即使 DELETE 了一些数据，也不会释放磁盘空间，而是留给未来使用。
  - 可以通过 mysqldump 重建数据库，从而减小 ibdata1 文件。
- MySQL v5.6.6 开始，默认设置了 innodb_file_per_table=ON ，将每个数据表的数据页、索引分别保存到 $datadir/<db_name>/<table_name>.ibd 文件中，而 ibdata1 文件用于保存其它数据。
  - 这样 DROP TABLE 时会自动释放磁盘空间。

# 表碎片

InnoDB 表以 B+ tree 结构存储数据。删除一行数据时，并不会释放存储空间，而是标记为 free ，留待以后写入新数据。这些 free 空间称为碎片（fragment）。
- 执行 DELETE、UPDATE 操作可能产生碎片。
- 如果碎片空间比新数据行体积小，则一直不会用于写入新数据。
- 一般按 data_free / (data_length + index_length) 计算碎片率。
- 如果碎片率长时间较高，建议手动清理碎片，从而减少占用的存储空间、减少读取表的耗时。
- 每个数据表占用的磁盘空间等于：全部数据行 + 索引 + 碎片空间。