面试宝典（1）——数据库篇（MySQL）

2024-04-27 07:21:02

面试宝典（1）——数据库篇（MySQL）

1.什么是索引？

索引是一种用于加快数据库查询速度的数据结构。

索引可以帮助数据库快速定位到数据库表中特定列的记录，从而加快数据检索和查询的速度。

通过在表的列上创建索引，可以减少数据库系统需要扫描的数据量，从而提高查询效率。

MySQL中，常见的索引类型包括普通索引，唯一索引，主键索引，全文索引等。

创建索引可以在表的列上快速查找数据，但是同时也会增加存储空间和写入操作的开销。

2.索引的底层数据结构是什么？

一般为B-Tree索引，Hash索引，以及Full-Text索引

  • B-Tree：是一种平衡树结构，一般常用于实现数据库索引。在MySQL中，主要使用的是B+树，B+树是一种多路平衡查找树，具有良好的平衡性。
  • Hash：Hash索引使用哈希表数据结构加快数据查找速度。在MySQL中，一般用于Memory存储引擎的哈希索引。Hash索引适用于等值查找，但不支持范围查找。
  • Full-text索引：全文索引是用于全文搜索的索引类型，在MySQL中用于对文本字段进行全文搜索。全文索引底层数据结构通常基于倒排索引。

3.InnoDB引擎是什么？

InnoDB是MySQL中最常用的存储引擎之一，它具有以下特点：

  1. 事务支持：InnoDB存储引擎支持事务，可以使用ACID（原子性、一致性、隔离性、持久性）属性来确保数据的完整性和一致性。
  2. 行级锁定：InnoDB存储引擎支持行级锁定，这意味着在并发访问时可以更好地控制并发性能，减少锁定冲突。
  3. 外键约束：InnoDB存储引擎支持外键约束，可以确保数据完整性，实现数据表之间的关联关系。
  4. 支持热备份：InnoDB存储引擎支持热备份，可以在不停止MySQL服务的情况下进行备份操作。
  5. 支持MVCC：InnoDB使用多版本并发控制（MVCC）来处理事务，可以提高并发性能。
  6. 崩溃恢复：InnoDB存储引擎具有良好的崩溃恢复能力，可以在数据库发生异常情况时进行自动恢复。

4.简述InnoDB存储引擎和Memory存储引擎的区别

InnoDB存储引擎和Memory存储引擎是MySQL中两种不同的存储引擎，它们之间有以下主要区别：

  1. 数据存储方式：
    • InnoDB存储引擎：数据存储在磁盘上，支持持久化存储，适合处理大量数据和复杂查询。
    • Memory存储引擎：数据存储在内存中，不支持持久化，适合处理临时数据、缓存数据或数据量较小的表。
  2. 事务支持：
    • InnoDB存储引擎：支持事务，可以使用ACID属性确保数据的完整性和一致性。
    • Memory存储引擎：不支持事务，数据在数据库重启时会丢失。
  3. 索引支持：
    • InnoDB存储引擎：支持各种类型的索引，包括主键索引、唯一索引、全文索引等。
    • Memory存储引擎：只支持HASH和BTREE索引，不支持全文索引。
  4. 并发控制：
    • InnoDB存储引擎：支持行级锁定和MVCC，可以提高并发性能。
    • Memory存储引擎：不支持行级锁定，可能会出现并发访问的性能问题。
  5. 数据恢复：
    • InnoDB存储引擎：具有良好的崩溃恢复能力，可以在数据库异常情况下进行自动恢复。
    • Memory存储引擎：数据在数据库重启时会丢失，无法进行数据恢复。

综上所述，InnoDB存储引擎适合处理大量数据、支持事务、并发性能要求高的场景，而Memory存储引擎适合处理临时数据、缓存数据或数据量较小的表。选择合适的存储引擎取决于具体的业务需求和性能要求。

5.B树与B+树的区别

B树和B+树是常用的数据结构，用于实现索引，提高数据库的查询效率。它们的区别主要体现在以下几个方面：

  1. 数据存储方式：
    • B树：每个节点既存储数据，又存储索引，节点之间通过指针连接。
    • B+树：只有叶子节点存储数据，非叶子节点只存储索引，叶子节点之间通过指针连接。
  2. 检索效率：
    • B树：由于每个节点都存储数据，所以在进行范围查询时，可以直接从非叶子节点开始查找。
    • B+树：由于只有叶子节点存储数据，范围查询需要遍历叶子节点，因此范围查询的效率比B树稍低。
  3. 范围查询：
    • B树：范围查询效率高，因为可以直接在非叶子节点进行范围查询。
    • B+树：范围查询需要遍历叶子节点，效率相对较低。
  4. 顺序访问：
    • B树：由于数据和索引分散存储在各个节点中，顺序访问时需要多次磁盘IO。
    • B+树：叶子节点连成链表，可以快速进行顺序访问。

