08-数据库面试高频题

-- 假设有1000万用户数据
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_age (age)
);

-- 查询某个年龄段的用户
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- B+树优势：
-- 1. 通过索引快速定位到age=20的叶子节点
-- 2. 沿着叶子节点链表顺序扫描到age=30
-- 3. 如果使用B树，需要多次回溯父节点，效率低

CREATE TABLE employees (
    id INT PRIMARY KEY,           -- 聚簇索引
    name VARCHAR(50),
    email VARCHAR(100),
    dept_id INT,
    INDEX idx_email (email),      -- 非聚簇索引
    INDEX idx_dept (dept_id)      -- 非聚簇索引
);

INSERT INTO employees VALUES
(1, 'Alice', 'alice@example.com', 10),
(2, 'Bob', 'bob@example.com', 20),
(3, 'Charlie', 'charlie@example.com', 10);

-- 场景1：通过主键查询（使用聚簇索引）
SELECT * FROM employees WHERE id = 2;
-- 执行过程：
-- 1. 在聚簇索引B+树中查找id=2
-- 2. 在叶子节点直接获取完整行数据
-- 磁盘IO：1次（只访问聚簇索引）

-- 场景2：通过普通索引查询（使用非聚簇索引）
SELECT * FROM employees WHERE email = 'bob@example.com';
-- 执行过程：
-- 1. 在idx_email索引树中查找email='bob@example.com'
-- 2. 在叶子节点获取主键id=2
-- 3. 拿着id=2回到聚簇索引树中查找完整数据
-- 磁盘IO：2次（访问非聚簇索引 + 访问聚簇索引）
-- 这个过程称为"回表"

-- 场景3：覆盖索引（无需回表）
SELECT id, email FROM employees WHERE email = 'bob@example.com';
-- 执行过程：
-- 1. 在idx_email索引树中查找email='bob@example.com'
-- 2. 叶子节点包含了id和email，直接返回
-- 磁盘IO：1次（只访问非聚簇索引）
-- 这个过程称为"覆盖索引"

-- 优化建议1：使用覆盖索引避免回表
-- 不好的写法
SELECT * FROM employees WHERE dept_id = 10;

-- 改进写法（如果只需要部分字段）
ALTER TABLE employees ADD INDEX idx_dept_name (dept_id, name);
SELECT id, name FROM employees WHERE dept_id = 10;
-- 索引idx_dept_name包含了dept_id、name、id（自动包含主键）
-- 无需回表

-- 优化建议2：主键尽量使用自增ID
-- 不好的设计
CREATE TABLE orders (
    order_no VARCHAR(50) PRIMARY KEY,  -- UUID或业务编号
    ...
);
-- 问题：
-- 1. 字符串比整数占用空间大
-- 2. 非聚簇索引叶子节点存储的主键值变大
-- 3. 页分裂频繁（UUID无序）

-- 好的设计
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) UNIQUE,
    ...
);
-- 优势：
-- 1. 整数主键占用空间小
-- 2. 自增保证数据插入顺序，减少页分裂
-- 3. 非聚簇索引更紧凑

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50),
    INDEX idx_name_age_city (name, age, city)
);

-- 联合索引(name, age, city)的B+树结构：
-- 先按name排序，name相同再按age排序，age相同再按city排序

-- 1. 使用name（最左列） 
SELECT * FROM users WHERE name = 'Alice';

-- 2. 使用name, age（从最左开始的连续列） 
SELECT * FROM users WHERE name = 'Alice' AND age = 25;

-- 3. 使用name, age, city（完整索引） 
SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Beijing';

-- 4. 使用name, city（跳过age但name是最左列） 
-- 注意：只有name列会走索引，city不会
SELECT * FROM users WHERE name = 'Alice' AND city = 'Beijing';

-- 5. 顺序无关（优化器会调整） 
SELECT * FROM users WHERE age = 25 AND name = 'Alice';
-- MySQL优化器会调整为：name = 'Alice' AND age = 25

-- 1. 只使用age（不是最左列） ✗
SELECT * FROM users WHERE age = 25;

-- 2. 只使用city（不是最左列） ✗
SELECT * FROM users WHERE city = 'Beijing';

