GeezerVison

命令行方式：

* 备份命令：mysqldump -u root -p 数据库名称 > 文件保存路径

* 恢复

1. 登录MySQL数据库：`mysql -u root p`

2. 删除已经备份的数据库

3. 重新创建与备份数据库名称相同的数据库

4. 使用该数据库

5. 导入文件执行：`source 备份文件全路径`

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

图形化界面：

* 备份

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

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


- 可使用空格和缩进来增强语句的可读性。

- MySQL 数据库的 SQL 语句不区分大小写，**关键字建议使用大写**。

- 数据库的注释：

- 单行注释：-- 注释内容 #注释内容(mysql特有)

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

- 多行注释：/* 注释内容 */

- SQL 分类

MEMORY 存储引擎：

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

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

- 应用场景：通常用于更新不太频繁的小表，用以快速得到访问结果，类似缓存

- 存储方式：表结构保存在 .frm 中

| 存储限制 | 有（平台对文件系统大小的限制） | 64TB | 有（平台的内存限制） |

| **事务安全** | **不支持** | **支持** | **不支持** |

| **锁机制** | **表锁** | **表锁/行锁** | **表锁** |

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

| 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

临时表分为内部临时表和用户临时表

* 内部临时表：系统执行 SQL 语句优化时产生的表，例如 Join 连接查询、去重查询等

执行创建临时表的 SQL：

创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出时会自动删除临时表，但备库同步线程是持续在运行的并不会退出，所以这时就需要在主库上再写一个 DROP TEMPORARY TABLE 传给备库执行

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

表的结构定义存放在后缀名为.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);

```

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

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

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

**主键是否连续对性能影响不大，只要是递增的就可以**，比如雪花算法产生的 ID 不是连续的，但是是递增的

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

* 插入 ID 顺序排列数据：

数据准备：

GROUP BY 也会进行排序操作，与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作，所以在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引

对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的**每个条件列都必须用到索引，而且不能使用到条件之间的复合索引**，如果没有索引，则应该考虑增加索引

* 可以使用 SELECT 语句来创建一个单列的查询结果，然后把结果作为过滤条件用在另一个查询中

* 使用子查询可以一次性的完成逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死

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

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

例如查找有角色的所有的用户信息：

MySQL 采用的是边读边发的逻辑，因此对于数据量很大的查询来说，不会在 Server 端保存完整的结果集，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是**不会把内存打爆导致 OOM**

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

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

SHOW PROCESSLIST 获取线程信息后，处于 Sending to client 状态代表服务器端的网络栈写满，等待客户端接收数据

说明：本章节知识是抄录自《MySQL 实战 45 讲》，作者目前没有更深的理解，后续有了新的认知后会更新知识

InnoDB 会对间隙（GAP）进行加锁，就是间隙锁 （RR 隔离级别下才有该锁）。间隙锁之间不存在冲突关系，**多个事务可以同时对一个间隙加锁**，但是间隙锁会阻止往这个间隙中插入一个记录的操作

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

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

* 可以**保护当前记录和前面的间隙**，遵循左开右闭原则，单纯的是间隙锁左开右开

* 假设有 10、11、13，那么可能的间隙锁包括：(负无穷,10]、(10,11]、(11,13]、(13,正无穷)

* 按照 Message Key 查询消息，IndexFile 索引文件为提供了通过 Message Key 查询消息的服务

实现方式：通过 Broker 端的 QueryMessageProcessor 业务处理器来查询，读取消息的过程用 Topic 和 Key 找到 IndexFile 索引文件中的一条记录，根据其中的 CommitLog Offset 从 CommitLog 文件中读取消息的实体内容

实现方式：通过 Broker 端的 QueryMessageProcessor 业务处理器来查询，读取消息的过程用 **Topic 和 Key** 找到 IndexFile 索引文件中的一条记录，根据其中的 CommitLog Offset 从 CommitLog 文件中读取消息的实体内容