避免全表扫描！5种MySQL索引失效场景与实战解决方案

原创

小明互联网技术分享社区

发布于 2025-07-17 09:38:52

32100

代码可运行

文章被收录于专栏：MYSQLMYSQL

运行总次数：0

代码可运行

作为数据库核心优化手段，索引设计直接影响查询性能。但在实际场景中，即使创建了索引，仍可能因设计不当导致全表扫描。今天小编通过真实示例解析5种典型索引失效场景，并提供可靠的优化方案。

一、联合索引未遵循最左前缀原则

问题描述

当使用联合索引时，查询条件未包含最左列或未按顺序使用索引列时，将无法触发索引。

典型案例

某电商用户表结构：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(20),
    KEY idx_name_age (name,age)
);

执行范围查询：

SELECT * FROM users WHERE age > 25;

执行计划显示type=ALL（全表扫描）

解决方案

调整查询顺序包含最左列：

SELECT * FROM users WHERE name = '张三' AND age > 25;

修改索引顺序（根据业务频率）：

ALTER TABLE users ADD INDEX idx_age_name(age,name);

二、索引列参与表达式计算

问题描述

对索引列使用函数、算术运算或类型转换时，将导致索引失效。

典型案例

订单表时间查询：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    create_time DATETIME,
    KEY idx_create_time(create_time)
);

-- 错误写法
SELECT * FROM orders 
WHERE DATE_FORMAT(create_time,'%Y-%m-%d') = '2023-08-01';

执行计划显示type=ALL

解决方案

改用范围查询：

SELECT * FROM orders 
WHERE create_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59';

使用生成列（MySQL 5.7+）：

ALTER TABLE orders 
ADD COLUMN create_date DATE AS (DATE(create_time)) STORED,
ADD INDEX idx_create_date(create_date);

三、隐式类型转换导致失效

问题描述

字段类型与查询值类型不一致时，MySQL会进行隐式转换，最终导致索引失效。

典型案例

商品表字符串主键：

CREATE TABLE products (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100)
);

-- 错误写法（数字类型查询）
SELECT * FROM products WHERE id = 10086;

执行计划显示type=ALL

解决方案

需要保持数据表字段类型一致性：

SELECT * FROM products WHERE id = '10086';

执行查询计划变为type=const

四、OR连接非索引字段

问题描述

当OR连接的条件中存在未建立索引的列时，整个查询将退化为全表扫描。

典型案例

员工信息查询：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    KEY idx_name(name)
);

-- 错误写法
SELECT * FROM employees 
WHERE name = '王伟' OR department = '技术部';

执行计划显示type=ALL

解决方案

拆分查询使用UNION：

SELECT * FROM employees WHERE name = '王伟'
UNION ALL
SELECT * FROM employees WHERE department = '技术部';

为department建立联合索引：

ALTER TABLE employees ADD INDEX idx_name_department(name,department);

这种写法更简洁一些

五、低选择性索引被优化器放弃

问题描述

当索引列区分度低于30%时，优化器可能认为全表扫描效率更高。

典型案例

用户性别查询：

CREATE TABLE members (
    id INT PRIMARY KEY,
    gender ENUM('M','F'),
    KEY idx_gender(gender)
);

-- 低效查询
SELECT * FROM members WHERE gender = 'M';

执行计划显示type=ALL

前提条件：表中有大量数据，并且性别分布大致均匀（即大约一半是'M'，另一半是'F'）。

解决方案

组合高区分度字段：

ALTER TABLE members ADD INDEX idx_gender_city(gender,city);

使用覆盖索引：

-- 主要关心的是用户的ID和性别 可以创建如下索引
CREATE INDEX idx_gender_id ON members(gender, id);

SELECT id FROM members WHERE gender = 'M';

覆盖索引：指的是一个索引包含了查询所需的所有列，这样数据库就可以直接从索引中获取数据，而无需访问表中的实际数据行，从而提高了查询效率。

最佳实践总结

场景	检查要点	优化策略
联合索引失效	EXPLAIN的key_len字段	最左前缀匹配原则
表达式计算	WHERE条件是否包含函数	重构查询条件/使用生成列
类型转换	字段类型与值类型一致性	显式类型转换
OR条件失效	查看执行计划using union	改用UNION/建立全覆盖索引
低选择性索引	计算区分度（COUNT(DISTINCT)/总行数）	组合索引/覆盖索引

优化建议：

使用EXPLAIN分析执行计划
定期执行ANALYZE TABLE更新统计信息
使用SHOW INDEX FROM table查看索引基数
避免过度索引，单个表建议不超过5个索引

通过精准的索引设计和规避常见陷阱，可使查询性能提升非常大。建议每次创建索引后，通过EXPLAIN验证是否按预期工作，并持续监控慢查询日志进行优化调整。大家如果还有更多索引失效案例场景欢迎评论区沟通交流！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

腾讯技术创作特训营S14#补给站

mysql

腾讯技术创作特训营S14#新手村

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度