关于mysql:2022年MySQL最新面试题

前言

最近整顿一份对于 MySQL 常见面试题的，也会依据本人的教训，标注一些呈现的概率，最高 5 颗★呈现的概率最高。比方这样:

百万级别或以上的数据如何删除
呈现概率: ★★★

一般来讲在面试当中，对于数据库相干的面试题频率呈现比拟高的几个关键词是 SQL 优化、索引、存储引擎、事务、死锁、乐观锁、乐观锁、关系型数据库和非关系数据库比照等等。把这几个点问完根本也差不多 10~20 分钟了(个别一轮面试 1 小时左右), 根本这些能够让面试官对你的数据库常识有肯定的理解了。

如果你线上运维教训, 个别也会问一些比方数据库扩容, 如何给大表加索引, 如何在业务顶峰是给一个大表增加字段等。

也欢送关注我的公众号: 散步 coding。一起交换, 在 coding 的世界里散步。

心愿这篇文章能够帮忙大家, 也心愿大家都能找到找到的好工作。

也能够通过我的博客地址: 在线浏览

一、数据库基础知识

0、概要

• 1、平时 MySQL 次要用哪个版本
• 2、数据库三大范式是什么
• 3、MySQL 无关权限的表都有哪几个
• 4、MySQL 的 binlog 有有几种录入格局？别离有什么区别？
• 5、平时用到哪些关系型数据库和非关系数据库, 能够谈谈你对它们的了解吗?
• 6、能够简略说说你对 MySQL 的逻辑架构理解吗？
• 7、理解 MySQL 中的 MVCC 是什么?
• 8、PostgreSQL 绝对于 MySQL 的劣势
• 9、PostgreSQL 和 MySQL 的一些区别

1、平时 MySQL 次要用哪个版本

呈现概率: ★★★★

能够说说本人用的 MySQL 版本, 比方我本人用的版本是 5.7 版本，而后能够也简略聊聊这个版本的一些特点:

1)、安全性

在 MySQL 5.7 中，有不少安全性相干的改良。包含：

MySQL 数据库初始化实现当前，会产生一个 root@localhost 用户，从 MySQL 5.7 开始，root 用户的明码不再是空，而是随机产生一个明码，这也导致了用户装置 5.7 时发现的与 5.6 版本比拟大的一个不同点。

MySQL 官网曾经删除了 test 数据库，默认装置完后是没有 test 数据库的，就算用户创立了 test 库，也能够对 test 库进行权限管制了
MySQL 5.7 版本提供了更为简略 SSL 平安拜访配置，并且默认连贯就采纳 SSL 的加密形式。

能够为用户设置明码过期策略，肯定工夫当前，强制用户批改明码。

2)、灵活性

MySQL 5.7 的两个全新的性能，即 JSON 和 generate column

CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1":"value1","key2":"value2"}');

3)、可用性

在线设置复制的过滤规定，不再须要重启 MySQL，只须要进行 SQL thread，批改实现当前，启动 SQL thread

这个次要大抵谈谈你本人的了解，也能够依据本人的了解多延展一些，进步面试的加分。

2、数据库三大范式是什么

呈现概率: ★★★

第一范式：每个列都不能够再拆分。

第二范式：在第一范式的根底上，非主键列齐全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的根底上，非主键列只依赖于主键，不依赖于其余非主键。

在设计数据库构造的时候，要尽量恪守三范式，如果不恪守，必须有足够的理由。比方性能。事实上咱们常常会为了性能而斗争数据库的设计。

3、MySQL 无关权限的表都有哪几个

呈现概率: ★★★

MySQL 服务器通过权限表来管制用户对数据库的拜访，权限表寄存在 mysql 数据库里，由 mysql_install_db 脚本初始化。这些权限表别离 user，db，table_priv，columns_priv 和host。上面别离介绍一下这些表的构造和内容：

user权限表：记录容许连贯到服务器的用户帐号信息，外面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合 db 权限表对给定主机上数据库级操作权限作更粗疏的管制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

4、MySQL 的 binlog 有有几种录入格局？别离有什么区别？

呈现概率: ★★★

MySQL 的 binlog 有三种格局: statement，row 和 mixed。

1)、statement 模式下，每一条会批改数据的 sql 都会记录在 binlog 中。不须要记录每一行的变动，缩小了 binlog 日志量，节约了 IO，进步性能。因为 sql 的执行是有上下文的，因而在保留的时候须要保留相干的信息，同时还有一些应用了函数之类的语句无奈被记录复制。

2)、row 级别下，不记录 sql 语句上下文相干信息，仅保留哪条记录被批改。记录单元为每一行的改变，根本是能够全部记下来然而因为很多操作，会导致大量行的改变(比方 alter table)，因而这种模式的文件保留的信息太多，日志量太大。

3)、mixed，一种折中的计划，一般操作应用 statement 记录，当无奈应用 statement 的时候应用 row。

此外，新版的 MySQL 中对 row 级别也做了一些优化，当表构造发生变化的时候，会记录语句而不是逐行记录。

平时用到哪些关系型数据库和非关系数据库, 能够谈谈你对它们的了解吗?

呈现概率: ★★★★★

次要讲讲你用过的关系型数据库比方 MySQL, 非关系数据库 (NoSql 数据库) 比方 Redis, MongoDB 等等。

1)、事务方面

关系型数据库的最大特点就是事务的一致性, 所以对于订单模型 对一致性要求比拟高的还是倡议用 MySQL。

2)、关系数据库的另一个特点就是其具备固定的表构造

其实在业务模型中, 表构造固定反而是一件坏事, 没有束缚的模型 更容易出问题。

3)、简单 SQL，特地是多表关联查问

NoSql 是不反对 JOIN 这种查问的。

4)、索引形式

关系型数据库：B 树、哈希等
NoSql：键值索引

5)、并发反对

关系型数据库：通过事务和锁来反对并发，高并发状况下，执行效率较低。
NoSql：突破了传统关系型数据库范式的束缚和事务一致性，因而并发性能高。

当然本人也能够多延长看一下，毕竟这个面试呈现的概率还是蛮高的。

5、能够简略说说你对 MySQL 的逻辑架构理解吗？

呈现概率: ★★★

第一层是服务器层，次要提供连贯解决、受权认证、平安等性能。

第二层实现了 MySQL 外围服务性能，包含查问解析、剖析、优化、缓存以及日期和工夫等所有内置函数，所有跨存储引擎的性能都在这一层实现，例如存储过程、触发器、视图等。

第三层是存储引擎层，存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎通信，这些接口屏蔽了不同存储引擎的差别，使得差别对下层查问过程通明。除了会解析外键定义的 InnoDB 外，存储引擎不会解析 SQL，不同存储引擎之间也不会互相通信，只是简略响应下层服务器申请。

6、理解 MySQL 中的 MVCC 是什么?

呈现概率: ★★★

MVCC 是多版本并发管制，在很多状况下防止加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。

InnoDB 的 MVCC 通过在每行记录前面保留两个暗藏的列来实现，这两个列一个保留了行的创立工夫，一个保留行的过期工夫间。不过存储的不是理论的工夫值而是零碎版本号，每开始一个新的事务零碎版本号都会主动递增，事务开始时刻的零碎版本号会作为事务的版本号，用来和查问到的每行记录的版本号进行比拟。

MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是合乎以后事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

7、PostgreSQL 绝对于 MySQL 的劣势

呈现概率: ★★★★

• 在 SQL 的规范实现上要比 MySQL 欠缺，而且性能实现比拟谨严；
• 存储过程的性能反对要比 MySQL 好，具备本地缓存执行打算的能力；
• 对表连贯反对较完整，优化器的性能较完整，反对的索引类型很多，简单查问能力较强；
• PG 主表采纳堆表寄存，MySQL 采纳索引组织表，可能反对比 MySQL 更大的数据量。
• PG 的主备复制属于物理复制，绝对于 MySQL 基于 binlog 的逻辑复制，数据的一致性更加牢靠，复制性能更高，对主机性能的影响也更小。

8、PostgreSQL 和 MySQL 的一些区别

呈现概率: ★★★★

这个其实呈现的概率还比拟高，本人能够说几点就行了

MySQL 不反对天文数据类型。

从 9.2 开始，PG 反对 json 数据类型。绝对于 MySQL 来说，PG 对 json 的反对比拟先进。他有一些 json 指定的操作符和函数，是的搜寻 json 文本十分高效。9.4 开始，能够以二进制的格局存储 json 数据，反对在该列上进行全文索引（GIN 索引），从而在 json 文档中进行疾速搜寻。

