不要随随便便的distinct和orderby
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了不要随随便便的distinct和orderby,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4090字,纯文字阅读大概需要6分钟。
内容图文
有客户反应网站后台订单相关查询非常慢,通过程序拿到了相关sqlexplainexplainSELECTDISTINCT(o.orders_id),o.oa_order_id,customers_email_address,o.order_type
有客户反应网站后台订单相关查询非常慢,通过程序拿到了相关sql
explain
explain SELECT DISTINCT(o.orders_id), o.oa_order_id, customers_email_address, o.order_type, ot.text AS total_value, o.track_number, o.date_purchased, o.orders_status, o.specialOperate, o.isSpecialParent, o.pay_ip, o.supply_id, o.products_center_id, o.split_code, o.is_import, o.shipDays,o.delivery_country,o.use_coupon ,o.payment_method FROM orders AS o LEFT JOIN orders_total AS ot ON ot.orders_id=o.orders_id AND ot.class='ot_total' WHERE 1 AND o.is_delete = 0 AND o.date_purchased >= '2013-09-30 10:00:00' AND (o.specialOperate = 0 OR o.isSpecialParent=1) ORDER BY date_purchased DESC, orders_id DESC LIMIT 0, 20; +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+----------------------------------------------+ | 1 | SIMPLE | o | range | date_purchased | date_purchased | 9 | NULL | 606632 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+----------------------------------------------+ 2 rows in set (0.05 sec)发现索引使用正常,执行状态中发现有Copying to tmp table on disk状态,执行时间超过50s。
使用profiling发现Copying to tmp table on disk占用了大部分性能。
仔细查看该语句并和开发讨论,发现distinct和ORDER BY date_purchased DESC, orders_id DESC中,distinct关键字可以省略,而且ORDER BY date_purchased DESC, orders_id DESC可以去掉后面的orders_id desc(开发对多个字段排序不理解).
去掉后,再次explain
mysql> EXPLAIN -> SELECT o.orders_id, o.oa_order_id, customers_email_address, o.order_type, ot.text AS total_value, o.track_number, o.date_purchased, o.orders_status, o.specialOperate, o.isSpecialParent, o.pay_ip, o.supply_id, o.products_center_id, o.split_code, o.is_import, o.shipDays,o.delivery_country,o.use_coupon ,o.payment_method FROM orders AS o LEFT JOIN orders_total AS ot ON ot.orders_id=o.orders_id AND ot.class='ot_total' WHERE 1 AND o.is_delete = 0 AND o.date_purchased >= '2013-09-30 10:00:00' AND (o.specialOperate = 0 OR o.isSpecialParent=1) -> ORDER BY date_purchased DESC LIMIT 0, 20; +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+-------------+ | 1 | SIMPLE | o | range | date_purchased | date_purchased | 9 | NULL | 606632 | Using where | | 1 | SIMPLE | ot | ref | idx_orders_total_orders_id,class | idx_orders_total_orders_id | 4 | banggood.o.orders_id | 19 | | +----+-------------+-------+-------+----------------------------------+----------------------------+---------+----------------------+--------+-------------+ 2 rows in set (0.01 sec)索引使用情况不变,但是下面的profiling,发现结果瞬间出来,执行时间不过0.003s,而且已经没有了Copying to tmp table on disk状态。
总结:1.因为distinct关键字需要对结果集进行去重,如果天然无重复,是不需要加上去重关键字的,上面的例子结果集有将近百万,去重字段又多,在tmp_table_size以及sort_buffer_size中排序已经不够用,所以将结果集复制到磁盘,严重影响速度
2. order by a,b 开发人员很喜欢用类似的语句,尽管对功能没有多大作用
本文出自 “原下” 博客,,请务必保留此出处
内容总结
以上是互联网集市为您收集整理的不要随随便便的distinct和orderby全部内容,希望文章能够帮你解决不要随随便便的distinct和orderby所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。