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