每日八股文6.3

Mysql

1.COUNT 作用于主键列和非主键列时，结果会有不同吗？

在大多数情况下，COUNT() 作用于主键列和非主键列时，如果这些列中不包含 NULL 值，那么结果是相同的，都表示符合条件的行数。

然而，关键的区别在于 COUNT(column_name) 的行为：COUNT(column_name) 只计算指定列中非 NULL 值的数量。

• 主键列 (Primary Key Column): 根据定义，主键列的值不允许为 NULL。因此，COUNT(主键列) 实际上等同于 COUNT(*) 或者 COUNT(1)，它会统计表中的总行数（或者符合 WHERE 子句条件的行数）。

• 非主键列 (Non-Primary Key Column):

  • 如果该非主键列不允许为 NULL (定义了 NOT NULL 约束)，那么 COUNT(非主键列) 的结果也会和 COUNT(*) 相同。
  • 如果该非主键列允许为 NULL，并且该列中实际存在 NULL 值，那么 COUNT(非主键列) 的结果将会小于 COUNT(*)，因为它只统计了该列中非 NULL 的行。

总结一下 COUNT 的几种常见用法：

• COUNT(*): 计算表中的总行数（或者符合 WHERE 子句条件的行数），包含 NULL 值的行。这是最常用的方式，通常效率也较好。
• COUNT(1): 效果和 COUNT() 类似，也是计算总行数。MySQL 对其进行了优化，效率与 COUNT() 相当。
• COUNT(column_name): 计算指定列 column_name 中非 NULL 值的行数。
• COUNT(DISTINCT column_name): 计算指定列 column_name 中不重复且非 NULL 值的行数。

-- 创建一个示例表
CREATE TABLE employees (
    id INT PRIMARY KEY,          -- 主键列 (不允许 NULL)
    name VARCHAR(100) NOT NULL, -- 非主键列，不允许 NULL
    department VARCHAR(50),      -- 非主键列，允许 NULL
    salary DECIMAL(10, 2)
);

-- 插入一些数据
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000.00),
(2, 'Bob', 'Engineering', 70000.00),
(3, 'Charlie', 'HR', 55000.00),
(4, 'David', NULL, 60000.00),         -- department 是 NULL
(5, 'Eve', 'Engineering', NULL),      -- salary 是 NULL
(6, 'Frank', NULL, 75000.00);       -- department 是 NULL

-- 查看表中的所有数据
SELECT * FROM employees;

-- 1. COUNT(*) - 统计所有行
SELECT COUNT(*) AS total_rows FROM employees;
-- 预期结果: 6

-- 2. COUNT(主键列) - 统计主键列 'id' (不允许 NULL)
SELECT COUNT(id) AS count_primary_key FROM employees;
-- 预期结果: 6 (与 COUNT(*) 相同，因为主键列不能为 NULL)

-- 3. COUNT(非主键列，NOT NULL) - 统计非主键列 'name' (不允许 NULL)
SELECT COUNT(name) AS count_name_not_null FROM employees;
-- 预期结果: 6 (与 COUNT(*) 相同，因为 'name' 列不允许 NULL)

-- 4. COUNT(非主键列，允许 NULL) - 统计非主键列 'department' (允许 NULL)
SELECT COUNT(department) AS count_department_nullable FROM employees;
-- 预期结果: 4 (因为 'department' 列有两个 NULL 值，它们不被计算在内)

-- 5. COUNT(非主键列，允许 NULL) - 统计非主键列 'salary' (允许 NULL)
SELECT COUNT(salary) AS count_salary_nullable FROM employees;
-- 预期结果: 5 (因为 'salary' 列有一个 NULL 值，它不被计算在内)

2.MySQL 中的内连接（INNER JOIN）和外连接（OUTER JOIN）有什么主要的区别？

• 内连接： 返回两个表中匹配的行（交集）。
• 外连接： 返回匹配的行加上驱动表中不匹配的行。
• 左外连接： 保留左表所有行，右表不匹配则填充 NULL。
• 右外连接： 保留右表所有行，左表不匹配则填充 NULL。

内连接（INNER JOIN）：它会只返回两个表中连接条件相匹配的行。也就是说，只有当连接的两个表中都存在满足 ON 子句条件的记录时，这条记录才会被包含在结果集中。内连接关注的是两个表的交集部分。

外连接（OUTER JOIN）：外连接则会返回两个表中匹配的行，并且还会包含某个表（或两个表）中不匹配的行。MySQL 外连接主要分为两种：

