请问这个查询如何优化
本帖最后由 阿呆 于 2013-4-3 10:25 编辑select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 31600,10;
耗时：1.7s
请问这个查询如何优化， 才能在0.0几s
create table goods(
goodsid int(8) not null primary key auto_increment,
title char(50) not null,
description text
);
create table goodsatt(
goodsid int(8) not null,
cid int(4) not null
);
1. 这里应写成自然连接，不必写成两个select，如此奇怪。
2. 慢的原因是goodsatt内的cid并无index，得在表内老老实实，一个一个的去找。goodsatt越大，查询越慢。
明白了慢的原因，试试自行去优化。 能用JOIN尽量不要用子查询
连接查询经常用到的字段要加索引 索引 加索引后。
limit 10W以上数据时很慢，至少要5S
mysql> SELECT * FROM goods
-> JOIN goodsatt ON
-> (goods.goodsid=goodsatt.goodsid)
-> WHERE goodsatt.cid IN(101,200,201,202,203,204,205)
-> LIMIT 110020,10;
+---------+----------------+---------------------+---------+-----+
| goodsid | title | description | goodsid | cid |
+---------+----------------+---------------------+---------+-----+
| 65786 | 产品标题=65786 | 描述65786<br>结束。 | 65786 | 201 |
| 65787 | 产品标题=65787 | 描述65787<br>结束。 | 65787 | 200 |
| 65788 | 产品标题=65788 | 描述65788<br>结束。 | 65788 | 201 |
| 65789 | 产品标题=65789 | 描述65789<br>结束。 | 65789 | 200 |
| 65790 | 产品标题=65790 | 描述65790<br>结束。 | 65790 | 200 |
| 65791 | 产品标题=65791 | 描述65791<br>结束。 | 65791 | 200 |
| 65792 | 产品标题=65792 | 描述65792<br>结束。 | 65792 | 200 |
| 65793 | 产品标题=65793 | 描述65793<br>结束。 | 65793 | 201 |
| 65794 | 产品标题=65794 | 描述65794<br>结束。 | 65794 | 201 |
| 65794 | 产品标题=65794 | 描述65794<br>结束。 | 65794 | 200 |
+---------+----------------+---------------------+---------+-----+
10 rows in set (11.25 sec) 本帖最后由 阿呆 于 2013-4-10 16:30 编辑
mysql> show index from goodsatt;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goodsatt | 1 | gi | 1 | goodsid | A | 1093257 | NULL | NULL | | BTREE | |
| goodsatt | 1 | ci | 1 | cid | A | 644 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.17 sec)
mysql> show index from goods;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goods | 0 | PRIMARY| 1 | goodsid | A | 384155 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.19 sec) mysql> show index from goodsatt;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goodsatt | 1 | gi | 1 | goodsid | A | 1093257 | NULL | NULL | | BTREE | |
| goodsatt | 1 | ci | 1 | cid | A | 644 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.17 sec)
mysql> show index from goods;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goods | 0 | PRIMARY| 1 | goodsid | A | 384155 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.19 sec) mysql> show index from goodsatt;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goodsatt | 1 | gi | 1 | goodsid | A | 1093257 | NULL | NULL | | BTREE | |
| goodsatt | 1 | ci | 1 | cid | A | 644 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.17 sec)
mysql> show index from goods;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| goods | 0 | PRIMARY| 1 | goodsid | A | 384155 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.19 sec) limit 110020,10 mysql 要查找110030 条数据，优化的形式一般要么想办法让110020变小，例如知道110000笔记录是第110000条记录，就改写为id>110000 limit 20,10。要么就把查询结果缓存起来 SeraphETX 发表于 2013-4-11 15:10 static/image/common/back.gif
limit 110020,10 mysql 要查找110030 条数据，优化的形式一般要么想办法让110020变小，例如知道110000笔记 ...
re
记得之前有个贴子，说结果集过百就很奇怪，因为用户不可能翻阅那么长的页面，还是要精简命中结果集。
页:
[1]
2