跳到主要内容

MySQL 性能优化

性能优化是数据库管理中最重要的技能之一。本章将详细介绍 MySQL 性能优化的方法和技巧。

性能优化概述​

MySQL 性能优化是一个系统工程，涉及多个层面：

┌─────────────────────────────────────────────────────────────┐
│                    性能优化层次                              │
├─────────────────────────────────────────────────────────────┤
│ 1. SQL 优化     │ 查询语句优化、索引使用                   │
│ 2. 表结构优化   │ 数据类型、范式设计、反范式                │
│ 3. 参数优化     │ Buffer Pool、连接数、日志配置           │
│ 4. 硬件优化     │ 内存、磁盘、CPU                          │
│ 5. 架构优化     │ 读写分离、分库分表、缓存                │
└─────────────────────────────────────────────────────────────┘

优化应该从上到下进行：首先优化 SQL 和索引（成本最低、效果最好），然后考虑表结构和参数配置，最后才是硬件升级和架构改造。

EXPLAIN 执行计划分析​

EXPLAIN 是 MySQL 提供的查询分析工具，用于查看 MySQL 如何执行 SQL 语句。理解 EXPLAIN 的输出是性能优化的基础。

基本使用​

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- MySQL 8.0.18+ 支持更详细的分析
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 以 JSON 格式输出（更详细）
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE name = '张三';

-- 以树形结构输出
EXPLAIN FORMAT=TREE SELECT * FROM users WHERE name = '张三';

EXPLAIN 输出列详解​

执行 EXPLAIN 后会返回以下列：

列名说明
id查询标识符，相同 id 表示同一查询
select_type查询类型
table访问的表
partitions匹配的分区
type访问类型（重要）
possible_keys可能使用的索引
key实际使用的索引
key_len使用的索引长度
ref索引比较的列
rows预估扫描行数
filtered条件过滤的百分比
Extra额外信息

type 访问类型（从优到差）​

type 列表示 MySQL 如何查找数据，是判断查询效率的关键指标：

type 值说明性能
NULL不需要访问表，在优化阶段就完成极好
system表只有一行（系统表）极好
const主键或唯一索引的常量查询极好
eq_ref主键或唯一索引的关联查询很好
ref非唯一索引的等值查询好
fulltext全文索引查询好
ref_or_null类似 ref，额外查找 NULL一般
index_merge索引合并优化一般
range索引范围扫描一般
index全索引扫描较差
ALL全表扫描最差

各类型示例：

-- const：主键查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type = const

-- eq_ref：JOIN 时使用主键
EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- type = eq_ref（对于 users 表）

-- ref：非唯一索引等值查询
CREATE INDEX idx_name ON users(name);
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- type = ref

-- range：范围查询
EXPLAIN SELECT * FROM users WHERE id BETWEEN 1 AND 100;
-- type = range

-- index：全索引扫描
EXPLAIN SELECT id FROM users;
-- type = index（覆盖索引）

-- ALL：全表扫描（需要优化）
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type = ALL（函数导致索引失效）

Extra 额外信息​

Extra 列提供额外的执行信息：

Extra 值说明建议
Using index使用覆盖索引，不回表好
Using where服务器层过滤正常
Using temporary使用临时表需关注
Using filesort文件排序需优化
Using index condition索引下推优化好
Using join buffer使用连接缓冲区需关注
Impossible WHEREWHERE 条件不可能为真检查逻辑

Extra 信息示例：

-- Using index：覆盖索引
CREATE INDEX idx_name_email ON users(name, email);
EXPLAIN SELECT name, email FROM users WHERE name = '张三';
-- Extra: Using index

-- Using filesort：需要额外排序
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- Extra: Using filesort

-- Using temporary：使用临时表
EXPLAIN SELECT DISTINCT status FROM orders;
-- Extra: Using temporary

-- Using index condition：索引下推
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- Extra: Using index condition

EXPLAIN ANALYZE（MySQL 8.0.18+）​

EXPLAIN ANALYZE 会实际执行查询并显示真实的时间开销：

EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 输出示例：
-> Index lookup on users using idx_name (name='张三')
    (cost=0.35 rows=1) (actual time=0.015..0.016 rows=1 loops=1)

