ORDERBY 時間戳性能問題

我有一些處理儲存在POINT. 無序一切正常（查詢 500 萬條文章和 100 萬使用者數據庫大約需要 0.6 毫秒，用於測試），但添加時會進入 13 秒ORDER BY created_at DESC。）

子句中的所有欄位WHERE都有索引。 Created_at索引是DESC排序。

交易是恢復使用者在地理範圍內送出的文章。

SET @radius = 30;

SET @center = GeomFromText('POINT(12.005 -1.001)');
SET @r = @radius/69.1;
SET @bbox = CONCAT('POLYGON((', 
 X(@center) - @r, ' ', Y(@center) - @r, ',', 
 X(@center) + @r, ' ', Y(@center) - @r, ',', 
 X(@center) + @r, ' ', Y(@center) + @r, ',', 
 X(@center) - @r, ' ', Y(@center) + @r, ',', 
 X(@center) - @r, ' ', Y(@center) - @r, '))' 
);

SELECT * FROM posts INNER JOIN users ON posts.user_id = users.user_id 
WHERE posts.user_id IN (
   SELECT user_id  FROM users_contact WHERE
   Intersects( coordinates, GeomFromText(@bbox) ) 
   AND SQRT(POW( ABS( X(coordinates) - X(@center)), 2) + POW( ABS(Y(coordinates) - Y(@center)), 2 )) < @r )
   AND users.user_status=1 AND posts.status=1 AND posts.created_at <= '2016-10-24 10:30:53' AND posts.context = '1'
# ORDER BY posts.created_at DESC 
LIMIT 30;

嘗試INNER JOIN代替IN子句，但結果是一樣的。

將座標放在 users 表中而不是 users_contact 中是相同的。

謝謝，MySQL 5.5

這是正常情況

• 當您在沒有 ORDER BY - LIMIT 30 的情況下執行查詢時；工作完美 - 它在前 30 條記錄後停止查詢，任何 30 條記錄
• 當您添加 ORDER BY - MySQL 首先將所有記錄發送到臨時表（如果數據集很大），然後對其進行排序，然後返回給您 30 條正確的記錄

所以在巨大的數據集上 - 它按預期工作。解決方案真的只有一個 - 分析查詢並減少查詢返回的總記錄數，如何 - 取決於業務邏輯

例如 - posts.created_at <= ‘2016-10-24 10:30:53’ （表示昨天）從 5 000 000 返回所有記錄，但如果添加 posts.created_at >= ‘2016-09-24 10:30 :53’ 它只返回月份的數據等 - 減少記錄數量的任何合法方式。

第二個建議取決於您的表結構，有時將 SELECT * 替換為 SELECT “WHAT REALLY NEED” 可能會顯著提高速度 - 例如列 BLOB 或 TEXT 之一，因此從 SELECT 中排除此列 - 在對許多人進行排序之前減少數據大小. 多次。即使此 BLOB 列需要您 - 您也可以通過頁面中的第二個查詢按 ID（單條記錄）請求它

OK 發現了真正的問題。在優化where子句後，在select中轉義了一些數據，問題是一樣的。

事實上，在我的大型 post 表中，我有一個基數非常低的上下文列（5 百萬條記錄大約為 7）。當然，這個列中的索引從未使用過。因此，在此列上沒有 where 子句的查詢是可以的，而且很糟糕。

將為此打開另一個問題。謝謝

引用自：https://dba.stackexchange.com/questions/153203