java面试题之-数据库篇(持续更新)

提前预知

MySQL中常见的面试题就是：索引，事务，数据库引擎，数据库中的锁，数据一致性等问题，本篇博客总结的是常见的数据库面试题。

MySQL基础

01、MySQL有哪些数据类型？

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

• 整数类型：

类型	大小	范围（有符号）	范围（无符号）	用途
tinyint	1byte	（-128，127）	(0，255)	小整数值
smallint	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
mediumint	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
int	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigint	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值

• 浮点小数类型

类型	大小	范围（有符号）	范围（无符号）	用途
float	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度，浮点数值
double	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度，浮点数值

• 定点小数类型

类型	大小	范围（有符号）	范围（无符号）	用途
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

• 每个时间类型有一个有效值范围和一个零值，当指定不合法的MySQL不能表示的值时使用零值。

类型	大小( bytes)	范围	格式	用途
YEAR	1	1901/2155	YYYY	年份值
TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

• text类型：char，varchar，tinytext、text、mediumtext、longtext

类型	大小	用途
char	0-255 bytes	定长字符串
varchar	0-65535 bytes	变长字符串
tinytext	0-255 bytes	短文本字符串
text	0-65 535 bytes	长文本数据
mediumtext	0-16 777 215 bytes	中等长度文本数据
longtext	0-4 294 967 295 bytes	极大文本数据

• 二进制类型：tinyblob，blob，mediumblob，longblob，二进制大对象，用来存储可变数量的数据。

类型	大小	用途
tinyblob	0-255 bytes	不超过 255 个字符的二进制字符串
blob	0-65 535 bytes	二进制形式的长文本数据
mediumblob	0-16 777 215 bytes	二进制形式的中等长度文本数据
longblob	0-4 294 967 295 bytes	二进制形式的极大文本数据

02、char 和 varchar 的区别是什么？

先说一下注意的点：

• char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数。

区别：

• 1、固定长度 & 可变长度

  • varchar：用于存储可变长度字符串，是最常见的字符串数据类型，它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)， 有一种情况例外，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储
  • char：用于存储固定长度字符串，MySQL总是根据定义的字符串长度分配足够的空间，当存储CHAR值时，MySQL会根据需要采用空格进行剩余空间填充，以方便比较和检索，但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

