MySQL 性能优化和索引

MySQL 性能优化对于处理大量数据并需要快速查询响应的应用程序至关重要。本教程涵盖了优化 MySQL 性能的综合策略，重点关注索引、查询优化和数据库调优。

理解 MySQL 性能

什么影响 MySQL 性能？

数据库架构设计 - 表结构和关系
索引策略 - 如何为快速检索建立数据索引
查询结构 - 查询如何编写和执行
服务器配置 - MySQL 服务器设置和资源
硬件资源 - CPU、RAM、存储和网络
应用程序设计 - 应用程序如何与数据库交互

要监控的性能指标

查询响应时间 - 查询执行所需时间
吞吐量 - 每秒处理的查询数
CPU 使用率 - 处理器利用率
内存使用 - RAM 消耗
磁盘 I/O - 读/写操作
连接数 - 活跃数据库连接

MySQL 索引深入解析

什么是索引？

索引是改善数据库表上数据检索操作速度的数据结构。它们创建数据的快捷方式，类似于书中的索引。

索引类型

1. 主索引（聚集）

sql

-- 主键自动创建聚集索引
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 主键索引自动创建
SHOW INDEX FROM users;

2. 辅助索引（非聚集）

sql

-- 为频繁查询的列创建辅助索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);

-- 查看所有索引
SHOW INDEX FROM users;

3. 复合索引

sql

-- 多列索引
CREATE INDEX idx_status_created ON users(status, created_at);
CREATE INDEX idx_name_email ON users(first_name, last_name, email);

-- 复合索引中顺序很重要
-- 此索引帮助查询过滤：
-- 1. 仅按 status
-- 2. 按 status 和 created_at
-- 但不帮助仅按 created_at

4. 唯一索引

sql

-- 确保唯一性并提高性能
CREATE UNIQUE INDEX idx_unique_email ON users(email);
CREATE UNIQUE INDEX idx_unique_username ON users(username);

-- 复合唯一索引
CREATE UNIQUE INDEX idx_unique_user_project ON user_projects(user_id, project_id);

5. 部分索引

sql

-- 仅索引特定行（MySQL 8.0+）
CREATE INDEX idx_active_users ON users(username) WHERE status = 'active';

-- 对于较旧的 MySQL 版本，使用函数方法
CREATE INDEX idx_active_users ON users(status, username);

6. 全文索引

sql

-- 用于文本搜索功能
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)
);

-- 或添加到现有表
ALTER TABLE articles ADD FULLTEXT(title, content);

-- 使用全文搜索
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database optimization');

索引最佳实践

1. 选择正确的列进行索引

sql

-- ✅ 索引的好候选：
-- 在 WHERE 子句中频繁使用
CREATE INDEX idx_order_status ON orders(status);

-- 在 JOIN 条件中使用
CREATE INDEX idx_order_user_id ON orders(user_id);

-- 在 ORDER BY 子句中使用
CREATE INDEX idx_product_price ON products(price);

-- 在 GROUP BY 子句中使用
CREATE INDEX idx_order_date ON orders(DATE(created_at));

2. 避免过度索引

sql

-- ❌ 坏：一个表上有太多索引
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category_id INT,
    created_at TIMESTAMP
);

-- 不要在每列上都创建索引
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_price ON products(price);
CREATE INDEX idx_category ON products(category_id);
CREATE INDEX idx_created ON products(created_at);
-- 这会拖慢 INSERT/UPDATE/DELETE 操作

-- ✅ 好：策略性索引
CREATE INDEX idx_category_price ON products(category_id, price);
CREATE INDEX idx_name ON products(name);
-- 用更少的索引覆盖大多数查询模式

3. 复合索引中的索引列顺序

sql

-- 规则：最具选择性的列在前
CREATE INDEX idx_status_date ON orders(status, order_date);

-- 此索引帮助这些查询：
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND order_date > '2023-01-01';

