技术CTO-关注编程入门知识,提供编程入门教程

您的位置: 首页 > 数据库 > mysql > 正文

咨询一个sql查询的连接优化问题,谢谢!

来源: 技术CTO 阅读:

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

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

score加索引即可


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

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)
刚才的代码没有用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)

最好建个索引 


create index idx_xx_score on xx(score desc)

引用
简单但是不优化的方法就是先查询uid相等(用inner join),然后limit 1000。
这个MYSQL本身会自动进行优化。

^_^ 如果您热爱技术、热爱编程,想与更多的朋友一起交流学习,欢迎加入本站官方QQ群:345733473 ^_^