从 5.7 开始，MySQL 反对 json 数据类型，比 PG 晚。也能够在 json 列上建设索引。然而对 json 相干的函数的反对比拟无限。不反对在 json 列上全文索引。因为 MySQL 对 SQL 反对的限度，在存储和解决 json 数据方面，MySQL 不是一个很好的抉择。

二、索引

0、概要

• 1、索引有哪些应用场景（重点）
• 2、索引的数据结构（b 树，hash）
• 3、创立索引的准则是什么?（重中之重）
• 4、应用索引查问肯定能进步查问的性能吗？为什么
• 5、索引有哪些优缺点？
• 6、讲一讲聚簇索引与非聚簇索引？
• 7、百万级别或以上的数据如何删除
• 8、什么是最左前缀准则？什么是最左匹配准则
• 9、数据库为什么应用 B + 树而不是 B 树
• 10、非聚簇索引肯定会回表查问吗？
• 11、有哪些状况, 索引会生效, 能够简略说说吗?

1、索引有哪些应用场景

呈现概率: ★★★★★

1)、应该创立索引的场景

• 主键应该创立主键索引。
• 频繁作为查问条件的字段应该创立索引。
• 查问中须要与其余表进行关联的字段应该创立索引。
• 须要排序的字段应该创立索引。
• 须要统计或分组的字段应该创立索引。
• 优先思考创立复合索引。

2)、不应创立索引的场景

• 数据记录较少的表。
• 常常须要增删改操作的字段。
• 数据记录反复较多且散布均匀的字段（如性别、状态等）。

索引的选择性是指索引列中不同值的数目与表中记录总数的比。
索引的选择性越靠近于 1，创立索引的价值就越高。反之就越低。

2、索引的数据结构（B+ 树，hash）

呈现概率: ★★★★★

从存储构造上来划分：BTree 索引（B-Tree 或 B +Tree 索引），Hash 索引，full-index 全文索引，R-Tree 索引。这里所形容的是索引存储时保留的模式，MySQL 默认采纳的 B +Tree，这里次要讲讲 B + 树的特点:

1. 非叶子节点不存储 data，只存储索引(冗余)，能够放更多的索引
2. 叶子节点蕴含所有索引字段
3. 叶子节点用指针连贯，进步区间拜访的性能（疾速定位范畴查问，例如查问大于 20，第一次 io 从根节点查问三次定位到 20，而后通过前面的指针查问大于 20 的数据，就不必再从根节点的从新再查问，进步性能，叶子节点开始完结节点也是用指针连贯串起来的）

3、创立索引的准则是什么?

呈现概率: ★★★★

1)、抉择唯一性索引

2)、为常常须要排序、分组和联合操作的字段建设索引

3)、为常作为查问条件的字段建设索引

4)、限度索引的数目

索引的数目不是越多越好。每个索引都须要占用磁盘空间，索引越多，须要的磁盘空间就越大。批改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5)、尽量应用数据量少的索引

如果索引的值很长，那么查问的速度会受到影响。例如，对一个 CHAR(100)类型的字段进行全文检索须要的工夫必定要比对 CHAR(10)类型的字段须要的工夫要多。

6)、尽量应用前缀来索引

如果索引字段的值很长，最好应用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的后面的若干个字符，这样能够进步检索速度。

7)、最左前缀匹配准则

8)、查问时应用计算，会导致索引生效

4、应用索引查问肯定能进步查问的性能吗？为什么

呈现概率: ★★★★

不是所有的查问应用查问都能进步性能，比方上面几个案例

• 像 like % xxx%、不满足最左匹配准则的状况下并不能应用到建好的索引
• MySQL 在能够应用多个索引的状况下，查问优化器会依据查问范畴的数据量估算索引代价，最坏的是估算结束后，发现这些索引的字段区分度不高，还不如扫全表，于是 Mysql 扫全表了
• 如果索引的列比须要查问的列少，Mysql 会通过聚簇索引回表查问其余字段
• 如果索引的字段很大，每个页能存的条目就很少，读取时 IO 会耗费更多，页 Buffer 轮替的更快

5、索引有哪些优缺点？

呈现概率: ★★★

1)、索引的长处

能够大大放慢数据的检索速度，这也是创立索引的最次要的起因。
通过应用索引，能够在查问的过程中，应用优化暗藏器，进步零碎的性能。

2)、索引的毛病

工夫方面：创立索引和保护索引要消耗工夫，具体地，当对表中的数据进行减少、删除和批改的时候，索引也要动静的保护，会升高增 / 改 / 删的执行效率；
空间方面：索引须要占物理空间。

6、讲一讲聚簇索引与非聚簇索引？

呈现概率: ★★★★

在 InnoDB 里，索引 B +Tree 的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，行将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引 B +Tree 的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

非汇集索引与汇集索引的区别在于非汇集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）

对于 InnoDB 来说，想要查找数据咱们还须要依据主键再去汇集索引中进行查找，这个再依据汇集索引查找数据的过程，咱们称为回表。第一次索引个别是程序 IO，回表的操作属于随机 IO。须要回表的次数越多，即随机 IO 次数越多，咱们就越偏向于应用全表扫描。

通常状况下，主键索引（聚簇索引）查问只会查一次，而非主键索引（非聚簇索引）须要回表查问屡次。当然，如果是笼罩索引的话，查一次即可

留神：MyISAM 无论主键索引还是二级索引都是非聚簇索引，而 InnoDB 的主键索引是聚簇索引，二级索引是非聚簇索引。咱们本人建的索引根本都是非聚簇索引。

7、百万级别或以上的数据如何删除

呈现概率: ★★★

办法一:

索引是独自的文件，增删改时，当存在索引，会耗费额定 io。删除速度和索引数量成正比

• 删除索引
• 删除须要删除的数据
• 从新建设索引

这个办法有一个很显著的毛病, 就是在正式环境这个表如果拜访频率比拟高的话，删除索引后有大量的 SQL 查问会导致数据库 IO 和 CPU 特地高

办法二:

之前我在正式环境的做法是 将删除工作拆分为一次删除 1w 条，而后把删除工作从新压入的异步工作队列外面。

8、什么是最左前缀准则？什么是最左匹配准则

呈现概率: ★★★★

在 mysql 建设联结索引时会遵循最左前缀匹配的准则，即最左优先，在检索数据时从联结索引的最右边开始匹配，示例：
对列 col1、列 col2 和列 col3 建一个联结索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联结索引 test_col1_col2_col3 理论建设了 (col1)、(col1,col2)、(col,col2,col3) 三个索引。

SELECT * FROM test WHERE col1=“1”AND clo2=“2”AND clo4=“4”

下面这个查问语句执行时会按照最左前缀匹配准则，检索时会应用索引 (col1,col2) 进行数据匹配。

9、数据库为什么应用 B + 树而不是 B 树

呈现概率: ★★★

B 树和 B + 树的区别次要有两点：

• 在 B 树中，你能够将键和值寄存在外部节点和叶子节点，但在 B + 树中，外部节点都是键，没有值。叶子节点同时寄存键和值
• B+ 树的叶子节点有一条链相连，而 B + 树的叶子节点各自独立。

应用 B + 树的益处

因为 B + 树的外部节点只寄存键，不寄存值，因而，一次读取，能够在内存页中获取更多的键，有利于更快地放大查找范畴。
B+ 树的叶节点由一条链相连，因而，当须要进行一次全数据遍历的时候，B+ 树只须要应用 O(logN)工夫找到最小的一个节点，而后通过链进行 O(N)的程序遍历即可。而 B 树则须要对树的每一层进行遍历，这会须要更多的内存置换次数，因而也就须要破费更多的工夫。

数据库的数据读取都是须要进行代价微小的磁盘 IO 操作，因而，更快地放大范畴和更少的读取次数是数据库须要关注的重点。而 B + 树在这些点上比 B 树做的更好。这就是为什么数据库要选用 B + 树作为底层实现。

10、非聚簇索引肯定会回表查问吗？

呈现概率: ★★★

不肯定，这波及到查问语句所要求的字段是否全副命中了索引，如果全副命中了索引，那么就不用再进行回表查问。一个索引蕴含（笼罩）所有须要查问字段的值，被称之为 ” 笼罩索引 ”。

举个简略的例子，假如咱们在学生表的问题上建设了索引，那么当进行 select score from student where score > 90 的查问时，在索引的叶子节点上，曾经蕴含了 score 信息，不会再次进行回表查问。

