MySQL 8.0新特性与面试专题

第六部分：MySQL 8.0新特性

第15章 重要更新

15.1 窗口函数（Window Functions）

窗口函数回顾

-- MySQL 8.0引入了强大的窗口函数
-- 详细内容已在第5章讲解，这里快速回顾

-- 基础语法
function_name OVER ([PARTITION BY ...] [ORDER BY ...] [WINDOW ...])

-- 排名函数
SELECT
    department,
    employee,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 聚合函数
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

-- 取值函数
SELECT
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM sales;

15.2 通用表表达式（CTE）

CTE基础

-- CTE（Common Table Expression）：通用表表达式
-- 优势：提高可读性、支持递归、可重用

-- 基础语法
WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

-- 示例1：基础CTE
WITH user_stats AS (
    SELECT
        city,
        COUNT(*) AS user_count,
        AVG(age) AS avg_age
    FROM users
    GROUP BY city
)
SELECT * FROM user_stats WHERE user_count > 100;

-- 示例2：多个CTE
WITH
city_users AS (
    SELECT city, COUNT(*) AS user_count
    FROM users
    GROUP BY city
),
city_orders AS (
    SELECT u.city, COUNT(o.order_id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.city
)
SELECT
    cu.city,
    cu.user_count,
    co.order_count
FROM city_users cu
JOIN city_orders co ON cu.city = co.city;

-- 示例3：CTE连接
WITH top_cities AS (
    SELECT city, COUNT(*) AS user_count
    FROM users
    GROUP BY city
    ORDER BY user_count DESC
    LIMIT 10
)
SELECT u.*, o.order_id
FROM top_cities tc
JOIN users u ON u.city = tc.city
LEFT JOIN orders o ON u.id = o.user_id;

递归CTE

-- 递归CTE：处理层级结构、树形数据

-- 语法
WITH RECURSIVE cte_name AS (
    -- 初始查询（非递归部分）
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ... FROM cte_name ...
)
SELECT * FROM cte_name;

-- 示例1：生成数字序列
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- 示例2：组织架构树
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    salary INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL, 100000),
(2, 'CTO', 1, 80000),
(3, 'CFO', 1, 80000),
(4, 'Engineering Manager', 2, 60000),
(5, 'Finance Manager', 3, 60000),
(6, 'Senior Engineer', 4, 50000),
(7, 'Junior Engineer', 4, 40000);

-- 查找CEO下的所有员工（层级查询）
WITH RECURSIVE org_tree AS (
    -- 初始：CEO
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归：下属
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree;

-- 结果：包含层级信息
-- id | name | manager_id | level
-- 1  | CEO  | NULL      | 1
-- 2  | CTO  | 1         | 2
-- 3  | CFO  | 1         | 2
-- 4  | ... | 2         | 3
-- ...

-- 示例3：计算路径
WITH RECURSIVE org_path AS (
    SELECT
        id,
        name,
        CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.id,
        e.name,
        CONCAT(op.path, ' > ', e.name) AS path
    FROM employees e
    JOIN org_path op ON e.manager_id = op.id
)
SELECT * FROM org_path;

-- 结果：
-- CEO
-- CEO > CTO
-- CEO > CTO > Engineering Manager
-- CEO > CFO

-- 示例4：计算工资总和（含下属）
WITH RECURSIVE salary_tree AS (
    SELECT
        id,
        name,
        salary,
        salary AS total_salary
    FROM employees

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.salary,
        e.salary + st.total_salary AS total_salary
    FROM employees e
    JOIN salary_tree st ON e.manager_id = st.id
)
SELECT
    name,
    salary,
    MAX(total_salary) AS team_salary
FROM salary_tree
GROUP BY id, name, salary
ORDER BY team_salary DESC;

CTE性能优化

-- CTE vs 子查询性能对比

-- 使用CTE（可读性好）
WITH user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
)
SELECT u.username, uo.order_count, uo.total_amount
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
WHERE uo.order_count > 10;

-- 等价的子查询（可读性差）
SELECT u.username, uo.order_count, uo.total_amount
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) uo ON u.id = uo.user_id
WHERE uo.order_count > 10;

-- 性能说明：
-- MySQL 8.0中，CTE和子查询性能基本相同
-- 优化器会自动将CTE转换为派生表或内联
-- 复杂CTE可能不会被物化，每次都重新计算

