面试题-数据库篇

最新推荐文章于 2024-06-16 12:21:08 发布

wpwdm159357

最新推荐文章于 2024-06-16 12:21:08 发布

阅读量230

点赞数

文章标签： mysql redis

本文链接：https://blog.csdn.net/wpwdm159357/article/details/115871969

版权

面试题-数据库篇

mysql篇

优化建议

表设计

	表字段not null，null很难查询优化，且额外占用索引空间
	尽量使用整型代替字符串，字符串占用空间大
	字符串长度不要随意设置，保证业务的情况下尽量小
	单表的字段不要太多

索引设计

explain关键字检查sql
取消外键
复合索引匹配最左列规则，查询条件保持一致
值分布较少的字段不适合建索引
需要排序的字段加上索引

查询优化

使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的，是否有全表扫描等；
查询的条件尽量使用索引字段，如某一个表有多个条件，就尽量使用复合索引查询，复合索引使用要注意字段的先后顺序。
多表关联尽量用join，减少子查询的使用。表的关联字段如果能用主键就用主键，也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
尽量使用limit进行分页批量查询，不要一次全部获取。
绝对避免select *的使用，尽量select具体需要的字段，减少不必要字段的查询；
尽量将or 转换为 union all。
尽量避免使用is null或is not null。
要注意like的使用，前模糊和全模糊不会走索引
Where后的查询字段尽量减少使用函数，因为函数会造成索引失效。
避免使用不等于（!=），因为它不会使用索引。
用exists代替in，not exists代替not in，效率会更好；
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
千万不要 ORDER BY RAND()

explain执行计划

在这里插入图片描述

id 是一组数字，代表多个表之间的查询顺序，或者包含子句查询语句中的顺序
select_type 包含以下几种值
simple：简单的 select 查询，查询中不包含子查询或者 union 查询
primary：如果 SQL 语句中包含任何子查询，那么子查询的最外层会被标记为 primary
subquery:在 select 或者 where 里包含了子查询，那么子查询就会被标记为 subQquery，同三.二同时出现
derived：在 from 中包含的子查询，会被标记为衍生查询，会把查询结果放到一个临时表中
union，union result：如果有两个 select 查询语句，他们之间用 union 连起来查询，那么第二个 select 会被标记为 union，union 的结果被标记为 union result。它的 id 是为 null 的
table 表示这一行的数据是哪张表的数据
type是代表 MySQL 使用了哪种索引类型，不同的索引类型的查询效率也是不一样的，type 大致有以下种类
NULL：在执行阶段不需要访问表。
system：表中只有一行记录，system 是 const 的特例，几乎不会出现这种情况，可以忽略不计
const：将主键索引或者唯一索引放到 where 条件中查询，MySQL 可以将查询条件转变成一个常量，只匹配一行数据，索引一次就找到数据了
eq_ref：在多表查询中，如 T1 和 T2，T1 中的一行记录，在 T2 中也只能找到唯一的一行，说白了就是 T1 和 T2 关联查询的条件都是主键索引或者唯一索引，这样才能保证 T1 每一行记录只对应 T2 的一行记录
ref：不是主键索引，也不是唯一索引，就是普通的索引，可能会返回多个符合条件的行。
range：体现在对某个索引进行区间范围检索，一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。
index：将所有的索引树都遍历一遍，查找到符合条件的行。索引文件比数据文件还是要小很多，所以比不用索引全表扫描还是要快很多。
all:没用到索引，单纯的将表数据全部都遍历一遍，查找到符合条件的数据
一般来说，至少要保证查询达到 range 级别，最好能达到 ref 级别。
possible_keys
此次查询中涉及字段上若存在索引，则会被列出来，表示可能会用到的索引，但并不是实际上一定会用到的索引
key
此次查询中实际上用到的索引
key_len
表示索引中使用的字节数，通过该属性可以知道在查询中使用的索引长度
ref
显示关联的字段。如果使用常数等值查询，则显示 const，如果是连接查询，则会显示关联的字段。
rows
根据表信息统计以及索引的使用情况，大致估算说要找到所需记录需要读取的行数，rows 越小越好
extra
不适合在其他列显示出来，但在优化时十分重要的信息
Using index：使用覆盖索引，表示查询索引就可查到所需数据，不用扫描表数据文件，往往说明性能不错。
Using Where：在存储引擎检索行后再进行过滤，使用了 where 从句来限制哪些行将与下一张表匹配或者是返回给用户。
Using temporary：在查询结果排序时会使用一个临时表，一般出现于排序、分组和多表 join 的情况，查询效率不高，建议优化。
Using filesort：对结果使用一个外部索引排序，而不是按索引次序从表里读取行，一般有出现该值，都建议优化去掉，因为这样的查询 CPU 资源消耗大。
有没有出现filesort或者using template，一旦出现就要想办法避免，接着再看key使用的是什么索引，还有看filtered筛选比是多少

索引失效的情况