-- 解读：
-- cost=0.35：预估成本
-- rows=1：预估行数
-- actual time=0.015..0.016：实际执行时间（毫秒）
-- rows=1：实际返回行数
-- loops=1：循环次数

注意：EXPLAIN ANALYZE 会真正执行查询，对于大数据量修改操作要谨慎使用。

索引优化​

索引是提升查询性能最有效的方法之一。正确设计和使用索引是数据库优化的核心。

索引类型​

MySQL 支持多种索引类型：

-- 主键索引（聚簇索引）
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

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

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 前缀索引
CREATE INDEX idx_name_prefix ON users(name(10));

索引设计原则​

1. 选择合适的列​

-- 好的索引列选择：
-- 1. WHERE 条件中的列
-- 2. JOIN 关联的列
-- 3. ORDER BY 排序的列
-- 4. 区分度高的列

-- 查看列的区分度
SELECT 
    COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM users;
-- selectivity 越接近 1，区分度越高，越适合建索引

MySQL 8.0 索引新特性​

MySQL 8.0 引入了多项索引优化特性，理解并合理使用这些特性可以显著提升查询性能。

降序索引（Descending Index）

在 MySQL 8.0 之前，索引只能按升序存储。对于 ORDER BY col DESC 这样的查询，即使有索引也需要额外的 filesort 操作。MySQL 8.0 支持真正的降序索引，可以完全消除排序开销。

-- 创建降序索引
CREATE INDEX idx_created_desc ON orders(created_at DESC);

-- 复合索引中混合排序方向
CREATE INDEX idx_user_date ON orders(user_id ASC, created_at DESC);

-- 这个查询可以直接利用索引，无需 filesort
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY created_at DESC;
-- Extra: Using index condition（无 Using filesort）

适用场景：

  • 需要按时间倒序查询的场景（如获取最新记录）
  • 多列排序方向不一致的查询
  • 报表和仪表板中的排名查询

隐藏索引（Invisible Index）

隐藏索引是一种特殊的索引，优化器会忽略它的存在，但后台仍会维护它。这个特性对于测试索引影响、安全删除索引非常有用。

-- 创建隐藏索引
CREATE INDEX idx_test ON users(email) INVISIBLE;

-- 将现有索引设为隐藏
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- 将隐藏索引恢复可见
ALTER TABLE users ALTER INDEX idx_test VISIBLE;

-- 查看索引可见性
SHOW INDEX FROM users;
-- Visible 字段显示 YES 或 NO

应用场景：

场景操作说明
测试索引删除设为 INVISIBLE观察性能是否下降
安全删除索引INVISIBLE -> 一段时间 -> DROP确认无影响后再删除
测试新索引效果创建 INVISIBLE -> 设为 VISIBLE对比前后执行计划
软删除设为 INVISIBLE保留索引但不使用
-- 示例：安全删除索引的流程
-- 1. 先隐藏索引
ALTER TABLE orders ALTER INDEX idx_old VISIBLE;

-- 2. 观察一段时间（如一周），检查慢查询日志
-- 3. 如果没有性能问题，真正删除
DROP INDEX idx_old ON orders;

-- 4. 如果发现性能下降，立即恢复
ALTER TABLE orders ALTER INDEX idx_old VISIBLE;

直方图统计（Histogram Statistics）

直方图是 MySQL 8.0 引入的重要统计信息，用于描述列值的数据分布。与索引不同，直方图不占用存储空间，不影响写性能，但能帮助优化器做出更准确的执行计划决策。

-- 创建直方图统计
ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 32 BUCKETS;

-- 为多列创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, created_at WITH 32 BUCKETS;

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

-- 查看直方图信息
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM->>'$."last-updated"' AS last_updated,
    HISTOGRAM->>'$."number-of-buckets-specified"' AS buckets_specified,
    JSON_LENGTH(HISTOGRAM, '$.buckets') AS buckets_created
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = 'your_database';

直方图的两种类型：

类型说明适用场景
Singleton（单值）每个桶存储单个值低基数列（如性别、状态）
Equi-height（等高）每个桶存储相近数量的行高基数列（如价格、时间）

何时使用直方图：

-- 场景一：数据分布不均匀（倾斜）
-- 例如：订单状态大部分是 'completed'
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 8 BUCKETS;

