各位大侠,小弟我在执行如下查询语句时:
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in ( select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 312 )
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;customer记录数15万,orders记录数150万,lineitem记录数600万。
mysql没有开缓存,参数设置如下
innodb_buffer_pool_size = 1000M,innodb_additional_mem_pool_size = 32M,innodb_log_file_size = 512M,innodb_log_buffer_size = 8Mexplain后结果如下
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 151595 | Using temporary; Using filesort |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | test.customer.c_custkey | 7 | Using where |
| 1 | PRIMARY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | test.orders.o_orderkey | 1 | |
| 2 | DEPENDENT SUBQUERY | lineitem | index | NULL | PRIMARY | 8 | NULL | 4 |我觉得执行可能会慢,因为有子查询和group by||order by,但是两个小时了,执行语句的state一直处于sending data上。
这事怎么回事啊?
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in ( select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 312 )
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;customer记录数15万,orders记录数150万,lineitem记录数600万。
mysql没有开缓存,参数设置如下
innodb_buffer_pool_size = 1000M,innodb_additional_mem_pool_size = 32M,innodb_log_file_size = 512M,innodb_log_buffer_size = 8Mexplain后结果如下
| 1 | PRIMARY | customer | ALL | PRIMARY | NULL | NULL | NULL | 151595 | Using temporary; Using filesort |
| 1 | PRIMARY | orders | ref | PRIMARY,i_o_custkey | i_o_custkey | 5 | test.customer.c_custkey | 7 | Using where |
| 1 | PRIMARY | lineitem | ref | PRIMARY,i_l_orderkey,i_l_orderkey_quantity | i_l_orderkey | 4 | test.orders.o_orderkey | 1 | |
| 2 | DEPENDENT SUBQUERY | lineitem | index | NULL | PRIMARY | 8 | NULL | 4 |我觉得执行可能会慢,因为有子查询和group by||order by,但是两个小时了,执行语句的state一直处于sending data上。
这事怎么回事啊?
各位高手,这个难道只是执行慢么?InnoDB里优化什么参数可以提高group by和order by的速度?
这样貌似会快点是么? 执行速度是这样么:存储过程>静态视图>动态联接>子查询?
我现在就是这么做。不过不想显示在终端里,想把查询结果写在一个文件里,但是
加入INTO OUTFILE'/home/1.txt'这句话之后报了个错:ERROR 1 (HY000): Can't create/write to file '/home/1.txt' (Errcode: 13)这又是咋了???
无此说法。 虽然 存储过程>静态视图 会节省SQL语句的分析,优化时间,但这个说法并不准确。可以浏览一下官方文档的说明。
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#query-speed
7.2. 优化SELECT语句和其它查询
7.2.1. EXPLAIN语法(获取SELECT相关信息)
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句
7.2.5. 范围优化
7.2.6. 索引合并优化
7.2.7. MySQL如何优化IS NULL
7.2.8. MySQL如何优化DISTINCT
7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
7.2.10. MySQL如何优化嵌套Join
7.2.11. MySQL如何简化外部联合
7.2.12. MySQL如何优化ORDER BY
7.2.13. MySQL如何优化GROUP BY
7.2.14. MySQL如何优化LIMIT
7.2.15. 如何避免表扫描
7.2.16. INSERT语句的速度
7.2.17. UPDATE语句的速度
7.2.18. DELETE语句的速度
7.2.19. 其它优化技巧