时间戳

首页 > 解决方案 > 通过数百万条记录表连接优化 MySQl 查询性能

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

标签： mysqlindexingquery-optimization

解决方案

  • 查询格式不正确；在你解决这个问题之前，我们不应该讨论它。阅读“only_full_group_by”。
  • 可能永远不会“正确”同时拥有GROUP BY和。DISTINCTSELECT
  • 你有“爆炸-内爆”综合症。这是当您执行 a 时JOIN，它会构建一个大型临时表，然后是 aGROUP BY以缩小到您可能必须在其中一个表中开始的内容。
  • 结果集仍然很大；你打算如何处理结果？
  • 你真的应该PRIMARY KEY在每张桌子上都有一个。如果这是唯一的(history_id, product_id)，请将其作为 PK。（请注意，我交换了顺序，如其他评论中所述。）
  • year_id- 那是 a 的归一化YEAR吗？不值得。只需将年份放在表中即可；没有额外的查找。
  • 另一张桌子有多大？（我可能在咆哮错误的优化。）

  • 这可能会为您提供相同的数据，但速度要快得多：

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

您将不得不对表格进行一些结构更改；计划一些停机时间。

如果您只需要有关 Hammers 的数据，请向我们展示会WHERE限制输出的子句。针对该查询进行优化可能与您的问题中的 30 分钟查询有很大不同！

修复我和其他人建议的大部分内容，然后用新的架构等返回一个新问题。（这个问答太混乱了，无法继续。）

推荐阅读