-- 查询时优化器能准确估计行数
SELECT * FROM orders WHERE status = 'pending';
-- 优化器知道只有少量行匹配，可能选择索引查找
-- 而非全表扫描

-- 场景二：范围查询的估算
-- 时间列通常有数据倾斜
ANALYZE TABLE logs UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;

-- 优化器能准确估计时间范围内的数据量
SELECT * FROM logs WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

直方图 vs 索引：

特性直方图索引
存储开销极小较大
写性能影响无有（需维护索引）
查询加速无（仅优化执行计划）有
更新频率手动更新自动维护
适用场景数据分布倾斜的列需要快速定位的列

直方图最佳实践：

-- 1. 为数据分布倾斜的列创建直方图
-- 检查数据分布
SELECT status, COUNT(*) as cnt, 
       COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders) as pct
FROM orders GROUP BY status;

-- 如果发现明显倾斜，创建直方图
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

-- 2. 桶数量的选择
-- 低基数列（如状态码）：8-16 个桶
-- 高基数列（如价格）：64-128 个桶
-- 默认建议从 32 开始

-- 3. 定期更新直方图
-- 对于变化频繁的表，建议定期更新
-- 可以通过事件调度器自动执行
CREATE EVENT update_histograms
ON SCHEDULE EVERY 1 WEEK
DO
  ANALYZE TABLE orders UPDATE HISTOGRAM ON status, created_at WITH 32 BUCKETS;

索引条件下推（Index Condition Pushdown，ICP）

索引条件下推是 MySQL 5.6 引入但持续优化的特性。当索引列上有无法完全利用索引的条件时，ICP 将过滤条件下推到存储引擎层执行，减少回表次数。

-- 假设有复合索引 idx_name_age
CREATE INDEX idx_name_age ON users(name, age);

-- 传统方式（无 ICP）：
-- 1. 存储引擎通过索引找到 name LIKE '张%' 的所有行
-- 2. 回表获取完整行
-- 3. Server 层过滤 age > 20

-- 使用 ICP：
-- 1. 存储引擎在索引扫描时直接过滤 age > 20
-- 2. 只对满足条件的行回表
-- 显著减少回表次数

-- 查看是否使用了 ICP
EXPLAIN SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- Extra: Using index condition

-- ICP 适用条件：
-- 1. 使用 InnoDB 或 MyISAM 引擎
-- 2. 使用索引范围扫描或索引查找
-- 3. 条件可以被下推到存储引擎

-- 控制是否启用 ICP
SET optimizer_switch = 'index_condition_pushdown=on';  -- 启用（默认）
SET optimizer_switch = 'index_condition_pushdown=off'; -- 禁用

2. 遵循最左前缀原则​

-- 复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- 能使用索引的查询
WHERE name = '张三'
WHERE name = '张三' AND age = 25
WHERE name = '张三' AND age = 25 AND city = '北京'

-- 不能使用索引的查询
WHERE age = 25              -- 跳过了 name
WHERE city = '北京'         -- 跳过了 name 和 age
WHERE name = '张三' AND city = '北京'  -- 跳过了 age，只能用到 name

最左前缀原则意味着复合索引只能从最左列开始连续使用。设计索引时，应该将区分度高的列放在左边。

3. 覆盖索引优化​

-- 普通查询需要回表
SELECT * FROM users WHERE name = '张三';

-- 覆盖索引避免回表
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index（无需回表）

-- 尽量只查询需要的列
-- 不好的写法
SELECT * FROM users WHERE name = '张三';

-- 好的写法
SELECT id, name, email FROM users WHERE name = '张三';

覆盖索引是指查询所需的所有列都在索引中，无需回表查询数据行。这可以显著提高查询性能。

索引失效场景​

以下情况会导致索引失效：

-- 1. 在索引列上使用函数
-- 索引失效
WHERE YEAR(created_at) = 2024
-- 优化
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- 2. 隐式类型转换
-- 假设 phone 是 VARCHAR 类型
-- 索引失效
WHERE phone = 13800138000
-- 优化
WHERE phone = '13800138000'

