①基础

🌟关系型数据库和非关系型数据库的区别

关系型数据库采用了关系模型（二维表格类型）组织数据，一般遵守事务的ACID特性
- 优点：支持通用SQL语句、支持事务、数据存储在磁盘，安全可靠
- 缺点：高并发读写能力差（磁盘I/O有限）、海量数据读写效率低
非关系型数据库通常指数据以对象的形式存储在数据库中，常用于存储非结构化的数据。
- 优点：速度快、效率高、成本低、高并发、海量数据处理轻松
- 缺点：不支持SQL语句、无事务处理、数据结构复杂

🌟MySQL 基础架构

MySQL 主要由下面几部分构成：

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
- 缓存虽能够提升查询性能，但也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎 ：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。

引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。

🌟MySQL执行计划分析

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。

执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

EXPLAIN 适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句，我们一般分析 SELECT 查询较多。

我们这里简单来演示一下 EXPLAIN 的使用。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

🌟MySQL常见存储引擎区别

MySQL 存储引擎有 MyISAM 、InnoDB、Memory

MySQL 5.5 之前，MyISAM 是 MySQL 的默认存储引擎，MySQL 5.5 之后， InnoDB 成为 MySQL 的默认存储引擎。

1.是否支持行级锁。InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。
2.是否支持事务。MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。
3.是否支持外键。MyISAM 不支持外键，而 InnoDB 支持。
4.是否支持数据库异常崩溃后的安全恢复。MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。
5.是否支持 MVCC。MyISAM 不支持 MVVC，而 InnoDB 支持。
6.索引实现不一样。虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。
7.性能有差别。InnoDB 的性能比 MyISAM 更强大。
表结构文件：MyISAM 的表结构文件包括 .frm(表结构定义)，.MYI(索引)、.MYD（数据）；而InnDB的表数据文件为 .ibd(数据和索引集中存储)和.frm(表结构定义)。
记录存储顺序：MyISAM按照记录插入顺序，InnoDB按照主键大小顺序有序插入。
操作速度：对于SELECT前者更优，INSERT、UPDATE、DELETE后者更优。select count(*)使用MyISAM更块，因为内部维护了一个计数器，可以直接调度。
- 为什么InnoDB没有计数器变量？因为InnoDB的事务特性，同一时刻表中的行数对于不同事务而言是不同的，因此计数器统计的是当前事务对应的行数，而不是总行数。

🌟InnoDB 四大特性如下

插入缓存，Insert buffer
二次写，double write
自适应哈希索引，adaptive hash index
预读，read ahead

🌟SQL语句执行顺序

SQL 查询语句的执行顺序解析

(9) SELECT 
(10) DISTICNCT column,
(6) AGG_FUNC(column or expiression), ...
(1) FROM left_table
    (3) JOIN right_table
    (2) ON tablename.column = other_tablename.column
(4) WHERE constraint_expiression
(5) GROUP BY column
(7) WITH CUBE|ROLLUP
(8) HAVING constraint_expiression
(11)ORDER BY column ASC|DESC
(12)LIMIT count OFFSET count;

FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT

1、from 子句组装来自不同数据源的数据；

2、on 过滤器

3、join 添加外部行

4、where 子句基于指定的条件对记录行进行筛选；

5、group by 子句将数据划分为多个分组；

6、AGG_FUNC 使用聚集函数进行计算；

7、WITH CUBE|ROLLUP

8、having 子句筛选分组；

9、select 的字段；

10、distinct 行去重

11、order by 对结果集进行排序。

12、limit 指定返回行

🌟select的执行过程

查询语句的执行流程如下：连接器权限校验—>查询缓存—>解析器—>优化器—>执行器—>引擎

连接器：建立连接，管理连接、校验用户身份；
- 首先客户端和MySQL通过TCP三次握手建立连接，连接成功就去去校验用户名和密码，检验通过之后连接器会获取用户权限并且保存起来，后续的操作都会基于读到的权限进行判断。
查询缓存：SQL语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
- 对于更新比较频繁的表，查询缓存的命中率很低的，只要一个表有更新操作，那么这个表的查询缓存就会被清空。MySQL 8.0 查询缓存已被删掉。
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
- 词法分析是把SQL语句的字符串识别出关键字，方便后续优化
- 语法分析根据语法规则判断SQL语句是否满足要求，如果SQL语句不对就会报错
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

🌟update的执行过程

更新语句执行流程如下：连接器—>解析器—>优化器—>执行器—>引擎—>undo log—>redo log(prepare 状态)—>binlog—>redo log(commit状态)

当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。

具体更新一条记录 UPDATE t_user SET name = 'jj' WHERE id = 1; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，在内存修改该 Undo 页面后，需要记录对应的 redo log。
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术（MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上）
至此，一条记录更新完了。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
事务提交，剩下的就是「两阶段提交」的事情了。

两阶段提交：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，将记录写到 redo log 里面,然后将 redo log 持久化到磁盘；
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit；

🌟DDL、DML、DCL、TCL

DDL – 数据定义语言，用于操作数据结构，比如创建表，删除表，更改索引等都是DDL。
DML – 数据操作语言，用于检索或者修改数据。我们平常最常用的增删查改就是DML。
DCL – 数据控制语言，用于定义数据库用户的权限，比如创建用户，授权用户，删除用户等都是DCL。
TCL – 事务控制语言，用于管理数据库中的事务。比如事务开启，提交，回滚等都是TCL

🌟drop、delete、truncate的区别

	drop	delete	truncate
删除内容	删除整个表（结构、数据、索引等）	删除一行记录	删除整个表数据，表结构还在
类型	DDL	DML，会产生redo log	DDL
回滚	不可回滚	可回滚	不可回滚
速度	快	慢	较快

一般来讲，删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。

🌟数据库设计的三大范式

三大范式是 Mysql 数据库设计表结构所遵循的规范和指导方法，目的是为了减少冗余，建立结构合理的数据库，从而提高数据存储和使用的性能。

第一范式（1NF）：字段不可再拆分（原子性）。要求表中的数据每一列都是不可分割的原子项数据
第二范式（2NF）：属性完全依赖于主键（唯一性）。消除部分依赖。在表满足第一范式的条件下，要求表中的每一列都依赖于主键。
第三范式（3NF）：属性不依赖于其它非主属性。消除传递依赖。在表满足第二范式的条件下，要求表中的每一列都和主键是直接依赖的，不是间接依赖。
- 在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取某非主键字段 B。

🌟范式设计会出现什么问题

范式是普适的规则，满足大多数的业务场景的需求。对于一些特殊的业务场景，范式设计的表，无法满足性能的需求。此时，就需要根据业务场景，在范式的基础之上进行灵活设计，也就是反范式设计。

反范式设计就是用空间来换取时间，提高业务场景的响应时间，减少多表关联。

🌟join 与 left join 的区别是什么？

join等价于inner join内连接，返回两个表中都有的符合条件的行。
left join左连接，返回左表中所有的行及右表中符合条件的行。
right join右连接，返回右表中所有的行及左表中符合条件的行。
full join全连接，返回左表中所有的行及右表中所有的行，并按条件连接。（Oracle支持，mysql不支持）
- 左外连接+ union+右外连接实现

🌟SQL怎么实现模糊查询？

每一次按照模糊匹配的前缀字典序来进行比较。like关键字

%：表示任意0个或多个字符
_：表示任意单个字符
[ ]：表示括号内所列字符中的一个
[^ ] ：表示不在括号所列之内的单个字符

