MySQL面试题和MVCC

发布于 2021-09-07 11:24 ，所属分类：2021面试经验技巧分享

MVCC多版本并发控制

1、MVCC

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

2、当前读：读取最新版本的数据

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

3、快照读（提高数据库的并发查询能力）：读取历史数据

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

4、当前读、快照读、MVCC关系

MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

5、MVCC解决的问题

数据库并发场景有三种，分别为：

1、读读：不存在任何问题，也不需要并发控制

2、读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读

3、写写：有线程安全问题，可能存在更新丢失问题

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：

1、在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

2、解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

6、MVCC实现原理

mvcc的实现原理主要依赖于记录中的三个隐藏字段，undolog，read view来实现的。

1.隐藏字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

（1）DB_TRX_ID

6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id

（2）DB_ROLL_PTR

7字节，回滚指针，指向这条记录的上一个版本,用于配合undolog，指向上一个旧版本

（3）DB_ROW_JD

6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id

记录如图所示：

在上图中，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID，DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

2.undo log

undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志。

当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃。

当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit，并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true，并且DB_TRX_ID相对于purge线程的read view 可见，那么这条记录一定时可以被清除的）

下面我们来看一下undolog生成的记录链

1、假设有一个事务编号为1的事务向表中插入一条记录，那么此时行数据的状态为：

2、假设有第二个事务编号为2对该记录的name做出修改，改为lisi

在事务2修改该行记录数据时，数据库会对该行加排他锁。

然后把该行数据拷贝到undolog中，作为旧记录，即在undolog中有当前行的拷贝副本。

拷贝完毕后，修改该行name为lisi，并且修改隐藏字段的事务id为当前事务2的id，回滚指针指向拷贝到undolog的副本记录中

事务提交后，释放锁

3、假设有第三个事务编号为3对该记录的age做了修改，改为32

在事务3修改该行数据的时，数据库会对该行加排他锁

然后把该行数据拷贝到undolog中，作为旧纪录，发现该行记录已经有undolog了，那么最新的旧数据作为链表的表头，插在该行记录的undolog最前面

修改该行age为32岁，并且修改隐藏字段的事务id为当前事务3的id，回滚指针指向刚刚拷贝的undolog的副本记录

事务提交，释放锁

从上述的一系列图中，大家可以发现，不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录。

3.Read View

上面的流程如果看明白了，那么大家需要再深入理解下read view的概念了。

Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。

其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据

Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的id去对比，如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较，即遍历链表中的DB_TRX_ID，直到找到满足条件的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。

Read View的可见性规则如下所示：

首先要知道Read View中的三个全局属性：

（1）trx_list:一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID（1,2,3）

（2）up_limit_id:记录trx_list列表中事务ID最小的ID（1）

（3）low_limit_id:Read View生成时刻系统尚未分配的下一个事务ID，（4）

具体的比较规则如下：

1、首先比较DB_TRX_ID < up_limit_id（1）,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断

2、接下来判断DB_TRX_ID >= low_limit_id（4）,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断

3、判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

7、MVCC的整体处理流程

假设有四个事务同时在执行，如下图所示：

事务1	事务2	事务3	事务4
事务开始	事务开始	事务开始	事务开始
......	......	......	修改且已提交
进行中	快照读	进行中
......	......	......

从上述表格中，我们可以看到，当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View视图，可以看到事务1和事务3还在活跃状态，事务4在事务2快照读的前一刻提交了更新，所以，在Read View中记录了系统当前活跃事务1，3，维护在一个列表中。同时可以看到up_limit_id的值为1，而low_limit_id为5，如下图所示：

在上述的例子中，只有事务4修改过该行记录，并在事务2进行快照读前，就提交了事务，所以该行当前数据的undolog如下所示：

当事务2在快照读该行记录的是，会拿着该行记录的DB_TRX_ID去跟up_limit_id,lower_limit_id和活跃事务列表进行比较，判读事务2能看到该行记录的版本是哪个。

具体流程如下：先拿该行记录的事务ID（4）去跟Read View中的up_limit_id相比较，判断是否小于，通过对比发现不小于，所以不符合条件，继续判断4是否大于等于low_limit_id,通过比较发现也不大于，所以不符合条件，判断事务4是否处理trx_list列表中，发现不再次列表中，那么符合可见性条件，所以事务4修改后提交的最新结果对事务2 的快照是可见的，因此，事务2读取到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度的最新版本。如下图所示：

