MySQL優化-年份列分組-使用臨時表、文件排序

我有一個包含 600,000 條記錄的事務表，我需要在財政年度的基礎上列出儀表板的計數。使用的表是 MyISAM。我嘗試為交易日期 ( tran_date) 添加索引。即使它正在使用索引，它也會創建臨時表，由於臨時表和文件排序，這需要更多時間。有什麼辦法可以優化查詢以提高查詢時間？

SELECT COUNT( * ) AS cnt, CASE WHEN MONTH( tran_date ) >=3
然後 concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS 財務年度
來自“交易1”
WHERE tran_date >= '2010-06-01'
按金融年分組

**顯示第 0 - 4 行（共 5 行，查詢耗時 1.2095 秒）**

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE transactions1 range PRIMARY,tran_date tran_date 8 NULL 346485 使用 where；使用索引；使用臨時的；使用文件排序

鍵名類型唯一打包欄位基數排序規則
PRIMARY BTREE 是 否 tran_date 205720 A 
tran_ID 617162 一個
coupon_No BTREE No No coupon_No 617162 A 
account_typeBTREE 否 否 account_type 3 A 
prodCode BTREE 否 否 prodCode 430 A 
翻譯日期 308581 一個
tran_date BTREE 否 否 tran_date 205720 A 
cust_ID BTREE 否 否 cust_ID 3265 A 
翻譯日期 308581 一個
account_type 308581 一個
points_earned 617162 一個

更新 ：

嘗試添加與非分區相比沒有太大幫助的分區。在這種情況下，複製是否有助於讀取此表？讀取數據時會根據日期（使用日期函式）進行更多分組。

編輯：

我更改了查詢並減少了查詢執行時間。我使用的查詢是，

選擇總和（計數）
從 （
選擇計數（*）作為計數，
案例當月（tran_date）> = 3
然後 concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
來自交易1
按 tran_date 分組
) 作為小號
GROUP BY format_date

**顯示第 0 - 4 行（共 5 行，查詢耗時 0.5636 秒）**

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 229676 使用臨時；使用文件排序
2 DERIVED transactions1 索引 NULL tran_date 8 NULL 617162 使用索引

但是使用的時候

選擇計數（*）作為計數，
案例當月（tran_date）> = 3
然後 concat( YEAR( tran_date ) , '-', YEAR( tran_date ) +1 )
ELSE concat( YEAR( tran_date ) -1, '-', YEAR( tran_date ) )
END AS format_date
來自交易1
按 tran_date 分組

**顯示第 0 - 29 行（總共 229,676 行，查詢耗時 0.0006 秒）**

無需SUM(count)在派生表中使用 即可減少時間。有沒有其他方法可以在不使用 MySQL 中的子查詢的情況下獲取總和，或者可以優化子查詢以獲取索引。

我沒有看到很多改進的機會。

您添加的索引可能有很大幫助，因為它被用於 WHERE 子句上的範圍匹配（type => range, key => tran_date），並且它被用作覆蓋索引（extra => using index），避免需要查找表以獲取行數據。

但是，由於您使用函式來構造 group by 的 Financial_year 值，因此無法避免“使用文件排序”和“使用臨時”。但是，這些都不是真正的問題。真正的問題是，您要評估 MONTH(tran_date) 346,485 次，而 YEAR(tran_date) 至少要評估很多次……一秒鐘內約 700,000 次函式呼叫似乎還不錯。

計劃 B：我絕對不喜歡儲存冗餘數據，而且我堅決反對讓應用程序負責維護它……但我可能會嘗試的一個選擇是創建一個 dashboard_stats_by_financial_year 表，並且在 transactions1 表上使用 after-insert/update/delete 觸發器來管理使這些統計資訊保持最新。

當然，該選項是有成本的——增加了更新/插入/刪除事務所需的時間……但是，等待 > 1200 毫秒以獲取儀表板的統計資訊也是一種成本。因此，這可能歸結為您是想現在付款還是以後付款。

我認為更有效的方法是生成查詢（使用動態 SQL 或外部語言），因此它使用以下索引(tran_date)：

SELECT cnt
    , CONCAT(year, '-', year+1) AS financial_year
FROM 
   ( SELECT COUNT(*) AS cnt,  2010 AS year
     FROM transactions1
     WHERE tran_date >= '2010-06-01'
       AND tran_date <  '2011-04-01'
   UNION ALL
     SELECT COUNT(*), 2011 
     FROM transactions1
     WHERE tran_date >= '2011-04-01'
       AND tran_date <  '2012-04-01'
   UNION ALL
     SELECT COUNT(*), 2012
     FROM transactions1
     WHERE tran_date >= '2012-03-01'
       AND tran_date <  '2013-04-01'
   ) AS tmp
ORDER BY year ;

---

您還可以引入一個FiscalCalendar表：

CREATE TABLE Fiscal_Calendar
 ( fiscal_year SMALLINT NOT NULL
 , start_date DATE NOT NULL
 , next_start_date DATE NOT NULL
 , PRIMARY KEY (fiscal_year)
 , INDEX start_date_IDX (start_date)
 ) ;

INSERT INTO Fiscal_Calendar
 (fiscal_year, start_date, next_start_date) 
VALUES
 (1900, '1900-04-01', '1901-04-01'),
 ---
 (2099, '2099-04-01', '2100-04-01') ; 

然後使用它（不需要動態 SQL）：

SELECT 
   ( SELECT COUNT(*)
     FROM transactions1 AS tr
     WHERE tr.tran_date >= fc.start_date
       AND tr.tran_date <  fc.next_start_date
   ) AS cnt,
   CONCAT(fc.year, '-', fc.year+1) AS financial_year
FROM 
       Fiscal_Calendar AS fc
   JOIN
       ( SELECT year
         FROM Fiscal_Calendar
         WHERE start_date <= '2010-06-01'
         ORDER BY start_date DESC
       ) AS param
     ON  fc.year >= param.year 
     AND fc.year <= YEAR(NOW())
ORDER BY 
   fc.year ;

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