优化MySQl查询性能，使用数百万条记录表联接

提问者：小点点

优化MySQl查询性能，使用数百万条记录表联接

我必须使用两个表连接在一个临时表中插入数据。作为一个有hist_data_app（大约3亿条）记录的表，它需要30分钟才能完全执行。我想知道我是如何更好地优化查询以使它更快。

第一个表包含一些特定数据的变更日志数据，另一个表包含所有与之相关的数据。下面是我的两个表创建语句。

CREATE TABLE `hist_data_app` (
    `product_id` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
    `application_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `year_id` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    `history_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `field_name` VARCHAR(60) NOT NULL COLLATE 'utf8_unicode_ci',
    `old_value` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    `new_value` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    `comments` TEXT NOT NULL,
    INDEX `ps` (`product_id`, `history_id`)
)

CREATE TABLE `histry_log` (
`history_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
`history_hash` CHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
`type` ENUM('products','brands','partnames','mc_partnames','applications') NOT NULL,
`user_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`stamp` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`source` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
`source_data` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
`description` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`history_id`),
INDEX `Types` (`type`)

）

这是我的解释结果

EXPLAIN
SELECT DISTINCT a.product_id
              , a.history_id
              , a.comments
              , a.field_name
           FROM history_log b
           JOIN hist_data_app a
             ON a.history_id = b.history_id
          GROUP 
             BY product_id;

id select_type table type   possible_keys key     key_len ref                       rows      Extra
 1 SIMPLE      a     ALL    NULL          NULL    NULL    NULL                      278327646 Using temporary; Using filesort
 1 SIMPLE      b     eq_ref PRIMARY       PRIMARY 8       LONGBOW_data.a.history_id         1 Using index

history_app_data表

product_id  application_id year_id history_id
598865023   12813220945 92  16777304
598865023   12813220945 93  16777304
598865023   12813221222 93  16777304
598865023   12815428123 94  16777304
598865023   12813221833 92  16777304
598865023   12813221833 93  16777304
598865023   12815457549 92  16777304
598865023   12815457549 93  16777304
598865023   12815457549 94  16777304

共1个答案

匿名用户

基于你对我的评论的反应，你应该与你现在已经设置的好。因为您主要考虑的是单个产品，所以您的Hist_Data_App表在第一个位置有product_id的索引，这也是您所希望的，但同时也有history_id来连接到日志表将会有所帮助。只需添加您的where子句并测试某些产品。

SELECT 
      hd.product_id, 
      hd.history_id, 
      hd.comments, 
      hd.field_name
   FROM 
      hist_data_app hd
   where
      -- or whatever single product ID you want
      hd.product_id = 12345

现在，我不知道您是否真的需要distinct，但可以添加回来没有问题。因为您没有从日志表中提取任何列，所以您甚至也不需要连接到该表。因为您没有聚合（如sum，count，avg等），所以不需要group by。我认为您只是在样例查询中这样做，同时将所有数据下拉，这样就不会返回3亿行。

现在，为了更好地按时进行测试，您可能需要对一些记录最多的产品进行采样，看看单个产品记录最多的时间有多长。为了得到它，你可能需要

SELECT 
      hd.product_id,
      count(*) totalRecsPerProduct
   FROM 
      hist_data_app hd
   group by
      hd.product_id
   order by
      count(*) desc
   limit 10

这将得到前10个产品与最多的记录在您的3亿，然后您可以运行之前的查询对这些，看看多少实时时间得到结果回来。我想你会看到性能是很好的去与您的单一需求的产品。