一个SQL order by的优化

nogroup
一个SQL order by的优化

[table=95%][tr][td][font=FixedSys][color=#000000][color=#0000FF]SELECT[/color] product[color=#0000CC].[/color]id_pro[color=#0000CC],[/color] product[color=#0000CC].[/color]proname[color=#0000CC],[/color] product[color=#0000CC].[/color]uid[color=#0000CC],[/color] [color=#FF0000]user[/color][color=#0000CC].[/color]username[color=#0000CC],[/color] product[color=#0000CC].[/color]mypri[color=#0000CC],[/color] product[color=#0000CC].[/color]propic[color=#0000CC],[/color] product[color=#0000CC].[/color]proinfo[color=#0000CC],[/color] product[color=#0000CC].[/color]propic[color=#0000CC],[/color] product[color=#0000CC].[/color]city[color=#0000CC],[/color] area[color=#0000CC].[/color]areaname[color=#0000CC],[/color] area[color=#0000CC].[/color]parentid[color=#0000CC],[/color] product[color=#0000CC].[/color]type[color=#0000CC],[/color] product[color=#0000CC].[/color]prolimit[color=#0000CC],[/color] product[color=#0000CC].[/color]protime[color=#0000CC],[/color] product[color=#0000CC].[/color]propri[color=#0000CC],[/color] product[color=#0000CC].[/color]profreightp[color=#0000CC],[/color] product[color=#0000CC].[/color]profreight1[color=#0000CC],[/color] class[color=#0000CC].[/color]classname[color=#0000CC],[/color] product[color=#0000CC].[/color]id_class[color=#0000CC],[/color] product[color=#0000CC].[/color]smallpic[color=#0000CC],[/color] product[color=#0000CC].[/color]profreight2[color=#0000CC],[/color] product[color=#0000CC].[/color]profreight3[color=#0000CC],[/color] class[color=#0000CC].[/color]classtype[color=#0000CC],[/color] class[color=#0000CC].[/color]fatherid[color=#0000CC],[/color] userinfo[color=#0000CC].[/color]qq[color=#0000CC],[/color] userinfo[color=#0000CC].[/color]lava[color=#0000CC],[/color] userinfo[color=#0000CC].[/color]msn
[color=#0000FF]FROM[/color] product
[color=#FF0000]LEFT[/color] JOIN [color=#FF0000]user[/color] ON product[color=#0000CC].[/color]uid [color=#0000CC]=[/color] [color=#FF0000]user[/color][color=#0000CC].[/color]uid
[color=#FF0000]LEFT[/color] JOIN userinfo ON userinfo[color=#0000CC].[/color]uid [color=#0000CC]=[/color] product[color=#0000CC].[/color]uid
[color=#FF0000]LEFT[/color] JOIN class ON product[color=#0000CC].[/color]id_class [color=#0000CC]=[/color] class[color=#0000CC].[/color]id_class
[color=#FF0000]LEFT[/color] JOIN area ON product[color=#0000CC].[/color]city [color=#0000CC]=[/color] area[color=#0000CC].[/color]areaid
[color=#0000FF]WHERE[/color] 1
[color=#FF0000]AND[/color] prolimit [color=#0000CC]>[/color]1212115302
[color=#FF0000]AND[/color] product[color=#0000CC].[/color]pronum [color=#0000CC]>[/color]0
ORDER BY [color=#0000CC]([/color]
prolimit - [color=#FF00FF]'1212115302'[/color]
[color=#0000CC])[/color] ASC
LIMIT 0 [color=#0000CC],[/color] 20 [/color][/font][/td][/tr][/table]

现在库里面有近三千条数据,加上ORDER BY (prolimit - '1212115302') ASC后的查询维持在0.43+s
而去掉这个order by 后查询立马提升到0.006s
所以个人认识瓶颈就在这个order by上了.
但排序又是必需的,不知道各位能给个什么方案来优化.

猪知猪之道
这个好像和 sort_buffer 还是 tmp_size 有关,

nogroup
回复 #2 猪知猪之道 的帖子

能详细点么

yueliangdao0608
There are two algorithm within MySQL, read the particular section at the document.

nogroup
回复 #4 yueliangdao0608 的帖子

能不能给个链接,好让我知道看哪部分.:shock:

猪知猪之道
[quote]原帖由 [i]nogroup[/i] 于 2008-5-30 11:19 发表 [url=http://bbs.chinaunix.net/redirect.php?goto=findpost&pid=8470319&ptid=1118187][img]http://bbs.chinaunix.net/images/common/back.gif[/img][/url]
能不能给个链接,好让我知道看哪部分.:shock: [/quote]
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M

chuhongze
[quote]原帖由 [i]nogroup[/i] 于 2008-5-30 11:02 发表 [url=http://bbs.chinaunix.net/redirect.php?goto=findpost&pid=8470123&ptid=1118187][img]http://bbs.chinaunix.net/images/common/back.gif[/img][/url]
SELECT product.id_pro, product.proname, product.uid, user.username, product.mypri, product.propic, product.proinfo, product.propic, product.city, area.areaname, area.parentid, product.type, produ ... [/quote]

看看这个，是不是适合你.[url]http://codex.wordpress.org.cn/index.php?title=Mysql%E4%B8%AD%E7%9A%84%E4%B8%B4%E6%97%B6%E8%A1%A8&oldid=89005[/url]

Edengundam
你可以试试先对 product.prolimit 排序, 然后选取20条数据.
最后进行JOIN操作

prolimit 有索引么?

1212115302这个是什么东西? 每次查询都回改变?

nogroup
prolimit 没有索引,
1212115302是当前时间戳,所以会一直变化．
＂先对 product.prolimit 排序, 然后选取20条数据＂
这个如何理解？如何操作？

Edengundam
[quote]原帖由 [i]nogroup[/i] 于 2008-5-31 19:53 发表 [url=http://bbs.chinaunix.net/redirect.php?goto=findpost&pid=8478947&ptid=1118187][img]http://bbs.chinaunix.net/images/common/back.gif[/img][/url]
prolimit 没有索引,
1212115302是当前时间戳,所以会一直变化．
＂先对 product.prolimit 排序, 然后选取20条数据＂
这个如何理解？如何操作？ [/quote]

大结构如下:
select ......         
FROM (SELECT .... FROM product WHERE prolimit > 1212115302 ORDER BY prolimit LIMIT 0, 20)
LEFT JOIN ...
WHERE ...

prolimit是不是要减去时间戳, 堆排序不产生影响吧?

加一个索引 where 那里可能会快一些. 你可以最后试试

wildlily980
ORDER BY (
prolimit - '1212115302'
)
想办法把1212115302这个去掉,因为加了这个不会使用索引.

小胡他爹
直接取出来然后自己用数组排呢

nogroup
回复 #10 Edengundam 的帖子

嗯, prolimit减不减时间戳, 对排序不产生影响.
所以改成了直接order by prolimit limit 0,20就行了.
加了一个index索引,但速度上似乎还是维持在0.44左右.也就是还是没有提高.
还有,你上面的select .... from (select .... from product where prolimit > 1212115302 ORDER BY prolimit LIMIT 0, 20)
LEFT JOIN ...
WHERE ...
这个语法,能讲讲如何运用吗?因为我一直以为from后面是直接加表名的.  上面这样,不成了字段名了么?

nogroup
回复 #12 小胡他爹 的帖子

现在是知道瓶颈在哪,就是这个order by上了.
先看看有没有直接从数据库上解决吧,尽量不改程序了,看能不能偷个懒 :mrgreen:

Edengundam
[quote]原帖由 [i]nogroup[/i] 于 2008-5-31 23:47 发表 [url=http://bbs.chinaunix.net/redirect.php?goto=findpost&pid=8479986&ptid=1118187][img]http://bbs.chinaunix.net/images/common/back.gif[/img][/url]
嗯, prolimit减不减时间戳, 对排序不产生影响.
所以改成了直接order by prolimit limit 0,20就行了.
加了一个index索引,但速度上似乎还是维持在0.44左右.也就是还是没有提高.
还有,你上面的select .... from ... [/quote]

FROM后面可以接子查询等, 只要输出是多个元组即可, 表名只是一种可以产生元组的情况.

我这里只是用了子查询而已, 如果product表中真的只有几千数据, 那么排序不应该这么慢.

你在SELECT前面加上EXPLAIN SELECT ....

然后把结果贴出来看看

Edengundam
PS: 请提供每张表的记录数, 以及涉及到WHERE字段的列是否有索引 :mrgreen:

tyz
学习

nogroup
能讲讲from后接子查询的用法吗?还不是很熟悉.
其中表user中,uid是primary key
表userinfo中,userinfo_id是primary key
id_class是表class的primary key
areaid是表area的primary key.
id_pro是表product表的primary key.

Edengundam
直接执行SELECT * FROM PRODUCT ORDER BY prolimit;
看看需要执行多久...

然后给prolimit建立一个索引再看看速度

nogroup
prolimit是加了index索引的.

SELECT *
FROM product
ORDER BY prolimit 显示行 0 - 29 (3,295 总计, 查询花费 0.0010 秒)
一直维持在0.001左右.