面试题总结 - 数据库

一. 分布式数据库的数据一致性怎么保证

1. 通过悲观锁的 for update
2. 通过乐观锁的加version 字段，然后不断的检查
3. 针对秒杀系统，可以把并发请求放到队列中，一个一个处理
4. 通过redis 实现事务

二. MySQL主从复制什么原因会造成不一致，如何预防及解决？

• 原因

1. 人为原因导致从库与主库数据不一致（从库写入）
2. 主从复制过程中，主库异常宕机
3. 设置了ignore/do/rewrite等replication等规则
4. binlog非row格式
5. 异步复制本身不保证，半同步存在提交读的问题，增强半同步起来比较完美。 但对于异常重启（Replication Crash Safe），从库写数据（GTID）的防范，还需要策略来保证。
6. 从库中断很久，binlog应用不连续，监控并及时修复主从
7. 从库启用了诸如存储过程，从库禁用存储过程等
8. 数据库大小版本/分支版本导致数据不一致？，主从版本统一
9. 备份的时候没有指定参数 例如mysqldump --master-data=2 等
10. 主从sql_mode 不一致
11. 一主二从环境，二从的server id一致
12. MySQL自增列 主从不一致
13. 主从信息保存在文件里面，文件本身的刷新是非事务的，导致从库重启后开始执行点大于实际执行点
14. 采用5.6的after_commit方式半同步，主库当机可能会引起主从不一致，要看binlog是否传到了从库
15. 启用增强半同步了（5.7的after_sync方式），但是从库延迟超时自动切换成异步复制

• 预防和解决的方案有

16. master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
17. slave:master_info_repository=“TABLE”&relay_log_info_repository=“TABLE”&relay_log_recovery=1
18. 设置从库库为只读模式
19. 可以使用5.7增强半同步避免数据丢失等
20. binlog row格式
21. 必须引定期的数据校验机制
22. 当使用延迟复制的时候，此时主从数据也是不一致的（计划内），但在切换中，不要把延迟从提升为主库哦~
23. mha在主从切换的过程中，因主库系统宕机，可能造成主从不一致（mha本身机制导致这个问题

三. mysql为什么用b+树 不用b树

3.1 什么是b树

b树 是一种m叉树，节点都是有序的，吸收了搜索树的特点， 每个节点存储指针 指向下面的节点，索引的值，以及一个data数据，就是一行数据

3.2 什么是b+树

b+树的非叶子节点存储索引，叶子节点存储data，并用链表连接起来，都是有序的。

3.3 为什么用b+树

因为b树每个节点存储data，而每个页的空间是有限制的，导致存储的索引比较少，这样树的深度会变深，磁盘io的次数变多，读取效率变差。

b+树由于节点存储的都是索引，所以可以极大的减少io效率，每次查询的时间也比较一致，b树搜索可能在非叶子节点结束。也可能到最下面的层才能查找到数据。

四. 主键id为什么自增？

因为如果不是自增的状态，插入或者删除数据的时候会造成页分裂或者页合并，造成io的开销，如果是自增的，每次直接追加数据就好了。

五. MyISAM 与innodb的区别

1. innodb 的数据与索引在一个文件内（聚簇索引，是一种存储结构），MyISAM的数据与索引分别在不同文件（非聚簇索引）
2. innodb 的b+树中叶子结点存储的是一行数据，MyISAM叶子节点存储的是地址，根绝地址可以找到那一行数据
3. innodb支持表锁，以及行锁。MyISAM 支持表锁，不支持行锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限
4. innodb支持事务，MyISAM不支持事务
5. InnoDB 支持外键，而 MyISAM 不支持

五. 索引

1. 主键索引
   主键是一种唯一性的索引，必须指定primarykey,每个表只能有一个索引
2. 唯一索引
   索引列的值只能出现一次，必须唯一可以为空
3. 普通索引
   基本的索引类型，值可以为空，没有唯一性的限制
4. 全文索引
   全文索引的类型是fulltext,可以在varchar char text类型的列上创建

