MySQL 查询优化方案汇总（索引相关）
优化 MySQL 查询性能，索引的正确使用至关重要。本文总结了索引相关的优化方法和注意事项，帮助开发者更高效地管理数据库查询。
类型隐式转换的陷阱
在 MySQL 中，字段的类型隐式转换可能导致索引失效。例如，字段 field1 是 VARCHAR 类型并添加了索引，如果使用 where field1 = 123 进行查询，MySQL 会将 123 视为 INT 类型并进行隐式转换，从而导致索引失效。解决方案是确保类型匹配，在查询中将 123 转为字符串格式（例如 '123'）。
示例
-- 会导致索引失效的查询
SELECT * FROM table WHERE field1 = 123;
-- 类型匹配，避免索引失效
SELECT * FROM table WHERE field1 = '123';
大数据深度分页优化
对于深度分页的查询，传统的 LIMIT 查询可能会变慢。可以通过主键过滤加速分页。
优化示例
-- 传统分页查询，效率较低
SELECT field1, field2 FROM table LIMIT 100000, 10;
-- 优化后的查询，基于主键分页
SELECT field1, field2 FROM table WHERE id > 100000 LIMIT 10;
避免使用 MySQL 函数或表达式
在 WHERE 条件中使用 MySQL 内置函数或表达式会导致索引失效。MySQL 在遇到函数调用或表达式时，通常会进行全表扫描。
示例
-- 导致索引失效的查询
SELECT * FROM table WHERE DATE(date_column) = '2023-01-01';
-- 优化查询，避免函数
SELECT * FROM table WHERE date_column >= '2023-01-01' AND date_column < '2023-01-02';
说明
尽量避免在 WHERE 中使用如 !=、<>、NOT IN、IS NOT NULL 等排除条件，这些操作通常会导致索引失效。
NULL 值的使用
避免字段包含大量 NULL 值，大量的 NULL 值会影响索引的 B+ 树结构，导致索引效率降低。同时，聚合查询如 COUNT() 的结果也可能受到 NULL 值影响。
索引无关优化
1. 减少大字段查询，避免 SELECT *
查询时应只选择必要字段，避免使用 SELECT *，这样不仅减少磁盘 I/O，还能节省网络带宽开销。当仅查询索引字段时，MySQL 可以使用覆盖索引，进一步提升性能。
2. 检查记录是否存在
要检查记录是否存在时，推荐使用 LIMIT 1，避免 COUNT(*) 遍历所有符合条件的记录。
-- 优化查询，检查记录是否存在
SELECT field FROM table WHERE condition LIMIT 1;
冗余优化
在记录访问量时，为了避免使用 COUNT() 来统计每篇文章的浏览量，可以在文章表中添加一个字段记录浏览量。这种优化将复杂度从 O(n) 降至 O(1)。
避免大表与小表 JOIN
大表与小表的关联查询，可以将小表数据加载到内存，使用程序语言内存中的数据进行匹配，避免数据库中直接 JOIN 操作。
索引失效场景总结
以下情况可能导致索引失效：
- 排除条件：使用
NOT IN、IS NOT NULL、<>、!=等排除条件。 - 最左匹配原则：复合索引中，查询必须从最左侧字段开始，否则索引无效。
- 区间查询：在复合索引的左侧字段使用区间查询（如
>、<）会影响右侧字段的索引。 LIKE查询：在LIKE查询的左侧或两侧使用%通配符。- 隐式转换：例如
VARCHAR字段在WHERE中被数字值比较。 OR条件：WHERE中的OR包含非索引列。- 回表查询：在大数据量时对非主键字段排序，会导致频繁的回表操作，增加查询成本。
ORDER BY与索引顺序不一致：如果排序顺序与索引不一致，可能导致索引失效。
适合创建索引的查询场景
以下情况适合创建索引：
- 唯一性约束字段：如唯一 ID 等。
- 高频查询字段：作为
WHERE条件的字段，尤其是=、>、<、IN等操作。 GROUP BY或ORDER BY字段：常被排序或分组的字段。- 高区分度字段：如身份证号等唯一性较强的字段。
JOIN字段：用于表关联的字段（注意类型一致性）。- 联合索引：将最常查询的列放在联合索引的左侧。
不适合创建索引的查询场景
以下场景不建议创建索引：
- 数据量小的表：例如配置表，总类别表等，记录少时索引效果不明显。
- 写多读少的表：写操作较多的表，索引的维护成本较高。
- 低区分度字段：如性别、状态等字段，查询效率提升不明显。
- 包含排除性查询：
<>、!=、NOT IN、IS NOT NULL等操作无法使用索引。
通过以上优化方法，结合索引在 MySQL 查询中的正确应用，能够显著提升数据库查询性能，减少服务器负载。
评论区