数据库基础和原理
数据库设计的三范式
第一范式：数据库表中的每一列都是不可再拆分
第二范式：主键约束，数据库表中的每一行具有唯一性，并且非主键字段完全依赖主键
第三范式：外键约束，所有非主键字段不能传递依赖
注意：数据库的三范式是层层递进，第二范式建立在第一范式的基础上，第三范式建立在第二范式的基础上
但是平时我们在设计数据库表的时候会打破第三范式，添加适当的冗余字段，减少表的join，提高查询效率
关系代数
- 集合运算：
- 交集：两个或多个集合之间的共有元素。
- 并集：合并两个或多个集合中的所有元素，并去除重复的元素。
- 差集：两个集合之间的不同元素
- 笛卡儿积：将两个集合的元素按照所有可能的组合进行配对。
- 关系运算：
- 选择：选择操作用于从一个表中选取满足指定条件的行，并返回结果。
- 投影：投影操作用于从一个表中选取特定的列，并返回结果。
- 连接：连接操作用于从两个或多个表中获取数据，并生成新的表。
- 除：除操作用于在一个表中查找所有与另一个表中的一组值匹配的行，并返回结果。
MySQL数据类型
MySQL数据类型、类型长度以及与Java类型的对应关系如下如所示：
char与varchar的区别
-
char：定长，空格填充。
-
varchar：变长，括号里的数字是符串最大长度,占用的空间为字符长度+1，最后一位存使用了多少空间。
MySQL日志类型
- **归档日志（Binary Log）：**也被称为binlog，记录所有对数据库的DDL，DML，TCL操作。归档日志可以用于数据备份和恢复、主从复制等功能。
- **回滚日志（Undo Log）：**记录事务中被修改或删除的数据，使得在回滚事务时可以将数据恢复到之前的状态。
- **重做日志（Redo Log）：**记录已提交事务所做的修改，以便在发生故障时快速恢复数据库。重做日志可以用于确保事务的持久化。
- **中继日志（Relay Log）：**记录 MySQL 主从复制的数据变更信息，以便在从库上恢复主库中的数据。
binlog录入模式
-
**statement：**每一条会修改数据的sql，包括上下文信息。缺点：特定的函数可能会存在问题。
-
**row：**不记录sql语句上下文相关信息，仅保存每一行的改动。缺点产生大量的日志内容。
-
**mixed：**修改使用statment，无法使用statement的时候使用row。
查看binlog录入模式
SHOW VARIABLES LIKE 'binlog_format';
设置binlog录入模式
-- 设置为"STATEMENT"模式
SET GLOBAL binlog_format = 'STATEMENT';
-- 设置为"ROW"模式
SET GLOBAL binlog_format = 'ROW';
-- 设置为"MIXED"模式
SET GLOBAL binlog_format = 'MIXED';
注意：设置binlog录入模式通常需要具有适当的权限。另外，改变binlog的录入模式可能会对数据库产生影响，因此在做出改变之前，请务必做好相关的备份工作，并确保你了解所做改变可能带来的潜在影响。
SQL语法
常用SQL语法
-
建表语句：
CREATE TABLE 表名 ( `字段1` 数据类型(字段长度) COMMENT 字段注释, `字段2` 数据类型(字段长度) COMMENT 字段注释, `字段3` 数据类型(字段长度) COMMENT 字段注释, PRIMARY KEY (主键字段名) ); -- 例如 CREATE TABLE `tb_user` ( `f_id` bigint NOT NULL COMMENT '主键id', `f_user_name` varchar(255) COLLATE utf8mb4_0900_as_ci DEFAULT NULL COMMENT '用户名', `f_age` tinyint DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`f_id`) ) ; -
删除表语句：
DROP TABLE 表名; -
添加字段：
ALTER TABLE 表名 ADD COLUMN 列名 数据类型(字段长度); -
修改字段：
ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型; -
查询语句：
SELECT 字段名 FROM 表名 WHERE 条件 GROUP BY分组字段 HAVING 分组条件 ORDER BY 排序字段 LIMIT 分页参数 -
添加语句：
-- 单条插入 INSERT INTO 表名(字段1, 字段2, ......, 字段n) VALUE(值1, 值2, ......, 值n); -- 批量插入 INSERT INTO 表名(字段1, 字段2, ......, 字段n) VALUES(值1, 值2, ......, 值n), (值1, 值2, ......, 值n), (值1, 值2, ......, 值n); -
更新语句：
UPDATE 表名 SET 字段1 = 值1，字段2 = 值2，......，字段n = 值n WHERE 条件 -
删除语句：
DELETE FROM 表名 WHERE 条件 -
创建索引语句：
CREATE [UNIQUE] [INDEX] 索引名称 ON 表名 (字段1, 字段2, ...);
SQL查询语句执行顺序
执行顺序如下：
FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> UNION -> DISTINCT -> ORDER BY -> LIMIT
- FROM子句：指定要查询的表。
- ON 子句：定义连接条件，将多个表进行连接。
- JOIN子句：根据连接条件进行表连接操作。
- WHERE子句：筛选满足条件的行。
- GROUP BY子句：按照指定的列对结果进行分组。
- HAVING子句：筛选分组后的结果。
- SELECT子句：选择要查询的列。
- UNION字句：将多个查询结果合并。
- DISTINCT：去除重复的行。
- ORDER BY子句：按照指定的列对结果进行排序。
- LIMIT/OFFSET子句：限制结果的数量和偏移量。
存储引擎
MyISAM
MyISAM引擎是MySQL5.5版本之前的数据库所默认的数据表引擎。每一个采用MyISAM引擎的数据表在实际存储中都是由三个文件组成，分别是frm文件，MYD文件和MYI文件，文件后缀为上述三个，文件名与数据表名相同。
- **frm文件：**保存表的结构
- **MYD文件：**保存表的数据
- **MYI文件：**保存表的索引文件
MyISAM特性
- 不支持事务。
- 表级锁定。 即发生数据更新时，会锁定整个表，以防止其他会话对该表中数据的同时修改所导致的混乱。这样做可以使得操作简单，但是会减少并发量。
- 读写互相堵塞。 在MyISM类型的表中，既不可以在向数据表中写入数据的同时另一个会话也向该表中写入数据，也不允许其他的会话读取该表中的数据。只允许多个会话同时读取该数据表中的数据。
- 只会缓存索引，不会缓存数据。 所谓缓存，就是指数据库在访问磁盘数据时，将更多的数据读取进入内存，这样可以使得当访问这些数据时，直接从内存中读取而不是再次访问硬盘。MyISAM可以通过key_buffer_size缓存索引，以减少磁盘I/O，提升访问性能。但是MyISAM数据表并不会缓存数据。
- 读取速度较快，占用资源较少。
- 不支持外键约束。
- 支持全文索引。
InnoDB
从Mysql5.5版本开始，InnoDB是默认的表存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读、同时被设计用来最有效的利用以及使用内存和CPU。采用InnoDB存储引擎的表实际存储在一个ibd文件中。
InnoDB四大特性
插入缓冲
对于非聚集索引的插入或更新操作，不是每次直接插入到索引页，而是先判断插入的非聚集索引页是否在缓冲池（Buffer pool）中，若在，则直接插入。若不在，则先放入到一个 Insert Buffer 对象中，然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。
插入缓冲的使用需要满足以下两个条件：
1、索引是辅助索引。
2、索引不是唯一的。
二次写
脏页刷盘风险（部分页写入问题partial page write）：InnoDB 的 page size一般是16KB，操作系统写文件是以4KB作为单位，那么每写一个 InnoDB 的 page 到磁盘上，操作系统需要写4个块。于是可能出现16K的数据，写入4K 时，发生了系统断电或系统崩溃，只有一部分写是成功的。这时会出现数据不完整的问题，无法通过 redo log 恢复的，因为 redo log 记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。
二次写由两部分组成：
第一部分：内存中的两次写缓冲（doublewrite buffer），大小为2MB
第二部分：磁盘上共享表空间中连续的128个页，即2个extent(块，1 extent = 64 page)，大小也是2M。
二次写流程：
1、当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至内存中的两次写缓冲区。
2、接着从两次写缓冲区分两次写入磁盘共享表空间中，每次写入1MB（顺序写，性能很高）。
3、完成第二步之后，马上调用 fsync 函数，将doublewrite buffer中的页数据写入实际的各个表空间文件（离散写）。
如果将页写入磁盘的过程中发生崩溃，InnoDB再次启动后，发现了一个 page 数据已经损坏，InnoDB 可以从共享表空间中找到该页的一个最近的副本，用于数据恢复。
自适应哈希索引
一些特殊场景下使用 hash 索引。InnoDB会监控对表上索引的查找，如果观察到某些索引被频繁访问，索引成为热数据，就会自适应地通过缓冲池的B+树构造哈希索引，InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。
预读
当 InnoDB 预计某些 page 可能很快就会需要用到时，它会异步地将这些 page 提前读取到缓冲池（buffer pool）中。InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和 随机预读（randomread-ahead）。
线性预读（Linear read-ahead）：如果一个extent中的被顺序读取的page>=innodb_read_ahead_threshold，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64（一个 extend 上限就是64页）的任何值，默认值为56，值越高，访问模式检查越严格。
随机预读（Random read-ahead）: 随机预读方式则是表示当同一个extent 中的一些 page 在 buffer pool 中发现时，Innodb 会将该 extent 中的剩余 page 一并读到 buffer pool中，由于随机预读方式给 Innodb code 带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置 innodb_random_read_ahead为ON。
MyISAM和InnoDB的区别
| 对比项 | InnoDB | MyISAM |
|---|---|---|
| 索引 | B+树，主键是聚簇索引 | B+树，非聚簇索引 |
| 主键索引 | 必须有 | 可以没有 |
| hash索引 | 支持 | 支持 |
| 外键 | 支持 | 不支持 |
| 锁类型 | 表锁，行锁 | 表锁（SELECT加S锁，ELETE，INSERT，UPDATE加X锁） |
| 缓存 | 缓存索引和数据 | 缓存索引和数据 |
| select count(*) from table | 扫描全表 | 用一个变量保存了表的行数 |
| 文件格式 | .ibd | 结构 .frm，数据 .MYD ，索引 .MYI |
| 关注点 | 事务，安全，高并发 | 没事务，高性能 |
事务
事物的特性（ACID）
- **原子性（Atomicity）：**一个事务内的多条对数据库的操作组成一个不可拆分的原子单元，只有在所有操作都成功后，事务才会提交，如果有任何一条操作失败，整个事务对数据库的操作就会回滚，回到初始状态。
- **一致性（Consistency）：**事务操作成功后，数据库所处的状态和它的业务规则是一致的。如A转账100元给B，不管操作是否成功，A和B的账户总额是不变的。
- **隔离性（Isolation）：**一个事务不会被其他事务的操作所影响。
- **持久性（Durability）：**一旦事务提交成功，则对数据库的修改将永久保存在数据库中，即使数据库崩溃，也能在重启之后恢复数据。
并发事务带来的问题
脏读：一个事务读取到了另一个事物未提交的修改数据。当A事务修改了一条数据还未提交，B事务再去读取这条数据，读到了A事务修改过后的值，那么这条数据就是"脏数据"。
幻读：一个事务读取到了另一个事物未提交的新增数据。当A事务读取了一次数据，B事务再向数据表中添加或删除一条数据还未提交事务，此时A事务再去读取一次数据，读取到的条数比第一次多了一条或者少了一条，就像产生了幻觉一样，所以又称幻读。幻读跟脏读类似，脏读是两次读取到的数据内容不同，幻读是两次读取到的数据条数不同。
不可重复读：一个事务读取到了另一个事物已提交的数据。当A事务读取了一次数据，这时B事务修改了这条数据并且提交，A事务再去读取这条数据时，读取到的数据是已经被B事务修改后的数据，造成同一个事务中两次读取到的数据不一致，这就是不可重复读。
一类丢失更新：也叫回滚更新，当A事务修改了某一条数据还未提交，B事务也修改了这条数据还未提交，此时A事务提交之后，B事务再进行回滚，此时数据恢复成两个事务修改之前的数据，造成了原本A事务正常修改的数据丢失了，这就叫做第一类丢失更新。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开始事务 | |
| T2 | 开始事务 | |
| T3 | 查询账户余额为1000元 | |
| T4 | 查询账户余额为1000元 | |
| T5 | 取出100元，账户余额为900元 | |
| T6 | 提交事务 | |
| T7 | 存入100元，账户余额为1100元 | |
| T8 | 回滚事务 | |
| T9 | 余额恢复为1000元（回滚更新） |
二类丢失更新：也叫覆盖更新，当A事务修改了某一条数据还未提交，B事务也修改了这条数据还未提交，此时A事务提交之后，B事务再进行提交，此时数据直接修改为B事务提交的值，造成A事务修改的数据丢失了，这就叫做第二类丢失更新。
| 时间 | 事务A | 事务B |
|---|---|---|
| T1 | 开始事务 | |
| T2 | 开始事务 | |
| T3 | 查询账户余额为1000元 | |
| T4 | 查询账户余额为1000元 | |
| T5 | 取出100元，账户余额为900元 | |
| T6 | 提交事务 | |
| T7 | 存入100元，账户余额为1100元 | |
| T8 | 提交事务 | |
| T9 | 账户余额为1100元（覆盖更新） |
事务的隔离级别
- **读未提交（READ-UNCOMMITTED）：**最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- **读已提交（READ-COMMITTED）：**允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- **可重复读（REPEATABLE-READ）：**InnoDB引擎默认的隔离级别，对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- **串行化（SERIALIZABLE）：**最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
| 隔离级别 | 脏读 | 幻读 | 不可重复读 | 回滚更新 | 覆盖更新 |
|---|---|---|---|---|---|
| 读未提交 | √ | √ | √ | × | √ |
| 读已提交 | × | √ | √ | × | √ |
| 可重复读 | × | √ | × | × | √ |
| 串行化 | × | × | × | × | × |
InnoDB是如何实现原子性的
MySQL的InnoDB是通过undo log回滚日志来实现的，undo log会记录修改之前的数据，当事务执行失败，或者执行了rollback的时候，导致事务需要回滚，就可以通过undo log回滚日志中记录的数据恢复到修改前的样子
例如：
(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当你insert一条数据的时候，就需要这条记录的主键，回滚的时候，delete这条旧数据
InnoDB是如何实现持久性的
MySQL的InnoDB是通过redo log重做日志来实现的，redo log分为两个部分，第一部分是在内存中的重做日志缓冲（redo log buffer），第二部分是用来做持久化的重做日志文件（redo log file），事务在执行的过程中产生的数据改变，首先会将改变之后的新数据存储在redo log buffer中，后面在统一存入redo log file进行刷盘进行持久化操作。
InnoDB是如何实现隔离性的
MySQL的InnoDB是通过读写锁和MVCC机制来实现的，MySQL只有读提交和可重复提交隔离级别才有MVCC，在可重复读的隔离级别下事务在第一次查询数据的时候会生成一个全局的数据快照，后续的对数据库的查询操作都是读取的数据快照的数据，也叫做为快照读，而新增、修改、删除操作会加写锁（排他锁），也叫当前读。并且会在执行DML语句的时候将之前的旧数据存在undo log回滚日志文件中，用于如果后续事务回滚做数据恢复操作。
**注意：**可重复度隔离级别是在第一次查询的时候就生成一次全局的数据快照，而读提交隔离级别是在每一次查询的时候都会生成一次新的数据快照。
事务的执行流程
读操作（查询）：
在事务中普通查询数据读取的是当前的版本快照。可以通过lock in share mode加共享锁，for update加排他锁。
写操作（增删改）：
- 先对数据进行加X锁（排他锁）。
- 将旧的数据记录到undo log中，并构建指向该 undo log 的回滚指针。
- 进行写操作，更新DB_TRX_ID字段为当前事务的ID，DB_ROLL_PTR的值为上一个版本数据的地址，也就是步骤二生成的回滚指针。
- 将新数据写入到redo log。
- 释放锁。
MVCC的实现原理
MVCC全称叫做Multi-Version Concurrent Control（多版本并发控制），MVCC的实现主要依靠三个部分
一、隐式字段
InnoDB引擎会给每条数据添加两个隐藏的字段（如果没有聚簇索引还会多一个6字节的隐式ID字段）
- DB_TRX_ID：记录创建这条数据上次修改它的事务 ID
- DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本
- DB_ROW_ID：隐式ID
二、undo log（回滚日志）
undo log不止实现了原子性，它还是实现MVCC的版本控制器，undo log保存的是一个数据的历史版本链，它使用DB_TRX_ID来记录本次修改数据的事务ID，用DB_ROLL_PTR字段来记录上一个版本的指针地址。可以用指针地址来找到历史数据。当执行查询操作的时候会生成一个一致性视图read-view，read-view是由查询时所有未提交的事务ID组成的数组，和当前最大的事务ID组成的，查询的数据需要跟read-view作比较从而得到快照结果。
三、read-view
当执行查询操作时会产生一个一致性视图read-view，它是由查询的那一时间的所有未提交的事务ID组成的数组，和当前的最大事务ID组成的，数组内的最小事务ID被称之为min_id，数组外的那个最大的事务ID被称为max_id，查询的数据要根据read-view做对比从而得到快照结果。
对比规则如下：
1.如果当前查询数据的事务ID，也就是DB_TRX_ID < min_id，则说明此版本是已经提交过的事务，则数据可见
2.如果DB_TRX_ID > max_id，则说明此版本是后来启动的事务生成的，则数据不可见
3.如果min_id <= DB_TRX_ID <= max_id，则会出现两种情况：
a.如果查询数据的DB_TRX_ID在数组中，表示此版本是未提交的事务生成的，则数据不可见
b.如果查询数据的DB_TRX_ID不在数组中，表明是已经提交的事务生成的该版本，则数据可见
这里需要注意的是对于删除的数据时，undo log会复制一份版本链上的最新的记录，然后将事务ID改为删除时事务的事务ID，同时会在该记录的头信息中存一个delete flag标记，将这个标记写为true，用来表示该记录被删除。
mvcc实现原理详细链接：https://www.cnblogs.com/fkaka/p/14620957.html
索引
索引是一种数据结构，是数据库管理系统中一个排序的数据结构，以协助快速查询数据库表中数据。索引的实现通常使用B+树或hash表。更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。
索引分类
按存储结构划分：
- **聚簇索引：**将数据与索引放到了一块，索引结构的叶子节点存储了行数据，找到索引也就找到了数据。在InnoDB中通常是主键，如果没有主键，则优先选择非空的唯一索引，如果唯一索引也没有，则会创建一个隐藏的DB_ROW_ID作为聚簇索引。
- **非聚簇索引：**也被称为辅助索引，将数据与索引分开存储，索引结构的叶子节点存储的是行数据的地址。
按包含字段划分：
- **单值索引：**指创建索引时只选择一个字段。
- **联合索引：**指创建索引时选择多个字段。联合索引需要遵循最左匹配原则。
按索引类型划分：
-
主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
-
**唯一索引：**唯一索引不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
-
**普通索引：**基本的索引类型，没有唯一性的限制，允许为NULL值，一个表允许多个列创建普通索引。
-
**全文索引：**用来对表中文本域字段进行索引，主要是用来解决WHERE name LIKE '%word%'这类针对文本的模糊查询效率较低的问题。
-
**空间索引：**用于处理地理空间数据的索引结构。
聚簇索引与非聚簇索引
聚簇索引的优点
- 数据访问更快。聚族索引将索引和数据保存在同一个B+树中，因此从聚族索引中获取数据通常比非聚族索引中查找更快。
- 当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
- 使用覆盖索引扫描的查询可以直接使用节点中的主键值。
聚簇索引的缺点
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键。
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
- 通过辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
索引结构
- **B+树：**B树索引是MySQL数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引）。一颗传统的M阶B+树需要满足以下几个要求：
- 从根节点到叶节点的所有路径都具有相同的长度
- 所有数据信息都存储在叶子节点，非叶子节点仅作为叶节点的索引存在
- 叶子节点通过指针连在一起
- 根节点至少拥有两个子树
- 每个树节点最多拥有M个子树
- 每个树节点(除了根节点)拥有至少M/2个子树
- **Hash：**类似于数据结构中简单实现的HASH表（散列表）一样，当我们在MySQL中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置，如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
为什么使用 B+ 树，与其他数据结构相比有什么优点
-
**红黑树特点：**内存操作时快。但层数多，意味着IO次数多。
-
**Hash特点：**单值操作快，但不支持范围查询，索引值排序，联合索引最左匹配等。
-
**B树特点：**所有节点都存储键和数据，并且由于没有叶子节点的链表，范围查询只能中序遍历，需要跨层，所以有额外的IO操作。
-
**B+树特点：**非叶子节点只存储键不存具体数据，由于一个节点为一个数据页，大小为16kb，那么一个节点就能够存的更多的关键字，每一层都是一次IO操作，层数越少，IO次数就越少。数据都在叶子节点，而叶子节点的深度都相同，则查询效率稳定。并且叶子节点是一个双向链表，对范围查询和排序有很好的支持。
回表
在InnoDB引擎中，辅助索引的叶子节点存储的是主键值和key，在通过辅助索引查询数据的时候，如果查询的字段或者作为查询条件的字段有没有建立索引的列，那么会根据主键值去主键索引再进行检索一遍，才能拿到数据，这个操作就叫做回表。
如果我们有一个tb_user表，并且只对name字段创建了普通索引，那么下面的SQL就会回表：
SELECT id,name,age,sex FROM tb_user WHERE name = "张三"
可以通过索引覆盖的操作来避免回表。
索引覆盖
通过辅助索引进行查询时，如果要查询的字段刚好都是建了索引的，那么只通过辅助索引就能够获取到全部的数据，那么就不会再进行回表操作，这就叫做索引覆盖，所以应该避免有select *的操作，select *会导致回表，影响查询效率。
如果我们有一个tb_user表，并且只对name字段创建了普通索引，那么下面的SQL就不会回表：
SELECT id,name FROM tb_user WHERE name = "张三"
因为在辅助索引上能够找到id和name字段，并且查询条件也没有其他字段，那么就达成了索引覆盖的条件，就不会再进行回表操作了。
联合索引结构和原理
假设，我们对(a,b)字段建立索引，那么如下图所示
如上图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。
因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态！什么意思呢？
从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。
从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。因此，你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。
综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。
最左匹配原则
- 最左前缀原则，就是最左边的优先。指的是联合索引中，优先走最左边列的索引。对于多个字段的联合索引，如 index(a,b,c) 联合索引，则相当于创建了 a 单列索引，(a,b)联合索引，和(a,b,c)联合索引（但并不是建立了多个索引树）。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
- 如果建立的索引顺序是 (a,b) 那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。
索引设计的原则
索引虽好，但也不是无限制的使用，最好符合以下几个原则
- 为常作为查询条件的字段建立索引，where子句中的列，或者连接子句中指定的列
- 为经常需要排序、分组操作的字段建立索引
- 更新频繁字段不适合创建索引，因为增删改操作可能会频繁的修改索引树。
- 不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
- 对于定义为text、image和bit的数据类型的列不要建立索引
- 联合索引中，应该遵守最左匹配原则
- 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
- 用0、一个特殊的值或者一个空串代替空值
- 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长
SQL优化
explain命令
我们平时可以通过explain命令查看sql的执行计划，如表的读取顺序，sql是否走了索引，走了哪个索引，大概扫描行数等信息。下面将对explain命令的相关字段和属性进行详细解释。
字段及其属性详解：
- id：ID字段的值及其排列顺序，表明 MySQL 执行时从各表取数据的顺序。一般情况下遵循下面两个原则
- ID 相同的组，其执行优先级按照其顺序由上到下。
- ID 越大的组，其执行优先级越高。
- select_type：表示该 SQL 是什么查询类型，一共有以下几种取值
- SIMPLE：简单查询，不包含子查询或 union 查询
- PRIMARY：如果查询中包含了任何复杂的子查询，那么最外层查询会被标记为主查询
- SUBQUERY：在 select 或 where 中包含子查询
- DERIVED：from 中包含子查询
- UNION
- UNION RESULT
- table：查询的表名
- partitions：查询涉及的分区数
- **type：**表示访问情况，通常用来衡量 SQL 的查询效率。其值的查询效率为
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL- system：表只有一行记录（等于系统表），这是 const 类型的特列。
- **const：**表示该表最多有一个匹配记录。通常情况下是 SQL 中出现了主键索引或唯一索引。
- **eq_ref：**表示主键索引或唯一索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。与 const 类型非常相似，唯一的区别是 eq_ef 通常出现在联表的情况下，而 const 通常出现在单表情况下。
- **ref：**表示使用了非唯一索引扫描，会返回匹配某个单独值的所有行。
- fulltext：使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
- ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
- index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range。
- unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。
- index_subquery：用于where中in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
- **range：**索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
- **index：**索引全表扫描，把索引从头到尾扫一遍。比如SELECT * FROM t ORDER BY 索引。效率比ALL高：按照索引扫描全表的数据是有序的，或索引覆盖。
- ALL：全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
- possible_keys：查询可能使用到的索引都会在这里列出来。（若索引覆盖，则该字段显示为NULL）。
- **key：**查询真正使用到的索引。
- key_len：查询用到的索引长度（字节数）。
- ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。
- **rows：**这里是执行计划中估算的扫描行数。并不是准确的行数。
- filtered：按表条件过滤的行百分比。
- **Extra：**额外信息
- Using filesort：不是按照查询所用索引来进行排序的，建议优化掉。
- Using temporary：使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
- Using index：表示相应的 select 操作中使用了覆盖索引，往往说明性能不错。
- Using where：表明使用了 where 过滤。
- distinct：优化 distinct 操作，在找到第一匹配的行后即停止找同样值的动作。
如何优化SQL
- 对经常查询的列或者要作为where条件和order by排序的列建立索引。
- where后使用is null， like 以通配符开头(‘%abc…’)会使索引失效。
- where后使用or，如果有一个条件没有索引，那么整个sql就不会走索引。
- where后用in，in里面多了会使索引失效，尽量使用between and 代替（该关键字是闭区间）。
- where后用!=，<>，not等负向条件会使索引失效。
- 避免使用select *，会导致回表。
- 联合索引要遵循最左前缀法则。
- 索引列数据重复度高会使索引失效。
- 如果排序字段没有用到索引，就尽量少排序。
- 以in内表作为驱动表适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。
- left join 时，将小表写在左边，作为驱动表。
- 深度分页时，后边的分页用>来代替limit的第一个参数。
如何定位慢SQL
1、通过德鲁伊连接池的内置监控定位慢sql
2、通过开启mysql的慢查询日志查看慢sql
3、通过show processlist查看当前数据库sql的执行情况
大表优化策略
- 建立合适索引，尽量避免全表扫描。
- 使用redis等手段建立热点数据缓存。
- 读写分离。
- 垂直分表、分库。优点：列少容易维护。缺点：数据冗余。
- 水平分表、分库。优点：支持存储大量数据。缺点：逻辑复杂。
锁
MySQL锁的分类
按模式分类
- **乐观锁：**假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。实现方式有版本号，时间戳，MVCC算法等
- **悲观锁：**假设并发操作时总会发生冲突，因此在操作期间通过持有锁来避免冲突。实现方式有共享锁和排他锁等。
按粒度分类
- **全局锁：**对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。一般用于备份、恢复等操作。
- **表级锁：**对整个表加锁，其他连接无法修改或读取该表的数据，但可以对其他表进行操作。
- **页级锁：**对数据页（通常是连续的几个行）加锁，控制并发事务对该页的访问。适用于数据较大且并发量较高的场景。
- **行级锁：**对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。并发性高，但锁管理较复杂。
按属性分类
- 共享锁：也被称为读锁或者S锁，读读不冲突，读写冲突。查询时可以通过lock in share mode加共享锁。例如: select * from table where id = 1 lock in share mode
- 排他锁：也被称为写锁或者X锁，写读、写写都会冲突，在mysql中insert、update、delete操作都会加排他锁。查询时可以通过for update加排他锁。例如：select * from table where id = 1 for update
按算法分类
-
**Record lock：**记录锁，单条索引记录上加锁，锁住的永远是索引，而非记录本身。
-
**Gap lock：**间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。
-
**Next-key lock：**Record lock 和 Gap lock的结合，即除了锁住记录本身，也锁住索引之间的间隙。
InnoDB行锁的实现
-
对于主键索引：直接锁住锁住主键索引即可。
-
对于普通索引：先锁住普通索引，接着锁住主键索引。
MySQL锁优化
-
MyISAM（表锁，性能高）
- 缩短锁定时间（大查询拆分）。
- 分离能并行的操作，充分利用MyISAM可以并发插入的特性。
- 根据读写情况合理设置优先级。默认写优先级>读优先级。
-
InnoDB（索引，事务，死锁）
- 尽量走索引，避免升级为表锁。
- 减少基于范围的数据检索过滤条件，避免间隙锁的负面影响。
- 控制事务大小，缩短锁定时间。
- 类似业务模块中，尽可能按照相同的访问顺序来访问，防止死锁。
- 容易产生死锁的地方，升级锁的粒度。
架构
MySQL组成
MySQL的逻辑架构图如下图所示：
首先mysql内部可分为Server层和存储引擎层两部分
**Server层：**查询缓存、连接器、分析器、优化器、执行器，涵盖了MySQL大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），相关功能：存储过程、触发器、视图都在这一层实现。
- 连接器：负责跟客户端建立连接、获取校验权限、维持和管理链接。
- 查询缓存：连接建立后，就可以执行select语句，然后会进行查询缓存，MySQL收到查询请求后，会到查询缓存中查看，其中都是保存着以key-value形式的键值对，key为查询语句，value为查询结果，如果命中缓存则直接返回value。不在查询缓存中，会执行后续过程，执行完成后会将查询的结果保存到缓存中，需要注意的是查询缓存的失效非常频繁，只要对一张表有更新，那么这张表上的查询缓存都会被清空。如果不想走查询缓存可以 将参数query_cache_type 设置成 DEMAND，则所有的SQL都不会进行查询缓存。在MySQL8.0后已经取消了查询缓存的功能。
- 分析器：对SQL语句进行分析，主要是词法分析和语法分析，它会把相关的关键字、表名、字段名分析出来，然后根据词法分析的结果进行语法规则的判断，没有通过分析就会有错误提示
You have an error in your SQL syntax。 - 优化器：通过分析器，MySQL知道了执行目的，分析器主要是决定sql中执行的顺序，而优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，不同的执行顺序，所执行的效率是不一样的。MySQL数据库的查询优化器是采用了基于代价的方式，而查询代价的估算是基于CPU代价和IO代价。如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。
- 执行器：MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
**存储引擎层：**负责数据的存储和提取，其架构模式是插件式的，存储引擎是可以多选的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。
一条SQL的执行流程
**第一步：**客户端与数据库server层的连接器进行连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
**第二步：**连接建立完成后，会判断查询缓存是否开启，如果已经开启，会判断sql是select还是update/insert/delete，对于select，尝试去查询缓存，如果命中缓存直接返回数据给客户端， 如果缓存没有命中，或者没有开启缓存， 会进入到下一步分析器。
**第三步：**分析器进行词法分析和语法分析，分析器先会做“词法分析”，分析SQL中的字符串分别是什么，校验数据库表和字段是否存在，然后进行语法分析，判断SQL是否满足MySQL语法。
**第四步：**优化器对sql执行计划分析，得到最终执行计划，得到优化后的执行计划交给执行器。
**第五步：**执行器开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，执行器调用存储引擎api执行sql，得到响应结果， 将结果返回给客户端，如果缓存是开启状态， 会更新缓存。
备份与恢复
备份分类
按照备份方法分类
- **热备份：**又叫在线备份，在运行中直接备份，并对正在运行的数据库没有影响。依赖数据库的日志文件（第三方工具：mysqlhotcopy）
- **冷备份：**又叫离线备份，先停止数据库服务，然后复制.frm文件，共享表空间文件的.idb文件，redo log文件。另外再定期备份my.cnf文件。优点：简单快捷。缺点：跨版本，跨平台可能是问题。
- **温备份：**在运行中备份，但对当前数据库有影响，数据库锁定表（不可写入但可读）的状态下进行备份操作。（mysqldump备份）
按照备份文件的内容分类
- **逻辑备份：**备份出来的文件内容是可读的，内容一般是SQL。适用于数据库迁移，升级。但所需时
间长。 - **裸文件备份：**备份数据库的物理文件。
按备份数据库的内容
- **完全备份：**对数据库进行完整的备份。
- **增量备份：**在上次完全备份的基础上，对更改的部分进行备份。
- **日志备份：**备份bin log文件。
逻辑备份与恢复
mysqldump方式
备份
# 备份一个数据库，info01。
mysqldump -u[用户名] -p[密码] --databases info01 > /opt/info01.sql
# 备份两个数据库，info01，info02
mysqldump -u[用户名] -p[密码] --detabases info01 info02 > /opt/info01-info02.sql
# 备份所有的数据库
mysqldump -u[用户名] -p[密码] --all-databases >/opt/all-database.sql
恢复
恢复有两种方法，
-
如果是使用mysqldump备份的文件，那么可以使用mysql导入的方法恢复
-
还有一种方法就是使用source命令在数据库中加载备份的目录即可
# 使用-e的方法进入数据库执行删除数据库的操作 mysql -u[用户名] -p[密码] -e 'drop database info01;'; # 使用-e的方法进行显示数据库的数量 mysql -u[用户名] -p[密码] -e 'show databases;'; # 使用mysql命令恢复数据库info01 mysql -u[用户名] -p[密码] </opt/info01.sql; # 查看数据库中是否恢复成功 mysql -u[用户名] -p[密码] -p 'show databases;';
SELECT…INTO OUTFILE方式
备份
通过SELECT * INTO OUTFILE ‘文件名’ FROM 表。
备份出来的是表格化的数据，并不是SQL。
恢复
通过LOAD DATA INFILE ‘文件名’ INTO table 表名来进行恢复
binlog备份与恢复
默认是关闭的，通过在配置文件中配置：log-bin=mysql-bin来开启。
通过mysqlbinlog binlog.0000001来恢复单个日志文件。
只能通过mysqlbinlog binlog[1-10]*来恢复多个日志文件，而不是一个一个的恢复。
数据误删的解决方案
误删行
1、通过Flashback闪回技术，binlog的内容，拿回原库重放。需要确保 binlog_format=row 和binlog_row_image=FULL。
2、对于 insert，对应的binlog event类型是Write_rows event，把它改成Delete_rows event 即可。
3、对于delete语句，将Delete_rows event改为Write_rows event。
4、对于Update_rows的话，binlog 里面记录了数据行修改前和修改后的值，对调这两行的位置即可。
误删库或表
用全量备份+加增量日志的方式。
1、取最近一次全量备份
2、用备份恢复出一个临时库；
3、从日志备份里面，取出全量备份之后的日志；
4、把这些日志，除了误删除数据的语句外，全部应用到临时库。
核心业务的快速恢复
1、如果全量备份的时间过长，核心业务可以采用延迟备份。
2、通过CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N秒的延迟。
3、通过延迟备库为基础进行快速恢复。
防止误删
1、账号读写权限分离。
2、把sql_safe_updates参数设置为 on。delete或者update语句中没有where，或者 where条件里面没有包含索引字段的话，这条语句的执行就会报错。
3、删除前先改名，运行一段时间无问题后再删除。
4、如果是用的navicate，可以对不同的环境加不同的颜色来区分。
5、代码上线前，必须经过SQL审计。
集群
集群的优点以及风险
优点：
- **高可用性：**故障检测及迁移，多节点备份
- **可伸缩性：**新增数据库节点便利，方便扩容
- **负载均衡：**切换某服务访问某节点，分摊单个节点的数据库压力
风险：
- **网络分裂：**群集可能由于网络故障而拆分为多个部分，每部分内的节点相互连接，但各部分之间的节点失去连接。
- **脑裂：**导致数据库节点彼此独立运行的集群故障称为「脑裂」。这种情况可能导致数据不一致，并且无法修复，例如当两个数据库节点独立更新同一表上的同一行时。
MySQL主从模式
主从复制原理
MySQL主从复制原理如下图所示：
首先介绍一下相关日志和线程：
- **Binary log：**归档日志，也被称为binlog，记录所有对数据库的改动操作。
- **Relay log：**中继日志，记录MySQL 主从复制的数据变更信息，以便在从库上恢复主库中的数据。
- **log dump thread：**主库可以主动通过log dump thread将bin log变动信息发送给从库。
- **I/O thread：**I/O thread可以请求获取主库的bin log信息，将返回的bin log信息写入到Relay log中。
- **SQL thread：**将relay log中的内容解析，并在本数据库中执行。
异步复制（默认方式）
1、slave上的I/O Thread连接master，并请求从指定bin log file的指定位置（bin log position，或
者从最开始的日志）之后的日志内容，如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。
2、master接收到来自slave的 I/O Thread请求后，读取相应信息，返回给slave。返回信息还包括本
次返回的信息的 bin log file 以及 bin log position。
3、slave的 I/O Thread接收到内容后，将接收到的日志内容更新到relay log中，并将读取到的bin
log file和bin log position保存到master-info文件中，以便在下一次读取的时候能够清楚的告诉
master “我需要从某个bin log的哪个位置开始往后的日志内容”。
4、从节点的 SQL Thread检测到relay log中新增加了内容后，会解析relay log的内容，并在本数据库
中执行。
**特点：**主服务器不需要等待从服务器的确认，因此主从之间的数据同步是异步的，主服务器的事务提交不依赖于从服务器的确认。这种方式下，从服务器可能存在一定程度的延迟，同时也存在数据丢失的风险。
半同步复制
介绍半同步复制之前先快速过一下 MySQL 事务写入碰到主从复制时的完整过程，主库事务写入分为 4个步骤：
- InnoDB Redo File Write (Prepare Write)
- Binlog File Flush & Sync to Binlog File
- InnoDB Redo File Commit（Commit Write）
- Send Binlog to Slave
半同步复制整体逻辑与异步复制类似，异步复制不需要关注Slave是否接收到Binlog Event，而半同步复制会在主数据库的修改语句写入bin log之后并且事务提交之前的某一时刻（也就是上诉的第三步之后）等待至少一个Slave节点的ACK消息，接收到ACK消息后才进行事务提交然后返回结果给客户端。依次来保证数据复制的可靠性。
如果是在第二步之后等待 Slave 返回 ACK 时，即为增强半同步。
**特点：**相比异步复制，半同步复制可以减少数据丢失的风险，因为主服务器会等待至少一个从服务器确认日志写入成功后才提交事务。这种方式下，数据同步是半同步的，可以提高数据的可靠性和一致性。
同步复制
同步复制是指主服务器在事务提交时会等待所有从服务器将日志写入成功后才认为事务提交成功，然后主服务器才能继续处理其他事务。
**特点：**同步复制是最严格的复制方式，可以保证主从之间的数据完全一致。但是由于要等待所有从服务器的确认，可能会影响主服务器的性能，尤其是在网络延迟较大的情况下。
主从延迟
在从库执行完成的时间和主库执行完成的时间之间的差值，叫做主从延迟，计算方法如下：
1、事务的bin log里一个时间字段，用于记录主库上写入的时间。
2、从库取出当前正在执行的事务对应的bin log的时间字段的值，计算它与当前系统时间的差值，得到seconds_behind_master（show slave status，查看seconds_behind_master的值）。
3、如果从库发现主库的系统时间与自己不一致，备库在计算seconds_behind_master的时候会自动扣掉这个差值。
导致主备延迟的原因：
- 备库性能差。
- 备库查询压力大。
- 大事务。
并行复制
MySQL从5.6版本开始追加了并行复制功能，目的就是为了改善复制延迟问题，并行复制称为enhanced multi-threaded slave（简称MTS）。
在从库中有两个线程IO Thread和SQL Thread，都是单线程模式工作，因此有了延迟问题，我们可以采用多线程机制来加强，减少从库复制延迟。（IO Thread多线程意义不大，主要指的是SQL Thread多线程）。
MySQL5.7并行复制实现原理
MySQL 5.7是通过对事务进行分组，当事务提交时，它们将在单个操作中写入到二进制日志中。如果多个事务能同时提交成功，那么它们意味着没有冲突，因此可以在Slave上并行执行，所以通过在主库上的二进制日志中添加组提交信息。
MySQL 5.7的并行复制基于一个前提，即所有已经处于prepare阶段的事务，都是可以并行提交的。这些当然也可以在从库中并行提交，因为处理这个阶段的事务都是没有冲突的。在一个组里提交的事务，一定不会修改同一行。这是一种新的并行复制思路，完全摆脱了原来一直致力于为了防止冲突而做的分发算法，等待策略等复杂的而又效率底下的工作。
InnoDB事务提交采用的是两阶段提交模式。一个阶段是prepare，另一个是commit。
MySQL8.0并行复制原理
MySQL8.0 是基于write-set的并行复制。MySQL会有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过hash后都会与那个变量的集合进行对比，来判断该行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。这样的粒度，就到了 row级别了，此时并行的粒度更加精细，并行的速度会更快。
主从切换
主从切换是指将从库变为主库，主库变为从库，有可靠性优先和可用性优先两种策略，可靠性优先为常用。
可靠性优先
主备切换过程一般由专门的HA高可用组件完成，但是切换过程中会存在短时间不可用，为保证数据一致性
切换流程：
1、判断从库的seconds_behind_master是否小于某个值（比如 5 秒），若是就继续下一步，否则持续重试这一步。
2、把主库改成只读状态，即把readonly设置为 true。
3、再次判断从库的seconds_behind_maste的值，直到这个值变成 0 为止。
4、把从库改成可读写状态，也就是把 readonly 设置为 false。
5、把业务请求切到之前的从库，也就是现在的主库。
可用性优先
不等主从同步完成，直接把业务请求切换至从库 ，并且让从库可读写。
读写分离
大多数互联网业务中，往往读多写少，这时候数据库的读会首先成为数据库的瓶颈。如果我们已经优化了SQL，但是读依旧还是瓶颈时，这时就可以选择“读写分离”架构了。
读写分离首先需要将数据库分为主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过主从复制机制进行数据的同步。
读写分配机制
- **基于编程和配置实现：**根据操作类型进行路由分配，增删改时操作主库，查询时操作从库。
- **基于服务器端代理实现：**使用中间件代理，中间件代理一般介于应用服务器和数据库服务器之间，应用服务器并不直接进入到master数据库或者slave数据库，而是进入MySQL proxy代理服务器。代理服务器接收到应用服务器的请求后，先进行判断然后转发到后端master和slave数据库。目前有很多性能不错的数据库中间件，常用的有MySQL Proxy、MyCat以及Shardingsphere等等。
主从同步延迟问题及解决方案
由于主从可能存在延迟，客户端执行完更新事务后马上发起向从库的查询，可能读到事务更新前的状态。
解决方案如下：
- **强制走主库：**对于重要业务，必须要拿到最新结果的请求，强制将其发到主库上。对于可以读到旧数据的请求，才将其发到从库上。
- **延迟读取：**在主库把更新操作执行后，等待一定时间再去从库执行查询。降低速度并且没有根本解决问题。
- **客户端自身回显：**客户端自身用更新数据去回显需要查询的数据。
- **二次查询：**先去从库读取数据，找不到时就去主库进行数据读取。该操作容易将读压力返还给主库。
双主架构
适用场景
在单主的集群模式下如果主数据库宕机了，那么整个集群将会不可用，于是可以使用双主或者多主，来提高主库的可用性。双主模式是指两台服务器互为主从，任何一台服务器数据变更，都会通过复制应用到另外一方的数据库中。推荐使用双主单写，因为双主双写存在ID冲突和双主更新覆盖丢失问题。
**ID冲突：**在A主库写入，当A数据未同步到B主库时，对B主库写入，如果采用自动递增容易发生ID主键的冲突。可以采用MySQL自身的自动增长步长来解决，例如A的主键为1,3,5,7…，B的主键为2,4,6,8… ，但是对数据库运维、扩展都不友好。
**更新覆盖：**同一条记录在两个主库中进行更新，会发生前面覆盖后面的更新丢失。
MMM架构
MMM（Master-Master Replication Manager for MySQL）是一套用来管理和监控双主复制，支持双主故障切换 的第三方软件。虽然是双主架构，但是业务上同一时间只允许一个节点进行写入操作。MMM 包含writer和reader两类角色，分别对应写节点和读节点。
MMM故障处理机制：
- 当 writer节点出现故障，程序会自动移除该节点上的VIP(虚拟 IP)。
- 写操作切换到 Master2，并将Master2设置为writer。
- 将所有Slave节点会指向Master2。
**MMM监控机制：**MMM 包含monitor和agent两类程序
- **monitor：**监控集群内数据库的状态，在出现异常时发布切换命令，一般和数据库分开部署。
- **agent：**运行在每个 MySQL 服务器上的代理进程，monitor 命令的执行者，完成监控的探针工作和具体服务设置，例如设置 VIP（虚拟IP）、指向新同步节点。
MHA架构
MHA（Master High Availability）是一套比较成熟的 MySQL 高可用方案，也是一款优秀的故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA还支持在线快速将Master切换到其他主机，通常只需0.5－2秒。
目前MHA主要支持一主多从的架构，要搭建MHA，要求一个复制集群中必须最少有三台数据库服务器(即一台 master，一台充当备用 master，另外一台充当从库)。
MHA由两部分组成：
- **MHA Manager（管理节点）😗*MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。负责检测master是否宕机、控制故障转移、检查MySQL复制状况等。
- **MHA Node（数据节点）😗*MHA Node运行在每台MySQL服务器上，不管是Master角色，还是Slave角色，都称为Node，是被监控管理的对象节点，负责保存和复制master的二进制日志、识别差异的中继日志事件并将其差异的事件应用于其他的slave、清除中继日志。
MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master，整个故障转移过程对应用程序完全透明。
MHA故障处理机制：
- 把宕机master的binlog保存下来
- 根据binlog位置点找到最新的slave
- 用最新slave的relay log修复其它slave
- 将保存下来的binlog在最新的slave上恢复
- 将最新的slave提升为master
- 将其它slave重新指向新提升的master，并开启主从复制
MHA优点：
- 自动故障转移快
- 主库崩溃不存在数据一致性问题
- 性能优秀，支持半同步复制和异步复制
- 一个Manager监控节点可以监控多个集群
分库分表
分库
分库就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。
- **垂直分库：**垂直分库就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。
- **水平分库：**水平分库是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。
分表
分表就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。
- **垂直分表：**垂直分表是对数据表列的拆分，把一张列比较多的表拆分为多张表。
- **水分平表：**水平分表是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。
为什么要分库
1、一般单示例MySQL数据库，每秒能抗2000左右的请求，超过了这个请求，可能会让实例挂掉。让每个实例的并发稳定在1000左右。
2、磁盘容量问题。
3、冷热数据明确。
为什么要分表
1、单表数据量大，SQL性能下降。
分片策略
基于范围分片
根据特定字段的范围（ID区间、时间区间）进行拆分。比如将ID为1~299999的记录分到第一个库， 300000~599999的分到第二个库。
**优点：**新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。
**缺点：**数据热点分布不均，数据冷热不均匀，导致节点负荷不均。
哈希取模分片
求指定 key（比如 ID）的哈希，然后可直接对设备数量取模，然后根据哈希值确定数据应被放置在哪个表中。。
**优点：**实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况。
**缺点：**扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和迁移。
一致性哈希分片
一致性Hash是指构造一个长度为2的32次方的整数环（这个环被称之为一致性Hsah环），根据数据库节点的ip或者机器名称的Hash值将服务器节点放置在这个Hash环上，然后根据数据的Key值计算得到其Hash值（其分布也为0-2的32次方），接着在Hash环上顺时针查找距离这个key的Hash值最近的服务器接到哪，完成Key到服务器的映射查找。
具体如下图所示：
一致性哈希特点：
- **单调性(Monotonicity)：**单调性是指如果已经有一些请求通过哈希分派到了相应的服务器进行处理，又有新的服务器加入到系统中时候，应保证原有的请求可以被映射到原有的或者新的服务器中去，而不会被映射到原来的其它服务器上去。
- **分散性(Spread)：**好的哈希算法应尽量避免尽量降低分散性。 一致性hash具有很低的分散性。
- **平衡性(Balance)：**平衡性也就是说负载均衡，是指客户端hash后的请求应该能够分散到不同的服务器上去。一致性hash可以做到每个服务器都进行处理请求，但是不能保证每个服务器处理的请求的数量大致相同。
主键策略
自增主键，设置步长
通过对不同的分片的表设置不同的自增步长实现。
-- 将自增长步长设置为3
SET @@auto_increment_increment = 3;
**优点：**简单
**缺点：**难扩展
UUID
通过UUID生成全局唯一的ID。
**优点：**简单
**缺点：**ID不递增、不易储存、信息不安全
Redis生成
使用redis的incr和increby这样的自增原子命令。
**优点：**简单、全局唯一且递增
**缺点：**高度依赖redis
snowflake
Twitter提供的snowflake，也叫雪花算法，使用一个long类型字段来存储：标识符+时间戳+机器码+序列。
-
**标识符（1bit）：**二进制里第一个bit如果是1就是负数，但是我们生成的 id 都是正数，所以第一个
bit统一都是0。 -
**时间戳（41bit）：**单位是毫秒。41bit可以表示的数字为2^41 - 1，也就是可以标识2^41 - 1个毫
秒值，就是69年的时间。 -
**机器码（10bit）：**记录工作机器id，代表的是这个服务最多可以部署在 2^10台机器上。10bit
里 前5个bit代表机房id，5个bit代表机器id。意思就是最多代表2^5个机房（32个机房），每个机
房里可以部署2^5 个机器（32台机器）。 -
**序列（12bit）：**记录同一个毫秒内产生的不同id，12 bit可以标识4096个不同的id。
稳定灵活，但强依赖于时钟
**优点：**全局递增、简单高效、并发度高
**缺点：**受限于机器时钟精度、如果单机房部署的机器超过32台有极低概率重复
Leaf
Leaf-Segment方案
Leaf-segment号段模式是对直接用数据库自增ID充当分布式ID的一种优化，减少对数据库的频率操作。相当于从数据库批量的获取自增ID，每次从数据库取出一个号段范围，例如 (1,1000] 代表1000个ID，业务服务将号段在本地生成1~1000的自增ID并加载到内存.。号段耗尽之后再去数据库获取新的号段，可以大大的减轻数据库的压力。
由于以来数据库，表结构设计如下：
- **biz_tag：**针对不同业务需求，用biz_tag字段来隔离，如果以后需要扩容时，只需对biz_tag分库分表即可。
- **max_id：**当前业务号段的最大值，用于计算下一个号段。
- **step：**步长，也就是每次获取ID的数量，原来获取ID每次都需要写数据库，现在只需要把step设置得
足够大，比如1000。那么只有当1000个号被消耗完了之后才会去重新读写一次数据库。读写数据库的
频率从1减小到了1/step。 - **description：**对于业务的描述，没啥好说的。
- **update_time：**更新时间。
双buffer优化
内部有两个号段缓存区segment。当前号段已使用10%时，如果下一个号段未更新，则另启一个更新线程去更新下一个号段。当前号段全部下发完后，如果下个号段准备好了则切换到下个号段为当前segment接着下发，循环往复。通常推荐号段长度设置为服务高峰期发号QPS的600倍（10分钟），这样即使DB宕机，Leaf仍能持续发号10-20分钟不受影响。
优点：
- Leaf服务可以很方便的线性扩展，性能完全能够支撑大多数业务场景。
- 容灾性高：Leaf服务内部有号段缓存，即使DB宕机，短时间内Leaf仍能正常对外提供服务。
缺点：
- ID号码不够随机，能够泄露发号数量的信息，不太安全。
- DB宕机会造成整个系统不可用（用到数据库的都有可能）。
Leaf-snowflake方案
沿用snowflake方案的bit位设计，即是“1+41+10+12”的方式组装ID号。与原始的雪花算法不同的是其中10位的workerId从zookepper中取（zookeeper持久顺序节点生成的int类型的ID号）。
获取流程如下：
- 启动Leaf-snowflake服务，连接Zookeeper，在leaf_forever父节点下检查自己是否已经注册
过（是否有该顺序子节点）。 - 如果有注册过直接取回自己的workerID（zk顺序节点生成的int类型ID号），启动服务。
- 如果没有注册过，就在该父节点下面创建一个持久顺序节点，创建成功后取回顺序号当做自己
的workerID号，启动服务。
优点：
- ID号码是趋势递增的8byte的64位数字，满足上述数据库存储的主键要求。
缺点：
- 依赖ZooKeeper，存在服务不可用风险。
- 如果机器的时钟发生了回拨，那么就会有可能生成重复的ID号。可以在服务启动时，将系统当前时间与zookepper的其它Leaf节点做计算，看当前时间与其它Leaf节点的平均时间的差值是否小于阈值，小于则启动成功，以后每3s上报一次自身时间，否则启动失败
TinyId
滴滴的TinyId分布式ID实现思路与美团的Leaf分布式ID生成方案类似。对于号段用完需要访问db，我们很容易想到在号段用到一定程度的时候，就去异步加载下一个号段，保证内存中始终有可用号段，则可避免性能波动。可以支持多个db，比如2个db，A和B，我们获取号段可以随机从其中一台上获取。那么如果A,B都获取到了同一号段，让A只生成偶数id，B只生产奇数id，对应的db设计增加了两个字段，如下图所示：
delta代表id每次的增量，remainder代表余数，例如可以将A，B的delta都设置2，remainder分别设置为0，1则，A的号段只生成偶数号段，B是奇数号段。 通过delta和remainder两个字段我们可以根据使用方的需求灵活设计db个数，同时也可以为使用方提供只生产类似奇数的id序列。
UidGenerator
百度的UidGenerator分布式ID生成方案类似于Twitter的snowflake算法，但是默认做了调整。
改动如下：
-
**sign(1bit)：**固定1bit符号标识，与Twitter的的一致。
-
**delta seconds (28 bits)：**当前时间与epoch时间的时间差，单位为秒。epoch时间就是指集成
UidGenerator生成分布式ID服务第一次上线的时间，可配置，也一定要配置，因为默认的epoch时间可是2016-09-20，不配置的话，会浪费好几年的可用时间。 -
**worker id (22 bits)：**机器id，UidGenerator会在集成用它生成分布式ID的实例启动的时候，往下边表中插入一行数据，得到的id值就是准备赋给workerId的值。由于workerId默认22位，那么，集成UidGenerator生成分布式ID的所有实例重启次数是不允许超过4194303次（即2^22-1），否则会抛出异常。
DROP TABLE IF EXISTS WORKER_NODE; CREATE TABLE WORKER_NODE( ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY , HOST_NAME VARCHAR(64) NOT NULL COMMENT 'host name', PORT VARCHAR(64) NOT NULL COMMENT 'port', TYPE INT NOT NULL COMMENT 'node type: ACTUAL or CONTAINER', LAUNCH_DATE DATE NOT NULL COMMENT 'launch date', MODIFIED DATETIME NOT NULL COMMENT 'modified time', CREATED DATEIMTE NOT NULL COMMENT 'created time' )COMMENT='DB WorkerID Assigner for UID Generator',ENGINE = INNODB; -
**序列 (13 bits)：**每秒下的并发序列，13 bits可支持每秒8192个并发，具体原理如下：
- synchronized保证线程安全。
- 如果时间有任何的回拨，那么直接抛出异常。
- 如果当前时间和上一次是同一秒时间，那么sequence自增。如果同一秒内自增值超过2^13-1，那么就会自旋等待下一秒（getNextSecond）。
- 如果是新的一秒，那么sequence重新从0开始。
这些字段的长度可以根据具体的应用需要进行动态的调整，满足总长度为64位即可。
动态扩容
停机迁移
与单库单表数据迁移到分库分表的停机迁移基本一致。
1、发布公告：为了进行数据的重新拆分，在停止服务之前，我们需要提前通知用户，比如：我们的服务会在yyyy-MM-dd进行升级，给您带来的不便敬请谅解。
2、停止服务：关闭Service
3、离线数据迁移（拆分，重新分配数据）：将旧库中的数据按照Service层的算法，将数据拆分，重新分配数据
4、数据校验：开发定制一个程序对旧库和新库中的数据进行校验，比对
5、更改配置：修改Service层的配置算法，也就是将原来的uid%3变为uid%4
6、恢复服务：重启Service服务
7、回滚预案：针对上述的每个步骤都要有数据回滚预案，一旦某个环节（如：数据迁移，恢复服务等）执行失败，立刻进行回滚，重新再来
**缺点：**时间长，数据大容易出错，不推荐使用。
主从扩容
两个集群，分别是主库A和从库A0，主库B和从库B0，如下图所示：
1、先把A0和B0升级为新的主库节点，如此，由2个分库变为4个分库。同时在上层的分片配置，做好映射，规则如下：
uid%4=0的和uid%4=2的数据分别分配到A和A0主库中uid%4=1的和uid%4=3的数据分别分配到B和B0主库中
2、由于A和A0库的数据相同，B和B0库的数据相同，此时无需数据迁移，只需调整分片配置即可，可通过配置中心更新，不需要重启。
3、扩容之后旧数据有冗余，需要对冗余数据做清理
-- 针对A，去除%4!=0的
delete from user.userinfo where uid % 4 != 0;
-- 针对A0，去除%4!=2的
delete from user.userinfo where uid % 4 != 2;
-- 针对B，去除%4!=1的
delete from user.userinfo where uid % 4 != 1;
-- 针对B0，去除%4!=3的
delete from user.userinfo where uid % 4 != 3;
利用中间件
1、利用Rokect MQ监听旧库binlog，进行rehash后，按库分到不同的队列中，此处是为了保证消息的顺序性。此处只是开启积压Rokect消息并不会真正消费。
2、单独一个服务，从旧库分批select数据，经过rehash后批量插入到新库。
3、当历史全量数据同步完成后，再消费Rokect MQ消息进行增量数据同步，这样来保证迁移数据过程中的数据一致。
4、最后比对新旧库数据。
分库分表产生的问题及解决方案
事务问题
需要使用分布式事务，sharding-jdbc支持分布式事务。
跨库关联
全局表，也可看做是“数据字典表“，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。
选择合适的字段做分库分表的分片key，避免跨库关联。
如果每次join操作只是为了获取少量的字段，那么可以考虑直接将这些字段冗余到表上。
需要做两次查询，把两次查询的结果在应用层做合并。这种做法是最简单的，在应用层设计的时候需要考虑。
可以将全量数据冗余一份到ES中，当出现分表字段支持不了的跨库查询时，可以使用ES来支持。除此之外，ES也会用于支持一些复杂搜索查询请求。ES只存储需要进行搜索的字段，查询完ES后再根据关键字段去数据库查询完整的数据，这样是为了控制ES的大小，否则ES会容易过大，导致性能和存储问题。
聚合/排序/分页问题
分别在各个节点上得到聚合/排序后在应用程序端进行合并处理。
先合并各个节点上的数据，再在应用程序端进行聚合/排序后处理。
sphinx解决。
分布式id问题
使用分布式ID生成方案。
统计问题
分表的数据合并，并在此基础上执行聚合查询。
统计表预聚合。
ES，Redis聚合。
分表的数据合并，并在此基础上执行聚合查询
分库分表方案
Client 客户端直连模式
在设计分片时，我们已经明确了每张表的分片键信息，所以业务或服务可以直接根据分片键对应的数据库信息，直接访问底层的 MySQL 数据节点，比如在代码里可以做类似的处理：
void InsertOrders(String orderKey, int userKey...) {
int shard_id = userKey % 4;
if (shard_id == 0) {
conn = MySQLConncetion('shard1',...);
conn.query(...);
} else if (shard_id == 1) {
conn = MySQLConncetion('shard2',...);
conn.query(...);
} else if (shard_id == 2) {
conn = MySQLConncetion('shard3',...);
conn.query(...);
} else if (shard_id == 3) {
conn = MySQLConncetion('shard4',...);
conn.query(...);
}
}
从这段代码中我们可以看到，在业务代码中会嵌入分库分表的路由逻辑，在业务层计算出对应分片的信息，然后访问数据库：
- 这种处理方式的好处是与单实例数据库没有太大的不同，只是多了一次计算分片的操作，没有额外的开销，性能非常好。
- 这种处理逻辑的缺点是业务需要知道分片信息，感知分片的变化。对于上面的例子，如果分片 shard1 发生变化，又或者进行了扩容，业务就需要跟着修改。
为了解决这个缺点，比较好的处理方式是使用名字服务，而不要直接通过 IP 访问分片。这样当分片发生切换，又或者扩容缩容时，业务也不需要进行很大的改动。
Proxy 代理模式
代理分片就是在应用层和数据库层之间添加一个代理层，把分片的路由规则配置在代理层，代理层对外提供与JDBC兼容的接口给应用层，在业务实现之后，在代理层配置路由规则即可。比较成熟的方案有MyCat、ShardingSphere等。
**优点：**让应用层的开发人员专注于业务逻辑的实现，把分库分表的配置留给代理层处理。
**缺点：**增加了代理层，这样的话对每个数据库操作都增加了一层网络传输，这对性能是有影响的，同时需要维护增加的代理层，也有了硬件成本，线上生产环境出现了问题，不能迅速定位。
ShardingSphere简介
核心概念
**逻辑表：**水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是t_order_0到t_order_9，他们的逻辑表名为t_order。
**真实表：**在分片的数据库中真实存在的物理表。即上个示例中的t_order_0到t_order_9。
**数据节点：**数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_order_0。
**绑定表：**指分片规则一致的主表和子表。比如t_order_0与t_order_item_0。
**广播表：**所有的分片数据源中都存在的表，表结构和数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。
Sharding-JDBC分表简单步骤
配置多个DruidDataSource表示多个数据源。
配置表规则。
配置分库策略。
配置分表策略。
配置分片规则。
获取数据源对象
QL 数据节点，比如在代码里可以做类似的处理：
void InsertOrders(String orderKey, int userKey...) {
int shard_id = userKey % 4;
if (shard_id == 0) {
conn = MySQLConncetion('shard1',...);
conn.query(...);
} else if (shard_id == 1) {
conn = MySQLConncetion('shard2',...);
conn.query(...);
} else if (shard_id == 2) {
conn = MySQLConncetion('shard3',...);
conn.query(...);
} else if (shard_id == 3) {
conn = MySQLConncetion('shard4',...);
conn.query(...);
}
}
从这段代码中我们可以看到，在业务代码中会嵌入分库分表的路由逻辑，在业务层计算出对应分片的信息，然后访问数据库：
- 这种处理方式的好处是与单实例数据库没有太大的不同，只是多了一次计算分片的操作，没有额外的开销，性能非常好。
- 这种处理逻辑的缺点是业务需要知道分片信息，感知分片的变化。对于上面的例子，如果分片 shard1 发生变化，又或者进行了扩容，业务就需要跟着修改。
为了解决这个缺点，比较好的处理方式是使用名字服务，而不要直接通过 IP 访问分片。这样当分片发生切换，又或者扩容缩容时，业务也不需要进行很大的改动。
Proxy 代理模式
代理分片就是在应用层和数据库层之间添加一个代理层，把分片的路由规则配置在代理层，代理层对外提供与JDBC兼容的接口给应用层，在业务实现之后，在代理层配置路由规则即可。比较成熟的方案有MyCat、ShardingSphere等。
**优点：**让应用层的开发人员专注于业务逻辑的实现，把分库分表的配置留给代理层处理。
**缺点：**增加了代理层，这样的话对每个数据库操作都增加了一层网络传输，这对性能是有影响的，同时需要维护增加的代理层，也有了硬件成本，线上生产环境出现了问题，不能迅速定位。
ShardingSphere简介
核心概念
**逻辑表：**水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例：订单数据根据主键尾数拆分为10张表，分别是t_order_0到t_order_9，他们的逻辑表名为t_order。
**真实表：**在分片的数据库中真实存在的物理表。即上个示例中的t_order_0到t_order_9。
**数据节点：**数据分片的最小单元。由数据源名称和数据表组成，例：ds_0.t_order_0。
**绑定表：**指分片规则一致的主表和子表。比如t_order_0与t_order_item_0。
**广播表：**所有的分片数据源中都存在的表，表结构和数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。
Sharding-JDBC分表简单步骤
配置多个DruidDataSource表示多个数据源。
配置表规则。
配置分库策略。
配置分表策略。
配置分片规则。
获取数据源对象
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