-- CTE物化提示（MySQL 8.0.24+）
WITH user_orders AS MATERIALIZED (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM users u
JOIN user_orders uo ON u.id = uo.user_id;

-- 强制不物化
WITH user_orders AS NOT MATERIALIZED (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM users u
JOIN user_orders uo ON u.id = uo.user_id;

15.3 原子DDL

DDL原子性

-- 原子DDL：DDL操作要么全部成功，要么全部回滚
-- MySQL 8.0之前：DDL不支持事务，无法回滚
-- MySQL 8.0之后：DDL操作是原子的，支持CRASH-SAFE

-- 示例1：创建表并创建索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- 如果索引创建失败，整个CREATE TABLE操作回滚

-- 示例2：DROP多个表（原子操作）
DROP TABLE table1, table2, table3;
-- 任一表删除失败，全部回滚

-- 示例3：ALTER TABLE多操作（原子）
CREATE TABLE test (
    id INT PRIMARY KEY,
    col1 VARCHAR(50),
    col2 INT
) ENGINE=InnoDB;

INSERT INTO test VALUES (1, 'test', 100);

-- 原子ALTER（多个操作）
ALTER TABLE test
    ADD COLUMN col3 INT,
    ADD INDEX idx_col1 (col1),
    DROP COLUMN col2;

-- 如果任一操作失败，所有修改回滚

-- 示例4：RENAME TABLE原子性
RENAME TABLE old_table TO new_table;
-- 如果崩溃，要么旧表名存在，要么新表名存在

-- 事务中的DDL
START TRANSACTION;
INSERT INTO users VALUES (1, 'user1', 'user1@test.com');
CREATE TABLE test (...);
COMMIT;
-- MySQL 8.0：DDL会隐式提交，但原子性得到保证

DDL原子性原理

-- 原子DDL实现原理
-- 1. 使用Redo Log记录DDL操作
-- 2. DDL操作分为Prepare、Commit、Rollback阶段
-- 3. 崩溃恢复时根据日志决定提交或回滚

-- 查看DDL日志
SHOW VARIABLES LIKE 'log_error_verbosity';
-- 默认为2（包含DDL日志）

-- 错误日志包含DDL操作记录
-- /var/log/mysql/error.log

-- 示例日志
-- [Server] DDL log: : create database testdb
-- [Server] DDL log: : create table testdb.users

-- 原子DDL优势
-- 1. 避免部分失败导致的不一致状态
-- 2. 简化恢复流程
-- 3. 提高可靠性

-- 注意事项
-- DDL仍然会隐式提交当前事务
START TRANSACTION;
INSERT INTO users VALUES (1, 'user1', 'user1@test.com');  -- 会被提交
ALTER TABLE users ADD COLUMN col INT;  -- DDL隐式提交
ROLLBACK;  -- 无法回滚INSERT操作

15.4 资源组管理

资源组创建与使用

-- 资源组：管理线程资源（CPU）
-- 需要系统权限

-- 1. 创建资源组
CREATE RESOURCE GROUP rg_app_users
    TYPE = USER
    VCPU = 0-1           -- 使用CPU 0和1
    THREAD_PRIORITY = 10 -- 优先级（0-19，越小越高）

DEFAULT;
-- 线程优先级10

CREATE RESOURCE GROUP rg_batch_jobs
    TYPE = USER
    VCPU = 2-3           -- 使用CPU 2和3
    THREAD_PRIORITY = 19
DEFAULT;
-- 批处理任务，优先级19（最低）

-- 2. 查看资源组
SELECT * FROM information_schema.RESOURCE_GROUPS;

-- 结果：
-- RESOURCE_GROUP_NAME | TYPE | VCPU_AFFINITY | THREAD_PRIORITY
-- --------------------+------+---------------+----------------
-- USR_default         | USER | 0-           | 0
-- SYS_default         | SYS  | 0-           | 0
-- rg_app_users        | USER | 0-1          | 10
-- rg_batch_jobs       | USER | 2-3          | 19

-- 3. 修改资源组
ALTER RESOURCE GROUP rg_app_users
    VCPU = 0-1, 4-5;    -- 扩展到CPU 0,1,4,5
-- 修改线程优先级
ALTER RESOURCE GROUP rg_batch_jobs
    THREAD_PRIORITY = 15;

-- 4. 删除资源组
DROP RESOURCE GROUP rg_batch_jobs;

-- 5. 使用资源组
-- 方式1：会话级别
SET RESOURCE GROUP rg_app_users;

-- 查询或在此会话中的操作将使用该资源组
SELECT * FROM users;

-- 方式2：语句级别
SELECT * FROM users RESOURCE GROUP rg_app_users;

-- INSERT/UPDATE/DELETE也可以指定资源组
UPDATE orders RESOURCE GROUP rg_batch_jobs
SET status = 'completed'
WHERE created_at < '2023-01-01';

-- 方式3：绑定线程
-- 应用代码中设置线程资源组
-- Java示例：
-- conn.createStatement().execute("SET RESOURCE GROUP rg_app_users");

资源组监控

-- 监控资源组使用情况

-- 1. 查看当前会话资源组
SELECT * FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND';

-- RESOURCE_GROUP列显示当前资源组

-- 2. 查看资源组统计
-- 需要启用instrumentation
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME LIKE 'wait/io/cpu/%';

-- 3. 监控CPU使用
SELECT
    THREAD_ID,
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    PROCESSLIST_DB,
    PROCESSLIST_COMMAND,
    RESOURCE_GROUP,
    CPU_TIME
FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND';

-- 4. 资源组使用场景
-- 场景1：分离OLTP和OLAP工作负载
CREATE RESOURCE GROUP rg_oltp
    TYPE = USER
    VCPU = 0-3
    THREAD_PRIORITY = 5;

CREATE RESOURCE GROUP rg_olap
    TYPE = USER
    VCPU = 4-7
    THREAD_PRIORITY = 15;

-- OLTP应用使用rg_oltp
-- OLAP分析使用rg_olap

-- 场景2：批处理任务资源隔离
CREATE RESOURCE GROUP rg_batch
    TYPE = USER
    VCPU = 6-7
    THREAD_PRIORITY = 19;

-- 批处理任务
SET RESOURCE GROUP rg_batch;
LOAD DATA INFILE '/data/batch.csv' INTO TABLE orders;

15.5 角色管理

角色创建与授权

-- 角色：权限的集合，简化权限管理

-- 1. 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 2. 授予角色权限
GRANT SELECT ON blogdb.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON blogdb.* TO 'app_write';
GRANT ALL PRIVILEGES ON blogdb.* TO 'app_admin';

-- 3. 创建用户并授予角色
CREATE USER 'read_user'@'%' IDENTIFIED BY 'ReadPass123!';
CREATE USER 'write_user'@'%' IDENTIFIED BY 'WritePass123!';
CREATE USER 'admin_user'@'%' IDENTIFIED BY 'AdminPass123!';

GRANT 'app_read' TO 'read_user'@'%';
GRANT 'app_write' TO 'write_user'@'%';
GRANT 'app_admin' TO 'admin_user'@'%';

-- 4. 查看角色
SHOW GRANTS FOR 'app_read';
-- GRANT SELECT ON `blogdb`.* TO `app_read`%

SHOW GRANTS FOR 'read_user'@'%';
-- GRANT USAGE ON *.* TO `read_user`@`%`
-- GRANT `app_read`@`%` TO `read_user`@`%`

-- 5. 查看当前用户角色
SELECT CURRENT_ROLE();

-- 6. 激活角色
-- 角色默认不激活，需要显式激活
SET ROLE 'app_read';

-- 查看激活后的权限
SHOW GRANTS;

-- 7. 设置默认激活角色
-- 为用户设置默认激活的角色
SET DEFAULT ROLE ALL TO 'read_user'@'%';
SET DEFAULT ROLE 'app_read' TO 'write_user'@'%';

-- 查看用户默认角色
SELECT DEFAULT_ROLE_ROLE FROM mysql.default_roles
WHERE USER = 'read_user' AND HOST = '%';

-- 8. 撤销角色
REVOKE 'app_write' FROM 'write_user'@'%';
DROP ROLE 'app_admin';

角色嵌套与管理

-- 角色嵌套：一个角色可以包含其他角色

-- 1. 创建基础角色
CREATE ROLE 'dev_role', 'qa_role', 'ops_role';

GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'dev_role';
GRANT SELECT ON qa_db.* TO 'qa_role';
GRANT ALL PRIVILEGES ON *.* TO 'ops_role';

-- 2. 创建管理员角色（包含其他角色）
CREATE ROLE 'team_lead';

GRANT 'dev_role' TO 'team_lead';
GRANT 'qa_role' TO 'team_lead';

-- 3. 授予用户
CREATE USER 'lead_user'@'%' IDENTIFIED BY 'LeadPass123!';
GRANT 'team_lead' TO 'lead_user'@'%';

-- 4. 查看角色关系
-- 查看角色包含的角色
SELECT * FROM mysql.role_edges;

-- FROM_HOST | FROM_USER | TO_HOST | TO_USER
-- ----------+-----------+----------+---------
-- %         | dev_role  | %        | team_lead
-- %         | qa_role   | %        | team_lead

-- 5. 角色强制激活
-- 即使角色未激活，也需要强制激活
SET ROLE ALL;
SET ROLE NONE;  -- 不激活任何角色

-- 6. 角色与权限检查
-- 检查用户是否有权限
SELECT * FROM mysql.tables_priv
WHERE User = 'read_user';

-- 检查激活的角色
SHOW GRANTS FOR 'read_user'@'%';
-- USING 'app_read'  -- 显示激活的角色

15.6 JSON功能增强

JSON函数增强

-- MySQL 8.0 JSON功能大幅增强

-- 创建示例表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    attributes JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入JSON数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', JSON_OBJECT(
    'color', 'Space Gray',
    'storage', 256,
    'price', 7999,
    'features', JSON_ARRAY('Face ID', '5G', 'Wireless Charging'),
    'specs', JSON_OBJECT(
        'screen', '6.1 inch',
        'battery', '3279 mAh',
        'weight', '171 g'
    )
));

