相关文章推荐
神勇威武的蟠桃  ·  Mysql中如果建立了索引，索引所占的空间随 ...·  3 周前    · 
忐忑的领带  ·  MySQL优化索引 - 废物大师兄 ·  3 周前    · 
英俊的蟠桃  ·  西游记最漂亮的女角色 – 快看漫画问答·  5 月前    · 
俊逸的肉夹馍  ·  城镇调查失业率首次纳入预期目标 - ...·  8 月前    · 
逃跑的高山  ·  【国产/SLG/】母上攻略重置版(二) ...·  1 年前    · 
纯真的苦瓜  ·  湖南省“十四五”规划和2035年远景目标的建 ...·  1 年前    · 
冷静的葫芦  ·  企院·分享 | ...·  1 年前    · 
Code  ›  EXPLAIN sql优化方法（2） Using temporary ; Using filesort-阿里云开发者社区
mysql mysql执行计划 sql优化 mysql创建索引
https://developer.aliyun.com/article/42450
跑龙套的弓箭
1 年前

优化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 执行时使用了哪些索引对分析加锁场景至关重要。
     
    推荐文章
    神勇威武的蟠桃  ·  Mysql中如果建立了索引，索引所占的空间随着数据量增长而变大，这样无论写入还是查询，性能都会有所下降，怎么处理？_一段时间后数据库中有近千万条数据 建立索引会因为写入量大
    3 周前
    忐忑的领带  ·  MySQL优化索引 - 废物大师兄
    3 周前
    英俊的蟠桃  ·  西游记最漂亮的女角色 – 快看漫画问答
    5 月前
    俊逸的肉夹馍  ·  城镇调查失业率首次纳入预期目标 - 党中央国务院信息 - 开江县人民政府
    8 月前
    逃跑的高山  ·  【国产/SLG/】母上攻略重置版(二) 中文版【PC+安卓模拟器】_哔哩哔哩_bilibili
    1 年前
    纯真的苦瓜  ·  湖南省“十四五”规划和2035年远景目标的建议正式发布（全文）-湖南省人民政府门户网站
    1 年前
    冷静的葫芦  ·  企院·分享 | 百年辉瑞(Pfizer):慢慢褪去家族外衣
    1 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号