-- 但不能高效地帮助这个：
SELECT * FROM orders WHERE order_date > '2023-01-01';
-- 为此，您需要：CREATE INDEX idx_date ON orders(order_date);

查询优化技术

1. 使用 EXPLAIN 分析查询

sql

-- 基本 EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 更详细的分析
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = '[email protected]';

-- 分析实际执行
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

2. 优化 WHERE 子句

sql

-- ✅ 好：使用索引列
SELECT * FROM users WHERE user_id = 123;

-- ❌ 坏：WHERE 子句中的函数阻止索引使用
SELECT * FROM users WHERE UPPER(username) = 'JOHN';

-- ✅ 好：以您要查询的格式存储数据
SELECT * FROM users WHERE username = 'john';

-- ❌ 坏：前导通配符阻止索引使用
SELECT * FROM users WHERE username LIKE '%john%';

-- ✅ 好：前缀搜索可以使用索引
SELECT * FROM users WHERE username LIKE 'john%';

3. 优化 JOIN

sql

-- ✅ 好：JOIN 列上的适当索引
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id  -- user_id 上的索引
WHERE u.status = 'active';        -- status 上的索引

-- ✅ 好：使用适当的 JOIN 类型
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.id = 123;

-- ❌ 坏：笛卡尔积
SELECT * FROM users, posts WHERE users.id = posts.user_id;

-- ✅ 好：使用显式 JOIN 语法
SELECT * FROM users
JOIN posts ON users.id = posts.user_id;

4. 优化 LIMIT 和 OFFSET

sql

-- ❌ 坏：大 OFFSET 很慢
SELECT * FROM users ORDER BY created_at LIMIT 10000, 10;

-- ✅ 好：使用基于游标的分页
SELECT * FROM users
WHERE created_at > '2023-01-01 10:30:00'
ORDER BY created_at
LIMIT 10;

-- ✅ 好：使用基于 ID 的分页
SELECT * FROM users
WHERE id > 10000
ORDER BY id
LIMIT 10;

5. 优化 GROUP BY 和 ORDER BY

sql

-- ✅ 好：索引支持 GROUP BY
CREATE INDEX idx_category_created ON products(category_id, created_at);

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;

-- ✅ 好：索引支持 ORDER BY
SELECT * FROM products
ORDER BY category_id, price;

-- ❌ 坏：没有适当索引的不同方向 ORDER BY
SELECT * FROM products
ORDER BY category_id ASC, price DESC;

-- ✅ 好：为混合排序创建索引
CREATE INDEX idx_category_price_desc ON products(category_id, price DESC);

高级查询优化

1. 查询重写

sql

-- 原始慢查询
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.order_id = o.id
    AND oi.product_id = 123
);

-- 用 JOIN 优化
SELECT DISTINCT o.* FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id = 123;

-- 或用 IN（有时更快）
SELECT * FROM orders
WHERE id IN (
    SELECT order_id FROM order_items
    WHERE product_id = 123
);

2. 子查询优化

sql

-- ❌ 坏：相关子查询
SELECT * FROM users u
WHERE (
    SELECT COUNT(*) FROM orders o
    WHERE o.user_id = u.id
) > 5;

-- ✅ 好：JOIN 与聚合
SELECT u.* FROM users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > 5
) o ON u.id = o.user_id;

3. 使用覆盖索引

sql

-- 创建包含所有需要列的覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, order_date, total_amount);

-- 查询可以完全从索引中满足
SELECT status, order_date, total_amount
FROM orders
WHERE user_id = 123;

4. 性能分区

sql

