mysql order by file_mysql order by优化

mysql下，一个数据库查询优化

关键字: mysql数据库优化

对查询

Java代码

1. select distinct product0_.id as id62_........................................................

2. from

3.     t_product product0_, t_product_catagory productcat1_

4. where

5.     product0_.catagory_id=productcat1_.id

6.     and product0_.site_id='54586546898098098'

7.     and product0_.state=1 and productcat1_.publish=1

8. order by product0_.modifyTime desc limit 0, 30;

select distinct product0_.id as id62_........................................................

from

t_product product0_, t_product_catagory productcat1_

where

product0_.catagory_id=productcat1_.id

and product0_.site_id='54586546898098098'

and product0_.state=1 and productcat1_.publish=1

order by product0_.modifyTime desc limit 0, 30;

的优化

1.优化order by,order by 使用index的几种情况，其中key_part1,key_part2表示联合索引中的某索引字段

Java代码

1. SELECT * FROM t1

2.     ORDER BY key_part1,key_part2,... ;

3.

4.     SELECT * FROM t1

5.       WHERE key_part1=constant

6.       ORDER BY key_part2;

7.

8.     SELECT * FROM t1

9.       ORDER BY key_part1 DESC, key_part2 DESC;

10.

11.     SELECT * FROM t1

12.       WHERE key_part1=1

13.       ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1

WHERE key_part1=constant

ORDER BY key_part2;

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1=1

ORDER BY key_part1 DESC, key_part2 DESC;

具体见：http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

所以优化产品查询的排序只要做这样的改变就可以了alter table t_product add index t_product_site_modifytime_idx (site_id,modifytime),

如果order by 没有利用到索引，那么将会出现fileSort，如果sort_buffer不够大，fileSort过程则需要使用临时文件,fileSort优化，主要通过调整环境来达到，如下

2.设置参数，优化order by 时可能出现的file sort：

将sort_buffer_size = 1M  read_rnd_buffer_size = 1M

修改为sort_buffer_size = 16M read_rnd_buffer_size = 16M

避免order by 过程 进行fileSort排序过程临时文件的产生。从3秒->0.7秒左右

3.去掉distinct,因为distinct加order by,mysql将自动使用临时表

distinct的优化方式详见：http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

4.修改jdbc的url，增加参数useServerPrepStmts=false，使得query cache生效，

这个参数就是让参数与sql连接成整一个字符串，调试对参数中的单引号做了转义，应该

不用担心sql注入攻击了。另外，是否会导致服务端对查询重复的编译而导致的性能下降就不   清楚了.

整个测试去掉了querycache,保证innodb_buffer的命中率的情况下进行.

结果，在limit很小的时候，原来需要7秒，现在需要0.0秒。但在limit 1000或者更大的时候，查询速度下降，

因为需要得到开始索引那么多条记录，这只能通过限制limit的开始值和期望query cache命中率高些.

最重要工具是下面链接的explain教程

http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

http://nihongye.javaeye.com/blog/193442

http://www.javaeye.com/topic/227161

作者: liyihongcug

发布时间: 2010-05-04