查询语句如何优化啊，1W条就开始慢了

Question

表：goods
create table goods(
goodsid int(8) not null primary key auto_increment,
title char(50) not null,
description text
);表：goodsatt
create table goodsatt(
goodsid int(8) not null,
cid int(4) not null
);
alter table goodsatt add index gi(goodsid);
alter table goodsatt add index ci(cid);
0.09s
select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 900,10;0.9s
select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 9000,10;1.4s
select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 31600,10;
到limit 1万条数据就开始慢了。用什么办法优化啊

Answer 1

mysql一定要禁用这种in嵌套select的语句select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 900,10;改成select *
from goods A,goodsatt  B
where A.goodsid =B.goodsid  and B. cid in(33, 101, 182, 188, 265, 377,402,498)
limit 900,10;
另外可能强制使用cid可能会效果好些

Answer 2

select * from goodsid a,goodsatt b
where a.goodsid=b.goodsid and b.cid 
in(33,101,182,188,265,377,402,498) limit 900,10;

Answer 3

贴出 show index from goods ;show index from goodsatt   ;
explain select * from goods where goodsid in(select goodsid from goodsatt where cid in(33, 101, 182, 188, 265, 377,402,498)) limit 31600,10;以供分析。

Answer 4

select g.* from goods g , goodsatt  s where s.cid in(33, 101, 182, 188, 265, 377,402,498)  and g.goodsid=s.goodsid

Answer 5

这个好象还是慢，1W以上记录都是秒单位了。
1.70s
select *
from goods A,goodsatt  B
where A.goodsid =B.goodsid  and B. cid in(33, 101, 182, 188, 265, 377,402,498)
limit 30000,10;1.50s
select g.* from goods g , goodsatt  s where s.cid in(33, 101, 182, 188, 265,377,402,498)  and g.goodsid=s.goodsid limit 30000,10;

Answer 6

现在好象快了些，
0.17s左右，我再试下10W百W记录

Answer 7

select * from goods 
where exists (
select 1 from goodsatt
where cid in(33, 101, 182, 188, 265, 377,402,498)
and goodsid=goods.goodsid
)
limit 31600,10;
另外贴结果时请不要贴图片，直接贴文本。 另外explain ...并没有贴出。

Answer 8

mysql> select *
    -> from goods A,goodsatt  B
    -> where A.goodsid =B.goodsid  and B. cid in(101,200,201,202,203,204,205)
    -> limit 100000,10;
+---------+----------------+---------------------+---------+-----+
| goodsid | title          | description         | goodsid | cid |
+---------+----------------+---------------------+---------+-----+
|   37423 | 产品标题=37423 | 描述37423<br>结束。 |   37423 | 201 |
|   37424 | 产品标题=37424 | 描述37424<br>结束。 |   37424 | 201 |
|   37426 | 产品标题=37426 | 描述37426<br>结束。 |   37426 | 201 |
|   37427 | 产品标题=37427 | 描述37427<br>结束。 |   37427 | 201 |
|   37428 | 产品标题=37428 | 描述37428<br>结束。 |   37428 | 201 |
|   37429 | 产品标题=37429 | 描述37429<br>结束。 |   37429 | 201 |
|   37430 | 产品标题=37430 | 描述37430<br>结束。 |   37430 | 201 |
|   37431 | 产品标题=37431 | 描述37431<br>结束。 |   37431 | 201 |
|   37432 | 产品标题=37432 | 描述37432<br>结束。 |   37432 | 201 |
|   37436 | 产品标题=37436 | 描述37436<br>结束。 |   37436 | 201 |
+---------+----------------+---------------------+---------+-----+
10 rows in set (1.77 sec)mysql> explain select *
    -> from goods A,goodsatt  B
    -> where A.goodsid =B.goodsid  and B. cid in(101,200,201,202,203,204,205)
    -> limit 100000,10;
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
|  1 | SIMPLE      | B     | range  | gi,ci         | ci      | 4       | NULL           | 334550 | Using where |
|  1 | SIMPLE      | A     | eq_ref | PRIMARY       | PRIMARY | 4       | test.B.goodsid |      1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------+--------+-------------+
2 rows in set (0.05 sec)

