Mysql高频问题(附答案)

水深无声 2022-09-08 03:49 150阅读 0赞

> 持续更新中

### 一、什么是脏读、幻读、不可重复读？要怎么处理？ ###

这些问题都是Mysql进行事务并发控制时经常遇到的问题。

*  脏读：在事务进行过程中，读到了其他事务未提交的数据
 *  不可重复度：在一个事务过程中，多次查询的结果不一致
 *  幻读：在一个事务过程中，多次查询结果不一致

处理的方式：加锁、事务隔离、MVCC  
脏读、不不可重复读加共享、排他锁；幻读加范围锁

### 二、事务的基本特性(ACID)有哪些？ ###

事务：表示多个数据操作组成一个完整的事务单元，这个事务内的所有数据操作要么同时完成，要么同时失败  
特性：

*  原子性：事务是不可分割的，要么完成成功，要么完成失败
 *  一致性：事务无论是完成还是失败，都必须保存事务内操作的一致性(比如转账前后双方总金额不变)。当失败时，都要对前面的操作进行回滚，不管途中是否成功
 *  隔离性：当多个事务操作一个数据的时候，为防止数据损坏，需要将每个事务进行隔离，互不干扰
 *  持久性：事务一旦提交，所做的修改就会永久保存到数据库中

### 三、事务的隔离级别有哪些？ ###

show variables like 'transation&'; 		查看事务隔离级别
    set transation level xxx; 				设置下次事务隔离级别
    set session transation level xxx;		设置当前会话隔离级别
    set global transation level xxx; 		设置全局事务隔离级别

五种隔离级别，级别越高，事务并发性越低：

*  None：不使用事务
 *  Read Uncommited：允许脏读。当事务A更新B数据，事务C也可以读B数据，若A发生回滚则产生脏读
 *  Read Commited：防止脏读，但会出现不可重复读。当事务A更新B数据为b1并提交，事务C第一次读取B得b1；此时事务C尚未提交，事务D更新B数据为b2，事务C再次读取B数据为b2，发生前后不一致则产生不可重复读。原理是每个事务都会产生readview
 *  Repeatable Read(默认)：防止脏读和不可重复读，可能出现幻读。原理是每个事务共用同一readview。当事务A查询到5行数据，此时事务B插入2行数据，事务A在接下来得查询中就会发现多了2行数据。
 *  Serializable：事务串行(每行加锁，可导致超时和锁竞争)，可防止脏读、幻读，不可重复读

### 四、ACID靠什么保证 ###

> myisam存储引擎不支持事务，innodb这样的存储引擎级别有undolog,redolog，mysql级别有binlog

ACID是指数据库中事务的基本特性：原子性、一致性、持久性和隔离性。

*  A：原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的SQL
 *  C：一致性由其他三大特性保证，程序代码要保证业务上的一致性
 *  I：隔离性由MVCC来保证
 *  D：持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

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

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

### 五、MVCC是什么？ ###

> 理解版本链和Read View就理解了MVCC

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

