表: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万条数据就开始慢了。用什么办法优化啊
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万条数据就开始慢了。用什么办法优化啊
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可能会效果好些
where a.goodsid=b.goodsid and b.cid
in(33,101,182,188,265,377,402,498) limit 900,10;
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;以供分析。
这个好象还是慢,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;
0.17s左右,我再试下10W百W记录
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 ...并没有贴出。
-> 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)
-> 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)
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;
-> 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秒以上
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