我有一個大訊息資料庫，有 240 萬行：
Showing rows 0 - 24 (2455455 total, Query took 0.0006 seconds.)
訊息，所以我需要更快地加載對話，對于加載的對話較少的用戶（用戶有 3.2k 對話）：
Showing rows 0 - 24 (3266 total, Query took 0.0345 seconds.) [id: 5009666... - 4375619...]
對于具有大量對話的用戶，加載速度較慢（用戶有 40k 對話）：
Showing rows 0 - 24 (40296 total, Query took 5.1763 seconds.) [id: 5021561... - 5015545...]
我正在為這些列使用索引鍵：
id, to_id, from_id, time, seen
資料庫表：
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;
我正在使用這個查詢：
SELECT
*
FROM
messages,
(
SELECT
MAX(id) as lastid
FROM
messages
WHERE
(
messages.to_id = '1' -- ID to compare with (logged in users's ID)
OR messages.from_id = '1' -- ID to compare with (logged in users's 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
我不知道如何讓有很多對話的用戶更快，我應該重新創建資料庫結構。
uj5u.com熱心網友回復：
筆記：
- 使用 UNION 代替 OR（見下文）
- 有多余的鍵。是
PRIMARY KEY鑰匙，所以折騰KEY(id) - 不要盲目地索引每一列；而是使用查詢來確定哪些索引，尤其是復合索引，實際上是有用的。
CONCATGROUP BY在和中是不必要的并且可能適得其反ORDER BY。INT忽略長度欄位。您擁有的值僅限于 20 億個值。（這對于seen假設是 0 或 1 來說太過分了？）- 使用新語法：JOIN..ON。
- 如果
seen只是真/假，則將索引扔在上面。（或者告訴我你認為會從中受益的查詢。）
CONCAT-LEAST-GREATEST——這是為了構造一個“friends_id”？也許你真的想要一個“conversation_id”？目前，兩個用戶永遠不能有多個“對話”，對嗎？
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
并擁有這些“覆寫”和“復合”索引：
INDEX(to_id, from_id, id)
INDEX(from_id, to_id, id)
并折騰 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. 但它不會。我看到了一些解決方案：
- Add a
conversation_idand dedup using it. (Meanwhile, I changedUNION DISTINCTtoUNION ALL, making the query a little faster without changing the results.) - Put the output of my query into a temp table with (from_id, to_id, latestid); then do your
CONCAT-LEAST-GREATESTtrick to dedup conversations; finally do the JOIN back tomessagesto get the rest of the columns. - That temp table technique makes it easier to write and debug. My 3rd suggestion is simply cramming the pieces together to do it is a single (hard-to-read) query with Selects nested at 3 levels deep.
uj5u.com熱心網友回復：
嗯，也許您可??以嘗試向表中添加索引：https ://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#:~:text =Creating Indexes&text= statement to create index， index must be distinct。確保按您查詢的行添加組合索引。
如果這不能改善您的查詢時間，那么應該改進查詢。
uj5u.com熱心網友回復：
您還可以使用time例如對訊息表進行磁區。
磁區是資料庫（在本例中為 MySQL）將其實際資料拆分為單獨的表的一種方式，但仍被 SQL 層視為單個表。在 MySQL 中進行磁區時，最好找到一個自然的磁區鍵
https://www.percona.com/blog/2017/07/27/what-is-mysql-partitioning/#:~:text=So, What is MySQL Partitioning,find a natural% 20partition key。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/444079.html
上一篇：基于兩個單元格值的SQL過濾表
下一篇：使SQL查詢優化以更快地回傳結果