我正在使用 MySQL 8.0，并且在要優化的大表上有一個慢查詢。
該表包含1100 萬行資料及其結構：
CREATE TABLE `ccu` (
`id` bigint NOT NULL,
`app_id` int NOT NULL,
`ccu` int NOT NULL,
`audit_create` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `ccu_game_create_time_2a10bc69_idx` (`app_id`,`audit_create`) USING BTREE,
KEY `ccu_audit_create_idx` (`audit_create`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
我的查詢是：
SELECT app_id, DATE(audit_create) cal_day, MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu
FROM ccu
WHERE audit_create BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW())
GROUP BY app_id, DATE(audit_create)
查詢運行超過 2 秒。我添加條件between ... and ...來過濾有用的資料。但是，存盤的資料audit_create是格式yyyy-MM-dd HH:mm:ss，我必須使用該date函式但根據執行計劃只有where條件使用索引（仍然有臨時表），該group by子句根本不使用任何索引。
我無權更改表結構以添加日期列。是否可以優化查詢以降低查詢時間？
uj5u.com熱心網友回復：
我能夠Using temporary通過添加運算式索引來消除：
mysql> alter table ccu add key bk1 (app_id, (cast(audit_create as date)));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain SELECT app_id, DATE(audit_create) cal_day,
MAX(ccu) pcu, ROUND(AVG(ccu)) id_acu
FROM ccu
WHERE date(audit_create) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 29 DAY) AND DATE(NOW())
GROUP BY app_id, cast(audit_create as date)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ccu
partitions: NULL
type: index
possible_keys: bk1
key: bk1
key_len: 8
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
不幸的是，該 EXPLAIN 報告顯示它將使用type: index，這是一種索引掃描，換句話說，它將檢查 1100 萬個索引條目中的每一個。它可能會比您的查詢更糟。
我唯一的其他建議是每天運行一次此查詢并將結果存盤在匯總表中。每天運行一次 2 秒查詢，以便您可以快速獲得聚合結果應該是可以接受的。但是您說您沒有權限添加列，所以我想您也沒有權限添加表。
在這種情況下，請購買一臺速度更快、記憶體更大的計算機。
uj5u.com熱心網友回復：
微不足道的改進： DATE(NOW())-->CURDATE()
主要改進：
擺脫id和改變
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `ccu_game_create_time_2a10bc69_idx` (`app_id`,`audit_create`) USING BTREE,
只是
PRIMARY KEY (`app_id`,`audit_create`),
這避免了對每一行的二次查找。
在 29 天的范圍內，似乎有 240 萬行（共 1100 萬行）。優化器必須決定是否使用索引（它確實這樣做了），但要承受 240 萬次額外查找，而不是掃描所有 1100 萬行，需要額外的排序。
要檢查的另一件事是innodb_buffer_pool_size. 如果表太大以至于無法放入快取中，則可能存在大量 I/O。（同樣，我的索引更改將對此有所幫助。）
是的，與我的建議無關，Bill 生成的列可能會增加更多性能。
注意：
您的范圍是 29 天 1 秒。
比爾的范圍是 30 天。
無論 的資料型別如何，這都可以在今天早上前audit_create29 天得到：
WHERE audit_create >= CURDATE() - INTERVAL 29 DAY
AND audit_create < CURDATE()
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/430028.html
上一篇：mysql查詢兩個日期列內的資料