• 2、存储方式

  • varchar：需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。

  假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。

  • char：适合存储很短或长度近似的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值，对于经常变更的数据，char也比varchar更好，因为定长的CHAR类型不容易产生碎片。

  对于非常短的列，char比varchar在存储空间上也更有效率，例如用char(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是varchar(1)却需要两个字节，因为还有一个记录长度的额外字节。

• 3、存储容量

  • char：最多只能存放的字符个数为255，和编码无关，任何编码最大容量都是255，一个CHAR类型为8位，一个字节。
  • varchar：MySQL行默认最大65535字节，是所有列共享（相加）的，所以varchar的最大值受此限制。

  表中只有单列字段情况下，varchar一般最多能存放(65535 - 3)个字节，varchar的最大有效长度通过最大行数据长度和使用的字符集来确定，通常的最大长度是65532个字符（当字符串中的字符都只占1个字节时，能达到65532个字符）；

遗留问题：

• 为什么是65532个字符？算法如下（有余数时向下取整）：

  • 最大长度(字符数) = （行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数） / 字符集单字符最大字节数
  • NULL标识列占用字节数：允许NULL时，占一字节
  • 长度标识字节数：记录长度的标识，长度小于等于255（$2^8$）时，占1字节；小于65535时（$2^{16}$），占2字节

• VARCHAR类型在4.1和5.0版本发生了很大的变化，使得情况更加复杂。从MySQL 4.1开始，每个字符串列可以定义自己的字符集和排序规则。这些东西会很大程度上影响性能。

  • 4.0版本及以下，MySQL中varchar长度是按字节展示，如varchar(20)，指的是20字节；

  • 5.0版本及以上，MySQL中varchar长度是按字符展示。如varchar(20)，指的是20字符。

  • 当然，行总长度还是65535字节，而字符和字节的换算则与编码方式有关，不同的字符所占的字节是不同的。编码划分如下：

  • GBK编码：一个英文字符占一个字节，中文2字节，单字符最大可占用2个字节。

  • UTF-8编码： 一个英文字符占一个字节，中文3字节，单字符最大可占用3个字节。

  • utf8mb4编码： 一个英文字符占一个字节，中文3字节，单字符最大占4个字节（如emoji表情4字节）。

  • 假设当前还有6字节可以存放字符，按单字符占用最大字节数来算，可以存放3个GBK、或2个utf8、或1个utf8mb4。

思考：既然VARCHAR长度可变，那我要不要定到最大?

• 肯定不行

• 就像使用VARCHAR(8)和VARCHAR(200)存储AISMALL的磁盘空间开销是一样的，那么使用更短的列有什么优势呢？

  • 事实证明有很大的优势，更长的列会消耗更多的内存，存储引擎在存储的数据之前，并不会知道我这一行拿出来的数据到底有多长，可能长度只有1，可能长度是100，那怎么办呢？那就只能先把最大空间分配好了，避免放不下的问题发生，这样实际上对于真实数据较短的varchar确实会造成空间的浪费。

    举例：

    我向数据类型为：varchar（1000）的列插入了1024行数据，但是每个只存一个字符，那么这1024行真实数据量其实只有1K，但是我却需要约1M的内存去适应他。所以最好的策略是只分配真正需要的空间。

03、binary 和 varbinary的区别？

• binary 和 varbinary 类似于char 和varchar

• binary：存储固定长度二进制数据

• varbinary：存储可变长度的二进制数据

04、MySQL中myisam与innodb的区别?

MySQL中支持的数据库引擎可以通过下面这个指令来查看

SHOW ENGINES

但是常用的数据库存储引擎有MyISAM和InnoDB，下面说一下他们两个的区别：

对比项	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
锁	行锁	表锁
读写效率	写操作效率高	读操作效率高
全文索引	MySQL 5.6 后开始	支持
表占用空间	小	大
聚簇和给聚簇索引	都支持	只支持非聚簇索引

05、MySQL建表的约束条件有哪些？

• 主键约束（Primay Key Coustraint） 唯一性，非空性
• 唯一约束 （Unique Counstraint）唯一性，可以空，但空值只能有一个
• 检查约束 (Check Counstraint) 对该列数据的范围、格式的限制
• 默认约束 (Default Counstraint) 该数据的默认值
• 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

06、 数据库的三范式是什么？

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。

例如：我们要在数据库的一列存放一个地址，只用地址列存放地址就不太好，我们就要把这个地址列拆分成省，市，县，三列，这样方便以后分组查询，也不违背数据库设计的第一范式（列的原子性）。

第二范式：要求实体的属性完全依赖于主关键字，所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

例如：一张员工表，主关键字就是员工的id（唯一的），我们在表里面可以存放员工的基本信息，这些信息都依赖主关键字（描述一个员工的信息），如果我们在把和员工信息不太相关的列放在这张表中就违背了数据的第二范式。

第三范式：任何非主属性不依赖于其它非主属性，也就是非主键之间的列互相不依赖。

例如：还是一张员工表，如果我们在里面存放一个领导id的列，再增加存放领导姓名，领导电话的列，我们可以看出领导姓名和领导电话是依赖领导id这个列的，这样就违背数据库的第三范式，我们可以单独拆出一张表，表示领导的信息。

最后：数据的三范式可以尽可能的避免数据的冗余，设计出精简的表结构，也就类似于一种建议，我建议你这样做一样。

07、超键、候选键、主键、外键分别是什么？

写在前面：如果感觉元组不好理解，就把它理解为数据库表中的一行

• 超键(super key)：在关系中能唯一标识元组的属性集称为关系模式的超键

• 候选键(candidate key): 不含有多余属性的超键称为候选键，也就是在候选键中，若再删除属性，就不是键了！

• 主键(primary key): 用户选作元组标识的一个候选键程序主键

• 外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。

举例

学号	姓名	性别	年龄	系别	专业
140611127	张三	男	20	计算机	软件开发
140611128	李四	男	18	计算机	软件开发
140611129	王五	女	19	物理	力学
140611130	赵六	女	17	生物	动学
140611131	陈七	男	21	化学	食品化学
140611132	刘八	女	20	生物	植物学

• 超键

  • 在关系中能唯一标识元组的属性集称为关系模式的超键，于是我们从例子中可以发现 学号是标识学生实体的唯一标识，那么该元组的超键就为学号。
  • 除此之外我们还可以把它跟其他属性组合起来，比如：(学号，性别)，(学号，年龄)，这样也是超键，因为可以唯一标识元组属性集。

• 候选键

  • 不含多余属性的超键为候选键，根据例子可知，学号是一个可以唯一标识元组的唯一标识，因此学号是一个候选键，实际上，候选键是超键的子集，比如 （学号，年龄）是超键，但是它不是候选键，因为它还有了额外的属性。

• 主键

  • 用户选择的候选键作为该元组的唯一标识，那么它就为主键。简单的说，例子中的元组的候选键为学号，但是我们选定他作为该元组的唯一标识，那么学号就为主键。

• 外键

  • 外键是相对于主键的，比如在学生记录里，主键为学号，在成绩单表中也有学号字段，因此学号为成绩单表的外键，为学生表的主键。

总结

• 主键为候选键的子集，候选键为超键的子集，而外键的确定是相对于主键的。

08、MySQL中 in 和 exists 区别

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的，这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。

not in 和not exists：

• 如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；
• 而not extsts的子查询依然能用到表上的索引。
• 所以无论那个表大，用not exists都比not in要快。

09、 drop、delete与truncate的区别

三者都表示删除，但是三者有一些差别：

	delete	truncate	drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在删除表中的所有数据	删除数据库中的表，表结构，索引权限全部删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

10、MySQL中什么是存储过程？有哪些优缺点？

存储过程是一些预编译的 SQL 语句。

1、更加直白的理解：存储过程可以说是一个记录集，它是由一些 T-SQL 语句组成的代码块，这些 T-SQL 语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

2、存储过程是一个预编译的代码块，执行效率比较高,一个存储过程替代大量 T_SQL 语句 ，可以降低网络通信量，提高通信速率,可以一定程度上确保数据安全

但是,在互联网项目中,其实是不太推荐存储过程的,比较出名的就是阿里的《Java 开发手册》中禁止使用存储过程,我个人的理解是,在互联网项目中,迭代太快,项目的生命周期也比较短,人员流动相比于传统的项目也更加频繁,在这样的情况下,存储过程的管理确实是没有那么方便,同时,复用性也没有写在服务层那么好。

11、MySQL 执行查询的过程

• 第一层：处理客户端连接、授权认证，安全校验等。

  客户端通过 TCP 连接请求到 MySQL 连接器，连接器会对该请求进行，权限验证，安全校验，连接资源分配等

• 第二层：服务器server层，负责查缓存、分析、优化、执行操作引擎等。

  查缓存：当判断缓存是否命中时，MySQL 不会对查询语句进行解析，而是直接使用 SQL 语句和客户端发送过来的其他原始信息，所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。

  语法分析：看看SQL 语法是否写错了

  优化：是否使用索引，生成执行计划。

  执行操作：交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

• 第三层：存储引擎，负责MySQL中数据的存储和提取。

注意：我们要知道MySQL的服务器层是不管理事务的，事务是由，而MySQL中支持事务的存储引擎又属InnoDB`使用的最为广泛。

MySQL事务

01、什么是MySQL事务?

简单说，事务就是一组原子性的SQL执行单元，如果数据库引擎能够成功地对数据库应用该组査询的全部语句，那么就执行该组SQL，如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。

总结就是：要么全部执行成功（commit），要么全部执行失败（rollback）。

02、事务的四大特性是什么？

四大特性即ACID特性：原子性、一致性、隔离性、持久性

• 原子性(Atomicity)：单个事务，为一个不可分割的最小工作单元，整个事务中的所有操作要么全部commit成功，要么全部失败rollback，对于一个事务来说，不可能只执行其中的一部分SQL操作，这就是事务的原子性。

• 一致性(Consistency)：数据库总是从一个一致性的状态转换到另外一个一致性的状态。

  也就是数据库中的事务要么成功（一个状态），要么回滚（一个状态）

• 隔离性(Isolation)：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性(Durability)： 一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

03、事务的ACID特性具体是怎么实现的（事务的实现原理）？

对MySQL来说：逻辑备份日志（binlog）、重做日志（redolog）、回滚日志（undolog）、锁技术 + MVCC就是MySQL实现事务的基础。

• 原子性：通过undolog来实现（回滚操作就是根据这个日志来做的）。
• 持久性：通过binlog、redolog来实现（前滚操作就是根据这个日志来做的）。
• 隔离性：通过(读写锁+MVCC)来实现。
• 一致性：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性。

1、原子性原理

事务通常是以BEGIN TRANSACTION 开始，以 COMMIT 或 ROLLBACK 结束。

• COMMIT 表示提交：即提交事务的所有操作并持久化到数据库中。
• ROLLBACK表示回滚：即在事务中运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库所有已完成的操作全部撤销，回滚到事务开始时的状态，这里的操作指对数据库的更新操作，已执行的查询操作不用管，这时候也就需要用到 undolog（一种日志） 来进行回滚。

undolog:
	每条数据变更（INSERT/UPDATE/DELETE/REPLACE）等操作都会生成一条undolog记录，在SQL执行前先于数据持久化到磁盘。
	当事务需要回滚时，MySQL会根据回滚日志对事务中已执行的SQL做逆向操作，比如 DELETE 掉一行数据的逆向操作就是再把这行数据 INSERT回去，其他操作同理。

2、持久性原理

先了解一下MySQL的数据存储机制，MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘 IO，然而即使是使用 SSD 磁盘 IO 也是非常消耗性能的。为此，为了提升性能 InnoDB 提供了缓冲池(Buffer Pool)，Buffer Pool 中包含了磁盘数据页的映射，可以当做缓存来使用：

• 读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
• 写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；

我们知道，MySQL表数据是持久化到磁盘中的，但如果所有操作都去操作磁盘，等并发上来了，那处理速度谁都吃不消，因此引入了缓冲池(Buffer Pool)的概念，Buffer Pool 中包含了磁盘中部分数据页的映射，可以当做缓存来用；这样当修改表数据时，我们把操作记录先写到Buffer Pool中，并标记事务已完成，等MySQL空闲时，再把更新操作持久化到磁盘里，从而大大缓解了MySQL并发压力。

但是它也带来了新的问题，当MySQL系统宕机，断电时Buffer Pool数据不就丢了？

因为我们的数据已经提交了，但此时是在缓冲池里头，还没来得及在磁盘持久化，所以我们急需一种机制需要存一下已提交事务的数据，为恢复数据使用。

于是 redolog + binlog的经典组合就登场了，这两个日志会在事务提交之前先落盘，如果发生突然宕机的情况，再次开启MySQL服务的时候就会通过这两个日志文件将事务进行前滚。

3、隔离性原理

隔离性是事务ACID特性里最复杂的一个，在SQL标准里定义了四种隔离级别，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是不可见的。

Mysql 隔离级别有以下四种（级别由低到高）：

隔离级别	效果
读未提交（READ UNCOMMITTED）	一个事务还没提交时，它做的变更就能被别的事务看到。（别的事务指同一时间进行的增删改查操作）
读提交（READ COMMITTED）	一个事务提交（commit）之后，它做的变更才会被其他事务看到。
可重复读（REPETABLE READ）	一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行（xíng）化（SERIALIZABLE）	正如物理书上写的，串行是单线路，顾名思义在MySQL中同一时刻只允许单个事务执行，写会加写锁，读会加读锁。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

搞懂了隔离级别以及实现原理其实就可以理解ACID里的隔离性了。

前面说过原子性，隔离性，持久性的目的都是为了要做到一致性。

但隔离型跟其他两个有所区别，原子性和持久性是为了要实现数据的正确、可用，比如要做到宕机后的恢复、事务的回滚等，保证数据是正确可用的！

那么隔离性是要做到什么呢？

• 隔离性要管理的是：多个并发读写请求（事务）过来时的执行顺序。当并发处理多个DML更新操作时，如何让事务操作他该看到的数据，出现多个事务处理同一条数据时，让事务该排队的排队，别插队捣乱，保证数据和事务的相对隔离，这就是隔离性要干的事儿。
• 所以，从隔离性的实现原理上，我们可以看出这是一场数据的可靠性与性能之间的权衡。

4、一致性原理

一致性，我们要保障的是数据一致性，数据库中的增删改操作，使数据库不断从一个一致性的状态转移到另一个一致性的状态。

事务该回滚的回滚，该提交的提交，提交后该持久化磁盘的持久化磁盘，该写缓冲池的写缓冲池+写日志，对于数据可见性，通过四种隔离级别进行控制，使得库表中的有效数据范围可控，保证业务数据的正确性的前提下，进而提高并发程度，支撑服务高QPS的稳定运行，保证数据的一致性。

04、并发场景下事务会存在哪些数据问题？

并发场景下MySQL事务可能会出现脏读、不可重复读、幻读问题；

• 脏读(Drity Read)： 一个事务读到了另外一个事务更改且未提交的数据。

• 不可重复读(Non-repeatable read)：同一个事务的两次查询返回的数据不一致，这可能是两次查询过程中间插入了一个事务更新了原有的数据。

  一个事务对数据做出更改提交之后，其他事务可读。

• 幻读(Phantom Read)：在一个事务的两次查询中返回数据的个数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

  一个事务对数据做出更新并提交不会产生幻读，但是如果对数据进行插入操作并提交，其他事务可读

05、InnoDB存储引擎是如何解决幻读问题的？

在MySQL事务中，我们了解到不同的事务隔离级别会引发不同的问题，在 RR 级别下会出现幻读，但如果将存储引擎选为 InnoDB ，在 RR 级别下，幻读的问题也是可以被解决的。

先说结论，MySQL 存储引擎 InnoDB 在可重复读（RR）隔离级别下是解决了幻读问题的。

方法是通过：next-key lock在当前读事务开启时

• 1、给涉及到的行加写锁（行锁）防止写操作
• 2、给涉及到的行两端加间隙锁(Gap Lock)防止新增行写入，从而解决了幻读问题。

幻读出现的场景：

• 幻读出现在可重复读（RR）隔离级别下：
• 普通的SELECT查询就是快照读，是不会看到别的事务插入的数据的，因此，幻读在当前读下才会出现。（当前读会生成行锁，但行锁只能锁定存在的行，针对新插入的操作没有限定）
• 假如session B 的修改结果，被 session A 之后的 select 语句用当前读看到，不能称为幻读，幻读仅专指新插入的行。

为什么InnoDB在可重复读级别可以解决幻读问题？

• 因为幻读能仅通过行锁解决是解决不了的，所以要解决幻读，就必须得解决新增行的问题。
• 行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的间隙，因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。
• MySQL中的InnoDB存储引擎，将行锁 + 间隙锁组合统称为 next-key lock，通过 next-key lock 在RR级别下解决了幻读问题。

注意：InnoDB存储引擎可以在RR级别下可以解决幻读问题，其他的数据库引擎不一定可以。

06.、在事务中可以混合使用存储引擎吗？

• 尽量不要在同一个事务中使用多种存储引擎，MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。
• 如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表），在正常提交的情况下不会有什么问题。
• 但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定，所以，为每张表选择合适的存储引擎非常重要。

MySQL索引

01、什么是MySQL索引？

• 引入索引的目的是为了提高查询效率。
• 索引是表的目录，在查询数据之前先通过索引目录查找到相应的位置，以此快速定位要查询的数据。
• 对于索引，会保存在额外的文件中，索引是帮助MySQL高效获取数据的数据结构。

02、索引的种类有哪些？

1、从数据结构角度分：这里所描述的是索引存储时保存的形式

• BTree索引（B-Tree或B+Tree索引）
• Hash索引
• full-index全文索引
• R-Tree索引

2、从应用层次来分：普通索引，唯一索引，联合（复合）索引

• MySQL中索引使用的数据结构是B+Tree

• 普通索引也称为单值索引：即一个索引只包含单个列，一个表可以有多个普通索引 。

  由于创建索引会占用内存空间，如果普通索引创建的很多，就会占用太多的内存，因此建议使用联合索引

• 唯一索引：索引列的值必须唯一，但允许有空值且只能有一个。

  推荐使用自增的主键，因为如果使用UUID的话是随机生成的数，那样索引数据结构维护起来就比使用自增的主键要麻烦，这个和索引的数据结构有关系，了解即可

  主键索引为特殊的唯一索引，不允许有空值

• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并 。

  查找的时候按照创建的顺序进行查找（最左匹配原则），第一个值无法确定，就使用第二个，然后依次类推，如果第一个值就能确定那么后面就不在继续查找，直接返回。

3、从物理存储角度分：根据数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

03、聚簇索引和非聚簇索引的区别？

• 聚集索引：该索引中索引的逻辑顺序决定了表中相应行的物理顺序，因为索引使用B+Tree实现，也就是叶子节点中存放的就是要查找的数据。
• 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，因为索引使用B+Tree实现，也就是叶子节点中存放的不是要查找的数据，而是指向要查找数据的指针，因为地址离散，就好比先给离散地址编号，先找到这个编号的地址，然后通过这个地址找到存储数据的数据页。

04、哪些列上适合创建索引？创建索引有哪些开销？

• 经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。
• 创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响
• 索引也会占用额外的空间，所以不能所有列都创建索引。

05、为什么MySQL索引要用B+Tree而不是其他类型的树形结构，例如B树？

• B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，而每一个页的存储空间是有限的，如果数据较大时将会致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

• Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

• 二叉树：普通的二叉树，树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

• 红黑树：树的高度随着数据量增加而增加，IO代价高。

• B+Tree：B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息(也就是指针)，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度，IO操作少，查询效率就会变高。

06、什么是最左匹配原则？

最左匹配也即最左优先，以最左边的为起点任何连续的索引都能匹配上。

注意：当遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配原则的原理

• MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引，最左匹配原则都是针对联合索引来说的

• 我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个，构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

• 例子：

  • 假如创建一个（a,b)的联合索引，那么它的索引树是这样的可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，然后按照b进行排序，直接使用b是无序的。

  • 同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

  • 所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。

  • 例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

• 注意：如果查询条件是b = 2 and a = 1 也会走索引，因为优化器会自动调整a,b的顺序

优点：最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

07、索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

08、 建立联合索引时候需要考虑什么？

• 联合索引要遵从最左前缀原则，否则不会用到索引
• Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

09、MySQL b+树和hash实现原理

b+树采用的存储结构是树形结构，hash采用的是hash算法（地址散列）

• 采用 Hash 进行检索效率非常高，基本上一次检索就可以找到数据，而 B+ 树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次 I/O 操作，从效率来说 Hash 比 B+ 树更快。

B+ 树索引，Hash 索引使用上也会有差别。

• Hash 索引不能进行范围查询，而 B+ 树可以，这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。

• Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。

• Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+ 树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。

• 同理，我们也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 xx% 开头）的话就可以起到优化作用。

• 对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。

MySQL中的锁

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

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

• 悲观锁：总是认为会发生并发冲突，执行之前先把数据加锁，屏蔽一切可能违反数据完整性的操作，直到提交事务，才释放锁。实现方式：使用数据库中的锁机制

• 乐观锁：总是认为不会发生并发冲突，只在提交操作时检查是否违反数据完整性，这时候才把事务锁起来，并不是上来就加锁。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种：

• 乐观锁适用于读比较多的场景。

• 悲观锁适用于写比较多的场景。

02、MySQL中的锁有哪些？

先说一下为什么要加锁：

• 数据库是一个多用户使用的共享资源，当多个用户并发的存取数据时，在数据库中会产生多个事务同时的存取同一数据库的情况，若对并发操作不加以控制，就可能会读取和存储不正确的数据，破坏数据库的一致性。

数据库中锁的分类

• 按锁的粒度划分：表级锁、行级锁、页级锁
• 按锁级别划分：共享锁、排它锁、意向锁
• 按是否独占划分：读锁（共享锁），写锁（排它锁）
• 而表级锁/行级锁/页面锁可以使用读锁或者写锁来实现

共享的读锁：当一个线程对数据加锁之后，其他线程可以对数据进行读操作（它不会阻塞其他线程（用户）对同一数据的读请求，但会阻塞对同一数据的写请求）

独占的写锁：当一个线程对数据加锁之后，其它进程既不能对该数据进行读操作也无法对该数据进行写操作（它会阻塞其他线程（用户）对同一数据的读和写请求）

行级锁：引擎 INNODB， 仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作，InnoDB也支持两种形式的行级锁，一种是共享行级锁（读锁），一种是排他行级锁（写锁）。

InnoDB行锁是通过给索引上的索引项加锁来实现的，而不是给表的行记录加锁实现的，这就意味着，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁（因为没有索引嘛，存储引擎只能给所有的行都加锁，和表锁一样，把记录返回给MySQL Server，它会筛选出符合条件的行进行加锁，其余的行就会释放锁）！

表级锁：引擎 MyISAM，直接锁定整张表，MyISAM存储引擎支持：表共享的读锁和表独占的写锁。

MyISAM在执行查询语句(Select)前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户控制，是MySQL SERVER端自动完成的。

页级锁：引擎 BDB，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录

上述三种锁的特性可大致归纳如下：

• 1） 表级锁：开销小，加锁快；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 2） 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
• 3） 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

03、InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

04、什么是死锁？怎么解决？

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

常见的解决死锁的方法：

• 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

• 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

• 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

• 4、如果业务处理不好可以用分布式事务锁或者使用乐观锁

05、 隔离级别与锁的关系

在Read Uncommitted级别下：读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下：读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下：读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE ：是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

总结：不同隔离界别加锁和释放锁的时机不同。

06、优化锁方面的意见？

• 如果可以，尽量使用较低的隔离级别

• 设计索引，尽量使用索引去访问数据，加锁更加精确，从而减少锁冲突，锁粒度越大冲突越高。

• 选择合理的事务大小，给记录显示加锁时，最好一次性请求足够级别的锁。列如，修改数据的话，最好申请排他锁，而不是先申请共享锁，修改时在申请排他锁，这样会导致死锁

• 不同的程序访问一组表的时候，应尽量约定一个相同的顺序访问各表，对于一个表而言，尽可能的固定顺序的获取表中的行。这样大大的减少死锁的机会。

• 尽量使用相等条件访问数据，这样可以避免间隙锁对并发插入的影响

• 不要申请超过实际需要的锁级别

• 数据查询的时候不是必要，不要使用加锁。MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能：MVCC只在committed read（读提交）和 repeatable read （可重复读）两种隔离级别

• 对于特定的事务，可以使用表锁来提高处理速度活着减少死锁的可能。

MySQL中的分库分表

01. 为什么要分库分表？

分表：

• 单表数据量太大，会极大影响sql执行的性能，一般来说，单表到几百万的时候，性能就会相对差一些了，这时候就要分表了。

• 分表就是把一个表的数据放到多个表中，有垂直分表（竖着切），水平分表（横着切）

• 垂直分表：或者叫竖着切表，是不是感受到该策略是以字段为依据的！主要按照字段的活跃性、字段长度，将表中字段拆分到不同的表（主表和扩展表）中。

  • 特点：
    • 每个表的结构都不一样；
    • 每个表的数据也不一样，
    • 有一个关联字段，一般是主键或外键，用于关联兄弟表数据；
    • 所有兄弟表的并集是该表的全量数据；
  • 场景：
    • 有几个字段属于热点字段，更新频率很高，要把这些字段单独切到一张表里，不然innodb行锁很恶心的，锁死你呀，如用户表里的余额字段？不，我的余额就很稳定，一直是0。
    • 有大字段，如text，存储压力很大，毕竟innodb数据和索引是同一个文件；同时，我又喜欢用SELECT *，你懂得，这磁盘IO消耗的，跟玩儿似的，谁都扛不住的。
    • 有明显的业务区分，或表结构设计时字段冗余，有些小伙伴看到第一点时，就发现用户表怎么会有余额字段？明显有问题啊！是的，因此不同业务我们要把具体字段拆开，这样才有利于业务后续扩展哦。

• 水平分表：也叫横着切表，以行数据为依据进行切分，一般按照某列的自容进行切分。

  • 如手机号表，我们可以通过前两位或前三位进行切分，如131、132、133 → phone_131、phone_132、phone_133，手机号有11位（100亿），量大是很正常的事儿，这年头谁家老头老太太每个手机呢是吧。这样切就把一张大表切成了好几十张小表，数据量不就下来了。有同学就问了那我怎么知道我这手机号查哪个表呢？一看你就没认真看前两行标红的点，为啥标红嘞？比如我查13100001111，那我截取前三位，动态拼接到查询的表名上，就行了。例如：phone_ + 131=phone_131
  • 特点：
    • 每个表的结构都一样；
    • 每个表的数据都不一样，没有交集；
    • 所有表的并集是该表的全量数据；
  • 场景：
    • 单表的数据量过大或增长速度很快，已经影响或即将会影响SQL查询效率，加重了CPU负担，提前到达瓶颈。记得水平分表越早越好，别问我为什么。。

分库：

• 注意：传统的分库和我们熟悉的集群、主从复制可不是一个事儿，多节点集群是将一个库复制成N个库，从而通过读写分离实现多个MySQL服务的负载均衡，实际是围绕一个库来搞的，这个库称为Master主库。

• 我们现在说的分库是将主库一分为N，比如一分为二，然后针对这两个主库，再配置2N个从库节点。

• 分库的方式也有两种：垂直分库和水平分库

• 垂直分库：（纵向切库），太经典的切分方式，基于表进行切分，通常是把新的业务模块或集成公共模块拆分出去，比如我们最熟悉的单点登录、鉴权模块。

  • 特点：

    • 每个库的表都不一样；
    • 表不一样，数据就更不一样了，没有任何交集；
    • 每个库相对独立，模块化

  • 场景：

    • 可以抽象出单独的业务模块时，可以抽象出公共区时（如字典、公共时间、公共配置等）

• 水平分库：以行数据为依据，将一个库中的数据拆分到多个库中。大型分表体验一下？坦白说这种策略并不实用，因为会对后台开发很不友好，有很多坑，不建议采用，理解即可。

  • 特点：
    • 每个库的结构都一样；
    • 每个库的数据都不一样，没有交集；
    • 所有库的并集是全量数据；
  • 场景：
    • 系统绝对并发量上来了，CPU内存压力大。分表难以根本上解决量的问题，并且还没有明显的业务归属来垂直分库，主库磁盘接近饱和。

总结：在实际工作中，我们在选择分库分表策略前，想到的应该是从缓存、读写分离、SQL优化等方面，因为这些能够更直接、代价更小的解决问题。要记住动表就是动根本，你永远不知道这张表后面会连带多少历史遗留问题。

02. 用过哪些分库分表中间件？不同的分库分表中间件都有什么优点和缺点？

这个其实就是看看你了解哪些分库分表的中间件，各个中间件的优缺点是啥？比较常见的包括：

• cobar，TDDL，atlas，sharding-jdbc，mycat

• cobar：阿里 b2b 团队开发和开源的，属于 proxy 层方案。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。

• TDDL：淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

• atlas：360 开源的，属于 proxy 层方案，以前是有一些公司在用的，但是确实有一个很大的问题就是社区最新的维护都在 5 年前了。所以，现在用的公司基本也很少了。

• sharding-jdbc：当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。

• mycat：基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了，但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。

分库分表本人也没搞过，学生时代那有什么大数据，高并发，都是本地demo罢了，所以如果面试官问我，我直接回答没用过！！！！！

MVCC

什么是MVCC？

MVCC：多版本并发控制，读取数据时通过一种类似快照的方式将数据保存下来，这样读锁和写锁就不冲突了，不同的事务会话会看到自己版本的数据。

• MVCC只在READ COMMITTED和REPETABLE READ两个级别下面工作，其他隔离级别和MVCC不兼容。
• READ UNCOMMITTED总是读取最新的数据行，不符合当前事务版本的数据行
• SERIALIZABLE会对所有的数据加锁

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

• trx_id：用来存储每次对某条聚簇索引记录进行修改的时候的事务id
• roll_pointer：每次对那条聚簇索引记录有修改的时候，都会把老版本写入undo日志中，这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获取上一个版本的记录信息（注意：插入操作的undo日志没有这个属性，因为它没有老版本）

MySQL中的数据一致性问题

01、MySQL主从复制，MySQL的读写分离？

先说一下什么是主从复制：

• 主从复制也称主从同步，也就是使数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

• 复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

在说一下为什么需要主从复制：

• 1、通过增加从服务器来提高数据库的性能：在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。

• 2、数据备份：一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

主从复制的方式的过程：

• 基本原理流程是：3个线程以及之间的关联

  • 主：binlog线程，记录下所有改变了数据库数据的语句，放进master上的binlog中；
  • 从服务器生成两个线程，一个I/O线程，一个SQL线程；
  • I/O线程（logdump 线程）：在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log（中继日志）中；
  • SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致

• 注意：要实现MySQL的主从复制，首先必须打开Master端的Binlog日志功能，否则就无法实现，因为整个复制过程实际上就是Slave从Master端获取BInlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中记录的各种SQL操作。

MySQL如何实现读写分离？

• 其实很简单，就是基于主从复制架构，简单来说，就搞一个主库，挂多个从库，然后我们就单单只是写主库，然后主库会自动把数据给同步到从库上去，读从库。

02、主从复制如何保证数据的一致性？

先说结论：

• 可以采用MySQL的半同步机制，但是也是有风险的，因为可能会因为网络原因自动降级称异步复制
• 在程序段进行控制，比如说如果在从上读取到的数据和主不一致的话，那么就触发主从间的一次数据修复工作。
• 采用双写的方式，当写操作的时候，同时写主库和从库。

我们再来谈谈主从复制：

• 现在常用的MySQL高可用方案，十有八九是基于 MySQL的主从复制（replication）来设计的，包括常规的一主一从、双主模式，或者半同步复制（semi-sync replication）。

• 我们常常把MySQL主从复制 （replication）说成是MySQL同步（sync），但事实上这个过程是异步（async）的。大概过程是这样的：

  • 1、在master上提交事务后，并且写入binlog，返回事务成功标记；
  • 2、将binlog发送到slave，转储成relay log；
  • 3、在slave上再将relay log读取出来应用。

• 步骤1和步骤3之间是异步进行的，无需等待确认各自的状态，所以说MySQL主从复制 （replication）是异步的，这种异步的方式就很难保证主从复制中数据的一致性。

• MySQL中的半同步复制（semi-sync replication）在之前的基础上做了加强完善，整个流程变成了下面这样：

  • 1、首先，master和至少一个slave都要启用semi-sync replication模式；
  • 2、某个slave连接到master时，会主动告知当前自己是否处于semi-sync模式；
  • 3、在master上提交事务后，写入binlog后，还需要通知至少一个slave收到该事务，等待写入relay log并成功刷新到磁盘后，向master发送slave节点已完成该事务确认通知；
  • 4、master收到上述通知后，才可以真正完成该事务提交，返回事务成功标记；
  • 5、注意：当slave向master发送通知时间超过rpl_semi_sync_master_timeout设定值时，主从关系会从半同步复制模式（semi-sync）自动调整成为传统的异步复制模式。

• 半同步复制看起来很美好有木有，但如果网络质量不高，是不是出现抖动，触发上述第5条的情况，会从半同步复制降级为普通复制，此外，采用半同步复制，会导致master上的tps性能下降非常严重，最严重的情况下可能会损失50%以上。

• 这样来看，除非需要非常严格保证数据一致性等迫不得已的场景，就不太建议使用半同步复制了。当然了，事实上我们也可以通过加强程序端的逻辑控制，来避免主从数据不一致时发生逻辑错误，比如说如果在从上读取到的数据和主不一致的话，那么就触发主从间的一次数据修复工作。或者，我们也可以用 pt-table-checksum & pt-table-sync 两个工具来校验并修复数据，只要运行频率适当，是可行的。

• 真想要提高多节点间的数据一致性，可以考虑采用PXC方案。现在已知用PXC规模较大的有qunar、sohu，如果团队里初期没有人能比较专注PXC的话，还是要谨慎些，毕竟和传统的主从复制差异很大，出现问题时需要花费更多精力去排查解决。

现在来说说如何保证主从复制数据一致性？

• 上面说完了异步复制、半同步复制、PXC，我们回到主题：在常规的主从复制场景里，如何能保证主从数据的一致性，不要出现数据丢失等问题呢？

• 在MySQL中，一次事务提交后，需要写undo、写redo、写binlog，写数据文件等等，在这个过程中，可能在某个步骤发生crash，就有可能导致主从数据的不一致，为了避免这种情况，我们需要调整主从上面相关选项配置，确保即便发生crash了，也不能发生主从复制的数据丢失。

• 1、 在master上修改配置

  innodb_flush_log_at_trx_commit = 1
  sync_binlog = 1
  

  • 上述两个选项的作用是：保证每次事务提交后，都能实时刷新到磁盘中，尤其是确保每次事务对应的binlog都能及时刷新到磁盘中，只要有了binlog，InnoDB就有办法做数据恢复，不至于导致主从复制的数据丢失。

• 2、 在slave上修改配置

  master_info_repository = "TABLE"
  relay_log_info_repository = "TABLE"
  relay_log_recovery = 1
  

  • 上述前两个选项的作用是：确保在slave上和复制相关的元数据表也采用InnoDB引擎，受到InnoDB事务安全的保护，而后一个选项的作用是开启relay log自动修复机制，发生crash时，会自动判断哪些relay log需要重新从master上抓取回来再次应用，以此避免部分数据丢失的可能性。

• 通过上面几个选项的调整，就可以确保主从复制数据不会发生丢失了。但是，这并不能保证主从数据的绝对一致性，因为，有可能设置了ignore\do\rewrite等replication规则，或者某些SQL本身存在不确定因素，或者人为在slave上修改数据，最终导致主从数据不一致。这种情况下，可以采用pt-table-checksum 和 pt-table-sync 工具来进行数据的校验和修复。

03、MySQL主从同步延时问题如何解决？

先分析一下原因：

• 前面已经说了主从复制的原理，从原理可以知道，为什么会有延时。

• MySQL的主从复制都是单线程的操作，主库对所有DDL（数据库定义语言）和DML（数据库操作语言）产生的日志写进binlog，由于binlog是顺序写，所以效率很高。

• Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重做，DML和DDL的IO操作是随即的，不是顺序的，成本高很多。

• 另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的DML数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

• 常见原因：Master负载过高、Slave负载过高、网络延迟、机器性能太低、MySQL配置不合理。

问题解决：

• MySQL 实际上在有两个同步机制，一个是半同步复制，用来解决主库数据丢失问题；一个是并行复制，用来解决主从同步延时问题。
• 方案一：采用半同步复制（也有弊端，由于网络不可抗力因素，而且主库的吞吐量会大幅下降）。
• 方法二：采用并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

04、如何保证缓存和数据库的一致性（重点问题）？

老生常谈的一个话题了，先来说说更新和删除缓存策略的方式：

• 1、先更新缓存，再更新数据库
• 2、先更新数据库，再更新缓存
• 3、先删除缓存，再更新数据库
• 4、先更新数据库，再删除缓存

每个方案都是有利有弊，接下来分析一下具体原因：

• 方案一：更新缓存，更新数据库

  这种方式如果先更新缓存成功，但是数据库更新失败，则肯定会造成数据不一致，这时候缓存中存在的就是脏数据，不建议采用。

• 方案二：更新数据库，更新缓存

  这种方式的缓存更新策略俗称双写，存在问题是，并发更新数据库场景下，会将脏数据刷到缓存

  举例：如果A线程更新数据库，还未来的即更新缓存，B线程进来，更新了数据库，同时还更新了缓存，这时候A线程获得执行权，更新到缓存的就是脏数据。

• 方案三：删除缓存，更新数据库

  这种方式存在问题：更新数据库之前，若有查询请求，会将脏数据刷到缓存。

  举例：如果A线程删除了缓存，还未来得及更新数据库，，B线程进来查询，这时候会把原始数据再次刷到缓存，然后A线程获得执行权，更新数据库后，缓存中存在的就是脏数据。

• 方案四：更新数据库，删除缓存

  这种方式存在问题：如果在查询数据库和放入缓存这两个操作中间发生了数据更新并且删除缓存，那么会有旧数据放入缓存，缓存中存在的就是脏数据。

方案对比：

• 方案1和方案2的共同缺点：并发更新数据库场景下，会将脏数据刷到缓存，但一般并发写的场景概率都相对小一些；

• 方案3和方案4的共同缺点：不管采用哪种顺序，2种方式都是存在一些问题的：

  • 主从延时问题：不管是先删除还是后删除，数据库主从延时可能导致脏数据的产生。
  • 缓存删除失败：如果缓存删除失败，则都会产生脏数据

如何保证缓存和数据库的一致性解决：

• 延迟双删，添加重试机制

  采用更新前后双删除缓存策略：先删缓存，在更新数据库，然后在删除缓存。

  这么做的目的，就是确保读请求结束，写请求可以删除读请求造成的缓存脏数据。

  public void write(String key,Object data){
    	 redis.del(key);
       db.update(data);
       Thread.sleep(1000);
       redis.del(key);
   }
  

MySQL优化

1.、如何定位及优化SQL语句的性能问题？

• 首先使用执行计划，查找到低效率的SQL，MySQL提供了explain命令来查看语句的执行计划

• 对于查询语句，最重要的优化方式就是使用索引， 通过执行计划，就可以显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

2、大表数据查询，怎么优化？

• 方案一：sql语句+索引

• 方案二：加缓存，例如：redis

• 方案三：主从复制，读写分离（注意：读写分离是基于主从复制的）；

• 方案四：切表（垂直拆分）根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

• 方案五：切表（水平切分）针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

3、超大分页怎么处理?

当数据库数据量达到百万级别，而我们又需要在查询条件之后使用limit进行分页，那么我们就不能简单的使用普通的sql语句了。

普通的sql查询语句如下：

• select * from table where age > 20 limit 1000000，10
• 这种查询其实也是有可以优化的余地的，这条语句需要加载1000000 数据，然后基本上全部丢弃，只取10条，当然比较慢。

优化方式：可以使用子查询的方法，先使用覆盖索引找到符号条件的id，再使用聚簇索引查询数据，简单来说就是：

• 利用延迟关联或者子查询优化超多分页场景
• 修改为select * from table where id in (select id from table where age > 20 limit 1000000,10)
• 这样虽然也加载了一百万的数据，但是由于索引覆盖，要查询的所有字段都在索引中，所以速度会很快。

解决超大分页，其实主要是靠缓存，可预测性的提前查到内容，缓存至redis等k-V数据库中，直接返回即可.

在阿里巴巴《Java开发手册》中，对超大分页的解决办法是类似于上面提到的第一种.

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的id段，然后再关联：

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

4、统计过慢查询吗？对慢查询都怎么优化过？

慢查询的优化首先要搞明白慢的原因是什么？

• 是查询条件没有命中索引？
• 是加载了不需要的数据列？
• 还是数据量太大？

所以优化也是针对这三个方向来的：

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

5. 如何优化查询过程中的数据访问

访问数据太多导致查询性能下降，确定应用程序是否在检索大量超过需要的数据，可能是太多行或列，确认MySQL服务器是否在分析大量不必要的数据行：

• 查询不需要的数据。解决办法：使用limit解决。
• 多表关联返回全部列。解决办法：指定列名。
• 总是返回全部列。解决办法：避免使用SELECT *。
• 重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存。
• 是否在扫描额外的记录。解决办法：使用explain进行分析，然后优化
  • 1、使用索引
  • 2、改变数据库和表的结构。修改数据表范式
  • 3、重写SQL语句，让优化器可以以更优的方式执行查询。

6、如何优化关联查询

• 确定ON或者USING子句中是否有索引。
• 确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

7、数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果，数据库设计的时候要遵循数据库的三大范式，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

方案一：将字段很多的表分解成多个表

• 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
• 因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

方案二：增加中间表

• 对于需要经常联合查询的表，可以建立中间表以提高查询效率。
• 通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

方案三：增加冗余字段

• 设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅，但是，合理的加入冗余字段可以提高查询速度。
• 表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
• 注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

8、MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 观察是不是 MySQLd 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 MySQLd 造成的， 使用命令show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行，找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

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

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

9. 大表怎么优化？

类似的问题（其实前面已经提到这个问题了）：

• 某个表有近千万数据，CRUD比较慢，如何优化？
• 分库分表了是怎么做的？
• 分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

• 限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
• 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
• 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑；
• 通过分库分表的方式进行优化：主要有垂直分表和水平分表。