• 左外连接（LEFT JOIN 或 LEFT OUTER JOIN）：它会返回左表中的所有行，以及右表中与左表匹配的行。如果右表中没有与左表某行匹配的记录，那么在结果集中，右表对应的列将会显示为 NULL。左连接适用于需要保留左表所有数据的场景。
• 右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：它会返回右表中的所有行，以及左表中与右表匹配的行。如果左表中没有与右表某行匹配的记录，那么在结果集中，左表对应的列将会显示为 NULL。右连接适用于需要保留右表所有数据的场景。

-- 创建顾客表
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

-- 创建订单表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,        -- 外键，关联到 Customers 表的 CustomerID
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);

-- 插入顾客数据
INSERT INTO Customers (CustomerID, CustomerName) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'); -- Diana 没有下过订单

-- 插入订单数据
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount) VALUES
(101, 1, '2024-01-15', 150.00), -- Alice的订单
(102, 2, '2024-01-20', 200.00), -- Bob的订单
(103, 1, '2024-02-10', 75.50),  -- Alice的另一个订单
(104, 5, '2024-02-15', 300.00); -- 这个订单的CustomerID在Customers表中不存在

-- 查看表数据 (可选)
SELECT * FROM Customers;
SELECT * FROM Orders;

--内连接
SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.Amount
FROM
    Customers c
INNER JOIN
    Orders o ON c.CustomerID = o.CustomerID;

--left join
SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.Amount
FROM
    Customers c  -- 左表
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID; -- 右表

--right join
SELECT
    c.CustomerName,
    o.OrderID,
    o.OrderDate,
    o.Amount
FROM
    Customers c  -- 左表
RIGHT JOIN
    Orders o ON c.CustomerID = o.CustomerID; -- 右表

3.能详细描述一下 MySQL 执行一条查询 SQL 语句的完整流程吗？

首先，客户端会通过连接器与 MySQL 服务器建立连接。连接器负责处理 TCP 连接、用户身份验证和权限校验。

连接成功后，MySQL 会先检查查询缓存（在 MySQL 8.0 版本之前）。如果查询的 SQL 语句和查询条件与缓存中的记录完全一致，并且查询结果未过期，则会直接从缓存中返回结果，避免了后续的执行过程。但由于查询缓存的命中率不高，MySQL 8.0 已经移除了这个功能。

如果查询缓存没有命中或者版本在 8.0 之后，SQL 语句会被发送到解析器。解析器会对 SQL 语句进行词法分析和语法分析，识别出语句的类型、表名、字段名等，并构建一个抽象语法树（AST）。

接下来是优化器的阶段。优化器会根据一定的规则和成本模型，对语法树进行优化，生成最优的执行计划。这包括选择合适的索引、决定表的连接顺序等。优化器的目标是找到执行成本最低的方案。

优化完成后，执行器会根据优化器生成的执行计划，调用存储引擎（如 InnoDB、MyISAM 等）提供的接口来执行实际的数据查询操作。执行器会负责控制数据的读取和返回，它就像一个调度员，指挥存储引擎完成任务。查询结果返回给执行器后，由执行器最终返回给客户端。

4.MySQL 中都有哪些常见的存储引擎？你能简单介绍一下吗？

MySQL 中常见的存储引擎主要有 InnoDB、MyISAM 和 Memory。

我比较熟悉的是 InnoDB 引擎，它也是 MySQL 的默认存储引擎。InnoDB 支持事务，具有事务的提交、回滚和崩溃恢复功能，这保证了数据的完整性和一致性。此外，InnoDB 还支持行级锁，这使得在并发环境下可以有更好的性能。

MyISAM 引擎是我在学习过程中了解到的，它不支持事务，只支持表级锁。这意味着当一个会话正在写入 MyISAM 表时，其他会话都无法读取或写入该表，并发性能相对较差。但 MyISAM 在某些只读或者读多写少的场景下，可能会有更高的性能，因为它维护了一个表的行数，执行 COUNT(*) 操作会非常快。

Memory 引擎正如其名，它将数据存储在内存中，因此读写速度非常快。但是，Memory 引擎不支持持久化存储，一旦服务器发生故障或者重启，数据就会丢失。它通常用于临时存储对性能要求较高的中间结果或者作为缓存。

当然，MySQL 还支持其他的存储引擎，比如 Archive、CSV、NDB Cluster 等

5.MySQL 支持哪些常见的索引类型呢？

MySQL 支持多种索引类型，这主要取决于存储引擎的实现。不同的存储引擎支持的索引类型可能会有所不同。

从存储结构上来看，主要的索引类型有：

• B+ 树索引：这是 MySQL 中最常见和最广泛使用的索引类型，尤其是在 InnoDB 和 MyISAM 存储引擎中。它是一种平衡树结构，非常适合范围查询、排序查询和等值查询。
• 哈希索引：这种索引类型主要由 Memory 存储引擎实现。它的特点是查找速度非常快，时间复杂度可以达到 O(1)，但它不支持范围查询、排序等操作。
• 全文索引：全文索引用于在文本类型的数据中进行关键词搜索，可以支持模糊匹配等功能。它主要用于解决 LIKE ‘%keyword%’ 这类查询效率低下的问题。

