数据库基础面试第三弹

2023-09-10 22:30 由空慧居士发表于 #数据库

1. mysql数据库四种常见数据库引擎

1. MyISAM：

MyISAM是MySQL最早的数据库引擎之一。它被设计成处理大量的插入和查询操作。MyISAM表格的数据存储在三个文件上：.frm文件存储表结构，.MYD文件存储数据，.MYI文件存储索引。MyISAM表格不支持事务处理和崩溃恢复，因此在并发写入操作和故障发生时可能会导致数据不一致。然而，MyISAM引擎非常适合于读密集型的应用，例如数据仓库和日志分析。

2. InnoDB：

innoDB是MySQL的默认数据库引擎，也是目前最常用的引擎之一。与MyISAM不同，InnoDB支持事务处理、行级锁定和崩溃恢复。它采用了多版本并发控制（MVCC）来提供高并发性能。InnoDB表格的数据存储在表空间中，可以跨多个文件组织和管理。由于其支持事务和强大的ACID特性，InnoDB引擎适用于大多数应用场景，尤其是需要事务支持和高并发读写的应用。

3. Memory：

Memory（也称为Heap）是一种基于内存的数据库引擎，它将数据存储在内存中而不是磁盘上。这使得它具有非常快的读写速度，但也意味着数据在数据库重新启动时会丢失。Memory引擎非常适合于需要快速读写和临时数据存储的应用，例如缓存表格或会话管理。

4. Archive：

Archive引擎是一种用于存档数据的特殊引擎。它通过使用压缩算法将数据以非常高的压缩比率存储在磁盘上，并提供了快速的插入性能。Archive引擎不支持索引，只支持顺序访问。它适用于存储和查询历史数据或用于数据备份的场景。

主要区别

MyISAM和InnoDB是MySQL中最常见的两种数据库引擎，它们具有以下区别和特点：

MyISAM：

不支持事务处理和崩溃恢复。在并发写入操作和故障发生时，可能导致数据不一致。
适合于读密集型应用，如数据仓库和日志分析。
具有较快的插入和查询速度，因为它采用了简单的表格锁定机制。
存储结构使用三个文件：.frm文件存储表结构，.MYD文件存储数据，.MYI文件存储索引。

InnoDB：

支持事务处理、行级锁定和崩溃恢复。具备强大的ACID特性。
适合于大多数应用场景，尤其是需要事务支持和高并发读写的应用。
采用多版本并发控制（MVCC）来提供高并发性能和高度的数据一致性。
数据存储在表空间中，可以跨多个文件组织和管理。

总结：

MyISAM适合读密集型应用，而InnoDB适合读写并发性要求较高的应用。
MyISAM不支持事务处理和崩溃恢复，而InnoDB具备强大的事务支持和崩溃恢复能力。
MyISAM使用表锁定，而InnoDB使用行级锁定，使得InnoDB能够提供更好的并发性能和数据一致性。
MyISAM的插入和查询速度较快，而InnoDB的读写性能相对较好。
MyISAM的存储结构简单，而InnoDB的存储结构更复杂，支持更多高级特性。

2. Delete 和 Drop区别是什么

Delete和Drop是在数据库中执行不同操作的两个关键词，它们的区别如下：

Delete（删除）：
Delete用于从数据库表中删除行或记录。它是一个DML（数据操作语言）命令，可以通过指定条件来选择要删除的行。Delete语句执行后，被选中的行将从表中永久删除。

关键点：

Delete语句只删除表中的行，而不删除表本身。
Delete语句可以使用WHERE子句来指定删除的条件。如果没有指定WHERE子句，将删除表中的所有行。
Delete操作是事务安全的，可以通过回滚操作撤销删除操作。
Delete操作会触发表上的触发器（如果有定义）。
Delete操作可能会影响表中的其他行和相关的约束。

Drop（删除）：
Drop用于从数据库中完全删除数据库对象，如表、视图、索引、存储过程等。它是一个DDL（数据定义语言）命令，可以删除整个对象及其相关的结构和数据。Drop操作是不可逆的，一旦执行，对象将被永久删除。

关键点：

Drop语句可以用于删除表、视图、索引、存储过程等数据库对象。
Drop语句将删除对象本身以及与该对象相关的约束、索引、触发器等。
Drop操作是不可逆的，一旦执行将无法恢复。
Drop操作会导致相关的数据和结构完全丢失。

总结：
Delete是用于删除表中的行或记录，是DML命令，可以通过条件选择要删除的行，操作是可撤销的。而Drop是用于完全删除数据库对象，是DDL命令，操作是不可撤销的，删除后对象及其相关的结构和数据都将丢失。

