MySQL 技巧你必须掌握-CSDN博客

一、拒绝SELECT

为什么要写具体字段？
• 减少网络 IO：只拿需要的列；
• 利用覆盖索引：下文第 8 点细讲；
• 避免表结构变更导致程序崩溃。

反面教材

-- 鬼知道以后表里会不会多一个 10M 的 blob
SELECT * FROM orders WHERE user_id = 123;

正面示范

SELECT order_id, amount, status, created_at
FROM orders
WHERE user_id = 123;

二、学会使用EXPLAIN

用 EXPLAIN 给每条慢 SQL 做“B 超”

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

关键列：
• type：最好能达到 range、ref，别出现 ALL；
• key：看有没有用到索引；
• rows：扫描行数越少越好。

三、最左前缀原则

最左前缀原则：联合索引像火车车厢，必须按顺序挂
表结构

CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  age INT,
  city VARCHAR(20),
  KEY idx_age_city (age, city)
);

能用到的查询

SELECT * FROM user WHERE age = 18 AND city = 'sz';
SELECT * FROM user WHERE age = 18;   -- 只会用到 age 部分

用不到的查询

SELECT * FROM user WHERE city = 'sz'; -- 跳过 age，索引失效

四、利用覆盖索引

利用覆盖索引，让查询在索引里就结束
还是上面的表，再加一个查询：

SELECT age, city FROM user WHERE age = 18;

因为 idx_age_city 里已经包含 age、city，MySQL 直接读索引不回表，IO 瞬间减半。

五、防隐式转换

隐式转换是性杀手字段是 varchar，条件传数字：

SELECT * FROM user WHERE phone = 13800138000;

看似没问题，实际 MySQL 会把每一行 phone 先转成数字再比较，索引直接失效。
正确姿势：

SELECT * FROM user WHERE phone = '13800138000';

六、批量INSERT

大批量插入，用多值 INSERT 而不是 N 条单条

-- 千万别循环 1000 次执行下面这句
INSERT INTO log (msg) VALUES ('a');
-- 建议一次怼进去
INSERT INTO log (msg) VALUES ('a'), ('b'), ... ('xxx');

从 10 秒优化到 0.3 秒，亲测有效。

七、ON DUPLICATE

INSERT … ON DUPLICATE KEY UPDATE：存在即更新，不存在则插入
场景：埋点统计表，每天每个用户一条记录。

INSERT INTO stat (user_id, cnt)
VALUES (123, 1)
ON DUPLICATE KEY UPDATE cnt = cnt + 1;

一条 SQL 顶过去两条，不再先 SELECT 再决定 INSERT 还是 UPDATE。

八、UNION替OR

用 UNION ALL 代替 OR，让索引各司其职

-- 低效写法，age 和 city 只能二选一用索引
SELECT * FROM user WHERE age = 18 OR city = 'sz';

-- 改写后，两个子查询各自走最优索引
SELECT * FROM user WHERE age = 18
UNION ALL
SELECT * FROM user WHERE city = 'sz';

UNION ALL 不去重，比 UNION 再省一步。

九、深分页游标

深分页用“延迟游标”而不是 LIMIT 100000, 20
传统深分页

SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

MySQL 要先扫 100020 行，再丢掉前 100000 行。
推荐写法

-- 先记住上一页最大 id
SELECT * FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

性能瞬间回到第一页。

十、JOIN条件别乱

JOIN 的 ON 后面别加过滤条件
错误示范

SELECT * FROM orders o
JOIN user u ON o.user_id = u.id AND u.level = 3;

把 u.level = 3 放到 WHERE 里，让优化器更早过滤：

SELECT * FROM orders o
JOIN user u ON o.user_id = u.id
WHERE u.level = 3;