MVCC只在Read Commited和Repeatable Read 隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为Read UnCommited总是读取最新的数据行，而不是符合当前事务版本的数据行(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

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

### 六、索引的基本原理 ###

索引用来快速地寻找哪些具有特定值的记录。若没有索引，查询时遍历整张表。  
索引的原理就是把无需的数据变为有序的查询

*  把创建了索引的列的内容进行排序
 *  对排序结果生成倒排表
 *  在倒排表内容上拼上数据地址链
 *  在查询时，先霸道倒排表内容，再取出数据地址链，从而拿到数据

### 七、B树和B+树的区别，为什么Mysql索引默认使用B+树 ###

B数特点：

*  节点是排序的
 *  一个节点可以存多个元素，多个元素也排序了

B+树特点：

*  拥有B树的特点
 *  非叶子节点不存储数据，只进行数据索引
 *  相邻叶子节点之间有指针
 *  叶子节点存储了所有数据，并且排好序

为什么Mysql使用B+树？

*  索引是用来加快查询，而B+树通过对数据进行排序所以可以提高查询速度
 *  一个节点中可以存储多个元素，从而可以使得B+树的高度不会太高，
 *  在mysql中一个Innodb页就是一个B+树节点，一个Innodb也默认16kb，所以一般情况下一颗两层的B+树可以存2000万行左右数据
 *  B+树叶子节点存储了所有数据并排好序，并且叶子间有指针，可以很好的支持全表扫描，范围查找等SQL语句。

另外可以使用hash索引，对于单条记录查询时可以选择哈希索引，查询性能最快

### 八、Mysql慢查询如何优化？ ###

1、是否利用索引  
2、是否最优索引  
3、是否有多余字段  
4、是否表大、库大  
5、是否宿主机性能欠缺  
主要通过explain分析SQL语句

### 九、Explain作用及各字段意思 ###

通常我需要开启慢查询找出耗时较多的sql语句，并使用explain 执行一次分析该语句被优化的地方，比如sql执行的顺序、索引类型、SQL语句中两个SELECT子查询是否优化成join，可通过id是否一样判定等

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>查有序递增编号，有几个select就有几行，值越大优先执行，id为null最后执行</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>表示查询中每个select子句查询类型，如普通查询、union(联合查询)、子查询</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>查询的表名</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区信息</td> 
  </tr> 
  <tr> 
   <td><code>type</code></td> 
   <td>分析索引有无命中，索引扫描多少行。const(索引一次命中，匹配一行数据)、system(表仅一记录，相当于系统表)、eq_ref(唯一性索引扫描，对每个索引键，表中只有一条记录与之匹配)、ref(非唯一性索引扫描，返回匹配某个值的所有)、range(只检索给定范围的行，使用一个索引选择行，一般用于between、&gt;、&lt;)、index(只遍历索引树)、ALL(表示全表扫描，此类型查询性能最差之一，表数据越多执行效率越慢)</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可能用到的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际上用到的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>实际使用到索引的长度</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>当使用索引列等值查询时，与索引列进行等值匹配的对象信息</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>预估的需要读取的记录数</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>某个表经过条件过滤后剩余记录条数百分比</td> 
  </tr> 
  <tr> 
   <td><code>Extr</code></td> 
   <td>一些额外信息，比如using index(覆盖索引扫描、效率高)、using where(使用过滤，效率较高)、using temporary(出现临时表，效率低)、using filesort(需外部排序，效率低)</td> 
  </tr> 
 </tbody> 
</table>

### 十、InnoDB如何实现事务的？ ###

Innodb通过BufferPool、LogBuffer、redolog、undolog来实现事务，以update语句为例

*  Innodb收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool
 *  执行update语句，修改Buffer Pool中的数据，也就是内存中的数据
 *  针对update语句生成一个redolog对象，并写入LogBuffer
 *  针对update语句生成undolog日志，用于事务回滚
 *  如果事务提交，那么则把redolog对象进行持久化，后续还有其他机制将BuffLog所修改的数据页持久化到磁盘中
 *  如果事务回滚，则利用undolog日志进行回滚

### 十一、什么是二叉树、AVL数、红黑树、B-树 ###

二叉树：每个节点最多有两个子节点，左子节点比节点小，右子节点比节点大。  
满二叉树：叶子节点与叶子节点之间的高度差为 0 的二叉树，即整棵树是满的，树呈满三角形结构。  
完全二叉树：例如二叉树的深度为 k，除第 k 层外，其他各层的节点数都达到最大值，且第 k 层所有的节点都连续集中在最左边。  
AVL树：任意节点的两个子树高度差最大为1  
红黑树：每个节点非红即黑；根为黑；叶子节点都是黑色空节点；红色节点的父子节点必须为褐色；任意节点到每个叶子节点的所有路径都包含黑色节点。  
B-树：每个非叶子节点子节点都不会超过B-树的阶；叶子节点都在同一层；节点关键字按递增顺序排列

### 十二、聚簇索引和非聚索引是什么？ ###

聚簇索引就是数据和索引在一起，例如Innodb使用idb文件记录表记录和索引，找到索引就等于找到记录。  
非聚簇则相反，例如MyISAM使用MYD和MYI文件分别存储表记录和表索引；叶子节点存放的数据是记录(行)地址，再根据行地址从盘中找数据  
聚簇索引优点：

*  仅需一次查询，效率高
 *  数据是按大小排序，适合范围查找、排序  
    聚簇索引缺点：
 *  维护索引昂贵，特别是插入行、更新主键导致要分页的时候
 *  表记录和索引在一起，载入内存特别大，涉及排序、全部扫描、count等操作需要在内存中进行

### 十三、MyISAM和InnoDB区别 ###

MyISAM：

*  不支持事务、每次查询都是原子
 *  支持表级锁，每次操作对整表加锁
 *  存储表的总行数
 *  使用非聚簇索引

InnoDB：

*  支持ACID的事务，支持事务的四种隔离级别
 *  支持行级锁及外键约束，因此支持写并发
 *  不存储总行数(查询总行数会进行全部扫描)
 *  使用聚簇索引

### 十四、回表和覆盖索引 ###

如果一颗索引树上就可以获取SQL所需的所有列，就不需要回表查询，提升查询速度  
索引覆盖就是SQL所要查询的字段在当前索引对应的字段中都包含了，那么此SQL走完索引后不用回表了，所需要的字段在当前索引的叶子节点上存在，可直接作为结果返回。 实现覆盖索引最简单的方式就是将查询的字段，全部建立到联合索引中

### 十五、最左前缀原则是什么 ###

当一个SQL想要利用索引，就一定要提供该索引对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sql时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,c三个字段的联合索引时，底层的B+树是按照abc三个字段从左到右取比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则。

### 十六、索引类型及对数据库的影响 ###

普通索引：字段值允许重复，一表可多个  
唯一索引：字段值不允许重复，一表可多个  
主键：字段值不允许重复，一表一个  
联合索引：多个字段组成索引  
全文索引：通过建立倒排索引，提升检索速度，解决字段包含问题，例如github的检索

优点：索引可以提升查询速度(表连接、分组和排序)：使用索引，可使用Mysql底层的优化隐藏器，提高系统性能。  
缺点：索引额外占用物流空间，若是聚簇索引则占用更多，当索引所在列频繁发生变更，将致索引的变更。

### 十七、索引应用场景 ###

建议使用

*  在经常需要搜索的列上(经常把做查询条件的字段)，可以加快检索速度
 *  在作为主键的列上，强制该列的唯一行和组织表中数据和排列结构
 *  在经常用在连接的列上，这些列主要为外键，可加快连接速度
 *  在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
 *  在经常需要排序的列上，因为索引已排序，查询时利用索引排序加快排序查询时间
 *  在经常使用where子句中的列上面创建索引，加快条件的判断速度

避免使用

*  列很少被查询
 *  表数据量小
 *  大数据类型(text，blob)
 *  经常被修改的列

### 十八、Mysql的锁有哪些？ ###

*  行级锁(Innodb)：锁住某行，粒度小并发高
 *  表级锁(myisam)：锁住整表，力度大并发低
 *  共享锁(读锁)：一个事务给某行(表)加了读锁，其他事务也可读不可写。(select查询不加任何锁，若想加则select xxx Lock IN SHARE MODE)
 *  排他锁(写锁)：一个事务对某行(表)加了写锁，其他事务不可读写。innodb会对insert/delete/update语句自动加排他锁。(若想查询获得排他锁则select xxx for update)
 *  自增锁：针对自增字段，如有事务，数据回滚，自增不回滚。
 *  意向锁：innodb自动添加的一种锁，不需要用户干预
 *  全局锁：flush tables with read lock；加锁之后整个数据库实例都处于只读，数据变更操作被挂起。一般用于全局备份
 *  乐观锁：并不会真正去锁，而是通过版本号实现
 *  悲观锁：行、表锁都是悲观锁

常见锁算法：比如表记录有userid(1,4,9)

*  记录锁：当记录存在时会加记录锁，比如update set xxx where userid=4，锁一条记录
 *  间隙锁：当RR(repeatable read)隔离级别下，记录不存在时，会把该记录到后继范围加锁，比如update set xxx where userid=5,锁(4-9).防止幻读
 *  next-key：间隙锁+右记录锁。(4,9\]

在事务的隔离级别实现中，就需要锁来解决幻读。

### 十九、如何进行分库分表 ###

解决单服务器访问压力和存储压力  
解决单表负载过大的问题  
数据切片模式：

*  垂直：专库专用，不同的库（主机）分担不同的业务，由一库x个业务=>（一库一个业务）\*x
 *  水平：数据行切分(分片)，由一个表的x条数据=>分散到多个库中同名表中。客户端操作网关(非数据库)，由网关决定往哪个数据库存取

### 二十、消息队列的作用 ###

解耦：使用消息队列作为两个系统的通讯方式，彼此松耦合  
异步：仅需给队列发送消息即可，无需关注是否消费、谁消费  
流量削峰：消息在队列排队，消费者可自行控制消费速度

### 二十一、Mysql主从同步原理 ###

Mysql主从复制涉及三个线程：主库的binlog dump线程，从库的IO线程，SQL线程  
1、主库使用binlog记录数据变更记录，当binlog的position有变动时，binlog dump线程读其内容发送给从节点。  
2、从节点IO线程接收到binlog内容，写入relaylog文件  
3、从节点SQL线程读取relaylog文件对数据操作进行重放，最终主从库保持一致性  
4、如从节点宕机发生重启，则会从position位置发起同步

### 二十二、二叉搜索树和平衡二叉树什么关系？ ###

二叉搜索树，每个节点值大于左孩子，小于右孩子。  
平衡二叉树，又被平衡二叉搜索树，任意节点的左右两个子树的高度差不超过1。拥有二叉搜索树特性。

### 二十三、强平衡二叉树和弱平衡二叉树区别？ ###

强平衡二叉树(AVL树)，弱平衡二叉树(红黑树)  
1、AVL树比红黑树对于平衡的程度更严格，在相同节点的情况下，AVL树的高度低于红黑树  
2、红黑树中增加了一个节点颜色的概念  
3、AVL树的旋转操作比红黑树的旋转操作更耗时

### 二十四、epoll和poll的区别 ###

1、select模型：使用的是数组来存储socket连接文件描述符，容量是固定的，需要轮询判定是否发生了IO事件  
2、poll模型：使用的是链表存储socket连接文件描述符，容量是不固定的，需要轮询判定是否发生IO事件  
3、epoll模型：事件通知模型，发生IO事件时，应用程序才进行IO操作，不需要主动轮询