请问这个查询如何优化

阿呆

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越大，查询越慢。

明白了慢的原因，试试自行去优化。

hadi

能用JOIN尽量不要用子查询
连接查询经常用到的字段要加索引

spt119

索引

阿呆

加索引后。
limit 10W以上数据时很慢，至少要5S

SQL复制代码

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)

复制代码

阿呆

1. mysql> show index from goodsatt;
   
2. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
   
3. | Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
   
4. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
   
5. | goodsatt |          1 | gi       |            1 | goodsid     | A         |     1093257 |     NULL | NULL   |      | BTREE      |         |
   
6. | goodsatt |          1 | ci       |            1 | cid         | A         |         644 |     NULL | NULL   |      | BTREE      |         |
   
7. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
   
8. 2 rows in set (0.17 sec)
   
9. 
   
10. mysql> show index from goods;
    
11. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    
12. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
    
13. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    
14. | goods |          0 | PRIMARY  |            1 | goodsid     | A         |      384155 |     NULL | NULL   |      | BTREE      |         |
    
15. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
    
16. 1 row in set (0.19 sec)

复制代码

阿呆

[code=sql]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)[code]

阿呆

SQL复制代码

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)

复制代码

SeraphETX

limit 110020,10 mysql 要查找110030 条数据，优化的形式一般要么想办法让110020变小，例如知道110000笔记录是第110000条记录，就改写为id>110000 limit 20,10。要么就把查询结果缓存起来

gauspican

SeraphETX 发表于 2013-4-11 15:10
limit 110020,10 mysql 要查找110030 条数据，优化的形式一般要么想办法让110020变小，例如知道110000笔记 ...

re

记得之前有个贴子，说结果集过百就很奇怪，因为用户不可能翻阅那么长的页面，还是要精简命中结果集。