咨询一个sql查询的连接优化问题，谢谢！

Question

我有两张表，基础表（简化后的） uid,score，资源表 uid, res0,res1,res2
如果我想查询排名(score)前1000的玩家的res1，该如何优化？简单但是不优化的方法就是先查询uid相等（用inner join），然后limit 1000。有没有别的好办法呢？谢谢

Answer 1

score加索引即可

Answer 2

-- prepare
create table t1(uid int auto_increment primary key, score int);
insert into t1(score) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
create table t2(uid int auto_increment primary key, res1 varchar(5));
insert into t2(res1) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);select * from (select uid from t1 order by score desc limit 5) a join t2 b using (uid);
+-----+------+
| uid | res1 |
+-----+------+
|  10 | 10   |
|   9 | 9    |
|   8 | 8    |
|   7 | 7    |
|   6 | 6    |
+-----+------+
5 rows in set (0.00 sec)select t2.* from t1 join t2 on t1.uid = t2.uid order by score desc limit 5
+-----+------+
| uid | res1 |
+-----+------+
|  10 | 10   |
|   9 | 9    |
|   8 | 8    |
|   7 | 7    |
|   6 | 6    |
+-----+------+
5 rows in set (0.00 sec)explain select * from (select uid from t1 order by score desc limit 5) a join t2 b using (uid);
+------+-------------+------------+--------+---------------+---------+---------+-------+------+----------------+
| id   | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra          |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+----------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    5 |                |
|    1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.uid |    1 |                |
|    2 | DERIVED     | t1         | ALL    | NULL          | NULL    | NULL    | NULL  |   10 | Using filesort |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+----------------+explain select t2.* from t1 join t2 on t1.uid = t2.uid order by score desc limit 5;
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra          |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------+
|    1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL        |   10 | Using filesort |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.uid |    1 |                |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------+

Answer 3

alter table t1 add index (score);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0explain select * from (select uid from t1 order by score desc limit 5) a join t2 b using (uid);
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    5 |             |
|    1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.uid |    1 |             |
|    2 | DERIVED     | t1         | index  | NULL          | score   | 5       | NULL  |    5 | Using index |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)explain select t2.* from t1 join t2 on t1.uid = t2.uid order by score desc limit 5;
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|    1 | SIMPLE      | t1    | index  | PRIMARY       | score   | 5       | NULL        |    5 | Using index |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.uid |    1 |             |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)

Answer 4

刚才的代码没有用code扩上，重新发一遍alter table t1 add index (score);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0explain select * from (select uid from t1 order by score desc limit 5) a join t2 b using (uid);
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
|    1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |    5 |             |
|    1 | PRIMARY     | b          | eq_ref | PRIMARY       | PRIMARY | 4       | a.uid |    1 |             |
|    2 | DERIVED     | t1         | index  | NULL          | score   | 5       | NULL  |    5 | Using index |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)explain select t2.* from t1 join t2 on t1.uid = t2.uid order by score desc limit 5;
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|    1 | SIMPLE      | t1    | index  | PRIMARY       | score   | 5       | NULL        |    5 | Using index |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.uid |    1 |             |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)

Answer 5

最好建个索引 
create index idx_xx_score on xx(score desc)

Answer 6

这个MYSQL本身会自动进行优化。