数据库相关面试题

关系型数据库常考考点

架构

如何设计一个关系型数据库？

1）程序实例：存储管理、缓存机制、SQL解析、权限划分、容灾机制、索引管理、锁管理
2）存储：文件系统

索引

为什么要使用索引？

1）为了加速数据表中行记录的检索的数据结构
2）快速查询信息，避免全表扫描，减少IO操作次数

什么信息能够成为索引?

主键、唯一键等

索引常用的数据结构？

MySQL存储引擎使用较多的索引有Hash索引,B+树索引等
而oracle存储引擎也用到位图索引（bitMap）
InnoDB存储引擎的默认索引实现为:B+树索引

B树与B+树区别？

​ B树

1）根节点最少包含两个孩子
2）树中每个节点最多含有m个孩子
3）除根节点和叶节点外，其他每个节点最少有ceil(m/2)个孩子
4）所有叶子节点都位于一层索引-数据结构-运用B+树
B+树

1）采用的左闭合比较方式（1<=x<28）
2）根节点支节点不保存数据，叶子节点才保存数据区的内容
3）叶子节点关键字形成双向链表结构

结论
1）B+Tree更适合用来做存储索引
2）B+Tree的磁盘读写代价更低
3）B+Tree的查询效率更加稳定
4）B+Tree更有利于对数据库的扫描

Hash索引和B+树优劣呢？

Hash索引缺点
1）仅仅能满足"="，“in”,不能使用范围查询
2）无法被用来避免数据排序操作
3）不能利用部分索引键查询
4）不能避免全表扫描
5）遇到大量Hash值相等的情况后性能并不一定会比B+tree索引高

聚集索引和非聚集索引的区别

根本区别：数据行的物理顺序和与索引的排列顺序是否一致

聚集索引
1）数据行的物理和索引的排列顺序相同，一个表只能有一个聚集索引
2）如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引
3）SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引
4）索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，非聚集索引在索引没有覆盖到对应的列的时候需要进二次查询
5）因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序,会插入数据慢

非聚集索引
1）数据行的物理和索引的排列顺序不同，一个表可以有多个非聚集索引
2）非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索
没有覆盖的列，那么他还要进行第二次的查询

如何定位并优化慢查询sql？

1）根据慢日志定位慢查询
显示所有查的变量：show variables like ‘%quer%’
显示慢查询记录：show status like ‘%slow_quer%’
开启慢查询日志：set global slow_query_log = on
设置漫查询时间：set global long_query_time = 1（单位：秒）

2）使用explain等工具分析sql
explain关键字字段
type：如果出现index或all表明这语句需要优化（index和all查询是全表扫描）
extra：如果出现Using filesort、Using temporary说明没用到索引，可能需要进行优化

3）修改sql或尽量让sql走索引

sql优化的一些细节？

1、应尽量避免在 where子句中使用!=或<>操作符
2、尽量避免在 where子句中使用 or来连接条件，用union all
3、in和 not in也要慎用
4、应尽量避免在 where子句中对字段进行表达式操作
5、很多时候用 exists代替 in是一个好的选择
6、应尽量避免在where子句中对字段进行函数操作

联合索引最左匹配原则成因?

1）最左匹配非常重要的原则，MySQL会一直向右匹配，直到遇到范围查询（>、<、between、like）就停止匹配。比如a=3 and b=4and c>5 and d=6，如果建立（a,b,c,d）顺序的索引，d是用不到索引的；如果建立(a,b,d,c)的索引，则都可以用到，a,b,d的顺序都以任意调整。

2）=和in可以乱序，比如a=1 and b=2 and c=3建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。MySQL创建联合索引首先会对复合索引最左边即第一个索引字段的数据进行排序，在第一个排序字段的基础上再对后面第二个索引字段进行排序，类似实现了order by 字段1 order by 字段2，第一个字段绝对有序第二个字段无序。因此MySQL用第二个字段进行条件判断是用不到索引的。

索引建立越多越好吗

1）数据量小的表不需要建立索引，建立会增加额外的索引开销。
2）数据变更需要维护索引，因此更多的索引意味着更多的维护成本。
3）更多的索引意味着需要更多的空间。

锁模块

MyISAM和InnoDB关于锁方面的区别？

MyISAM默认使用表级锁，不支持行级锁；
InnoDB默认用的行级锁，也支持表级锁

MyISAM与InnoDB分别适用的场景？

1）MyISANM适用的场景
频繁执行全表count语句
对数据进行增删改的频率不高，查询非常频繁
没有事务

2）InnoDB适用的场景
数据增删改查都相对频繁
可靠性要求比较高的，要求支持事务

什么是死锁，如何避免

死锁是由于两个或以上的线程互相持有对方需要的资源，导致这些线程处于等待状态，无法执行

常见的避免死锁的方法

1）如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3）对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

数据库事务的四大特性

1）原子性
2）一致性
3）隔离性
4）持久性

什么是脏读？幻读？不可重复读？

1）更新丢失：mysql所有事务隔离级别在数据层面上都可以避免（知道理解即可）
2）脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
3）不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数时，结果 不一致
4）幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的录，当系统管理员A改结束后再次查询发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件行，解决幻读需要锁表

什么是事务的隔离级别？

read-uncommitted(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
read-committed(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发
repeatable-read(可重复读)： 对同一字段的多次读取结果都是一致的，可以阻止脏读和不可读，但幻读仍有可能发生。
serializable(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。

拓展：
Mysql 默认采用的 repeatable-read（可重复读）隔离级别
Oracle 默认采用的 read-committed（读已提交）隔离级别

理论范式

数据库的理论范式？

第一范式：每个列都不可以再拆分。
第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式：在第二范式的基础上，每个非主键列都不能传递依赖于主键

语法

常见语法知识点

group by
给定数据列的每个成员，对查询结果进行分组统计，最终得到一个分组汇总表。
1）select子句的列名必须为分组列（group by用到的列）或列函数（count、sum、max、min、avg）
2）列函数对于group by子句定义的每个组各返回一个结果

order by
根据指定的列对结果集进行排序，默认按照升序（ASC）对记录进行排序，降序使用 DESC 关键字。

having
1）通常与group by子句一起使用
2）where过滤行，having过滤组
3）出现在同一sql的顺序：where>group by>having

统计相关（聚合函数）
count求总数、sum求和、max求最大值、min求最小值、avg求平均。

limit
查询前N条记录

主从复制、

读写分离、分库分表