非常快(但有時非常慢)的 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 laOR
) 具有多個值。通常把它變成 aUNION
可以幫助解決這個問題OR
,但我不確定這種情況。表中有 233M 行嗎?磁碟上的表是否約為 120GB(數據+索引)?(
SHOW TABLE STATUS
) 我看到很多可能的地方來縮小數據類型;這樣做可能會顯著減少大小,從而提高記憶體能力,從而提高速度。你有多少記憶體?的設置是innodb_buffer_pool_size
什麼?規範化狀態、類別、子類別、通道和其他一些,可能會從平均 10 個字節縮小到 1 或 2 個(
TINYINT UNSIGNED
0-255 或SMALLINT UNSIGNED
0-64K)。還有一點。由於我的索引是“覆蓋”的,因此子查詢將包含在索引的大小中。這將避免現在發生的事情——即掃描大部分 100GB數據,用可能只有 5MB 的索引來替換它。
誠然,我沒有解決關於兩種不同配方執行的問題。相反,我建議進行更改以使該問題變得無關緊要。“7 分鐘”應該下降到“幾秒鐘”,而一些變化可能會下降到“幾乎瞬間”。
而且,假設數據將繼續增長,這個(和其他)性能問題將繼續惡化。