-- 1. JSON_VALUE：提取标量值（MySQL 8.0.21+）
SELECT
    name,
    JSON_VALUE(attributes, '$.color') AS color,
    JSON_VALUE(attributes, '$.price' RETURNING DECIMAL(10,2)) AS price
FROM products;

-- 2. JSON_TABLE：将JSON转为关系表
SELECT
    p.name,
    jt.feature
FROM products p,
JSON_TABLE(
    p.attributes,
    '$.features[*]' COLUMNS (
        feature VARCHAR(50) PATH '$'
    )
) jt;

-- 结果：
-- name       | feature
-- -----------+------------------
-- iPhone 15  | Face ID
-- iPhone 15  | 5G
-- iPhone 15  | Wireless Charging

-- 复杂JSON_TABLE示例
SELECT
    p.name,
    jt.screen,
    jt.battery,
    jt.weight
FROM products p,
JSON_TABLE(
    p.attributes,
    '$.specs' COLUMNS (
        screen VARCHAR(20) PATH '$.screen',
        battery VARCHAR(20) PATH '$.battery',
        weight VARCHAR(20) PATH '$.weight'
    )
) jt;

-- 3. JSON聚合函数
-- JSON_ARRAYAGG：聚合为数组
SELECT
    category,
    JSON_ARRAYAGG(name) AS products
FROM products
GROUP BY category;

-- JSON_OBJECTAGG：聚合为对象
SELECT
    JSON_OBJECTAGG(name, price) AS price_map
FROM products;

-- 4. JSON合并函数（MySQL 8.0.3+）
-- JSON_MERGE_PRESERVE：保留重复键
SELECT JSON_MERGE_PRESERVE(
    JSON_OBJECT('a', 1, 'b', 2),
    JSON_OBJECT('b', 3, 'c', 4)
);
-- 结果：{"a": 1, "b": [2, 3], "c": 4}

-- JSON_MERGE_PATCH：后者覆盖前者
SELECT JSON_MERGE_PATCH(
    JSON_OBJECT('a', 1, 'b', 2),
    JSON_OBJECT('b', 3, 'c', 4)
);
-- 结果：{"a": 1, "b": 3, "c": 4}

-- 5. JSON比较函数
-- JSON_CONTAINS：检查是否包含
SELECT * FROM products
WHERE JSON_CONTAINS(attributes, '"Face ID"', '$.features');

-- JSON_OVERLAPS：检查是否有重叠（MySQL 8.0.17+）
SELECT * FROM products
WHERE JSON_OVERLAPS(attributes->'$.features', JSON_ARRAY('5G', 'WiFi'));

-- 6. JSON Schema验证（MySQL 8.0.17+）
-- 不再支持JSON Schema验证，建议应用层验证

JSON索引优化

-- JSON索引优化（MySQL 8.0）

-- 1. 生成列 + 索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    profile JSON,
    city VARCHAR(50) AS (JSON_UNQUOTE(profile->>'$.city')) STORED,
    INDEX idx_city (city)
);

INSERT INTO users (username, profile) VALUES
('user1', JSON_OBJECT('city', 'Beijing', 'age', 25)),
('user2', JSON_OBJECT('city', 'Shanghai', 'age', 30));

-- 查询可以使用索引
SELECT * FROM users WHERE city = 'Beijing';

-- 2. 函数索引（MySQL 8.0.13+）
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_info JSON,
    INDEX idx_customer ((CAST(order_info->>'$.customer_id' AS UNSIGNED)))
);

-- 查询可以使用索引
SELECT * FROM orders
WHERE CAST(order_info->>'$.customer_id' AS UNSIGNED) = 1001;

-- 3. 多值索引（MySQL 8.0.17+）
-- 为JSON数组创建索引
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    tags JSON,
    INDEX idx_tags ((CAST(tags->'$[*]' AS CHAR(50) ARRAY)))
);

-- 查询数组元素
SELECT * FROM products
WHERE tags->'$[*]' = '"electronics"';

15.7 其他重要特性

不可见索引

-- 不可见索引：优化器不可见，但维护正常

-- 1. 创建不可见索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    INVISIBLE INDEX idx_email (email) INVISIBLE
) ENGINE=InnoDB;

-- 或
ALTER TABLE users
ADD INDEX idx_email (email) INVISIBLE;

-- 2. 使索引可见/不可见
ALTER TABLE users
ALTER INDEX idx_email VISIBLE;

ALTER TABLE users
ALTER INDEX idx_email INVISIBLE;

-- 3. 查看索引可见性
SHOW INDEX FROM users;
-- Column_name | Visible
-- ------------+--------
-- email       | NO

-- 4. 使用场景
-- 场景1：测试删除索引前的影响
-- 先设为不可见，观察性能
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
-- 如果没问题，再删除
DROP INDEX idx_email ON users;

-- 场景2：软删除索引
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
-- 需要时可以快速恢复
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- 场景3：强制使用索引
SELECT * FROM users USE INDEX (idx_email) WHERE email = 'test@test.com';
-- 即使索引不可见，也可以强制使用

-- 5. 主键和唯一索引也可以不可见
-- 但不推荐，可能导致约束失效

降序索引

-- 降序索引：支持降序扫描，提升排序性能

-- 1. 创建降序索引
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    INDEX idx_date_amount (order_date DESC, amount DESC)
) ENGINE=InnoDB;

-- 或
ALTER TABLE orders
ADD INDEX idx_date_amount (order_date DESC, amount DESC);

-- 2. 查询使用降序索引
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY order_date DESC, amount DESC
LIMIT 100;

-- Extra: Using index（使用索引，避免filesort）

-- 3. 降序索引优势
-- 传统索引：升序存储，降序排序需要额外排序
-- 降序索引：直接按降序存储，避免排序开销

-- 4. 混合索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    category INT,
    price DECIMAL(10, 2),
    created_at TIMESTAMP,
    INDEX idx_category_price_desc (category ASC, price DESC, created_at DESC)
) ENGINE=InnoDB;

-- 查询
SELECT * FROM products
WHERE category = 1
ORDER BY price DESC, created_at DESC
LIMIT 100;
-- 完全利用索引，无需filesort

函数索引

-- 函数索引：基于表达式的索引（MySQL 8.0.13+）

-- 1. 创建函数索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP,
    INDEX idx_username_lower ((LOWER(username))),
    INDEX idx_email_domain ((SUBSTRING_INDEX(email, '@', -1))),
    INDEX idx_year ((YEAR(created_at)))
) ENGINE=InnoDB;

-- 2. 使用函数索引的查询
-- 可以使用索引
EXPLAIN SELECT * FROM users WHERE LOWER(username) = 'testuser';

-- 可以使用索引
EXPLAIN SELECT * FROM users
WHERE SUBSTRING_INDEX(email, '@', -1) = 'test.com';

