内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

索引是提高MySQL查询性能的重要工具，但在某些情况下，索引可能会失效，导致查询性能下降。了解索引失效的场景和原因，对于优化数据库性能至关重要。本文将详细分析MySQL中索引失效的常见场景，以及如何避免这些问题。 03 学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 索引列上使用函数

-- 索引列上使用函数，导致索引失效
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 优化后：使用范围查询，利用索引
SELECT * FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

2.2 索引列上进行计算

-- 索引列上进行计算，导致索引失效
SELECT * FROM products WHERE price * 0.8 < 1000;

-- 优化后：将计算移到右侧
SELECT * FROM products WHERE price < 1000 / 0.8;

2.3 索引列上使用不等于操作符

-- 使用不等于操作符，可能导致索引失效
SELECT * FROM employees WHERE salary != 10000;

-- 优化后：如果必须使用不等于，可以考虑使用UNION
SELECT * FROM employees WHERE salary < 10000
UNION
SELECT * FROM employees WHERE salary > 10000;

2.4 索引列上使用LIKE操作符（以通配符开头）

-- LIKE以通配符开头，导致索引失效
SELECT * FROM employees WHERE name LIKE '%风哥1号%';

-- 优化后：如果必须使用LIKE，可以考虑使用全文索引
SELECT * FROM employees WHERE MATCH(name) AGAINST('风哥1号');

2.5 索引列上使用IS NULL或IS NOT NULL

-- 使用IS NULL或IS NOT NULL，可能导致索引失效
SELECT * FROM employees WHERE department_id IS NULL;

-- 优化后：考虑在应用层面处理，或者使用默认值代替NULL

2.6 复合索引不遵循最左前缀原则

-- 复合索引为(department_id, salary)，但查询只使用了salary
SELECT * FROM employees WHERE salary > 10000;

-- 优化后：要么添加单独的salary索引，要么在查询中包含department_id
SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;

2.7 索引列类型不匹配

-- 索引列是INT类型，但查询使用了字符串
SELECT * FROM employees WHERE id = '1';

-- 优化后：使用正确的类型
SELECT * FROM employees WHERE id = 1;

2.8 索引列上使用OR操作符

-- 使用OR操作符，可能导致索引失效
SELECT * FROM employees WHERE department_id = 1 OR salary > 10000;

-- 优化后：使用UNION
SELECT * FROM employees WHERE department_id = 1
UNION
SELECT * FROM employees WHERE salary > 10000;

Part03-生产环境项目实施方案

3.1 场景1：函数操作导致索引失效

SQL语句	是否使用索引	原因	优化方案
SELECT * FROM employees WHERE YEAR(hire_date) = 2023	否	索引列上使用了YEAR函数	使用范围查询：WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31'
SELECT * FROM employees WHERE LOWER(name) = 'zhangsan'	否	索引列上使用了LOWER函数	存储数据时统一大小写，或使用大小写不敏感的排序规则
SELECT * FROM employees WHERE SUBSTRING(name, 1, 2) = '张'	否	索引列上使用了SUBSTRING函数	使用前缀索引或全文索引

3.2 场景2：计算操作导致索引失效

SQL语句	是否使用索引	原因	优化方案
SELECT * FROM products WHERE price * 0.8 < 1000	否	索引列上进行了计算	将计算移到右侧：WHERE price < 1000 / 0.8
SELECT * FROM employees WHERE salary + 1000 > 10000	否	索引列上进行了计算	将计算移到右侧：WHERE salary > 9000

3.3 场景3：LIKE操作导致索引失效

SQL语句	是否使用索引	原因	优化方案
SELECT * FROM employees WHERE name LIKE '%风哥1号%'	否	LIKE以通配符开头	使用全文索引或应用层面处理
SELECT * FROM employees WHERE name LIKE '风哥1号%'	是	LIKE以固定字符开头	无需优化

3.4 场景4：复合索引不遵循最左前缀原则

SQL语句	是否使用索引	原因	优化方案
SELECT * FROM employees WHERE department_id = 1 AND salary > 10000	是	遵循最左前缀原则	无需优化
SELECT * FROM employees WHERE department_id = 1	是	遵循最左前缀原则	无需优化
SELECT * FROM employees WHERE salary > 10000	否	不遵循最左前缀原则	添加单独的salary索引

3.5 场景5：OR操作导致索引失效

SQL语句	是否使用索引	原因	优化方案
SELECT * FROM employees WHERE department_id = 1 OR salary > 10000	否	OR操作可能导致索引失效	使用UNION
SELECT * FROM employees WHERE department_id = 1 OR department_id = 2	是	OR操作的列是同一个索引列	无需优化，或使用IN：WHERE department_id IN (1, 2)

