MySQL索引优化

单表索引优化案例

• 案例场景：有一张users表，包含id、username、email、age、status、created_at等字段，数据量达百万级。执行select * from users where age = 25 and status = 1查询速度很慢。
• 问题分析：未在age和status字段上建立索引，导致全表扫描。
• 优化方案：创建复合索引create index idx_age_status on users(age, status)。
• 优化效果：查询速度从原来的数秒优化到了数毫秒。

订单表覆盖索引优化案例

• 案例场景：有orders表，结构包括id、order_no、user_id、status、create_time等字段，每天有数百万数据写入，按user_id查询最近的订单非常慢。
• 问题分析：执行explain select * from orders where user_id = 10086 order by create_time desc limit 10发现是全表扫描，仅对user_id建立单列索引效果不佳。
• 优化方案：先建立复合索引create index idx_user_time on orders(user_id, create_time)，再使用覆盖索引只查询需要的字段，如select order_no, status, create_time from orders where user_id = 10086 order by create_time desc limit 10。
• 优化效果：优化前查询select sql_no_cache count(*) from orders where user_id = 10086执行时间 2.35 秒，优化后仅 0.003 秒，速度提升近 1000 倍。

多条件排序索引优化案例1

• 案例场景：有article表，包含id、author_id、category_id、views、comments、title、content等字段。需要查询category_id为 1 且comments > 1的情况下，观看数量最多的文章。
• 问题分析：执行explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1，显示type为all全表扫描，Using filesort文件内排序。
• 优化方案：先尝试创建索引ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views)，发现文件排序依然存在，删除索引后重建CREATE INDEX idx_article_ccv ON article (category_id,views)。
• 优化效果：全表扫描和文件排序问题都得到解决。

索引范围查找优化案例

• 案例场景：有employees表，包含id、name、age、position、hire_time等字段，有组合索引idx_name_age_position。执行EXPLAIN SELECT * FROM employees WHERE name > "LiLei" AND age = 23 AND position = "dev"和EXPLAIN SELECT * FROM employees WHERE name < "LiLei" AND age = 23 AND position = "dev"，发现第二个语句未使用索引查找。
• 问题分析：第二个语句中查询的结果集很大，MySQL 内部决策认为回表查询效率低，不如全表扫描效率快。
• 优化方案：使用覆盖索引EXPLAIN SELECT name,age,position FROM employees WHERE name < 'LiLei' AND age = 22 AND position ='manager'，只查询索引值，直接从索引树中获取数据，不需要回表。
• 优化效果：使得 MySQL 内部决策采用索引查找，提高了查询效率。