EXPLAIN sql优化方法（2） Using temporary ; Using filesort-阿里云开发者社区

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
相关文章推荐
大鼻子的企鹅 · Laravel 数据库迁移 | ...· 昨天 ·
乐观的小刀 · BufferedImage资源释放 ...· 4 月前 ·
道上混的滑板 · 程序设计第二十一题 ...· 1 年前 ·
逃跑的键盘 · RapidJSON —— C++ 快速 ...· 2 年前 ·
帅气的蚂蚁 · 功能区和卡参考 - Tableau· 2 年前 ·
英俊的木耳 · Java 抽象类和接口 - 小a的软件思考 ...· 2 年前 ·
优化GROUP BY语句
默认情况下， MySQL 对所有GROUP BY col1，col2...的字段进行排序。这与在查询中指定ORDER BY col1,col2...类似。因此，如果显式包括一个包含相同的列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。如果查询包括GROUP BY 但用户想要避免排序结果的消耗，则可以指定ORDER By NULL禁止排序，例如：
Java代码
explain select id, sum(moneys) from sales2 group by id \G
explain select id, sum(moneys) from sales2 group by id order by null \G
你可以通过比较发现第一条语句会比第二句在Extra:里面多了Using filesort.而恰恰filesort是最耗时的。
优化ORDER BY语句
在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。WHERE 条件和 ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。
Java代码
SELECT * FROM t1 ORDER BY key_part1,key_part2,....:
SELECT * FROM t1 WHERE key_part1 = 1 ORDER BY key_part1 DESC,key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下的情况不使用索引：
      Java代码
      
        SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
       
        --ORDER by的字段混合ASC 和 DESC
       
        SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
       
        ----用于查询行的关键字与ORDER BY 中所使用的不相同
       
        SELECT * FROM t1 ORDER BY key1, key2;
       
        ----对不同的关键字使用ORDER BY
       
        mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id order by B . id ;
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
       
        | id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows   | Extra                            |
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
       
        |
        
         1
        
        | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                     |
        
         46585
        
        | Using temporary ; Using filesort |
       
        |
        
         1
        
        | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY |
        
         4
        
        | joomla_test . A . catid      |
        
         1
        
        |                                 |
       
        |
        
         1
        
        | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY |
        
         4
        
        | joomla_test . A . sectionid |
        
         1
        
        | Using index                      |
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+---------------------------------+
       
         3
        
        rows in set (
        
         0.00
        
        sec )
       
        mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id order by A . id ;
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
       
        | id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows   | Extra           |
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
       
        |
        
         1
        
        | SIMPLE       | A      | ALL     | NULL           | NULL     | NULL     | NULL                     |
        
         46585
        
        | Using filesort |
       
        |
        
         1
        
        | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY |
        
         4
        
        | joomla_test . A . catid      |
        
         1
        
        |                |
       
        |
        
         1
        
        | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY |
        
         4
        
        | joomla_test . A . sectionid |
        
         1
        
        | Using index     |
       
        +----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+----------------+
       
      对于上面两条语句，只是修改了一下排序字段，而第一个使用了Using temporary，而第二个却没有。在日常的网站维护中，如果有Using temporary出现，说明需要做一些优化措施了。
     
      而为什么第一个用了临时表，而第二个没有用呢？
      
      因为如果有ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。
      
      那么，对于上面例子中的第一条语句，我们需要对jos_categories的id进行排序，可以将SQL做如下改动：
     
      Java代码
      
        mysql > explain select B . id , B . title , A . title from jos_categories A left join jos_content B on A . id = B . catid left join jos_sections C on B . sectionid = C . id order by A . id ;
       
        +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
       
        | id | select_type | table | type    | possible_keys | key        | key_len | ref                      | rows | Extra           |
       
        +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
       
        |
        
         1
        
        | SIMPLE       | A      | ALL     | NULL           | NULL       | NULL     | NULL                     |
        
         18
        
        | Using filesort |
       
        |
        
         1
        
        | SIMPLE       | B      | ref     | idx_catid      | idx_catid |
        
         4
        
        | joomla_test . A . id         |
        
         3328
        
        |                |
       
        |
        
         1
        
        | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY    |
        
         4
        
        | joomla_test . B . sectionid |
        
         1
        
        | Using index     |
       
        +----+-------------+-------+--------+---------------+-----------+---------+-------------------------+------+----------------+
       
         3
        
        rows in set (
        
         0.00
        
        sec )
       
       这样我们发现，不会再有Using temporary了，而且在查询jos_content时，查询的记录明显有了数量级的降低，这是因为jos_content的idx_catid起了作用。
       
       所以结论是：
      
        尽量对第一个表的索引键进行排序，这样效率是高的。
       
        我们还会发现，在排序的语句中都出现了Using filesort，字面意思可能会被理解为：使用文件进行排序或中文件中进行排序。实际上这是不正确的，这是一个让人产生误解的词语。
       
        当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。
       
       然而，当我们回过头来再看上面运行过的一个SQL的时候会有以下发现：
      
       Java代码
       
         mysql > explain select A . id , A . title , B . title from jos_content A , jos_categories B , jos_sections C where A . catid = B . id and A . sectionid = C . id order by C . id ;
        
         +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
        
         | id | select_type | table | type    | possible_keys          | key          | key_len | ref                  | rows   | Extra        |
        
         +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
        
         |
         
          1
         
         | SIMPLE       | C      | index   | PRIMARY                | PRIMARY      |
         
          4
         
         | NULL                 |
         
          1
         
         | Using index |
        
         |
         
          1
         
         | SIMPLE       | A      | ref     | idx_catid , idx_section | idx_section |
         
          4
         
         | joomla_test . C . id     |
         
          23293
         
         | Using where |
        
         |
         
          1
         
         | SIMPLE       | B      | eq_ref | PRIMARY                | PRIMARY      |
         
          4
         
         | joomla_test . A . catid |
         
          1
         
         | Using where |
        
         +----+-------------+-------+--------+-----------------------+-------------+---------+---------------------+-------+-------------+
        
          3
         
         rows in set (
         
          0.00
         
         sec )
        
        这是我们刚才运行过的一条语句，只是加了一个排序，而这条语句中C表的主键对排序起了作用，我们会发现Using filesort没有了。
        
        而尽管在上面的语句中也是对第一个表的主键进行排序，却没有得到想要的效果（第一个表的主键没有用到），这是为什么呢？实际上以上运行过的所有left join的语句中，第一个表的索引都没有用到，尽管对第一个表的主键进行了排序也无济于事。不免有些奇怪！
        
        于是我们继续测试了下一条SQL：
       
        Java代码
        
          mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id where A . id <
          
           100
          
          ;
         
          +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
         
          | id | select_type | table | type    | possible_keys   | key      | key_len | ref                      | rows | Extra        |
         
          +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
         
          |
          
           1
          
          | SIMPLE       | A      | range   | PRIMARY         | PRIMARY |
          
           4
          
          | NULL                     |
          
           90
          
          | Using where |
         
          |
          
           1
          
          | SIMPLE       | B      | eq_ref | PRIMARY         | PRIMARY |
          
           4
          
          | joomla_test . A . catid      |
          
           1
          
          |             |
         
          |
          
           1
          
          | SIMPLE       | C      | eq_ref | PRIMARY         | PRIMARY |
          
           4
          
          | joomla_test . A . sectionid |
          
           1
          
          | Using index |
         
          +----+-------------+-------+--------+----------------+---------+---------+-------------------------+------+-------------+
         
           3
          
          rows in set (
          
           0.05
          
          sec )
         
         然后，当再次进行排序操作的时候，Using filesoft也没有再出现
        
         Java代码
         
           mysql > explain select A . id , A . title , B . title from jos_content A left join jos_categories B on A . catid = B . id left join jos_sections C on A . sectionid = C . id where A . id <
           
            100
           
           order by A . id ;
          
           +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
          
           | id | select_type | table | type    | possible_keys | key      | key_len | ref                      | rows | Extra        |
          
           +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
          
           |
           
            1
           
           | SIMPLE       | A      | range   | PRIMARY        | PRIMARY |
           
            4
           
           | NULL                     |
           
            105
           
           | Using where |
          
           |
           
            1
           
           | SIMPLE       | B      | eq_ref | PRIMARY        | PRIMARY |
           
            4
           
           | joomla_test . A . catid      |
           
            1
           
           |             |
          
           |
           
            1
           
           | SIMPLE       | C      | eq_ref | PRIMARY        | PRIMARY |
           
            4
           
           | joomla_test . A . sectionid |
           
            1
           
           | Using index |
          
           +----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+-------------+
          
            3
           
           rows in set (
           
            0.00
           
           sec )
          
          这个结果表明：对where条件里涉及到的字段，Mysql会使用索引进行搜索，而这个索引的使用也对排序的效率有很好的提升。
          
          写了段程序测试了一下，分别让以下两个SQL语句执行200次：
         
          Java代码
          
            select A . id , A . title , B . title from jos_content   A left join jos_categories B on A . catid = B . id left join jos_sections C   on A . sectionid = C . id
           
            select A . id , A . title , B . title from jos_content   A , jos_categories B , jos_sections C where A . catid = B . id and   A . sectionid = C . id
           
            select A . id , A . title , B . title from jos_content A left   join jos_categories B on A . catid = B . id left join jos_sections C on   A . sectionid = C . id 　 order by rand () limit
            
             10
            
            select A . id from   jos_content A left join jos_categories B on B . id = A . catid left join   jos_sections C on A . sectionid = C . id order by A . id
           
           结果是第(1)条平均用时
           
            20s
           
           ，第(2)条平均用时
           
            44s
           
           ，第(3)条平均用时
           
            70s
           
           ，第(4)条平均用时
           
            2s
           
           。而且假如我们用explain观察第(3)条语句的执行情况，会发现它创建了temporary表来进行排序。
           
            综上所述，可以得出如下结论：
           
           1. 对需要查询和排序的字段要加索引。
           
           2. 在一定环境下，left join还是比普通连接查询效率要高，但是要尽量少地连接表，并且在做连接查询时注意观察索引是否起了作用。
           
           3. 排序尽量对第一个表的索引字段进行，可以避免mysql创建临时表，这是非常耗资源的。
           
           4. 对where条件里涉及到的字段，应适当地添加索引，这样会对排序操作有优化的作用。
           
           5. 在做随机抽取数据的需求时，避免使用order by rand()，从上面的例子可以看出，这种是很浪费
           
            数据库
           
           资源的，在执行过程中用show processlist查看，会发现第(3)条有Copying to tmp table on disk。而对(3)和(4)的对比得知，如果要实现这个功能，最好另辟奚径，来减轻Mysql的压力。
           
           6. 从第4点可以看出，如果说在分页时我们能先得到主键，再根据主键查询相关内容，也能得到查询的优化效果。通过国外《High Performance MySQL》专家组的测试可以看出，根据主键进行查询的类似“SELECT ... FROM... WHERE id = ...”的SQL语句（其中id为PRIMARYKEY），每秒钟能够处理
           
            10000次
           
           以上的查询，而普通的SELECT查询每秒只能处理
           
            几十次到几百次
           
           。涉及到分页的查询效率问题，网上的可用资源越来越多，查询功能也体现出了它的重要性。也便是sphinx、lucene这些第三方搜索引擎的用武之地了。
           
           7. 在平时的作业中，可以打开Mysql的Slow queries功能，经常检查一下是哪些语句降低的Mysql的执行效率，并进行定期优化。
          
           长达1.7万字的explain关键字指南奉上！请你别再说不会SQL优化了
          
           当你的数据里只有几千几万，那么 SQL 优化并不会发挥太大价值，但当你的数据里去到了几百上千万，SQL 优化的价值就体现出来了！因此稍微有些经验的同学都知道，怎么让 MySQL 查询语句又快又好是一件很重要的事情。要让 SQL 又快又好的前提是，我们知道它「病」在哪里，而 explain 关键字就是 MySQL 提供给我们的一把武器！
          
           最完整的Explain总结，妈妈再也不担心我的SQL优化了
          
           在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）
          
           几个必须掌握的SQL优化技巧(三)：Explain分析执行计划
          
           在应用的开发过程中，由于开发初期的数据量一般都比较小，所以开发过程中一般都比较注重功能上的实现，但是当完成了一个应用或者系统之后，随着生产数据量的急剧增长，那么之前的很多sql语句的写法就会显现出一定的性能问题，对生产的影响也会越来越大，这些不恰当的sql语句就会成为整个系统性能的瓶颈，为了追求系统的极致性能，必须要对它们进行优化。
          
           最完整的Explain总结，SQL优化不再困难！
          
           在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）两个变种explain extended会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain  中的i
          
           MySQL数据库性能优化由浅入深（表设计、慢查询、SQL索引优化、Explain分析、Show Profile分析、配置优化）
          
           通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解
          
           SQL 手册-实用 SQL 语句-EXPLAIN 和执行计划
          
           与多数数据库系统类似，PolarDB-X在处理SQL时，会通过优化器生成执行计划，该执行计划由关系操作符构成一个树形结构，反映PolarDB-X如何执行SQL语句；不同的是，PolarDB-X本身不存储数据，更侧重考虑分布式环境中的网络IO开销，将运算下推到各个分库（如RDS MySQL）执行，从而提升SQL执行效率。用户可通过EXPLAIN命令查看SQL的执行计划。
          
           用 Explain 命令分析 MySQL 的 SQL 执行
          
           在上一篇文章《MySQL常见加锁场景分析》中，我们聊到行锁是加在索引上的，但是复杂的 SQL 往往包含多个条件，涉及多个索引，找出 SQL 执行时使用了哪些索引对分析加锁场景至关重要。