面试准备-数据库篇

1. InnoDB和MyISAM的区别？

InnoDB与MyISAM的区别：

• InnoDB支持事务，而MyISAM不支持事务
• InnoDB支持行级锁和表级锁，而MyISAM只支持表级锁
• InnoDB和MyISAM底层都使用的B+树结构，但是InnoDB使用的是聚集索引，也就是叶子节点存放的是文件真实的数据，而MyISAM存放的是数据的地址
• InnoDB支持外键，而MyISAM不支持外键

2. Mysql中的各种索引

• 普通索引：最简单的索引，只是为了加快查询，使用create index语句创建
• 唯一索引：和普通索引不同的是，索引列的值只能出现一次，即必须唯一，允许空值
• 主键索引：特殊的唯一索引，不允许有空值，并且主键索引可以作为外键，而唯一索引不行
• 全文索引：仅是在MyISAM存储引擎下使用，用于在一篇文本中检索全文的信息
• 组合索引(联合索引、复合索引)：指对表上多个列进行索引，索引的原则是首先使用多列索引的第一列构建索引，在第一列相同的情况下，接着使用第二列，依次创建，所以有最左前缀匹配原则
• 聚族索引/非聚族索引：聚族索引叶子节点存放的是数据本身，而非聚族索引叶子节点存放的是数据的地址，使用聚族索引的方式减少了磁盘io的时间，因为数据直接被取出来在内存中
• 覆盖索引/回表：如果select到的列都在索引中能够直接获取，不必在数据库中获取，那么就是索引覆盖，而会表是指如果select到的列不全在索引中，那么就要通过索引回到原表找到相应列的值，回表的效率是很低的，所以一般不推荐使用select *
• 哈希索引：只有Memory支持哈希索引，哈希索引是基于哈希表实现，只有精确匹配索引列的查询才有效，哈希表的查询效率非常高，但是如果有范围查询，那么哈希表的查询效率将会非常低下
• 倒排索引：倒排索引支持非常高效的检索功能，ES中使用的便是倒排索引

3. SQL优化的步骤

一般步骤：

1. 首先查看sql的执行频率，使用show status 可以查看各个查询类型的语句的执行频率

2. 定位低效率的执行sql，查看慢查询日志，通过慢查询日志定位执行效率比较低的sql语句

3. 找到语句之后，使用explain分析效率比较低的SQL语句

4. 使用show profile分析sql，showprofile可以帮助我们了解到时间都耗费到那个步骤上了

5. 最后可以使用trace对sqll进行跟踪，通过trace文件可以进一步的了解到优化器为什么选择这个计划而不选择另外一个计划

总体步骤就是：查看sql执行频率(show status) --> 查看慢查询日志定位sql --> explain分析sql执行计划 --> show profile查看时间消耗在那一部分上 --> trace文件进一步了解到优化器选择这个优化方案的原因

4. SQL优化的一些规则

• 使用索引

• 最左前缀规则

• 范围查询右边的列，不能使用索引

• 不能再索引列上进行运算操作，所以失效

• 使用索引覆盖，避免select *，索引覆盖就是值查询的列都在索引中，如果有查询的列不在索引中，那么要进行回表查询该数据

• 使用or连接的两个条件，如果or前的列有索引而or后的列无索引，那么都不走索引

• 以%开头的like模糊查询，索引实现，仅仅是尾部模糊匹配，索引不失效，而头部模糊匹配索引失效

• Mysql评估使用索引比全表更慢，不使用索引

5.事务的四大特性

ACID，相信了解过事务的同学都知道这四大特性，我们具体说说：

• 原子性：指事务的操作要么一起成功，要么一起失败，最简单的还是银行转账的例子，张三向李四转100元，张三的钱要减少，李四的钱要增加，这两个操作必须是一体的，不可被分割的
• 一致性：指在执行事务前与执行事务后，数据库中的数据状态保存一致，可以简单的理解为转账前整个数据库的数据和转账后整个数据库的数据没有发生变化
• 隔离性：指两个事务之间操作是被隔离的，互不干扰的，后面的四种隔离级别中会详细介绍隔离性
• 持久性：指执行完事务后数据的状态是持久的，数据库不会发生丢失等等

Mysql中的原子性、一致性和持久性是通过事务日志实现，而隔离性是通过锁机制、MVCC进行实现

6. Mysql中的隔离级别

先来聊一聊在并发事务的情况下，有可能会出现什么问题？

