Distinct和Group By在SQL查询中是两个常用的关键字。它们的作用是用于去除重复值和进行分组操作。 Distinct用于从查询结果中去除重复行，只返回唯一的结果。它可以应用于一个或多个列。例如，SELECT DISTINCT column1, column2 FROM table; 将返回唯一的column1和column2的组合。 Group By用于将查询结果按照一个或多个列进行分组。它将相同值的行放在一起，并对每组应用聚合函数（如SUM、COUNT、AVG等）。例如，SELECT c

我有两个表格如下。\n\'订单\'表格有21886行。\n

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reg_date` (`reg_date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `order_detail_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `order_detail_id` int(11) NOT NULL,
  `prod_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_detail_id` (`order_detail_id`,`prod_id`),
  KEY `idx_order_id` (`order_id`,`order_detail_id`,`prod_id`)
) ENGINE=InnoDB AUTO_INCREMENT=572375 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

\n我的问题如下。\nMariaDB [test]> explain\n -> SELECT DISTINCT A.id\n -> FROM order A\n -> JOIN order_detail_products B ON A.id = B.order_id\n -> ORDER BY A.reg_date DESC LIMIT 100, 30;\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+\n| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+\n| 1 | SIMPLE | A | index | PRIMARY | idx_reg_date | 8 | NULL | 22151 | Using index; Using temporary; Using filesort |\n| 1 | SIMPLE | B | ref | idx_order_id | idx_order_id | 8 | bom_20140804.A.id | 2 | Using index; Distinct |\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+\n2 rows in set (0.00 sec)\nMariaDB [test]> explain\n -> SELECT A.id\n -> FROM order A\n -> JOIN order_detail_products B ON A.id = B.order_id\n -> GROUP BY A.id\n -> ORDER BY A.reg_date DESC LIMIT 100, 30;\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+\n| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+\n| 1 | SIMPLE | A | index | PRIMARY | idx_reg_date | 8 | NULL | 65 | Using index; Using temporary |\n| 1 | SIMPLE | B | ref | idx_order_id | idx_order_id | 8 | bom_20140804.A.id | 2 | Using index |\n+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+\n\n如上所述，两个查询返回相同结果，但使用DISTINCT太慢（解释中显示了太多行）。\n有什么区别？