MySQL数据库查询需要耗费一定时间
P粉764836448
2023-08-31 22:29:19
<p>我有一个大的消息数据库，有2.4万行：</p>
<pre class="brush:php;toolbar:false;">显示行0-24（共2455455行，查询耗时0.0006秒）。</pre>
<p>消息，所以我需要更快地加载对话，对于有较少对话的用户，加载如下（用户有3.2k对话）：</p>
<pre class="brush:php;toolbar:false;">显示行0-24（共3266行，查询耗时0.0345秒）[id：5009666... - 4375619...]。</pre>
<p>对于有大量对话的用户，加载较慢（用户有40k对话）：</p>
<pre class="brush:php;toolbar:false;">显示行0-24（共40296行，查询耗时5.1763秒）[id：5021561... - 5015545...]。</pre>
<p>我对这些列使用索引键：</p>
<pre class="brush:php;toolbar:false;">id，to_id，from_id，time，seen</pre>
<p>数据库表：</p>
<pre class="brush:php;toolbar:false;">CREATE TABLE `messages` (
`id` int(255) NOT NULL,
`to_id` int(20) NOT NULL,
`from_id` int(20) NOT NULL,
`message` longtext NOT NULL,
`time` double NOT NULL,
`seen` int(2) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `messages` (`id`, `to_id`, `from_id`, `message`, `time`, `seen`) VALUES
(2, 6001, 2, 'Hi there', 1587581995.5222, 1);
ALTER TABLE `messages`
ADD PRIMARY KEY (`id`),
ADD KEY `time_idx` (`time`),
ADD KEY `from_idx` (`from_id`),
ADD KEY `to_idx` (`to_id`),
ADD KEY `seenx` (`seen`),
ADD KEY `idx` (`id`);
ALTER TABLE `messages`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;</pre>
<p>我使用以下查询：</p>
<pre class="brush:php;toolbar:false;">SELECT
*
FROM
messages,
(
SELECT
MAX(id) as lastid
FROM
messages
WHERE
(
messages.to_id = '1' -- 与之比较的ID（已登录用户的ID）
OR messages.from_id = '1' -- 与之比较的ID（已登录用户的ID）
)
GROUP BY
CONCAT(
LEAST(messages.to_id, messages.from_id),
'.',
GREATEST(messages.to_id, messages.from_id)
)
) as conversations
WHERE
id = conversations.lastid
ORDER BY
messages.id DESC</pre>
<p>我不知道如何使具有大量对话的用户更快，我是否应该重新创建数据库结构。</p>
嗯，也许你可以尝试为你的表添加索引：https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#:~:text=Creating%20Indexes&text=The%20statement%20to%20create%20index,the%20index%20must%20be%20distinct。 确保按照你查询的行添加组合索引。
如果这样做没有改善你的查询时间，那么应该改进查询。
注意：
CONCAT-LEAST-GREATEST - 这是为了构造一个“friends_id”？也许你真正想要一个“conversation_id”？目前，两个用户永远不会有多个“conversation”，对吗？
如果确实需要，为conversation_id创建一个新列。（目前，GROUP BY是低效的。）下面的代码消除了对这样一个id的需求。
( SELECT lastid FROM ( ( SELECT from_id, MAX(id) AS lastid FROM messages WHERE to_id = ? GROUP BY from_id ) UNION DISTINCT ( SELECT to_id, MAX(id) AS lastid FROM messages WHERE from_id = ? GROUP BY to_id ) ) AS x ) AS conversations并且拥有这些“covering”和“composite”索引：
删除KEY(to_id)，KEY(from_id)，因为我的新索引可以处理这两个索引的所有其他任务。
我认为这具有相同的效果，但运行速度更快。
将它们组合起来：
SELECT * FROM ( ( SELECT from_id AS other_id, MAX(id) AS lastid FROM messages WHERE to_id = ? GROUP BY from_id ) UNION ALL ( SELECT to_id AS other_id, MAX(id) AS lastid FROM messages WHERE from_id = ? GROUP BY to_id ) ) AS latest JOIN messages ON messages.id = latest.lastid ORDER BY messages.id DESC(加上这两个索引)
更多
我曾经错误地认为UNION DISTINCT可以替代对conversation_id的需求。但事实并非如此。我立即看到了一些解决方案：