三、存储引擎

0、概要

• 1、能够简略谈谈 MySQL 存储引擎 MyISAM 与 InnoDB 区别
• 2、MyISAM 索引与 InnoDB 索引的区别？
• 3、InnoDB 引擎的 4 大个性

1、能够简略谈谈 MySQL 存储引擎 MyISAM 与 InnoDB 区别

呈现概率: ★★★★

1)、对于 InnoDB

InnoDB 是 MySQL 的默认事务型引擎，用来解决大量短期事务。InnoDB 的性能和主动解体复原个性使得它在非事务型存储需要中也很风行，除非有特地起因否则应该优先思考 InnoDB。

InnoDB 的数据存储在表空间中，表空间由一系列数据文件组成。MySQL4.1 后 InnoDB 能够将每个表的数据和索引放在独自的文件中。

InnoDB 采纳 MVCC 来反对高并发，并且实现了四个规范的隔离级别。其默认级别是 REPEATABLE READ，并通过间隙锁策略避免幻读，间隙锁使 InnoDB 不仅仅锁定查问波及的行，还会对索引中的间隙进行锁定避免幻行的插入。

InnoDB 表是基于聚簇索引建设的，InnoDB 的索引构造和其余存储引擎有很大不同，聚簇索引对主键查问有很高的性能，不过它的二级索引中必须蕴含主键列，所以如果主键很大的话其余所有索引都会很大，因而如果表上索引较多的话主键该当尽可能小。

InnoDB 的存储格局是平 * 立的，能够将数据和索引文件从一个平台复制到另一个平台。

InnoDB 外部做了很多优化，包含从磁盘读取数据时采纳的可预测性预读，可能主动在内存中创立减速读操作的自适应哈希索引，以及可能减速插入操作的插入缓冲区等。

2)、对于 MyISAM

MySQL5.1 及之前，MyISAM 是默认存储引擎，MyISAM 提供了大量的个性，包含全文索引、压缩、空间函数等，但不反对事务和行锁，最大的缺点就是解体后无奈平安复原。对于只读的数据或者表比拟小、能够忍耐修复操作的状况依然能够应用 MyISAM。

MyISAM 将表存储在数据文件和索引文件中，别离以 .MYD 和 .MYI 作为扩展名。MyISAM 表能够蕴含动静或者动态行，MySQL 会依据表的定义决定行格局。MyISAM 表能够存储的行记录数个别受限于可用磁盘空间或者操作系统中单个文件的最大尺寸。

MyISAM 对整张表进行加锁，读取时会对须要读到的所有表加共享锁，写入时则对表加排它锁。然而在表有读取查问的同时，也反对并发往表中插入新的记录。

对于 MyISAM 表，MySQL 能够手动或主动执行检查和修复操作，这里的修复和事务复原以及解体复原的概念不同。执行表的修复可能导致一些数据失落，而且修复操作很慢。

对于 MyISAM 表，即便是 BLOB 和 TEXT 等长字段，也能够基于其前 500 个字符创立索引。MyISAM 也反对全文索引，这是一种基于分词创立的索引，能够反对简单的查问。

MyISAM 设计简略，数据以严密格局存储，所以在某些场景下性能很好。MyISAM 最典型的性能问题还是表锁问题，如果所有的查问长期处于 Locked 状态，那么起因毫无疑问就是表锁。

2、MyISAM 索引与 InnoDB 索引的区别？

呈现概率: ★★★★

MyISM 和 InnoDB 索引都是由 B + 树实现的，但在索引治理数据形式上却有所不同。

1)、InnoDB 是汇集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据 = 整个表数据文件，通过主键索引到整个记录，必须要有主键，通过主键索引效率很高。然而辅助索引须要两次查问，因为辅助索引是以建索引的字段为关键字索引到主键，所以须要两次，先查问到主键，而后再通过主键查问到数据。

主键索引：以主键索引到整条记录

辅助索引：以另一字段索引到主键

2)、MyISAM 是非汇集索引，也是应用 B +Tree 作为索引构造，索引和数据文件是拆散的，索引保留的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB 的 B + 树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而 MyISAM 的 B + 树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

主键索引：以关键字索引到记录的地址

辅助索引：以某字段索引到记录地址

3、InnoDB 引擎的 4 大个性

呈现概率: ★★★

1)、插入缓冲（Insert Buffer/Change Buffer）

插入缓存之前版本叫 insert buffer，现版本 change buffer，次要晋升插入性能，change buffer 是 insert buffer 的增强，insert buffer 只针对 insert 无效，change buffering 对 insert、delete、update(delete+insert)、purge 都无效。

2)、双写机制（Double Write）

在 InnoDB 将 BP 中的 Dirty Page 刷（flush）到磁盘上时，首先会将（memcpy 函数）Page 刷到 InnoDB tablespace 的一个区域中，咱们称该区域为 Double write Buffer（大小为 2MB，每次写入 1MB，128 个页，每个页 16k, 其中 120 个页为后盾线程的批量刷 Dirty Page，还有 8 个也是为了前台起的 sigle Page Flash 线程，用户能够被动申请，并且能迅速的提供空余的空间）。在向 Double write Buffer 写入胜利后，第二步、再将数据别离刷到一个共享空间和真正应该存在的地位。

3)、自适应哈希索引（Adaptive Hash Index，AHI）

哈希算法是一种十分快的查找办法，在个别状况（没有产生 hash 抵触）下这种查找的工夫复杂度为 O(1)。InnoDB 存储引擎会监控对表上辅助索引页的查问。如果察看到建设 hash 索引能够晋升性能，就会在缓冲池建设 hash 索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。

4)、预读（Read Ahead）

预读（read-ahead)操作是一种 IO 操作，用于异步将磁盘的页读取到 buffer pool 中，意料这些页会马上被读取到。预读申请的所有页集中在一个范畴内。

四、MySQL 事务

0、概要

• 什么是数据库事务？
• 事物的四大个性 (ACID) 介绍一下?
• 什么是脏读？幻读？不可反复读？
• 什么是事务的隔离级别？MySQL 的默认隔离级别是什么？
• 隔离级别的实现原理
• 事务延长点: 分布式事务

1、什么是数据库事务？

呈现概率: ★★★★★

简略来说：数据库事务能够保障多个对数据库的操作（也就是 SQL 语句）形成一个逻辑上的整体。形成这个逻辑上的整体的这些数据库操作遵循：要么全副执行胜利, 要么全副不执行。

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

事务最经典也常常被拿出来说例子就是转账了。如果小明要给小红转账 1000 元，这个转账会波及到两个要害操作就是：

将小明的余额缩小 1000 元
将小红的余额减少 1000 元。
事务会把这两个操作就能够看成逻辑上的一个整体，这个整体蕴含的操作要么都胜利，要么都要失败。

这样就不会呈现小明余额缩小而小红的余额却并没有减少的状况。

2、事物的四大个性 (ACID) 介绍一下?

呈现概率: ★★★★

ACID，指数据库事务正确执行的四个基本要素的缩写。蕴含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个反对事务（Transaction）的数据库，必须要具备这四种个性，否则在事务过程（Transaction processing）当中无奈保证数据的正确性，交易过程极可能达不到交易方的要求。

3、什么是脏读？幻读？不可反复读？

呈现概率: ★★★★

1、脏读

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

当一个事务正在屡次批改某个数据，而在这个事务中这屡次的批改都还未提交，这时一个并发的事务来拜访该数据，就会造成两个事务失去的数据不统一。例如：用户 A 向用户 B 转账 100 元，对应 SQL 命令如下

update account set money=money+100 where name=’B’; (此时 A 告诉 B)
update account set money=money - 100 where name=’A’;

当只执行第一条 SQL 时，A 告诉 B 查看账户，B 发现的确钱已到账（此时即产生了脏读），而之后无论第二条 SQL 是否执行，只有该事务不提交，则所有操作都将回滚，那么当 B 当前再次查看账户时就会发现钱其实并没有转。

2、不可反复读
  
不可反复读是指在对于数据库中的某个数据，一个事务范畴内屡次查问却返回了不同的数据值，这是因为在查问距离，被另一个事务批改并提交了。
例如事务 T1 在读取某一数据，而事务 T2 立马批改了这个数据并且提交事务给数据库，事务 T1 再次读取该数据就失去了不同的后果，产生了不可反复读。