总的来说，B+树适合范围查询和顺序访问，而B树适合随机查询。在实际应用中，根据具体的需求和数据特点选择合适的数据结构可以提高查询效率。

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

聚集索引（Clustered Index）是数据与索引放在一起，B+树的叶子节点保存了整行数据，有且只有一个。

和非聚集索引（Non-clustered Index）是数据与索引分开，B+树的叶子节点保存了对应的主键，可以有多个。

以下是它们的具体区别：

  1. 存储方式：
    • 聚集索引：在聚集索引中，数据行的物理存储顺序与索引的顺序一致。即索引的叶子节点包含了完整的数据行，因此数据行按照聚集索引的键值进行排序存储。
    • 非聚集索引：在非聚集索引中，索引的叶子节点并不包含完整的数据行，而是包含指向数据行的指针。数据行的物理存储顺序与索引的顺序无关。
  2. 表中索引数量：
    • 聚集索引：每个表只能有一个聚集索引，因为数据行只能按照一种方式进行物理存储排序。
    • 非聚集索引：每个表可以有多个非聚集索引，因为非聚集索引并不影响数据行的物理存储顺序。
  3. 查询性能：
    • 聚集索引：由于数据行按照聚集索引的键值进行排序存储，因此针对聚集索引进行的范围查询或范围扫描操作通常具有较高的性能。
    • 非聚集索引：非聚集索引通常适用于单值查询，它可以快速定位到数据行的位置，但在进行范围查询时性能可能不如聚集索引。

总结：

  • 聚集索引和非聚集索引的最大区别在于数据行的物理存储方式和索引的叶子节点内容。
  • 聚集索引适用于范围查询或范围扫描操作，非聚集索引适用于单值查询。

7.什么是回表查询?

回表查询是数据库中一种常见的查询操作，指的是在使用索引进行查询时，需要根据索引定位到数据行后，再通过数据行的地址（即主键）到表中查找对应的数据。这个过程就称为回表查询。

回表查询会增加额外的IO操作，降低查询性能，因此在设计数据库索引时需要尽量避免回表查询。

简单来说，就是先通过非聚集索引查询到聚集索引，再通过聚集索引到整行数据，这个过程被称之为回表查询。

聚集索引一般是主键，如果没有主键，则使用第一个唯一索引（UNIQUE）作为聚集索引。

如果没有主键，也没有合适的唯一主键，则InnoDB引擎会自动生成一个rowid作为隐藏的聚集索引

8.什么是覆盖索引?

覆盖索引是在查询时使用了索引，并且需要返回的列，在该索引中能够全部找到。

简单来说，查询时使用了索引，并且需要返回的列，在该索引中都能够找到，我们就称之为覆盖索引.

覆盖索引是一种特殊的索引类型，它包含了查询所需的所有字段，而不仅仅是索引的键值。当查询需要的字段都包含在覆盖索引中时，数据库引擎可以直接从索引中获取数据，而不需要再去查找实际的数据行，这样可以减少IO操作和提高查询性能。

覆盖索引通常用于查询中只需要返回索引字段的情况下，可以有效地提升查询性能。

9.如何使用覆盖索引处理超大分页？

借助覆盖索引的思路，可以通过覆盖索引+超大分页的思路来进行性能优化。

示例如下：

-- 示例1：
select * from  table_name where id in (
     select id from table_name where xxx=xxx limit 999999999,10
)
-- 示例2：
select * from table_name as t1, 
             (select id from table_name where xxx limit 999999999,10) as t2
         where t1.id=t2.id

实现思路：子查询通过命中索引，返回主键ID。再使用主键ID去查询，则可以节省大量IO开销。

10.什么样的表可以考虑创建索引？

1.针对数据量比较大，且查询比较频繁的表

2.针对于常作为查询条件（where），排序（order），分组（group）操作的字段建立索引

3.尽量使用区分度较高的列作为索引，尽量建立唯一索引。区分度越高，使用索引的效率越高

4.如果是字符串类的字段，且存储的内容比较长，可以针对字符串的特点，建立前缀索引。即截取前边的一部分内容作为索引即可。

