MySQL 实用技巧：从入门到性能优化
MySQL 是当今最流行的开源关系型数据库之一。无论是小型项目还是大型互联网应用，掌握一些核心的使用技巧都能显著提升开发效率和系统性能。本文总结了 15 条 MySQL 实用技巧，涵盖查询优化、索引设计、 schema 规范以及日常运维等方面。

1. 永远不要用 SELECT *
只选取需要的列，而不是用 SELECT *。原因有三：

浪费网络带宽和内存（尤其当表包含 TEXT/BLOB 大字段时）

无法使用覆盖索引，可能迫使 MySQL 回表查询

表结构变更后容易引发业务逻辑错误

sql
-- 不推荐
SELECT * FROM users WHERE id = 123;

-- 推荐：只取需要的列
SELECT id, username, email FROM users WHERE id = 123;
2. 学会使用 EXPLAIN 分析执行计划
在慢查询前加上 EXPLAIN，可以查看 MySQL 如何执行 SQL（是否使用索引、扫描行数、是否文件排序等）。重点关注以下字段：

type: const > eq_ref > ref > range > index > ALL（尽量避免 ALL）

possible_keys / key: 实际使用的索引

rows: 估算扫描的行数，越小越好

Extra: Using index（覆盖索引）、Using filesort（需要优化）

sql
EXPLAIN SELECT id, name FROM user WHERE age > 18;
3. 合理使用索引
区分度高的列优先：在 WHERE、JOIN、ORDER BY、GROUP BY 的列上建索引

避免在索引列上使用函数或计算：例如 WHERE DATE(create_time) = '2025-01-01' 会使索引失效，应改为 create_time BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'

联合索引遵循最左前缀原则：例如索引 (a, b, c) 支持 a、a,b、a,b,c 的查询，但不支持单独 b 或 c

避免冗余索引：已有 (a,b) 再建 (a) 是冗余，但 (b,a) 不同

4. 分页优化：不使用 OFFSET 过大
当 OFFSET 很大时，MySQL 需要扫描丢弃前 N 行，效率极低。改用“书签”或“延迟关联”：

sql
-- 低效写法（第100000页）
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化方法1：记住上一页最大id（适用于自增主键且无删除）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化方法2：延迟关联（先取主键再回表）
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t
ON o.id = t.id;
5. 尽量使用 IN 代替多次 OR，但 IN 值不宜太多
WHERE id IN (1,2,3) 通常优于多个 OR，且 MySQL 会对其排序后使用二分查找。但如果 IN 列表包含数百甚至上千个值，会严重影响优化器成本估算，建议拆分或使用临时表连接。

6. 连接（JOIN）优化
小表驱动大表：MySQL 默认使用 Nested Loop Join，将小表作为驱动表（即 EXPLAIN 中第一行）

确保关联列上有索引：被驱动表的关联字段必须建索引，否则会全表扫描

非必要不用 LEFT JOIN：如果业务允许，INNER JOIN 可让优化器有更多调整空间

7. 避免使用 OR 导致索引失效
当 OR 连接的两个条件涉及不同索引时，MySQL 可能放弃使用索引而全表扫描。可以用 UNION 改写：

sql
-- 可能走不全索引
SELECT * FROM user WHERE id = 1 OR name = 'Tom';

-- 改写为 UNION（各自走索引）
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = 'Tom';
8. 批量操作代替逐条循环
在应用代码中避免循环单条插入/更新。一次批量操作能大幅减少网络往返和 SQL 解析开销：

sql
-- 推荐：一条语句插入多行
INSERT INTO logs (user_id, message) VALUES
(1, 'login'), (2, 'pay'), (3, 'logout');

-- 批量更新可用 CASE WHEN 或临时表关联（注意控制事务大小）
UPDATE product SET price = CASE id
WHEN 1 THEN 100
WHEN 2 THEN 200
END WHERE id IN (1,2);
9. 慎用 COUNT(*) 在大表上
COUNT(*) 在 MyISAM 中很快（存储了行数），但在 InnoDB 中需要扫描索引。

若只需要判断是否存在记录，用 EXISTS 或 LIMIT 1 更高效：

sql
-- 不推荐（可能扫描大量行）
SELECT COUNT(*) FROM orders WHERE user_id = 100;

-- 推荐：只需知道有记录即可
SELECT 1 FROM orders WHERE user_id = 100 LIMIT 1;
10. 使用合适的数据类型
能用 INT 不用 BIGINT；能用 TINYINT 不用 INT

存储定长字符串（如手机号、身份证）用 CHAR，变长用 VARCHAR

避免 TEXT/BLOB 过大，实在需要尽量拆到子表

对于 IP 地址，使用 INET_ATON() 和 INET_NTOA() 转为 INT UNSIGNED 存储，既省空间又能范围查询

11. 事务与锁的注意事项
保持事务短小，避免长事务导致锁堆积和 undo 膨胀

显式控制事务的隔离级别（默认 REPEATABLE READ 在 MySQL 中使用间隙锁可能带来并发性能下降，若业务允许可调为 READ COMMITTED）

避免在事务中混用存储引擎（如 MyISAM + InnoDB）

12. 利用 INSERT ... ON DUPLICATE KEY UPDATE 实现 upsert
当存在唯一键冲突时更新，否则插入，一行代码搞定幂等写入：

sql
INSERT INTO user_counter (user_id, count) VALUES (1, 1)
ON DUPLICATE KEY UPDATE count = count + 1;
13. 使用 GROUP BY 时注意排序行为
MySQL 5.7 之前 GROUP BY 会隐式排序（消耗性能），5.7+ 默认不再排序。若不需要排序，可以加上 ORDER BY NULL 避免早期版本的额外开销。另外，对于 GROUP BY 的结果只要去重而不需要聚合函数，语义上用 DISTINCT 更清晰。

14. 分析慢查询日志与监控
开启慢查询日志，设置 long_query_time = 1 秒（或更低）

使用 pt-query-digest 工具分析日志，找出最需要优化的 SQL

查看当前数据库状态：SHOW STATUS LIKE 'Handler%'、SHOW ENGINE INNODB STATUS

15. 定期维护表
对于频繁更新的表，执行 OPTIMIZE TABLE 回收碎片（会锁表，建议低峰期）

检查表是否有不再使用或重复的索引，及时删除

归档历史数据，对大表做分区（PARTITION BY RANGE）或分库分表

总结
MySQL 的性能和易用性高度依赖于正确的使用方式。掌握 EXPLAIN、索引设计原则、避免常见反模式（如 SELECT *、大偏移量分页、逐条操作）是成为 MySQL 高手的必经之路。建议在开发阶段就遵循这些技巧，避免上线后再来修补。当然，每个规则都有其适用场景，最终还需结合业务数据特征和实际指标（慢查询、QPS、IO负载）来做判断。希望本文对你有所帮助，让你的 MySQL 跑得又快又稳！