Mysql
優化mysql中大量行的查詢
我在 MySQL 中有以下數據庫表:
並且正在執行以下查詢
SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, `scratch_tickets`.`prize`, `scratch_tickets`.`is_win`, `scratch_tickets`.`game_id` FROM (`scratch_tickets`) WHERE `scratch_tickets`.`created_at` >='2015-02-12 00:00:00' and created_at<='2015-02-18 23:59:59' AND `scratch_tickets`.`status` = 4 LIMIT 50;
現在解釋給出以下結果
該查詢需要將近 35 秒才能執行,當我將它與 PHP 一起使用時,它會超時。在這裡,我認為正在使用的索引。我怎樣才能優化它以獲得更好的性能?
該表已經有 25708614 條記錄。
您的查詢:
SELECT scratch_tickets.id AS ticket_id, -- more columns FROM (scratch_tickets) WHERE scratch_tickets.created_at >='2015-02-12 00:00:00' AND created_at <= '2015-02-18 23:59:59' AND scratch_tickets.status = 4 LIMIT 50 ;
需要一個簡單的索引
(status, created_at)
:ALTER TABLE scratch_tickets ADD INDEX status_created_at_idx -- pick a name for the index (status, created_at) ;
在測試查詢正在使用新索引並且效率更高之後,您可能可以刪除舊
status
索引。(*“可能”*是因為您可能有一些罕見的查詢,這些查詢從索引中獲益良多,(status)
但在使用該索引時速度很慢(status, created_at)
。)還有一些注意事項:
LIMIT
沒有ORDER BY
是不確定結果的秘訣。始終使用ORDER BY
,這樣您就可以控制選擇返回的 (50) 行(除非您真的不介意半隨機結果或存在嚴重的性能問題)。- 另請注意,添加或更改
ORDER BY
表達式可能會影響性能。ORDER BY created_at
對於特定查詢,如果您有orORDER BY created_at DESC
或,將有效使用索引ORDER BY created_at, ticket_id
。如果使用不同的列或表達式,效率可能不會那麼好。- 而不是包含 (
>=
and<=
- orBETWEEN
),對日期時間類型使用包含獨占範圍更容易且不易出錯。這樣,您就不會太在意類型是否為DATE
,DATETIME
或者TIMESTAMP
它是否具有秒、毫秒或微秒精度。- 使用別名查詢更清晰。查看重寫:
SELECT st.id AS ticket_id, -- more columns FROM scratch_tickets AS st WHERE at.created_at >= '2015-02-12 00:00:00' -- you could even use '2015-02-12' AND st.created_at < '2015-02-19 00:00:00' -- notice the < AND st.status = 4 ORDER BY <some_expression> LIMIT 50 ;