Answer 9

mysql> select * from goods
    -> where exists (
    -> select 1 from goodsatt
    -> where cid in(101,200,201,202,203,204,205)
    -> and goodsid=goods.goodsid
    -> )
    -> limit 101600,10;
+---------+-----------------+----------------------+
| goodsid | title           | description          |
+---------+-----------------+----------------------+
|  109726 | 产品标题=109726 | 描述109726<br>结束。 |
|  109727 | 产品标题=109727 | 描述109727<br>结束。 |
|  109728 | 产品标题=109728 | 描述109728<br>结束。 |
|  109729 | 产品标题=109729 | 描述109729<br>结束。 |
|  109730 | 产品标题=109730 | 描述109730<br>结束。 |
|  109731 | 产品标题=109731 | 描述109731<br>结束。 |
|  109732 | 产品标题=109732 | 描述109732<br>结束。 |
|  109733 | 产品标题=109733 | 描述109733<br>结束。 |
|  109734 | 产品标题=109734 | 描述109734<br>结束。 |
|  109735 | 产品标题=109735 | 描述109735<br>结束。 |
+---------+-----------------+----------------------+
10 rows in set (4.39 sec)

Answer 10

用in的嵌套查询会导致全表扫描诶，
SELECT * FROM goods
JOIN goodsatt ON 
(goods.goodsid=goodsatt.goodsid)
WHERE goodsatt.cid IN(33, 101, 182, 188, 265, 377,402,498)
LIMIT 31600,10;

Answer 11

mysql> select *
    -> from goods A,goodsatt  B
    -> where A.goodsid =B.goodsid  and B. cid in(101,200,201,202,203,204,205)
    -> limit 110000,10;
+---------+-----------------+----------------------+---------+-----+
| goodsid | title           | description          | goodsid | cid |
+---------+-----------------+----------------------+---------+-----+
|  166547 | 产品标题=166547 | 描述166547<br>结束。 |  166547 | 200 |
|  166548 | 产品标题=166548 | 描述166548<br>结束。 |  166548 | 200 |
|  166550 | 产品标题=166550 | 描述166550<br>结束。 |  166550 | 200 |
|  166551 | 产品标题=166551 | 描述166551<br>结束。 |  166551 | 200 |
|  166552 | 产品标题=166552 | 描述166552<br>结束。 |  166552 | 200 |
|  166553 | 产品标题=166553 | 描述166553<br>结束。 |  166553 | 200 |
|  166555 | 产品标题=166555 | 描述166555<br>结束。 |  166555 | 200 |
|  166556 | 产品标题=166556 | 描述166556<br>结束。 |  166556 | 200 |
|  166557 | 产品标题=166557 | 描述166557<br>结束。 |  166557 | 200 |
|  166559 | 产品标题=166559 | 描述166559<br>结束。 |  166559 | 200 |
+---------+-----------------+----------------------+---------+-----+
10 rows in set (8.33 sec)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)有些慢，可能是电脑在拷贝几个大文件的原因。不过基本上 10W数据还是 1秒以上

Answer 12

select * from goods inner join (
select goodsid from(
select goodsid from goodsatt where cid = 33
union all
select goodsid from goodsatt where cid = 101
union all
select goodsid from goodsatt where cid =  182
union all
select goodsid from goodsatt where cid =  188 
union all
select goodsid from goodsatt where cid = 265 
union all
select goodsid from goodsatt where cid = 377
union all
select goodsid from goodsatt where cid = 402
union all
select goodsid from goodsatt where cid = 498
) as b group by goodsid ) as c on c.goodsid =goods.goodsid limit 31600,10