• 脏读：指读到了别的事务还没有提交的数据
• 不可重复读：指在事务的执行过程中，对同一数据读取了两次，两次结果不一样
• 幻读：指在事务的执行过程中，事务重复数据，在获得数据的时候多次读取的数据行数不一样

不可重复读重点是在修改，意思是在同一事务中，同样的条件下，两次读取的结果不一样，因为中间被事务修改，而幻读重点是在插入和删除，意思是在同一事务中，同样的条件下，两次读取出来的记录数不一样，因为中间被其他事务插入或者删除过数据

SQL标准中定义了四种隔离级别，下面分别来介绍：

• 读未提交(RU)：事务可以读取到其他事务没有提交的数据，在我的经验中，应该没有人会使用这种隔离级别，这会产生上面的所有问题，但是在特定环境下，例如在debug的时候可以暂时将隔离级别设置为读未提交，这样便于提交，不过debug完一定要设置回来
• 读已提交(RC)：这是大多数数据库默认的隔离级别，但是不是mysql的默认隔离级别，读已提交是指事务能看到其他事务已经提交过的数据，这个级别可以解决脏读，但是不能解决不可重复读，当有交叉事务的时候，如果其他事务commit了，那么该事务两次读取的数据就不一样
• 可重复读(RR)：这是Mysql默认的隔离级别，解决了RC的不可重复读的问题，指在同一个事务中，会看到相同的数据，但是没有解决幻读的问题
• 可串行化(Serializable)：这是最高的隔离级别，相当于Java中的Synchronized(没有优化前的)，也就是说对数据库中的操作，同一时间内只能有一个事务，这样多个事务之间肯定不会有冲突，从而解决了幻读的问题，但是锁的效率很低，导致超时先现象等等

7. MVCC的实现原理

MVCC我认为是一种乐观锁的实现，MVCC是通过保存数据在某个时间点的快照来实现的，MVCC通过在每行记录后面保存两个隐藏的列来实现的：一个行的创建时间、一个行的过期时间，每行的数据都要一个版本，每次数据更新的时候都要更新这个版本，然后多个事务复制当前拿到的版本进行修改，这就保证各个事务之间无干扰，然后到保存的时候就比较版本号，如果成功，就覆盖原先的数据

8. 事务的实现原理-三大日志的作用

在Mysql中有三种比较重要的日志binlog、undolog、redolog，事务的实现原理便是靠三大日志：

• binlog是二进制日志，主要是用于复制、是存在于Mysql中的serve层的，利用binlog可以主从复制
• undo log是回滚日志，是在事务开始之前就已经执行写入了，是一个逻辑日志，就是说如果执行一个insert操作，在undo log里面写入一个delete操作，保证事务的原子性
• redolog是重做日志，是一个物理日志真正记录了页面的修改信息，Innodb中有一个重做日志文件组，一般这个组下有两个重做日志文件，写入的时候是循环写入的，当一个日志文件写满的时候在写入另外一个日志文件，保证了事务的持久性

事务具有四大特性，原子性、隔离性、持久性、一致性。

• 原子性的实现基础是靠undo log(回滚日志)，undo log属于逻辑日志，记录的是sql执行的相关信息，当发生回滚时，Innodb根据undo log的内容做相反的工作
• 持久性的实现基础是靠redo log(重做日志)实现的。因为Mysql的数据是存在磁盘里面的，但是如果每次读写数据都要进行磁盘IO，效率将会变得非常低，因为Mysql维护了一个Buffer Pool，也就是在磁盘与mysql之间维护一个缓冲区域，这样在读写的时候先在Buffer Pool里面进行，如果没有再在数据库里面读取，但是如果Mysql宕机，而Buffer Pool里的数据还没有写入磁盘，将会导致事务的持久性问题。redo log(重做日志)就可以保证在宕机的时候重新恢复数据，也就是当Mysql将数据写入Buffer Pool之前现在重做日志上记录一份
• 隔离性的实现原理与锁相关 和MVCC进行的

9. Mysql是如何保证不幻读的

加锁或者是MVCC保证不幻读的

10. Mysql的备份

Mysql备份的好处：

• 灾难恢复、容灾处理，在遇到系统故障或者是宕机的时候可以恢复数据
• 需求改变：因为需求改变了，而要把数据恢复到之前
• 测试：可以用来测试新功能是否可用

Mysql备份的方式：

