非常快（但有時非常慢）的 Mysql 查詢與始終有點慢的同一查詢的替代版本

我有一個查詢是更大查詢的一部分。我嘗試了兩個版本的查詢來嘗試改進頁面執行時間。一個版本幾乎可以立即執行.. 但有時（大約 50 分之一）執行速度非常慢（幾乎 7 分鐘），另一個版本執行速度稍慢（幾秒鐘）但始終如一（即，對於某些帳戶而言，執行速度不是非常慢）

版本 1（稍快，一致）

select max(created_at) from transactions where 
created_at < date_add('2022-02-27 06:00:00',interval -60 day) 
and customer_username = c.username and status = 'SUCCESS' 
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'

版本 2（即時，但在極少數情況下非常慢）

select created_at from transactions where 
created_at < date_add('2022-02-27 06:00:00',interval -60 day)
and customer_username = c.username and status = 'SUCCESS' 
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'
order by created_at desc limit 1

（總結一下。一個使用 max() 來獲取最新日期。另一個使用 order by with limit 1 來獲取最新日期）

這些是更大查詢中的內部/子查詢，在遠端數據庫上執行，我對其授予的權限非常有限，因此我無法利用我習慣於在內部 MS SQl 數據庫上使用的性能改進技巧（例如創建有用的臨時表）

有什麼我不知道的mysql技巧可以讓我兩全其美嗎？這些查詢是報告的一部分，它們可能執行一百次，所以我被困在報告要麼超時（或花費很長時間），因為一兩次非常慢的執行，或者數百次慢 -比即時執行。

---

---

額外的資訊…

CREATE TABLE `transactions` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `status` varchar(10) NOT NULL,
 `customer_username` varchar(128) NOT NULL,
 `ticket_id` int(11) DEFAULT NULL,
 `category` varchar(10) NOT NULL,
 `subcategory` varchar(10) NOT NULL,
 `channel` varchar(20) NOT NULL,
 `provider_id` varchar(10) DEFAULT NULL,
 `amount` decimal(20,8) NOT NULL DEFAULT '0.00000000',
 `balance_forward` decimal(20,8) NOT NULL DEFAULT '0.00000000',
 `currency` varchar(3) NOT NULL,
 `ip_address` varchar(128) DEFAULT NULL,
 `description` varchar(128) DEFAULT NULL,
 `response_code` varchar(128) DEFAULT NULL,
 `response_text` varchar(255) DEFAULT NULL,
 `created_at` datetime DEFAULT NULL,
 `updated_at` datetime DEFAULT NULL,
 `account_id` int(11) NOT NULL,
 `withdrawal_id` int(11) DEFAULT NULL,
 `deposit_id` int(11) DEFAULT NULL,
 `committed_at` datetime DEFAULT NULL,
 `provider_reference` varchar(255) DEFAULT NULL,
 `account_adjust_id` int(11) DEFAULT NULL,
 `payment_card_merchant_reference` varchar(255) DEFAULT NULL,
 `owner_category` varchar(255) DEFAULT 'CUSTOMER',
 `merchant_reference` varchar(255) DEFAULT NULL,
 `authentik_user_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `index_transactions_on_customer_username` (`customer_username`),
 KEY `index_transactions_on_ticket_id` (`ticket_id`),
 KEY `index_transactions_on_category_and_subcategory` (`category`,`subcategory`),
 KEY `index_transactions_on_deposit_id` (`deposit_id`),
 KEY `index_transactions_on_withdrawal_id` (`withdrawal_id`),
 KEY `index_transactions_on_account_adjust_id` (`account_adjust_id`),
 KEY `index_transactions_on_provider_reference` (`provider_reference`),
 KEY `index_transactions_on_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=233634386 DEFAULT CHARSET=utf8;

意圖： 包含此查詢的較大查詢的意圖是確定帳戶是否在日期 - 1 年和日期 - 60 天之間沒有下注（暗示他們在過去 60 天內下注或使用過他們的帳戶）這是基本上確定它是否是重新啟動的帳戶。較大的查詢（本身是查詢的一部分，太大而無法包含在此處）如下…

case when 
 (select created_at from transactions where 
  created_at < date_add('$todate',interval -60 day)
  and customer_username = c.username and status = 'SUCCESS' 
               and category in('TICKET','DEPOSIT')
  and subcategory = 'RESPONSE' order by created_at desc limit 1 ) 
 < date_add('$todate',interval -1 year)  
then 'reactivated' else 'active' end active_status

我無法更改索引或表，或創建臨時表（除其他外）

我不知道確切的數據庫版本或硬體規格。

我（似乎已經）通過添加查詢來檢查帳戶的創建日期來解決此問題。

and created_at >= (select created_at from customers where username = '__username__' )

（使用者名是 php 中的 string_replaced 與實際使用者名。我確實username = c.username先嘗試了，但沒有實現性能改進，所以我求助於 php 字元串替換（注意，這不是使用者提供的資訊，所以不是 sql 注入風險）

select created_at from transactions where 
created_at < date_add('2022-02-27 06:00:00',interval -60 day)

/* Added this line... */
and created_at >= (select created_at from customers where username = '__username__' )

and customer_username = '__username__' and status = 'SUCCESS' 
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'
order by created_at desc limit 1

對於在查詢的日期範圍內實際上沒有創建的帳戶，它的執行速度非常慢，因此非常慢的查詢返回 NULL

簡化（但沒有速度差異）：

date_add('2022-02-27 06:00:00',interval -60 day)

–>

'2022-02-27 - INTERVAL 60 DAY

這只是一個子查詢嗎？外部查詢涉及帶有別名的表c？那麼，這應該是改進的索引：

INDEX(customer_name, status, subcategory, category, created_at)

並放下你的電流KEY(customer_name)，因為這可以滿足它的需求。

你標記了它

$$ mysql $$，但提到“MS SQL”？？MySQL 可以創建臨時表，但我沒有看到任何好處。 與頂部的case兩個查詢不匹配，因此我對應該調查的內容感到困惑。

性能變化的一個主要部分是存在一個IN(a la OR) 具有多個值。通常把它變成 aUNION可以幫助解決這個問題OR，但我不確定這種情況。

表中有 233M 行嗎？磁碟上的表是否約為 120GB（數據+索引）？( SHOW TABLE STATUS) 我看到很多可能的地方來縮小數據類型；這樣做可能會顯著減少大小，從而提高記憶體能力，從而提高速度。你有多少記憶體？的設置是innodb_buffer_pool_size什麼？

規範化狀態、類別、子類別、通道和其他一些，可能會從平均 10 個字節縮小到 1 或 2 個（TINYINT UNSIGNED0-255 或SMALLINT UNSIGNED0-64K）。

還有一點。由於我的索引是“覆蓋”的，因此子查詢將包含在索引的大小中。這將避免現在發生的事情——即掃描大部分 100GB數據，用可能只有 5MB 的索引來替換它。

誠然，我沒有解決關於兩種不同配方執行的問題。相反，我建議進行更改以使該問題變得無關緊要。“7 分鐘”應該下降到“幾秒鐘”，而一些變化可能會下降到“幾乎瞬間”。

而且，假設數據將繼續增長，這個（和其他）性能問題將繼續惡化。

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