2026/9/10 18:29:23
FEATURED · 精选文章 
1. 为什么索引反而让查询变慢刚接触MySQL索引的开发人员经常会遇到一个反直觉的现象明明已经建立了索引查询速度却比没加索引时更慢了。这种情况往往出现在数据量中等百万级以下的表上我见过不少团队为此通宵排查问题。1.1 优化器的成本估算误区MySQL优化器在选择执行计划时会基于统计信息计算不同方案的成本。当出现以下情况时优化器可能错误选择全表扫描索引区分度过低如性别字段只有M/F两种值统计信息过期特别是频繁更新的表查询需要访问超过30%的数据行重要提示执行ANALYZE TABLE your_table可以更新统计信息这在数据分布发生重大变化后特别有用。1.2 索引维护的隐藏成本每次DML操作都会触发索引维护这个成本常被低估。我曾处理过一个案例批量插入10万条数据时有索引的情况下耗时是从无索引时的3.7倍。具体影响因素包括索引数量每多一个索引写入开销增加约10-15%索引类型自增主键的维护成本最低随机UUID最高索引列宽度特别是对于TEXT/VARCHAR等变长字段1.3 回表查询的致命陷阱最典型的性能杀手是回表操作。当使用非聚簇索引时如果SELECT的字段不在索引中就需要回到主键索引获取完整数据。一个真实案例-- 假设在user_name上有普通索引 SELECT * FROM users WHERE user_name LIKE 张%;这个查询会先走user_name索引找到所有姓张的用户ID再回主键索引获取完整记录。当匹配结果超过5%时优化器可能直接选择全表扫描。2. 索引失效的六大经典场景2.1 最左前缀原则的边界情况虽然大家都知道联合索引要遵循最左前缀原则但有些隐式失效场景很容易忽略-- 假设有联合索引 (dept_id, position, status) SELECT * FROM employees WHERE position 工程师 AND status 1; -- 这个查询完全用不到索引因为跳过了dept_id更隐蔽的是范围查询后的列失效-- 只有dept_id和position会用到索引 SELECT * FROM employees WHERE dept_id 10 AND position 助理 AND status 1;2.2 隐式类型转换的坑当比较操作涉及类型转换时索引会失效。常见于字符串字段用数字查询反之亦然字符集不匹配的JOIN操作ENUM类型与字符串混用-- user_id是varchar类型但用数字查询 SELECT * FROM users WHERE user_id 10086; -- 实际执行的是SELECT * FROM users WHERE CAST(user_id AS SIGNED) 100862.3 函数操作的魔法失效任何对索引列使用函数或运算都会导致失效-- 这些查询都用不到索引 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; SELECT * FROM products WHERE price 10 100; SELECT * FROM articles WHERE SUBSTRING(title, 1, 3) MySQL;2.4 OR条件的联合失效开发中经常用OR连接多个条件但要注意-- 假设name和phone都有独立索引 SELECT * FROM contacts WHERE name 张三 OR phone 13800138000; -- 这个查询通常不会使用任何索引改用UNION ALL往往有更好效果SELECT * FROM contacts WHERE name 张三 UNION ALL SELECT * FROM contacts WHERE phone 13800138000;2.5 不等于(!/)的全面扫描不等于操作几乎总是导致全表扫描-- 这个查询用不到status上的索引 SELECT * FROM orders WHERE status ! completed;2.6 LIKE通配符开头的陷阱-- 只有这种前缀匹配能用索引 SELECT * FROM products WHERE name LIKE Apple%; -- 这些都用不到索引 SELECT * FROM products WHERE name LIKE %Pro; SELECT * FROM products WHERE name LIKE %Mac%;3. 高性能索引设计实战3.1 三星索引设计原则理想的索引应该满足第一星WHERE条件中的列都包含在索引中减少扫描范围第二星ORDER BY/GROUP BY的列顺序与索引一致避免排序第三星SELECT的列都包含在索引中避免回表示例对于查询SELECT user_name, email FROM users WHERE city北京 ORDER BY register_time DESC;最优索引是(city, register_time, user_name, email)3.2 覆盖索引的妙用覆盖索引是指索引包含查询需要的所有字段可以避免回表-- 假设有索引 (category, price) SELECT product_id, price FROM products WHERE category 电子产品; -- 这个查询只需要扫描索引不需要访问表数据3.3 前缀索引的平衡术对于长字符串字段可以使用前缀索引ALTER TABLE articles ADD INDEX idx_title(title(20));选择合适的前缀长度很关键计算完整列的选择性SELECT COUNT(DISTINCT title)/COUNT(*) FROM articles;测试不同前缀长度的选择性SELECT COUNT(DISTINCT LEFT(title, 10))/COUNT(*) as sel10, COUNT(DISTINCT LEFT(title, 20))/COUNT(*) as sel20, COUNT(DISTINCT LEFT(title, 30))/COUNT(*) as sel30 FROM articles;选择达到90%以上完整选择性的最小长度3.4 索引合并的真相MySQL有时会使用Index Merge优化但实际效果往往不如复合索引-- 假设name和age都有独立索引 EXPLAIN SELECT * FROM users WHERE name张三 OR age30; -- 可能会看到Using union(idx_name,idx_age)这种情况下建立(name, age)的联合索引通常更好。4. 索引监控与优化实战4.1 识别无用索引通过performance_schema查看索引使用情况SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_READ FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_READ DESC;COUNT_READ为0的索引可能是冗余的。4.2 强制索引的使用场景当优化器选择错误时可以用FORCE INDEXSELECT * FROM orders FORCE INDEX(idx_status_date) WHERE status processing AND create_date 2023-01-01;但要注意数据分布变化后可能适得其反。4.3 索引碎片整理频繁更新的表会出现索引碎片定期优化-- InnoDB表的优化方式 ALTER TABLE your_table ENGINEInnoDB; -- 或使用pt-index-usage工具4.4 EXPLAIN的深度解读掌握EXPLAIN的关键字段type列从优到差system const eq_ref ref range index ALLExtra列重要提示Using filesort需要额外排序Using temporary使用了临时表Using index使用了覆盖索引4.5 索引跳跃扫描优化MySQL 8.0支持跳跃扫描-- 假设有索引(gender, age)gender只有M/F SELECT * FROM people WHERE age 30; -- 8.0可以跳跃扫描不同gender值5. 特殊场景的索引策略5.1 JSON字段的索引技巧对JSON字段建立生成列索引ALTER TABLE products ADD COLUMN price_value DECIMAL(10,2) GENERATED ALWAYS AS (JSON_EXTRACT(attributes, $.price)) STORED, ADD INDEX idx_price(price_value);5.2 地理空间索引的应用对于地理位置查询-- 创建空间索引 ALTER TABLE locations ADD SPATIAL INDEX(pt); -- 查询5公里范围内的点 SELECT * FROM locations WHERE ST_Distance_Sphere(pt, POINT(116.404, 39.915)) 5000;5.3 全文索引的优化替代LIKE的高效方案-- 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_idx(title, content); -- 使用布尔搜索 SELECT * FROM articles WHERE MATCH(title, content) AGAINST(MySQL -Oracle IN BOOLEAN MODE);5.4 分区表的索引策略分区表上的索引有两种类型全局索引整个表一个B树ALTER TABLE sales ADD INDEX idx_date(sale_date);本地索引每个分区独立的B树ALTER TABLE sales ADD INDEX idx_local(order_id);全局索引适合范围查询本地索引适合分区键之外的查询。
MySQL索引优化：为何索引反而降低查询性能？
1. 为什么索引反而让查询变慢刚接触MySQL索引的开发人员经常会遇到一个反直觉的现象明明已经建立了索引查询速度却比没加索引时更慢了。这种情况往往出现在数据量中等百万级以下的表上我见过不少团队为此通宵排查问题。1.1 优化器的成本估算误区MySQL优化器在选择执行计划时会基于统计信息计算不同方案的成本。当出现以下情况时优化器可能错误选择全表扫描索引区分度过低如性别字段只有M/F两种值统计信息过期特别是频繁更新的表查询需要访问超过30%的数据行重要提示执行ANALYZE TABLE your_table可以更新统计信息这在数据分布发生重大变化后特别有用。1.2 索引维护的隐藏成本每次DML操作都会触发索引维护这个成本常被低估。我曾处理过一个案例批量插入10万条数据时有索引的情况下耗时是从无索引时的3.7倍。具体影响因素包括索引数量每多一个索引写入开销增加约10-15%索引类型自增主键的维护成本最低随机UUID最高索引列宽度特别是对于TEXT/VARCHAR等变长字段1.3 回表查询的致命陷阱最典型的性能杀手是回表操作。当使用非聚簇索引时如果SELECT的字段不在索引中就需要回到主键索引获取完整数据。一个真实案例-- 假设在user_name上有普通索引 SELECT * FROM users WHERE user_name LIKE 张%;这个查询会先走user_name索引找到所有姓张的用户ID再回主键索引获取完整记录。当匹配结果超过5%时优化器可能直接选择全表扫描。2. 索引失效的六大经典场景2.1 最左前缀原则的边界情况虽然大家都知道联合索引要遵循最左前缀原则但有些隐式失效场景很容易忽略-- 假设有联合索引 (dept_id, position, status) SELECT * FROM employees WHERE position 工程师 AND status 1; -- 这个查询完全用不到索引因为跳过了dept_id更隐蔽的是范围查询后的列失效-- 只有dept_id和position会用到索引 SELECT * FROM employees WHERE dept_id 10 AND position 助理 AND status 1;2.2 隐式类型转换的坑当比较操作涉及类型转换时索引会失效。常见于字符串字段用数字查询反之亦然字符集不匹配的JOIN操作ENUM类型与字符串混用-- user_id是varchar类型但用数字查询 SELECT * FROM users WHERE user_id 10086; -- 实际执行的是SELECT * FROM users WHERE CAST(user_id AS SIGNED) 100862.3 函数操作的魔法失效任何对索引列使用函数或运算都会导致失效-- 这些查询都用不到索引 SELECT * FROM orders WHERE DATE(create_time) 2023-01-01; SELECT * FROM products WHERE price 10 100; SELECT * FROM articles WHERE SUBSTRING(title, 1, 3) MySQL;2.4 OR条件的联合失效开发中经常用OR连接多个条件但要注意-- 假设name和phone都有独立索引 SELECT * FROM contacts WHERE name 张三 OR phone 13800138000; -- 这个查询通常不会使用任何索引改用UNION ALL往往有更好效果SELECT * FROM contacts WHERE name 张三 UNION ALL SELECT * FROM contacts WHERE phone 13800138000;2.5 不等于(!/)的全面扫描不等于操作几乎总是导致全表扫描-- 这个查询用不到status上的索引 SELECT * FROM orders WHERE status ! completed;2.6 LIKE通配符开头的陷阱-- 只有这种前缀匹配能用索引 SELECT * FROM products WHERE name LIKE Apple%; -- 这些都用不到索引 SELECT * FROM products WHERE name LIKE %Pro; SELECT * FROM products WHERE name LIKE %Mac%;3. 高性能索引设计实战3.1 三星索引设计原则理想的索引应该满足第一星WHERE条件中的列都包含在索引中减少扫描范围第二星ORDER BY/GROUP BY的列顺序与索引一致避免排序第三星SELECT的列都包含在索引中避免回表示例对于查询SELECT user_name, email FROM users WHERE city北京 ORDER BY register_time DESC;最优索引是(city, register_time, user_name, email)3.2 覆盖索引的妙用覆盖索引是指索引包含查询需要的所有字段可以避免回表-- 假设有索引 (category, price) SELECT product_id, price FROM products WHERE category 电子产品; -- 这个查询只需要扫描索引不需要访问表数据3.3 前缀索引的平衡术对于长字符串字段可以使用前缀索引ALTER TABLE articles ADD INDEX idx_title(title(20));选择合适的前缀长度很关键计算完整列的选择性SELECT COUNT(DISTINCT title)/COUNT(*) FROM articles;测试不同前缀长度的选择性SELECT COUNT(DISTINCT LEFT(title, 10))/COUNT(*) as sel10, COUNT(DISTINCT LEFT(title, 20))/COUNT(*) as sel20, COUNT(DISTINCT LEFT(title, 30))/COUNT(*) as sel30 FROM articles;选择达到90%以上完整选择性的最小长度3.4 索引合并的真相MySQL有时会使用Index Merge优化但实际效果往往不如复合索引-- 假设name和age都有独立索引 EXPLAIN SELECT * FROM users WHERE name张三 OR age30; -- 可能会看到Using union(idx_name,idx_age)这种情况下建立(name, age)的联合索引通常更好。4. 索引监控与优化实战4.1 识别无用索引通过performance_schema查看索引使用情况SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_READ FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_READ DESC;COUNT_READ为0的索引可能是冗余的。4.2 强制索引的使用场景当优化器选择错误时可以用FORCE INDEXSELECT * FROM orders FORCE INDEX(idx_status_date) WHERE status processing AND create_date 2023-01-01;但要注意数据分布变化后可能适得其反。4.3 索引碎片整理频繁更新的表会出现索引碎片定期优化-- InnoDB表的优化方式 ALTER TABLE your_table ENGINEInnoDB; -- 或使用pt-index-usage工具4.4 EXPLAIN的深度解读掌握EXPLAIN的关键字段type列从优到差system const eq_ref ref range index ALLExtra列重要提示Using filesort需要额外排序Using temporary使用了临时表Using index使用了覆盖索引4.5 索引跳跃扫描优化MySQL 8.0支持跳跃扫描-- 假设有索引(gender, age)gender只有M/F SELECT * FROM people WHERE age 30; -- 8.0可以跳跃扫描不同gender值5. 特殊场景的索引策略5.1 JSON字段的索引技巧对JSON字段建立生成列索引ALTER TABLE products ADD COLUMN price_value DECIMAL(10,2) GENERATED ALWAYS AS (JSON_EXTRACT(attributes, $.price)) STORED, ADD INDEX idx_price(price_value);5.2 地理空间索引的应用对于地理位置查询-- 创建空间索引 ALTER TABLE locations ADD SPATIAL INDEX(pt); -- 查询5公里范围内的点 SELECT * FROM locations WHERE ST_Distance_Sphere(pt, POINT(116.404, 39.915)) 5000;5.3 全文索引的优化替代LIKE的高效方案-- 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_idx(title, content); -- 使用布尔搜索 SELECT * FROM articles WHERE MATCH(title, content) AGAINST(MySQL -Oracle IN BOOLEAN MODE);5.4 分区表的索引策略分区表上的索引有两种类型全局索引整个表一个B树ALTER TABLE sales ADD INDEX idx_date(sale_date);本地索引每个分区独立的B树ALTER TABLE sales ADD INDEX idx_local(order_id);全局索引适合范围查询本地索引适合分区键之外的查询。 