-- 3. LIKE 以通配符开头
-- 索引失效
WHERE name LIKE '%张'
-- 能用索引
WHERE name LIKE '张%'

-- 4. 使用 OR（部分情况）
-- 如果 OR 前后列不同，可能失效
WHERE name = '张三' OR age = 25
-- 优化：使用 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25

-- 5. 使用 NOT IN、NOT EXISTS、<>
-- 可能导致索引失效
WHERE status NOT IN (1, 2)
-- 优化
WHERE status IN (0, 3, 4, 5)

-- 6. 对索引列进行计算
-- 索引失效
WHERE age + 1 = 26
-- 优化
WHERE age = 25

-- 7. IS NULL 和 IS NOT NULL
-- 可能导致索引失效（取决于数据分布）
WHERE name IS NOT NULL

索引维护​

-- 查看索引使用情况
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database';

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_READ = 0
AND COUNT_FETCH = 0;

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 优化表（重建表，释放空间）
OPTIMIZE TABLE users;

-- 检查索引基数
SHOW INDEX FROM users;

慢查询日志​

慢查询日志是定位性能问题的重要工具。

启用慢查询日志​

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态启用（无需重启）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

配置文件方式​

[mysqld]
# 启用慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log

# 慢查询阈值（秒，支持小数）
long_query_time = 1

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# 限制每分钟记录的索引查询数量
log_throttle_queries_not_using_indexes = 10

# 记录慢管理语句
log_slow_admin_statements = 1

# 输出目标（FILE、TABLE 或 FILE,TABLE）
log_output = 'FILE'

分析慢查询日志​

# 使用 mysqldumpslow 汇总分析
# 按平均查询时间排序，显示前 10 条
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# 按总查询时间排序
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按查询次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按平均扫描行数排序
mysqldumpslow -s ar -t 10 /var/log/mysql/slow.log

mysqldumpslow 输出示例：

Count: 1523  Time=2.45s (3735s)  Lock=0.00s  Rows=1.0 (1523), webapp[webapp]@localhost
  SELECT * FROM orders WHERE customer_id = N AND status = 'S'
  • Count: 查询执行次数
  • Time: 平均时间（总时间）
  • Lock: 锁等待时间
  • Rows: 返回行数

查询慢日志表​

如果 log_output = 'TABLE'，可以查询表：

-- 查询最近的慢查询
SELECT 
    start_time,
    user_host,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 10;

-- 查询扫描行数过多的查询
SELECT 
    sql_text,
    rows_examined,
    rows_sent,
    rows_examined / rows_sent AS ratio
FROM mysql.slow_log
WHERE rows_sent > 0
ORDER BY ratio DESC
LIMIT 10;

使用 pt-query-digest​

Percona Toolkit 提供更强大的分析工具：

# 安装 Percona Toolkit
sudo apt-get install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > analysis.txt

# 只分析最近 1 小时的查询
pt-query-digest --since '1h' /var/log/mysql/slow.log

# 分析特定数据库
pt-query-digest --filter '$event->{db} =~ /mydb/' /var/log/mysql/slow.log

查询优化技巧​

1. 只查询需要的列​

-- 不好：SELECT *
SELECT * FROM users WHERE id = 1;

-- 好：只查询需要的列
SELECT id, name, email FROM users WHERE id = 1;

2. 使用 LIMIT 限制结果​

-- 限制返回行数
SELECT * FROM orders WHERE status = 'pending' LIMIT 100;

-- 分页查询
SELECT * FROM orders ORDER BY id LIMIT 0, 20;
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 0;

3. 优化分页查询​

-- 传统分页（偏移量大时性能差）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化方案 1：使用 WHERE 子查询
SELECT * FROM orders 
WHERE id > 1000000 
ORDER BY id LIMIT 20;

-- 优化方案 2：使用 INNER JOIN
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

4. 避免 SELECT DISTINCT​

-- DISTINCT 需要排序和去重
SELECT DISTINCT name FROM users;

-- 使用 GROUP BY 可能更高效
SELECT name FROM users GROUP BY name;

-- 或确保索引覆盖
CREATE INDEX idx_name ON users(name);

5. 使用 UNION ALL 代替 UNION​

-- UNION 会去重，需要临时表
SELECT name FROM users WHERE age > 20
UNION
SELECT name FROM admins WHERE age > 20;