在where子句中进行is null,is not null
在where子句中使用!= ,< >,not in,not exist这样的符号
在where子句中使用or来连接条件
查询类型和字段类型不一致
在where子句中=的左边使用表达式操作或者函数操作
在where子句中使用后缀like模糊查询
当前联合索引是index(a,b,c),那么（a,b）,(a,c),(abc)使用索引，其他都不使用索引

千万级数据量分页查询优化

方法1：直接使用limit查询
语句：SELECT * FROM 表名称 LIMIT M,N
适用于数据量上千的情况，全表扫描导致查询很慢

方法2：建立索引
语句：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M
适用于数据量上万的情况，索引扫描加快速度

方法3：基于索引再排序
语句：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
适用于数据量上万的请求，索引扫描

方法4：基于索引使用prepare
语句：PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
适用于大数据量，prepare语句比一般查询快一点

方法5：利用子查询连接+索引快速定位位置再读取元组
语句：
SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ( $p a g e - 1) *$ pagesize ORDER BY id desc
LIMIT $pagesize

SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ( $p a g e - 1) *$ pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

方法6：使用复合索引
语句：select id from collect where vtype=1 limit 90000,10;
适用于大量数据，建立search(vtype,id) 复合索引

数据库和缓存一致性方案

出现的问题：
1.如果删除缓存，后写库，另一个线程来读取，缓存为空就去数据库查询后写入缓存，此时缓存中为脏数据
2.先写库，在删除缓存，写库线程宕机了未删除缓存，也会出现数据不一致问题

解决方案：
1.采用延时双删策略，步骤为先删除缓存，写入数据库，休眠一段时间再次删除缓存，最后一次删除也可以放入mq中，通过消费进行删除
2.异步更新缓存，基于订阅binlog，一旦mysql产生更新操作可以把binlog相关的消息推送至redis，redis根据binlog的记录对redis进行更新，可以使用阿里的canal，对mysql的binlog进行订阅。

ACID靠什么保证的

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；由undo log日志保证，它记录需要回滚的日志信息，事务回滚时撤销自己已经执行成功的sql；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；由其他三大特性保证，程序代码要保证业务上的一致性
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；由MVCC来保证
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机时可以从redo log恢复

MVCC是什么

多版本并发控制，通过保存数据的历史版本，根据比较版本号来处理数据的是否显示，从而达到读取数据的时候不需要加锁就可以保证事务隔离性的效果。

具体逻辑：

开启事务时创建readview，readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组；
访问数据的时候获取数据中的事务di，对于readview；
如果比readview都小，可以访问，说明事务已经提交了；
如果在readview中，获取roll_pointer（上一个版本地址），重新进行对比；
如果在readview都大，意味着该事务还未提交。

MVCC只在已提交读和可重复读隔离级别生效，已提交读下事务在每次查询的时候开始都会生成一个独立的readview，而可重复读隔离级别在第一次读的时候生成一个readview，之后读取都复用之前的readview。

三大范式

第一范式：数据库表每一列都是不可分割的
第二范式：需要确保数据库表中的每一列都与主键相关
第三范式：确保数据库中每一列数据都会主键直接相关

B+树

一个m阶的B+树特征

有k个子树的中间节点包含有k个元素，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点；
所有的叶子节点包含全部的元素信息，并且每个叶子节点都带有指向下一个节点的指针形成一个有序的双向链表，且叶子节点本身依关键字大小自小到大顺序链接。
所有的中间节点元素都同时存在子节点中，在子节点元素中是最大（或最小）元素
每个节点至多有m个子节点
根节点至少有2个子节点，除根节点外，每个节点至少有m/2个子节点。

插入操作规则

在这里插入图片描述

删除操作规则

在这里插入图片描述

redis篇

雪崩，穿透，击穿

雪崩
当大量缓存同一时间失效，导致db负载过重
解决方案：
把缓存的时效时间分散开，在原有的统一时效基础上增加一个随机值；
对热点数据设置永不过期。

穿透
大量的查询缓存不存在的key，因此每次都查询数据库
解决方案：
接口校验，对非法参数进行拦截；
查询数据库为空时，直接设置空值存到缓存，并设置较短的过期时间；
使用布隆过滤器存储所有可能访问的key，不存在的key直接过滤。

击穿
某个热点key缓存过期，但是大量的请求过来，导致压垮db
解决方案：
缓存的数据不会变化，则尝试设置热点数据为永不过期,不建议使用；
更新不频繁，且整个流程耗时较少情况下，使用分布式锁，保证只有一个请求线程能执行数据库查询操作，等到第一个线程将数据写入缓存后走缓存。
更新频繁，或者缓存刷新流程耗时较长，可以使用线程定时在缓存过期前主动构建缓存或者延长缓存的过期时间，

布隆过滤器

含义

一种数据结构，是由一串很长的二进制向量组成，可以将其看成一个二进制数组。既然是二进制，那么里面存放的不是0，就是1，但是初始默认值都是0。

添加数据