-- 按日期范围分区
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 哈希分区以均匀分布
CREATE TABLE user_activities (
    id INT AUTO_INCREMENT,
    user_id INT,
    activity_type VARCHAR(50),
    created_at TIMESTAMP,
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH(user_id) PARTITIONS 4;

数据库配置优化

1. MySQL 配置变量

sql

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'key_buffer_size';
SHOW VARIABLES LIKE 'query_cache_size';

-- 要优化的重要设置：
-- InnoDB 缓冲池大小（最重要）
SET GLOBAL innodb_buffer_pool_size = 2147483648; -- 2GB

-- 查询缓存（在 MySQL 8.0 中已弃用）
SET GLOBAL query_cache_size = 268435456; -- 256MB
SET GLOBAL query_cache_type = ON;

-- 线程设置
SET GLOBAL max_connections = 200;
SET GLOBAL thread_cache_size = 16;

2. InnoDB 配置

sql

-- 缓冲池设置
SET GLOBAL innodb_buffer_pool_size = '70%_of_RAM';
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 日志设置
SET GLOBAL innodb_log_file_size = 268435456; -- 256MB
SET GLOBAL innodb_log_buffer_size = 16777216; -- 16MB

-- I/O 设置
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_flush_method = 'O_DIRECT';

3. 监控和分析

sql

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 性能架构
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC LIMIT 10;

-- 信息架构
SELECT * FROM information_schema.innodb_buffer_pool_stats;

实际优化示例

1. 电子商务产品搜索

sql

-- 产品表结构
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    description TEXT,
    price DECIMAL(10,2),
    category_id INT,
    brand_id INT,
    stock_quantity INT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 搜索功能的最优索引
CREATE INDEX idx_category_status_price ON products(category_id, status, price);
CREATE INDEX idx_brand_status ON products(brand_id, status);
CREATE INDEX idx_status_stock ON products(status, stock_quantity);
CREATE FULLTEXT INDEX idx_fulltext_name_desc ON products(name, description);

-- 优化的搜索查询
SELECT id, name, price
FROM products
WHERE category_id = 1
  AND status = 'active'
  AND stock_quantity > 0
  AND price BETWEEN 10.00 AND 100.00
ORDER BY price ASC
LIMIT 20;

2. 用户活动跟踪

sql

-- 带分区的活动表
CREATE TABLE user_activities (
    id BIGINT AUTO_INCREMENT,
    user_id INT,
    activity_type VARCHAR(50),
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    KEY idx_user_type_date (user_id, activity_type, created_at)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p202301 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01')),
    -- 根据需要添加更多分区
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 最近用户活动的高效查询
SELECT activity_type, COUNT(*) as count
FROM user_activities
WHERE user_id = 123
  AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY activity_type
ORDER BY count DESC;

3. 社交媒体动态优化

sql

-- 帖子表
CREATE TABLE posts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    likes_count INT DEFAULT 0,
    comments_count INT DEFAULT 0,
    status ENUM('active', 'deleted') DEFAULT 'active'
);

-- 动态生成的优化索引
CREATE INDEX idx_user_status_created ON posts(user_id, status, created_at);
CREATE INDEX idx_status_created_likes ON posts(status, created_at, likes_count);

-- 关注者表
CREATE TABLE followers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    follower_id INT,
    following_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_follow (follower_id, following_id),
    KEY idx_follower (follower_id),
    KEY idx_following (following_id)
);

-- 优化的动态查询
SELECT p.id, p.content, p.created_at, p.likes_count
FROM posts p
JOIN followers f ON p.user_id = f.following_id
WHERE f.follower_id = 123
  AND p.status = 'active'
  AND p.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY p.created_at DESC
LIMIT 50;

性能监控工具

1. MySQL 内置工具

sql

-- 显示进程列表
SHOW PROCESSLIST;

-- 显示引擎状态
SHOW ENGINE INNODB STATUS;

-- 性能架构查询
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%SELECT%'
ORDER BY avg_timer_wait DESC LIMIT 10;

-- 检查表大小
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;

2. 外部监控工具

bash

# MySQL Tuner
wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl

# Percona Toolkit
pt-query-digest /var/log/mysql/slow.log

# MySQLReport
./mysqlreport --user root --password your_password

3. 应用程序级监控

python

# Python 示例，带计时
import time
import mysql.connector

