1. MySQL索引失效的典型场景剖析作为数据库性能优化的核心手段索引的正确使用直接影响查询效率。但在实际工作中我们经常会遇到明明加了索引却还是慢的诡异现象。根据我处理过的数百个生产案例以下五种场景最为常见且最具迷惑性1.1 隐式类型转换导致的索引失效当查询条件的数据类型与索引字段定义类型不一致时MySQL会进行隐式类型转换导致索引失效。例如定义user_id为varchar类型却用数字查询-- 表结构 CREATE TABLE orders ( id INT PRIMARY KEY, user_id VARCHAR(20), INDEX idx_user_id (user_id) ); -- 错误查询索引失效 SELECT * FROM orders WHERE user_id 10086; -- 正确查询使用索引 SELECT * FROM orders WHERE user_id 10086;注意所有字符类型的字段在条件中必须用引号包裹特别是手机号、身份证号等数字形式的字符串。1.2 函数操作导致的索引失效对索引字段使用函数会使优化器无法使用索引。常见场景包括日期处理、字符串截取等-- 表结构 CREATE TABLE logs ( id INT PRIMARY KEY, create_time DATETIME, INDEX idx_create_time (create_time) ); -- 错误查询索引失效 SELECT * FROM logs WHERE DATE(create_time) 2023-01-01; -- 正确查询使用索引 SELECT * FROM logs WHERE create_time 2023-01-01 00:00:00 AND create_time 2023-01-02 00:00:00;1.3 前导模糊查询问题LIKE查询以通配符开头会导致索引失效这是B树索引结构的固有特性-- 表结构 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), INDEX idx_name (name) ); -- 错误查询索引失效 SELECT * FROM products WHERE name LIKE %手机%; -- 优化方案1使用全文索引 ALTER TABLE products ADD FULLTEXT INDEX ft_name (name); SELECT * FROM products WHERE MATCH(name) AGAINST(手机); -- 优化方案2使用覆盖索引后置模糊 SELECT id FROM products WHERE name LIKE 小米%;1.4 不符合最左前缀原则联合索引必须遵循最左前缀匹配原则否则会出现索引断点-- 表结构 CREATE TABLE employees ( id INT PRIMARY KEY, dept_id INT, position VARCHAR(50), salary DECIMAL(10,2), INDEX idx_dept_position (dept_id, position) ); -- 有效使用索引的查询 SELECT * FROM employees WHERE dept_id 3 AND position 工程师; SELECT * FROM employees WHERE dept_id 3; -- 索引失效的查询 SELECT * FROM employees WHERE position 工程师;1.5 OR条件使用不当OR条件可能导致索引失效特别是当OR两边的条件字段不同时-- 表结构 CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50), phone VARCHAR(20), INDEX idx_username (username), INDEX idx_phone (phone) ); -- 索引失效的查询 SELECT * FROM users WHERE username admin OR phone 13800138000; -- 优化方案使用UNION ALL SELECT * FROM users WHERE username admin UNION ALL SELECT * FROM users WHERE phone 13800138000 AND username ! admin;2. 索引失效的诊断方法论2.1 EXPLAIN命令深度解读EXPLAIN是诊断索引问题的瑞士军刀关键字段解析字段含义理想值type访问类型const/eq_ref/ref/rangekey实际使用的索引显示索引名称rows预估扫描行数与实际数据量正相关Extra额外信息Using index(覆盖索引)典型问题模式typeALL全表扫描keyNULL未使用索引ExtraUsing filesort需要额外排序2.2 性能模式监控MySQL 5.7的性能模式提供更细粒度的监控-- 开启性能监控 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %events_statements%; -- 查看慢查询统计 SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;2.3 索引使用统计通过sys库查看索引使用情况SELECT * FROM sys.schema_unused_indexes WHERE object_schema your_database;3. 高级优化策略3.1 索引跳跃扫描(MySQL 8.0)MySQL 8.0引入的Index Skip Scan特性可以突破最左前缀限制-- 表结构 CREATE TABLE orders ( id INT PRIMARY KEY, gender ENUM(M,F), register_date DATE, INDEX idx_gender_date (gender, register_date) ); -- MySQL 8.0可以部分使用索引 SELECT * FROM orders WHERE register_date 2023-01-01;3.2 降序索引优化MySQL 8.0支持真正的降序索引优化ORDER BY ... DESC场景-- 传统索引 CREATE INDEX idx_score ON students(score); -- 降序索引(MySQL 8.0) CREATE INDEX idx_score_desc ON students(score DESC); -- 查询优化 SELECT * FROM students ORDER BY score DESC LIMIT 100;3.3 函数索引(MySQL 8.0)通过函数索引解决计算字段的查询问题-- 创建函数索引 CREATE INDEX idx_name_lower ON employees((LOWER(name))); -- 使用函数索引查询 SELECT * FROM employees WHERE LOWER(name) john;4. 生产环境实战案例4.1 电商订单查询优化原始查询执行时间2.8sSELECT * FROM orders WHERE DATE_FORMAT(create_time,%Y-%m) 2023-01 ORDER BY amount DESC;优化方案添加计算列和函数索引使用覆盖索引减少回表-- 添加计算列 ALTER TABLE orders ADD COLUMN create_month VARCHAR(7) GENERATED ALWAYS AS (DATE_FORMAT(create_time,%Y-%m)) STORED; -- 创建复合索引 CREATE INDEX idx_month_amount ON orders(create_month, amount DESC, id); -- 优化后查询执行时间0.02s SELECT id, user_id, amount FROM orders FORCE INDEX(idx_month_amount) WHERE create_month 2023-01 ORDER BY amount DESC;4.2 社交平台Feed流优化原始分页查询随着offset增大性能急剧下降SELECT * FROM posts WHERE user_id 123 ORDER BY create_time DESC LIMIT 10 OFFSET 10000;优化方案使用游标分页-- 第一页 SELECT * FROM posts WHERE user_id 123 ORDER BY create_time DESC LIMIT 10; -- 后续页假设上一页最后一条create_time为2023-01-01 12:00:00 SELECT * FROM posts WHERE user_id 123 AND create_time 2023-01-01 12:00:00 ORDER BY create_time DESC LIMIT 10;5. 索引维护与管理5.1 索引碎片整理定期检查并优化索引碎片-- 查看碎片率 SELECT table_name, index_name, ROUND(stat_value * innodb_page_size / 1024 / 1024, 2) AS size_mb, stat_description FROM mysql.innodb_index_stats WHERE database_name your_db AND stat_name size; -- 优化表 ALTER TABLE your_table ENGINEInnoDB;5.2 索引使用监控建立索引使用监控机制-- 创建监控表 CREATE TABLE index_usage_monitor ( id INT AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(64), index_name VARCHAR(64), select_count BIGINT DEFAULT 0, last_updated TIMESTAMP ); -- 定期更新统计 INSERT INTO index_usage_monitor (table_name, index_name, select_count) SELECT object_schema, object_name, count_read FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL ON DUPLICATE KEY UPDATE select_count VALUES(select_count), last_updated CURRENT_TIMESTAMP;5.3 索引生命周期管理制定索引管理规范新索引上线前必须通过EXPLAIN验证设置3个月观察期收集使用数据建立季度评审机制清理无用索引重大业务变更时重新评估索引策略经验法则单表索引数量不超过5个联合索引字段不超过3个。超过这个阈值就需要考虑业务拆分或架构调整。