高级java开发工程师面试题之----mysql

1、mysql是怎么执行sql语句的

客户端通过连接器链接，然后分析器分析sql，优化器youhuasql再通过执行器执行，最后更新存储引擎。

2、mysql 数据结构为什么使用B+树

      

       Mysq中的B+树是mysql存储引擎中最主要的存储结构，而在使用索引的时候，为了提高我们查询数据的查询效果，要尽可能少的从磁盘中读取数据，同时要保证读取的数据足够有效，不可能一下把数据都读到内存中，这不现实，所以要分块读取。分块读取的时候要考虑到，mysql存储系统中，磁盘和内存在进行交互的时候，是以页4K为单位的，而我们在进行数据读取的时候，一般读的都是页的整数倍，像innodb的存储引擎默认读取16KB的数据。为什么要使用B+树呢，因为不管使用二叉树、AVL树、红黑树也好，他们最终都会有一个问题，树的分支有且只有两个，当我们想在树中插入更多的数据的时候，会造成这个树的深度变深，而树的深度变深，就会导致一个问题，IO次数变多，那么查询效率就慢，所以基于这样的考虑，我们要将二叉树变成多叉树，同时要依托与前面的有序的特点，所以又了多叉有序树，也就是我们说的B树，B树的特点，在进行数据检索的时候，数据和Key值时放在一起的，这就意味着，我们每次在读取数据的时候，每一个16K的读取数据上，会有Key加实际数据，而实际数据多了之后，可能会占用大量的存储空间，从而导致树的分支范围变小，范围变小之后，从而导致想插入更多数据的时候，深度增加，因此要考虑将B树中的非叶子节点中的数据放到叶子节点中，非叶子节点中只存储Key值，而叶子节点中只存储实际的数据，所以就有了B+树。

B树和B+还有一个重要区别就是B+树的叶子节点是有双向指针的，所以可以在叶子节点可以快速从左到右查找

3、B树和B+的区别

B+树叶子节点中包含了全量的数据，而在B树中每一个节点中，数据在叶子节点和非叶子节点都有存放，

在B+树中的叶子节点是个双向链表

4、innodb 和myisam的区别

5、聚簇索引、非聚簇索引、回表、索引覆盖

        在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要通过主键信息去查询。

    回表:通过非聚簇索引叶子节点中主键的信息去查询具体数据的过程。

    索引覆盖：select 查询的字段刚好是索引字段，不需要额外数据，不需要回表查询。

6、索引下推

        索引下推指的是：筛选条件刚好是组合索引的字段，不需要额外回表查询，如果不是那么找到第一个索引时还需要根据这个索引保存的主键索引去回表查出数据，再进行另一个字段的判断筛选。

        与索引覆盖不同的是：索引覆盖可以理解为查询的字段刚好是索引字段，而索引下推是判断条件刚好是索引字段。

7、mysql主从同步原理

       保证两个master 和slave的数据一致性，基于binglog文件来完成，当master 写入数据的时候，每次操作都会生成binglog日志，binglog日志会存储到本地的磁盘区。会有另外一个线程（IO Thread）定时访问binglog，如果binglog有变化会把变化的binglog日志拿到slave的relay log（中继日志），然后slave再开一个线程 sql thread 来读取relay log 把sql语句执行到slave中

为什么要使用bingLog 和relayLog？

举个例子（1T数据 分10次拷贝快 还是 一次拷贝快  当然是一次，）

其实就是顺序IO和随机IO的问题 （ 随机IO位置不固定还得找）

写入bingLog 和relayLog都是顺序IO，sqlThread 是随机IO 比较慢，造成主从同步有延迟的根原因

8、mysql主从同步延迟怎么解决？

     什么提高从库性能、使用缓存、减小主库压力之类的就不谈了，这里给一个好的解决办法

     MTS 并行复制 ：其核心就是将sqlTread 变成并行执行，提高效率。

9、explan执行计划

       

哪些是重点字段：

id:sql复杂的时候可能会先看哪个先执行

type:

system > const > eq_ref > ref > fultext > ref_or_ null > index_merge > unique _subquery > index subquery > range > index > ALL

system级别最高 all最低 ，调优最少要达到range级别，最好是ref

key：索引字段

10、mysql 的事务隔离级别

 

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

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

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

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

11、事务的ACID怎么保证的（开始重点）

         原子性：undolog 日志+MVCC （见12，通过undolog 拿到回滚数据回滚）

        一致性：最核心和最本质的要求

        隔离性：mvcc

        持久性：redo log  见（13）