🌟varchar 和 char 的区别是什么？

区别：存储的容量不同、定长和变长、效率和空间

对 char 来说，最多能存放 255 个字符，大小和编码无关。char 表示定长，长度固定。char插入的长度小于定义长度时，剩余的空间用空格填充。char长度固定，所以存取速度比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间，可谓是以空间换取时间效率。
而 varchar 呢，最多能存放 65532 个字符，varchar的最大有效长度由最大行大小和使用的字符集确定。varchar表示变长，长度可变。varchar插入的长小于定义长度时，剩余的空间会留给其他数据使用。varchar则刚好相反，以时间换空间。

🌟varchar能完全代替char么？

不能。varchar的优点是更灵活。但是char也不是一无是处的。

首先，varchar会额外用一个字节存储长度信息，而char则节约了一个字节；

其次，char的存储空间都是一次性分配的，存储是固定连续的，varchar的存储的长度是可变的，当varchar更改前后数据长度不一致时，就不可避免的会出现碎片的问题。针对此，需要进行碎片消除作业，也是额外的成本。

一般来说，长度固定的字段，还是用char比较合适，比如Hash，就很适合用char。

🌟varchar(5)和int(5)中的50，有什么区别？

varcahr中代表能存5个字符，实际存储“50”

int中只是代表显示长度，实际存储“00050”

比如int(1)和int(20)存储和计算其实是一样的。数字只是用于显示长度，不会影响存储空间

🌟count(*) 和 count(1)和count(列名)区别

count(*)≈count(1)>count(主键)>count(字段)

count(*)会统计表所有的列，相当于行数，不会忽略为NULL的值。
count(1)会统计表所有的列，不会忽略为NULL的值。忽略了所有列，每一列都被使用1来代表
count(列名) 会统计该列名在表中出现的次数，忽略字段为NULL 的情况。

count(主键)需要判断主键是否为空值；count(字段)会进行全表扫描，效率最差。

如果表只有一个字段， count(*)最优
如果表多个列并且没有主键，count(1) 优于 count（*）
列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果有主键，count(主键)最优

🌟UNION和UNION ALL的区别？

union和union all的作用都是将两个结果集合并到一起。

union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。
union all的性能比union性能好。

②索引

🌟B+ 树简介

标准 B+ 树	Innodb 里的 B+ 树

B+树的特点有两个：

1.非叶子节点]仅具有索引作用，也就是说，非叶子节点只能存储Key,不能存储value
2.树的所有叶节点构成一个有序链表，可以按照key排序的次序依次遍历全部数据。

但是 Innodb 使用的 B+ 树有一些特别的点，比如：

B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

🌟B+树有几层，最大可以存放多少条数据

B+树一般是1～3层。

InnoDB页的大小默认是16KB：

假设一条记录大小为1KB，则一个数据页中可以存16条数据（忽略页中的其他数据结构）
假设主键为int 4B，指针大小为6B，则一个索引页中可以存储16KB/(4B+6B)≈1638个索引

所以，两层的B+树可以存储：16*1638=26208条数据（2 万）；三层的B+树可以存储：16*1638*1638=42928704条数据（4 千万）。

🌟MySQL 为什么使用 B+ 树来作索引

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

🌟MySQL的索引为什么使用B+树而不使用跳表？

B+树是多叉树结构，每个结点都是一个16k的数据页，能存放较多索引信息。三层左右就可以存储2kw左右的数据。也就是说查询一次数据，如果这些数据页都在磁盘里，那么最多需要查询三次磁盘IO。

跳表是链表结构，一条数据一个结点，如果最底层要存放2kw数据，且每次查询都要能达到二分查找的效果，2kw大概在2的24次方左右，所以，跳表大概高度在24层左右。最坏情况下，这24层数据会分散在不同的数据页里，也即是查一次数据会经历24次磁盘IO。

因此存放同样量级的数据，B+树的高度比跳表的要少，如果放在MySQL数据库上来说，就是磁盘IO次数更少，因此B+树查询更快。

而针对写操作，B+树需要拆分合并索引数据页，跳表则独立插入，并根据随机函数确定层数，没有旋转和维持平衡的开销，因此跳表的写入性能会比B+树要好。

其实，MySQL的存储引擎是可以换的，以前mysql 5.5是myisam，后来才有的innodb，它们底层索引用的都是B+树。也就是说，你完全可以造一个索引为跳表的存储引擎装到MySQL里。事实上，facebook造了个rocksDB的存储引擎，里面就用了跳表。直接说结论，它的写入性能确实是比innodb要好，但读性能确实比innodb要差不少。

🌟Redis为什么使用跳表而不使用B+树或二叉树呢？

因为数据在内存中读取耗费的时间是从磁盘的IO读取的百万分之一，而Redis是内存中操作数据，不涉及IO，因此使用了跳表；

B+树的原理是叶子节点存储数据，非叶子节点存储索引，B+树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点，每个叶子节点还有指向前后节点的指针，为的是最大限度的降低磁盘的IO。

🌟B+-tree优点

I/O次数更少：B+-tree 的内部结点没有存储关键字，所以同样大小的磁盘页能容纳更多结点。一次性读入内存中的也就越多，相对来说IO读写次数也就降低了
查询速度更稳定：B+-tree 所有关键字数据地址都存在叶子结点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
范围查询更简便：B+-tree 所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
全结点遍历更快：B+-tree 遍历整棵树只需要遍历所有的叶子节点即可， B-tree 需要对每一层进行遍历，这有利于数据库做全表扫描。

🌟B+-tree和B-tree的区别

B 树的所有节点既存放键(key) 也存放数据(data)，B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的；B+-tree 相邻的叶子节点之间是通过链表指针连起来的。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

🌟InnoDB 和 MyISAM 实现的 B+ 树区别

InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

MyISAM实现的 B+ 树	InnoDB实现的 B+ 树

🌟简述 MySQL 常见索引