def execute_query_with_timing(query):
    start_time = time.time()

    connection = mysql.connector.connect(
        host='localhost',
        database='your_db',
        user='your_user',
        password='your_password'
    )

    cursor = connection.cursor()
    cursor.execute(query)
    results = cursor.fetchall()

    end_time = time.time()
    execution_time = end_time - start_time

    print(f"Query executed in {execution_time:.4f} seconds")

    cursor.close()
    connection.close()

    return results

# 使用
results = execute_query_with_timing("SELECT * FROM users WHERE id = 123")

常见性能反模式

1. N+1 查询问题

sql

-- ❌ 坏：N+1 查询
-- 运行一个查询获取帖子的应用程序代码
SELECT id, title, user_id FROM posts LIMIT 10;

-- 然后为每个帖子运行另一个查询获取用户信息
SELECT name FROM users WHERE id = 1;
SELECT name FROM users WHERE id = 2;
-- ... 10 个更多查询

-- ✅ 好：单个 JOIN 查询
SELECT p.id, p.title, u.name
FROM posts p
JOIN users u ON p.user_id = u.id
LIMIT 10;

2. SELECT * 使用

sql

-- ❌ 坏：选择所有列
SELECT * FROM users WHERE id = 123;

-- ✅ 好：只选择需要的列
SELECT id, username, email FROM users WHERE id = 123;

-- ✅ 好：创建覆盖索引
CREATE INDEX idx_user_info ON users(id, username, email);

3. 低效的分页

sql

-- ❌ 坏：大 OFFSET
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10000, 20;

-- ✅ 好：基于游标的分页
SELECT * FROM posts
WHERE created_at < '2023-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 20;

扩展策略

1. 读取副本

sql

-- 配置读/写分离
-- 主服务器用于写入
INSERT INTO users (username, email) VALUES ('john', '[email protected]');
UPDATE users SET email = '[email protected]' WHERE id = 123;

-- 副本用于读取
SELECT * FROM users WHERE username = 'john';
SELECT COUNT(*) FROM posts WHERE created_at >= '2023-01-01';

2. 数据库分片

sql

-- 按 ID 范围分片用户
-- 分片 1：id 1-1000000 的用户
-- 分片 2：id 1000001-2000000 的用户
-- 分片 3：id 2000001-3000000 的用户

-- 应用程序逻辑确定查询哪个分片
def get_shard_for_user(user_id):
    if user_id <= 1000000:
        return 'shard1'
    elif user_id <= 2000000:
        return 'shard2'
    else:
        return 'shard3'

3. 缓存策略

sql

-- 缓存频繁访问的数据
-- 应用程序缓存用户配置文件 1 小时
SELECT id, username, email, profile_image
FROM users
WHERE id = 123;

-- 缓存聚合数据
SELECT category_id, COUNT(*) as product_count
FROM products
WHERE status = 'active'
GROUP BY category_id;

维护和优化任务

1. 定期索引维护

sql

-- 检查索引使用情况
SELECT
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY
FROM information_schema.statistics
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY CARDINALITY DESC;

-- 删除未使用的索引
-- 首先，监控查询以识别未使用的索引
DROP INDEX idx_unused_column ON table_name;

-- 定期重建索引
ALTER TABLE table_name ENGINE=InnoDB;

2. 表优化

sql

-- 分析表统计信息
ANALYZE TABLE users;

-- 优化表存储
OPTIMIZE TABLE users;

-- 检查表完整性
CHECK TABLE users;

3. 监控查询

sql

-- 启用和监控慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 定期审查慢查询日志
-- tail -f /var/log/mysql/slow.log

-- 使用 pt-query-digest 进行分析
-- pt-query-digest /var/log/mysql/slow.log

最佳实践总结

1. 数据库设计

适当规范化 - 避免过度规范化
使用适当的数据类型 - 适合您数据的最小类型
为您的查询设计 - 为频繁查询的内容建索引
考虑分区 - 对于非常大的表

2. 索引策略

