user(用户表)
user_id (PRIMARY KEY)
username
gender
user_device(用户设备表)
user_device_id (PRIMARY KEY)
user_id unique(INDEX)
created_at_date(NORMAL INDEX)
sql1:
SELECT
*
FROM
`user` a
JOIN user_device b ON a.user_id = b.user_id
ORDER BY
b.user_id;
Using index; Using temporary; Using filesort
原因:以user表为驱动表,但是排序却使用user_device表的user_id
优化:
SELECT
*
FROM
`user` a
JOIN user_device b ON a.user_id = b.user_id
ORDER BY
a.user_id;
sql2:
SELECT
a.user_id,
a.gender
FROM
`user` a
JOIN user_device b ON a.user_id = b.user_id
WHERE
b.created_at_date = '2022-07-08'
ORDER BY
b.user_id;
Using index condition; Using filesort
原因:以user_device表为驱动表,使用了user_device表的索引created_at_date过滤数据,使用user_device表的索引user_id排序,但是有where条件导致只能内部重新快排
优化:created_at_date修改为联合索引created_at_date+user_id
结论:mysql通常以小表驱动大表,一定要以驱动表的索引进行排序