面试题集锦:数据库

一、关系型数据库–mysql：

1. 数据库设计的三大范式：

• 第一范式: 每个列都不可以再拆分;
• 第二范式: 在第一范式的基础上, 非主键列完全依赖于主键, 而不能是依赖于主键的一部分;
• 第三范式: 在第二范式的基础上, 非主键列只能依赖于主键, 不能依赖于其他非主键;

2. mysql有关权限的表有哪几个?

mysql服务器通过权限表来控制用户对数据的访问 权限表存放在mysql数据库中, 有mysql_install_db脚本初始化;

• user表: 记录允许连接到服务器的用户账号信息, 是全局级别的;
• db表: 记录允许连接到服务器的用户账号信息, 是全局级别;
• table_priv表: 记录数据表级的操作权限;
• columns_priv表: 记录数据列级 的操作权限;
• host表: 配合db表对给定主机上数据库及操作权限作更细致的控制;

3. mysql的事务:

MySQL 事务主要用于处理操作量大; 复杂度高的数据:

• 在mysql中只有使用可Innodb数据库引擎的数据库或表才支持事务;
• 事务处理可以用来维护数据库的完整性, 保证成批的SQL语句要么全部执行, 要么全部不执行;
• 事务用来管理insert, update, delete语句;

一般来说, 事务是必须满足***4个条件(ACID)***的: 原子性(Atomicity, 或称 不可分割性)、一致性(Consistency)、隔离性(Isolation, 又称 独立性)、持久性(Durability)

• 原子性: 一个事务中的所有操作, 要么全部完成, 要么全部不完成, 不会结束在中间某个环节; 事务在执行过程中发生错误, 会被回滚(Rollback)到十五开始前的状态吗就像这个事务从来没有执行过一样;
• 一致性: 在事务开始之前和事务结束之后, 数据库的完整性没有被皮怀; 这表示写入的数据必须完全符合所有的预设规则, 这包含数据的精确度、串联型以及后续数据库可以自发性的完成预定的工作;
• 隔离性: 数据库允许多个并发事务同时对其数据进行读取和修改的能力, 隔离性可以防止多个事务并发执行时由于交叉执行而导致数据不一致问题; 事务的隔离分为不同级别: 未提交(Read uncommitted)、读提交(脏读, read committed)、可重复读(repeatable read)、串行化(Serializable);
• 持久化: 事务处理结束后, 对数据的修改就是永久的, 即便系统用故障了也不会丢失;

4. 什么是索引:

• 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分), 所以, 要占一定的物理空间, 它们包含数据表里所有记录的引用指针;
• 索引是一种数据结构; 数据库索引, 是数据库管理系统中一个排序的数据结构, 用来协助快速查询、更新数据表中的数据;
• 索引的实现通常使用B树或者B+树

5. 索引有哪些优缺点:

索引的优点:

• 提高查询速度：通过索引可以加快数据检索速度;
• 保证数据的唯一性：通过创建唯一索引，可以保证数据库表中每一行数据的唯一性;
• 加快表与表之间的连接：在实现数据的参考完整性方面，可以加速表与表之间的连接;
• 在分组和排序子句中进行数据检索：通过索引，分组和排序操作可以更快完成;
  索引的缺点:
• 时间方面: 创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率;
• 空间方面: 索引需要占物理空间;

6. 索引的类型

• B-Tree索引：最常见的索引类型，支持全键值、键值范围或键值前缀查找;
• 哈希索引：基于哈希表的索引，只支持等值比较查询;
• 全文索引：用于搜索文本中的关键字，而不是直接比较索引中的值;
• 空间索引：用于地理空间数据类型;

7. mysql中有哪几种锁:

• 表级锁: 开销小, 加锁快; 不会出现死锁; 锁定粒度大, 发生锁冲突的概率最高, 并发度最低;
• 行级锁: 开销大, 加锁慢; 会出现死锁; 锁定粒度最小, 发生锁冲突的概率最低, 并发度最高;
• 页面锁: 开销和加锁时间介于 表锁和行锁之间; 会发生死锁; 锁定粒度介于表锁和行锁之间, 并发度一般;

8. Mysql中InnoDB支持的四种事务隔离级别:

• 未提交(Read uncommitted)
• 读提交(脏读, read committed)
• 可重复读(repeatable read)
• 串行化(Serializable);

9. char和varchar的区别:

• char和varchar类型在存储和检索方面有所不同;
• char列长度固定为创建表时声明的长度, 1~255;
• 当char值被存储时, 他们被用空格填充到特定的长度, 检索char值时需要删除尾随的空格;

10. 主键与候选主键有什么区别:

• 表格的每一行都有主键作为唯一标识, 一个表只有一个主键;
• 逐渐也是候选键; 按照惯例, 候选键可以被指定为主键, 并且可以作为任何外键引用;

11. 如何在unix和mysql时间戳之间进行转换:

• UNIX_TIMESTAMP: 从mysql转换为Unix时间戳的命令;
• TFROM_UNIXTIME: 从Unix时间戳 转换为mysql时间戳;

12. MyISAM表如何进行存储:

每个MyISAM表以三种格式存储在磁盘上:

• .frm文件: 存储表定义;
• .myd(MyData)文件: 数据文件;
• .myi(myIndex)文件: 索引文件;

13. Mysql中记录货币用什么字段比较好:

• UNMERIC(数字)和DECIMAL(十进制)类型被mysql实现为同样的类型, 这在SQL92标准中是被允许的;
• UNMERIC和DECIMAL值作为字符串存储, 而不是二进制浮点数, 以便保存呢些值的小数精度;
• 它们被用来保存准确度及其重要的值, 例如: 与金钱有关的数据;
• 当声明一个类为这些类型的时候, 是需要指定精度(precision)和规模(scale)的;
  • salary DECIAML(3,7): 3代表将被用于存储的总的小数位; 7代表将被用于存储小数点后的位数;
  • DECIMAL(p)等价于DECIMAL(p,0);

14. 创建索引时需要注意什么?

• 非空字段: 应该指定为NOT NULL, 除非想存储NULL; 因为在mysql中, 含有空值的列很难进行查询优化, 他们会使的索引、索引的统计信息以及比较比较运算更加复杂;
• 取值离散大的字段: 将这些列放在联合索引的前面, 可以用过count()函数查看字段的差异值, 返回的值越大, 说明字段的唯一值越多, 字段的离散程度越高;
• 索引字段越小越好: 数据库的数据存储以页为单位, 一页存储的数据越多, 一次IO操作获取的数据越大, 效率越高;

15. 使用索引查询一定能提高查询性能嘛?

• 通常, 通过索引查询数据比全表扫描要快, 但是也必须注意到它的代价;
• 索引需要空间来存储, 也需要定期维护; 每当有记录在表中增加或索引列被修改时, 索引本身也会被修改; 这就意味着INSERT, DELETE, UPDATE将为此多付出4,5次的磁盘IO;
• 因为索引需要额外的存储空间和处理, 那些不必要的索引, 反而会使查询反应时间变慢;
• 同时, 使用索引不一定能提高查询性能, 索引范围查询(index range scan)适用于两种情况:
  • 基于一个范围的检索, 一般查询返回结果集小于表中记录数的30%;
  • 基于非唯一性索引的检索;

16. 百万级及以上的数据如何删除:

关于索引: 由于索引需要额外的维护成本, 因为索引文件是单独存在的文件, 所以当对数据进行增加,修改,删除时, 都会产生额外的对索引文件的操作, 这些操作需要消耗额外的 IO, 会降低增/改/删的执行效率;

在删除数据库百万级别数据的时候, 删除数据的速度和创建的索引数量是成正比的;

• 想要删除百万数据的时候, 可以先删除索引;
• 然后删除其中无用数据;
• 删除完成后重新创建索引, 此时因为数据少, 所以创建索引很快;

17. 什么是最左前缀原则? 什么是最左匹配原则?

最左前缀原则: 顾名思义, 就是最左优先, 在创建多列索引时, 要根据业务需求, where字句中使用最频繁的一列放在最左边;
最左匹配原则: mysql会一直向右匹配直到遇到范围查询(<, >, between, like)就停止匹配;

18.什么是聚簇索引? 何时使用聚簇索引与非聚簇索引?