就像搜索一篇文章出现哪个单词之类的
5. 组合索引
多列值组成一个索引，专门用来组合搜索。

六. 回表

当用的普通索引查找数据时，叶子节点存储的是主键的值。所以还需要再去主键的b+树查找值。这样就叫做回表。如果没有主键，那么会按照唯一索引，如果没有唯一索引，mysql维护了一个6位的row_id。

七. 最左匹配

针对的是组合索引
当一个查询条件是两个列的时候，就是需要两个查询条件
比如age gender

select * form t1 where age = ? and gender = ?
select * form t1 where gender = ? and age = ?
select * form t1 where gender = ?
select * form t1 where age = ?

这四条语句中
第一条首先是age 然后是gender
第二条 虽然顺序反了，但是mysql有一个优化器，可以把顺序反转过来继续使用最左匹配
第三条 只有一个而且开头不是age 那么不会进行最左匹配
第四条 只有一个但是第一个是age 那么会进行最左匹配原则

八. 索引覆盖

当建立了以name为普通索引
select * from t1 where name=a
select id from t1 where name=a
第一条语句在执行的时候会去回表查询
第二条语句的执行的时候第一次就拿到了这个值，不会再去回表查询，这就叫做索引覆盖

其实通过遍历索引取得数据

九. 索引下推

9.1 什么是索引下推

不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。

当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

9.2 执行过程

• 不使用索引条件下推优化时的查询过程

1. 获取下一行，首先读索引元组，然后使用索引去查找并读取所有的行

2. 根据WHERE条件部分，判断数据是否符合。根据判断结果接受或拒绝该行

• 使用索引条件下推优化时的查询过程

1. 获取下一行的索引元组（不是所有行）

2. 根据WHERE条件部分，判断是否可以只通过索引列满足条件。如果不满足，则获取下一行索引元组

3. 如果满足条件，则通过索引元组去查询并读取所有的行

4. 根据遗留的WHERE子句中的条件，在当前表中进行判断，根据判断结果接受或者拒绝改行

简单来说就是先检查改行是否是索引要用的，是的话继续检查其他题哦键

十. 组合索引失效条件

我们的索引需要满足最左匹配条件的。

对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

(1) select * from myTest where a=3 and b=5 and c=4; ---- abc顺序
abc三个索引都在where条件里面用到了，而且都发挥了作用

(2) select * from myTest where c=4 and b=6 and a=3;
where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

(3) select * from myTest where a=3 and c=7;
a用到索引， b没有用，所以c是没有用到索引效果的

(4) select * from myTest where a=3 and b>7 and c=3; ---- b范围值，断点，阻塞了c的索引
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

(5) select * from myTest where b=3 and c=4; ---- 联合索引必须按照顺序使用，并且需要全部使用
因为a索引没有使用，所以这里 bc都没有用上索引效果

(6) select * from myTest where a>4 and b=7 and c=9;
a用到了 b没有使用，c没有使用

(7) select * from myTest where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，a下面任意一段的b是排好序的

(8) select * from myTest where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

(9) select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

总结：

1. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

2. 存储引擎不能使用索引范围条件右边的列

3. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

十一. 数据库三大范式

1NF :
原子性。每个元素不可再分。也就是属性列
2NF:
消除了非主属性对码的部分函数依赖
3NF:
消除了非主属性对码的传递函数依赖
bcNF:

11.1 如果违反了1NF会怎么样

1. 假如是升高体重为一列，查询身高大于70cm的人，那么首先会做一次全表的扫描，然后在应用层做一遍fulter，然后才能得到结果。效率非常低
2. 如果是组合列的话，是没办法做join的

十二. 为什么非关系型数据库用b树，关系型用b+树

1. b树的最好时间是O(1) 最坏是O(logn)，平均的时间复杂度要好于b+树
2. 但是b树在写的时候节点分裂概率会更大，适合读多写少的操作。
3. 关系型数据库遍历查找比较多，b+树很方便就可以进行在链表的遍历操作。
4. 从io上来看，相同数据量，b树比b+树深，单个节点b+树存储的索引量更多，io操作时间就少一些。