[深入浅出 InnoDB Index.pdf (aliyuncs.com)](https://smartkeyerror.oss-cn-shenzhen.aliyuncs.com/Phyduck/database/深入浅出 InnoDB Index.pdf)

按「数据结构」分类：B+树索引、Hash索引、Full-text索引。
- 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
- 主键索引：设定为主键后数据库会自动建立索引，innodb 为聚簇索引，值必须唯一且不能为null，一个表中只有一个。
  - 建表时，加上 primary key(列名) 指定
- 唯一索引：索引列的值必须唯一，但允许有 null 且 null 可以出现多次
  - 建表时，加上 unique(列名) 指定
  - 单独创建，create unique index idx 表名(列名) on 表名(列名)
  - 单独创建，alter table 表名 add unique 索引名(列名)
- 前缀索引 ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上
  - 单独创建，alter table 表名 add 索引名(column_name(索引长度))
按「字段个数」分类：单列索引、联合索引。
- 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引
  - 建表时，加上 key(列名) 指定
  - 单独创建，create index 索引名 on 表名(列名)
  - 单独创建，alter table 表名 add index 索引名(列名)
- 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
  - 建表时，加上 key(列名列表) 指定
  - 单独创建，create index 索引名 on 表名(列名列表)
  - 单独创建，alter table 表名 add index 索引名(列名列表)

普通索引：仅加速查询。

覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。

MySQL 8.x 中实现的索引新特性：

隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

🌟聚集索引和二级索引的区别

对主键字段建立的索引叫做聚簇索引，对普通字段建立的索引叫做二级索引。

Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于

聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，
二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

如下左图为聚集索引，右图为二级索引（name 字段设置为二级索引）

聚集索引	二级索引

在我们使用「主键索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么就会在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后直接读取要查询的数据。如下面这条语句：

// id 字段为主键索引
select * from t_user where id=1;

在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么需要检索两颗B+树：

先在「二级索引」的 B+ 树找到对应的叶子节点，获取主键值；
然后用上一步获取的主键值，在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据。

上面这个过程叫做回表，如下面这条语句：

// name 字段为二级索引
select * from t_user where name="林某";

在我们使用「二级索引」字段作为条件查询的时候，如果要查询的数据在「二级索引」的叶子节点，那么只需要在「二级索引」的 B+ 树找到对应的叶子节点，然后读取要查询的数据，这个过程叫做覆盖索引。如下面这条语句：

// name 字段为二级索引
select id from t_user where name="林某";

🌟聚簇索引和非聚簇索引有什么区别？

聚簇索引即索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引
非聚簇索引即索引结构和数据分开存放的索引，二级索引就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

🌟非聚簇索引一定回表查询吗(覆盖索引)

非聚簇索引不一定回表查询。当查询的数据刚好就是非聚簇索引叶子节点所保存的主键，则不需要回表。这种情况就称之为覆盖索引了。

🌟介绍一下覆盖索引

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

🌟怎么通过执行计划知道这条语句使用了索引覆盖

在下图中执行计划，我们可以看到，执行过程中使用了普通索引（name），Exta 为 Using index，这就是表明使用了覆盖索引优化。

🌟唯一索引与普通索引的区别是什么

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。

🌟最左前缀匹配原则

面试官:谈谈你对mysql联合索引的认识? - 知乎 (zhihu.com)

使用联合索引时，存在最左匹配原则，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配。使用联合索引进行查询的时候，如果不遵循最左匹配原则，联合索引会失效。

如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 **>、<**）才会停止匹配。对于 **>=、<=**、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据，区分度低的字段放后面，像性别、状态这种字段区分度就很低，我们一般放后面。

比如，对(a,b,c,d)建立索引

where a = 1 and b = 2 and c > 3 and d = 4

那么，a,b,c三个字段能用到索引，而d就匹配不到。因为遇到了范围查询！

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？下面会讲解

为什么联合索引不遵循最左匹配原则就会失效？

在联合索引的情况下，数据是按照索引第一列排序，第一列数据满足时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

🌟假设建立联合索引 (a, b, c) 如果对字段 b 和 c 查询，会用到这个联合索引吗？

多个普通字段组合在一起创建的索引就叫做联合索引。

(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左前缀匹配原则的情况下，是无法利用到索引的。利用索引的前提是索引里的 key 是有序的。

🌟假设建立联合索引 (a, b, c) 如果对字段 a 和 c 查询，会用到这个联合索引吗？

MySQL 5.6 之前 a可以用，c用不了，MySQL 5.6 开始可以用

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

🌟索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

给你这个SQL：

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

有些朋友可能觉得奇怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。因此，MySQL5.6版本之后，选出包含“小”字后，顺表过滤age=28

🌟使用索引会有哪些优缺点

优点：

使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)
加速表与表之间的连接

缺点：

创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
索引需要使用物理文件存储，也会耗费一定空间。

🌟什么时候需要/不需要创建索引

使用索引：

字段有唯一性限制，比如商品编码
经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度
经常用于 GROUP BY 和 ORDER BY 的字段，可以创建联合索引
查询中排序的字段：排序的字段如果通过索引去访问将大大提高排序速度
查询中与其它表关联的字段：例如字段建立了外键关系

不用索引：

表记录太少：表数据太少的时候，不需要创建索引
经常增删改的字段：经常更新的字段不用创建索引，索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，会影响数据库性能
数据重复且分布平均的表字段：假如一个表有10万行记录，性别只有男和女两种值，且每个值的分布概率大约为50%，那么对这种字段建索引一般不会提高数据库的查询速度。
where、group by、order by 里用不到的字段

🌟MySQL 索引使用什么数据结构

哈希表结构
B+树结构

为什么 MySQL 没有使用其作为索引的数据结构呢？

Hash 索引不支持顺序和范围查询
不支持模糊查询、最左前缀匹配
存在哈希冲突

🌟如何设计索引，如何优化查询？

能在尽可能少的磁盘的 I/O 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；
- 为频繁查询的字段建立索引，频繁增删改的字段不要建立索引
- 避免为”大字段”建立索引
- 选择区分度大的列作为索引
- 尽量为ORDER BY 和 GROUP BY 后面的字段建立索引
- 不要在条件中使用函数
- 不要建立太多的索引，占用空间

优化索引的方法？

前缀索引优化；
- 使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。
- order by 就无法使用前缀索引；
- 无法把前缀索引用作覆盖索引；
覆盖索引优化；
- 使用覆盖索引的好处就是，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。
主键索引最好是自增的；
- 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
防止索引失效；
联合索引最左前缀原则
索引最好设置为 NOT NULL
- 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。
- 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间。

🌟索引什么情况下会失效？

当索引使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
- 因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
当对索引列使用函数，就会导致索引失效。
- 因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
对索引列进行表达式计算，也是无法走索引的。
- 原因跟对索引使用函数差不多。
对索引隐式类型转换，也是无法走索引的。
- MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。
- 如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
联合索引不遵循最左匹配原则，就会导致索引失效。
- 原因是在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。
- 这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

③事务

🌟什么是数据库事务

MySQL 为什么会使用 InnoDB 作为默认选项

数据库事务可以保证多个对数据库的操作（也就是 SQL 语句）构成一个逻辑上的整体。这个整体：要么全部执行成功,要么全部不执行 。

不过并不是所有的引擎都能支持事务，比如 MySQL 原生的 MyISAM 引擎就不支持事务，也正是这样，所以大多数 MySQL 的引擎都是用 InnoDB。

🌟简述事务的ACID特性

原子性（Atomicity）：确保操作要么全部成功，要么全部失败；——通过 undo log（回滚日志）来保证

一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；——通过持久性+原子性+隔离性来保证

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；——通过 MVCC（多版本并发控制）或锁机制来保证

持久性（Durability）：一个事务被提交之后。它对数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。——通过 redo log （重做日志）来保证

🌟如何开启、提交、回滚一个事务？

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
SQL1,SQL2...
## 提交事务
COMMIT;

### 回滚事务
rollback；

🌟InnoDB 怎么保证事务的ACID特性？

原子性是通过 undo log（回滚日志）来保证的；

InnoDB对一条记录进行操作（增、删、改）时，先将回滚需要的信息记录到undo log的日志文件中，如果失败了或者主动rollback，就可以通过undo log的内容，将事务回滚。

持久性是通过 redo log （重做日志）来保证的；

当数据修改时，会先在redo log记录这次操作，然后再修改缓冲池（buffer pool）中的数据，当事务提交时，会调用fsync接口对 redo log 进行刷盘。（redo log是预写式(WAL)日志，所有修改是先写入日志再执行，所以保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。）

隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

🌟并行事务会引发什么问题？

MySQL 服务端是允许多个客户端连接的，这意味着 MySQL 会出现同时处理多个事务的情况。

那么在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题。

脏读：一个事务「读到」了另一个「未提交事务修改过的数据」

不可重复读：一个事务内多次读取同一个数据，出现前后两次读到的数据不一样的情况

幻读：一个事务内多次查询某个符合条件的「记录数量」，出现前后两次查询到的记录数量不一样的情况

不可重复读和幻读有什么区别？

不可重复读的重点是内容修改或者记录减少。
幻读的重点在于记录增加。

总结：

脏读：读到其他事务未提交的数据；
不可重复读：前后读取的数据不一致；
幻读：前后读取的记录数量不一致。

这三个现象的严重性排序如下：脏读 > 不可重复读 > 幻读

🌟数据库的事务隔离级别有哪些？优缺点？

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别如下：

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，**InnoDB 默认隔离级别 **；
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

按隔离水平高低排序如下：串行化 > 可重复读 > 读提交 > 读未提交

针对不同的隔离级别，并发事务时可能发生的现象也会不同。

各大企业通常为什么采用rc隔离级别?

rr隔离级别有gap锁，在并发的场景，很容易产生死锁，死锁问题的发生比出现幻读的问题成本更大

🌟如何解决脏读、可重复读、幻读的？

要解决脏读现象，就要升级到「读提交」以上的隔离级别；

要解决不可重复读现象，就要升级到「可重复读」的隔离级别，

要解决幻读现象不建议将隔离级别升级到「串行化」（影响性能）。

MySQL 在「可重复读」隔离级别下，可以很大程度上避免幻读现象的发生（注意是很大程度避免，并不是彻底避免），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（update、insert、delete、select … for update等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好避免幻读问题。

🌟四种隔离级别具体是如何实现的呢？

事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的，读提交和可重复读可以通过MVCC/锁实现，串行化可以通过锁机制实现。

对于「读未提交」隔离级别，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于「读提交」和「可重复读」隔离级别

快照读通过MVCC（Read View + undolog + 隐藏列）来实现的，两种隔离级别的区别在于创建 Read View 的时机不同（可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。）

「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
「可重复读」隔离级别是在「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。
这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

当前读通过加锁来实现的，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

🌟简述 MVCC 的实现原理

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

在读提交和可重复读隔离级别下，快照读是通过MVCC（Read View + undolog + 隐藏列）来实现的

通过**事务的 Read View 字段和记录中的两个隐藏列(trx_id 和 roll_pointer)**的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为。

MVCC 在 MySQL 中实现所依赖的手段主要是: **Read View 、隐藏列、undo log **。

undo log : undo log 用于记录某行数据的多个版本的数据。
Read View 和隐藏列 : 用来判断当前版本数据的可见性。

Read View 有四个重要的字段：

m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

聚簇索引记录中的两个隐藏列：

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

🌟分布式事务

分布式系统会把一个应用系统拆分为多个可独立部署的服务，因此需要服务与服务之间远程协作才能完成事务操作，这种分布式系统环境下由不同的服务之间通过网络远程协作完成事务称之为分布式事务，例如用户注册送积分事务、创建订单减库存事务，银行转账事务等都是分布式事务。

CAP理论

一致性（Consistency） : 所有节点访问同一份最新的数据副本
可用性（Availability）: 非故障的节点在合理的时间内返回合理的响应（不是错误或者超时的响应）。
分区容错性（Partition Tolerance） : 分布式系统出现网络分区的时候，仍然能够对外提供服务。

如果系统没有发生“分区”，节点间的网络连接通信正常，也就不存在 P 了。这个时候，可以同时保证 C 和 A 了。如果系统发生“分区”，要考虑选择 CP 还是 AP。

CAP 理论中分区容错性 P 是一定要满足的，在此基础上，只能满足可用性 A 或者一致性 C。

因此，分布式系统理论上不可能选择 CA 架构，只能选择 CP 或者 AP 架构。 比如 ZooKeeper、HBase 就是 CP 架构，Cassandra、Eureka 就是 AP 架构，Nacos 不仅支持 CP 架构也支持 AP 架构。

为啥不可能选择 CA 架构呢？ 举个例子：若系统出现“分区”，系统中的某个节点在进行写操作。为了保证 C，必须要禁止其他节点的读写操作，这就和 A 发生冲突了。如果为了保证 A，其他节点的读写操作正常的话，那就和 C 发生冲突了。

BASE理论

也就是牺牲数据的一致性来满足系统的高可用性，系统中一部分数据不可用或者不一致时，仍需要保持系统整体“主要可用”。

Basically Available（基本可用） ：指分布式系统在出现不可预知故障的时候，允许损失部分可用性。
- 响应时间上的损失: 正常情况下，处理用户请求需要 0.5s 返回结果，但是由于系统出现故障，处理用户请求的时间变为 3 s。
- 系统功能上的损失：正常情况下，用户可以使用系统的全部功能，但是由于系统访问量突然剧增，系统的部分非核心功能无法使用。
Soft-state（软状态） ：指允许系统中的数据存在中间状态（CAP 理论中的数据不一致），并认为该中间状态的存在不会影响系统的整体可用性。
Eventually Consistent（最终一致性） ：最终一致性强调的是系统中所有的数据副本，在经过一段时间的同步后，最终能够达到一个一致的状态。

BASE 理论是对 CAP 中一致性 C 和可用性 A 权衡的结果，其来源于对大规模互联网系统分布式实践的总结，是基于 CAP 定理逐步演化而来的，它大大降低了我们对系统的要求。

BASE 理论本质上是对 CAP 的延伸和补充，更具体地说，是对 CAP 中 AP 方案的一个补充。

AP 方案只是在系统发生分区的时候放弃一致性，而不是永远放弃一致性。在分区故障恢复后，系统应该达到最终一致性。这一点其实就是 BASE 理论延伸的地方。

分布式一致性的 3 种级别：

强一致性 ：系统写入了什么，读出来的就是什么。
弱一致性 ：不一定可以读取到最新写入的值，也不保证多少时间之后读取到的数据是最新的，只是会尽量保证某个时刻达到数据一致的状态。
最终一致性 ：弱一致性的升级版，系统会保证在一定时间内达到数据一致的状态。

业界比较推崇是最终一致性级别，但是某些对数据一致要求十分严格的场景比如银行转账还是要保证强一致性。

那实现最终一致性的具体方式是什么呢?

读时修复 : 在读取数据时，检测数据的不一致，进行修复。（推荐，性能消耗比较低）
写时修复 : 在写入数据，检测数据的不一致时，进行修复。
异步修复 : 这个是最常用的方式，通过定时检测副本数据的一致性，并修复。

ACID 是数据库事务完整性的理论，CAP 是分布式系统设计理论，BASE 是 CAP 理论中 AP 方案的延伸。

④锁

🌟表级锁和行级锁了解吗？有什么区别？

MyISAM 仅仅支持表级锁，一锁就锁整张表，这在并发写的情况下性非常差。
InnoDB 不光支持表级锁，还支持行级锁，默认为行级锁。

表级锁和行级锁对比 ：

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

🌟行级锁的使用有什么注意事项？

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

那 update 语句的 where 带上索引就能避免全表记录加锁了吗？

并不是。关键还得看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

解决办法

当我们要执行 update 语句的时候，确保 where 条件中带上了索引列，并且在测试机确认该语句是否走的是索引扫描，防止因为扫描全表，而对表中的所有记录加上锁。

我们可以打开 MySQL sql_safe_updates 参数，这样可以预防 update 操作时 where 条件没有带上索引列。

如果发现即使在 where 条件中带上了列索引列，优化器走的还是全表扫描，这时我们就要使用 force index([index_name]) 可以告诉优化器使用哪个索引。

🌟select … for update加什么锁

如果 WHERE条件中字段命中唯一索引，即当查询用到索引时是行锁

如果 WHERE条件中字段没有命中唯一索引，即当查询没用到索引时是表锁

select … for update学习

🌟InnoDB 有哪几类行锁？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

🌟记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

mysql > begin;
mysql > select * from test where id = 1 for update;

就是对 test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

当事务执行 commit 后，事务过程中生成的锁都会被释放。

🌟间隙锁

Gap Lock 称为间隙锁，锁定一个范围，不包括记录本身。只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻读记录而提出的。

🌟临键锁

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

🌟当前读和快照读有什么区别？

快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

🌟共享锁和排他锁呢？

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

普通的 select 语句是不会对记录加锁的，因为它属于快照读（由于 MVCC 的存在）。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

🌟意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁-IS 锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁-IX 锁」；

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁和独占表锁发生冲突。

🌟MySQL怎么加锁的？

当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」。

当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」。

当查询的记录存在时，用非唯一索引进行等值查询除了会加 next-key lock 外，还额外加间隙锁。

当查询的记录不存在时，用非唯一索引进行等值查询只会加 next-key lock，然后会退化为间隙锁。

非唯一索引范围查，next-key lock 不会退化为间隙锁和记录锁。

🌟为什么会发生死锁，如何避免死锁

死锁是指两个或两个以上的线程在执行过程中，因争夺资源而造成的一种互相等待的现象。若无外力作用，它们都将无法推进下去。MySQL中行锁会发生死锁，表锁不会。

如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
- 在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。当发生超时后，就出现下面这个提示：
开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
- 将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。当检测到死锁后，就会出现下面这个提示：

上面这个两种策略是「当有死锁发生时」的避免方式。

⑤日志

undo log（回滚日志）：记录数据被修改前的样子，它保证了事务的原子性，实现事务回滚，实现 MVCC 关键因素之一。Innodb 存储引擎生成的
事务日志 redo log（重做日志）：记录数据被修改后的样子，它保证了事务的持久性，让 MySQL 有崩溃恢复的能力。Innodb 存储引擎生成的
二进制日志 binlog（归档日志）：记录了所有数据库表结构变更和表数据修改的日志，保证数据一致性。Server 层生成的

Buffer Pool

🌟为什么需要 Buffer Pool？

为了提高读写MySQL的读写性能，设计了Buffer Pool

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

🌟Buffer Pool 缓存什么？

在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 除了缓存索引页和数据页，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

undo log

🌟什么是 undo log

undo log（回滚日志），记录数据被修改前的样子，它保证了事务的原子性。

🌟为什么要用 undo log

实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

🌟undo log里面具体记录了什么信息呢？

不同的操作，需要记录的内容也是不同的，所以不同类型的操作（修改、删除、新增）产生的 undo log 的格式也是不同的

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作。

redo log

🌟什么是redo log

redo log （重做日志）是物理日志，记录数据被修改后的样子，它保证了事务的持久性。

在我们执行CRUD操作时，首先会在buffer pool中更新缓存页，更新完buffer pool中的缓存页之后，必须要写一条redo log，这样才能记录下来我们对数据库做的修改

redo log本质是保证事务提交之后，修改的数据绝对不会丢失

🌟为什么要用redo log

redo log可以保证我们事务提交之后，如果事务中的增删改SQL语句更新的缓存页还没有刷到磁盘上去，此时MySQL宕机了，就可以把redo log重做一遍，恢复出来事务当前更新的缓存页，然后再把缓存页刷到磁盘就可以了

这就是 WAL （Write-Ahead Logging）技术。WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

🌟redo log比直接落盘的优点？

redo log 要写到磁盘，数据也要写磁盘，为什么要多此一举？

实现事务的持久性，让 MySQL 有崩溃恢复的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
将写操作从「随机写」变成了「顺序写」，redo log 的写方式使用了追加，日志操作是顺序写，磁盘操作是随机写，提升 MySQL 写入磁盘的性能。

🌟redo log 什么时候刷盘？

执行一个事务的过程中，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。

redo log 也有自己的缓存—— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续在持久化到磁盘。redo log buffer 默认大小 16 MB，可以通过 innodb_log_Buffer_size 参数动态的调整大小，增大它的大小可以让 MySQL 处理「大事务」是不必写入磁盘，进而提升写 IO 性能。

刷盘时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时，根据配置参数 innodb_flush_log_at_trx_commit来确定刷盘时机

参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略如下：

当设置该参数为 0 时，表示每次事务提交时，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。
- 针对参数 0 ：会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失;
当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启之后数据不会丢失。
当设置该参数为 2 时，表示每次事务提交时，都只是缓存在 redo log buffer 里的 redo log 写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache（Page Cache 是专门用来缓存文件数据的，所以写入「 redo log文件」意味着写入到了操作系统的文件缓存。
- 针对参数 2 ：调用 fsync，将缓存在操作系统中 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，较取值为 0 情况下更安全，因为 MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

这三个参数的数据安全性和写入性能的比较如下：

数据安全性：参数 1 > 参数 2 > 参数 0
写入性能：参数 0 > 参数 2> 参数 1

🌟redo log写入过程？

redo log 文件写满了怎么办？

InnoDB有 1 个 redo log 日志文件组，由有 2 个 redo log 文件组成：logfile0 和 logfile1。

redo log组中的两个文件大小是固定且相同的（每个上限1GB），redo log组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。先写 logfile0 文件，当 logfile0 文件被写满的时候，会切换至 logfile1 文件，当 logfile1 文件也被写满时，会切换回 logfile0 文件。

随着系统运行，Buffer Pool 的脏页刷新到了磁盘中， redo log 对应的记录没用了，会腾出空间记录新的更新操作。redo log 是循环写的方式相当于一个环形，用 write pos 表示 redo log 当前记录写到的位置，用 checkpoint 表示当前要擦除的位置。

write pos 追上了 checkpoint，说明 redo log 文件满了，这时 MySQL 不能再执行新的更新操作， MySQL 会被阻塞，会停下来将 Buffer Pool 中的脏页刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动，然后 MySQL 恢复正常运行，继续执行新的更新操作。

🌟redo log 和 undo log 区别在哪？

简述 undo log 和 redo log 的作用

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务
undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务

binlog

🌟什么是binlog

binlog 是逻辑日志，binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性

🌟binlog的三种格式？

STATEMENT(默认格式)、ROW、 MIXED：

STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现。
- 缺陷：STATEMENT 有动态函数的问题，比如用了 uuid 或者 now 这些函数，在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致
ROW：记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题。
- 缺陷：但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句。
MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式。

🌟为什么有了 binlog 还要有 redo log？

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有崩溃恢复（crash-safe）的能力，binlog 日志只能用于归档。所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

🌟redo log 和 binlog 有什么区别？

binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可使用；binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。binlog 用于备份恢复、主从复制。
redo log 是 Innodb 存储引擎实现的日志；redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。redo log 用于掉电等故障恢复。

🌟主从复制是怎么实现？

MySQL 主从复制依赖于 binlog 。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的

MySQL 集群的主从复制过程梳理成 3 个阶段：

写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
回放 Binlog：回放 binlog，并更新存储引擎中的数据。

从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

🌟binlog 什么时候刷盘？

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到page cache中，再调用 fsync 将数据持久化到磁盘的 binlog 文件中，但频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只写入page cache，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；（默认设置）
sync_binlog = 1 的时候，表示每次提交事务都会写入page cache，然后马上执行 fsync；
sync_binlog = N(N>1) 的时候，表示每次提交事务都会写入page cache，但累积 N 个事务后才 fsync。

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

🌟为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，造成两份日志之间的逻辑不一致。MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务一致性协议，它可以保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。MySQL 重启后，通过 redo log 能将 Buffer Pool 恢复到新值，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行是旧值，主从不一致。
如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，数据是旧值，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，这一行字段是新值，与主库的值不一致性。

所以会造成主从环境的数据不一致性。因为 redo log 影响主库的数据，binlog 影响从库的数据，redo log 和 binlog 必须保持一致。

两阶段提交把单个事务的提交拆分成了 2 个阶段，分别是准备(Prepare)阶段和提交(Commit)阶段，每个阶段都由协调者和参与者共同完成。

🌟两阶段提交的过程是怎样的？

在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 日志与 InnoDB 的 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部开启一个 XA 事务，分两阶段来完成 XA 事务的提交。

事务的提交过程有两个阶段，将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog：

prepare 阶段：将内部 XA 事务的 ID 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘。
commit 阶段：把内部 XA 事务的 ID 写入到 binlog，然后将 binlog 持久化到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 成功，只要 binlog 写磁盘成功，redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功。

两阶段提交

⑥性能优化

SQL优化13连问

🌟日常工作中，你是怎么优化SQL的？

分析慢查询日志
使用explain查看执行计划
索引优化
- 最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
- 索引列的基数越大，索引效果越好。
- 根据情况创建复合索引，复合索引可以提高查询效率。
- 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
- 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
- 对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。
避免全表扫描
避免返回不必要的数据（如select具体字段而不是select*）
使用合适的数据类型（如可以使用int类型的话，就不要设计为varchar）
优化sql结构（如join优化等等）
适当分批量进行 (如批量更新、删除)
定期清理无用的数据
适当分库分表
读写分离
深分页优化

🌟聊聊explain执行计划

当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息。

一般来说，我们需要重点关注type、rows、filtered、extra、key。

type

type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
ref : 常用于非主键和唯一索引扫描。
ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：类似于eq_ref，条件用了in子查询
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range：常用于范围查询，比如：between … and 或 In 等操作
index：全索引扫描
ALL：全表扫描

rows

该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。

filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

extra

该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
Using index ：表示是否用了覆盖索引。
Using temporary: 表示是否使用了临时表，性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
Using where : 表示使用了where条件过滤.
Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

key

该列表示实际用到的索引。一般配合possible_keys列一起看。

注意:有时候，explain配合show WARNINGS; （可以查看优化后,最终执行的sql），效果更佳哦。

🌟如何使用索引优化SQL查询？

添加合适索引（在where、group by、order by等后面的字段添加合适索引）
选择合适的索引类型 (B-tree索引适合范围查询、哈希索引适合等值查询)
注意不适合加索引的场景（数据量少的表，更新频繁的字段，区分度低的字段）
加索引的时候，需要考虑覆盖索引，减少回表，考虑联合索引的最左前缀原则
explain查看SQL的执行计划，确认是否会命中索引。
注意索引并不是越多越好，通常建议在单个表中不要超过5个索引。因为索引会占用磁盘空间，索引更新代价高。

🌟sql查询很慢怎么排查

聊聊慢SQL的优化思路

MySQL 问题排查都有哪些手段？

查看慢查询日志记录，分析慢SQL，看看是否命中索引，是否符合最左前缀原则
explain分析SQL的执行计划
profile 分析执行耗时
Optimizer Trace分析详情
确定问题并采用相应的措施

慢查询分析三步曲

一步曲：EXPLAIN（explain 查询命令）
- 查看 SQL 语句执行计划的命令
- 手工执行的时候，在 Extra 列里面，避免出现Use Temporary Table和Using file sort这类关键字，TYPE 列中也尽量避免 ALL 类型（全表扫描）出现。
二步曲：PROFILE（show profiles）
- 可以详细的列出 SQL 语句在每一个步骤消耗的时间，前提（缺点）是先执行一遍语句。
- PROFILE 默认是关闭的，所以需要在 client 端先打开，操作如下：set session profiling = 1;
三步曲：OPTIMIZER_TRACE
- 可以看到内部查询计划的 TRACE 信息，从而可以知道 MySQL 是如何在众多索引中选中最“棒”的那个。
- 一般来说，这个最“棒”的索引选错了，就需要根据 OPTIMIZER_TRACE 的信息来判断为什么会选错，是 MySQL 的配置原因，还是 SQL 某些地方写的不好导致 MySQL 误判了。
  
  开启这个功能的方式如下：set session optimizer_trace='enabled=on';

总而言之，通过这三步曲的排查，基本上 SQL 的问题就都能找出来了。

MySQL 最佳实践：慢查询分析三步曲 - 腾讯云开发者社区-腾讯云 (tencent.com)

🌟慢查询的原因

索引不足：如果查询的表没有合适的索引，MySQL需要遍历整个表才能找到匹配的记录，这会导致查询变慢。可以通过添加索引来优化查询性能。
数据库设计问题：如果数据库设计不合理，例如表过于庞大、列过多等，查询时可能需要耗费大量时间。这时可以通过优化数据库设计来解决问题。
数据库服务器负载过高：如果MySQL服务器上同时运行了太多的查询，会导致服务器负载过高，从而导致查询变慢。可以通过增加服务器硬件配置或分散查询负载来解决问题。
查询语句复杂：复杂的查询语句可能需要耗费更多的时间才能完成。可以尝试简化查询语句或将查询分解成多个较简单的查询语句来提高性能。
数据库统计信息不准确：如果数据库统计信息不准确，MySQL可能会选择不合适的查询计划，从而导致查询变慢。可以通过更新数据库统计信息来解决问题。
MySQL版本过低：较老版本的MySQL可能性能较差，升级到较新版本的MySQL可能会提高查询性能。

盘点MySQL慢查询的12个原因

🌟了解慢查询日志吗？对慢查询如何优化？

慢查询日志一般用于记录执行时间超过某个临界值的SQL语句的日志。

慢查询日志记录了执行时间超过阈值 long_query_time（默认是 10s，通常设置为1s）的所有查询语句，在解决 SQL 慢查询（SQL 执行时间过长）问题的时候经常会用到。

在MySQL中有一个变量专门记录当前慢查询语句的个数，可以通过 show global status like '%Slow_queries%'; 命令查看。

🌟SQL语句执行的很慢原因是什么？

如果SQL语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是redo log日志写满了，要将redo log中的数据同步到磁盘中去。
如果SQL语句一直都很慢，可能是字段上没有索引或者字段有索引但是没用上索引。

如果单表数据量过千万，怎么优化？

1、数据库设计和表创建时，考虑性能问题，比如：单表不要有太多字段，建议在20以内、索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描、选择合适的数据类型、选择合适索引类型等。

2、SQL编写时需要注意，比如：列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大、可通过开启慢查询日志来找出较慢的SQL、避免select *，将需要查找的字段列出来等。

3，存储引擎选择，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表 。

4、分库分表，比如：分库把一个数据库分成多个，建议做个读写分离就行了，真正的做分库也会带来大量的开发成本，得不偿失！分表就是把一张大表，按照如上过程都优化了，还是查询卡死，那就把这个表分成多张表，把一次查询分成多次查询，然后把结果组合返回给用户。分表分为垂直拆分和水平拆分，通常以某个字段做拆分项。比如以id字段拆分为100张表：表名为 tableName_id%100。但：分表需要修改源程序代码，会给开发带来大量工作，极大的增加了开发成本，故：只适合在开发初期就考虑到了大量数据存在，做好了分表处理，不适合应用上线了再做修改，成本太高等。

5、硬件升级，这办法是最简单的，相对的成本也高，老板就不愿意了。

6、数据库升级，比如：把MySQL数据库换成大数据引擎处理数据、换成阿里云POLARDB，POLARDB 是阿里云自研的下一代关系型分布式云原生数据库，100%兼容MySQL，存储容量最高可达 100T，性能最高提升至 MySQL 的 6 倍。

🌟一个500w条数据的表 a，一个300w数据的表 b，通过外键 tid 关联，如何最快的查询出满足条件的第50000到第50200中的这200条数据记录？

方法一：如果 a 表 tid 是自增长，并且是连续的，b表的id为索引。SQL语句如下。

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

方法二：如果 a 表的 tid 不是连续的，那么就需要使用覆盖索引，tid 要么是主键，要么是辅助索引，b 表 id 也需要有索引。SQL语句如下。

select * from b, (select tid from a limit 50000,200) a where b.id = a.tid;

🌟大表数据查询如何进行优化？

数据库设计优化

合理的数据库设计可以极大地提高查询效率。我们在设计大表时，可以考虑拆分表、使用分区表、添加索引等方式来优化表结构。同时也要避免使用大量冗余字段、避免频繁使用join查询等操作。

索引优化

对于大表的查询操作，索引优化是非常重要的一环。可以考虑增加或者修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。同时也要注意定期清理冗余的索引以及对于经常使用的查询语句建立索引。

分区优化

将大表按照某个列分成多个分区表，每个分区表的数据量较小，可以提高查询和更新的性能。分区表还可以帮助在维护表结构的同时，减少锁表时间，提高并发处理能力。

数据清理归档

对于一些历史数据或者无用数据，可以进行定期归档，避免数据过多造成SQL查询效率降低。同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。

缓存优化

对于一些经常被查询的数据，可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据，以减少查询数据库的次数，提高查询效率。

SQL语句优化

在编写SQL查询语句时，要尽可能地简单明了，避免复杂的查询语句，同时也要避免一些不必要的查询操作。对于复杂的查询语句，可以使用Explain执行计划来进行优化。同时也要注意避免使用OR等耗费性能的操作符。

分库分表

如果数据量千万级别，需要考虑分库分表哈。我们为什么要分库分表？

🌟如何优化查询过程中的数据访问？

从减少数据访问方面考虑：

正确使用索引，尽量做到索引覆盖
优化SQL执行计划

从返回更少的数据方面考虑：

数据分页处理
只返回需要的字段

从减少服务器CPU开销方面考虑：

合理使用排序
减少比较的操作
复杂运算在客户端处理

从增加资源方面考虑：

客户端多进程并行访问
数据库并行处理

🌟数据库优化问题

（1）根据服务层面：配置mysql性能优化参数；

（2）从系统层面增强mysql的性能：优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等。

（3）从数据库层面增强性能：优化SQL语句，合理使用字段索引。

（4）从代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。

（5）减少数据库操作次数，尽量使用数据库访问驱动的批处理方法。

（6）不常使用的数据迁移备份，避免每次都在海量数据中去检索。

（7）提升数据库服务器硬件配置，或者搭建数据库集群。

（8）编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤（非法字符串过滤）；

🌟如何优化长难的查询语句?

将一个大的查询分解为多个小的查询
分解关联查询，使缓存的效率更高

🌟如何优化LIMIT分页？

是否遇到过深分页问题，如何解决
SELECT * FROM table LIMIT 5,10; //检索记录行6-15
在LIMIT偏移量较大的时候（offset 越大，扫描页越多，所以导致变慢），查询效率会变低

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。

标签记录法：可以记录每次取出的最大ID，下次查询时可以利用ID进行查询
延迟关联法：利用表的 覆盖索引 来加速分页查询，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。因为利用索引查找有优化算法，且数据块就在查询索引上面，不用再去找相关的数据块，这样节省了很多时间，也就是说，查询的数据就在索引上，不用再经过回表的操作。

标签记录法

就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

假设上一次记录到100000，则SQL可以修改为：

select  id,name,balance FROM account where id > 100000 limit 10;

这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。

延迟关联法

延迟关联法，就是把条件转移到主键索引树，然后减少回表。假设原生SQL是这样的的，其中id是主键，create_time是普通索引

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

使用延迟关联法优化，如下：

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN 
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) 
AS acct2 on acct1.id= acct2.id;