此外，我们还可以从其他角度对索引进行分类：

• 按存储方式（针对 InnoDB 引擎）：可以分为聚簇索引和非聚簇索引。
• 按字段特性：可以分为主键索引、唯一索引、普通索引和前缀索引。
• 按索引列数：可以分为单列索引和联合索引。

我个人比较常用的是 B+ 树索引，因为它是 InnoDB 引擎默认使用的索引类型，而且功能非常全面，支持各种常见的查询场景。

顺便提一下，MySQL 选择使用树结构（特别是 B+ 树）作为索引，主要是因为树结构能够很好地支持范围查询，并且在磁盘 I/O 方面做了优化，更适合数据库这种需要频繁进行磁盘操作的场景。像哈希索引虽然查找快，但不支持范围查询，而跳表在磁盘场景下的适应性不如树结构。

6.InnoDB 存储引擎的索引底层是基于什么数据结构实现的呢？

InnoDB 存储引擎主要采用 B+ 树作为其索引的数据结构。

B+ 树是一种自平衡的多路查找树。对于一个度数为 m 的 B+ 树，它的每个节点最多可以有 m 个子节点，并且所有的数据都存储在叶子节点上。叶子节点之间通常会通过指针连接起来，形成一个有序链表（在 InnoDB 中，这个链表是双向的）。

B+ 树的一些关键特性使得它非常适合作为数据库索引的底层结构，比如它的平衡性减少了磁盘的IO次数，保证了查询效率，数据只存储在叶子节点的特性以及叶子节点之间的链表结构都为范围查询提供了便利。

7.能详细说说 B+ 树这种数据结构都有哪些特点吗？

B+ 树作为一种常用的索引结构，它有几个非常重要的特性：

• 首先，B+ 树的中间节点（也就是非叶子节点）不会存储实际的数据，它们只存储索引信息（也就是键值）以及指向子节点的指针。这样做的好处是，在相同的磁盘页大小下，中间节点可以存储更多的索引，从而使得 B+ 树更加“矮胖”，降低了树的高度。更低的树高度意味着查询时需要访问的磁盘 I/O 次数更少，提高了查询效率。
• 其次，B+ 树的所有实际数据都存储在叶子节点上。每个叶子节点包含了索引的键值以及对应的数据记录。
• 第三个重要的特性是，B+ 树的所有叶子节点之间会通过双向指针（在 InnoDB 中）串联在一起，形成一个双向链表。这个特性对于进行范围查询（例如查找某个区间内的数据）非常高效。我们只需要找到范围的起始叶子节点，然后沿着链表顺序遍历即可，不需要再回到上层节点进行搜索。同时，这个链表也方便进行全表扫描。
• 最后，B+ 树的查询性能非常稳定。因为所有的数据都存储在叶子节点，所以每次查询都需要从根节点遍历到叶子节点，查询路径的长度是相同的，这确保了所有数据项的检索都具有相同的 I/O 延迟。而且，由于 B+ 树通常能够保持很低的树高度（例如，3-4 层的 B+ 树可以存储千万级别的数据），因此查询效率非常高。

8.B+ 树和 B 树这两种数据结构有什么主要的区别呢？

B+ 树和 B 树都是常用的多路平衡查找树，它们之间主要的区别可以从以下几个方面来说明：

• 数据存储的区别：B 树的所有节点（包括中间节点和叶子节点）都会存储索引和实际的数据，而 B+ 树只有叶子节点才会存储实际的数据，中间节点只存储索引信息和指向子节点的指针。这意味着在存储相同数据量的情况下，B+ 树的中间节点可以存储更多的索引，因此 B+ 树通常会比 B 树更加“矮胖”，树的高度更低，从而减少了查询叶子节点所需的磁盘 I/O 次数。
• 范围查询的区别：B+ 树的所有叶子节点之间会通过双向指针串联在一起，形成一个有序链表。这种结构使得 B+ 树在进行范围查询时非常高效。我们只需要找到范围的起始点，然后沿着链表顺序遍历即可。而 B 树并没有将叶子节点用链表连接起来，进行范围查询时可能需要通过中序遍历，这可能会涉及更多节点的磁盘 I/O 操作，因此在范围查询方面，B+ 树通常比 B 树更高效。
• 查询效率的区别：在 B 树中，如果我们要查找的值恰好在某个非叶子节点上，那么在查找到该节点后就可以直接结束查询，B 树的优点在于其查找速度可能更快，尤其是在要查找的数据位于靠近根节点的非叶子节点时。然而，B+ 树由于数据只存储在叶子节点，所以每次查询都必须从根节点搜索到叶子节点，查询路径的长度是固定的。从平均时间代价来看，B 树可能在某些情况下会比 B+ 树稍快一些，但是 B+ 树的查询性能更加稳定，因为每一次成功的查询都需要访问到叶子节点，具有相同的 I/O 延迟。