不可反复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可反复读则是读取了前一事务提交的数据。
在某些状况下，不可反复读并不是问题，比方咱们屡次查问某个数据当然以最初查问失去的后果为主。但在另一些状况下就有可能产生问题，例如对于同一个数据 A 和 B 顺次查问就可能不同，A 和 B 就可能打起来了……

3、虚读(幻读)

幻读是事务非独立执行时产生的一种景象。例如事务 T1 对一个表中所有的行的某个数据项做了从“1”批改为“2”的操作，这时事务 T2 又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务 T1 的用户如果再查看刚刚批改的数据，会发现还有一行没有批改，其实这行是从事务 T2 中增加的，就如同产生幻觉一样，这就是产生了幻读。

幻读和不可反复读都是读取了另一条曾经提交的事务（这点就脏读不同），所不同的是不可反复读查问的都是同一个数据项，而幻读针对的是一批数据整体（比方数据的个数）。

SQL 规范定义了 4 类隔离级别，包含了一些具体规定，用来限定事务内外的哪些扭转是可见的，哪些是不可见的。低级别的隔离级个别反对更高的并发解决，并领有更低的零碎开销。

4、什么是事务的隔离级别？MySQL 的默认隔离级别是什么？

呈现概率: ★★★★

MySQL 的事务隔离级别一共有四个，别离是读未提交、读已提交、可反复读以及可串行化。

MySQL 的隔离级别的作用就是让事务之间相互隔离，互不影响，这样能够保障事务的一致性。

隔离级别比拟：可串行化 > 可反复读 > 读已提交 > 读未提交

隔离级别对性能的影响比拟：可串行化 > 可反复读 > 读已提交 > 读未提交

由此看出，隔离级别越高，所须要耗费的 MySQL 性能越大（如事务并发严重性），为了均衡二者，个别倡议设置的隔离级别为可反复读，MySQL 默认的隔离级别也是可反复读。

5、隔离级别的实现原理

呈现概率: ★★★

这里应用 MySQL 的默认隔离级别（可反复读）来进行阐明 隔离级别的实现原理。

每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志 undo log）。同一条记录在零碎中能够存在多个版本，这就是数据库的多版本并发管制（MVCC）。即通过回滚（rollback 操作），能够回到前一个状态的值。

假如一个值从 1 被按程序改成了 2、3、4，在回滚日志外面就会有相似上面的记录。

以后值是 4，然而在查问这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 外面，这一个记录的值别离是 1、2、4，同一条记录在零碎中能够存在多个版本，就是数据库的多版本并发管制（MVCC）。对于 read-view A，要失去 1，就必须将以后值顺次执行图中所有的回滚操作失去。

同时你会发现，即便当初有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会抵触的。

发问：回滚操作日志（undo log）什么时候删除？

MySQL 会判断当没有事务须要用到这些回滚日志的时候，回滚日志会被删除。

发问：什么时候不须要了？

当零碎里么有比这个回滚日志更早的 read-view 的时候。

6、事务延长点: 什么是分布式事务，有哪些解决方案?

呈现概率: ★★★

这个次要看面试官的爱好，如果用的是微服务架构，平时开发中会遇到蛮多案例的，不过置信很多人没有思考过这个的解决方案。

这里介绍一个分布式事务典型场景: 银行跨行转账业务是一个典型分布式事务场景，假如 A 须要跨行转账给 B，那么就波及两个银行的数据，无奈通过一个数据库的本地事务保障转账的正确性，只可能通过分布式事务来解决。

将服务拆分为微服务时，遇见相似须要分布式事务的场景十分多，尽管微服务最佳实际倡议尽量躲避分布式事务，然而在很多业务场景，分布式事务是一个绕不开的技术问题。

分布式事务模式常见的有 XA、TCC、SAGA、可靠消息。

1)、两阶段提交 /XA

XA 是由 X /Open 组织提出的分布式事务的标准，XA 标准次要定义了 (全局) 事务管理器 (TM) 和(部分)资源管理器 (RM) 之间的接口。本地的数据库如 mysql 在 XA 中表演的是 RM 角色

XA 一共分为两阶段：

第一阶段（prepare）：即所有的参与者 RM 筹备执行事务并锁住须要的资源。参与者 ready 时，向 TM 报告已准备就绪。

第二阶段 (commit/rollback)：当事务管理者 (TM) 确认所有参与者 (RM) 都 ready 后，向所有参与者发送 commit 命令。

目前支流的数据库根本都反对 XA 事务，包含 mysql、oracle、sqlserver、postgre

2)、TCC 事务计划

TCC 计划其实是 XA 提交的一种改良。其将整个业务逻辑的每个分支显式的分成了 Try、Confirm、Cancel 三个操作。Try 局部实现业务的筹备工作，confirm 局部实现业务的提交，cancel 局部实现事务的回滚。

3)、SAGA 事务计划

Saga 和 TCC 一样，也是最终一致性事务、柔性事务。Saga 的实质就是把一个长事务分隔成一个个小的事务，每个事务都蕴含一个执行模块和弥补模块。
Saga 没有 try，间接提交事务，可能呈现脏读的状况，在某些对一致性要求较高的场景下，是不可承受的。

在启动一个 Saga 事务时，事务管理器会通知第一个 Saga 参与者，也就是子事务，去执行本地事务。事务实现之后 Saga 的会依照执行顺序调用 Saga 的下一个参加的子事务。这个过程会始终继续到 Saga 事务执行结束。

如果在执行子事务的过程中遇到子事务对应的本地事务失败，则 Saga 会依照相同的程序执行弥补事务。

4)、可靠消息

音讯一致性计划是通过消息中间件保障上下游利用数据操作的一致性。基本思路是将本地操作和发送音讯放在一个本地事务中，保障本地操作和音讯发送要么两者都胜利或者都失败。上游利用向音讯零碎订阅该音讯，收到音讯后执行相应操作。

RocketMQ 提供了典型的可靠消息接口，能够参考

五、MySQL 数据库读写锁

0、概要

• 1、谈一谈 MySQL 的读写锁
• 2、隔离级别与锁的关系
• 3、依照锁的粒度分数据库锁有哪些？锁机制与 InnoDB 锁算法
• 4、从锁的类别上分 MySQL 都有哪些锁呢？像下面那样子进行锁定岂不是有点妨碍并发效率了
• 5、MySQL 中 InnoDB 引擎的行锁是怎么实现的？
• 6、InnoDB 存储引擎的锁的算法有三种
• 7、什么是死锁？怎么解决？
• 8、数据库的乐观锁和乐观锁是什么？怎么实现的？

1、谈一谈 MySQL 的读写锁

呈现概率: ★★★★★

在解决并发读或写时，能够通过实现一个由两种类型组成的锁零碎来解决问题。这两种类型的锁通常被称为共享锁和排它锁，也叫读锁和写锁。读锁是共享的，互相不阻塞，多个客户在同一时刻能够同时读取同一个资源而不互相烦扰。写锁则是排他的，也就是说一个写锁会阻塞其余的写锁和读锁，确保在给定工夫内只有一个用户能执行写入并避免其余用户读取正在写入的同一资源。

在理论的数据库系统中，每时每刻都在产生锁定，当某个用户在批改某一部分数据时，MySQL 会通过锁定避免其余用户读取同一数据。写锁比读锁有更高的优先级，一个写锁申请可能会被插入到读锁队列的后面，然而读锁不能插入到写锁后面。

2、隔离级别与锁的关系

呈现概率: ★★★

在 Read Uncommitted 级别下，读取数据不须要加共享锁，这样就不会跟被批改的数据上的排他锁抵触

在 Read Committed 级别下，读操作须要加共享锁，然而在语句执行完当前开释共享锁；

在 Repeatable Read 级别下，读操作须要加共享锁，然而在事务提交之前并不开释共享锁，也就是必须期待事务执行结束当前才开释共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范畴的键，并始终持有锁，直到事务实现。

3、依照锁的粒度分数据库锁有哪些？锁机制与 InnoDB 锁算法

呈现概率: ★★★

在关系型数据库中，能够依照锁的粒度把数据库锁分为行级锁 (INNODB 引擎)、表级锁(MYISAM 引擎) 和页级锁(BDB 引擎)。

MyISAM 和 InnoDB 存储引擎应用的锁：

MyISAM 采纳表级锁(table-level locking)。

InnoDB 反对行级锁 (row-level locking) 和表级锁，默认为行级锁

行级锁，表级锁和页级锁比照

行级锁 行级锁是 Mysql 中锁定粒度最细的一种锁，示意只针对以后操作的行进行加锁。行级锁能大大减少数据库操作的抵触。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

