MySQL性能优化 - 当发网

MySQL性能优化概述

MySQL性能优化是一个系统性的工作，涉及查询优化、索引优化、服务器配置优化、架构设计优化等多个方面。

性能优化的目标

提高查询响应速度
减少服务器资源消耗
提高系统并发处理能力
确保系统稳定性和可扩展性

查询优化

使用EXPLAIN分析查询

                        SQL示例
                    
                        EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123 AND order_date > '2024-01-01';

EXPLAIN输出格式（FORMAT选项）

                        SQL示例
                    

                        -- 传统表格格式
EXPLAIN SELECT ...;

-- JSON格式，提供更详细信息
EXPLAIN FORMAT=JSON SELECT ...;

-- TREE格式，展示执行计划树（MySQL 8.0.16+）
EXPLAIN FORMAT=TREE SELECT ...;

-- 查看实际执行统计（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT ...;
                    

EXPLAIN输出关键字段

字段	描述	优化建议
type	连接类型	避免ALL（全表扫描），追求const、eq_ref、ref
key	实际使用的索引	确保查询使用了合适的索引
rows	预估扫描行数	行数越少越好
Extra	额外信息	避免Using filesort、Using temporary
filtered	过滤比例	值越高越好，表示WHERE条件过滤掉更多行

使用Optimizer Trace分析优化器决策

                        SQL示例
                    

                        SET optimizer_trace = "enabled=on";
SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace = "enabled=off";
                    

避免SELECT *

                        SQL示例
                    

                        -- 不推荐的写法
SELECT * FROM customers;

-- 推荐的写法
SELECT customer_id, first_name, last_name, email 
FROM customers;
                    

使用LIMIT限制结果集

                        SQL示例
                    

                        -- 限制返回的记录数
SELECT * FROM products 
ORDER BY created_at DESC
LIMIT 10;
                    

避免在WHERE子句中使用函数

                        SQL示例
                    

                        -- 不推荐的写法（无法使用索引）
SELECT * FROM orders 
WHERE YEAR(order_date) = 2024;

-- 推荐的写法
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
                    

查询重写技巧

                        SQL示例
                    

                        -- 将OR改写为UNION（可能更高效）
-- 原查询
SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';

-- 改写为UNION
SELECT * FROM employees WHERE department = 'IT'
UNION ALL
SELECT * FROM employees WHERE department = 'HR';

-- 使用COUNT替代EXISTS（在特定场景下）
-- 原查询
SELECT IF(EXISTS(SELECT 1 FROM orders WHERE customer_id=100), 1, 0);

-- 改写
SELECT COUNT(1) FROM orders WHERE customer_id=100 LIMIT 1;
                    

索引优化

选择合适的索引列

WHERE子句中的列
JOIN连接条件的列
ORDER BY和GROUP BY中的列
选择性高的列（唯一值多的列）

索引下推（Index Condition Pushdown）

                        SQL示例
                    
                        -- 启用索引下推（默认启用）
SET optimizer_switch = 'index_condition_pushdown=on';

-- 查看是否使用索引下推（Extra列显示Using index condition）
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND first_name LIKE 'J%';

多范围读取（Multi-Range Read）

                        SQL示例
                    
                        -- 启用MRR
SET optimizer_switch = 'mrr=on,mrr_cost_based=off';

-- MRR可以优化二级索引回表操作，减少随机IO

函数索引（MySQL 8.0.13+）

                        SQL示例
                    
                        -- 创建函数索引
CREATE INDEX idx_year_order_date ON orders ((YEAR(order_date)));

-- 查询可以使用该索引
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 函数索引也支持JSON字段
CREATE INDEX idx_json_data ON products ((data->'$.price'));

生成列索引

                        SQL示例
                    

                        -- 创建生成列
ALTER TABLE employees ADD COLUMN full_name VARCHAR(200) 
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

-- 在生成列上创建索引
CREATE INDEX idx_full_name ON employees(full_name);
                    

复合索引的最左前缀原则

                        SQL示例
                    

                        -- 创建复合索引
CREATE INDEX idx_last_first ON customers(last_name, first_name);

-- 可以使用索引的查询
SELECT * FROM customers WHERE last_name = 'Smith';
SELECT * FROM customers WHERE last_name = 'Smith' AND first_name = 'John';

-- 不能使用索引的查询
SELECT * FROM customers WHERE first_name = 'John';
                    

覆盖索引

                        SQL示例
                    

                        -- 创建覆盖索引
CREATE INDEX idx_customer_info ON customers(last_name, first_name, email);

-- 查询可以直接从索引中获取数据，无需访问表
SELECT last_name, first_name, email FROM customers 
WHERE last_name = 'Smith';
                    

索引合并（Index Merge）

                        SQL示例
                    

                        -- MySQL可能使用索引合并来组合多个索引
CREATE INDEX idx_age ON employees(age);
CREATE INDEX idx_salary ON employees(salary);

-- 查询可能同时使用两个索引（Extra显示Using union）
EXPLAIN SELECT * FROM employees WHERE age = 30 OR salary > 50000;
                    

表结构优化

选择合适的数据类型

