经典的数据库面试题

2022-11-22 09:43:25

目录

1.为什么用自增列作为主键

  1. 如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引(ROWID 随着行记录的写入而主键递增，这个 ROWID 不像 ORACLE 的 ROWID 那样可引用，是隐含的)。
  2. 数据记录本身被存于主索引（一颗 B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB 默认为 15/16），则开辟一个新的页（节点）。
  3. 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
  4. 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE 来重建表并优化填充页面。

2.为什么使用数据索引能提高效率

  1. 数据索引的存储是有序的
  2. 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
  3. 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N）

3.B+树索引和哈希索引的区别

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过 1，而且同层级的节点间有指针相互链接，是有序的

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似 B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的

4.哈希索引的优势

等值查询。哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

5.哈希索引不适用的场景

  1. 不支持范围查询
  2. 不支持索引完成排序
  3. 不支持联合索引的最左前缀匹配规则

6.B 树和 B+树的区别

  1. B 树，每个节点都存储 key 和 data，所有节点组成这棵树，并且叶子节点指针为 nul，叶子结点不包含任何关键字信息。
  2. B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而 B 树的非终节点也包含需要查找的有效信息)

7.为什么说 B+比 B 树更适合实际应用中操作系统的文件索引和数据库索引？

  1. B+的磁盘读写代价更低 B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了
  2. B±tree 的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

8.MySQL 联合索引

  1. 联合索引是两个或更多个列上的索引。对于联合索引:Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是 key index (a,b,c). 可以支持 a 、 a,b 、 a,b,c 3 种组合进行查找，但不支持 b,c 进行查找 .当最左侧字段是常量引用时，索引就十分有效。
  2. 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

9.什么情况下应不建或少建索引

  1. 表记录太少
  2. 经常插入、删除、修改的表
  3. 数据重复且分布平均的表字段，假如一个表有 10 万行记录，有一个字段A 只有 T 和 F 两种值，且每个值的分布概率大约为 50%，那么对这种表 A 字段建索引一般不会提高数据库的查询速度。
  4. 经常和主字段一块查询但主字段索引值比较多的表字段

10.什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

11.表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

12.表分区有什么好处？

  1. 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
  2. 和单个磁盘或者文件系统相比，可以存储更多数据。
  3. 优化查询。在 where 语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及 sum 和 count 语句时，也可以在多个分区上并行处理，最后汇总结果。
  4. 分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
  5. 可以使用分区表来避免某些特殊的瓶颈，例如 InnoDB 的单个索引的互斥访问，ext3 问价你系统的 inode 锁竞争等。

13.分区表的限制因素

  1. 一个表最多只能有 1024 个分区。
  2. MySQL5.1 中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5 中提供了非整数表达式分区的支持。
  3. 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。
  4. 分区表中无法使用外键约束
  5. MySQL 的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

14.如何判断当前 MySQL 是否支持分区？

使用命令

show variables like '%partition%'

值为 YES，表示支持分区。

15.MySQL 支持的分区类型有哪些？

  1. RANGE 分区：这种模式允许将数据划分不同范围。
  2. LIST 分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。
  3. HASH 分区 ：这中模式允许通过对表的一个或多个列的 Hash Key 进行计算，最后通过这个 Hash 码不同数值对应的数据区域进行分区。
  4. KEY 分区 ：上面 Hash 模式的一种延伸，这里的 Hash Key 是 MySQL系统产生的。

16.四种隔离级别

  1. Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
  2. Read committed (读已提交)：可避免脏读的发生。
  3. Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
  4. Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

17.行级锁定的优点

  1. 当在许多线程中访问不同的行时只存在少量锁定冲突。
  2. 回滚时只有少量的更改。
  3. 可以长时间锁定单一的行。

18.行级锁定的缺点

  1. 比页级或表级锁定占用更多的内存。
  2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
  3. 如果你在大部分数据上经常进行 GROUP BY 操作或者必须经常扫描整个表，比其它锁定明显慢很多。
  4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

19.MySQL 优化

  1. 开启查询缓存，优化查询。
  2. explain 你的 select 查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。
  3. 当只要一行数据时使用 limit 1，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
  4. 为搜索字段建索引。
  5. 使用 ENUM 而不是 VARCHAR，如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
  6. 垂直分表。
  7. 选择正确的存储引擎。

20.key 和 index 的区别

key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等

index 是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql 中的 innodb 表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等

21.Mysql 中 MyISAM 和 InnoDB 的区别有哪些

区别：
1、InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务。
2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败。
3、InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
4、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。
5、Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上MyISAM 要高；

上一篇：Python_技巧系列


下一篇：mysql 面试突击 mysql和innodb原理与机制