结丹期

8. 如何在 MySQL 中创建索引？索引的作用是什么？

索引 是 MySQL 中用于加速数据检索的数据库对象。它类似于书的目录，可以快速定位数据。

索引的主要作用是提升查询性能，但会增加数据的插入、更新和删除的成本。

创建索引的语法

-- 创建单列索引
CREATE INDEX idx_user_name ON users (username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

-- 创建组合索引
CREATE INDEX idx_order_user_date ON orders (user_id, order_date);

CREATE INDEX 用于创建索引。上面示例中的 idx_user_name 是针对 users 表的 username 列创建的普通索引。

索引提高了查询性能，尤其在 WHERE、JOIN 和 ORDER BY 等查询中。

组合索引 可以覆盖多列，适用于多个列常用来进行筛选的场景。

索引的作用

  • 加快数据检索，提升 SELECT 查询速度。

  • 通过唯一索引，保证数据的唯一性。

9. MySQL 中的查询优化器（Query Optimizer）如何工作？如何查看和优化查询执行计划？

查询优化器 是 MySQL 的一个重要组件，负责解析和优化 SQL 查询，选择最优的执行路径（如表扫描、索引扫描、嵌套循环、排序等）。

优化器通过分析 SQL 语句和表结构，决定最有效的执行顺序。

查看查询执行计划

使用 EXPLAIN 语句可以查看 MySQL 对某个查询的执行计划。

EXPLAIN SELECT username FROM users WHERE id = 1;

EXPLAIN 输出的关键字段：

  • id：查询的步骤编号。

  • select_type：查询的类型（如 SIMPLE、PRIMARY、SUBQUERY）。

  • type：连接类型，表示查询的性能（ALL 表示全表扫描，index 表示索引扫描）。

  • possible_keys：查询中可能使用的索引。

  • key：查询实际使用的索引。

  • rows：MySQL 估计需要读取的行数。

  • extra：附加信息，如 Using index 表示索引覆盖查询。

优化查询的常见方法

  • 添加索引，减少全表扫描。

  • 优化 SQL 语句的 WHERE、JOIN 和 ORDER BY 子句。

  • 分解复杂查询，减少嵌套子查询。

10. 什么是事务（Transaction）？MySQL 如何处理事务？

事务 是指一组 SQL 语句的逻辑单元，确保数据的一致性。

事务具有四个关键特性，称为 ACID 原则：

  • 原子性（Atomicity）：事务中的操作要么全部完成，要么全部失败。

  • 一致性（Consistency）：事务完成后，数据库必须从一个一致状态转换到另一个一致状态。

  • 隔离性（Isolation）：不同事务的操作相互隔离。

  • 持久性（Durability）：事务完成后，其对数据库的修改是永久的，即使系统崩溃也不会丢失。

MySQL 中的事务操作

-- 开始事务
START TRANSACTION;

-- 事务中的多个操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交事务
COMMIT;

-- 如果发生错误，回滚事务
ROLLBACK;

事务确保在资金转账场景下，余额更新要么全部成功，要么全部失败，保证数据一致性。

11. MySQL 中的事务隔离级别有哪些？它们之间的区别是什么？

MySQL 提供了四种 事务隔离级别，用于控制事务之间的并发访问。

READ UNCOMMITTED（未提交读）：

  • 事务可以读取其他未提交事务的数据，可能导致脏读。

  • 最低的隔离级别。

READ COMMITTED（提交读）：

  • 只能读取已提交的事务的数据，避免了脏读，但仍然可能出现不可重复读。

REPEATABLE READ（可重复读）（MySQL 的默认隔离级别）：

  • 保证在同一个事务中多次读取相同的数据时，结果是相同的，避免了脏读和不可重复读。

  • 但仍可能产生幻读。

SERIALIZABLE（可串行化）：

  • 最严格的隔离级别，将事务完全串行化执行，避免了脏读、不可重复读和幻读，但性能开销大。

示例：

# 设置事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

区别

  • 隔离级别越高，并发性能越低，但数据一致性越强。

  • REPEATABLE READ 是 MySQL 的默认级别，适合大多数应用场景。

12. 什么是视图（View）？如何创建和使用视图？

视图 是基于查询的虚拟表，存储的是 SQL 查询而不是实际数据。

视图可以简化复杂查询、提高安全性、隐藏底层表的细节。

创建视图：

CREATE VIEW active_users AS
SELECT username, email
FROM users
WHERE status = 'active';

使用视图：

SELECT * FROM active_users;

上面的视图 active_users 返回所有状态为活跃的用户。通过视图，我们可以简化查询并避免直接操作底层表。

13. 什么是联合查询（JOIN）？MySQL 中的 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN 有什么区别？

联合查询 用于根据两个或多个表之间的关系，返回组合后的数据。

MySQL 中常见的 JOIN 类型有：

  • 内连接 INNER JOIN：只返回两个表中满足条件的交集部分。

  • 左连接 LEFT JOIN：返回左表的所有记录，即使右表中没有匹配的记录。

  • 右连接 RIGHT JOIN：返回右表的所有记录，即使左表中没有匹配的记录。

  • 全连接 FULL JOIN：返回两个表中所有匹配和不匹配的记录（MySQL 不直接支持 FULL JOIN，可通过 UNION 模拟）。

示例

-- INNER JOIN 示例
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

此查询返回 users 表与 orders 表中匹配的记录。

14. 如何在 MySQL 中实现分区（Partitioning）？分区表的作用是什么？

分区 是 MySQL 中将表的数据按某种规则分割成多个部分的技术。

每个分区可以存储在不同的物理文件中，从而提高查询性能，尤其在处理大规模数据时非常有效。

分区类型：

  • RANGE 分区：按范围划分。

  • LIST 分区：按预定义的列表划分。

  • HASH 分区：按哈希函数分布数据。

  • KEY 分区：类似于 HASH，但使用 MySQL 内置函数。

示例：

# 按日期进行 RANGE 分区
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022)
);

此表将 orders 按照 order_date 划分成多个分区，每个分区存储不同年份的数据。

分区表的作用

  • 提高查询性能，尤其在大数据量时，查询可以只扫描一个分区而不是全表。

  • 简化管理，可以对单个分区进行维护（如备份、删除）。

results matching ""

    No results matching ""