优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

两个语句的内容都非常简单，差别只在 limit 的部分，第一个语句跳过的行数很少，第二个语句跳过的行数很多，结果是两个语句的执行时间差了至少 200 倍。

可以看到跳过的行数大幅度增长时，SQL 语句的执行时间也会快速增长，原因其实比较简单：在处理 limit M,N 的时候，MySQL 会先拿到 M+N 行结果数据，然后再丢弃 M 行数据，展示之后剩下的 N 行数据。所以上图的第二个语句实际上扫描了 800 多万行数据，然后丢弃了 800 万行数据，只展示之后的 1 行结果。

覆盖索引优化：利用 join，先根据主键搜索到需要的数据，再通过主键关联到原来的表输出结果。

id>的形式：可以记录每次取出的最大ID，下次查询时可以利用ID进行查询

MySQL 案例：Limit 分页查询优化 - 腾讯云开发者社区-腾讯云 (tencent.com)

🌟如何优化UNION查询?

如果不需要对结果集进行去重或者排序建议使用UNION ALL，会好一些。

🌟如何优化WHERE子句

不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。
不要在where子句中使用null或空值判断，尽量设置字段为not null。
不要在where子句中使用表达式、参数、函数，会导致存储引擎放弃索引进而全表扫描
尽量使用union all代替or
在where和order by涉及的列建立索引
尽量减少使用in或者not in，会进行全表扫描