使用最小的数据类型
避免使用TEXT/BLOB类型，除非必要
使用INT而不是VARCHAR存储数字
使用ENUM代替VARCHAR存储固定值

规范化与反规范化的平衡

规范化：减少数据冗余，提高数据一致性，但可能增加查询复杂度。
反规范化：增加数据冗余，减少表连接，提高查询性能。

分区表

                        SQL示例
                    

                        -- 按范围分区
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024)
);

-- 分区修剪：查询只访问相关分区
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01';
                    

服务器配置优化

关键配置参数

参数	描述	建议值
innodb_buffer_pool_size	InnoDB缓冲池大小	物理内存的50-80%
innodb_log_file_size	重做日志文件大小	1-4GB
max_connections	最大连接数	根据应用需求调整
innodb_flush_log_at_trx_commit	日志刷新策略	1（最安全）或2（高性能）
innodb_io_capacity	IO能力设置	根据磁盘性能设置（如SSD设为2000）

InnoDB缓冲池调优

                        SQL示例
                    

                        -- 查看缓冲池状态
SHOW ENGINE INNODB STATUS\G
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)

-- 动态调整缓冲池大小（MySQL 5.7+）
SET GLOBAL innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;
                    

redo log和undo log优化

                        配置示例
                    

                        [mysqld]
# redo log配置
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 64M

# undo log配置（MySQL 8.0）
innodb_undo_tablespaces = 2
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 2G
                    

监控服务器状态

                        SQL示例
                    

                        -- 查看服务器状态
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- 查看进程列表
SHOW PROCESSLIST;

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
                    

使用Performance Schema进行性能分析

                        SQL示例
                    

                        -- 启用Performance Schema（需要配置）
[mysqld]
performance_schema = ON

-- 查询最耗时的语句
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;
                    

SQL提示（Optimizer Hints）

                        SQL示例
                    

                        -- 强制使用特定索引
SELECT * FROM employees USE INDEX (idx_last_name) WHERE last_name = 'Smith';

-- 忽略索引
SELECT * FROM employees IGNORE INDEX (idx_last_name) WHERE last_name = 'Smith';

-- 强制使用索引
SELECT * FROM employees FORCE INDEX (idx_last_name) WHERE last_name = 'Smith';

-- 设置JOIN顺序（MySQL 8.0+）
SELECT /*+ JOIN_ORDER(customers, orders) */ * 
FROM customers JOIN orders ON customers.id = orders.customer_id;
                    

批量操作优化

                        SQL示例
                    

                        -- 使用批量INSERT提高插入性能
INSERT INTO employees (name, department) VALUES
    ('Alice', 'IT'), ('Bob', 'HR'), ('Charlie', 'Finance');

-- 使用LOAD DATA INFILE批量导入
LOAD DATA INFILE '/tmp/data.csv' INTO TABLE employees
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 批量UPDATE时使用CASE WHEN
UPDATE products SET price = CASE id
    WHEN 1 THEN 19.99
    WHEN 2 THEN 29.99
    ELSE price
END WHERE id IN (1,2);
                    

临时表优化

                        SQL示例
                    

                        -- 调整临时表参数
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M
internal_tmp_mem_storage_engine = TempTable  -- MySQL 8.0默认使用TempTable引擎

-- 查看临时表使用情况
SHOW STATUS LIKE 'Created_tmp%';
                    

架构优化

读写分离

将读操作和写操作分发到不同的数据库服务器：

主服务器处理写操作
从服务器处理读操作
通过主从复制保持数据同步

分库分表

当单表数据量过大时，可以考虑分库分表：

水平分表：按数据行拆分
垂直分表：按列拆分
分库：将不同业务的数据分布到不同数据库

使用缓存

应用层缓存（Redis、Memcached）
数据库查询缓存（MySQL 8.0之前）
CDN缓存静态资源

优化原则：性能优化是一个持续的过程，需要根据实际业务需求和数据特征进行调整。测量、分析、优化、再测量。

性能监控工具

使用专业的监控工具可以帮助识别性能瓶颈：

MySQL内置工具

SHOW STATUS - 查看服务器状态变量
SHOW PROCESSLIST - 查看当前连接和查询
EXPLAIN - 分析查询执行计划
Performance Schema - 性能模式，收集数据库性能数据
Information Schema - 信息模式，查看数据库元数据

外部监控工具

MySQL Enterprise Monitor - MySQL官方企业级监控工具
Percona Monitoring and Management - Percona监控管理工具
Prometheus + Grafana - 开源监控解决方案
pt-query-digest - Percona Toolkit中的查询分析工具

慢查询日志分析

慢查询日志是识别性能问题的重要工具：

启用慢查询日志

                        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
log_slow_admin_statements = 1
min_examined_row_limit = 100
                    

分析慢查询日志

                        终端命令
                    
                        # 使用mysqldumpslow分析慢查询日志
mysqldumpslow /var/log/mysql/slow.log

# 使用pt-query-digest分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

下一步学习

掌握了性能优化知识后，您可以继续学习：

安全管理 - 了解MySQL安全管理和权限控制
备份与恢复 - 学习数据备份和灾难恢复策略
MySQL参考手册 - 查看完整的MySQL语法参考