如何“進一步”改進我的表格和查詢

這個問題是這個問題的延伸。Rick James 好心地提供了我應該在我的表格和查詢中更改的內容列表。

我還更改了我的應用程序，以便查詢中根本不包含未使用的過濾器。

現在我的表是這樣的：

CREATE TABLE `transaction_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `txn_id` varchar(32) NOT NULL,
 `nationality_id` smallint(6) NOT NULL,
 `tdate` date NOT NULL,
 `destination` varchar(80) NOT NULL,
 `sku` int(11) NOT NULL,
 `sales` decimal(8,2) NOT NULL,
 `units` tinyint(4) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `tdate` (`tdate`),
 KEY `datenat` (`tdate`,`nationality_id`),
 KEY `dest` (`destination`),
 KEY `txnid` (`txn_id`),
 KEY `cov` (`sku`,`nationality_id`,`tdate`)
) ENGINE=InnoDB AUTO_INCREMENT=3236519 DEFAULT CHARSET=utf8;

不幸的是，這實際上大大增加了查詢時間（花了 100 秒），這裡是解釋。注意我還刪除了子查詢中的國籍連接，因為它是多餘的。

詢問

SELECT `products`.`brand`, COUNT(DISTINCT transaction_data.txn_id) AS numOrders
                               FROM `products`
                               INNER JOIN `transaction_data` ON `transaction_data`.sku=`products`.sku
                               INNER JOIN `nationalities` ON `transaction_data`.`nationality_id`=`nationalities`.`nationality_id` 
                               INNER JOIN (SELECT DISTINCT `transaction_data`.`txn_id`
                               FROM `transaction_data`
                               INNER JOIN `products` USING (sku)
                               WHERE brand = '<brand>') AS tmp_txns ON tmp_txns.txn_id=`transaction_data`.txn_id
                               WHERE brand <> '<brand>' AND 
                                tdate >= '2014-01-01' 
                                AND tdate < '2014-01-01' + INTERVAL 1 YEAR
                               GROUP BY brand
                               ORDER BY numOrders DESC, brand ASC
                               LIMIT 10

我不明白它如何使用更少的行但花費的時間更長。

我還將我的 innodb_buffer_pool_size 設置為較低的“60M”（根據 RickJames 對 t2.micro amazon RDS 的建議）。

此外，要添加到以前。不幸的是，設置匯總表並不是一個好主意。雖然它們適合我的使用是正確的。我不能保證將來的使用者不會嘗試不按順序添加數據，甚至不會嘗試交換數據。

編輯

除了下面的里克詹姆斯的回答

WOW - 在內部查詢中使用日期範圍一針見血。我基本上一直在執行錯誤的查詢（其他 =brand 結果太多）。它現在在 2.4 秒內執行！

id= 自動增量，整數 txn_id= 非唯一的“籃子”標識符。

'1', '7662_20120101_PC  672_1_12:25:00', 
'2', '7662_20120101_PC  672_1_12:25:00', 
'3', '7662_20120101_PC  672_1_12:25:00', 
'4', '7660_20120101_KL  867_1_12:23:00',  
'5', '7662_20120101_PC  672_1_12:25:00', 
'6', '7660_20120101_KL  867_1_12:23:00', 
'7', '7658_20120101_KL  1211_1_12:12:0', // only sku in basket

您不需要nationality在外部查詢中。刪除它，然後：

INDEX(tnx_id, sku)  -- instead of just INDEX(txn_id)

這將使兩個查詢都被“覆蓋”，Using index如EXPLAIN.

在內部查詢中包含日期範圍不是“正確的”嗎？那將乞求添加INDEX(sku, tdate)

我不明白為什麼外連接products無法使用索引。請將合適的欄位更改為NOT NULL數據類型並保持一致。INTs在可行的地方縮小，並UNSIGNED在適當的地方添加。但要確保它們是一致的JOINs.

60M 是為了讓你遠離“交換”。你能看到還有多少 RAM 是空閒的，並且你沒有進行交換嗎？如果仍然有一些 RAM，則將其中的一部分提供給 buffer_pool。

id和之間是否存在 1:1 的關係trx_id？如果是這樣，還有一些更改建議。

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