🌟如何优化 group by

group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。

group by可能会慢在哪里？因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

如何优化group by呢?

group by 后面的字段加索引
order by null 不用排序
尽量只使用内存临时表
使用SQL_BIG_RESULT

看一遍就理解：group by详解

🌟如何优化order by

大家是否还记得order by查询为什么会慢嘛?

order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点.如果是order by排序,可能会借助磁盘文件排序的话，效率就更慢一点.

如何优化order by的文件排序?

因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化order by语句。
我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化；

看一遍就理解：order by详解

⑦高性能

读写分离

🌟MySQL 有什么调优的方式

读写分离
分库分表

🌟简述数据库读写分离？

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能。

通过主从复制实现，MySQL 主从复制是依赖于 binlog 。另外，常见的一些同步 MySQL 数据到其他数据源的工具（比如 canal）的底层一般也是依赖 binlog 。

一般情况下，我们都会选择一主多从，也就是一台主数据库负责写，其他的从数据库负责读。主库和从库之间会进行数据同步，以保证从库中数据的准确性。这样的架构实现起来比较简单，并且也符合系统的写少读多的特点。

🌟读写分离会带来什么问题？如何解决？

主从同步延迟：主库和从库的数据存在延迟，比如你写完主库之后，主库的数据同步到从库是需要时间的，这个时间差就导致了主库和从库的数据不一致性问题。