• 聚簇索引: 将数据存储与索引放到了一块, 找到索引也就找到了数据;
• 非聚簇索引: 将数据与索引分开存储, 索引结构的叶子节点指向了数据的对应行;
  • MyISAM通过key_buffer把索引先缓存到内存中, 当需要通过索引访问数据时, 在内存中直接搜索索引, 然后通过索引找到磁盘响应数据; 这也就是为什么索引不再key_buffer命中时, 速度慢的原因;

19. Mysql连接器:

• 连接器和服务端先建立连接;
• 在完成TCP握手之后, 连接器会根据输入的用户名和密码验证登录身份;
  • 如果用户名或者密码错误, MySQL 就会提示 Access denied for user, 来结束执行;
  • 如果登录成功后, MySQL 会根据权限表中的记录来判定你的权限;

20. Mysql查询缓存:

• mysql在得到一个执行请求后, 会首先查询缓存, 是否执行过这条SQL语句;
• 之前执行过的语句以及结果会以 key-value 对的形式, 被直接放在内存中; key 是查询语句, value 是查询的结果;
• 如果通过 key 能够查找到这条 SQL 语句, 就直接返回 SQL 的执行结果;
• 如果语句不在查询缓存中, 就会继续后面的执行阶段; 执行完成后, 执行结果就会被放入查询缓存中;

21. Mysql分析器:

如果没有命中缓存查询, 就开始执行真正的SQL语句:

• 首先, mysql会根据sql语句进行解析, 分析器会先做词法分析, mysql需要识别出语句中的字符串是什么, 代表什么;
• 然后进行语法分析, 根据词法分析的结果, 语法分析器会根据语法规则, 判断出输入的这个sql语句是否满足mysql语法;
• 如果sql语句不正确, 就会提示You have an error in your SQL syntax;

22. Mysql优化器:

• 经过分析器的词法分析和语法分析后, 这条sql就合法了; mysql就知道要什么了, 但是在执行前, 还需要进行优化器的处理;
• 优化器会判断使用了哪种索引, 使用了何种连接, 优化器的作用就是确定效率最高的执行方案;

23.Mysql执行器:

• MySQL 首先会判断有没有执行这条语句的权限, 没有权限的话, 就会返回没有权限的错误; 如果有权限, 就打开表继续执行;
• 打开表的时候, 执行器就会根据表的引擎定义, 去使用这个引擎提供的接口;
• 对于有索引的表, 执行的逻辑也差不多;

24. 什么是临时表? 什么时候会创建临时表? :

mysql在执行sql语句的过程中, 通常会临时创建一些存储中间结果的表, 临时表只对当前连接连接, 在连接关闭时, 临时表会被删除并释放所有表空间;
临时表分为两种:

• 内存临时表, 使用MEMORY存储引擎;
• 磁盘临时表, 使用MyISAM存储引擎;

MySQL 会在下面这几种情况产生临时表:

• 使用 UNION 查询: UNION 有两种, 一种是 UNION, 一种是 UNION ALL,
  • 它们都用于联合查询; 区别是使用 UNION 会去掉两个表中的重复数据, 相当于对结果集做了一下 去重(distinct);
  • 使用 UNIONALL, 则不会排重, 返回所有的行; 使用 UNION 查询会产生临时表
• 使用TEMPTABLE算法或者是UNION查询中的视图: TEMPTABLE算法是一种创建临时表的算法,
  • 它是将结果放置到临时表中, 意味这要 MySQL要先创建好一个临时表, 然后将结果放到临时表中去, 然后再使用这个临时表进行相应的查询;
• ORDER BY 和 GROUPBY 的子句不一样时也会产生临时表;
• 去重查询并且加上 ORDER BY 时;
• SQL中用到SQL_SMALL_RESULT选项时: 如果查询结果比较小的时候, 可以加上 SQL SMALL RESULT 来优化, 产生临时表;
• FROM中的子查询;
• EXPLAIN 查看执行计划结果的 Extra 列中, 如果使用 Using Temporary 就表示会用到临时表;

25. 浅谈SQL优化经验:

• 查询语句无论是使用哪种判断条件等于、小于、大于, WHERE 左侧的条件查询字段不要使用函数或者表达式
• 使用 EXPLAIN 命令优化SELECT 查询, 对于复杂、效率低的 SQL语句, 通常是使用 explainsql 来分析这条 SQL 语句, 这样方便分析, 进行优化;
• 当你的 SELECT 查询语句只需要使用一条记录时, 要使用 LIMIT 1:
  • 不要直接使用 SELECT*, 而应该使用具体需要查询的表字段, 因为使用 EXPLAIN 进行分析时, SELECT*使用的是全表扫描, 也就是 type =all ;
• 为每一张表设置一个ID属性;
• 避免在 WHERE 字句中对字段进行 NULL判断;
• 避免在WHERE中使用!或<>操作符;
• 使用 BETWEEN AND 替代 IN;
• 为搜索字段创建索引;
• 选择正确的存储引擎, 如InnoDB、MyISAM、MEMORY等;
• 使用 LIKE%abc%不会走索引, 而使用 LIKE abc%会走索引;
• 对于枚举类型的字段(即有固定罗列值的字段), 建议使用 ENUM 而不是VARCHAR; 如性别、星期、类型、类别等;
• 拆分大的 DELETE 或 INSERT 语句;
• 选择合适的字段类型, 选择标准是尽可能小、尽可能定长、尽可能使用整数;
• 字段设计尽可能使用 NOT NULL;
• 进行水平切割或者垂直分割;

26. 什么叫外链接:

外链接分为三种: 左外链接(LEFT OUTER J0IN 或 LEFT JOIN), 右外链接(RIGHT OUTER JOIN 或 RIGHT JOIN), 全外链接(FULL OUTER JOIN 或 FULLJOIN)
左外链接: 又称为左连接, 这种连接方式会显示左表不符合条件的数据行, 右边不符合条件的数据行直接显示 NULL;
右外链接: 又称为右连接, 他与左连接相对, 这种连接方式会显示右表不符合条件的数据行, 左表不符合条件的数据行直接显示 NULL;

27. 什么是内链接:

结合两个表中相同的字段, 返回关联字段相符的记录就是内链接;

28. 使用 union 和 union all 时需要注意些什么:

• 通过 union 连接的 SQL 分别单独取出的列数必须相同;
• 使用 union 时, 多个相等的行将会被合并, 由于合升比较耗时, 一般不直接使 用 union 进行合并, 而是通常采用 union all 进行合并;

29. MyISAM 存储引擎的特点:

在 5.1 版本之前, MyISAM 是 MySQL 的默认存储引擎, MylSAM 并发性比较差, 使用的场景比较少, 主要特点是:

• 不支持事务操作, ACID的特性也就不存在了, 这一设计是为了性能和效率考虑的;
• 不支持外键操作, 如果强行增加外键, MySQL不会报错, 只不过外键不起作用;
• MyISAM 默认的锁粒度是表级锁, 所以并发性能比较差, 加锁比较快, 锁冲突比较少, 不太容易发生死锁的情况;
  MyISAM会在磁盘上存储三个文件, 文件名和表名相同, 扩展名分别是.frm(存储表定义)、.MYD(MYData，存储数据)、.MYI(MyIndex，存储索引); 这里需要特别注意的是 MyISAM 只缓存索引文件, 并不缓存数据文件;
• MyISAM 支持的索引类型有全局索引(Full-Text)、B-Tree 索引、R-Tree 索引;
  • Full-Text索引: 它的出现是为了解决针对文本的模糊查询效率较低的问题;
  • B-Tree索引: 所有的索引节点都按照平衡树的数据结构来存储, 所有的索引数据节点都在叶节点;
  • R-Tree索引: 它的存储方式和B-Tree索引有一些区别，主要设计用于存储空间和多维数据的字段做索引目前的 MySQL 版本仅支持 geometry类型的字段作索引, 相对于 BTREE,RTREE 的优势在于范围查找;
• 数据库所在主机如果宕机, MyISAM的数据文件容易损坏, 而且难以恢复;
• 增删改查性能方面: SELECT性能较高, 适用于查询较多的情况;

30. InnoDB 存储引擎的特点:

自从 MySQL5.1 之后, 默认的存储引擎变成了 InnoDB 存储引擎, 相对于 MylSAM, InnoDB 存储引擎有了较大的改变, 它的主要特点是:

• 支持事务操作, 具有事务ACID隔离特性, 默认的隔离级别是可重复读 (repetable-read)、通过 MVCC(并发版本控制)来实现的; 能够解决 脏读 和 不可重复读 的问题;
• InnoDB 支持外键操作;
• InnoDB 默认的锁粒度行级锁, 并发性能比较好, 会发生死锁的情况。
• 和MyISAM一样的是, InnoDB存储引擎也有.frm文件存储表结构定义, 但是不同的是, InnoDB 的表数据与索引数据是存储在一起的, 都位于 B+数的叶子节点上, 而 MylSAM 的表数据和索引数据是分开的;
• InnoDB有安全的日志文件, 这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性;
• InnoDB和MylSAM支持的索引类型相同, 但具体实现因为文件结构的不同有很大差异;
• 增删改查性能方面, 果执行大量的增删改操作, 推荐使用InnoDB存储引擎, 它在删除操作时是对行删除, 不会重建表;

二：key-value 存储系统–Redis：

1. 什么是Redis:

• Redis 是完全开放免费的, 是一个高性能的Key-value数据库;
• Redis与其他key-value缓存产品以下三个特点:
  • Reids支持数据的持久化, 可以将内存中的数据保存在磁盘中, 重启时可以再次加载进行使用;
  • Redis不仅支持简单的key-value类型的数据, 还提供了list, set, zset, hash等数据结构;
  • Redis支持数据的备份, 即master-slave模式的数据备份

2. Redis优势:

• 速度快：Redis使用内存存储数据，这使得其读写速度非常快，远超过传统的基于磁盘的数据库系统。这一特点使得Redis非常适合作为高性能缓存系统，能够快速提升数据访问速度和减轻数据库负载。
• 丰富的数据类型：Redis支持多种数据类型，包括字符串、哈希、列表、集合和有序集合等。这些灵活的数据结构使得Redis能够满足各种数据存储和操作需求，为开发者提供了极大的便利。
• 原子性：Redis的命令是原子的，这意味着在执行多个命令时，如果发生任何错误，整个操作都会失败。这一特性保证了数据的一致性和可靠性。
• 事务处理：Redis支持事务处理，可以保证一组命令的原子性执行。这进一步增强了Redis在数据处理方面的能力和灵活性。
• 持久性：尽管Redis主要依赖内存存储，但它也提供了持久化机制。Redis支持两种持久化方式：RDB和AOF。这使得Redis在保持高速读写的同时，也能确保数据的可靠性和持久存储。
• 分布式：Redis支持分布式模式，可以多个Redis实例组成一个集群，实现数据的垂直和水平切分。这一特性使得Redis能够轻松应对高并发和大数据量的场景，提供高可用性和可扩展性。
• 简单易用：Redis具有简洁的命令接口和丰富的内置命令，易于使用和管理。同时，它还提供了丰富的客户端库，使得开发者能够轻松地在各种编程环境中集成和使用Redis。

3. Redis的数据类型:

• 字符串(String): 这是Redis中最基本的数据类型，可以包含任何数据，如文本、数字、二进制数据等。
• 哈希（Hash): 一个键值对集合，常用于存储对象或映射。
• 列表（List): 一个简单的列表，可以存储一系列的字符串元素。
• 集合（Set): 一个无序集合，可以存储不重复的字符串元素。
• 有序集合（Sorted Set）：类似于集合，但是每个元素都有一个分数（score）与之关联。
• 位图（Bitmaps）：基于字符串类型，可以对每个位进行操作。
• 超日志（HyperLogLogs）：用于基数统计，可以估算集合中的唯一元素数量。
• 地理空间（Geospatial）：用于存储地理位置信息。
• 发布/订阅（Pub/Sub）：一种消息通信模式，允许客户端订阅消息通道，并接收发布到该通道的消息。
• 流（Streams）：用于消息队列和日志存储，支持消息的持久化和时间排序。
• 模块（Modules）：Redis 支持动态加载模块，可以扩展 Redis 的功能。

三：消息队列–kafka：