-- 可以使用索引
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 3. 函数索引限制
-- 必须是确定性函数
-- 不能使用随机数、当前时间等

-- 错误示例
-- INDEX idx_random ((RAND()))

-- 4. 虚拟列 vs 函数索引
-- 虚拟列方式
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) STORED,
    INDEX idx_domain (email_domain)
) ENGINE=InnoDB;

-- 函数索引方式（推荐，更简洁）
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_domain ((SUBSTRING_INDEX(email, '@', -1)))
) ENGINE=InnoDB;

直方文统计

-- 直方图：统计信息，优化查询计划

-- 1. 创建直方图
ANALYZE TABLE users UPDATE HISTOGRAM ON username, age WITH 100 BUCKETS;
-- 100个桶（值越多越精确）

-- 2. 查看直方图
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'users' AND column_name = 'age'\G

-- 3. 删除直方图
ANALYZE TABLE users DROP HISTOGRAM ON age;

-- 4. 直方图优势
-- 提供数据分布统计
-- 帮助优化器选择更好的执行计划
-- 适用于非索引列的查询优化

-- 5. 使用场景
-- 场景1：低选择性列
-- gender列只有2个值（M/F），索引效果差
ANALYZE TABLE users UPDATE HISTOGRAM ON gender WITH 2 BUCKETS;

-- 优化器可以根据直方图选择更优计划
SELECT * FROM users WHERE gender = 'M' AND age > 25;

-- 场景2：范围查询
-- city列数据分布不均
ANALYZE TABLE users UPDATE HISTOGRAM ON city WITH 100 BUCKETS;

-- 优化器知道哪些city数据多，选择合适索引
SELECT * FROM users WHERE city = 'Beijing' AND age > 25;

---

第七部分：实战与面试专题

第16章 常见问题排查

16.1 连接数过多

问题定位

-- 问题现象：Too many connections

-- 1. 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- 2. 查看最大连接数设置
SHOW VARIABLES LIKE 'max_connections';

-- 3. 查看所有连接详情
SHOW PROCESSLIST;

-- 或
SELECT
    ID AS process_id,
    USER AS user,
    HOST AS host,
    DB AS database,
    COMMAND AS command,
    TIME AS duration,
    STATE AS state,
    LEFT(INFO, 100) AS query
FROM INFORMATION_SCHEMA.PROCESSLIST
ORDER BY TIME DESC;

-- 4. 查看连接状态统计
SELECT
    COMMAND,
    COUNT(*) AS count
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY COMMAND
ORDER BY count DESC;

-- 结果示例：
-- Command | count
-- ---------+-------
-- Sleep    | 95
-- Query    | 4
-- Connect  | 1

-- 5. 查看每个用户的连接数
SELECT
    USER,
    COUNT(*) AS connections
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER
ORDER BY connections DESC;

-- 6. 查看每个数据库的连接数
SELECT
    DB,
    COUNT(*) AS connections
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE DB IS NOT NULL
GROUP BY DB
ORDER BY connections DESC;

解决方案

-- 方案1：临时增加连接数
SET GLOBAL max_connections = 500;

-- 持久化配置（my.cnf）
[mysqld]
max_connections = 500

-- 方案2：杀掉空闲连接
-- 查找长时间Sleep的连接
SELECT ID, USER, HOST, TIME
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 300;

-- 杀掉连接
KILL 123;
-- 或批量杀掉
-- CONCAT('KILL ', ID, ';')生成kill语句
SELECT CONCAT('KILL ', ID, ';')
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND = 'Sleep' AND TIME > 300;

-- 方案3：设置连接超时
SET GLOBAL wait_timeout = 600;  -- 10分钟
SET GLOBAL interactive_timeout = 600;

-- 持久化配置
[mysqld]
wait_timeout = 600
interactive_timeout = 600

-- 方案4：应用层优化
-- 使用连接池（Druid、HikariCP）
-- 避免长连接
-- 及时释放连接
-- 连接泄漏检测

-- Java示例（HikariCP）
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(20);  -- 最大连接数
config.setMinimumIdle(5);       -- 最小空闲连接
config.setConnectionTimeout(30000);  -- 连接超时30秒
config.setIdleTimeout(600000);       -- 空闲超时10分钟
config.setMaxLifetime(1800000);      -- 最大生命周期30分钟

16.2 慢查询优化案例

案例1：索引缺失

-- 问题：查询耗时5秒

-- 慢查询
SELECT * FROM orders WHERE user_id = 100;

-- 分析
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type: ALL（全表扫描）
-- rows: 1000000

-- 优化：添加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 验证
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type: ref（索引查找）
-- rows: 50

-- 优化后：查询耗时0.01秒

案例2：索引失效

-- 问题：查询耗时8秒

-- 慢查询
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 分析
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type: ALL（全表扫描）
-- key: NULL（未使用索引）

-- 原因：函数破坏索引
SHOW INDEX FROM users;
-- key_name: idx_created_at

-- 优化：改写查询
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 验证
EXPLAIN SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type: range（范围扫描）
-- key: idx_created_at

-- 优化后：查询耗时0.02秒

案例3：JOIN优化

-- 问题：查询耗时15秒

-- 慢查询
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.username = o.user_id
WHERE u.city = 'Beijing';

-- 分析
EXPLAIN SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.username = o.user_id
WHERE u.city = 'Beijing';

-- 问题：
-- 1. 连接字段类型不一致
-- 2. 连接字段无索引

-- 优化步骤
-- 1. 确保类型一致
ALTER TABLE orders MODIFY COLUMN user_id VARCHAR(50);

-- 2. 添加索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_city ON users(city);

-- 3. 改写查询（如果数据量大）
SELECT u.*, o.*
FROM users u
INNER JOIN orders o ON u.id = CAST(o.user_id AS UNSIGNED)
WHERE u.city = 'Beijing';

-- 优化后：查询耗时0.5秒

案例4：子查询优化

-- 问题：查询耗时10秒

-- 慢查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 分析
EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- select_type: DEPENDENT SUBQUERY（相关子查询）
-- 性能差

-- 优化1：改写为JOIN
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- 优化2：使用EXISTS（如果有索引）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 1000
);

-- 优化后：查询耗时0.3秒

案例5：分页优化

-- 问题：深分页查询耗时20秒

-- 慢查询
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 分析
EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- rows: 1000010（扫描100万行）

-- 优化1：使用子查询（记录最大ID）
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 1
)
ORDER BY id
LIMIT 10;

-- 优化2：使用延迟关联（如果查询条件复杂）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

-- 优化3：如果连续浏览，记录上次最大ID
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 优化后：查询耗时0.1秒

16.3 死锁分析与解决

死锁定位

-- 1. 查看死锁日志
-- 错误日志中包含死锁信息
-- /var/log/mysql/error.log

-- SHOW ENGINE INNODB STATUS;
-- 包含最近一次死锁的详细信息

-- 2. 启用死锁监控
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 将所有死锁记录到错误日志

-- 3. 查看锁等待
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- MySQL 8.0+
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 4. 查看锁状态
SHOW STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_current_waits: 当前等待的锁
-- Innodb_row_lock_time: 锁等待总时间
-- Innodb_row_lock_time_avg: 平均锁等待时间
-- Innodb_row_lock_time_max: 最大锁等待时间