• 热备份(Backup)：指在数据库的运行时进行直接备份，对正在运行的数据库没有影响
• 冷备份：指备份操作是在数据库停止的情况下进行的，这种备份最为简单，只需要复制相关的数据库物理文件即可
• 温备份：指在数据库运行时进行的，可以保证备份的数据一致性，但是会影响数据库的操作，要保证数据的一致性可以加一个全局锁

11.主从复制

为什么要做主从复制：

• 读写分离，在一个比较大的业务系统中，如果有一条语句需要进行update操作，那么就有可能需要锁表，那么就会导致暂时不能读的服务，那么使用主从复制就可以做到让主库负责写、从库负责写，这样就保证业务的正常运行
• 做数据的热备份
• 架构的扩展，当业务越来越大的时候，如果一台mysql机器支持不住可以使用多台机器

主从复制的原理：

首先前提是主服务器开启二进制日志文件(记录了整个mysql的变更信息)，主从复制的原理是：

1. 主服务器将数据变更的操作记录到二进制日志文件上
2. 从服务器开启IO线程获取到主服务器上的二进制日志文件，将这个binlog日志的内容依次写入到从服务器的中继日志文件上
3. 从服务器端的SQL线程会实时的检测中继日志文件中新增的内容，然后将这些内容解析成sql语句，并且在自身服务器上执行sql语句

主从复制的基本原则：

• 每个从服务器只有一个主服务器
• 每个从服务器只能有一个服务ID
• 每个主服务器可以有多个从服务器

12. 为什么推荐主键使用自增的整形

• 首先为什么要主键，Innodb底层使用的是B+树索引，需要选择一个列作为索引
• 为什么要自增：自增的特点是有顺序，那么当插入一条数据的时候，只需要向B+树的叶子节点后面添加数据，而不需要B+树为了保持有序而进行旋转这些操作
• 为什么要整形：整形作为索引，容易直接判断大小而保持有序，而如果使用uuid，那么判断大小的时候比较麻烦，效率更低

使用主键自增会有什么问题？

• 在单表单库的情况下没有问题，但是如果需要进行拆库的话，可能会造成id值重复
• 在高并发的场景下，如果存在大量的写入，因为使用主键自增，MySQL的Innodb引擎会在内存中保存一个计数器来记录自增值，每当插入一个新的数据的时候，就会用一个表锁来锁住这个计数器，如果是高并发的常见下会造成sql堵塞，影响性能

我觉得可以将主键值控制在应用层，可以采用一些分布式全局ID算法，例如雪花算法或者是使用一些中间价例如zookeeper来实现分布式全局id

13. 死锁现象

当多个事务同时持有和请求同一资源上的锁的而产生了循环依赖的时候就产生了死锁现象

如何定位死锁：

①show engine innodb status 命令查找最后出现死锁的sql语句，然后进行分析sql语句，可以走分析sql语句的那一套流程

②可以试着降低隔离级别，对于读的操作可以不用加锁

③可以尝试使用表级锁，让事务顺序执行

④实在不行，就先kill掉该进程

14.常见数据类型 char ？ varchar？

MySQL的数据类型大致可以分为三类：数值、日期/时间和字符串类型

• 常见的数值有：tinyint（1字节）、int（4字节）、float（4字节）、double（8字节）

• 常见的日期类型：time（3字节）、timestamp时间戳类型，4个字节）

• 常见的字符串类型：char（0-255字节）、varchar（0-65535字节 变长）、Text

varchar和char之间的区别？

char是定长字符串，当输入的字读不足的时候会自动在右边补齐空格，varchar是可变长字符串，因为是可变长度，所以需要一段空间来存储当前字符串的长度，在存储空间上varchar更优，但是在短字符的查询效率上char更优秀，因为varchar的话，磁盘还需要寻找存储长度的这片空间，所以磁盘IO要比char更多。char一般适用于定长的字符串例如电话号码、身份证等等。我在项目中对于0-10bit的数据都采用char，大于10的采用varchar

int(10)代表什么含义？

int的数据类型是4个字节，int(10)只是展示字符的长度，仍然是占用4个字节，存储范围不变，会添加前置0，一般用于报表

15.MySQL中的各种锁

当数据库有并发事物的时候，可能会出现数据的不一致，这个时候就需要锁机制去保证访问的次序，MySQL中Innodb存储引擎有表级锁和行级锁：

• 表级锁是指在在并发操作的时候，直接将整张表锁住，这种锁的粒度很大，但是开销小，加锁快，不会出现死锁
• 行级锁是指将操作的这一行锁住，加锁的粒度很小，但是开销大，加锁慢，会出现死锁

然后又有共享锁和排它锁：