索引频繁查询的列 - WHERE、JOIN、ORDER BY
明智地使用复合索引 - 最具选择性的列在前
避免过度索引 - 平衡查询速度与写入性能
监控索引使用 - 删除未使用的索引

3. 查询优化

使用 EXPLAIN - 始终分析查询执行计划
**避免 SELECT *** - 只查询需要的列
优化 JOIN - 使用适当的 JOIN 类型和索引
最小化子查询 - 考虑使用 JOIN

4. 配置调优

设置适当的缓冲区大小 - 特别是 innodb_buffer_pool_size
监控性能指标 - CPU、内存、I/O
启用慢查询日志 - 识别问题查询
定期维护 - ANALYZE、OPTIMIZE 表

5. 应用程序设计

实现连接池 - 重用数据库连接
使用预准备语句 - 防止 SQL 注入并提高性能
实现缓存 - 减少数据库负载
考虑异步操作 - 对于非关键操作

结论

MySQL 性能优化是一个持续的过程，需要关注数据库设计、索引策略、查询优化和系统配置。主要要点：

从正确的数据库设计开始 - 良好的架构设计防止许多性能问题
策略性索引 - 创建支持最常见查询的索引
监控和分析 - 使用工具识别瓶颈和慢查询
测试和测量 - 始终验证优化是否真正提高了性能
考虑完整堆栈 - 数据库性能影响整体应用程序性能

记住，过早优化可能适得其反。专注于通过监控和测量识别实际瓶颈，然后基于您的特定用例和查询模式应用有针对性的优化。

下一步

掌握 MySQL 性能优化后，探索：

高级复制设置 - 主从和主主复制
MySQL 集群 - 高可用性和可扩展性
NoSQL 替代方案 - 何时考虑文档或键值存储
数据库监控工具 - Percona Monitoring and Management、MySQL Enterprise Monitor
云数据库服务 - Amazon RDS、Google Cloud SQL、Azure Database

性能优化对可扩展应用程序至关重要 - 掌握这些技术来构建高性能数据库系统！

✉️ Discover TinkMail

MySQL 性能优化和索引 ​

理解 MySQL 性能 ​

什么影响 MySQL 性能？ ​

要监控的性能指标 ​

MySQL 索引深入解析 ​

什么是索引？ ​

索引类型 ​

1. 主索引（聚集） ​

2. 辅助索引（非聚集） ​

3. 复合索引 ​

4. 唯一索引 ​

5. 部分索引 ​

6. 全文索引 ​

索引最佳实践 ​

1. 选择正确的列进行索引 ​

2. 避免过度索引 ​

3. 复合索引中的索引列顺序 ​

查询优化技术 ​

1. 使用 EXPLAIN 分析查询 ​

2. 优化 WHERE 子句 ​

3. 优化 JOIN ​

4. 优化 LIMIT 和 OFFSET ​

5. 优化 GROUP BY 和 ORDER BY ​

高级查询优化 ​

1. 查询重写 ​

2. 子查询优化 ​

3. 使用覆盖索引 ​

4. 性能分区 ​

数据库配置优化 ​

1. MySQL 配置变量 ​

2. InnoDB 配置 ​

3. 监控和分析 ​

实际优化示例 ​

1. 电子商务产品搜索 ​

2. 用户活动跟踪 ​

3. 社交媒体动态优化 ​

性能监控工具 ​

1. MySQL 内置工具 ​

2. 外部监控工具 ​

3. 应用程序级监控 ​

常见性能反模式 ​

1. N+1 查询问题 ​

2. SELECT * 使用 ​

3. 低效的分页 ​

扩展策略 ​

1. 读取副本 ​

2. 数据库分片 ​

3. 缓存策略 ​

维护和优化任务 ​

1. 定期索引维护 ​

2. 表优化 ​

3. 监控查询 ​

最佳实践总结 ​

1. 数据库设计 ​

2. 索引策略 ​

3. 查询优化 ​

4. 配置调优 ​

5. 应用程序设计 ​

结论 ​

下一步 ​