5.尽量使用联合索引，减少单列索引，查询时，联合索引可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6.合理的控制索引的数量，索引并不是多多益善。索引越多，维护索引的代价就会越大，会影响到增三改的效率。

7.如果索引列不能为NULL值，请在创建表时使用NOT NULL来约束它。当优化器知道每列是否包含NULL值时，可以更好的确定哪个索引能最有效的用于查询。

总结：

  1. 确保索引覆盖查询：索引应该覆盖最频繁的查询，以提高查询性能。
  2. 避免创建过多索引：创建过多的索引会增加数据库的维护成本和降低性能。
  3. 为频繁查询的字段创建索引：为经常用于查询的字段创建索引，可以加快查询速度。
  4. 为外键字段创建索引：为外键字段创建索引可以加快连接查询的速度。
  5. 索引字段选择性要高：选择性是指索引列的唯一性，选择性越高，索引的效率越高。
  6. 考虑索引的大小：索引的大小会影响查询性能，尽量选择较小的数据类型来创建索引。
  7. 定期维护索引：定期对索引进行优化和维护，包括重新构建索引、删除不需要的索引等操作。

11.什么情况下索引会失效？

  1. 违反最左前缀法则：如果对多列创建可索引，在使用索引时应当从最左列开始，按索引字段的顺序书写筛选条件。
  2. 范围查询右侧的列不能使用索引： 多列索引中，如果在查询条件里使用了范围查询的条件，则它右侧的列将不会在命中索引。
  3. 索引列上使用了函数或表达式，都可能会使索引失效。
  4. 索引列上发生任何的类型转换都可能会导致索引失效
  5. 以%开头的like模糊查询，会导致索引失效：如果仅仅是尾部模糊，则索引不会失效。
  6. **使用了非等值条件，**比如 NOT，<>，!= 等非等值条件时可能会导致索引失效，因为这些条件不是精确匹配，数据库无法利用索引直接查找
  7. 索引列参与了查询中的 or 条件，可能会导致索引失效。
  8. 数据量非常小的时候，数据库可能也会放弃索引，而是用全盘扫描

索引是否失效，可以使用explain执行计划来分析。

12.事务的特性是什么？

事务是一组操作的组合，它是一个不可分割的操作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求。即这些操作要么全部成功，要么全部失败。

简单来说，事务的特性就是ACID

  1. 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部失败回滚，保证数据的完整性。
  2. 一致性（Consistency）：事务执行前后，数据库从一个一致状态转换到另一个一致状态，不会破坏数据的完整性。
  3. 隔离性（Isolation）：事务之间相互隔离，一个事务的执行不会受到其他事务的影响，保证事务的独立性。
  4. 持久性（Durability）：一旦事务提交，对数据库的修改将永久保存，即使系统发生故障也不会丢失数据。

这些特性保证了数据库事务的可靠性和稳定性。

13.并发事务会带来哪些问题？

  1. 脏读：一个事务读取了另一个事务未提交的数据。
  2. 不可重复读：一个事务多次读取同一数据，但在读取的过程中，另一个事务对该数据进行了修改，导致读取结果不一致。
  3. 幻读：一个事务读取了一组数据，但在后续读取同一组数据时，发现数据数量或内容发生了变化，导致读取结果不一致。
  4. 丢失更新：两个事务同时对同一数据进行修改，但其中一个事务的修改被另一个事务覆盖，导致数据丢失。

为了避免并发事务带来的问题，可以使用锁机制、事务隔离级别、乐观锁或悲观锁等方式来控制并发访问。

14.MySql的隔离级别有哪些？

MySQL 默认的隔离级别有四种，分别是：

  1. READ UNCOMMITTED（读未提交）：允许事务读取未提交的数据，可能会导致脏读、不可重复读和幻读问题。
  2. READ COMMITTED（读已提交）：保证事务只能读取到已提交的数据，可以避免脏读问题，但依然可能出现不可重复读和幻读问题。
  3. REPEATABLE READ（可重复读）：默认隔离级别，保证事务在多次读取同一数据时，结果始终一致，可以避免不可重复读和幻读问题。
  4. SERIALIZABLE（串行化）：最高的隔离级别，保证事务之间完全隔离，可以避免所有并发问题，但会导致性能下降。

会话级别设置事务级别：

-- 会话级别设置事务级别：
-- isolation_level的值可以是 READ UNCOMMITTED，READ COMMITTED，REPEATABLE READ，SERIALIZABLE
set transaction isolation level <isolation_level>

