php方案 索引失效场景

— MySQL 索引失效场景完整示例

— ============ 1. 函数操作导致失效 ============
— 失效案例
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
SELECT * FROM users WHERE SUBSTRING(name, 1, 1) = '张';
SELECT * FROM products WHERE UPPER(name) = 'IPHONE';

— 优化方案
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';
SELECT * FROM users WHERE name LIKE '张%';
SELECT * FROM products WHERE name = 'iPhone'; — 使用 COLLATE 不区分大小写

— ============ 2. 隐式类型转换 ============
— 失效案例（phone 是 VARCHAR）
SELECT * FROM users WHERE phone = 13800138000;
SELECT * FROM orders WHERE order_no = 202401010001;

— 优化方案
SELECT * FROM users WHERE phone = '13800138000';
SELECT * FROM orders WHERE order_no = '202401010001';

— ============ 3. 模糊查询 ============
— 失效案例
SELECT * FROM users WHERE name LIKE '%张%'; — 前导模糊
SELECT * FROM users WHERE name LIKE '%张三'; — 前导模糊

— 有效案例
SELECT * FROM users WHERE name LIKE '张%'; — 后导模糊，可用索引
SELECT * FROM users WHERE name LIKE '张三%';

— 优化方案（全文索引）
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三' IN BOOLEAN MODE);

— ============ 4. OR 条件 ============
— 失效案例（只有 name 有索引）
SELECT * FROM users WHERE name = '张三' OR age = 25;

— 优化方案 1：UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 25;

— 优化方案 2：确保两个字段都有索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_age ON users(age);

— ============ 5. 不等于操作符 ============
— 失效案例
SELECT * FROM users WHERE status != 'active';
SELECT * FROM users WHERE status <> 'active';

— 优化方案
SELECT * FROM users WHERE status IN ('inactive', 'deleted', 'banned');

— ============ 6. IS NULL / IS NOT NULL ============
— 可能失效（取决于 NULL 值比例）
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

— 优化方案 1：使用默认值
ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL DEFAULT '';
SELECT * FROM users WHERE email = '';

— 优化方案 2：使用特殊标记
UPDATE users SET email = 'NULL' WHERE email IS NULL;
SELECT * FROM users WHERE email = 'NULL';

— ============ 7. 计算操作 ============
— 失效案例
SELECT * FROM orders WHERE amount + 10 > 100;
SELECT * FROM orders WHERE amount * 1.1 > 100;
SELECT * FROM users WHERE age – 5 > 20;

— 优化方案
SELECT * FROM orders WHERE amount > 90;
SELECT * FROM orders WHERE amount > 100 / 1.1;
SELECT * FROM users WHERE age > 25;

— ============ 8. 联合索引最左匹配原则 ============
— 创建联合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

— 有效案例
SELECT * FROM users WHERE name = '张三'; — 使用索引
SELECT * FROM users WHERE name = '张三' AND age = 25; — 使用索引
SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京'; — 使用索引
SELECT * FROM users WHERE name = '张三' AND city = '北京'; — 部分使用索引

— 失效案例
SELECT * FROM users WHERE age = 25; — 不使用索引
SELECT * FROM users WHERE city = '北京'; — 不使用索引
SELECT * FROM users WHERE age = 25 AND city = '北京'; — 不使用索引

— ============ 9. 范围查询后的字段 ============
— 创建联合索引
CREATE INDEX idx_age_status_city ON users(age, status, city);

— 部分使用索引
SELECT * FROM users WHERE age > 20 AND status = 'active' AND city = '北京';
— 说明：age 使用索引，status 和 city 不使用索引（因为 age 是范围查询）

— 优化方案：调整索引顺序
CREATE INDEX idx_status_city_age ON users(status, city, age);
SELECT * FROM users WHERE status = 'active' AND city = '北京' AND age > 20;

— ============ 10. SELECT * ============
— 不推荐（可能导致回表）
SELECT * FROM users WHERE name = '张三';

— 优化方案：覆盖索引
CREATE INDEX idx_name_email_age ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = '张三'; — 不需要回表

— ============ 11. IN 和 NOT IN ============
— IN 可以使用索引（数量不要太多）
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5); — 有效

— NOT IN 可能失效
SELECT * FROM users WHERE id NOT IN (1, 2, 3); — 可能失效

— 优化方案
SELECT * FROM users WHERE id NOT IN (1, 2, 3) AND id IS NOT NULL;

— ============ 12. ORDER BY 和 GROUP BY ============
— 失效案例（排序字段不在索引中）
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '张三' ORDER BY age; — age 排序无法使用索引

— 优化方案
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = '张三' ORDER BY age; — 可以使用索引

— ============ 验证索引使用情况 ============
— 使用 EXPLAIN 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

— 关键字段说明：
— type: ALL（全表扫描）< index < range < ref < eq_ref < const
— key: 实际使用的索引
— rows: 扫描的行数
— Extra: Using index（覆盖索引）, Using filesort（文件排序）

— ============ 索引优化建议 ============
— 1. 选择性高的列建索引
— 2. 频繁查询的列建索引
— 3. 联合索引遵循最左匹配
— 4. 避免过多索引（影响写入性能）
— 5. 定期分析和优化索引
ANALYZE TABLE users;
OPTIMIZE TABLE users;

MySQL 索引失效场景速查表

最短总结（8种场景）

快速检查

— 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

— 关键指标
— type: ALL（最差）→ index → range → ref → const（最好）
— key: 使用的索引名
— rows: 扫描行数（越少越好）
— Extra: Using index（最好）, Using filesort（需优化）

联合索引规则

— 索引：(name, age, city)
✅ WHERE name = '张三' — 使用
✅ WHERE name = '张三' AND age = 25 — 使用
✅ WHERE name = '张三' AND age = 25 AND city = '北京' — 使用
❌ WHERE age = 25 — 不使用
❌ WHERE city = '北京' — 不使用

覆盖索引优化

— 创建覆盖索引
CREATE INDEX idx_name_email ON users(name, email);

— 不需要回表（最快）
SELECT name, email FROM users WHERE name = '张三';

— 需要回表（较慢）
SELECT * FROM users WHERE name = '张三';

常用优化命令

— 查看索引
SHOW INDEX FROM users;

— 分析表
ANALYZE TABLE users;

— 优化表
OPTIMIZE TABLE users;

— 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';

索引设计原则

实战案例

案例 1：日期查询优化

— 慢查询（索引失效）
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

— 优化后（使用索引）
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2024-01-02';

案例 2：分页优化

— 慢查询（深分页）
SELECT * FROM users ORDER BY id LIMIT 100000, 20;

— 优化后（使用索引）
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;

案例 3：多条件查询

— 创建合适的联合索引
CREATE INDEX idx_status_city_age ON users(status, city, age);

— 高效查询
SELECT * FROM users
WHERE status = 'active'
AND city = '北京'
AND age > 20;

监控和诊断

— 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

— 查看重复索引
SELECT * FROM sys.schema_redundant_indexes;

— 查看表大小
SELECT
table_name,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_database';