-- 3. 使用age, city（跳过了最左列name） ✗
SELECT * FROM users WHERE age = 25 AND city = 'Beijing';

-- 4. name使用范围查询后，后面的列无法使用索引 ✗（部分）
SELECT * FROM users WHERE name LIKE 'A%' AND age = 25;
-- name可以使用索引，但age无法使用索引

-- 情况1：范围查询的影响
-- 索引：(name, age, city)

-- 查询1：name精确匹配，age范围查询
SELECT * FROM users WHERE name = 'Alice' AND age > 20 AND city = 'Beijing';
-- 索引使用情况：
--  name = 'Alice' 使用索引
--  age > 20 使用索引
-- ✗ city = 'Beijing' 不使用索引（age是范围查询，中断了后续列）

-- 查询2：name范围查询
SELECT * FROM users WHERE name > 'Alice' AND age = 25;
-- 索引使用情况：
--  name > 'Alice' 使用索引
-- ✗ age = 25 不使用索引

-- 情况2：函数操作
-- 索引：(name, age, city)

-- 不使用索引
SELECT * FROM users WHERE UPPER(name) = 'ALICE';

-- 使用索引
SELECT * FROM users WHERE name = 'Alice';

-- 情况3：隐式类型转换
-- 假设name是VARCHAR类型

-- 不使用索引（字符串列与数字比较）
SELECT * FROM users WHERE name = 123;
-- MySQL会将name转为数字：CAST(name AS SIGNED) = 123

-- 使用索引
SELECT * FROM users WHERE name = '123';

-- 场景：用户查询接口，支持多种筛选条件

-- 原始查询（性能差）
SELECT * FROM users
WHERE city = 'Beijing'
  AND age BETWEEN 20 AND 30
  AND name LIKE 'A%';

-- 分析：
-- 如果索引是(name, age, city)，查询条件顺序不匹配

-- 优化方案1：调整索引顺序
-- 根据业务场景，假设city筛选度最高，age次之，name最低
ALTER TABLE users DROP INDEX idx_name_age_city;
ALTER TABLE users ADD INDEX idx_city_age_name (city, age, name);

-- 优化后的查询
SELECT * FROM users
WHERE city = 'Beijing'
  AND age BETWEEN 20 AND 30
  AND name LIKE 'A%';
-- 索引使用：city精确匹配 , age范围查询 , name无法使用 ✗

-- 优化方案2：创建多个索引（适合不同查询场景）
ALTER TABLE users ADD INDEX idx_city_age (city, age);
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD INDEX idx_age (age);

-- 不同查询场景使用不同索引
-- 场景1：按城市和年龄查询
SELECT * FROM users WHERE city = 'Beijing' AND age = 25;
-- 使用idx_city_age

-- 场景2：按姓名查询
SELECT * FROM users WHERE name = 'Alice';
-- 使用idx_name

-- 场景3：按年龄查询
SELECT * FROM users WHERE age = 25;
-- 使用idx_age

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（临时生效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录

-- 永久配置（修改my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1  -- 记录未使用索引的查询

-- 示例慢查询
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'completed'
  AND create_time > '2024-01-01';

-- 关键字段分析：
-- type: ALL（全表扫描）- 最差的情况
-- type: index（索引扫描）- 扫描整个索引树
-- type: range（范围扫描）- 使用索引范围查询
-- type: ref（索引查找）- 非唯一索引查找
-- type: eq_ref（唯一索引查找）- 唯一索引或主键查找
-- type: const（常量查找）- 主键或唯一索引的常量查询

-- key: NULL 表示未使用索引
-- rows: 500000 表示扫描了50万行
-- Extra: Using where 表示在Server层进行过滤

-- 问题查询
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'completed'
  AND create_time > '2024-01-01';

-- EXPLAIN显示全表扫描，添加联合索引
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

-- 性能提升：
-- 扫描行数：500000 → 100
-- 查询时间：5秒 → 0.01秒

-- 不好的写法
SELECT * FROM orders WHERE user_id = 12345;

-- 优化写法（只查询需要的字段）
SELECT id, order_no, amount, status FROM orders WHERE user_id = 12345;

-- 进一步优化（使用覆盖索引）
CREATE INDEX idx_user_cover ON orders(user_id, id, order_no, amount, status);