-- UNION ALL 不去重，性能更好
SELECT name FROM users WHERE age > 20
UNION ALL
SELECT name FROM admins WHERE age > 20;

6. 批量插入优化​

-- 单条插入（效率低）
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');
INSERT INTO users (name, email) VALUES ('李四', '[email protected]');

-- 批量插入（效率高）
INSERT INTO users (name, email) VALUES 
    ('张三', '[email protected]'),
    ('李四', '[email protected]'),
    ('王五', '[email protected]');

-- 使用 LOAD DATA INFILE（最快）
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(name, email);

7. 使用 JOIN 代替子查询​

-- 子查询（可能效率低）
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- JOIN（通常更高效）
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

8. 避免大事务​

-- 大事务会长时间持有锁
BEGIN;
-- 大量操作...
UPDATE orders SET status = 'completed' WHERE status = 'pending';
-- 更多操作...
COMMIT;

-- 分批处理
-- 每次处理 1000 条
UPDATE orders SET status = 'completed' 
WHERE status = 'pending' 
LIMIT 1000;

9. 使用 CTE 优化复杂查询​

MySQL 8.0 支持公用表表达式（CTE），可以简化复杂查询并提高可读性：

-- 使用 CTE 简化复杂统计
WITH monthly_stats AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
avg_stats AS (
    SELECT AVG(order_count) AS avg_orders, AVG(total_amount) AS avg_amount
    FROM monthly_stats
)
SELECT 
    m.month,
    m.order_count,
    m.total_amount,
    CASE 
        WHEN m.order_count > a.avg_orders THEN '高于平均'
        ELSE '低于平均'
    END AS comparison
FROM monthly_stats m
CROSS JOIN avg_stats a
ORDER BY m.month;

-- 递归 CTE 处理层级数据
WITH RECURSIVE category_tree AS (
    -- 基础查询：顶级分类
    SELECT id, name, parent_id, 1 AS level, CAST(name AS CHAR(1000)) AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归查询：子分类
    SELECT c.id, c.name, c.parent_id, ct.level + 1, CONCAT(ct.path, ' > ', c.name)
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.level < 10  -- 防止无限递归
)
SELECT * FROM category_tree ORDER BY path;

10. 窗口函数优化排名查询​

MySQL 8.0 的窗口函数可以简化复杂的排名和聚合查询：

-- 传统方式：使用子查询计算排名（效率低）
SELECT 
    u.*,
    (SELECT COUNT(*) + 1 FROM users u2 WHERE u2.score > u.score) AS rank
FROM users u
ORDER BY rank;

-- 使用窗口函数（效率高）
SELECT 
    *,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY score DESC, id) AS row_num
FROM users;

-- 分组排名
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY score DESC) AS dept_rank
FROM employees;

-- 计算移动平均
SELECT 
    date,
    amount,
    AVG(amount) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

-- 累计求和
SELECT 
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS cumulative_amount
FROM daily_sales;

-- 同比/环比计算
WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT 
    month,
    total_amount,
    LAG(total_amount, 1) OVER (ORDER BY month) AS prev_month,
    LAG(total_amount, 12) OVER (ORDER BY month) AS prev_year_month,
    ROUND((total_amount - LAG(total_amount, 1) OVER (ORDER BY month)) 
          / LAG(total_amount, 1) OVER (ORDER BY month) * 100, 2) AS mom_growth
FROM monthly_sales;

11. Lateral Derived Table（MySQL 8.0.14+）​

MySQL 8.0.14 引入了 LATERAL 关键字，允许派生表引用外部表的列：

-- 需求：获取每个用户的最近 3 条订单

-- 传统方式：需要使用变量或多表关联，非常复杂

-- 使用 LATERAL 派生表（简洁高效）
SELECT 
    u.id,
    u.name,
    o.order_id,
    o.amount,
    o.order_date
FROM users u
LEFT JOIN LATERAL (
    SELECT order_id, amount, order_date
    FROM orders
    WHERE user_id = u.id
    ORDER BY order_date DESC
    LIMIT 3
) o ON TRUE;

-- 另一个应用：获取每个分组的前 N 条记录
SELECT 
    c.category_name,
    p.product_name,
    p.sales_count
