mysql面试知识点2

事务的基本特性和隔离级别

事务基本特性ACID分别是：

• 原子性 指的是一个事务中的操作要么全部成功，要么全部失败。

• 一致性 指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱， 假设A只有 90 块，支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库 数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证

• 隔离性 指的是一个事务的修改在最终提交前，对其他事务是不可见的。

• 持久性 指的是一旦事务提交，所做的修改就会永久保存到数据库中。

• 可以这么理解：原子性，隔离性和持久性都是为了保证一致性。

隔离性有 4 个隔离级别，分别是：

- read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。用户本来应该读取到id=1的用户age应该是 10 ，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。

- read commit 读已提交，两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。

- repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。

- serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

• 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，
  前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

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

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

ACID靠什么保证的？

• A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

• C一致性由其他三大特性保证、程序代码要保证业务上的一致性

• I隔离性由MVCC来保证

• D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。
如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么
InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

redolog的刷盘会在系统空闲时进行

锁的类型有哪些

基于锁的属性分类：共享锁、排他锁。

基于锁的粒度分类：行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎 )、记录锁、间隙锁、临键锁。

基于锁的状态分类：意向共享锁、意向排它锁。

• 共享锁(Share Lock)

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

• 排他锁（eXclusive Lock）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

• 表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；
特点： 粒度大，加锁简单，容易冲突；

• 行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问；
特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；

• 记录锁(Record Lock)

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。
精准条件命中，并且命中的条件字段是唯一索引
加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

• 页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

• 间隙锁(Gap Lock)

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。
范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。
触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。
比如表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10区间，10-n区间 （-n代表负无穷大，n代表正无穷大）

• 临建锁(Next-Key Lock)

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住
触发条件：范围查询并命中，查询命中了索引。
结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

• 意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

• 意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的排它共享锁。

什么是MVCC

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

• MVCC只在 读已提交 和 可重复读 两个隔离级别下工作。其他两个隔离级别和
  MVCC不兼容, 因为 读未提交 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 串行（SERIALIZABLE） 则会对所有读取的行都加锁。

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

trx_id ：用来存储每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer ：每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

已提交读和可重复读的区别就在于它们生成ReadView的策略不同 。

• 开始事务时创建readview，readView维护当前活动的事务id，即未提交的事务id，排序生成一个数组

• 访问数据，获取数据中的事务id（获取的是事务id最大的记录），对比readview：

• 如果在readview的左边（比readview都小），可以访问（在左边意味着该事务已经提交）

• 如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取roll_pointer，取上一版本重新对比（在右边意味着，该事务在readview生成之后出现，在readview中意味着该事务还未提交）

• 已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

• 这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。

mysql主从同步原理

mysql主从同步的过程：

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

* 主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
* 主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
* 从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
* 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

• 在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

• 慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

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

* 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
* 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
* 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

mysql执行计划怎么看

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

https://blog.csdn.net/wuseyukui/article/details/71512793

EXPLAIN SELECT * from A where X=? and Y=?

1. id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。

2. selectType 表示查询中每个select子句的类型

SIMPLE： 表示此查询不包含 UNION 查询或子查询
PRIMARY： 表示此查询是最外层的查询（包含子查询）
SUBQUERY： 子查询中的第一个 SELECT
UNION： 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

3. table：表示该语句查询的表

4. type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

const：通过索引一次命中，匹配一行数据
system: 表中只有一行记录，相当于系统表；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref: 非唯一性索引扫描,返回匹配某个值的所有
range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index: 只遍历索引树；
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多，执行效率越慢。

# 执行效率：

ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index

5. possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一
   定会用到。

6. key：此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集

7. key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是
   我们优化sql时，评估索引的重要指标

8. ref: 显示索引的那一列被使用了，如果可能，是一个常量const。

9. rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大

10. filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确， 百分比越小，说明查询到的数据量大，而结果集很少

11. extra

* using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
* using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。
* using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不高，建议优化。
* using where ：sql使用了where过滤,效率较高。