SELECT id, order_no, amount, status FROM orders WHERE user_id = 12345;
-- 使用覆盖索引，避免回表

-- 慢查询示例
SELECT o.*, u.name, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.create_time > '2024-01-01';

-- 优化1：添加索引
CREATE INDEX idx_status_time ON orders(status, create_time);
CREATE INDEX idx_id ON users(id);  -- 通常主键已有索引

-- 优化2：调整JOIN顺序（小表驱动大表）
-- 如果users表较小，可以改为
SELECT o.*, u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
  AND o.create_time > '2024-01-01';

-- 优化3：分步查询（如果关联数据量小）
-- Step 1: 查询订单ID
SELECT id, user_id FROM orders
WHERE status = 'completed'
  AND create_time > '2024-01-01';

-- Step 2: 应用层批量查询用户信息
SELECT id, name, email FROM users WHERE id IN (1, 2, 3, ...);

-- 慢查询：深分页
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
-- 问题：MySQL需要扫描100020行，然后丢弃前100000行

-- 优化方案1：使用子查询（延迟关联）
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 1
)
ORDER BY create_time DESC
LIMIT 20;

-- 优化方案2：使用上次查询的最后一条记录
-- 假设上次查询最后一条记录的create_time和id
SELECT * FROM orders
WHERE create_time < '2024-01-15 10:00:00'
   OR (create_time = '2024-01-15 10:00:00' AND id < 123456)
ORDER BY create_time DESC, id DESC
LIMIT 20;

-- 优化方案3：使用覆盖索引 + 回表
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20
) t ON o.id = t.id;

-- 假设user_id是VARCHAR类型

-- 慢查询（隐式转换）
SELECT * FROM orders WHERE user_id = 12345;
-- MySQL会执行：CAST(user_id AS SIGNED) = 12345
-- 导致索引失效

-- 优化
SELECT * FROM orders WHERE user_id = '12345';

-- 建议：统一数据类型
ALTER TABLE orders MODIFY user_id INT;

-- 慢查询
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-15';
-- 索引失效

-- 优化
SELECT * FROM orders
WHERE create_time >= '2024-01-15 00:00:00'
  AND create_time < '2024-01-16 00:00:00';

-- 另一个例子
-- 慢查询
SELECT * FROM users WHERE UPPER(name) = 'ALICE';

-- 优化（如果确实需要不区分大小写）
-- 方案1：使用ci排序规则
ALTER TABLE users MODIFY name VARCHAR(50) COLLATE utf8mb4_general_ci;
SELECT * FROM users WHERE name = 'alice';

-- 方案2：创建函数索引（MySQL 8.0+）
CREATE INDEX idx_upper_name ON users((UPPER(name)));
SELECT * FROM users WHERE UPPER(name) = 'ALICE';

-- 示例事务
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- InnoDB实现原理：

-- Step 1: 执行UPDATE前，记录undo log
-- undo log记录：
-- - 表空间ID、页号、记录位置
-- - 旧值：账户1余额=1000，账户2余额=500

-- Step 2: 执行UPDATE操作
-- 新值：账户1余额=900，账户2余额=600

-- Step 3: 如果COMMIT，标记事务完成，undo log保留（用于MVCC）
-- Step 4: 如果ROLLBACK，使用undo log恢复数据
ROLLBACK;
-- 恢复操作：
-- UPDATE accounts SET balance = 1000 WHERE id = 1;
-- UPDATE accounts SET balance = 500 WHERE id = 2;

-- 示例事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- InnoDB实现原理（WAL - Write-Ahead Logging）：

-- Step 1: 修改数据页（在内存的Buffer Pool中）
-- 账户1余额：1000 → 900（仅在内存中）

-- Step 2: 写入redo log（磁盘）
-- redo log记录：
-- - 表空间ID=5, 页号=100, 偏移量=50
-- - 修改内容：balance字段从1000改为900
-- 注意：redo log是物理日志，记录"在某个数据页的某个偏移量做了什么修改"

-- Step 3: 提交事务
COMMIT;
-- 此时只需确保redo log刷盘，数据页可以稍后刷盘

-- Step 4: 后台线程异步将脏页刷入磁盘
-- 如果在刷盘前数据库崩溃，重启后通过redo log恢复