死锁案例与解决

-- 案例1：不同顺序加锁导致死锁

-- 事务A
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;  -- 锁定id=1
-- 等待id=2的锁
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 事务B
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 2;  -- 锁定id=2
-- 等待id=1的锁
UPDATE users SET balance = balance + 100 WHERE id = 1;

-- 结果：死锁

-- 解决：固定加锁顺序
-- 所有事务按相同顺序加锁
-- 例如：总是先更新ID小的
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 案例2：索引失效导致表锁

-- 事务A
START TRANSACTION;
UPDATE users SET age = 25 WHERE email = 'test@test.com';
-- 如果email无索引，锁定整表

-- 事务B
START TRANSACTION;
UPDATE users SET age = 30 WHERE email = 'demo@demo.com';
-- 等待表锁

-- 事务A
UPDATE users SET age = 35 WHERE email = 'admin@admin.com';
-- 等待B释放，但B在等A

-- 结果：死锁

-- 解决：添加索引
CREATE INDEX idx_email ON users(email);

-- 案例3：外键未加索引

-- 主表
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

-- 从表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 外键未索引，更新/删除可能锁整表
CREATE INDEX idx_dept_id ON employees(dept_id);

死锁预防

-- 1. 固定加锁顺序
-- 所有事务按相同顺序访问表和行

-- 2. 减小锁粒度
-- 使用行锁而非表锁
-- 添加合适的索引

-- 3. 缩短锁持有时间
-- 不推荐：
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 执行耗时操作
-- （例如HTTP请求、文件操作）
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 推荐：
-- 先在应用层准备数据
-- 然后快速加锁并更新
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 4. 使用乐观锁
CREATE TABLE products (
    id INT PRIMARY KEY,
    stock INT,
    version INT
);

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;
-- 检查affected rows
-- 如果为0，说明版本冲突，重试

-- 5. 设置锁超时
SET SESSION innodb_lock_wait_timeout = 5;
-- 超时后返回错误，而非无限等待
-- 应用捕获错误并重试

-- 6. 降低隔离级别
-- 如果不需要RR级别，可以使用RC
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- RC级别减少间隙锁，降低死锁概率

16.4 主从延迟问题

延迟监控

-- 1. 查看从库延迟
SHOW SLAVE STATUS\G

-- 关键字段：
-- Seconds_Behind_Master: 延迟秒数
-- 0：无延迟
-- NULL：IO线程或SQL线程停止

