更多方式参考：https://time.geekbang.org/column/article/81925

grant 语句会同时修改数据表和内存，判断权限的时候使用的是内存数据

flush privileges 语句本身会用数据表（磁盘）的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下使用，这种不一致往往是由于直接用 DML 语句操作系统权限表导致的，所以尽量不要使用这类语句


- 单行注释：-- 注释内容 #注释内容（MySQL 特有）

- 特点：每个 MEMORY 表实际对应一个磁盘文件 ，该文件中只存储表的结构，表数据保存在内存中，且默认**使用 HASH 索引**，所以数据默认就是无序的，但是在需要快速定位记录可以提供更快的访问，**服务一旦关闭，表中的数据就会丢失**，存储不安全

| B+Tree 索引 | 支持 | 支持 | 支持 |

自增主键的插入数据模式，可以让主键索引尽量地保持递增顺序插入，不涉及到挪动其他记录，**避免了页分裂**

分区表是将大表的数据按分区字段分成许多小的子集，建立一个以 ftime 年份为分区的表：

CREATE TABLE `t` (

`ftime` datetime NOT NULL,

`c` int(11) DEFAULT NULL,

KEY (`ftime`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

PARTITION BY RANGE (YEAR(ftime))

(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,

PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

INSERT INTO t VALUES('2017-4-1',1),('2018-4-1',1);-- 这两行记录分别落在 p_2018 和 p_2019 这两个分区上

这个表包含了一个.frm 文件和 4 个.ibd 文件，每个分区对应一个.ibd 文件

* 对于引擎层来说，这是 4 个表，针对每个分区表的操作不会相互影响

* 对于 Server 层来说，这是 1 个表

打开表行为：第一次访问一个分区表时，MySQL 需要**把所有的分区都访问一遍**，如果分区表的数量很多，超过了 open_files_limit 参数（默认值 1024），那么就会在访问这个表时打开所有的文件，导致打开表文件的个数超过了上限而报错

通用分区策略：MyISAM 分区表使用的分区策略，每次访问分区都由 Server 层控制，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题

本地分区策略：从 MySQL 5.7.9 开始，InnoDB 引擎内部自己管理打开分区的行为，InnoDB 引擎打开文件超过 innodb_open_files 时就会**关掉一些之前打开的文件**，所以即使分区个数大于 open_files_limit，也不会报错

从 MySQL 8.0 版本开始，就不允许创建 MyISAM 分区表，只允许创建已经实现了本地分区策略的引擎，目前只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略

从 Server 层看一个分区表就只是一个表

* Session A：

```mysql

SELECT * FROM t WHERE ftime = '2018-4-1';

```

* Session B：

```mysql

ALTER TABLE t TRUNCATE PARTITION p_2017; -- blocked

```

现象：Session B 只操作 p_2017 分区，但是由于 Session A 持有整个表 t 的 MDL 读锁，就导致 B 的 ALTER 语句获取 MDL 写锁阻塞

分区表的特点：

* 一个是第一次访问的时候需要访问所有分区

* 在 Server 层认为这是同一张表，因此所有分区共用同一个 MDL 锁

* 在引擎层认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问需要的分区

分区表的优点：

* 对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。

* 分区表可以很方便的清理历史数据。按照时间分区的分区表，就可以直接通过 `alter table t drop partition` 这个语法直接删除分区文件，从而删掉过期的历史数据，与使用 drop 语句删除数据相比，优势是速度快、对系统影响小

使用分区表，不建议创建太多的分区，注意事项：

* 分区并不是越细越好，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表

* 分区不要提前预留太多，在使用之前预先创建即可。比如是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可，并且对于没有数据的历史分区，要及时的 drop 掉。

参考文档：https://time.geekbang.org/column/article/82560

自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑

表的结构定义存放在后缀名为.frm 的文件中，但是并不会保存自增值，不同的引擎对于自增值的保存策略不同：

* MyISAM 引擎的自增值保存在数据文件中

* InnoDB 引擎的自增值保存在了内存里，每次打开表都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为当前的自增值；8.0 版本后，才有了自增值持久化的能力，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值

在插入一行数据的时候，自增值的行为如下：

* 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段

* 如果插入数据时 id 字段指定了具体的值，比如某次要插入的值是 X，当前的自增值是 Y

* 如果 X<Y，那么这个表的自增值不变

* 如果 X≥Y，就需要把当前自增值修改为新的自增值

参数说明：auto_increment_offset 和 auto_increment_increment 分别表示自增的初始值和步长，默认值都是 1

语句执行失败也不回退自增 id，所以保证了自增 id 是递增的，但不保证是连续的（不能回退，所以有些回滚事务的自增 id 就不会重新使用，导致出现不连续）

MySQL 不同的自增 id 在达到上限后的表现不同：

* 表的自增 id 如果是 int 类型，达到上限 2^32-1 后，再申请时值就不会改变，进而导致继续插入数据时报主键冲突的错误

* row_id 长度为 6 个字节，达到上限后则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据，造成旧数据丢失，影响的是数据可靠性，所以应该在 InnoDB 表中主动创建自增主键报主键冲突，插入失败影响的是可用性，而一般情况下，**可靠性优先于可用性**

* Xid 长度 8 字节，由 Server 层维护，只需要不在同一个 binlog 文件中出现重复值即可，虽然理论上会出现重复值，但是概率极小

* InnoDB 的 max_trx_id 递增值每次 MySQL 重启都会被保存起来，重启也不会重置为 0，所以会导致一直增加到达上限，然后从 0 开始，这时原事务 0 修改的数据对当前事务就是可见的，产生脏读的现象

只读事务不分配 trx_id，所以 trx_id 的增加速度变慢了

* thread_id 长度 4 个字节，到达上限后就会重置为 0，MySQL 设计了一个唯一数组的逻辑，给新线程分配 thread_id 时做判断，保证不会出现两个相同的 thread_id：

```c++

do {

new_id = thread_id_counter++;

} while (!thread_ids.insert_unique(new_id).second);

```

1. **主键顺序插入**：因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率，如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键

主键是否连续对性能影响不大，只要是递增的就可以，比如雪花算法产生的 ID 不是连续的，但是是递增的，因为递增可以让主键索引尽量地保持顺序插入，**避免了页分裂**，因此索引更紧凑

* 在有些情况下，**子查询是可以被更高效的连接（JOIN）替代**

<img src="https://seazean.oss-cn-beijing.aliyuncs.com/img/DB/MySQL-查询内存优化.png" style="zoom:50%;" />

InnoDB 加锁的基本单位是 next-key lock，该锁是行锁和 gap lock 的组合（X or S 锁），但是加锁过程是分为间隙锁和行锁两段执行