Mysql

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

  • March 5, 2022

我有一個查詢是更大查詢的一部分。我嘗試了兩個版本的查詢來嘗試改進頁面執行時間。一個版本幾乎可以立即執行.. 但有時(大約 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