MySQL查询优化的19个实用方法与避坑指南

评估查询性能的指标

在分析查询性能时，主要关注三个指标：

• 执行时间：SQL语句从发出到返回结果的总耗时。
• 检查的行数：引擎实际扫描的数据行数，越少越好。
• 返回的行数：最终呈现给用户的记录数。

第1步：Count查询的优化策略

在使用COUNT()进行统计时，需根据条件选择最优写法。若已知数据分布，可利用子查询差异来减少扫描行数。

案例对比：

# 1. 直接统计（当无WHERE时最快，MySQL内部缓存了行数）
SELECT COUNT(*) FROM world.city;

# 2. 常规WHERE扫描（扫描满足条件的行）
# 当id > 5的数据量巨大时，此方法需扫描大量行
SELECT COUNT(*) FROM city WHERE id > 5;

# 3. 差值法优化（仅扫描满足小条件范围的数据）
# 逻辑：总数 - (id <= 5的数量) = (id > 5的数量)
# 假设id <= 5只有6条数据，此方式仅需扫描这6行加上一次总数查询
SELECT (SELECT COUNT(*) FROM city) - COUNT(*) 
FROM city WHERE id <= 5;

第2步：避免数据类型不兼容

混合使用不相容的数据类型（如INT与FLOAT，CHAR与VARCHAR）会导致优化器放弃索引优化，甚至引发隐式类型转换。建议在应用层保证传入参数与数据库字段类型一致。

案例演示：

-- 创建整型表
CREATE TABLE sss(id INT);
-- 插入浮点数可能导致精度丢失或额外计算开销
INSERT INTO sss VALUES(12.4); -- 结果存入12
INSERT INTO sss VALUES(12.5); -- 结果存入13

第3步：防止索引因运算而失效

规则：不要在WHERE子句中对索引列进行函数计算或数学运算，否则将触发全表扫描。
优化：将运算移至等号右侧。

-- 错误写法：导致索引失效
SELECT * FROM T1 WHERE F1/2 = 100;

-- 正确写法：利用索引
SELECT * FROM T1 WHERE F1 = 100 * 2;

第4步：慎用特定操作符

!= 、<>、IS NULL/NOT NULL、IN、NOT IN往往会导致索引失效。若必须判断是否存在，推荐使用EXISTS替代COUNT(*)，因为后者需要遍历所有行计数，而前者找到一条即可返回。

-- 低效：需要全表计数
IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx') > 0 ...

-- 高效：找到即止
IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx') ...

-- IN vs EXISTS
-- 尽量用EXISTS代替复杂的IN子句

第5步：优先使用数字型字段

包含数值信息的字段应设计为数字型而非字符型。字符串比较需逐字符比对，开销高于数字单次比较，且占用更多存储空间。

第6步：合理使用BETWEEN代替IN

对于连续区间查询，BETWEEN通常比IN (...)更高效，因为它能更好地利用索引范围扫描。

第7步：DISTINCT与GROUP BY的选择

如果能用DISTINCT解决的问题，尽量避免使用GROUP BY，因为聚合操作通常伴随额外的排序和资源消耗。

第8步：避免SELECT INTO带来的锁竞争

SELECT INTO语句会产生排他表锁，阻塞其他用户访问。除非必要，应避免在并发高的生产环境中使用该语句，建议改为普通的CREATE TABLE ... AS SELECT ...或分批处理。

第9步：必要时强制指定索引

当查询优化器未能选择理想索引时，可使用提示词强制指定索引。注意语法可能因版本而异，以下为通用思路：

-- 假设希望走IX_ProcessID索引
SELECT * FROM T1 FORCE INDEX (IX_ProcessID) 
WHERE nextprocess = 1 AND processid IN (8, 32, 45);

注：不同MySQL版本的Force Index语法略有不同，请查阅对应文档。

第10步：消除大型表的顺序存取（OR转UNION）

问题：复杂的OR条件有时会使优化器放弃索引转为顺序存取（全表扫描）。

-- 可能触发全表扫描
SELECT * FROM orders 
WHERE (customer_num=104 AND order_num>1001) OR order_num=1008;

-- 优化方案：拆分查询并使用UNION ALL（不去重，更快）
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION ALL
SELECT * FROM orders WHERE order_num=1008;

第11步：模糊查询必须“打头”

在使用LIKE时，如果通配符%不在最左侧（前缀），则可以利用索引；若在最左侧（如%L%），则无法利用索引。

-- 无效索引（全表扫描）
SELECT * FROM T2 WHERE name LIKE '%L%';
SELECT * FROM T2 WHERE SUBSTRING(name,2,1)='L';

-- 有效索引（前缀匹配）
SELECT * FROM T2 WHERE name LIKE 'L%';

第12步：UPDATE语句的注意事项

修改数据时需格外谨慎：
1. 不要修改主键。
2. 修改VARCHAR字段时，尽量保持长度一致以减少页分裂。
3. 尽量减少对含有触发器或大量索引的列的执行。

第13步：UNION ALL优于UNION

默认情况下，UNION会对结果集进行去重和排序，资源消耗大。若确认数据无重复或不需要排序，务必使用UNION ALL以提升性能。

第14步：字段数据类型精简

1. 拒绝NULL：尽量设置NOT NULL或使用默认值（如0、-1）。NULL需要特殊存储和处理逻辑。
2. 最小化体积：使用能满足需求的更小数据类型（如TINYINT代替INT），减少I/O和内存占用。
3. 定长优先：在有业务允许的情况下，优先使用固定长度字段（CHAR vs VARCHAR）以提高访问速度。

第15步：大数据量LIMIT的高效分页

当OFFSET极大时，LIMIT offset, count会丢弃前面无数行，效率极低。
解决方案：基于覆盖索引先获取ID，再回表查询详情。

-- 低效：偏移量大时极慢
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

-- 高效：先查ID（覆盖索引），再JOIN原文
SELECT f.film_id, f.description 
FROM sakila.film f
INNER JOIN (
    SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5
) AS temp ON f.film_id = temp.film_id;

第16步：批量插入多条数据

一次性插入多行数据比逐行插入快得多，减少了网络往返和事务提交次数。

-- 推荐写法
INSERT INTO person(name,age) VALUES 
('xboy', 14), 
('xgirl', 15), 
('nia', 19);

第17步：索引放置的正确位置

索引应建立在查询条件（WHERE）、排序（ORDER BY）或连接（JOIN）字段上，而非仅仅为了SELECT中的展示字段。盲目在Select栏位建索引是无意义的。

第18步：ORDER BY + LIMIT 的索引优化

针对带排序和分页的查询，索引设计至关重要：

• 单字段排序：直接在Sort字段建索引即可。
• WHERE + ORDER BY：建议使用联合索引 (WhereCol, SortCol)。
• 多字段排序：对于WHERE uid=1 ORDER x,y，索引(uid, x, y)的效果通常优于(x, y, uid)，符合最左前缀原则。

以上就是MySQL查询优化的详细内容，更多关于MySQL性能调优的资料请关注本站其它相关文章！