mysql - 如何提高mysql查询的性能？

Question

我正在更新表格中的一些行。查询需要很长时间才能执行。如何提高以下查询的执行性能？

update org_products op
inner join access_history
    on access_history.access_key = op.id and access_history.access_type = "OrgProduct.View"
set views =
(
    select count(access_key)
    from access_history
    where access_history.access_key = op.id and
          access_history.created_at >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) and
          access_history.access_type = "OrgProduct.View" and
          access_history.product_id = op.product_id
    GROUP BY  access_history.product_id
)
where access_history.access_key = op.id and
      access_history.access_type = "OrgProduct.View";

更新 SHOW CREATE TABLE access_history;

的输出

 'access_history', 'CREATE TABLE `access_history` (
\n  `id` bigint(20) NOT NULL AUTO_INCREMENT,
\n  `product_id` bigint(20) unsigned NOT NULL,
\n  `access_type` varchar(50) DEFAULT NULL,
\n  `access_key` varchar(50) DEFAULT NULL,
\n  `access_key_full` varchar(200) DEFAULT NULL,
\n  `client_ip_addr` varbinary(16) DEFAULT NULL,
\n  `userid` bigint(20) unsigned DEFAULT NULL,\n  `username` tinytext,
\n  `anon_user_id` bigint(20) unsigned DEFAULT NULL,
\n  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
\n  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  PRIMARY KEY (`id`),
\n  KEY `fk_access_history_has_product_product1_idx` (`product_id`),
\n  KEY `idx_access_history_prod_type_key` (`product_id`,`access_type`,`access_key`),
\n  CONSTRAINT `fk_access_history_has_product_product1_idx` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON 
DELETE NO ACTION ON UPDATE NO ACTION\n) ENGINE=InnoDB AUTO_INCREMENT=1290353 DEFAULT CHARSET=utf8'

SHOW INDEX from access_history; 的输出

EXPLAIN 查询的输出

需要对查询进行哪些更改才能提高性能？

感谢任何帮助!谢谢!

Answer 1

这里有两种变体:

1) 您通过 CRON 定期执行讨论的查询 - 因此您想要更新其访问历史记录在过去 90 天内更改的所有产品。尝试下面的查询(你最好提前计算开始日期然后使用这个常量值——使用 DATE_SUB() 会让 MySQL 认为这是一个动态值并且每次都会计算它，不会注意到它本质上是常数):

UPDATE org_products op
  SET views = (SELECT COUNT(*) FROM access_history
        WHERE access_key = op.id
            AND access_type = "OrgProduct.View"
    )
    WHERE op.id IN (SELECT access_key FROM access_history 
        WHERE access_type = "OrgProduct.View"
            AND created_at >= "YYYY-MM-DD"
    )

2) 在每次插入表 access_history 后执行讨论的查询 - 尝试以下操作(ACCESS_KEY_VALUE 是您刚刚插入到表 access_history 中的内容):

UPDATE org_products op
    SET views = (SELECT COUNT(*) FROM access_history
        WHERE access_key = ACCESS_KEY_VALUE
            AND access_type = "OrgProduct.View"
    )
    WHERE op.id = ACCESS_KEY_VALUE

在这两种情况下，我建议您将所有不同的字符串从 access_type 列移动到单独的表中，将该列转换为 INT 并使其成为新表的外键。原因是索引和比较数值比比较字符串更快。