• 共享锁，也叫读锁，当用户进行数据的读取的时候，对数据加上共享锁，可以同时加上多个共享锁，加上共享锁后，该事物只能对数据进行读取而不能修改，并且其他事物值呢加共享锁
• 排它锁：也叫写锁，当用户要进行数据的写入时候，对数据加上排它锁，排他锁智能加一次，其他事务不能对该数据加任意锁

为了让行级锁定和表级锁定共存，Innodb又有意向锁，比如说事务A锁住了表中的一行，只让读不让写，而事务B用表级锁锁住了整个表，可写，那么两个事务对那一行的操作就是冲突了，于是就有意向锁：

• 意向共享锁：事务要想获取某些记录的共享锁，需要在表上加上意向共享锁
• 意向排它锁：事务要想获取某些记录的排它锁，需要在表上加上意向排它锁

然后，Mysql的行锁有三种算法：

• Record锁：给单条记录上锁
• gap锁：间隙锁，锁定一个范围，但是不包含记录本身
• next-key锁：锁定一个范围，包含记录本身

16.三级封锁协议

事务一共有四种隔离级别，本质上都要通过加锁来实现，而什么时候加这个锁，加锁的时间这个就是由封锁协议定义的，三级封锁协议一共分为三级：

• 一级封锁协议：事务T在修改数据R之前必须加上写锁，直到事务结束才释放，不能保证脏读和可重复读
• 二级封锁协议：在一级封锁协议的基础上，事务T在读取数据R之前必须加上S锁，可以保证脏读，但是不能保证可重复读
• 三级封锁协议：在二级的基础上，事务在读取数据之前必须先加伤S锁，直到事务结束才能释放，可以解决可重复读的问题

17.CAP理论

CAP理论是解决分布式系统问题的基础，分别是一致性、可用行、分区容忍性：

• 一致性：是指写操作之后的读操作可以读到最新的数据，例如对于商品系统数据库做了一主一从，并且是主书籍负责写，从数据库负责读，当执行一个写操作之后，要在从数据库得到最新的数据，那么就可以在同步的时候对从数据库加锁，待同步完成之后在释放锁
• 可用性：是指任何的操作都可以得到响应结果，且不会出现响应错误或者超时，同样是上面的例子如果对从数据库加锁对出现错误响应。那么可用性的要求是不可以加锁，那怕给我返回旧数据也可以
• 分区容忍性：网络分区在不同的机器上，可以会因为网络问题出现节点之间不能通信，而分区容忍性要求还好可以对外提供服务，一本来说分区容忍性是分布式系统提供的最基本的要求

在分布式应用场景中不可能出现同时具备CAP三种特性，在Zookeper中保证的是CP，就是一致性和分区容忍性，因为在任何时候对Zookeeper访问数据都是一致的，但是它不能保证每一次服务都是可用的，例如在master节点故障从而需要重新选取master节点的时候，整个服务都是不可用的

18.柔性事务

对应着刚性事务，刚性事务就是指遵循ACID原子，强一致性的表现，而柔性事务则是Base理论的体现，前面说过CAP理论三种只可兼得其二，而BASE则是对CAP中一直性和可用性权衡之后的结果，牺牲强一致性来来获得可用性，允许系统中部分不可用上但是要保证核心功能可用，允许数据在一段时间内不是最新的，但是最终达到一致的状态，BASE规定三个概念：

• 基本可用：当分布式系统出现故障的时候，允许部分功能不可用，但是要保证核心的功能是可用的
• 软状态：因为不要求强一致性，所以可以存在中间的状态，这个状态不影响整个系统的使用
• 最终一致性：因为有中间数据，但是经过一段时间之后，各个节点上的数据要达到最终的一致性

19.分布式事务解决方案

在分布式系统下，对于各个模块可能分布在不同的机器上，例如库存模块和订单模块，当用户购买之后需要将库存模块减少和订单模块增加，这使用单机的事务是无法解决的，所以就需要分布式事务，分布式事务一共有以下几种解决方案：

• 2PC：两阶段提交，首先是有两个角色事务管理器和参与者，在准备阶段：事务管理器给每一个事务参与者发送准备消息，每个事务参与者在本地执行事务，这个时候事务没有提交了；在提交阶段，如果事务管理器收到了某一个事务参与者的失败消息，则给每个参与者发送回滚否则进行提交。实现2PC的一般有XA，XA是指数据库本身支持的分布式事务，问题就是需要等待两个阶段提交之后才释放资源锁，性能比较差