Part04-生产案例与实战讲解

4.1 避免在索引列上使用函数

• 将函数操作转换为范围查询
• 在应用层面处理函数操作
• 考虑使用生成列（Generated Columns）

4.2 避免在索引列上进行计算

• 将计算移到查询条件的右侧
• 在应用层面进行计算
• 考虑使用生成列

4.3 合理使用LIKE操作符

• 尽量使用前缀匹配（如'风哥1号%'）
• 对于全匹配，考虑使用全文索引
• 避免使用'%风哥1号%'这样的模式

4.4 遵循复合索引的最左前缀原则

• 根据查询顺序设计复合索引
• 确保查询条件包含复合索引的最左列
• 对于频繁使用的单列查询，考虑添加单独的索引

4.5 确保索引列类型匹配

• 使用正确的数据类型进行查询
• 避免隐式类型转换

4.6 合理使用OR操作符

• 对于不同列的OR操作，考虑使用UNION
• 对于同一列的OR操作，使用IN代替

Part05-风哥经验总结与分享

5.1 使用EXPLAIN分析执行计划

-- 分析查询执行计划
EXPLAIN SELECT * FROM employees WHERE YEAR(hire_date) = 2023;

-- 查看key列是否有值，如果为NULL则表示没有使用索引

5.2 查看慢查询日志

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志中的SQL语句，分析是否使用了索引

5.3 监控索引使用情况

-- 启用性能模式
SET GLOBAL performance_schema = ON;

-- 查看索引使用情况
SELECT 
    object_schema, 
    object_name, 
    index_name, 
    count_star AS access_count 
FROM 
    performance_schema.table_io_waits_summary_by_index_usage 
WHERE 
    object_schema = 'your_database' 
ORDER BY 
    access_count DESC;

6. 实际案例分析

6.1 案例1：函数操作导致索引失效

-- 原查询：索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2023-01-01';

-- 优化后：使用索引
SELECT * FROM orders WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

6.2 案例2：LIKE操作导致索引失效

-- 原查询：索引失效
SELECT * FROM products WHERE name LIKE '%手机%';

-- 优化后：使用全文索引
ALTER TABLE products ADD FULLTEXT INDEX idx_products_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

6.3 案例3：复合索引不遵循最左前缀原则

-- 原查询：索引失效
SELECT * FROM employees WHERE salary > 10000;

-- 优化后：添加单独的索引
CREATE INDEX idx_employees_salary ON employees(salary);
SELECT * FROM employees WHERE salary > 10000;

6.4 案例4：OR操作导致索引失效

-- 原查询：索引失效
SELECT * FROM employees WHERE department_id = 1 OR salary > 10000;

-- 优化后：使用UNION
SELECT * FROM employees WHERE department_id = 1
UNION
SELECT * FROM employees WHERE salary > 10000;

7. 索引失效的特殊情况

7.1 索引覆盖

-- 即使使用了函数，如果查询的列都在索引中，也会使用索引（索引覆盖）
-- 假设有索引(idx_name) on employees(name)
SELECT name FROM employees WHERE LOWER(name) = 'zhangsan';
-- 虽然使用了LOWER函数，但由于只查询name列，会使用索引覆盖

7.2 索引统计信息不准确

-- 索引统计信息不准确可能导致优化器选择不使用索引
-- 更新索引统计信息
ANALYZE TABLE employees;

7.3 数据分布不均匀

-- 如果数据分布不均匀，优化器可能认为全表扫描比使用索引更快
-- 例如，某列90%的值都是相同的，优化器可能选择全表扫描

8. 总结

索引失效是MySQL查询性能下降的常见原因之一。本文详细分析了MySQL中索引失效的各种场景，包括函数操作、计算操作、LIKE操作、复合索引使用不当等。通过了解这些场景和原因，我们可以采取相应的措施来避免索引失效，提高查询性能。 04 风哥提示：

在实际应用中，应该注意以下几点： 05更多学习教程公众号风哥教程itpux_com

• 避免在索引列上使用函数和计算操作
• 合理使用LIKE操作符，避免以通配符开头
• 遵循复合索引的最左前缀原则
• 确保索引列类型匹配
• 合理使用OR操作符，必要时使用UNION代替
• 定期使用EXPLAIN分析查询执行计划
• 监控索引使用情况，及时调整索引结构

通过遵循这些最佳实践，可以有效避免索引失效，提高MySQL查询性能，确保应用系统的高效运行。 06 from mysql视频:www.itpux.com