-- 2. 实时监控脚本
#!/bin/bash
while true; do
    DELAY=$(mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
    echo "$(date): Delay = ${DELAY:-NULL} seconds"
    sleep 5
done

-- 3. 告警脚本
if [ "$DELAY" -gt 60 ]; then
    echo "Replication delay: $DELAY seconds" | mail -s "Replication Alert" admin@example.com
fi

-- 4. Prometheus监控
-- mysqld_exporter自动采集
-- mysql_slave_status_seconds_behind_master 指标
-- Grafana配置告警：Delay > 60秒

延迟原因与解决

-- 原因1：主库写入量大，从库性能不足

-- 解决：
-- 1. 升级从库硬件
-- 2. 并行复制（MySQL 5.7+）
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_parallel_workers = 4;
STOP SLAVE;
START SLAVE;

-- 原因2：从库查询影响复制

-- 解决：
-- 从库设置read_only
SET GLOBAL read_only = ON;
-- 应用读写分离，查询只走从库

-- 原因3：网络延迟

-- 解决：
-- 1. 优化网络
-- 2. 使用更近的机房
-- 3. 压缩binlog（MySQL 8.0）
SET GLOBAL binlog_transaction_dependency_history_size = 10000;

-- 原因4：大事务

-- 问题：
-- 主库执行1小时的事务
-- 从库也需要1小时执行
-- 延迟持续累积

-- 解决：
-- 1. 拆分大事务
-- 不推荐：
START TRANSACTION;
UPDATE orders SET status = 'completed';
-- 100万行，执行1小时
COMMIT;

-- 推荐：分批执行
SET @batch_size = 1000;
SET @max_id = (SELECT MAX(id) FROM orders);
SET @current_id = 0;

WHILE @current_id < @max_id DO
    START TRANSACTION;
    UPDATE orders
    SET status = 'completed'
    WHERE id > @current_id AND id <= @current_id + @batch_size;
    COMMIT;
    SET @current_id = @current_id + @batch_size;
END WHILE;

-- 2. 提高从库执行速度
-- 增加innodb_buffer_pool_size
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB

-- 原因5：表结构不同（无索引）

-- 检查：
SHOW CREATE TABLE users;
-- 主从表结构是否一致

-- 解决：
-- 保持主从表结构一致
-- 包括索引、字符集等

延迟补偿

-- 方案1：并行复制
-- MySQL 5.7+
SET GLOBAL slave_parallel_type = LOGICAL_CLOCK;
SET GLOBAL slave_parallel_workers = 4;

-- MySQL 8.0+ 并行复制更智能
-- 无需特殊配置

-- 方案2：多从库负载均衡
-- 部署多个从库
-- ProxySQL实现负载均衡

-- 方案3：读写分离优化
-- 读操作走从库
-- 写操作走主库
-- 最终一致性场景可以接受延迟

-- 方案4：延迟从库
-- 故障恢复用
-- CHANGE MASTER TO MASTER_DELAY = 3600;
-- 延迟1小时，防止误操作

16.5 磁盘空间不足

空间分析

# 1. 查看磁盘使用
df -h
# 查看MySQL数据目录
du -sh /var/lib/mysql/*

# 2. 查看各个数据库大小
mysql -u root -p -e "
SELECT
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
"

# 3. 查看大表
mysql -u root -p -e "
SELECT
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
ORDER BY (data_length + index_length) DESC
LIMIT 20;
"

# 4. 查看binlog大小
mysql -u root -p -e "SHOW BINARY LOGS;"
ls -lh /var/lib/mysql/mysql-bin.*

# 5. 查看Redo Log大小
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_log_file_size';"

清理方案

-- 1. 清理binlog

-- 方式1：自动过期
SET GLOBAL expire_logs_days = 7;
-- 保留7天

-- 方式2：手动清理
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 方式3：重置（慎用）
RESET MASTER;
-- 删除所有binlog

-- 2. 清理Redo Log

-- Redo Log会自动重用，无需手动清理
-- 可以调整大小减少占用
-- 注意：需要删除日志文件重启

-- 3. 删除或归档旧数据

-- 删除旧数据
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 或归档
-- 1. 创建归档表
CREATE TABLE logs_archive LIKE logs;

-- 2. 迁移数据
INSERT INTO logs_archive
SELECT * FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 3. 删除原数据
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- 4. 优化表
OPTIMIZE TABLE logs;

-- 4. 分区表（删除分区）

-- 删除旧分区
ALTER TABLE orders DROP PARTITION p2023;

-- 或重组分区
ALTER TABLE orders REORGANIZE PARTITION p2022, p2023 INTO (
    PARTITION p2023 VALUES LESS THAN (2023)
);

-- 5. 临时表清理

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_users;

-- 查看临时表
SELECT * FROM information_schema.innodb_temp_table_info;

16.6 CPU使用率高

CPU分析

# 1. 查看CPU使用
top
# 查看mysqld进程CPU占用

# 2. 查看线程CPU使用
top -H -p $(pidof mysqld)

# 3. 查看MySQL线程状态
mysql -u root -p -e "SHOW PROCESSLIST;"

# 4. 性能Schema
mysql -u root -p -e "
SELECT
    thread_id,
    processlist_user,
    processlist_host,
    processlist_db,
    processlist_command,
    processlist_state,
    processlist_time
FROM performance_schema.threads
WHERE type = 'FOREGROUND'
ORDER BY processlist_time DESC;
"

解决方案

-- 1. 慢查询优化
-- 参见16.2节

-- 2. 并发连接过多
-- 参见16.1节

-- 3. 全表扫描
-- 查找全表扫描的查询
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile
WHERE query LIKE 'SELECT%' AND query NOT LIKE '%information_schema%';

-- 4. 排序和临时表
-- 增加排序缓冲区
SET GLOBAL sort_buffer_size = 2097152;  -- 2MB

-- 5. 线程缓存
-- 减少线程创建开销
SET GLOBAL thread_cache_size = 32;

-- 6. 限制并发
SET GLOBAL innodb_thread_concurrency = 16;
-- 限制InnoDB并发线程数

-- 7. 资源组（MySQL 8.0）
-- 参见15.4节

---

第17章 面试高频考点

17.1 索引优化场景题

题目1：联合索引最左前缀

-- 题目
-- 有索引 idx(a, b, c)，哪些查询会使用索引？

CREATE TABLE test (
    id INT PRIMARY KEY,
    a INT,
    b INT,
    c INT,
    INDEX idx_abc (a, b, c)
);

-- 能使用索引的查询
EXPLAIN SELECT * FROM test WHERE a = 1;
-- ✓ 使用索引（a）

EXPLAIN SELECT * FROM test WHERE a = 1 AND b = 2;
-- ✓ 使用索引（a, b）

EXPLAIN SELECT * FROM test WHERE a = 1 AND b = 2 AND c = 3;
-- ✓ 使用索引（a, b, c）

EXPLAIN SELECT * FROM test WHERE a = 1 AND c = 3;
-- ✓ 使用索引（a），c可能索引下推

EXPLAIN SELECT * FROM test WHERE a = 1 AND b > 2 AND c = 3;
-- ✓ 使用索引（a, b），范围查询

-- 不能使用索引的查询
EXPLAIN SELECT * FROM test WHERE b = 2;
-- ✗ 不符合最左前缀

EXPLAIN SELECT * FROM test WHERE c = 3;
-- ✗ 不符合最左前缀

EXPLAIN SELECT * FROM test WHERE b = 2 AND c = 3;
-- ✗ 不符合最左前缀

-- 总结：
-- 联合索引必须从左到右使用
-- 跳过中间列会导致后面列无法使用索引
-- 范围查询后的列无法使用索引

题目2：索引失效场景

-- 题目
-- 下列哪些操作会导致索引失效？

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_age (age),
    INDEX idx_created (created_at)
);

-- 1. 使用函数
-- ✗ 索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✓ 优化
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 2. 隐式类型转换
-- ✗ 索引失效
SELECT * FROM users WHERE username = 123;  -- 字符串vs数字

-- ✓ 优化
SELECT * FROM users WHERE username = '123';

-- 3. 前缀模糊查询
-- ✗ 索引失效
SELECT * FROM users WHERE username LIKE '%zhang%';

-- ✓ 可以使用索引
SELECT * FROM users WHERE username LIKE 'zhang%';

-- 4. OR连接（如果不都有索引）
-- ✗ 可能索引失效
SELECT * FROM users WHERE age = 25 OR email = 'test@test.com';
-- 如果age和email都有索引，可能index merge

-- ✓ 优化为UNION
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE email = 'test@test.com';

-- 5. != 或 <>
-- 可能索引失效（取决于数据分布）
SELECT * FROM users WHERE age != 25;
-- 如果大部分age != 25，全表扫描更快

-- 6. IS NULL
-- ✓ 可以使用索引
SELECT * FROM users WHERE username IS NULL;

-- 7. 负向查询
-- 可能索引失效
SELECT * FROM users WHERE age NOT IN (20, 21, 22);

-- ✓ 优化
SELECT * FROM users WHERE age < 20 OR age > 22;

题目3：覆盖索引优化

-- 题目
-- 如何利用覆盖索引优化查询？

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    status VARCHAR(20),
    INDEX idx_user_date (user_id, order_date)
);

-- 查询
EXPLAIN SELECT user_id, order_date FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';

-- Extra: Using index（覆盖索引，无需回表）

-- 查询2
EXPLAIN SELECT * FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';

-- Extra: Using index condition（需要回表查询其他列）

-- 优化：创建覆盖索引
CREATE INDEX idx_user_date_amount ON orders(user_id, order_date, amount);

EXPLAIN SELECT user_id, order_date, amount FROM orders
WHERE user_id = 1 AND order_date = '2024-01-01';

-- Extra: Using index（覆盖索引）

-- 覆盖索引优势：
-- 1. 避免回表查询
-- 2. 减少IO操作
-- 3. 提升查询性能

17.2 事务隔离级别与并发问题

题目1：事务隔离级别

-- 题目
-- 不同隔离级别解决了什么问题？

-- READ UNCOMMITTED（读未提交）
-- 问题：
-- 1. 脏读：读到未提交的数据
-- 2. 不可重复读：同一条记录两次读取结果不同
-- 3. 幻读：范围查询结果集变化

-- READ COMMITTED（读已提交，Oracle默认）
-- 解决：
-- 1. 避免脏读
-- 问题：
-- 1. 不可重复读
-- 2. 幻读

-- REPEATABLE READ（可重复读，MySQL默认）
-- 解决：
-- 1. 避免脏读
-- 2. 避免不可重复读（通过MVCC）
-- 问题：
-- 1. 幻读（可能，通过间隙锁避免大部分）

-- SERIALIZABLE（串行化）
-- 解决：
-- 1. 避免脏读
-- 2. 避免不可重复读
-- 3. 避免幻读
-- 性能最差

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

题目2：脏读、不可重复读、幻读

-- 题目
-- 请举例说明脏读、不可重复读、幻读

-- 1. 脏读示例
-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 事务A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 未提交

-- 事务B
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;
-- 读到了未提交的数据（脏读）

-- 事务A
ROLLBACK;

-- 事务B
SELECT balance FROM account WHERE id = 1;
-- 数据恢复了，不一致

-- 2. 不可重复读示例
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -- 第一次读取：1000

-- 事务B
START TRANSACTION;
UPDATE account SET balance = balance + 100 WHERE id = 1;
COMMIT;

-- 事务A
SELECT balance FROM account WHERE id = 1;  -- 第二次读取：1100
-- 不可重复读

-- 3. 幻读示例
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务A
START TRANSACTION;
SELECT * FROM account WHERE balance > 1000;
-- 返回10条记录

-- 事务B
START TRANSACTION;
INSERT INTO account VALUES (11, 'user11', 2000);
COMMIT;

-- 事务A
SELECT * FROM account WHERE balance > 1000;
-- 可能返回11条记录（幻读）
-- MySQL通过间隙锁避免幻读

题目3：MVCC原理

-- 题目
-- 解释MVCC如何实现可重复读

-- MVCC（Multi-Version Concurrency Control）：多版本并发控制

-- 核心概念：
-- 1. Undo Log：版本链
-- 2. Read View：快照
-- 3. 隐藏字段：
--    - DB_TRX_ID：事务ID
--    - DB_ROLL_PTR：回滚指针

-- Read View包含：
-- 1. m_ids：活跃事务ID列表
-- 2. min_trx_id：最小活跃事务ID
-- 3. max_trx_id：预分配事务ID
-- 4. creator_trx_id：创建者事务ID

-- 可见性判断：
-- 1. 如果被访问版本的 trx_id < min_trx_id：可见（已提交）
-- 2. 如果被访问版本的 trx_id >= max_trx_id：不可见（未来事务）
-- 3. 如果被访问版本的 trx_id 在 m_ids 中：不可见（未提交）
-- 4. 如果被访问版本的 trx_id 不在 m_ids 中：可见（已提交）

-- RC vs RR：
-- RC：每次SELECT都生成新的Read View
-- RR：第一次SELECT生成Read View，后续复用

-- 示例
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id = 1;  -- 生成Read View
-- 看到的是自己开始时的数据快照

-- 事务B
START TRANSACTION;
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;

-- 事务A
SELECT * FROM users WHERE id = 1;  -- 复用Read View
-- 仍然看到旧数据（可重复读）

17.3 分页查询优化

题目1：深分页优化

-- 题目
-- 如何优化深分页查询？

-- 慢查询
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 扫描100万行，耗时20秒

-- 优化方案1：子查询（记录最大ID）
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 1
)
ORDER BY id
LIMIT 10;
-- 耗时0.1秒

-- 优化方案2：INNER JOIN
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;
-- 耗时0.15秒

-- 优化方案3：ID范围（连续浏览）
-- 假设上页最后ID是1000000
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 耗时0.01秒

-- 对比：
-- 方案3最快，但需要连续浏览
-- 方案1适用于随机跳转
-- 方案2适用于复杂查询

题目2：COUNT优化

-- 题目
-- 如何优化大表的COUNT查询？

-- 慢查询
SELECT COUNT(*) FROM orders;
-- 耗时5秒（扫描全表）

-- 优化方案1：近似值
SELECT TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'blogdb' AND TABLE_NAME = 'orders';
-- 耗时0.01秒（但不精确）

-- 优化方案2：缓存
-- Redis缓存结果
-- 定期更新（如每分钟）

-- 优化方案3：维护计数表
CREATE TABLE table_stats (
    table_name VARCHAR(100) PRIMARY KEY,
    row_count BIGINT,
    updated_at TIMESTAMP
);

-- 触发器更新
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO table_stats (table_name, row_count, updated_at)
    VALUES ('orders', 1, NOW())
    ON DUPLICATE KEY UPDATE
        row_count = row_count + 1,
        updated_at = NOW();
END//
DELIMITER ;

-- 查询
SELECT row_count FROM table_stats WHERE table_name = 'orders';

17.4 大数据量处理方案

题目1：批量插入优化

-- 题目
-- 如何快速插入1000万条数据？

-- 慢方法：逐条插入
INSERT INTO users (username, email) VALUES ('user1', 'user1@test.com');
-- 重复1000万次
-- 耗时：数小时

-- 优化方案1：批量插入
INSERT INTO users (username, email) VALUES
    ('user1', 'user1@test.com'),
    ('user2', 'user2@test.com'),
    ...
    ('user10000', 'user10000@test.com');
-- 每次插入1万条
-- 耗时：数分钟

-- 优化方案2：LOAD DATA INFILE
-- 1. 准备数据文件
-- /tmp/users.csv
user1,user1@test.com
user2,user2@test.com
...

-- 2. 导入数据
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(username, email);
-- 耗时：几十秒

-- 优化方案3：关闭索引和约束
-- 1. 禁用索引
ALTER TABLE users DISABLE KEYS;

-- 2. 导入数据
LOAD DATA INFILE '/tmp/users.csv' ...

-- 3. 启用索引
ALTER TABLE users ENABLE KEYS;

-- 优化方案4：分批提交
START TRANSACTION;
-- 插入10万条
COMMIT;
-- 重复100次

-- 优化方案5：调整参数
SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8GB
SET GLOBAL innodb_log_file_size = 1073741824;  -- 1GB
SET GLOBAL innodb_flush_log_at_trx_commit = 0;  -- 导入时改为0

题目2：海量数据删除

-- 题目
-- 如何快速删除500万条旧数据？

-- 慢方法
DELETE FROM logs WHERE created_at < '2023-01-01';
-- 耗时：数小时，锁表时间长

-- 优化方案1：分批删除
DELETE FROM logs WHERE id < 10000;
DELETE FROM logs WHERE id < 20000;
...
-- 每次删除1万条

-- 优化方案2：循环删除
SET @batch_size = 10000;
SET @rows_affected = 1;

WHILE @rows_affected > 0 DO
    DELETE FROM logs
    WHERE created_at < '2023-01-01'
    LIMIT @batch_size;

    SET @rows_affected = ROW_COUNT();
    -- 短暂暂停，避免锁表
    DO SLEEP(0.1);
END WHILE;

-- 优化方案3：归档+删除
-- 1. 创建归档表
CREATE TABLE logs_archive LIKE logs;

-- 2. 迁移数据（INSERT ... SELECT）
INSERT INTO logs_archive
SELECT * FROM logs WHERE created_at < '2023-01-01'
LIMIT 1000000;

-- 3. 删除原数据
DELETE FROM logs WHERE id IN (
    SELECT id FROM logs_archive
);

-- 优化方案4：分区表（最佳）
-- 直接删除分区
ALTER TABLE logs DROP PARTITION p2023;
-- 耗时：几秒

题目3：分库分表

-- 题目
-- 何时需要分库分表？如何分？

-- 分库分表时机：
-- 1. 单表数据量超过2000万
-- 2. 单库数据量超过5000万
-- 3. 查询性能明显下降
-- 4. 磁盘空间不足

-- 分表策略：

-- 1. 垂直分表
-- 按列拆分
-- users_basic: id, username, email
-- users_ext: id, nickname, avatar, bio, ...

CREATE TABLE users_basic (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE users_ext (
    id INT PRIMARY KEY,
    nickname VARCHAR(50),
    avatar VARCHAR(255),
    bio TEXT
) ENGINE=InnoDB;

-- 2. 水平分表

-- 按时间分表
CREATE TABLE orders_2024 (
    order_id INT PRIMARY KEY,
    order_date DATE,
    ...
) ENGINE=InnoDB;

CREATE TABLE orders_2025 (
    ...
) ENGINE=InnoDB;

-- 按ID范围分表
CREATE TABLE users_0_1m (
    id INT PRIMARY KEY CHECK (id BETWEEN 1 AND 1000000),
    ...
) ENGINE=InnoDB;

CREATE TABLE users_1m_2m (
    id INT PRIMARY KEY CHECK (id BETWEEN 1000001 AND 2000000),
    ...
) ENGINE=InnoDB;

-- 按哈希分表
CREATE TABLE users_hash0 (
    id INT PRIMARY KEY,
    ...
) ENGINE=InnoDB;

CREATE TABLE users_hash1 (
    id INT PRIMARY KEY,
    ...
) ENGINE=InnoDB;

-- 路由：id % 2

17.5 数据库设计规范

题目1：三大范式

-- 题目
-- 什么是数据库三大范式？

-- 第一范式（1NF）：列不可再分
-- 不符合1NF：
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    address VARCHAR(200)  -- "北京市朝阳区xxx"
);
-- address包含省市区，可再分

-- 符合1NF：
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    detail VARCHAR(100)
);

-- 第二范式（2NF）：非主键字段完全依赖于主键
-- 不符合2NF（复合主键部分依赖）：
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 依赖于product_id
    quantity INT,
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- 符合2NF：
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

-- 第三范式（3NF）：非主键字段不传递依赖于主键
-- 不符合3NF（传递依赖）：
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    username VARCHAR(50),  -- user_id -> username
    order_date DATE,
    amount DECIMAL(10, 2)
);
-- username依赖于user_id，user_id依赖于order_id

-- 符合3NF：
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50)
);

题目2：字段类型选择

-- 题目
-- 如何选择合适的数据类型？

-- 原则：更小、更简单、不为NULL

-- 1. 整数类型
-- 年龄：TINYINT UNSIGNED（0-255）
CREATE TABLE users (
    age TINYINT UNSIGNED
);

-- 主键：INT UNSIGNED 或 BIGINT UNSIGNED
CREATE TABLE orders (
    order_id BIGINT UNSIGNED PRIMARY KEY
);

-- 2. 字符串类型
-- 固定长度：CHAR
CREATE TABLE users (
    phone CHAR(11),       -- 手机号固定11位
    id_card CHAR(18)      -- 身份证固定18位
);

-- 变长：VARCHAR
CREATE TABLE users (
    username VARCHAR(50),  -- 用户名长度不定
    email VARCHAR(100)     -- 邮箱长度不定
);

-- 长文本：TEXT
CREATE TABLE articles (
    content TEXT
);

-- 3. 金额类型
-- 必须使用DECIMAL
CREATE TABLE orders (
    amount DECIMAL(10, 2)  -- 总共10位，2位小数
);

-- 错误：FLOAT/DOUBLE（精度丢失）
-- 正确：DECIMAL（精确）

-- 4. 日期时间
-- 业务时间：DATETIME（不受时区影响）
CREATE TABLE orders (
    order_time DATETIME
);

-- 记录时间：TIMESTAMP（自动更新）
CREATE TABLE users (
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 5. 枚举类型
-- 少量固定值：ENUM
CREATE TABLE users (
    gender ENUM('male', 'female', 'other')
);

-- 或使用TINYINT
CREATE TABLE users (
    gender TINYINT UNSIGNED  -- 0: other, 1: male, 2: female
);

题目3：索引设计

-- 题目
-- 如何设计合理的索引？

-- 1. 主键索引
-- 使用自增ID（推荐）
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ...
);

-- 或使用UUID（不推荐，性能差）
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY,  -- UUID
    ...
);

-- 2. 唯一索引
-- 业务唯一字段
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 3. 普通索引
-- 频繁查询的字段
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);

-- 4. 联合索引
-- 考虑查询频率和最左前缀
CREATE TABLE orders (
    ...
    INDEX idx_user_date (user_id, order_date)
);

-- 适用查询：
-- ✓ WHERE user_id = ?
-- ✓ WHERE user_id = ? AND order_date = ?
-- ✓ WHERE user_id = ? ORDER BY order_date
-- ✗ WHERE order_date = ?

-- 5. 覆盖索引
-- 包含所有查询字段
CREATE INDEX idx_user_date_amount ON orders(user_id, order_date, amount);

-- 查询
SELECT user_id, order_date, amount FROM orders WHERE user_id = 1;
-- 无需回表

-- 6. 索引原则
-- - 区分度高的字段（性别不单独建索引）
-- - 频繁查询的字段
-- - JOIN连接字段
-- - ORDER BY字段
-- - 外键字段

-- 7. 避免过度索引
-- 索引需要维护成本
-- 每个表建议不超过5个索引

---

小结

本部分整合了MySQL 8.0新特性和实战面试专题，覆盖了高级开发和面试准备的核心内容：

第六部分 - MySQL 8.0新特性重点：

• 掌握窗口函数、CTE（特别是递归CTE）的使用
• 理解原子DDL的原理和优势
• 了解资源组管理和角色管理
• 掌握JSON功能增强和索引优化
• 熟悉不可见索引、降序索引、函数索引等新特性

第七部分 - 实战与面试专题重点：

• 掌握常见问题的排查方法（连接数、慢查询、死锁、主从延迟）
• 熟练运用索引优化、事务隔离、分页查询等面试高频考点
• 了解大数据量处理的最佳实践
• 掌握数据库设计规范（三大范式、类型选择、索引设计）

面试准备建议：

1. 理论结合实践：不仅要说得出原理，还要有实战经验
2. 准备案例：准备2-3个真实的优化案例
3. 深入理解：索引、事务、锁机制是重点
4. 性能敏感：时刻考虑性能和可扩展性
5. 场景思维：针对不同场景选择不同方案

核心要点：

• MySQL 8.0新特性大幅提升了开发效率和查询能力
• 实战问题排查需要系统性思维和工具支持
• 面试核心是索引优化、事务隔离、性能调优
• 大数据量处理需要权衡各种方案
• 良好的设计是系统稳定性的基础

至此，MySQL完整学习体系已经全部完成！ 从基础到高级、从开发到运维、从理论到实战，覆盖了MySQL学习和应用的所有方面。建议结合实际项目进行综合实践，构建完整的MySQL知识体系。

---

附录：MySQL学习路径建议

1. 初级阶段（1-2个月）

   • 完成第1-2章：基础知识和SQL语言
   • 熟练掌握基本查询和表操作
   • 理解索引基本概念

2. 中级阶段（2-3个月）

   • 完成第3-5章：核心机制和高级特性
   • 深入理解存储引擎、事务、锁
   • 掌握索引优化和查询优化
   • 学习存储过程和分区分表

3. 高级阶段（3-4个月）

   • 完成第6-7章：运维架构和面试专题
   • 掌握备份恢复和主从复制
   • 学习监控调优和高可用架构
   • 熟悉MySQL 8.0新特性

4. 实战阶段（持续）

   • 参与实际项目开发
   • 进行性能优化实践
   • 搭建高可用架构
   • 总结经验和最佳实践

祝学习顺利，早日成为MySQL专家！