当要向布隆过滤器中添加一个元素key时，我们通过多个hash函数，算出一个值，然后将这个值所在的方格置为1。

判断是否存在

我们只需要将这个新的数据通过上面自定义的几个哈希函数，分别算出各个值，然后看其对应的地方是否都是1，如果存在一个不是1的情况，那么我们可以说，该新数据一定不存在于这个布隆过滤器中。

布隆过滤器可以判断某个数据一定不存在，但是无法判断一定存在

优缺点

优点：优点很明显，二进制组成的数组，占用内存极少，并且插入和查询速度都足够快。

缺点：随着数据的增加，误判率会增加；还有无法判断数据一定存在；另外还有一个重要缺点，无法删除数据。

删除策略

惰性删除：只会在取出 key 的时候才对数据进行过期检查。这样对 CPU 最友好，但是可能会造成太多过期 key 没有被删除。
定期删除：每隔一段时间抽取一批 key 执行删除过期 key 操作。并且，Redis 底层会通过限制删除操作执行的时长和频率来减少删除操作对 CPU 时间的影响。

内存淘汰策略

名称	含义
volatile-lru	在过期的key中，移除最近最少使用的
volatile-random	在过期的key中，随机移除某个key
volatile-ttl	在过期的key中，移除过期最早的
noeviction	返回错误，不移除任何key
allkeys-lru	在全部的key中，移除最近最少使用的key
allkeys-random	在全部的key中，随机移除某个key
volatile-lfu	在过期的key中，移除使用频率最少的key
allkeys-lfu	在全部的key中，移除使用频率最少的key

数据类型和应用场景

类型	数据结构	应用场景
字符串String	简单的动态字符串SDS	缓存页面数据；计数
列表list	压缩列表+链表	朋友圈点赞列表，评论列表，排行榜
哈希hash	数组+链表	利用哈希的分类存储
集合set	hashset类似	去重功能，好友关注
有序集合	跳表	访问量排行榜、点击量排行榜

sds结构：
{
// 等于 SDS 所保存字符串的长度
int len;
// 记录 buf 数组中未使用字节的数量
int free;
// 字节数组，用于保存字符串
char buf[];
}
sds特性：
空间预分配：判断扩容后的长度是否大于1M，大于则扩容1M,小于1M则扩容的长度翻倍。
如果类型不变，则直接增加内存空间。变了重新创建字符串，
空间懒性释放：一个字符串缩减的操作，redis并不会马上收回我们的空间，因为可以预防你继续添加的操作，这样可以减少分配空间带来的消耗，但是当你再次操作还是没用到多余空间的时候，Redis也还是会收回对于的空间，防止内存的浪费的
数据格式多样性：redis 会以处理二进制的方式操作Buf数组中的数据，所以对存入其中的数据做任何的限制、过滤，只要存进来什么样，取出来还是什么样。
最大长度512M.

持久化

RDB

在指定的时间间隔内将内存中的数据集快照写入磁盘，实际操作过程是fork一个子进程，先将数据集写入临时文件，写入成功后，再替换之前的文件，用二进制压缩存储。

优点：

整个redis数据库只包含一个文件dump.rdb，方便持久化
容灾性好，方便备份
性能最大化，fork子进程来完成写操作，让主进程继续处理命令，所以是io最大化，使用单独子进程来进行持久化，主进程不会进行任何io操作，保证了redis的高性能。
相对于数据集大时，比aof启动效率高；

缺点：

数据安全性低，rdb是间隔一段时间进行持久化，如果持久化之间redis发生故障，发生数据丢失，所以这种方式更适合要求不严谨的时候
由于rdb是同fork子进程来协助完成数据持久化工作的，因此当数据集比较大时，可能会导致整个服务器停止服务几百毫秒，甚至1秒钟。

AOF

以日志的形式记录服务器所处理的每一次写，删除操作，查询操作不会记录，以文本的方式记录，可以打开文件看到详细的操作记录。

优点：

数据安全，同步持久化，每次发生数据变化都会被持久化到磁盘中
通过append模式写文件，即使中途服务器宕机也不会破坏已经存在的内容，可以通过redis-check-aof工具解决数据一致性问题
aof机制的rewrite模式，定期对aof进行重写，以达到压缩的目的；

缺点：

aof文件比rdb文件大，且恢复速度慢
数据集大的时候，比rdb启动效率低
运行效率没有rdb高

下一篇文章mq篇

wpwdm159357

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
面试题-数据库篇

面试题-Spring篇文章目录面试题-Spring篇mysql1.优化建议2.千万级数据量分页查询优化mysql1.优化建议表设计表字段not null，null很难查询优化，且额外占用索引空间尽量使用整型代替字符串，字符串占用空间大字符串长度不要随意设置，保证业务的情况下尽量小单表的字段不要太多索引设计explain关键字检查sql取消外键复合索引匹配最左列规则，查询条件保持一致值分布较少的字段不适合建索引需要排序的字段加上索引查询优化尽量短查询代替内联查询不适应sele
复制链接

扫一扫