數十億行數據的數據庫和表設計

基本上我有2張桌子：header，details tables。

CREATE TABLE `header` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`RECORD_DATE` datetime DEFAULT NULL,
`TICKER_ID` int(11) DEFAULT NULL,
`CURR_TIMESTAMP` datetime DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `detail` (
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`HEADER_ID` int(11) DEFAULT NULL,
`BROKER_ID` int(11) DEFAULT NULL,
`AMOUNT` decimal(26,0) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

每天大約有 4800 個股票需要更新，ticker_id (A00001, B00032 …) 並且每個ticker_id 包含每天的記錄數，我儲存在明細表中

一開始它工作得很好，幾年後，標題變成了240萬行，明細表有2.5億行，簡單的選擇需要一個小時，

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL 
INNER JOIN herder h 
   ON h.id = d.header_id
where h.ticker = 'A00001'

所以我創建了一個“連結”表來將它們連接在一起。

CREATE TABLE `linkA00001` (
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`HEADER_ID` int(11) DEFAULT NULL,
`DETAIL_ID` int(11) DEFAULT NULL, 
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

結果變成

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL
INNER JOIN linkA00001 l
INNER JOIN herder h 
   ON h.id = l.header_id and d.id = l.detail_id

它的速度驚人，從一小時到幾秒，但這種方法只解決了 1 個股票選擇，所以我創建了 4800 個連結表以獲得更好的性能，它確實解決了選擇問題，但我在我的應用程序中使用 java spring，建構超過 4800 個實體需要啟動應用程序需要 10 分鐘，並且很難維護連結表。

所以我的問題是：

1. 這種方法有效嗎？
2. 如果沒有，我該如何改進或重新設計它？
3. 根據這個連結，它需要時間戳，但我從不在詳細表中插入，這是好的做法加回來嗎？

非常感謝你！

改進查詢：

SELECT h.ticker_id, h.record_date , d.broker_id, d.broker_id, d.amount
FROM DETAIL 
INNER JOIN header h 
   ON h.id = d.header_id
WHERE
   h.ticker_id = 'A00001'

（忽略ticket_id表中的 INT）；

最會限制結果的查詢方面是 的值h.ticket_id。要快速找到它，需要在 table 中有一個索引header。

CREATE INDEX ticket ON header (TICKER_ID)

然後數據庫需要在detail表中快速找到HEADER_ID：

CREATE INDEX header ON detail (HEADER_ID)

這裡的問題不清楚的是為什麼detail有一個 auto_increment 主鍵。如果這沒有在任何查詢中使用，並且如果HEADER_ID,BROKER_ID是唯一的，那麼也許這些是表的更好的主鍵並且作為CREATE INDEX header上面的替代：

ALTER TABLE detail
DROP ID,
DROP PRIMARY KEY,
ADD PRIMARY KEY (HEADER_ID, BROKER_ID);

這也將加快查詢速度，因為主鍵比輔助鍵搜尋得更快（d.amount也需要非索引值）。

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