独特vs Group By [英] Distinct vs Group By

问题描述

我有两张这样的桌子。
'订单'表有21886行。

  CREATE TABLE`订单'（
`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，
 （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 
  

我的问题在这里。

  MariaDB [test]>解释
  - > SELECT DISTINCT A.id 
  - > FROM order A 
  - > JOIN order_detail_products B ON A.id = B.order_id 
  - > ORDER BY A.reg_date DESC LIMIT 100，30; 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------- + --------------------------------------- ------- + 
 | id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外| 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------- + --------------------------------------- ------- + 
 | 1 | SIMPLE | A | index | PRIMARY | idx_reg_date | 8 | NULL | 22151 |使用索引;使用临时;使用filesort | 
 | 1 | SIMPLE | B | ref | idx_order_id | idx_order_id | 8 | bom_20140804.A.id | 2 |使用索引;独特| 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------- + --------------------------------------- ------- + 
 2行（0.00秒）
 
 MariaDB [test]>解释
  - > SELECT A.id 
  - > FROM order A 
  - > JOIN order_detail_products B ON A.id = B.order_id 
  - > GROUP BY A.id 
  - > ORDER BY A.reg_date DESC LIMIT 100，30; 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------ + ------------------------------ + 
 | id | select_type |表| |键入| possible_keys |键| key_len | ref |行|额外| 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------ + ------------------------------ + 
 | 1 | SIMPLE | A | index | PRIMARY | idx_reg_date | 8 | NULL | 65 |使用索引;使用临时| 
 | 1 | SIMPLE | B | ref | idx_order_id | idx_order_id | 8 | bom_20140804.A.id | 2 |使用index | 
 + ------ + ------------- + ------- + ------- + -------- ------- + -------------- + --------- + ----------------- -  + ------ + ------------------------------ + 
 有什么区别？
解决方案
我相信你的select distinct很慢，因为你通过匹配另一个表。在大多数情况下，选择不同将会更快。但在这种情况下，由于您在另一个表的参数上匹配，所以索引被破坏并且速度要慢得多。
 
I have two tables like this.
The 'order' table has 21886 rows.
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
My question is here.
MariaDB [test]> explain
    -> SELECT DISTINCT A.id
    -> FROM order A
    -> JOIN order_detail_products B ON A.id = B.order_id
    -> ORDER BY A.reg_date DESC LIMIT 100, 30;
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+
| id   | select_type | table | type  | possible_keys | key          | key_len | ref               | rows  | Extra                                        |
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | A     | index | PRIMARY       | idx_reg_date | 8       | NULL              | 22151 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | B     | ref   | idx_order_id  | idx_order_id | 8       | bom_20140804.A.id |     2 | Using index; Distinct                        |
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+-------+----------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [test]> explain
    -> SELECT A.id
    -> FROM order A
    -> JOIN order_detail_products B ON A.id = B.order_id
    -> GROUP BY A.id
    -> ORDER BY A.reg_date DESC LIMIT 100, 30;
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+
| id   | select_type | table | type  | possible_keys | key          | key_len | ref               | rows | Extra                        |
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+
|    1 | SIMPLE      | A     | index | PRIMARY       | idx_reg_date | 8       | NULL              |   65 | Using index; Using temporary |
|    1 | SIMPLE      | B     | ref   | idx_order_id  | idx_order_id | 8       | bom_20140804.A.id |    2 | Using index                  |
+------+-------------+-------+-------+---------------+--------------+---------+-------------------+------+------------------------------+
Listed above, two queries returns same result but distinct is too slow(explain too many rows).
What's the difference?
解决方案
I believe your select distinct is slow because you broke the index by matching on another table.  In most cases, select distinct will be faster.  But in this case, since you are matching on parameters of another table, the index is broken and is much slower.

                        
这篇关于独特vs Group By的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！