用户
 找回密码
 入住 CI 中国社区
搜索
查看: 4939|回复: 11
收起左侧

[数据库] 请问这个查询如何优化

[复制链接]
发表于 2013-4-3 10:23:38 | 显示全部楼层 |阅读模式
本帖最后由 阿呆 于 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
);

未命名.jpg
发表于 2013-4-8 19:34:51 | 显示全部楼层
1. 这里应写成自然连接,不必写成两个select,如此奇怪。

2. 慢的原因是goodsatt内的cid并无index,得在表内老老实实,一个一个的去找。goodsatt越大,查询越慢。

明白了慢的原因,试试自行去优化。
发表于 2013-4-9 09:47:20 | 显示全部楼层
能用JOIN尽量不要用子查询
连接查询经常用到的字段要加索引
发表于 2013-4-10 09:13:21 | 显示全部楼层
索引
 楼主| 发表于 2013-4-10 09:27:57 | 显示全部楼层
加索引后。
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)
复制代码
 楼主| 发表于 2013-4-10 16:28:39 | 显示全部楼层
本帖最后由 阿呆 于 2013-4-10 16:30 编辑
  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. mysql> show index from goods;
  10. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  11. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  12. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  13. | goods |          0 | PRIMARY  |            1 | goodsid     | A         |      384155 |     NULL | NULL   |      | BTREE      |         |
  14. +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  15. 1 row in set (0.19 sec)
复制代码
 楼主| 发表于 2013-4-10 16:32:27 | 显示全部楼层
[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]
 楼主| 发表于 2013-4-10 16:32:52 | 显示全部楼层
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)
复制代码
发表于 2013-4-11 15:10:36 | 显示全部楼层
limit 110020,10 mysql 要查找110030 条数据,优化的形式一般要么想办法让110020变小,例如知道110000笔记录是第110000条记录,就改写为id>110000 limit 20,10。要么就把查询结果缓存起来
发表于 2013-4-15 07:36:24 | 显示全部楼层
SeraphETX 发表于 2013-4-11 15:10
limit 110020,10 mysql 要查找110030 条数据,优化的形式一般要么想办法让110020变小,例如知道110000笔记 ...

re

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

本版积分规则