3. 数据库中的连接，例如内，左，右等连接。

内连接（Inner Join）：
内连接根据连接条件从两个或多个表中获取匹配的行。它只返回符合连接条件的行，即两个表中连接列的值相等的行。内连接只返回匹配的行，未匹配的行将被排除。

示例：
假设我们有两个表：Customers（客户）和 Orders（订单），它们通过 CustomerID（客户ID）列连接。以下是一个内连接的示例，将返回具有匹配 CustomerID 的行：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

应用：
内连接常用于需要从多个表中获取相关数据的操作，例如在订单管理系统中，通过内连接可以获取每个订单对应的客户信息。

左连接（Left Join）：
左连接返回左表中所有的行，以及与右表匹配的行。如果右表中没有匹配的行，则返回 NULL 值。

示例：
继续使用上述 Customers 和 Orders 表，以下是一个左连接的示例，将返回所有客户以及与之相关联的订单（如果有的话）：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

应用：
左连接常用于需要获取左表中所有数据以及与之相关联的右表数据的场景，例如在客户管理系统中，可以使用左连接获取所有客户及其对应的订单信息，即使某些客户没有订单。

右连接（Right Join）：
右连接返回右表中所有的行，以及与左表匹配的行。如果左表中没有匹配的行，则返回 NULL 值。

示例：
继续使用上述 Customers 和 Orders 表，以下是一个右连接的示例，将返回所有订单以及与之相关联的客户（如果有的话）：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

应用：
右连接常用于需要获取右表中所有数据以及与之相关联的左表数据的场景，例如在订单管理系统中，可以使用右连接获取所有订单及其对应的客户信息，即使某些订单没有关联的客户。

外连接（Full Outer Join）：
外连接返回左表和右表中的所有行，如果没有匹配的行，则返回 NULL 值。

示例：
继续使用上述 Customers 和 Orders 表，以下是一个外连接的示例，将返回所有客户和订单，包括匹配和非匹配的行：

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

应用：
外连接常用于需要获取左表和右表所有数据的场景，即使某些行没有匹配的数据。它可以用于数据比较和合并操作。

需要注意的是，连接操作不仅限于两个表，可以连接多个表。连接条件可以是多个列的组合，不仅仅限于相等条件。在实际应用中，根据具体的数据关系和查询需求，选择适合的连接类型来获取所需的数据。

4. 数据库的三大范式：

第一范式（1NF）：
第一范式要求数据库中的每个列都是原子性的，即每个列都应该包含不可再分的数据，不允许重复的列或多值的列。

关键点：

每个表中的每个列都应该是原子的，不可再分的。
消除重复数据，避免在同一列中存储多个值。
每个表应该有一个唯一的主键来标识每一行。

应用：
例如，假设有一个包含学生信息的表，每个学生可能有多个电话号码。为了满足第一范式，可以将电话号码拆分为独立的表，并使用学生ID作为关联键。

第二范式（2NF）：
第二范式要求数据库中的每个非主键列都完全依赖于主键，即每个非主键列都应该与主键具有完整依赖关系，而不是部分依赖。

关键点：

每个非主键列都应该完全依赖于主键。
如果存在部分依赖，应该将相关的列拆分到独立的表中。

应用：
继续以学生信息表为例，假设主键是学生ID和课程ID的组合。如果存在一个非主键列是课程名称，那么课程名称应该与学生ID和课程ID一起作为复合主键，以满足第二范式。

第三范式（3NF）：
第三范式要求数据库中的每个非主键列都不传递依赖于主键，即每个非主键列都应该直接依赖于主键，而不是依赖于其他非主键列。

关键点：

每个非主键列都应该直接依赖于主键，而不是依赖于其他非主键列。
消除传递依赖，避免非主键列之间的依赖关系。

应用：
继续以上述学生信息表为例，假设除了学生ID和课程ID之外，还存在一个非主键列是学生姓名。如果学生姓名依赖于课程ID而不是学生ID，那么应该将学生姓名拆分到与课程ID相关的独立表中，以满足第三范式。

5. 产生慢查询的原因以及优化方法

索引问题：

原因：查询没有使用到合适的索引，导致数据库需要进行全表扫描或大量的数据页读取，从而降低查询性能。
优化方法：通过分析查询语句和表结构，确定合适的索引并创建/优化索引。使用EXPLAIN语句来查看查询执行计划，确保查询能够充分利用索引。

数据库统计信息不准确：

原因：数据库统计信息（如表的行数、索引的唯一值等）不准确，导致优化器无法做出正确的查询执行计划。
优化方法：更新数据库统计信息，可以使用ANALYZE TABLE语句手动重新计算统计信息，或者配置自动统计信息更新。