解决思路：

1.强制将读请求路由到主库处理。
2.延迟读取。

🌟如何实现读写分离？

1、部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
2、保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
3、系统将写请求交给主数据库处理，读请求交给从数据库处理。

落实到项目本身的话，常用的方式有两种：

1.代理方式

我们可以在应用和数据中间加了一个代理层。应用程序所有的数据请求都交给代理层处理，代理层负责分离读写请求，将它们路由到对应的数据库中。提供类似功能的中间件有 MySQL Router（官方）、MyCat。

2.组件方式

通过引入第三方组件来帮助我们读写请求。如果你要采用这种方式的话，推荐使用 sharding-jdbc 。

🌟主从复制（主从同步）原理是什么？

binlog（二进制日志文件）主要记录了 MySQL 数据库中数据的所有变化(数据库执行的所有 DDL 和 DML 语句)。因此，我们根据主库的 binlog 日志就能够将主库的数据同步到从库中。

主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

主库将数据库中数据的变化写入到 binlog
从库连接主库
从库会创建一个 I/O 线程向主库请求最新的 binlog
主库会创建一个 binlog dump 线程来发送 binlog ，从库中的 I/O 线程负责接收
从库的 I/O 线程将接收的 binlog 写入到中继日志 relay log 中。
从库的 SQL 线程读取中继日志 relay log 同步数据本地（也就是再执行一遍 SQL ）。

