如何优化查询以查找3列15亿行的重复项

e4eetjau  于 2021-06-20  发布在  Mysql
关注(0)|答案(0)|浏览(220)

我有超过15亿行的表,高负载项目。我想使用此查询按3列查找重复项:

SELECT v.product_id
     , v.value_id
     , COUNT(*) counter 
  FROM ann2_product_value AS v
 GROUP 
    BY v.product_id
     , v.value_id
     , v.set 
HAVING counter > 1
 ORDER 
    BY v.product_id
     , v.value_id 
 LIMIT 20;

创建表代码为:

CREATE TABLE `ann2_product_value` (
  `product_id` bigint(20) unsigned NOT NULL,
  `value_id` int(11) NOT NULL,
  `index` int(11) NOT NULL DEFAULT '0',
  `set` int(11) NOT NULL,
  `fitment` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`product_id`,`value_id`,`index`,`set`,`fitment`),
  KEY `value_id` (`value_id`),
  KEY `product_fitment` (`product_id`,`fitment`),
  CONSTRAINT `ann2_product_value_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `xcart_products` (`productid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_value_id_to_value` FOREIGN KEY (`value_id`) REFERENCES `ann2_value` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=1 STATS_AUTO_RECALC=0 ROW_FORMAT=DYNAMIC

问题是上面的查询速度非常慢,查询解释向我展示了以下结果:

+----+-------------+----------+-------+----------------------------------+----------+---------+------+------------+----------------------------------------------+
| id | select_type | table    | type  | possible_keys                    | key      | key_len | ref  | rows       | Extra                                        |
+----+-------------+----------+-------+----------------------------------+----------+---------+------+------------+----------------------------------------------+
|  1 | SIMPLE      | prod_val | index | PRIMARY,value_id,product_fitment | value_id | 4       | NULL | 1499189079 | Using index; Using temporary; Using filesort |
+----+-------------+----------+-------+----------------------------------+----------+---------+------+------------+----------------------------------------------+

我要做的主要事情是跳过第一条记录,在第一条记录重复之后选择。我该怎么做?请帮忙。谢谢。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题