复杂查询逻辑：

原因：查询语句过于复杂，包含多个嵌套的子查询、连接操作或函数运算，导致查询执行时间较长。
优化方法：简化查询逻辑，尽量减少不必要的子查询和连接操作。考虑使用更有效的查询方式，如JOIN操作替代子查询。

数据库连接或服务器配置问题：

原因：数据库连接池配置不合理、数据库服务器负载过高或配置不足，导致查询响应时间延长。
优化方法：优化数据库连接池配置，增加数据库服务器的硬件资源，如CPU、内存等。调整数据库参数和服务器参数，以提高性能和并发处理能力。

数据库设计问题：

原因：数据库表结构设计不合理，导致查询需要涉及大量的表连接、冗余数据或无效数据。
优化方法：重新评估数据库表结构，进行合理的数据库设计和范式化。优化数据模型，消除冗余和无效数据，以提高查询性能。

数据量过大：

原因：数据库中的数据量过大，导致查询需要处理大量的数据，影响查询性能。
优化方法：考虑对大表进行分区或分片，以减少单个查询需要处理的数据量。使用分页查询、缓存、异步处理等技术来优化数据访问方式。

锁竞争和并发问题：

原因：并发查询导致锁竞争，或者长时间的事务导致其他查询等待。
优化方法：优化事务的设计，尽量缩短事务的执行时间。合理设置事务隔离级别，减少锁竞争。使用行级锁而不是表级锁，以提高并发性能。

硬件和网络问题：

原因：数据库服务器硬件配置不足，网络带宽不足等问题。
优化方法：增加硬件资源，如CPU、内存、磁盘等，以提高数据库服务器的性能。优化网络连接，确保网络带宽和延迟满足需求。

6. 如何防止SQL 注入：

当应用程序未正确过滤用户输入时，攻击者可以通过各种方式注入恶意的SQL代码。以下是一个简单的SQL注入示例：

假设有一个登录表单，用户需要输入用户名和密码来进行认证。应用程序的代码可能是这样的：

username = getRequestParameter("username");
password = getRequestParameter("password");

sql = "SELECT * FROM users WHERE username='" + username + "' AND password='" + password + "'";

在正常情况下，应用程序会将用户输入的用户名和密码与数据库中的记录进行比较以验证用户身份。然而，如果未对用户输入进行适当的验证和过滤，攻击者可以输入恶意的数据来注入SQL代码。

例如，攻击者可以在用户名字段输入以下内容：

' OR '1'='1'; --

修改后的查询语句将变为：

SELECT * FROM users WHERE username='' OR '1'='1'; --' AND password='password'

由于'1'='1'永远为真，所以查询将返回所有用户的记录，绕过了密码验证。

攻击者还可以利用注释符（--）将原始的密码验证部分注释掉，从而达到绕过密码验证的目的。

为了防止SQL注入攻击，可以采取以下措施：

使用参数化查询（Prepared Statements）或存储过程：
- 参数化查询：使用预编译的SQL语句，并将用户输入的值作为参数传递给查询。这样可以确保用户输入的数据不会被解释为SQL代码的一部分。
- 存储过程：将SQL逻辑封装在存储过程中，并通过调用存储过程来执行查询。存储过程可以有效防止SQL注入攻击。
- ```
username = getRequestParameter("username")
password = getRequestParameter("password")

# 使用参数化查询
sql = "INSERT INTO users (username, password) VALUES (?, ?)"
params = (username, password)

# 执行查询
cursor.execute(sql, params)
connection.commit()
```
输入验证和过滤：
- 对用户输入进行严格验证和过滤，确保输入数据符合预期的格式和类型。可以使用白名单或正则表达式验证来过滤特定的字符或模式，防止恶意代码的注入。
最小权限原则：
- 为数据库用户分配最小权限，限制其对数据库的操作。确保应用程序连接数据库的用户只具有执行必要操作的权限，避免攻击者利用注入漏洞对整个数据库进行操作。
输入数据的转义：
- 对用户输入的特殊字符进行转义，以确保这些字符被正确地解释为数据而不是代码。这可以通过使用数据库提供的转义函数或工具来实现。
安全更新和维护：
- 及时应用数据库供应商发布的安全更新和补丁，以修复已知的安全漏洞。
- 定期审查和更新应用程序代码，以及相关的数据库查询语句，确保安全性和可靠性。
日志记录和监控：
- 启用详细的日志记录机制，记录所有数据库操作和错误信息。监控和分析日志，及时检测和响应潜在的SQL注入攻击。