特点：开销大，加锁慢；会呈现死锁；锁定粒度最小，产生锁抵触的概率最低，并发度也最高。

表级锁 表级锁是 MySQL 中锁定粒度最大的一种锁，示意对以后操作的整张表加锁，它实现简略，资源耗费较少，被大部分 MySQL 引擎反对。最常应用的 MYISAM 与 INNODB 都反对表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点：开销小，加锁快；不会呈现死锁；锁定粒度大，收回锁抵触的概率最高，并发度最低。

页级锁 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁两头的一种锁。表级锁速度快，但抵触多，行级抵触少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

特点：开销和加锁工夫界于表锁和行锁之间；会呈现死锁；锁定粒度界于表锁和行锁之间，并发度个别

4、从锁的类别上分 MySQL 都有哪些锁呢？像下面那样子进行锁定岂不是有点妨碍并发效率了

呈现概率: ★★★

从锁的类别上来讲，有共享锁和排他锁。

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁能够同时加上多个。

排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只能够加一个，他和其余的排他锁，共享锁都相斥。

用下面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是能够承受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不能够。

锁的粒度取决于具体的存储引擎，InnoDB 实现了行级锁，页级锁，表级锁。

他们的加锁开销从大到小，并发能力也是从大到小。

5、MySQL 中 InnoDB 引擎的行锁是怎么实现的？

呈现概率: ★★★★

答：InnoDB 是基于索引来实现行锁

select * from tab_with_index where id = 1 for update;

for update 能够依据条件来实现行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么 InnoDB 将实现表锁，并发将无从谈起

6、InnoDB 存储引擎的锁的算法有三种

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范畴，不包含记录自身
• Next-key lock：record+gap 锁定一个范畴，蕴含记录自身

7、什么是死锁？

呈现概率: ★★★★★

死锁是指两个或多个事务在同一资源上互相占用，并申请锁定对方的资源，从而导致恶性循环的景象。

8、常见的解决死锁的办法

呈现概率: ★★★★★

死锁是指多个事务在同一资源上互相占用并申请锁定对方占用的资源而导致恶性循环的景象。当多个事务试图以不同程序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。

为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越简单的零碎，例如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并立刻返回一个谬误。这种解决形式很无效，否则死锁会导致呈现十分慢的查问。还有一种解决办法，就是当查问的工夫达到锁期待超时的设定后放弃锁申请，这种形式通常来说不太好。InnoDB 目前解决死锁的办法是将持有起码行级排它锁的事务进行回滚。

死锁产生之后，只有局部或者齐全回滚其中一个事务，能力突破死锁。对于事务型零碎这是无奈防止的，所以应用程序在设计时必须思考如何解决死锁。大多数状况下只须要从新执行因死锁回滚的事务即可。

9、数据库的乐观锁和乐观锁是什么？怎么实现的？

呈现概率: ★★★★★

数据库管理系统（DBMS）中的并发管制的工作是确保在多个事务同时存取数据库中同一数据时不毁坏事务的隔离性和统一性以及数据库的统一性。乐观并发管制（乐观锁）和乐观并发管制（乐观锁）是并发管制次要采纳的技术手段。

乐观锁：假设会产生并发抵触，屏蔽所有可能违反数据完整性的操作。在查问完数据的时候就把事务锁起来，直到提交事务。实现形式：应用数据库中的锁机制

乐观锁：假如不会产生并发抵触，只在提交操作时查看是否违反数据完整性。在批改数据的时候把事务锁起来，通过 version 的形式来进行锁定。实现形式：乐个别会应用版本号机制或 CAS 算法实现。

两种锁的应用场景

从上面对两种锁的介绍，咱们晓得两种锁各有优缺点，不可认为一种好于另一种，像乐观锁实用于写比拟少的状况下（多读场景），即抵触真的很少产生的时候，这样能够省去了锁的开销，加大了零碎的整个吞吐量。

但如果是多写的状况，个别会常常产生抵触，这就会导致下层利用会一直的进行 retry，这样反倒是升高了性能，所以个别多写的场景下用乐观锁就比拟适合。

六、MySQL 视图

0、概要

• 1、为什么要应用视图？什么是视图？
• 2、视图有哪些特点？
• 3、视图的应用场景有哪些？
• 4、视图的长处
• 5、视图的毛病
• 6、存储过程与函数

1、为什么要应用视图？

呈现概率: ★★

应用视图能够简化简单的 SQL 操作，不便重用

2、视图有哪些特点？

呈现概率: ★★★

a)、视图的列能够来自不同的表，是表的形象和在逻辑意义上建设的新关系。
b)、视图是由根本表 (实表) 产生的表 (虚表)。
c)、视图的建设和删除不影响根本表。
d)、对视图内容的更新(增加，删除和批改) 间接影响根本表。
e)、当视图来自多个根本表时，不容许增加和删除数据。

3、视图的应用场景有哪些？

呈现概率: ★★★

晓得了视图的特点之后，咱们就晓得了能够应用视图 关联多个表的数据，不便咱们查问了，这个是在开发中还是有蛮多场景的，比方: 洽购单：有价格、数量、税率、含税金额，多半没有不含税金额、税额，而这些字段在很多报表中有都会用到，所以咱们能够创立一个含有计算列字段的视图来解决这个问题。

4、视图的长处

呈现概率: ★★★

• 数据库视图容许简化简单查问
• 数据库视图有助于限度对特定用户的数据拜访

5、视图的毛病

呈现概率: ★★★

• 性能：从数据库视图查问数据可能会很慢，特地是如果视图是基于其余视图创立的。
• 表依赖关系：将依据数据库的根底表创立一个视图。每当更改与其相关联的表的构造时，都必须更改视图。

6、存储过程与函数

呈现概率: ★★

实质上没区别，执行的实质都一样。函数有且只能返回一个变量, 存储过程能够返回多个。

一般来说，存储过程实现的性能要简单一点，而函数的实现的性能针对性比拟强。存储过程，功能强大，能够执行包含批改表等一系列数据库操作；用户定义函数不能用于执行一组批改全局数据库状态的操作。

对于存储过程来说能够返回参数，如记录集，而函数只能返回值或者表对象。函数只能返回一个变量；而存储过程能够返回多个。存储过程的参数能够有 IN,OUT,INOUT 三种类型，而函数只能有 IN 类型。存储过程申明时不须要返回类型，而函数申明时须要形容返回类型，且函数体中必须蕴含一个无效的 RETURN 语句。

存储过程个别是作为一个独立的局部来执行（EXECUTE 语句执行），而函数能够作为查问语句的一个局部来调用（SELECT 调用），因为函数能够返回一个表对象，因而它能够在查问语句中位于 FROM 关键字的前面。SQL 语句中不可用存储过程，而能够应用函数。

创立存储过程

create procedure sp_name()
begin
.........
end

调用存储过程

call sp_name()

七、MySQL 触发器

0、概要

• 1、什么是触发器？触发器的应用场景有哪些？
• 2、MySQL 中都有哪些触发器？

1、什么是触发器？触发器的应用场景有哪些？

呈现概率: ★★★

触发器是一种非凡的存储过程，它被调配给某个特定的表，触发器都是主动调用的。当一特定的表数据被插入，更新或删除时，数据库须要执行肯定的动作，触发器是确保数据完整性和一致性的根本无效的办法。

触发器应用场景:

• 能够通过数据库中的相干表实现级联更改。
• 实时监控某张表中的某个字段的更改而须要做出相应的解决。
• 例如能够生成某些业务的编号。
• 留神不要滥用，否则会造成数据库及应用程序的保护艰难。
• 大家须要牢记以上根底知识点，重点是了解数据类型 CHAR 和 VARCHAR 的差别，表存储引擎 InnoDB 和 MyISAM 的区别。

2、MySQL 中都有哪些触发器？

呈现概率: ★★★

MySQL 数据库中有六种触发器：

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

八、数据库优化

0、概要

• 1、为什么要优化
• 2、数据库构造优化
• 3、MySQL 数据库 cpu 飙升到 500% 的话他怎么解决？
• 4、大表怎么优化？某个表有近千万数据，CRUD 比较慢，如何优化？
• 5、垂直分表的实用场景和优缺点
• 6、程度分表的实用场景和优缺点
• 7、MySQL 的复制原理以及流程
• 8、读写拆散有哪些解决方案？
• 9、备份打算，mysqldump 以及 xtranbackup 的实现原理
• 10、数据表损坏的修复形式有哪些？