9.你觉得为什么 MySQL 会选择使用 B+ 树作为其索引结构呢？

MySQL 选择使用 B+ 树作为其索引结构，我认为主要有以下几个重要的原因：

• 首先，B+ 树是一种多路平衡查找树，这种结构非常适合磁盘 I/O 操作。数据库的数据通常存储在磁盘上，而每次磁盘 I/O 的成本是比较高的。B+ 树的非叶子节点只存储索引键值和子节点指针，而不存储实际的数据，这使得每个节点能够存储更多的索引，从而显著降低了树的高度。例如，一个 3-4 层的 B+ 树就可以存储千万级别的数据，这意味着查询一条数据只需要 3-4 次磁盘 I/O 操作，大大提高了查找效率。相比之下，像二叉搜索树（包括平衡树和红黑树），在存储大量数据时树的高度会很高，导致更多的磁盘 I/O。
• 其次，B+ 树通过自平衡的机制（节点的分裂和合并）来保持树的平衡，这确保了查询路径的长度是相对稳定的，从而保证了查询、插入和删除操作的时间复杂度都是 O(log n)，具有较好的性能稳定性。对于频繁进行增删改操作的数据库来说，这一点非常重要。
• 第三，B+ 树特别适合范围查询。这是因为 B+ 树的所有叶子节点都通过链表连接在一起，形成一个有序链表。在进行范围查询时，我们只需要找到范围的起始叶子节点，然后沿着链表顺序扫描即可，非常高效。而像 B 树在进行范围查询时可能需要进行多次中序遍历。
• 此外，对比其他数据结构，例如哈希表虽然等值查询很快，但是不支持范围查询；跳表在内存中表现良好，但在磁盘场景下不如 B+ 树；B 树虽然所有节点都存储数据，可能在某些情况下等值查询更快，但其范围查询性能不如 B+ 树，且查询性能不如 B+ 树稳定。

综上所述，B+ 树在平衡性、查询效率（特别是范围查询）、以及对磁盘 I/O 的优化等方面都非常适合数据库索引的需求，因此成为了 MySQL 等主流数据库系统的首选索引结构。

10.聚簇索引和非聚簇索引之间有什么主要的区别？

聚簇索引和非聚簇索引是 MySQL 中两种主要的索引类型，它们最主要的区别在于 B+ 树的叶子节点所存储的内容不同：

• 聚簇索引：在 InnoDB 存储引擎中，主键索引就是聚簇索引。聚簇索引的 B+ 树的叶子节点存储的是完整的行数据，包含了该行所有列的值。由于数据和索引是存储在一起的，所以通过聚簇索引查找数据时，可以直接获取到整行数据，不需要进行额外的查找。一个表只能有一个聚簇索引，通常是表的主键。
• 非聚簇索引（也叫二级索引或辅助索引）：非聚簇索引的 B+ 树的叶子节点存储的不是完整的行数据，而是索引列的值以及对应行的主键 ID。如果我们需要查询的数据列不是索引列本身，也不是主键，那么在使用非聚簇索引查询到主键 ID 后，通常还需要回到聚簇索引中再进行一次查找，才能获取到完整的行数据，这个过程被称为“回表”。一个表可以有多个非聚簇索引。

因此，从查询效率上来说，通常情况下，通过聚簇索引查询数据的速度会更快，因为只需要扫描一次 B+ 树就可以获取到所需的数据。而通过非聚簇索引查询时，可能需要扫描两次 B+ 树（一次非聚簇索引，一次聚簇索引，即回表）。

另外，需要补充一点的是，如果一个表没有显式地定义主键，InnoDB 会默认选择一个唯一的非空索引作为聚簇索引。

CREATE TABLE users (
    id INT PRIMARY KEY,         -- 聚簇索引 (主键)
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

-- 在 email 列上创建非聚簇索引
CREATE INDEX idx_email ON users (email);

-- 由于 name 和 age 列不在 idx_email 的叶子节点中，数据库必须使用 id = 10 这个主键值去聚簇索引中查找完整的行数据，然后取出 name 和 age。这就是“回表”
SELECT name, age FROM users WHERE email = 'test@example.com';

-- 查询所需的所有信息都在索引的叶子节点中找到了，无需回表。 这个查询就是使用了覆盖索引
SELECT email, id FROM users WHERE email = 'test@example.com';

-- 即使只查询 email 字段，也构成覆盖索引
SELECT email FROM users WHERE email = 'test@example.com';