mysql - 优化技术以在不使用 MySql 中的连接的情况下从两个大表中获取数据

Question

我在 MySQL 上工作，遇到服务器加载时间问题。这是我的表的结构和查询:

我需要从 message(超过 560 万行)表中获取 50 行。 message表有(id, description, author_id, timeline_id)等属性，条件是，每个author_id存在于users中(大于38K行)表。

建表脚本为:

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(11) NOT NULL,
  `post_description` text() NOT NULL
  `author_id` int(11) NOT NULL,
  `timeline_id` int(11) NOT NULL,
);

ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`);

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL,
  `username` text() NOT NULL
);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

为此，我使用以下 SQL 查询:

SELECT 
  m.id
  , m.post_description
  , m.author_id
  , m.timeline_id
  , u.username 
FROM message m, users u 
WHERE m.timeline_id ='1868'
          AND m.id <= '28190' 
          AND m.author_id NOT IN (24974,7920,1498,9020,0)
          AND u.id=m.author_id 
ORDER BY m.id 
DESC LIMIT 0 ,51;

请给我建议以在尽可能短的时间内获得所需的行。

谢谢

Answer 1

这是一个简单的案例。您需要在 ( https://dev.mysql.com/doc/refman/5.7/en/create-index.html) 上使用索引:

messages.author_id

您不需要对用户建立索引，因为在 Mysql 中默认情况下主键已建立索引。

考虑使用查询计划，它可以帮助您识别性能问题: https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

通过以上所有设置，您的查询应该如下所示*:

SELECT 
  m.id
  , m.post_description
  , m.author_id
  , m.timeline_id
  , u.username 
FROM message m
join users u 
 on m.author_id = u.id
WHERE m.author_id NOT IN (24974,7920,1498,9020,0)
     AND m.timeline_id ='1868'
     AND m.id <= '28190' 
ORDER BY m.id 
DESC LIMIT 0 ,51;

*您的原始查询也会因上述索引更改而变得更快，但我认为使用隐式连接会使查询更具可读性。

已编辑:

根据@Panagiotis Kanavos，感谢 Panagiotis，您可能必须在查询计划中查看还有什么导致速度缓慢。您可能还需要 m.timeline_id 上的索引。

当然，创建索引后还需要分析表(https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html)

编辑2:

你也可以考虑使用外键，但这不是一个性能工具，它是一个完整性概念: https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

关于FK和性能你可以引用那个答案: Does introducing foreign keys to MySQL reduce performance