技术饭

EXPLAIN分析sql时,如何优化Using temporary ; Using filesort

copylian    0 评论    21430 浏览    2017.09.12

1. 对需要查询和排序的字段要加索引

2. 在一定环境下,left join还是比普通连接查询效率要高,但是要尽量少地连接表,并且在做连接查询时注意观察索引是否起了作用。

3. 排序尽量对第一个表的索引字段进行,可以避免mysql创建临时表,这是非常耗资源的。

4. 对where条件里涉及到的字段,应适当地添加索引,这样会对排序操作有优化的作用。

5. 在做随机抽取数据的需求时,避免使用order by rand(),这种是很浪费数据库资源的,在执行过程中用show processlist查看,会发现有Copying to tmp table on disk。如果要实现这个功能,最好另辟奚径,来减轻Mysql的压力。

6. 如果说在分页时我们能先得到主键,再根据主键查询相关内容,也能得到查询的优化效果。通过国外《High Performance MySQL》专家组的测试可以看出,根据主键进行查询的类似“SELECT ... FROM... WHERE id = ...”的SQL语句(其中id为PRIMARYKEY),每秒钟能够处理10000次 以上的查询,而普通的SELECT查询每秒只能处理几十次到几百次 。涉及到分页的查询效率问题,网上的可用资源越来越多,查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。

7. 在平时的作业中,可以打开Mysql的Slow queries功能,经常检查一下是哪些语句降低的Mysql的执行效率,并进行定期优化。

8. 个人补充一点,正常测试中,对于数据相对较小的数据库而言,子查询比过多的left join效率来的更高一些

在已经设置了索引的前提下:

EXPLAIN  SELECT 

                           a.id,

                           a.user_id,

                           a.order_sn,

                           a.status,

                           a.create_time,

                           b.name,

                          IFNULL(d.buy_order_id, 0) AS rs 

               FROM order_buy a 

               LEFT JOIN source b 

              ON a.source_id = b.id 

              LEFT JOIN user c 

              ON a.user_id = c.id 

              LEFT JOIN order_ransom d 

              ON a.id = d.buy_order_id 

              WHERE a.status = 9

              ORDER BY a.create_time DESC 

              LIMIT 30, 10 

EXPLAIN  SELECT 

                           a.id,

                           a.user_id,

                           a.order_sn,

                          a.status,

                          a.create_time,

                          (SELECT b.name FROM source b WHERE b.id=a.source_id) AS `name`,

                          IFNULL((SELECT d.buy_order_id FROM order_ransom d WHERE d.buy_order_id=a.id), 0) AS rs 

                FROM order_buy a 

                LEFT JOIN jdt_user c ON c.id=a.user_id

                WHERE a.status = 9 

                ORDER BY a.create_time DESC 

                LIMIT 30, 10 


大家自行测试对比吧!

只袄早~~~
感谢你的支持,我会继续努力!
扫码打赏,感谢您的支持!

文明上网理性发言!

  • 还没有评论,沙发等你来抢