索引优化
索引是 MySQL 优化中最核心、最有效的手段，没有正确的索引,一切都无从谈起。
（图片来源网络，侵删）
查看索引信息
-- 查看指定表的索引 SHOW INDEX FROM your_table_name; -- 查看索引使用情况（需要打开 performance_schema） SELECT * FROM sys.schema_index_statistics;
创建索引
-- 创建普通索引 CREATE INDEX idx_column_name ON your_table_name (column_name); -- 创建唯一索引 CREATE UNIQUE INDEX idx_unique_column ON your_table_name (column_name); -- 创建复合索引（多列索引） CREATE INDEX idx_composite ON your_table_name (column_a, column_b); -- 使用 `ALTER TABLE` 创建索引 ALTER TABLE your_table_name ADD INDEX idx_column_name (column_name);
删除索引
-- 删除索引 DROP INDEX idx_column_name ON your_table_name; -- 使用 `ALTER TABLE` 删除索引 ALTER TABLE your_table_name DROP INDEX idx_column_name;
索引优化原则
- 最左前缀原则：对于复合索引
(A, B, C)，查询条件A、A, B、A, B, C都会使用到索引，但B或B, C不会。 - 索引列不参与计算：避免在索引列上使用函数或进行表达式计算，如
WHERE YEAR(create_time) = 2025或WHERE column_a + 1 = 10,这会导致索引失效。 - 避免使用 、
<>、IS NULL、IS NOT NULL作为索引列的判断条件（除非是索引覆盖查询）。 LIKE查询：以 开头的模糊查询会失效。LIKE 'keyword%'可以使用索引。- 选择区分度高的列作为索引：区分度越高，索引的效果越好，性别（区分度低）不适合做索引，而用户ID（区分度高）非常适合。
SQL 查询优化
即使有索引，糟糕的 SQL 语句也会让性能大打折扣。
使用 EXPLAIN 分析查询
这是诊断 SQL 性能问题的核心命令，它告诉你 MySQL 是如何执行一条 SQL 语句的。
EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';
重点关注 EXPLAIN 结果中的列：
id：查询标识符，id相同的执行顺序由上至下，id不同的，id值越大优先级越高。select_type：查询类型，如SIMPLE(简单查询),PRIMARY(主查询),SUBQUERY(子查询),DERIVED(派生表)。table：访问的表名。type：访问类型，这是最重要的指标之一，性能从好到差依次为：system>const>eq_ref>ref>range>index>ALL- 目标：避免出现
ALL（全表扫描），尽量优化到ref或range。
key：实际使用的索引名，如果为NULL,说明没有使用索引。key_len：使用的索引长度，越长,说明索引使用得越充分。ref：索引比较的列或常量。rows：预估需要扫描的行数,这个值越小越好。Extra：额外信息。Using index：好！表示使用了索引覆盖,性能极高。Using where：使用了WHERE过滤。Using temporary：坏！使用了临时表，通常需要优化，比如避免GROUP BY或ORDER BY的列没有索引。Using filesort：坏！使用了外部排序，需要优化，比如给ORDER BY的列加上索引。
优化 JOIN 查询
- 确保
JOIN的列上有索引。 - 将小表驱动大表（虽然 MySQL 优化器会尝试做这个，但手动指定更可靠）。
- 避免
JOIN过多的表，建议不超过 3 个。
避免 SELECT *
- 只查询需要的列,减少数据传输量。
- 如果查询的列都包含在索引中，会触发索引覆盖,性能极佳。
合理使用 LIMIT
-
分页查询时，使用
LIMIT offset, size。
（图片来源网络，侵删） -
对于深度分页（如
LIMIT 100000, 10），可以先查询出主键，再用主键JOIN：-- 不好的方式 SELECT * FROM large_table ORDER BY id LIMIT 100000, 10; -- 推荐的方式 SELECT t.* FROM large_table t JOIN (SELECT id FROM large_table ORDER BY id LIMIT 100000, 10) AS tmp ON t.id = tmp.id;
数据库配置优化 (my.cnf / my.ini)
调整 MySQL 的核心参数可以显著提升性能。
查看和设置配置
-- 查看当前运行时变量 SHOW VARIABLES; -- 查看当前运行时状态 SHOW STATUS; -- 临时修改变量（重启后失效） SET GLOBAL innodb_buffer_pool_size = 4G;
关键配置参数
-
innodb_buffer_pool_size：- 作用：InnoDB 存储引擎缓存数据和索引的内存区域，这是最重要的参数。
- 建议：通常设置为系统物理内存的 50%-80%，对于专用数据库服务器，可以设置到 70%-80%。
-
innodb_log_file_size：- 作用：事务日志（Redo Log）的大小，较大的日志可以减少事务提交时的 I/O 刷盘次数,提高写入性能。
- 建议：通常设置为 512MB ~ 4G,过大会增加恢复时间。
-
innodb_log_buffer_size：- 作用：事务日志缓冲区,事务在提交前会先写到这里。
- 建议：默认 16MB，对于高并发写入的场景，可以适当调大到 32MB 或 64MB。
-
max_connections：- 作用：允许的最大并发连接数。
- 建议：根据应用需求设置，过高可能导致内存耗尽，可以通过
SHOW STATUS LIKE 'Max_used_connections';查看历史峰值。
-
query_cache_size：- 作用：缓存查询结果。
- 建议：在 MySQL 8.0 中已被移除，在 5.7 中默认关闭，因为缓存的维护成本高，在写入频繁的场景下命中率极低，弊大于利。强烈建议不要使用。
-
tmp_table_size和max_heap_table_size：- 作用：内存临时表的大小，如果查询中创建了临时表（
EXPLAIN中Extra显示Using temporary），并且这个临时表的大小超过了配置值，MySQL 就会将其转为磁盘上的 MyISAM 表,性能急剧下降。 - 建议：根据业务中的复杂查询情况,适当调大这两个值。
- 作用：内存临时表的大小，如果查询中创建了临时表（
表结构优化
选择合适的存储引擎
- InnoDB：默认引擎，支持事务、行级锁、外键，适用于绝大多数业务场景,特别是高并发读写。
- MyISAM：不支持事务，不支持行级锁，只支持表级锁，读性能好，写性能差，适用于读多写少、对数据一致性要求不高的场景（如报表系统），MySQL 8.0 中已移除。
选择合适的数据类型
- 尽量使用小的数据类型：用
INT而不是BIGINT，用VARCHAR(50)而不是VARCHAR(255)。 - 优先使用精确的数据类型：用
DECIMAL存储金额，而不是FLOAT或DOUBLE。 - 优先使用
NOT NULL：NOT NULL列可以减少索引的存储空间,并且对某些优化器有帮助。
使用 垂直拆分 和 水平拆分
- 垂直拆分：将一张大表的某些列拆分到另一张表中，将用户基本信息表（user_id, name, email）和用户扩展信息表（user_id, address, hobby）分开。
- 水平拆分：将一张大表的数据按某种规则（如
user_id的范围或哈希）拆分到多个结构相同的表中（分库分表）。
服务器硬件优化
- CPU：对于高并发的 OLTP（在线事务处理）系统,需要更多的核心来处理并发请求。
- 内存：尽可能大的内存，用于
innodb_buffer_pool_size。 - 磁盘 I/O：
- 使用 SSD：SSD 的随机读写性能远超 HDD,能极大提升数据库的响应速度。
- 使用 RAID：RAID 10,可以提供良好的读写性能和数据冗余。
- 分离数据和日志：将数据文件、日志文件、临时文件放在不同的物理磁盘上，以减少 I/O 争用。
维护与监控
定期优化表
-- 优化表，消除碎片，对于频繁更新的表尤其重要 OPTIMIZE TABLE your_table_name;
慢查询日志
开启慢查询日志,是定位性能问题的利器。
-- 1. 开启慢查询日志（临时） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 设置超过1秒的查询为慢查询 SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log'; -- 2. 永久生效，需要在 my.cnf 中配置 [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1
使用 mysqldumpslow 分析慢查询日志
-- 分析慢查询日志 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log -- 参数说明 # -s: 排序方式 # t: 按查询时间排序 # c: 按执行次数排序 # l: 按锁定时间排序 # -t: 显示前 N 条
使用 pt-query-digest (Percona Toolkit)
这是一个更强大的慢查询分析工具,可以提供更详细的报告。
# 分析慢查询日志 pt-query-digest /var/log/mysql/mysql-slow.log
MySQL 优化的步骤通常是：
- 开启慢查询日志，收集有问题的 SQL。
- 使用
EXPLAIN分析这些慢 SQL，找出全表扫描、索引失效等问题。 - 添加或修改索引,解决查询效率问题。
- SQL 本身无法优化，考虑调整表结构（垂直/水平拆分）。
- 当所有 SQL 和表都优化后，如果性能仍不满足要求，再调整数据库配置参数。
- 考虑升级硬件或进行架构层面的改造（如读写分离、分库分表）。
这是一个循序渐进、由点到面的过程,需要耐心和持续的分析。