当上述的内容都看明白了的话，那么大家就应该能够搞清楚这几个核心概念之间的关系了，下面我们讲一个不同的隔离级别下的快照读的不同。

8、RC、RR级别下的InnoDB快照读有什么不同

因为Read View生成时机的不同，从而造成RC、RR级别下快照读的结果的不同

1、在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照即Read View,将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View,所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View,所以对之后的修改不可见

2、在RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动和事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见

3、在RC级别下，事务中，每次快照读都会新生成一个快照和Read View,这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。

总结：在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

MySQL

1、描述一下数据库事务隔离级别？

事务（ACID）4种属性：

原子性：undo log（MVCC）-全成功-全失败（回滚）；

一致性:最核心和最本质的要求（执行前后保持一致）

隔离性:锁，mvcc（多版本并发控制）（互不影响）

持久性：redo log （不丢失）

隔离级别实现原理？答：mvcc+锁

数据库的事务隔离级别有四种，分别是读未提交、读已提交、可重复读、序列化，不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题，因此在选择隔离级别的时候要根据应用场景来决定，使用合适的隔离级别。

各种隔离级别和数据库异常情况对应情况如下：

隔离级别	脏读	不可重复读	幻读
READ- UNCOMMITTED(读取未提交)	√	√	√
READ-COMMITTED(读取已提交)	×	√	√
REPEATABLE- READ(可重复读)	×	×	√
SERIALIZABLE(可串行化）	×	×	×

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。

READ-COMMITTED(读取已提交)：事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。

REPEATABLE-READ(可重复读)：这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读。

SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

2、MVCC的实现原理

mvcc的实现原理主要依赖于记录中的三个隐藏字段，undolog，read view来实现的。

3、mysql幻读怎么解决的

事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B新插入的数据称之为幻读。

幻读是不可重复读的子类。

CREATETABLE`user`(`id`int(11)NOTNULLAUTO_INCREMENT,`name`varchar(255)DEFAULTNULL,`age`int(11)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDB;INSERTintouserVALUES(1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);

假设有如下业务场景：

时间	事务1	事务2
	begin；
T1	select * from user where age = 20;2个结果
T2		insert into user values(25,'25',20);commit;
T3	select * from user where age =20;2个结果
T4	update user set name='00' where age =20;此时看到影响的行数为3
T5	select * from user where age =20;三个结果

执行流程如下：

1、T1时刻读取年龄为20 的数据，事务1拿到了2条记录。

2、T2时刻另一个事务插入一条新的记录，年龄也是20 。

3、T3时刻，事务1再次读取年龄为20的数据，发现还是2条记录，事务2插入的数据并没有影响到事务1的事务读取。

4、T4时刻，事务1修改年龄为20的数据，发现结果变成了三条，修改了三条数据。

5、T5时刻，事务1再次读取年龄为20的数据，发现结果有三条，第三条数据就是事务2插入的数据，此时就产生了幻读情况。

此时大家需要思考一个问题，在当下场景里，为什么没有解决幻读问题？

其实通过前面的分析，大家应该知道了快照读和当前读，一般情况下select * from ....where ...是快照读，不会加锁，而 for update,lock in share mode,update,delete都属于当前读，如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。

如果都是当前读的话，如何解决幻读问题呢？

truncatetableuser;INSERTintouserVALUES(1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);

时间	事务1	事务2
	begin;
T1	select * from user where age =20 for update;
T2		insert into user values(25,'25',20);此时会阻塞等待锁
T3	select * from user where age =20 for update;

此时，可以看到事务2被阻塞了，需要等待事务1提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题。

4、sql join原理?

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

1、Simple Nested-Loop Join

这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了。

2、Index Nested-Loop Join

索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。

这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

3、Block Nested-Loop Join

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。

Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。

在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

5、说明一下数据库索引原理、底层索引数据结构，叶子节点存储的是什么，索引失效的情况？

索引的实现原理，底层数据结构，叶子节点存储数据需要看视频了解。

索引失效的情况：

1、组合索引不遵循最左匹配原则。

2、组合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效。

3、不要在索引上做任何操作（计算，函数，类型转换）。

4、is null和is not null无法使用索引。

5、尽量少使用or操作符，否则连接时索引会失效。

6、字符串不添加引号会导致索引失效。

7、两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效。

8、like语句中，以%开头的模糊查询。

9、如果mysql中使用全表扫描比使用索引快，也会导致索引失效。

组合索引

1.组合索引多字段是有序的，并且是个完整的BTree 索引，有最左原则

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。

搜索需要从根节点出发，上层节点对应靠左的值，搜索需要从根节点出发，否则不从根节点出发，后面的节点对应下层的值，依旧是乱序的，需要遍历，所以索引就失效了，所以有最左原则。

2组合索引的使用：

例如组合索引（a,b,c），组合索引的生效原则是

从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

比如

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；

where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果

where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；

where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

组合索引使用判断：

(0) select * from mytable where a=3 and b=5 and c=4;

abc三个索引都在where条件里面用到了，而且都发挥了作用

(1) select * from mytable where c=4 and b=6 and a=3;

这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

(2) select * from mytable where a=3 and c=7;

a用到索引，b没有用，所以c是没有用到索引效果的

(3) select * from mytable where a=3 and b>7 and c=3;(范围值就算是断点)

a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

(4) select * from mytable where b=3 and c=4;

因为a索引没有使用，所以这里 bc都没有用上索引效果

(5) select * from mytable where a>4 and b=7 and c=9;

a用到了 b没有使用，c没有使用

(6) select * from mytable where a=3 order by b;

a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的

(7) select * from mytable where a=3 order by c;

a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

(8) select * from mytable where b=3 order by a;

b没有用到索引，排序中a也没有发挥索引效果

MySQL使用explain判断是否使用索引及类型

具使用explain函数查看sql执行信息

all:全表扫描

index:另一种形式的全表扫描，只不过他的扫描方式是按照索引的顺序

range：有范围的索引扫描，相对于index的全表扫描，他有范围限制，因此要优于index

ref: 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

const:通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。

当extra出现Using filesor或Using temproary时，表示无法使用索引，必须尽快做优化。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

6、mysql如何做分库分表的？

使用mycat或者shardingsphere中间件做分库分表，选择合适的中间件，水平分库，水平分表，垂直分库，垂直分表。

在进行分库分表的时候要尽量遵循以下原则：

1、能不切分尽量不要切分；

2、如果要切分一定要选择合适的切分规则，提前规划好；

3、数据切分尽量通过数据冗余或表分组来降低跨库 Join 的可能；

4、由于数据库中间件对数据 Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取尽量少使用多表 Join。

7、数据存储引擎有哪些？

大家可以通过show engines的方式查看对应的数据库支持的存储引擎。

MyISAM、InnoDB、MEMORY、MERGE、ARCHIVE

1、MyISAM无法处理事务。

适用场景：选择密集型的表--MyISAM在筛选大量数据时非常迅速；插入密集型的表--MyISAM的并发插入特性允许同时选择和插入数据。

应用：管理邮件或web服务器日志信息。

2、InnoDB:

适用场景：更新密集的表--InnoDB适用多重并发的更新请求；事务；自动灾难恢复；外键约束；支持自动增加列AUTO_INCREMENT属性。

新特性：透明压缩、加密、虚拟列。

3、MEMORY:逻辑存储介质是系统内存，但当mysqld守护进程崩溃时，所有memory数据都会丢失。同时要求数据表中使用的数据时长度不变的格式，这意味着不能使用BLOB、TEXT这样的长度可变的类型，但可以使用varchar，因为varchar在Mysql内部是定长的char类型。

适用场景：目标数据较小，而且被频繁的使用；数据临时，又要求立即可用。

4、MERGE:一组MyISAM的集合。

5、ARCHIVE:仅支持最基本的插入和查询操作，MySql 5.5之后开始支持索引，有很好的压缩机制，经常被当做仓库使用。

8、描述一下InnoDB和MyISAM的区别？

区别	Innodb	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	即支持聚簇索引又支持非聚簇索引	只支持非聚簇索引
行锁	支持	不支持
表锁	支持	支持
存储文件	frm，ibd	frm,myi,myd
具体行数	每次必须要全表扫描统计行数	通过变量保存行数（查询不能带条件）

如何选择？

1、是否需要支持事务，如果需要选择innodb，如果不需要选择myisam

2、如果表的大部分请求都是读请求，可以考虑myisam，如果既有读也有写，使用innodb

现在mysql的默认存储引擎已经变成了Innodb,推荐使用innodb

9、描述一下聚簇索引和非聚簇索引的区别？

innodb存储引擎在进行数据插入的时候必须要绑定到一个索引列上，默认是主键，如果没有主键，会选择唯一键，如果没有唯一键，那么会选择生成6字节的rowid，跟数据绑定在一起的索引我们称之为聚簇索引，没有跟数据绑定在一起的索引我们称之为非聚簇索引。

innodb存储引擎中既有聚簇索引也有非聚簇索引，而myisam存储引擎中只有非聚簇索引。

10、事务有哪些隔离级别，分别解决了什么问题？

各种隔离级别和数据库异常情况对应情况如下：

隔离级别	脏读	不可重复读	幻读
READ- UNCOMMITTED(读取未提交)	√	√	√
READ-COMMITTED(读取已提交)	×	√	√
REPEATABLE- READ(可重复读)	×	×	√
SERIALIZABLE(可串行化）	×	×	×

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。

READ-COMMITTED(读取已提交)：事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。

REPEATABLE-READ(可重复读)：这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读。

11、描述一下mysql主从复制的机制的原理？mysql主从复制主要有几种模式？

原理：

（1）master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；

（2）slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

（3）同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

- 从库会生成两个线程,一个I/O线程,一个SQL线程;

- I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;

- 主库会生成一个log dump线程,用来给从库I/O线程传binlog;

- SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

注意：

1--master将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了数据安全考虑，slave也开启binlog功能）。

2--slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到中继日志relay log里；SQL线程负责从relay log日志里读出binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。

3--Mysql复制至少需要两个Mysql的服务，当然Mysql服务可以分布在不同的服务器上，也可以在一台服务器上启动多个服务。

4--Mysql复制最好确保master和slave服务器上的Mysql版本相同（如果不能满足版本一致，那么要保证master主节点的版本低于slave从节点的版本）

5--master和slave两节点间时间需同步

具体步骤：

1、从库通过手工执行change master to 语句连接主库，提供了连接的用户一切条件（user 、password、port、ip），并且让从库知道，二进制日志的起点位置（file名 position 号）； start slave

2、从库的IO线程和主库的dump线程建立连接。

3、从库根据change master to 语句提供的file名和position号，IO线程向主库发起binlog的请求。

4、主库dump线程根据从库的请求，将本地binlog以events的方式发给从库IO线程。

5、从库IO线程接收binlog events，并存放到本地relay-log中，传送过来的信息，会记录到master.info中

6、从库SQL线程应用relay-log，并且把应用过的记录到relay-log.info中，默认情况下，已经应用过的relay 会自动被清理purge

12、如何优化sql，查询计划的结果中看哪些些关键数据？

参考执行计划文档

13、MySQL为什么选择B+树作为它的存储结构，为什么不选择Hash、二叉、红黑树？

索引的实现原理，底层数据结构，叶子节点存储数据需要看视频了解。

索引失效的情况：

1、组合索引不遵循最左匹配原则。

2、组合索引的前面索引列使用范围查询(<,>,like),会导致后续的索引失效。

3、不要在索引上做任何操作（计算，函数，类型转换）。

4、is null和is not null无法使用索引。

5、尽量少使用or操作符，否则连接时索引会失效。

6、字符串不添加引号会导致索引失效。

7、两表关联使用的条件字段中字段的长度、编码不一致会导致索引失效。

8、like语句中，以%开头的模糊查询。

9、如果mysql中使用全表扫描比使用索引快，也会导致索引失效。

14、描述一下mysql的乐观锁和悲观锁，锁的种类？

乐观锁并不是数据库自带的，如果需要使用乐观锁，那么需要自己去实现，一般情况下，我们会在表中新增一个version字段，每次更新数据version+1,在进行提交之前会判断version是否一致。

mysql中的绝大部分锁都是悲观锁，按照粒度可以分为行锁和表锁：

行锁：

共享锁：当读取一行记录的时候，为了防止别人修改，则需要添加S锁

排它锁：当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁

	X	S
X	不兼容	不兼容
S	不兼容	兼容

记录锁：添加在行索引上的锁

间隙锁：锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别

临键锁：记录锁+间隙锁

表锁：

意向锁：在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁

自增锁：对自增字段所采用的特殊表级锁

IX：意向排它锁

S：锁定记录本身和记录之前的间隙

X,REC_NOT_GAP：只锁定记录本身

S，REC_NOT_GAP：只锁定记录本身

X，GAP：间隙锁，不锁定记录本身

S，GAP：间隙锁，不锁定记录本身

X，GAP,INSERT_INTENTION：插入意向锁

15、mysql原子性和持久性是怎么保证的？

原子性通过undolog来实现，持久性通过redo log来实现

SQL排序

一、SQL基础查询1、select语句格式：select字段from表名；2、where 用于限制查询的结果3、查询条件> < >= <= = !=4、与或（AND，OR）5、在不在（IN，NOT IN）6、在[a,b] (between val1 and val2)7、空非空(NULL,NOT NULL)8、全部任一（ALL，ANY）不能单独使用，必须与关系运算符配合9、排重DISTINCT用在字段之前二、排序1、使用 ORDER BY 语句格式：select 字段 from 表名 where 条件 ORDER BY 字段；2、设置升序降序（ASC，DESC）格式：select 字段 from 表名 where 条件 ORDER BY 字段 ASC|DESC3、多项排序格式：select 字段 from 表名 where 条件 ORDER BY 字段 ASC|DESC，字段ASC|DESC三、聚合函数注意：在使用比较运算符时NULL为最大值，在排序时也会受影响把 select 语句的查询结果汇聚成一个结果，这样的函数叫聚合函数1、MAX\MIN获取最大值和最小值，可以是任何数据类型，但只能获取一个字段2、AVG\SUM获取平均值、总和nvl(salary,0)3、COUNT统计记录的数量四、分组1、GROUP BY格式：select 组函数 from 表 group by 字段2、HAVING 组判断条件它的真假决定一组数据是否返回五、查询语句的执行顺序1、格式：select sum(salary) from 表名 where bool order by group bya、from 表名，先确定数据的来源b、where 确定表中的哪些数据有效c、group by 字段名，确定分组的依据d、having 确定组数据是否返回e、order by 对组数据进行排序六、关联查询1、多表查询select 字段 from 表1，表2 where;2、多表查询时有相同字段怎么办　 1、表名.字段名2、表名如果太长，可以给表起别名（from 表别名）3、笛卡尔积a、8条数据b、9条数据在多表查询时，一定要设置where 条件，否则将得到笛卡尔积

七、连接查询当使用多表进行关联查询时，根据设置的条件会得到不同的结果1、内连接查询：左右两边能匹配上的select last_name ,name from s_emp,s_dept where dept_id=s_dept.id2、外连接：左右两边不能匹配的数据select last_name ,name from s_emp left|right|full outer join s_dept on dept_id=s_dept.id3、左外连接匹配成功的数据+左表不能匹配的数据4、右外连接匹配成功的数据+右表不能匹配的数据5、全外连接匹配成功的数据+左右表不能匹配的数据

mysql的加锁情况

1、REPEATABLE-READ隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

createtablet(id intdefaultnull,name char(20)defaultnull);insertintot values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;select*fromt forupdate;showengineinnodbstatus\G

从返回的信息中，可以看到对表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值。

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;select*fromt whereid =10forupdate;showengineinnodbstatus\G

从上述反馈信息中，可以发现跟不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum，这到底是为什么呢？

出现supremum的原因是：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。

出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。

2、REPEATABLE-READ隔离级别+表有显式主键无索引

创建如下表并添加数据：

createtablet2(id intprimarykeynotnull,name char(20)defaultnull);insertintot2 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;select*fromt2 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，与之前的加锁方式是相同的。

2、where条件是主键字段

begin;select*fromt2 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会是id=10这条记录。

3、where条件包含主键字段和非关键字段

begin;select*fromt2 whereid =10andname ='10'forupdate;showengineinnodbstatus\G

通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

3、REPEATABLE-READ隔离级别+表无显式主键有索引

1、不带where条件，跟之前的情况类似

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

创建如下表：

createtablet3(id intdefaultnull,name char(20)defaultnull);createindexidx_id ont3(id);insertintot3 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;select*fromt3 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。

此时大家可以开启一个新的事务，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

createtablet4(id intdefaultnull,name char(20)defaultnull);createuniqueindexidx_id ont4(id);insertintot4 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;select*fromt4 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，此方式与where条件是主键字段的加锁情况相同，表无显式主键则会把唯一索引作为主键，因为是主键，所以不能再插入id=10这条记录，因此也不需要间隙锁。

4、REPEATABLE-READ隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

createtablet5(id intnotnull,name char(20)defaultnull,primarykey(id),keyidx_name(name));insertintot5 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;select*fromt5 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;select*fromt5 wherename='10'forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁

(3)where条件是主键字段

begin;select*fromt5 whereid=10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;select*fromt5 whereid=10andname='10'forupdate;showengineinnodbstatus\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

createtablet6(id intnotnull,name char(20)defaultnull,primarykey(id),uniquekeyidx_name(name));insertintot6 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;select*fromt6 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;select*fromt6 wherename='10'forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;select*fromt6 whereid=10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;select*fromt6 whereid=10andname='10'forupdate;showengineinnodbstatus\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。

5、READ-COMMITTED隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

createtablet7(id intdefaultnull,name char(20)defaultnull);insertintot7 values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;select*fromt7 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;select*fromt7 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。

6、READ-COMMITTED隔离级别+表有显式主键无索引

创建如下表并添加数据：

createtablet8(id intprimarykeynotnull,name char(20)defaultnull);insertintot8 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;select*fromt8 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（主键）

2、where条件是主键字段

begin;select*fromt8 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

3、where条件包含主键字段和非关键字段

begin;select*fromt8 whereid =10andname ='10'forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

7、READ-COMMITTED隔离级别+表无显式主键有索引

创建如下表：

createtablet9(id intdefaultnull,name char(20)defaultnull);createindexidx_id ont9(id);insertintot9 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

begin;select*fromt9 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

begin;select*fromt9 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁，

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

createtablet10(id intdefaultnull,name char(20)defaultnull);createuniqueindexidx_id ont10(id);insertintot10 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;select*fromt10 whereid =10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁。

8、READ-COMMITTED隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

createtablet11(id intnotnull,name char(20)defaultnull,primarykey(id),keyidx_name(name));insertintot11 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;select*fromt11 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加记录锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;select*fromt11 wherename='10'forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加X记录锁，对主键索引列添加X记录锁

(3)where条件是主键字段

begin;select*fromt11 whereid=10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;select*fromt11 whereid=10andname='10'forupdate;showengineinnodbstatus\G

2、表有显式主键和唯一索引

创建如下表：

createtablet12(id intnotnull,name char(20)defaultnull,primarykey(id),uniquekeyidx_name(name));insertintot12 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;select*fromt12 forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加X记录锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;select*fromt12 wherename='10'forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;select*fromt12 whereid=10forupdate;showengineinnodbstatus\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;select*fromt6 whereid=10andname='10'forupdate;showengineinnodbstatus\G

表达方式：

能给我介绍一下MySQL的锁吗？谈谈你对锁的认识？

1、MySQL锁的分类，有哪些。

2、所的应用（事务隔离级别）提高数据库的并发能力。

3、在不同的隔离别，不同的索引情况下分别添加的什么锁，关键字。

4、这是我对锁的认知，你看有什么分体吗？

做过MySQL优化吗，简单聊一下？

之前做过很多mysql优化的点，优化其实不是出现问题之后再做优化，在做优化之前会提前做-些预防，比如关于表的设计，schema的设计等相关问题，肯定是要提前准备的，但是在实际生产过程中，更多的问题可能是在运行一段时间之后出现的，我们之前一般是通过profile,profermance schema等相关方式来监控SQL语句及数据库的状态，当出现问题的时候会从执行计划、索引、sql优化，参数调整等方便来进行调优，像之前我做的XX项目中，就出现了一个XX的问题，我们通过排查，找原因最终发现是XX的问题，当添加XX或者修改XX之后就解决了，其实sq|优化最主要的是思路，我们解决问题的时候从来不是上来就优化，而是找准问题，一刀毙命。