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

[yangyunzhao]

我有两张表，基础表（简化后的） uid,score，资源表 uid, res0,res1,res2
如果我想查询排名(score)前1000的玩家的res1，该如何优化？

简单但是不优化的方法就是先查询uid相等（用inner join），然后limit 1000。有没有别的好办法呢？谢谢

[rucypli]

score加索引即可

[gikod]

-- 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 |                |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------------+

[gikod]

alter table t1 add index (score); Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 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 | 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)

[gikod]

刚才的代码没有用code扩上，重新发一遍

alter table t1 add index (score);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

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         | 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)

[LongRui888]

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

[ACMAIN_CHM]

引用

简单但是不优化的方法就是先查询uid相等（用inner join），然后limit 1000。

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