不要随随便便的distinct和order by

有客户反应网站后台订单相关查询非常慢,通过程序拿到了相关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 开发人员很喜欢用类似的语句，尽管对功能没有多大作用更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Linux MySQL主从复制（Replication）配置order by多个字段对索引的影响相关资讯 Order By distinct

MySQL 强制操作以及order by 使用（04月20日）
MySQL优化order by导致的 using （11/06/2015 14:32:26）
关于order by的一些用法（04/17/2015 09:16:35）

MySQL中order by 结果不准确的问题（11/11/2015 12:14:35）
Oracle update和order by （07/24/2015 10:28:59）
MySQL新旧版本ORDER BY 处理方法（06/24/2014 15:18:10）

本文评论查看全部评论（0）