MySQL 性能优化:索引、慢查询与常用参数

数据库慢,往往不是机器不够快,而是查询没走对路、参数没调对档。本文以 Ubuntu/Debian 上的 MySQL 8.0 为例,带你从索引、执行计划、慢查询日志到几个关键参数,建立一条可落地的排查路径。所有做法请在你自己的服务器上按真实压力验证,别照抄网上的"最佳配置"。

先建对索引

索引是加速查询最直接的手段。原则很简单:WHERE、JOIN、ORDER BY 里频繁出现的列,才值得建索引。

-- 单列索引:按用户查订单
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 复合索引:遵循「最左前缀」,列顺序要匹配查询
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

复合索引 (userid, status) 能服务 WHERE userid = ? 和 WHERE userid = ? AND status = ?,但单独按 status 查用不上它。索引不是越多越好:每个索引都会拖慢写入、占用磁盘,还可能让优化器选错。

常见踩坑

• 在索引列上套函数或运算会失效:WHERE DATE(createdat) = '2026-07-14' 用不上索引,改成范围查询 WHERE createdat >= '2026-07-14' AND createdat < '2026-07-15'。
• 隐式类型转换同样失效:phone 是字符串却写 WHERE phone = 138xxxx(数字)。
• 前导通配 LIKE '%abc' 无法走索引。

用 EXPLAIN 看执行计划

改索引前,先让 MySQL 告诉你它打算怎么查:

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

重点看这几列:

• type:ALL 表示全表扫描(最需警惕),ref/range 通常正常,const/eqref 最优。
• key:实际用到的索引,为 NULL 说明没走索引。
• rows:预估扫描行数,越小越好。
• Extra:出现 Using filesort、Using temporary 往往意味着排序或分组开销大。

想看真实执行耗时,用 EXPLAIN ANALYZE(会真正执行语句)。

开慢查询日志定位慢 SQL

不知道哪条 SQL 慢,就让 MySQL 记下来。可在线动态开启,免重启:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;      -- 超过 1 秒即记录
SET GLOBAL log_queries_not_using_indexes = 'ON';

要持久化,写进 /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

日志用 mysqldumpslow -s t /var/log/mysql/slow.log 按耗时聚合,一眼看出最该优化的语句。注意 logqueriesnotusingindexes 在生产可能刷爆日志,排查完记得关。

几个关键参数

别背模板,理解含义再按内存和连接数调:

• innodbbufferpoolsize:InnoDB 的数据/索引缓存,是最重要的参数。专用数据库机器可给到物理内存的 50%–70%;若和应用共用服务器,务必留足余量,别把机器压到 OOM。
• maxconnections:最大并发连接数,默认 151。调高前先确认内存扛得住——每个连接都要吃内存,盲目调大反而更容易被打垮。用连接池通常比堆连接数更有效。

[mysqld]
innodb_buffer_pool_size = 4G
max_connections = 200

改完重启 sudo systemctl restart mysql,再用 SHOW GLOBAL STATUS 和慢日志观察实际效果。

小结

优化的正确顺序是:用 EXPLAIN 和慢查询日志找到瓶颈 → 为高频查询补合适索引、消灭全表扫描 → 最后才按真实内存与并发微调参数。每一步都用你自己服务器的真实数据验证,先量再调,别照抄任何"通用配置"。