MySQL索引选择与失效场景全解析

目录

• 一、适合建立索引的字段特征
• 1.1 高选择性的字段
• 1.2 常用查询条件的字段
• 1.3 具体推荐场景
• 1.3.1 应当建索引的字段
• 1.3.2 数值类型优先
• 二、索引失效的8大常见场景
• 2.1 违反最左前缀原则
• 2.2 对索引列使用函数或运算
• 2.3 隐式类型转换http://www.devze.com
• 2.4 使用不等于(!=或<>)
• 2.5 LIKE以通配符开头
• 2.6 OR条件使用不当
• 2.7 数据分布不均匀
• 2.8 索引列参与IS NULL判断
• 三、高级索引失效场景
• 3.1 索引合并导致的性能问题
• 3.2 范围查询后的索引失效
• 3.3 不同字符集比较
• 四、索引使用最佳实践
• 4.1 索引设计黄金法则
• 4.2 实战案例解析
• 4.3 监控与调优工具
• 五、mysql 8.0索引新特性
• 5.1 倒序索引
• 5.2 隐藏索引
• 5.3 函数索引
• 六、总结与决策流程图
• 6.1 索引创建决策流程
• 6.2 索引失效排查清单

一、适合建立索引的字段特征

1.1 高选择性的字段

选择性公式：

选择性 = 不重复的值数量(DISTINCT) / 总记录数

选择性越接近1，索引效果越好

示例分析：

-- 查看字段选择性
SELECT 
  COUNT(DISTINCT gender)/COUNT(*) AS gender_selectivity,
  COUNT(DISTINCT phone)/COUNT(*) AS phone_selectivity
FROM users;

1.2 常用查询条件的字段

1.3 具体推荐场景

1.3.1 应当建索引的字段

• 主键和外键字段（自动创建）
• 高频查询的WHERE条件字段
• 多表JOIN的关联字段
• 排序和分组字段（特别是组合排序）
• 区分度高的状态字段（如订单状态）

1.3.2 数值类型优先

-- 好的索引字段
ALTER TABLE products ADD INDEX idx_category_id (category_id);  -- 整型
ALTER TABLE users ADD INDEX idx_phone (phone);  -- 定长字符串

-- 较差的索引选择
ALTER TABLE logs ADD INDEX idx_content (content(255));  -- 长文本前缀索引

二、索引失效的8大常见场景

2.1 违反最左前缀原则

联合索引结构示例：

ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

失效案例：

-- 有效使用索引
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- 部分失效（只用到了user_id）
EXPLAIN SELECT * FROM orders WHERE user_id = 1001;

-- 完全失效（跳过了user_id）
EXPLAIN SELECT * FROM orders WHERE status = 1;

2.2 对索引列使用函数或运算

失效示例：

-- 索引失效
EXPLAIN SELECT * FROM users WHERE DATE(create_time) = '2023-01-01';
EXPLAIN SELECT * FROM products WHERE price + 100 > 500;

-- 优化方案
EXPLAIN SELECT * FROM users 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59';

2.3 隐式类型转换

常见陷阱：

-- phone字段是varchar类型
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;  -- 失效
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';  -- 有效

-- 枚举值比较
EXPLAIN SELECT * FROM orders WHERE status = '1';  -- 可能失效
EXPLAIN SELECT * FROM orders WHERE status = 1;    -- 有效

2.4 使用不等于(!=或<>)

失效分析：

-- 索引失效
EXPLAIN SELECT * FROM users WHERE age != 30;

-- 优化方案（范围查询+union）
EXPLAIN SELECT * FROM users WHERE age < 30 
UNION ALL 
SELECT * FROM users WHERE age > 30;

2.5 LIKE以通配符开头

对比示例：

-- 索引失效
EXPLAIN SELECT * F编程客栈ROM users WHERE name LIKE '%张%';

-- 索引有效（前缀匹配）
EXPLAIN SELECT * FROM users WHERE namwww.devze.come LIKE '张%';