1、为什么要优化数据库

呈现概率: ★

当然是让咱们的数据库更稳、更快、更长久了。

2、数据库构造优化

呈现概率: ★★★

其实个别业务开发中, 这个关注的不多，预计是一些偏极客的团队关注的比拟多些

使表占用尽量少的磁盘空间。缩小磁盘 I / O 次数及读取数据量是晋升性能的根底准则。表越小，数据读写解决时则须要更少的内存，同时，小表的索引占用也绝对小，索引解决也更加疾速。

MySQL 反对不同类型的存储引擎和行格局，针对不同类型，业务需要的表应该设置适合的存储引擎及索引办法。表设置倡议如下：

表列

• 为列抉择最合适（通常最小）的数据类型。MySQL 具备许多不同列类型以最大化的缩小磁盘和内存占用。例如，应用足够小的整型来示意小范畴的小整型数据。MEDIUMINT 通常是一个很好的抉择，它只占用 INT 25%，甚至更小的空间。
• 如果可能，则将列申明为 NOT NULL。申明为 NOT NULL，使得优化器可能更好的应用索引，并防止了判断 NULL 的解决，这使得 SQL 操作执行的更加疾速。同时也为每列节俭了 1 bit 的空间。如果的确须要应用 NULL 值，那么也应该防止所有列都容许 NULL。
• InnoDB 表默认应用动静类型 (DYNAMIC) 的行格局。能够通过设置默认行格局（innodb_default_row_format），或者在表定义（CREATE TABLE 或 ALTER TABLE）中申明应用的行格局。

行格局

压缩类型的行格局，包含 COMPACT, DYNAMIC, 和 COMPRESSED，对于特定操作，缩小了存储空间占用，然而减少了 CPU 计算能力应用。如果次要的负载在缓存命中率及磁盘读写速度，那么这种格局将可能晋升数据库反应速度。如果是极其状况负载受限于 CPU 性能，那么应用这种格局则会升高数据库性能。

压缩行格局也会对应用 utf8mb3 或者 utf8mb4 格局的变长 CHAR 类型列存储进行优化解决。对于应用 ROW_FORMAT=REDUNDANT, CHAR(N) 定义的表，每个列值最多占用 N × 个字节长度。许多语言能够应用但字节的 utf8 格局示意，所以规定那个长度的定义通常会造成空间节约。压缩行格局定义下，InnoDB 会每一个列值调配一个 N 到 N× 个字节的空间。

3、MySQL 数据库 cpu 飙升到 500% 的话, 应该怎么解决？

呈现概率: ★★★

当 cpu 飙升到 500% 时，先用操作系统命令 top 命令察看是不是 mysqld 占用导致的，如果不是，找出占用高的过程，并进行相干解决。

如果是 mysqld 造成的，show processlist，看看外面跑的 session 状况，是不是有耗费资源的 sql 在运行。找出耗费高的 sql，看看执行打算是否精确，index 是否缺失，或者切实是数据量太大造成。

一般来说，必定要 kill 掉这些线程 (同时察看 cpu 使用率是否降落)，等进行相应的调整(比如说加索引、改 sql、改内存参数) 之后，再从新跑这些 SQL。

也有可能是每个 sql 耗费资源并不多，然而忽然之间，有大量的 session 连进来导致 cpu 飙升，这种状况就须要跟利用一起来剖析为何连接数会激增，再做出相应的调整，比如说限度连接数等

show full processlist 能够看到所有链接的状况，然而大多链接的 state 其实是 Sleep 的，这种的其实是闲暇状态，没有太多查看价值

咱们要察看的是有问题的，所以能够进行过滤：

— 查问非 Sleep 状态的链接，按耗费工夫倒序展现，本人加条件过滤

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc 

总结:

CPU 报警：很可能是 SQL 外面有较多的计算导致的

连接数超高：很可能是有慢查问，而后导致很多的查问在排队，排查问题的时候能够看到”事发现场“相似的 SQL 语句一大片，那么有可能是没有索引或者索引不好使，能够用：explain 剖析一下 SQL 语句

4、大表怎么优化？某个表有近千万数据，CRUD 比较慢，如何优化？

呈现概率: ★★★

千万级其实数量不大, CRUD 比较慢, 可能要思考磁盘、索引等问题.

5、垂直分表的实用场景和优缺点

呈现概率: ★★★

把主码和一些列放到一个表，而后把主码和另外的列放到另一个表中。

如果一个表中某些列罕用，而另外一些列不罕用，则能够采纳垂直宰割，另外垂直宰割能够使得数据行变小，一个数据页就能寄存更多的数据，在查问时就会缩小 I / O 次数。其毛病是须要治理冗余列，查问所有数据须要 join 操作

垂直切分的长处：

• 解决业务零碎层面的耦合，业务清晰
• 与微服务的治理相似，也能对不同业务的数据进行分级管理、保护、监控、扩大等
• 高并发场景下，垂直切分肯定水平的晋升 IO、数据库连接数、单机硬件资源的瓶颈

毛病：

• 局部表无奈 join，只能通过接口聚合形式解决，晋升了开发的复杂度
• 分布式事务处理简单
• 仍然存在单表数据量过大的问题（须要程度切分）

6、程度分表的实用场景和优缺点

呈现概率: ★★★

程度切分分为库内分表和分库分表，是依据表内数据外在的逻辑关系，将同一个表按不同的条件扩散到多个数据库或多个表中，每个表中只蕴含一部分数据，从而使得单个表的数据质变小，达到分布式的成果。如图所示：

程度切分的长处：

• 不存在单库数据量过大、高并发的性能瓶颈，晋升零碎稳定性和负载能力
• 利用端革新较小，不须要拆分业务模块

毛病：

• 跨分片的事务一致性难以保障
• 跨库的 join 关联查问性能较差
• 数据屡次扩大难度和保护量极大

7、MySQL 的复制原理以及流程

MySQL 主从复制工作原理

• 在主库上把数据更高记录到二进制日志
• 从库将主库的日志复制到本人的中继日志
• 从库读取中继日志的事件，将其重放到从库数据中
• 基本原理流程，3 个线程以及之间的关联
• 主：binlog 线程——记录下所有扭转了数据库数据的语句，放进 master 上的 binlog 中；
• 从：io 线程——在应用 start slave 之后，负责从 master 上拉取 binlog 内容，放进本人的 relay log 中；
• 从：sql 执行线程——执行 relay log 中的语句；

复制过程

• Binary log：主数据库的二进制日志
• Relay log：从服务器的中继日志
• 第一步：master 在每个事务更新数据实现之前，将该操作记录串行地写入到 binlog 文件中。
• 第二步：salve 开启一个 I /O Thread，该线程在 master 关上一个一般连贯，次要工作是 binlog dump process。如果读取的进度曾经跟上了 master，就进入睡眠状态并期待 master 产生新的事件。I/ O 线程最终的目标是将这些事件写入到中继日志中。
• 第三步：SQL Thread 会读取中继日志，并程序执行该日志中的 SQL 事件，从而与主数据库中的数据保持一致。

8、读写拆散有哪些解决方案？

读写拆散是依赖于主从复制，而主从复制又是为读写拆散服务的。因为主从复制要求 slave 不能写只能读（如果对 slave 执行写操作，那么 show slave status 将会出现 Slave_SQL_Running=NO，此时你须要依照后面提到的手动同步一下 slave）。

计划一

• 应用 mysql-proxy 代理
• 长处：间接实现读写拆散和负载平衡，不必批改代码，master 和 slave 用一样的帐号，mysql 官网不倡议理论生产中应用
• 毛病：升高性能，不反对事务

计划二

• 应用 AbstractRoutingDataSource+aop+annotation 在 dao 层决定数据源。
• 如果采纳了 mybatis，能够将读写拆散放在 ORM 层，比方 mybatis 能够通过 mybatis plugin 拦挡 sql 语句，所有的 insert/update/delete 都拜访 master 库，所有的 select
  都拜访 salve 库，这样对于 dao 层都是通明。plugin 实现时能够通过注解或者剖析语句是读写办法来选定主从库。不过这样仍然有一个问题，
  也就是不反对事务，所以咱们还须要重写一下 DataSourceTransactionManager，将 read-only 的事务扔进读库，
  其余的有读有写的扔进写库。

计划三

• 应用 AbstractRoutingDataSource+aop+annotation 在 service 层决定数据源，能够反对事务.
• 毛病：类外部办法通过 this.xx()形式互相调用时，aop 不会进行拦挡，需进行非凡解决。

