mysql order by count * 导致filesort

Question

我有张表

1. t_order 订单表 -- 每卖出一件商品就会在订单表有一条记录
2. t_product 商品表

需求 查询销售排行前10的商品

SELECT o.product_id,COUNT(*) AS num 
FROM t_order o
GROUP BY o.product_id
ORDER BY COUNT(*) DESC

问题 通过explain 发现 有一个致命的问题 mysql 使用了临时表和 filesort 这种情况表数据一旦上了百万，效率会断崖式下降

请问如何解决这个order by 问题

最后附上建表sql
CREATE TABLE t_order (
order_id int(11) NOT NULL AUTO_INCREMENT,
product_id int(11) DEFAULT NULL,
deal_price int(11) DEFAULT NULL,
PRIMARY KEY (order_id),
KEY idx_product_id (product_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

insert into t_order(order_id,product_id,deal_price) values (1,2,18);
insert into t_order(order_id,product_id,deal_price) values (2,3,30);
insert into t_order(order_id,product_id,deal_price) values (3,2,9);

CREATE TABLE t_product (
product_id int(11) NOT NULL AUTO_INCREMENT,
product_name varchar(32) DEFAULT NULL,
PRIMARY KEY (product_id),
KEY idx_product_id (product_id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

insert into t_product(product_id,product_name) values (2,'葡萄');
insert into t_product(product_id,product_name) values (3,'苹果');
insert into t_product(product_id,product_name) values (4,'香蕉');

Answer 1

这种sql避免不了 "Using temporary;" 和 "Using filesort"，这是order by 非基表数据时会产生的，
基于group by生成temporary的数据执行order by ，就是filesort了。
一般情况下，这个查询应该要带条件，如果是订单表的话，一般会按某个时间区间来进行统计，这个时候统计的数据量会减少很多。
还有，如非必要，一般不建议在sql中使用order by，有时甚至可以取出数据来再进行排序，
你可以尝试把order by 去掉，会发现 "Using temporary;" 和 "Using filesort"都没有了，
另外，你可以测试一下下面这个sql，我这里数据量不够比较不出效果

select * from 
(SELECT o.product_id,COUNT(1) AS num 
FROM t_order o
GROUP BY o.product_id) x
ORDER BY 2 DESC