我有以下表格:
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);
- 我的查询采用这种形式以避免
OR
. - 为了索引,我已经在一定程度上进行了非规范化。
- 我不需要
base_event
为此查询引用我的表。 - 与
transaction_events
tocustomers
和的关系merchants
不是 1 对 1 而是 1 对 0 或 1。
我的问题:
- 我可以摆脱通配符,但
transaction_events
有大约 20 列(这有助于创建任何进一步的索引以加快查询速度吗? - 我是否需要放置任何其他复合索引(可能引用我的 FK)来进一步改进此查询?
您需要所有 20 列
transaction_events
吗?如果不是,那么删除*
并仅指定您需要的列不仅可以减少您一次拉回的数据量,还可以减少次优查询计划的机会。生成的查询计划可能会根据您的SELECT
子句中的列而有所不同。您可以测试在字段上添加索引并在字段上为您的子句添加
customer_id
另一个索引,看看它是否可以提高性能并产生更好的查询计划。但这需要针对每种情况对EXPLAIN进行测试和比较。merchant_id
JOIN
WHERE
子句指的是,t
因此优化器很可能会以t
每个开头SELECT
。你有他们的最佳索引。然后它需要进入其他两个表(
merchants
和customers
)并从中获取 1(或 0)行。这些表具有 的最佳索引JOIN
,即PRIMARY KEY(id)
在每种情况下。(FK 在此查询中不起任何作用。)t.*
TEXT
如果它正在获取您随后忽略的大列,则可能会减慢速度。由于您需要所有列,那么唯一可能的低效率是如果每个
SELECT
都冗余地获取同一行,只是被UNION DISTINCT
. 我认为这个问题不值得解决。(解决方法是只进行UNION
查找和重复数据删除t.id
;然后重新加入t
以获取其他 19 列。额外工作的成本可能超过收益;我无法确定。)