12、MVCC是什么

      要理解MVCC首先要理解什么是当前读，什么是快照读。

        当前读：最新版本的数据。

        快照读：历史版本的数据。

        多版本并行控制器：维持一个数据的多个版本，使得读写操作没有冲突，由三个隐式字段 和 undo log  read view 组成

三个隐式字段

1. 事务id，创建或最新的一次修改的该事务的id
2. 回滚指针 指向这条记录的上一个版本，配合undolog来完成回滚
3. Rowid  innodb表没有主键 会生成一个的隐藏rowid

       undo log：回滚日志

       readView: 进行快照读的时候生产读视图，做可见性判断，

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

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

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

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

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

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

​ 事务提交后，释放锁

readView：

      当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据，通过以下三个属性来判断能看到哪些版本。

readView有以下三个属性

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

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

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

        怎么才能可见( 当前undolog日志中的备份中的事务id大于最小的事务id，小于尚未分配的事务id，且不在list中)

13、事务的持久性怎么保证的？

        通过redoLog

        redo log称为重做日志，当有一条记录需要修改的时候，InnoDB引擎会先把这条记录写到redo log里面。redo log是物理格式日志，它记录的是对于每个页的修改。、

       redo log是由两部分组成的：一是内存中的重做日志缓冲（redo log buffer）;二是用来持久化的重做日志文件（redo log file）。所以为了消耗不必要的IO操作，事务再执行过程中产生的redo log首先会redo log buffer中，之后再统一存入redo log file刷盘进行持久化，这个动作成为fsync。

可以通过InnoDB提供的innodb_flush_log_at_trx_commit参数来配置。

设置为0的时候，表示事物提交的时候不写入重做日志文件持久化。
设置为1的时候，表示每次事务提交都将redo log直接持久化到磁盘
设置为2的时候，表示每次事务提交时将重做日志写入重做日志文件，但是写入的仅仅是文件系统的缓存page cache不进行fsync。

InnoDB有一个后台线程master thread，每隔一秒就会把redo log buffer中的日志文件调用write写到文件系统缓存page cache，然后调用fsync持久化磁盘。

14、mysql锁的种类

行锁：

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

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

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

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

临键锁：记录锁+间隙锁

表锁：

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

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

锁模式的含义：

IX：意向排它锁

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

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

X,REC_NOT_GAP：只锁定记录本身

S，REC_NOT_GAP：只锁定记录本身

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

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

X，GAP,INSERT_INTENTION：插入意向锁

15、select for update 

        select for update的含义是在查询数据的同时对所选的数据行进行锁定，以保证数据的一致性和并发控制。

         行锁：锁定范围是筛选的所有的行，其他行不影响。

        表锁：也就是所有的行都锁定

        怎么判断是行锁还是表锁？

        where 判断条件是索引字段还是非索引字典

        索引字段 and 索引字段：行锁

        索引字段 and 非索引字段：行锁

        索引字段 or 索引字段：行锁

        索引字段 or 非索引字段：表锁

        以上判断都是能查询到数据，查不到数据不上锁。

即便判断是行锁也有可能上的是表锁（当innodb认为全表扫描效率更高）

 

        

16、死锁怎么产生的，怎么解决

表锁死锁
产生原因：
用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A–》A表（表锁）–》B表（表锁）
用户B–》B表（表锁）–》A表（表锁）

解决方案：
这种死锁比较常见，是由于程序的 BUG 产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

行锁死锁
产生原因：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案：
SQL 语句中不要使用太复杂的关联多表的查询；使用 explain “执行计划"对 SQL 语句进行分析，对于有全表扫描和全表锁定的 SQL 语句，建立相应的索引进行优化。

2.3 共享锁转换为排他锁
产生原因：
事务A 查询一条纪录，然后更新该条纪录；此时事务B 也更新该条纪录，这时事务B 的排他锁由于事务A 有共享锁，必须等A 释放共享锁后才可以获取，只能排队等待。事务A 再执行更新操作时，此处发生死锁，因为事务A 需要排他锁来做更新操作。但是，无法授予该锁请求，因为事务B 已经有一个排他锁请求，并且正在等待事务A 释放其共享锁。

解决方案：

（1）对于按钮等控件，点击立刻失效，不让用户重复点击，避免引发同时对同一条记录多次操作；

（2）使用乐观锁进行控制。乐观锁机制避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统性能。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造成脏数据被更新到数据库中。
 

                

持续更新中~~~~