FROM categories c
LEFT JOIN LATERAL (
    SELECT product_name, sales_count
    FROM products
    WHERE category_id = c.id
    ORDER BY sales_count DESC
    LIMIT 5
) p ON TRUE;

12. JSON 函数优化​

MySQL 8.0 增强了 JSON 支持，可以避免频繁的表关联：

-- 存储 JSON 数据
CREATE TABLE user_preferences (
    user_id INT PRIMARY KEY,
    prefs JSON
);

INSERT INTO user_preferences VALUES 
(1, '{"theme": "dark", "notifications": {"email": true, "sms": false}, "interests": ["tech", "sports"]}');

-- 提取 JSON 值
SELECT 
    user_id,
    prefs->>'$.theme' AS theme,
    prefs->>'$.notifications.email' AS email_notify
FROM user_preferences;

-- 更新 JSON 值
UPDATE user_preferences 
SET prefs = JSON_SET(prefs, '$.theme', 'light')
WHERE user_id = 1;

-- 添加数组元素
UPDATE user_preferences
SET prefs = JSON_ARRAY_APPEND(prefs, '$.interests', 'music')
WHERE user_id = 1;

-- 条件查询 JSON
SELECT * FROM user_preferences
WHERE JSON_EXTRACT(prefs, '$.notifications.email') = TRUE;

-- 创建虚拟列和索引（MySQL 8.0）
ALTER TABLE user_preferences
ADD COLUMN theme VARCHAR(20) 
    GENERATED ALWAYS AS (prefs->>'$.theme') STORED;

CREATE INDEX idx_theme ON user_preferences(theme);

-- 现在可以高效查询
SELECT * FROM user_preferences WHERE theme = 'dark';

服务器参数优化​

InnoDB 存储引擎优化​

InnoDB 是 MySQL 的默认存储引擎，正确配置 InnoDB 参数对性能至关重要。

Buffer Pool 核心配置​

Buffer Pool 是 InnoDB 中最重要的内存区域，用于缓存数据和索引。正确配置 Buffer Pool 可以显著提高性能。

[mysqld]
# InnoDB Buffer Pool 大小（建议物理内存的 60-80%）
innodb_buffer_pool_size = 4G

# Buffer Pool 实例数（每个实例至少 1GB）
# 多实例可以减少锁竞争，提高并发性能
innodb_buffer_pool_instances = 4

# Buffer Pool 预热：MySQL 重启时加载之前的热数据
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

Buffer Pool 大小计算：

理想大小 = 数据总大小 + 索引总大小 + 预留空间

估算方式：
1. 查看当前数据大小：
   SELECT SUM(data_length + index_length) / 1024 / 1024 / 1024 AS size_gb
   FROM information_schema.tables;

2. 考虑增长空间：预留 20-30% 空间

3. 系统内存分配：
   - 操作系统：预留 10-20%
   - 其他 MySQL 缓存：连接缓冲、排序缓冲等
   - Buffer Pool：剩余内存的 70-80%

监控 Buffer Pool 效率：

-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算命中率（应 > 99%）
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads'
);

-- 命中率 = 1 - (reads / read_requests)
-- 如果命中率低，考虑增加 buffer_pool_size

-- 查看当前 Buffer Pool 内容
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COUNT(*) AS pages,
    SUM(DATA_SIZE) / 1024 / 1024 AS size_mb
FROM information_schema.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY pages DESC
LIMIT 20;

Log Buffer 和 Redo Log 配置​

[mysqld]
# Log Buffer 大小（默认 16MB，写密集场景可增大）
innodb_log_buffer_size = 32M

# Redo Log 文件大小（增大可减少检查点频率）
innodb_log_file_size = 512M

# Redo Log 文件数量
innodb_log_files_in_group = 2

# 日志刷新策略
# 0：每秒刷新（性能最好，可能丢 1 秒数据）
# 1：每次事务提交刷新（最安全，默认）
# 2：每次提交写入 OS 缓存，每秒刷新（折中方案）
innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit 选择建议：

值性能安全性适用场景
0最高最低可容忍少量数据丢失的非关键业务
1较低最高金融、交易等关键业务（默认）
2中等中等一般业务，可容忍极少数据丢失

