Mysql

優化mysql中大量行的查詢

  • February 18, 2015

我在 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 <=- or BETWEEN),對日期時間類型使用包含獨占範圍更容易且不易出錯。這樣,您就不會太在意類型是否為DATEDATETIME或者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 ;

引用自:https://dba.stackexchange.com/questions/93216