mysql语句详细查询时间
来源:互联网 发布:阿里云服务器开端口 编辑:程序博客网 时间:2024/04/30 01:15
I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.
Isolating the problem
As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was thatEXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW PROFILE:
‘Statistics’ means ‘finding a good execution plan’. Okay, we have found why the query is slow.
If you have read this post, you already know how to fix the problem: set optimizer_search_depth = 0 and response time instantly drops to 0.02s. Also mentioned in this post, we should expect MySQL 5.6 to be much better at handling such queries. Well, let’s give it a try!
MySQL 5.6
With the default value of optimizer_search_depth, response time is 0.02s in 5.6 and we can see in SHOW PROFILE that the time spent finding a good execution plan is very small:
However ‘Statistics’ is still the most expensive step. What if we set optimizer_search_depth = 0? It is even better:
And the response time drops to 0.01s.
Conclusions
- EXPLAIN is a great tool to understand how the server will run a query, but it does not tell you everything. Additional tools (here, SHOW PROFILES) are often useful as well.
- By default MySQL 5.6 is much better than the previous versions at handling joins with many tables: great job optimizer team at Oracle!
- However even with 5.6, you may get better response time with optimizer_search_depth = 0
- mysql语句详细查询时间
- MySQL 的时间查询语句
- mysql常用时间统计查询语句
- mysql常见的时间查询语句
- 复杂的MySQL 时间查询语句解析
- mysql时间格式化,按时间段查询的MySQL语句
- mongoDB详细查询语句
- jooq时间查询语句
- mysql时间语句
- 一条mysql查询语句
- mysql随机查询语句
- mysql 语句优化查询
- MySQL查询语句
- MySQL查询语句
- MYSQL查询语句全集
- MYSQL常用查询语句
- mysql的查询语句
- mysql查询语句优化
- file.separator
- 队列 C语言(转)
- 文件编程(4)多路复用
- OSAL的按键设计分析
- 盘点开发者最喜爱的Swift技巧
- mysql语句详细查询时间
- 取球游戏
- Remove Duplicates from Sorted Array
- linux编程-守护进程编写
- onblur事件与onfocus事件
- Mp4box的下载、安装和使用
- ExtJs4基础概念总结
- QT QSS 使用
- START WITH CONNECT BY PRIOR