-- 特殊优化方案（全文索引）
ALTER TABLE users ADD FULLTEXT INDEX ft_idx_name (name);
EXPLAIN SELECT * FROM users WHERE MATCH(name) AGAINST('张*' IN BOOLEAN MODE);

2.6 OR条件使用不当

失效场景www.devze.com：

-- 索引失效（其中一个条件无索引）
EXPLAIN SELECT * FROM users WHERE user_id = 1001 OR register_ip = '192.168.1.1';

-- 优化方案
EXPLAIN SELECT * FROM users WHERE user_id = 1001
UNION ALL
SELECT * FROM users WHERE register_ip = '192.168.1.1' AND user_id != 1001;

2.7 数据分布不均匀

案例演示：

-- 当status=1占90%数据时
EXPLAIN SELECT * FROM orders WHERE status = 1;  -- 可能全表扫描

-- 查看数据分布
SELECT status, COUNT(*) FROM orders GROUP BY status;

2.8 索引列参与IS NULL判断

特殊情况：

-- MySQL 5.7+可以走索引
EXPLAIN SELECT * FROM users WHERE phone IS NULL;

-- 通常需要结合其他条件
EXPLAIN SELECT * FROM users WHERE phone IS NULL AND create_time > '2023-01-01';

三、高级索引失效场景

3.1 索引合并导致的性能问题

-- 可能不如预期高效
EXPLAIN SELECT * FROM users 
WHERE username = 'admin' OR email = 'admin@example.com';

-- 优化方案
CREATE INDEX idx_username_email ON users(username, email);

3.2 范围查询后的索引失效

-- 只有user_id和status能用索引，age失效
EXPLAphpIN SELECT * FROM orders 
WHERE user_id = 1001 AND status > 0 AND age = 30;

-- 优化索引顺序
ALTER TABLE orders ADD INDEX idx_user_age_status (user_id, age, status);

3.3 不同字符集比较

-- 不同字符集比较导致失效
EXPLAIN SELECT * FROM users u JOIN logs l 
ON u.username = l.operator 
WHERE u.charset = 'utf8mb4' AND l.charset = 'latin1';

四、索引使用最佳实践

4.1 索引设计黄金法则

三星索引原则：

• 一星：WHERE条件列是索引前缀
• 二星：ORDER BY/GROUP BY列在索引中
• 三星：SELECT列被索引覆盖

索引维护成本：

• 写操作需要更新索引
• 每个表最佳索引数通常为3-5个

4.2 实战案例解析

电商系统优化：

-- 原始查询
SELECT product_id, product_name, price 
FROM products 
WHERE category_id = 5 
AND status = 1 
AND stock > 0 
ORDER BY sales_volume DESC 
LIMIT 20;

-- 优化方案
ALTER TABLE products ADD INDEX idx_cat_status_stock_sales 
(category_id, status, stock, sales_volume DESC);

4.3 监控与调优工具

索引使用分析：

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 索引统计信息
SHOW INDEX FROM products;

-- 查询性能分析
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

五、MySQL 8.0索引新特性

5.1 倒序索引

ALTER TABLE orders ADD INDEX idx_create_time (create_time DESC);

5.2 隐藏索引

-- 测试删除索引的影响
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

5.3 函数索引

-- 对jsON字段建立索引
ALTER TABLE products ADD INDEX idx_price_data ((CAST(price_data->'$.price' AS DECIMAL(10,2))));

六、总结与决策流程图

6.1 索引创建决策流程

6.2 索引失效排查清单

• 检查EXPLAIN执行计划
• 验证SQL是否遵循最左前缀原则
• 检查是否有隐式类型转换
• 排查是否使用了函数或运算
• 分析数据分布情况
• 确认字符集和排序规则一致性

通过系统性地理解索引适用场景和失效原理，可以显著提升数据库查询性能。实际应用中应当结合业务特点和数据分布，定期审查索引效果，避免过度索引和索引滥用。

以上就是MySQL索引选择与失效场景全解析的详细内容，更多关于MySQL索引选择与失效的资料请关注编程客栈(www.devze.com)其它相关文章！