刷新策略优化​

[mysqld]
# 刷新方法（Linux 推荐 O_DIRECT，避免双重缓冲）
innodb_flush_method = O_DIRECT

# 脏页刷新比例（默认 75，增大可减少刷新频率）
innodb_max_dirty_pages_pct = 75

# 脏页刷新延迟（减少刷新对性能的影响）
innodb_max_dirty_pages_pct_lwm = 10

# 后台刷新线程数
innodb_page_cleaners = 4

# 自适应刷新（根据工作量自动调整刷新频率）
innodb_adaptive_flushing = ON

并发控制​

[mysqld]
# 并发线程数（0 表示无限制）
innodb_thread_concurrency = 0

# 并发读线程数（建议等于 CPU 核心数）
innodb_read_io_threads = 8

# 并发写线程数
innodb_write_io_threads = 8

# 锁等待超时（秒）
innodb_lock_wait_timeout = 50

# 死锁检测（高并发可关闭以提升性能，但需要监控）
innodb_deadlock_detect = ON

其他 InnoDB 优化参数​

[mysqld]
# 文件格式（MySQL 8.0 已移除，默认使用 Barracuda）
# innodb_file_format = Barracuda

# 每表独立表空间（MySQL 8.0 默认开启）
innodb_file_per_table = ON

# 压缩页级别（0-9，越大压缩比越高但 CPU 消耗越大）
# 仅对 ROW_FORMAT=COMPRESSED 的表有效
innodb_compression_level = 6

# 自动扩展间隔（默认 64MB）
innodb_autoextend_increment = 64

# 统计信息持久化（MySQL 8.0 默认 ON）
innodb_stats_persistent = ON

# 统计信息采样页数
innodb_stats_persistent_sample_pages = 20

Buffer Pool 配置​

[mysqld]
# InnoDB Buffer Pool 大小（建议物理内存的 70-80%）
innodb_buffer_pool_size = 4G

# Buffer Pool 实例数（每个实例至少 1GB）
innodb_buffer_pool_instances = 4

# 预读优化
innodb_read_ahead_threshold = 56
-- 查看 Buffer Pool 状态
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 计算 Buffer Pool 命中率
SELECT 
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100 
    AS hit_rate
FROM (
    SELECT 
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status 
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) t;
-- 命中率应该在 99% 以上

连接参数优化​

[mysqld]
# 最大连接数
max_connections = 500

# 等待超时（秒）
wait_timeout = 28800
interactive_timeout = 28800

# 连接缓冲区
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

日志配置​

[mysqld]
# 慢查询日志
slow_query_log = 1
long_query_time = 1

# 二进制日志
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# 错误日志
log_error = /var/log/mysql/error.log

# InnoDB 日志
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1

性能监控​

使用 Performance Schema​

-- 启用 Performance Schema
-- 在 my.cnf 中确保：
-- performance_schema = ON

-- 查看等待事件
SELECT 
    EVENT_NAME,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000 AS total_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 查看语句执行统计
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_TIMER_WAIT / 1000000000 AS total_seconds,
    AVG_TIMER_WAIT / 1000000 AS avg_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

使用 sys 数据库​

MySQL 8.0 提供了 sys 数据库，简化性能分析：

-- 查看最慢的 SQL
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- 查看全表扫描的 SQL
SELECT * FROM sys.statements_with_full_table_scans;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

-- 查看表访问统计
SELECT * FROM sys.schema_table_statistics;

-- 查看内存使用
SELECT * FROM sys.memory_global_by_current_bytes;

小结​

本章我们学习了：

  1. EXPLAIN 执行计划：分析查询的执行方式
  2. 索引优化：设计原则、索引失效场景
  3. 慢查询日志：定位和分析慢查询
  4. 查询优化技巧：实用的 SQL 优化方法
  5. 服务器参数优化：Buffer Pool、连接、日志配置
  6. 性能监控：Performance Schema 和 sys 数据库

练习​

  1. 使用 EXPLAIN 分析一个复杂查询的执行计划
  2. 为一个慢查询设计合适的索引
  3. 配置慢查询日志并分析结果
  4. 计算当前数据库的 Buffer Pool 命中率

参考资料​