网站建设与管理课程总结兴平市住房和城乡建设局门户网站

网站建设与管理课程总结,兴平市住房和城乡建设局门户网站,中国建筑公司排名前20,平面设计图100张索引失效#xff1a;场景、排查、解决方案全解析你想了解 MySQL 中索引失效的场景、排查方法和解决思路#xff0c;这是优化数据库查询性能的核心问题 —— 索引失效会让本应快速的索引查询退化为全表扫描#xff0c;性能暴跌。下面我会从「失效场景」「排查方法」「解决策略…索引失效场景、排查、解决方案全解析你想了解 MySQL 中索引失效的场景、排查方法和解决思路这是优化数据库查询性能的核心问题 —— 索引失效会让本应快速的索引查询退化为全表扫描性能暴跌。下面我会从「失效场景」「排查方法」「解决策略」三个维度结合实战案例详细讲解让你能精准定位并解决问题。一、索引失效的核心场景附案例索引失效的本质是MySQL 优化器判断「走索引的成本 全表扫描的成本」因此放弃使用索引。以下是最常见的失效场景按出现频率排序1. 索引字段被函数 / 表达式操作最易踩坑对索引字段直接做函数、算术运算、字符串拼接等操作会导致优化器无法使用索引必须逐行计算后匹配。-- 表结构user(id, name, create_time)create_time有索引 -- ❌ 失效对索引字段create_time用函数 SELECT * FROM user WHERE DATE(create_time) 2025-12-27; -- ❌ 失效对索引字段id做算术运算 SELECT * FROM user WHERE id 1 100; -- ✅ 有效将函数/运算移到常量侧让索引字段“裸奔” SELECT * FROM user WHERE create_time 2025-12-27 00:00:00 AND create_time 2025-12-28 00:00:00; SELECT * FROM user WHERE id 99;2. 使用模糊查询%xxx左模糊LIKE查询中%放在开头会导致索引失效索引是有序的左模糊无法利用索引的有序性xxx%右模糊则有效。-- 表结构user(name)name有索引 -- ❌ 失效左模糊 %张三 SELECT * FROM user WHERE name LIKE %张三; -- ✅ 有效右模糊 张三% SELECT * FROM user WHERE name LIKE 张三%; -- ✅ 折中方案左模糊必须用用全文索引替代普通索引 ALTER TABLE user ADD FULLTEXT INDEX idx_name_ft(name); SELECT * FROM user WHERE MATCH(name) AGAINST(张三 IN NATURAL LANGUAGE MODE);3. 索引字段类型不匹配隐式转换比如索引字段是int类型但查询时传入字符串或varchar字段用数字查询都会触发隐式转换导致索引失效。-- 表结构user(id int, phone varchar(11))phone有索引 -- ❌ 失效phone是varchar用数字13800138000查询隐式转换 SELECT * FROM user WHERE phone 13800138000; -- ✅ 有效类型匹配用字符串查询 SELECT * FROM user WHERE phone 13800138000;4. 联合索引不满足 “最左前缀原则”联合索引a,b,c的生效顺序是「a → b → c」必须从最左列开始使用跳过中间列会导致后续列的索引失效。-- 表结构user(a,b,c)联合索引 idx_a_b_c(a,b,c) -- ✅ 有效使用最左列a SELECT * FROM user WHERE a 1; -- ✅ 有效使用a b连续最左 SELECT * FROM user WHERE a 1 AND b 2; -- ❌ 失效跳过a直接用b破坏最左前缀 SELECT * FROM user WHERE b 2; -- ❌ 失效a c跳过bc的索引失效 SELECT * FROM user WHERE a 1 AND c 3;5. 使用OR连接非索引字段OR两侧只要有一个字段没有索引整个查询就会失效优化器无法同时利用索引和全表扫描若两侧都有索引则有效。-- 表结构user(id, name)id有索引name无索引 -- ❌ 失效OR连接索引字段id和非索引字段name SELECT * FROM user WHERE id 1 OR name 张三; -- ✅ 有效要么给name加索引要么拆分为两个查询 -- 方案1给name加索引 CREATE INDEX idx_name ON user(name); SELECT * FROM user WHERE id 1 OR name 张三; -- 方案2拆分查询UNION去重 SELECT * FROM user WHERE id 1 UNION SELECT * FROM user WHERE name 张三;6. 查询结果集过大优化器放弃索引如果索引字段的匹配行数占表总行数的 30% 以上阈值可调整优化器会认为「回表的成本 全表扫描」主动放弃索引。-- 表结构user(age)age有索引表中90%数据都是age20 -- ❌ 失效匹配行数太多优化器走全表扫描 SELECT * FROM user WHERE age 20; -- ✅ 解决若业务必须用可强制走索引谨慎使用需测试 SELECT * FROM user FORCE INDEX(idx_age) WHERE age 20;7. 其他常见失效场景场景失效 SQL 示例修复思路NOT IN/!/SELECT * FROM user WHERE id NOT IN (1,2)替换为LEFT JOIN或NOT EXISTSIS NULL/IS NOT NULLSELECT * FROM user WHERE name IS NULL若字段无默认值可加索引或用COALESCE排序字段与索引不一致SELECT * FROM user WHERE a1 ORDER BY c调整联合索引为 (a,c)覆盖排序字段二、索引失效的排查方法实操步骤排查的核心是用EXPLAIN分析执行计划判断索引是否被使用。步骤 1执行EXPLAIN查看执行计划-- 对要排查的SQL加EXPLAIN EXPLAIN SELECT * FROM user WHERE DATE(create_time) 2025-12-27;步骤 2重点关注 4 个字段核心判断依据字段含义失效判断标准type访问类型若为ALL全表扫描说明索引失效理想值是ref/range/refkey实际使用的索引若为NULL说明未使用任何索引key_len索引使用的长度若为0说明索引未生效Extra额外信息若出现Using filesort/Using temporary可能是索引设计问题步骤 3案例分析实战排查-- 排查SQLSELECT * FROM user WHERE name LIKE %张三; EXPLAIN SELECT * FROM user WHERE name LIKE %张三;执行结果关键值type: ALL全表扫描key: NULL未用索引结论索引失效原因是左模糊查询。三、索引失效的解决策略按场景对应策略 1避免对索引字段做函数 / 运算操作核心原则让索引字段 “裸奔”把函数 / 运算移到常量侧。反例WHERE DATE(create_time) 2025-12-27正例WHERE create_time BETWEEN 2025-12-27 00:00:00 AND 2025-12-27 23:59:59策略 2优化模糊查询右模糊xxx%保留普通索引直接使用左模糊 / 全模糊%xxx%短文本用全文索引FULLTEXT长文本用 Elasticsearch 替代 MySQL 模糊查询。策略 3保证字段类型匹配查询值的类型必须和索引字段类型一致避免隐式转换varchar字段查询时加引号WHERE phone 13800138000int字段查询时用数字WHERE id 100。策略 4遵循联合索引 “最左前缀原则”设计联合索引时把查询频率最高的字段放在最左侧查询时必须从最左列开始使用比如联合索引 (a,b,c)查询条件至少包含 a。策略 5优化OR/NOT IN等操作OR要么给所有字段加索引要么拆分为UNION查询NOT IN替换为LEFT JOIN ... IS NULL或NOT EXISTS-- 反例NOT IN 失效 SELECT * FROM user WHERE id NOT IN (1,2,3); -- 正例LEFT JOIN 替代 SELECT u.* FROM user u LEFT JOIN (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3) t ON u.id t.id WHERE t.id IS NULL;策略 6强制索引谨慎使用如果优化器误判比如小表数据量变大后未更新统计信息可强制走索引-- FORCE INDEX 指定要使用的索引 SELECT * FROM user FORCE INDEX(idx_age) WHERE age 20;策略 7更新表的统计信息MySQL 优化器依赖统计信息判断索引成本若统计信息过时会导致误判-- ANALYZE TABLE 更新统计信息 ANALYZE TABLE user;总结索引失效核心原因优化器判断走索引成本更高常见触发点是函数操作、类型不匹配、破坏最左前缀原则等排查核心方法用EXPLAIN分析执行计划重点看type是否为 ALL、key是否为 NULL解决核心思路让索引字段 “裸奔”无函数 / 运算、遵循索引设计规则最左前缀、类型匹配、优化查询语句替换 OR/NOT IN。