九、MySQL 部署和运维

0、概要

• 1、如何更新给一个大表建索引
• 2、如何批量删除 N 行记录, 有什么注意事项
• 3、如何删除表？
• 4、MySQL 如何扩容
• 5、如何排查因为 MySQL 导致 CPU 占用高的问题？
• 6、MySQL 数据库磁盘 IO 应用高，请问如何进行排查？
• 7、如何批量插入大量数据?
• 8、数据备份和复原

1、如何更新给一个大表建索引

呈现概率: ★★★★

这个问题考查的点: 线上高并发下的增加大表建索引

当表数据量很大时，建设索引或者批改表构造会很慢，而且在操作的过程中，数据库甚至处于死锁状态，那么有没有其余的好的方法呢？

形式 1、“影子策略”

• 创立一张与原表（tb）构造雷同的新表（tb_new）
• 在新表上创立索引
• 重命名原表为其余表名（tb => tb_tmp），新表重命名为原表名（tb_new => tb），此时新表（tb）承当业务
• 为原表（tb_tmp）新增索引
• 替换表，新表改回最后的名称（tb => tb_new），原表改回最后的名称（tb_tmp => tb），原表（tb）从新承当业务
• 把新表数据导入原表（即把新表承当业务期间产生的数据和到原表中）

# 以下 sql 对应下面六步
create table tb_new like tb;
alter table tb_new add index idx_col_name (col_name);
rename table tb to tb_tmp, tb_new to tb;
alter table tb_tmp add index idx_col_name (col_name);
rename table tb to tb_new, tb_tmp => tb;
insert into tb (col_name1, col_name2) select col_name1, col_name2 from tb_new;

在生产环境, 这个形式有比较严重的问题, 步骤 3 之后，新表改为原表名后（tb）开始承当业务，步骤 3 到完结之前这段时间的新产生的数据都是存在新表中的，然而如果有业务对老数据进行批改或删除操作，那将无奈实现，所以步骤 3 到完结这段时间可能会产生数据（更新和删除）失落。

形式 2、利用辅助工具 Percona Toolkit 是一组高级的命令行工具，用来治理 MySQL 和零碎工作，次要包含：

• 验证主节点和复制数据的一致性
• 无效的对记录行进行归档
• 找出反复的索引
• 总结 MySQL 服务器
• 从日志和 tcpdump 中剖析查问
• 问题产生时收集重要的零碎信息
• 在线批改表构造

工作原理如下:

• 如果存在外键，依据 alter-foreign-keys-method 参数的值，检测外键相干的表，做相应设置的解决。没有应用 alter-foreign-keys-method 指定特定的值，该工具不予执行
• 创立一个新的空表，其命名规定是：下划线 + 原表名 +_new—-_原表名_new
• 依据 alter 语句，更新新表的表构造；
• 创立触发器，用于记录从拷贝数据开始之后，对源数据表持续进行数据批改的操作记录下来，用于数据拷贝完结后，执行这些操作，保证数据不会失落。如果表中曾经定义了触发器这个工具就不能工作了。
• 拷贝数据，从源数据表中拷贝数据到新表中。
• 批改外键相干的子表，依据批改后的数据，批改外键关联的子表。
• rename 源数据表为 old 表，把新表 rename 为源表名，其通过一个 RENAME TABLE 同时解决两个表，实现原子操作。（RENAME TABLE dbteamdb.user TO dbteamdb._user_old, dbteamdb._user_new TO dbteamdb.user）
• 将 old 表删除、删除触发器。

形式 3、凌晨进行保护，增加索引, 一部分公司也是这么做的，然而不够灵便，遇到一些紧急情况还是形式 2 更好些。

2、如何批量删除 N 行记录, 有什么注意事项

呈现概率: ★★★★

因为批量删除大表大量数据时, 数据会锁表, 在业务顶峰时会导致 数据库 CPU 暴涨, 升高服务器性能, 可能会造成数据库雪崩景象。

这个也是每个线上开发人员可能遇到的业务场景。

形式 1、删除大表的多行数据时，会超出 innod block table size 的限度，最小化的缩小锁表的工夫的计划是：

• 抉择不须要删除的数据，并把它们存在一张雷同构造的空表里
• 重命名原始表，并给新表命名为原始表的原始表名
• 删掉原始表

这个会有一个问题，正式环境删除间隙的数据同步问题。

形式 2、将批量删除工作拆减成 N 个小删除工作, 比方只删 1w 条后, 将删除工作压入到异步队列期待执行.

形式 3、删除表上的索引, 在删除数据, 能够提高效率, 然而不太适宜线上环境.

3、如何删除表

呈现概率: ★★★★

这个是如何批量删除 N 行记录的延长问题.

如果咱们清空表数据，倡议间接应用 truncate，效率上 truncate 远高于 delete，在另一篇文章讲 mysql 的，能够得悉，咱们 truncate 不走事务，不会锁表，也不会产生大量日志写入日志文件，咱们拜访 log 执行日志能够发现每次 delete 都有记录。truncate table table_name 会立即开释磁盘空间，并重置 auto_increment 的值，delete 删除不开释磁盘空间，insert 会笼罩之前的数据上，因为咱们创立表的时候有一个创立版本号。

4、MySQL 如何扩容

呈现概率: ★★★

目前可用计划

1)、MySQL 的复制：

一个 Master 数据库，多个 Salve，而后利用 MySQL 的异步复制能力实现读写拆散，这个计划目前利用比拟宽泛，这种技术对于以读为主的利用很无效。
数据切分（MySQL 的 Sharding 策略）：

• 垂直切分：一种是依照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切能够称之为数据的垂直（纵向）切分；垂直切分的思路就是剖析表间的聚合关系，把关系严密的表放在一起。
• 程度切分：另外一种则是依据表中的数据的逻辑关系，将同一个表中的数据依照某种条件拆分到多台数据库（主机）下面，这种切分称之为数据的程度（横向）切分。

2)、通过集群扩大：MySQL Cluster(NDB Cluster)

相似于 MongoDB 的动静扩容策略。

MySQL Cluster 是一套具备可扩大能力、实时、内存内且合乎 ACID 要求的事务型数据库，其将 99.999% 高可用性与低廉的开源总体领有老本相结合。在设计思路方面，MySQL Cluster 采纳一套分布式多主架构并借此彻底毁灭了单点故障问题。MySQL Cluster 可能横向扩大至商用硬件之上，可能通过主动分区以承载读取与写入敏感型工作负载，并可通过 SQL 与 NoSQL 接口实现拜访。

采纳 NDB 存储引擎，有数据节点，SQL 节点，和治理节点（1 个，配置要求低）

3)、分库分表分区

5、如何排查因为 MySQL 导致 CPU 占用高的问题？

呈现概率: ★★★

在问题呈现之前, 咱们须要做的是增强监控, 比方当 CPU 暴涨到 85% 时就进行报警, 这样当有问题时, 开发人员和运维人员能够第一工夫收到报警, 并解决，而不是让用户报进去: 你家 APP 是不是又崩了, 卡死了。

咱们平时遇到大部分 MySQL 导致 CPU 占用高的状况是因为慢查问实例呈现 CPU 飙升。这种状况体现是 QPS（每秒执行的查问次数）不高；查问执行效率低、执行时须要扫描大量表中数据。此时可能是因为存在慢查问导致，查问执行效率低，为了获取预期的后果就须要拜访大量的数据，导致均匀逻辑 IO 增高，此时就会是 CPU 利用率过高。

此时须要 DBA，定位效率低的查问、优化查问的执行效率、升高查问执行的老本。

具体定位的过程能够参考：

1)、通过在控制台下载慢查问日志，定位效率低的 SQL 并进行优化
2)、也能够通过 show processlist; 或 show full processlist; 命令查看以后执行的查问，重复执行，找到慢查问的 process ID，运行 kill 慢查问的 id 命名杀掉慢查问过程。

同时 MySQL 处在高负载环境下，磁盘 IO 读写过多，也会占用很多资源，必然会使 CPU 占用过高。CPU 过高，能够从上面几个方向进行排查问题和优化：

1)、关上慢查问日志，查问是否是某个 SQL 语句占用过多资源，如果是的话，能够对 SQL 语句进行优化，比方优化 insert 语句、优化 group by 语句、优化 order by 语句、优化 join 语句等等；
2)、思考索引问题；
3)、定期剖析表，使optimize table；
4)、优化数据库对象；
5)、思考是否是锁问题；
6)、调整一些 MySQL Server 参数，比方 key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size 等等；
7)、如果数据量过大，能够思考应用 MySQL 集群或者搭建高可用环境。
8)、思考磁盘是否满了

