如何添加具有非規範化和 JOINS 的複合 MySQL 索引？

我有以下表格：

CREATE TABLE base_event (
   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   created_by ... -- some columns
);

CREATE TABLE transaction_events (
   event_id BIGINT UNSIGNED NOT NULL,
   transaction_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   merchant_id BIGINT UNSIGNED NULL DEFAULT NULL,   
   merchant_city VARCHAR (...) NULL DEFAULT NULL, -- Denormalize
   customer_id BIGINT UNSIGNED NULL DEFAULT NULL,
   customer_ip_address VARCHAR(...) NULL DEFAULT NULL, -- Denormalize
   ...
   FOREIGN KEY (event_id) REFERENCES base_event(id),
   FOREIGN KEY (customer_id) REFERENCES customers(id),
   FOREIGN KEY (merchant_id) REFERENCES merchants(id),
);

CREATE TABLE customers (
   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   customer_ip_address VARCHAR(...) NULL DEFAULT NULL,
   ...
);

CREATE TABLE merchants (
   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ...
);

我的SELECT：

(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.customer_ip_address = 'abc' AND t.transaction_time > 'abc')
UNION DISTINCT
(SELECT t.*, c.name AS customer_name ...
FROM transaction_events t
JOIN customers c ON t.customer_id = c.id
JOIN merchants m ON t.merchant_id = m.id
WHERE t.merchant_city = 'abc' AND t.transaction_time > 'abc')

我的索引是：

ALTER TABLE transaction_events 
ADD INDEX index_1 (customer_ip_address, transaction_time),
ADD INDEX index_2 (merchant_city, transaction_time);

1. 我的查詢採用這種形式以避免OR.
2. 為了索引，我已經在一定程度上進行了非規範化。
3. 我不需要base_event為此查詢引用我的表。
4. 與transaction_eventstocustomers和的關係merchants不是 1 對 1 而是 1 對 0 或 1。

我的問題：

1. 我可以擺脫萬用字元，但transaction_events有大約 20 列（這有助於創建任何進一步的索引以加快查詢速度嗎？
2. 我是否需要放置任何其他復合索引（可能引用我的 FK）來進一步改進此查詢？

WHERE子句指的是，t因此優化器很可能會以t每個開頭SELECT。你有他們的最佳索引。

然後它需要進入其他兩個表（merchants和customers）並從中獲取 1（或 0）行。這些表具有 的最佳索引JOIN，即PRIMARY KEY(id)在每種情況下。（FK 在此查詢中不起任何作用。）

t.*``TEXT如果它正在獲取您隨後忽略的大列，則可能會減慢速度。

由於您需要所有列，那麼唯一可能的低效率是如果每個SELECT都冗餘地獲取同一行，只是被UNION DISTINCT. 我認為這個問題不值得解決。（解決方法是只進行UNION查找和重複數據刪除t.id；然後重新加入t以獲取其他 19 列。額外工作的成本可能超過收益；我無法確定。）

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