如何理解mysql执行计划教程.pptx

如何理解Mysql执行计划; 我们已经能够用tcpdump+ percona tookit的pt-query-digest来获取mysql的 语句。在得到查询比较慢的sql时，我们需要知道查询慢在哪?;目录;Mysql的逻辑体系结构;查询的过程以及开销;查询性能低下的原因;SQL标准的执行流程(select);SQL标准的执行流程;sql中的join;nested loop join算法(对连接的优化);原理分析;explain的输出;explain的输出;type的类型(1);type的类型(2);type的类型(3);type的类型(4);type的类型(5);type的类型(6);Key相关;others;extra;extra;案例分析;Explain分析;; 补充： Temporary Tables In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the?MEMORY?storage engine, or stored on disk and processed by the?MyISAM?storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large.? 临时表占用内存比较大时，超过?tmp_table_size and max_heap_table_size 的values值后会交换到磁盘 filesort 如果order by没有使用索引或者和groupby的字段不一样，执行计划中extra就会出现filesort，执行过程如下 1. Read the rows that match the?WHERE?clause. 2. For each row, record a tuple of values consisting of the sort key value and the columns referenced by the query. 3. When the sort buffer becomes full, sort the tuples by sort key value in memory and write it to a temporary file. 4. After merge-sorting the temporary file, retrieve the rows in sorted order, but read the required columns directly from the sorted tuples rather than by accessing the table a second time. 需要排序的行比较多的时，会出现sort buffer溢出，出现磁盘I/O，同时也消耗CPU 影响性能 ; 参考资料： 1. MySQL中Join算法实现原理分析 /art/200904/117947.htm 2. Mysql explain的输出 /persister/archive/2008/10/27/236813.html 3.图解sql的join /articles/3463.html? 4. How MySQL Uses Internal Temporary Tables /doc/refman/5.1/en/internal-temporary-tables.html 5. MySQL使用rand获取随机记录的性能优化问题 /iefreer/article/details6. MYSQL执行流程的简单探讨 /index.php/161.html 7.Filesort /doc/refman/5.6/en/order-by-optimization.html 8. emporary-tables /doc/refman/5.7/en/internal-temporary-tables.html ;THANK YOU