面试题：MySQL WHERE子句中等难度优化问题

Question

在MySQL中，假设我们有一个包含百万条记录的用户表 `users`，表中有字段 `id`（主键）、`name`、`age`、`email` 以及 `registration_date`。现在要查询年龄在25到35岁之间且注册时间在2023年1月1日之后的用户，如何对这个 `WHERE` 子句进行优化以提升过滤效率？

Answer 1

1. 添加索引：
   • 为 age 和 registration_date 字段添加复合索引。因为复合索引的使用原则是“最左前缀”原则，先对 age 进行过滤，再对 registration_date 进行过滤，这样可以有效利用索引。
   • 执行以下 SQL 语句添加复合索引：

   ALTER TABLE users ADD INDEX idx_age_registration_date (age, registration_date);
   

2. 查询语句：
   • 使用添加索引后的查询语句：

   SELECT id, name, age, email, registration_date
   FROM users
   WHERE age BETWEEN 25 AND 35
   AND registration_date > '2023 - 01 - 01';
   

   • 这样在查询时，MySQL 可以利用索引快速定位符合条件的数据，从而提升过滤效率。避免在 WHERE 子句中对字段进行函数操作（如 DATE(registration_date)），因为这会导致索引失效，影响查询性能。同时，确保数据库统计信息是最新的，以便查询优化器能够生成更高效的执行计划，可以通过 ANALYZE TABLE users; 来更新统计信息。