主页 / dba / 问题 / 182259
Accepted
Putnik
Asked: 2017-08-01 05:40:17 +0800 CST

如何使用 join 和 order-by 优化这个选择？

  • 772

我们有两个表：

 CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` int(10) unsigned DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
....
  `subject_id` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  KEY `created` (`created`),
  KEY `text_id` (`text_id`) USING BTREE,
  KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=237542180 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

第二个：

CREATE TABLE `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` char(150) DEFAULT NULL,
  `reg_time` int(10) unsigned DEFAULT '0',
  `password` char(255) DEFAULT NULL,
...................
  `moderation` int(1) unsigned NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created` (`reg_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123585 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

消息有 ~49M 记录，用户有 13k。数据库引擎：Aurora（兼容 MySQL）5.6.10a

非常长的请求是

SELECT messages.*, users.administrator_group_id FROM messages 
    LEFT JOIN users ON messages.user_id = users.id 
    ORDER BY messages.id desc LIMIT 0,20

如果我不运行此请求，order by则需要 14-16 秒。它order需要超过 5 分钟的时间。

我正在考虑更改业务逻辑以避免此请求并限制记录集，messages例如按消息日期，但想知道是否有任何方法可以在与原样相同的硬件上加速它。

mysql optimization

1 个回答

  1. Best Answer

    我从未使用过 Aurora，与 MySQL 可能存在差异，但有一种方法在 MySQL 中经常用于类似问题，当执行计划不是最佳时，即当它首先执行连接然后必须执行ORDER BY大的中间结果集。

    我们尝试首先LIMIT将结果放入派生表中，然后再JOIN返回，而不是连接这两个表。这样索引将用于 theORDER BY - LIMIT然后它只需要在第二个表中进行 N 次查找（在本例中为 20 次）：

    SELECT 
    m.*, 
    u.administrator_group_id 
FROM 
    ( SELECT id 
      FROM messages 
      ORDER BY id DESC 
      LIMIT 20
    ) AS mi
  JOIN 
    messages AS m ON m.id = mi.id
  LEFT JOIN 
    users AS u ON m.user_id = u.id 
ORDER BY 
    mi.id DESC ;

    还有一个变体：

    SELECT 
    m.*, 
    u.administrator_group_id 
FROM 
    ( SELECT mi.* 
      FROM messages AS mi 
      ORDER BY mi.id DESC 
      LIMIT 20
    ) AS m
  LEFT JOIN 
    users AS u ON m.user_id = u.id 
ORDER BY 
    m.id DESC ;

    尝试两者并检查执行计划和性能。在任何合理的硬件中，仅从一个或两个表中获取 20 行并使用索引的查询应该非常高效。在毫秒范围内，而不是秒或分钟。

    • 5

相关问题