6、MySQL 数据库磁盘 IO 应用高，请问如何进行排查？

呈现概率: ★★★

咱们以 mysql5.7 版本为例，联合 performance_schema 来查看 MySQL 数据库的各种指标。IO 的话，能够查看这张表：

performance_schema.file_instances：列出了文件 I O 操作及其相干文件的工具实例

个别遇到问题时的排查思路：

1、慢 SQL 排除
2、硬件问题 -RAID 降级，磁盘故障等排除
3、innodb_log、innodb_buffer_pool_wait 相干配置
4、IO 相干参数配置

innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_doublewrite = 1
delay_key_write
innodb_read_io_threads
innodb_read_io_threads
innodb_io_capacity
innodb_flush_neighbors
sync_binlog

其中能够次要关注：sync_binlog, binlog 刷新的参数，默认是 1。

mysql> show variables like '%sync_bin%';
+--------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1   |
+---------------+-------+
1 row in set (0.00 sec)

sync_binlog 为 1, 示意每次事务提交后 MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘，频繁的写盘导致磁盘 IO 居高不下，将 sync_binlog 调整为 500。

大家都晓得, 影响数据库最大的性能问题就是磁盘 IO，为了晋升数据库的 IOPS，能够应用 SSD 或者 PCIE-SSD 高速磁盘。内存方面也很重要，内存能够缓存热点数据和存储引擎文件，防止产生过多的物理 IO，能够减少物理内存来进步数据库的并发和读写性能。

最初也倡议：最好部署相干的监控平台或者比照历史性能记录，联合业务以及负载来剖析。

7、如何批量插入大量数据?

呈现概率: ★★★

1)、本人也循环一条一条插入, 毛病时因为频繁建设连贯, 比拟耗时

2)、缩小连贯资源，拼接一条 sql，这样写一次失常插入一万条根本问题不大，除非数据很长，应酬一般的批量插入够用了，比方：批量生成卡号，批量生成随机码等等。

3)、应用存储过程

delimiter $$$
create procedure zqtest()
begin
declare i int default 0;
set i=0;
start transaction;
while i<80000 do
    //your insert sql 
set i=i+1;
end while;
commit;
end
$$$
delimiter;
call zqtest();

4)、应用 MYSQL LOCAL_INFILE

LOAD DATA LOCAL INFILE 'F:\\milo.csv' INTO TABLE test
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'LINES TERMINATED BY'\r\n'
IGNORE 1 LINES
(id, name)

工夫比照:

通过测试插入 1w 条数据时候与拼装批量插入语句工夫差异不大，当插入数量达到 10w 呈现了显著的时间差：
拼装批量插入语句破费工夫：6.83s
LOAD DATA LOCAL INFILE 实现大批量插入破费工夫：1.23s
当表格的字段更多数据量更大呈现的时间差就越大。

总结：当须要进行大批量数据插入的时候，能够优先思考 LOAD DATA LOCAL INFILE 实现形式。

8、数据备份和复原

呈现概率: ★★★★

Mysql 提供了 mysqldump、ibbackup、replication 工具来备份, 对于备份须要理解的几个概念:

1)、数据备份按备份类型：

• 热备：在数据库运行过程中间接备份，读写操作均可执行
• 冷备：在数据库进行的状况下备份，读写操作均不可进行，个别间接复制相干的物理文件即可
• 温备：在数据库运行过程中备份，读操作可执行；但写操作不可执行，但对数据库操作有影响，如加个全局读锁以保障备份数据一致性

对于热备原理:

双机热备就是应用 MySQL 提供的一种主从备份机制实现。所谓双机热备其实是一个复制的过程，复制过程中一个服务器充当主服务器，一个或多个服务器充当从服务。这个复制的过程本质上是从服务器复制主服务器上 MySQL 的二进制日志（bin-log），并在从服务器上还原主服务器上的 sql 语句操作，这样只有两个数据库的初态是一样的，就能始终同步。

双机热备的实现须要 MySQL 的版本高于 3.2.。另外因为这种备份是基于 MySQL 二进制日志实现，所以主从服务器上的 MySQL 版本最好可能一样，至多从服务器的 MySQL 版本不能够低于主服务器的数据库版本。因为 MySQL 不同的版本之间二进制日志可能不一样。

当然这种复制和反复都是 MySQL 主动实现的，咱们只须要配置即可。

上图中有两个服务器，演示了从一个主服务器（master）把数据同步到从服务器（slave）的过程。

这是一个主 - 从复制的例子。主 - 主相互复制只是把下面的例子反过来再做一遍。

2)、按备份内容：

• 日志备份：次要备份 bin-log 日志，而后 replay 来实现 point-in-time
• 齐全备份：对数据库一个残缺的备份
• 增量备份：在上次齐全备份的根底上对更改局部进行备份（MySQL 没真正的增量备份，个别通过 bin-log 实现，要借助第三方工具能力实现）

3)、按备份文件：

• 逻辑文件：指备份出的文件可读，个别指 SQL 语句（实用库降级，迁徙，但复原工夫较长须要执行 SQL 语句）
• 物理文件：指复制数据库的物理文件

其中 mysqldump 是属于逻辑备份，也是最常见的备份工具了，毛病在于备份和复原速度不是特地快

4)、最初对于备份数据, 别忘了咱们也要备份 binlog

如果有 DBA 通知你，这个数据库可能复原到两个个月内任何状态，这阐明了，这个数据库的 binlog 日志至多保留了两个月。备份 binlog 的益处:

• 能够实现基于任意工夫点的复原
• 能够用于误操作数据闪回
• 能够用于审计

5)、对于备份复原

a)、对于应用 mysqldump 进行逻辑备份的文件，mysql -u root -p [dbname] < backup.sql 进行复原即可

b)、xtrabackup 备份全量复原

Percona-xtrabackup 是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具，反对 MySQL、Percona server 和 MariaDB，开源收费，是目前较为受欢迎的支流备份工具。xtrabackup 只能备份 innoDB 和 xtraDB 两种数据引擎的表，而不能备份 MyISAM 数据表。

MySQL 冷备、mysqldump、MySQL 热拷贝都无奈实现对数据库进行增量备份。在理论生产环境中增量备份是十分实用的，如果数据大于 50G 或 100G，存储空间足够的状况下，能够每天进行残缺备份，如果每天产生的数据量较大，须要定制数据备份策略。例如每周实用残缺备份，周一到周六实用增量备份。而 Percona-Xtrabackup 就是为了实现增量备份而呈现的一款支流备份工具，

xtrabackup 蕴含两个次要的工具，即 xtrabackup 和 innobackupex，二者区别如下：

（1）xtrabackup 只能备份 innodb 和 xtradb 两种引擎的表，而不能备份 myisam 引擎的表；

（2）innobackupex 是一个封装了 xtrabackup 的 Perl 脚本，反对同时备份 innodb 和 myisam，但在对 myisam 备份时须要加一个全局的读锁。还有就是 myisam 不反对增量备份

Xtrabackup 备份原理

Xtrabackup 备份流程图（xtrabackup 备份过程中，先备份 innodb 表，再备份非 innodb 表）：

复原过程如下：

#步骤一：解压（如果没有压缩能够疏忽这一步）innobackupex--decompress< 备份文件所在目录 >

#步骤二：利用日志
innobackupex--apply-log< 备份文件所在目录 >

#步骤三：复制备份文件到数据目录
innobackupex--datadir=<MySQL 数据目录 >--copy-back< 备份文件所在目录 >

c)、基于工夫点复原

基于工夫点的复原依赖的是 binlog 日志，须要从 binlog 中找过从备份点到复原点的所有日志，而后利用。

show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set (0.06 sec)

查看 log_bin 配置是否启用 binlog。Mysql 8.0 默认开启 binlog。

如果 log_bin 是敞开, 能够批改 my.conf 关上. 这里不就开展讲 binlog 的复原了，想深刻理解的敌人能够本人钻研一下。

结语: 对于一个在正式环境运行的零碎来说, 数据是最重要的了, 代码咱们能够通过 git 来治理, 数据只能靠咱们本人进行备份、只有平时做好了操作，能力在关键时刻复原。比方这个经典案例, 如果平时做好了演练, 也就没有那么多事件了: