mysql - 什么会导致连接比分成两个查询慢？

Question

我正在从多个表执行 JOIN 以执行分面搜索。当避免 JOIN 并将查询分成两个不同的查询时，我注意到性能得到了很大提升，所以我假设我的 JOIN 没有优化。

结构:

-- tags
userId  |   tagId
1              3
1              4
2              3
2              9

-- search
userId   |   number  |   countryId   |   stateId ...  
1              13           221             55

-- countries
countryId     |  countryName   
221                 Somewhere

-- users
userId    |   profileImageLink
1         |   <photo link>

我正在尝试提取所有具有标签的用户，根据 search.number 进行排序并从其他表中获取元数据。查询:

SELECT 
    search.*, users.a, users.b, users.c, users.d, users.e, users.f, countries.location_country, states.location_state, cities.location_city 
FROM search 
RIGHT JOIN tags 
   ON search.user_id = tags.user_id 
LEFT JOIN users 
   ON users.user_id=search.user_id 
LEFT JOIN countries 
   ON countries.countryId=search.countryId 
LEFT JOIN states 
   ON states.countryId=search.countryId AND states.stateId=search.stateId 
LEFT JOIN cities 
   ON cities.countryId=search.countryId AND cities.stateId=search.stateId AND cities.cityId=search.cityId 
WHERE 
  tags.skillId =52772 
ORDER BY 
  search.number DESC LIMIT 0,200

我注意到删除用户表的 JOIN(然后再这样做)使查询速度更快。我如何优化它以在同一个查询中工作？我试过将 FROM 更改为标签而不是搜索，但这没有用...

这是 EXPLAIN 显示的内容:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  tags    ref skill_user,skillId  skill_user  4   const   184854  Using index; Using temporary; Using filesort
1   SIMPLE  search  eq_ref  user_id user_id 4   tags.user_id    1   
1   SIMPLE  countries   eq_ref  PRIMARY PRIMARY 2   search.countryId    1   
1   SIMPLE  states  eq_ref  PRIMARY,state   PRIMARY 3   search.stateId  1   
1   SIMPLE  cities  eq_ref  PRIMARY,city    PRIMARY 3   search.cityId   1   
1   SIMPLE  users   eq_ref  user_id user_id 4   search.user_id  1   

EXPLAIN without the LEFT JOIN users:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  tags    ref skill_user,skillId  skill_user  4   const   155870  Using index
1   SIMPLE  search  eq_ref  user_id user_id 4   tags.user_id    1   
1   SIMPLE  countries   eq_ref  PRIMARY PRIMARY 2   search.countryId    1   
1   SIMPLE  states  eq_ref  PRIMARY,state   PRIMARY 3   search.stateId  1   
1   SIMPLE  cities  eq_ref  PRIMARY,city    PRIMARY 3   search.cityId   1   

EXPLAIN 答案中建议的查询:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  tags    index   NULL    userid_skill    8   NULL    22689539    Using where; Using index; Using temporary; Using filesort
1   SIMPLE  search  eq_ref  user_id user_id 4   tags.user_id    1   
1   SIMPLE  users   eq_ref  user_id user_id 4   search.user_id  1   
1   SIMPLE  countries   eq_ref  PRIMARY PRIMARY 2   search.countryId    1   
1   SIMPLE  states  eq_ref  PRIMARY,state   PRIMARY 3   search.stateId  1   
1   SIMPLE  cities  eq_ref  PRIMARY,city    PRIMARY 3   search.cityId   1   

Answer 1

在表上创建索引以运行以下查询:

Table    ColumnName
------   ----------
tags     user_id, skillid (Both column in one index)

试试这个:

SELECT s.*, u.a, u.b, u.c, u.d, u.e, u.f, c.location_country, 
       st.location_state, ct.location_city 
FROM tags t 
LEFT JOIN search s ON t.user_id = s.user_id 
LEFT JOIN users u ON t.user_id = u.user_id 
LEFT JOIN countries c ON s.countryId = c.countryId 
LEFT JOIN states st ON s.stateId = st.stateId 
LEFT JOIN cities ci ON s.cityId= ct.cityId 
WHERE t.skillId =52772 
ORDER BY s.number DESC 
LIMIT 0,200

编辑

使用适当的索引尝试这两个查询，让我知道以下查询是否适合您。

SELECT s.*, u.a, u.b, u.c, u.d, u.e, u.f, c.location_country, 
       st.location_state, ct.location_city 
FROM (SELECT user_id FROM tags WHERE t.skillId = 52772) AS t 
LEFT JOIN search s ON t.user_id = s.user_id 
LEFT JOIN users u ON t.user_id = u.user_id 
LEFT JOIN countries c ON s.countryId = c.countryId 
LEFT JOIN states st ON s.stateId = st.stateId 
LEFT JOIN cities ci ON s.cityId= ct.cityId 
ORDER BY s.number DESC 
LIMIT 0,200; 

或

SELECT s.*, u.a, u.b, u.c, u.d, u.e, u.f, c.location_country, 
       st.location_state, ct.location_city 
FROM (SELECT t.user_id, s.* FROM tags t 
      LEFT JOIN search s ON t.user_id = s.user_id  
      WHERE t.skillId = 52772 
      ORDER BY s.number DESC 
      LIMIT 0,200) AS t 
LEFT JOIN users u ON t.user_id = u.user_id 
LEFT JOIN countries c ON s.countryId = c.countryId 
LEFT JOIN states st ON s.stateId = st.stateId 
LEFT JOIN cities ci ON s.cityId= ct.cityId 
ORDER BY s.number DESC 
LIMIT 0,200;