-- 全局设置事务隔离级别（在MySQL的配置文件中设置）：
transaction-isolation=<isolation_level>

15.undo log 和redo log的区别

Undo log和redo log是数据库系统中用于实现事务的ACID属性的两种日志机制。

**Undo log记录了事务执行前的数据状态，用于在事务回滚时恢复数据到之前的状态。**当事务执行过程中发生错误或者事务被回滚时，数据库系统会根据undo log中的信息将数据恢复到事务执行前的状态。undolog可以实现事务的一致性和持久性

**Redo log记录了事务执行过程中对数据的修改操作，用于在事务提交时将数据持久化到磁盘。**当数据库系统发生宕机或者崩溃时，可以通过redo log中的信息来恢复事务提交后的数据状态。redolog可以实现事务的持久性。

因此，**undo log主要用于事务回滚和数据恢复，而redo log主要用于事务提交和数据持久化。**两者在数据库系统中起着互补的作用，共同保证了事务的原子性、一致性和持久性。

扩展概念：

缓冲池（buffer pool）：主内存中的一个区域，里面可以缓存磁盘上经常操作的数据。在增删改查操作时，先操作缓冲池中的数据（若缓冲池中没有，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少IO开销，加快处理速度。

数据页（page）：数据页是InnoDB存储引擎磁盘管理的最小单元，每页大小16KB，页中存储的是行数据。

总结：

redo log 记录的是数据页的物理变化，服务宕机可用来同步数据

undo log记录的是数据的逻辑日志，当事务回滚时，通过逆向操作来恢复数据

redo log保证了事务的持久性，undo log 保证了事务的原子性和一致性。

16.事务的隔离性是如何保证的？

使用排他锁和并发控制机制（MVCC）实现。

数据库管理系统使用锁和并发控制机制来确保事务之间的隔离性。具体来说，数据库管理系统会根据事务的隔离级别来确定事务之间的隔离程度，常见的隔离级别包括读未提交、读已提交、可重复读和串行化。

17.什么是MVCC？

MVCC是一种数据库并发控制技术，。它通过在数据库中存储多个版本的数据来实现并发控制，以提高数据库的并发性能和可靠性。

在MVCC中，每个事务在执行时会看到一个特定的数据库版本，而不会被其他事务的操作所影响。这样可以避免读取到脏数据或数据不一致的情况，提高了数据库的并发性能和事务的执行效率。

简单来讲，MVCC维护了一条数据的多个版本，使得读写操作没有冲突。

MVCC的具体实现，主要依赖于数据库记录中的隐式字段，undolog 日志，readView.

18.MySQL主从原理是什么？

核心是二进制日志。

二进制文件（binlog）中记录了所有DDL（数据定义语句）语句和DML（数据操纵语句）语句，不包含数据查询（select 和 show）语句、

具体步骤：

  1. 主库提交数据时，会把数据变更记录存储在二进制文件binlog中，
  2. 从库读取主库的二进制文件Binlog，写到从库的中继日志 RelayLog中
  3. 从库重做中继日志中的事件，将改变反映到自己的数据中。

MySQL的主从复制主要用于解决数据库的访问压力

19.数据库的分库分表的策略有哪些？

数据库分库分表主要用于解决存储压力。
在这里插入图片描述

1.垂直分库：以表为依据，根据业务将不同的表拆分到不同的库中。

在这里插入图片描述

特点：按照业务分级管理，维护，监控，扩展；在高并发下，提高磁盘IO的数据库的连接数。

2.垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中

拆分规则：把不常用的字段单独放在一张表；把text,blob等大字段拆分出来放在附表中。

特点：冷热数据分离；减少IO过渡争抢，两表互不影响。

3.水平分库：将一个库的数据拆分到多个表中

在这里插入图片描述

特点：解决了单库大数据量，高并发的性能瓶颈问题；提高了系统的稳定性和可用性。

4.水平分表：将一个表的数据拆分到多个表中（可以是同一个库内）

特点：优化单表数据量过大而产生的性能问题；避免IO争抢并减少锁表的几率。

19.分库分表容易带来哪些问题？

1.分布式事务一致性问题

2.跨节点关联问题

3.跨节点分页，排序函数

4.主键避重

以上问题，可以通过中间件来解决，常用的中间件有 sharding-sphere，mycat。

上一篇：ASP.NET MVC企业级程序设计 (接上个作品加了添加)


下一篇：MySQL学习笔记5——函数和索引-一、函数