数据库 mysql left join 使用索引的问题请教

twm · 2014年05月22日 · 最后由 twm 回复于 2014年05月22日 · 4276 次阅读
mysql> desc user;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| username   | varchar(255) | NO   | UNI | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> desc topic;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| group_id   | int(11)      | YES  | MUL | NULL    |                |
| user_id    | int(11)      | YES  | MUL | NULL    |                |
| touched_at | datetime     | NO   |     | NULL    |                |
| deleted_at | datetime     | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

索引:

KEY `IDX_9D40DE1BFE54D947` (`group_id`),
KEY `IDX_9D40DE1BA76ED395` (`user_id`),
KEY `IDX_9D40DE1BFE54D9474AF38FD18C43597D` (`group_id`,`deleted_at`,`touched_at`),
KEY `IDX_9D40DE1B4AF38FD18C43597D` (`deleted_at`,`touched_at`),
KEY `IDX_9D40DE1BA76ED3954AF38FD18C43597D` (`user_id`,`deleted_at`,`touched_at`),

查询:

explain select title from topic join user on (user.id = topic.user_id) where deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where group_id = 1 and deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where user_id = 1 and deleted_at is null order by touched_at desc;

explain select title from topic join user on (user.id = topic.user_id) where topic.id = 1 and deleted_at is null order by touched_at desc;

这些查询都用到了索引,没有使用 filesort

但是如果没有第 3 个和第 5 个索引,则第 2 条和第 3 条语句都使用 filesort

我的问题是,真的需要第 3 个和第 5 个索引吗?不能通过第 4 个索引不能满足?有没有其他办法?

你只需要第三个和第五个索引啦。

第一个和第二个完全多余。

keywords:最左前缀原则

PS. 你的几个查询都是乱搞,,

select title from topic join user on (user.id = topic.user_id) where user_id = 1 and deleted_at is null order by touched_at desc;

都知道 user_id 等于 1 了还 join 个 P 啊..

#1 楼 @hooopo 谢谢 但 KEY IDX_9D40DE1B4AF38FD18C43597D (deleted_at,touched_at), 这个索引还是需要的吧?当 where 中没有 user_id 或者 topic_id 时使用?

#1 楼 @hooopo 回复乱搞

测试的 sql 不是正式的 知道 user_id 了但需要 user 表的信息。

需要 登录 后方可回复, 如果你还没有账号请 注册新账号