主从复制的作用：

高可用和故障转移
负载均衡
数据备份
升级测试

分库分表

我们为什么要分库分表？

🌟什么是分库分表

分库：就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。
- 例子：将数据库中的用户表、订单表和商品表分别单独拆分为用户数据库、订单数据库和商品数据库。
水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上，这样就实现了水平扩展，解决了单表的存储和性能瓶颈的问题。
- 例子：订单表数据量太大，你对订单表进行了水平切分（水平分表），然后将切分后的 2 张订单表分别放在两个不同的数据库。

分表：就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。
- 例子：将用户信息表中的一些列单独抽出来作为一个表。
水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。
- 例子：将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
- 水平拆分只能解决单表数据量大的问题，为了提升性能，我们通常会选择将拆分后的多张表放在不同的数据库中。也就是说，水平分表通常和水平分库同时出现。

🌟什么情况下进行分库分表？

单表的数据达到千万级别以上，数据库读写速度比较缓慢。
数据库中的数据占用的空间越来越大，备份时间越来越长。
应用的并发量太大。

🌟常见的分片算法有哪些？

分片算法主要解决了数据被水平分片之后，数据究竟该存放在哪个表的问题。

哈希分片 ：求指定 key（比如 id）的哈希，然后根据哈希值确定数据应被放置在哪个表中。哈希分片比较适合随机读写的场景，不太适合经常需要范围查询的场景。
范围分片 ：按照特性的范围区间（比如时间区间、ID区间）来分配数据，比如将 id 为 1~299999 的记录分到第一个库， 300000~599999 的分到第二个库。范围分片适合需要经常进行范围查找的场景，不太适合随机读写的场景（数据未被分散，容易出现热点数据的问题）。
地理位置分片 ：很多 NewSQL 数据库都支持地理位置分片算法，也就是根据地理位置（如城市、地域）来分配数据。
融合算法 ：灵活组合多种分片算法，比如将哈希分片和范围分片组合。

🌟分库分表会带来什么问题呢？

事务问题 ：同一个数据库中的表分布在了不同的数据库中，如果单个操作涉及到多个数据库，那么数据库自带的事务就无法满足我们的要求了。
跨库跨表的 join 操作 ：同一个数据库中的表分布在了不同的数据库中，导致无法使用 join 操作。这样就导致我们需要手动进行数据的封装，比如你在一个数据库中查询到一个数据之后，再根据这个数据去另外一个数据库中找对应的数据。
分布式 id ：分库之后，数据遍布在不同服务器上的数据库，数据库的自增主键已经没办法满足生成的主键唯一了。我们如何为不同的数据节点生成全局唯一主键呢？这个时候，我们就需要为我们的系统引入分布式 id 了。
额外的数据管理负担和数据运算压力：最为常见的是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序来解决，但必然会引起额外的逻辑运算。

ShardingSphere 绝对可以说是当前分库分表的首选！ShardingSphere 的功能完善，除了支持读写分离和分库分表，还提供分布式事务、数据库治理等功能。

🌟分库分表后，数据怎么迁移呢？

分库分表之后，我们如何将老库（单库单表）的数据迁移到新库（分库分表后的数据库系统）呢？

常用的方案就是停机迁移，写个脚本将老库的数据写到新库中。

比如你在凌晨 2 点，系统使用的人数非常少的时候，挂一个公告说系统要维护升级预计 1 小时。然后，你写一个脚本将老库的数据都同步到新库中。

如果你不想停机迁移数据的话，也可以考虑双写方案。双写方案是针对那种不能停机迁移的场景，实现起来要稍微麻烦一些。具体原理是这样的：

我们对老库的更新操作（增删改），同时也要写入新库（双写）。如果操作的数据不存在于新库的话，需要插入到新库中。这样就能保证，咱们新库里的数据是最新的。

在迁移过程，双写只会让被更新操作过的老库中的数据同步到新库，我们还需要自己写脚本将老库中的数据和新库的数据做比对。如果新库中没有，那咱们就把数据插入到新库。如果新库有，旧库没有，就把新库对应的数据删除（冗余数据清理）。重复上一步的操作，直到老库和新库的数据一致为止。

想要在项目中实施双写还是比较麻烦的，很容易会出现问题。我们可以借助数据库同步工具 Canal 做增量数据迁移（还是依赖 binlog，开发和维护成本较低）。

🌟为什么不使用自增ID

id的规律性太明显
受单表数据量的限制，分布式下自增id重复

场景分析一：如果我们的id具有太明显的规则，用户或者说商业对手很容易猜测出来我们的一些敏感信息，比如商城在一天时间内，卖出了多少单，这明显不合适。

场景分析二：随着我们商城规模越来越大，mysql的单表的容量不宜超过500W，数据量过大之后，我们要进行拆库拆表，但拆分表了之后，他们从逻辑上讲他们是同一张表，所以他们的id是不能一样的，于是乎我们需要保证id的唯一性。

🌟分布式id号的要求有哪些呢？

全局唯一性：不能出现重复的ID号，既然是唯一标识，这是最基本的要求。
趋势递增：在MySQL InnoDB引擎中使用的是聚集索引，由于多数RDBMS使用B-tree的数据结构来存储索引数据，在主键的选择上面我们应该尽量使用有序的主键保证写入性能。
单调递增：保证下一个ID一定大于上一个ID，例如事务版本号、IM增量消息、排序等特殊需求。
信息安全：如果ID是连续的，恶意用户的扒取工作就非常容易做了，直接按照顺序下载指定URL即可；如果是订单号就更危险，可以直接知道我们一天的单量。所以在一些应用场景下，会需要ID无规则、不规则。

🌟简述分布式id生成方法

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID

UUID：优点：本地生成ID，不需要远程调用；全局唯一不重复。缺点：占用空间大，不适合作为索引。
Redis生成ID：优点：不依赖数据库，性能比较好。缺点：引入新的组件会使得系统复杂度增加
Twitter的snowflake算法：利用时间戳，机器id，当前数据库自增id进行拼接，生成的新的分布式id。一个64位的long型的ID，其中有1bit是符号位，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。
- 优点：id趋势自增、灵活
- 缺点：强依赖机器时钟，如果机器上时钟回拨，会导致发号重复或者服务会处于不可用状态。
美团的Leaf分布式ID生成系统，Leaf——美团点评分布式ID生成系统 - 美团技术团队 (meituan.com)