我有這個架構
create table table1
(
id int auto_increment primary key,
name varchar(2) null,
position int null,
);
create index table1_position
on table1 (position);
create table table_2
(
id int auto_increment primary key,
table1_id int null,
position int null,
constraint table_2_ibfk_1
foreign key (table1_id) references table1 (id)
);
create index ix_table_2_position
on table_2 (position);
create index table1_id
on table_2 (table1_id);
所以我在position每個表的列上添加了兩個索引。現在我需要在 BOTH 表中查找一系列位置（通過加入 then 并應用 OR 查詢）
所以我有這個查詢
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
OR table_2.position BETWEEN 5000 AND 5500
但是解釋查詢輸出給了我所有（全表掃描）
id 1
select_type SIMPLE
table table_1
partitions
type ALL
possible_keys PRIMARY,table1_position
key
key_len
ref
rows 9929
filtered 100.0
Extra
如果我更改為ANDif 給我預期的范圍索引掃描
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
AND table_2.position BETWEEN 5000 AND 5500
id 1
select_type SIMPLE
table table_1
partitions
type range
possible_keys PRIMARY,pos_idx2
key pos_idx2
key_len 5
ref
rows 1
filtered 100.0
Extra Using index condition
但是我需要OR這里的陳述句...我怎么能讓 mysql 使用范圍掃描索引來處理 OR 陳述句？我可以在這里改進我??的索引嗎（我想過在position和table1_id- 外鍵上建立一個多值索引，但它沒有幫助，它執行了全表掃描）。
uj5u.com熱心網友回復：
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
UNION ALL
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position NOT BETWEEN 5000 AND 5500
AND table_2.position BETWEEN 5000 AND 5500
還測驗
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE NOT ( table_1.position NOT BETWEEN 5000 AND 5500
AND table_2.position NOT BETWEEN 5000 AND 5500 )
uj5u.com熱心網友回復：
據推測，出于性能原因，您希望避免表掃描。
因此，嘗試將 OR 交換為 UNION 操作。
首先，table1.id使用子查詢獲取您需要的值集，就像這樣。
SELECT id FROM table_1 WHERE position BETWEEN 5000 AND 5500
UNION
SELECT table1_id FROM table_2 WHERE position BETWEEN 5000 AND 5500
UNION 的第二部分通過選擇 FK 列從 table_2 中檢索您需要的 table_1.id 值。
接下來，使用該子查詢從table1.
SELECT * FROM table_1
WHERE id IN (
SELECT id FROM table_1 WHERE position BETWEEN 5000 AND 5500
UNION
SELECT table1_id FROM table_2 WHERE position BETWEEN 5000 AND 5500
)
為了加快速度，請在 table_2 上添加此復合索引。
CREATE INDEX ix_table_2_position_table1id
ON table_2 (position, table1_id);
請注意，table2 上的兩個單列索引都不適用于此查詢。
uj5u.com熱心網友回復：
性能問題OR通常可以通過以下方式解決UNION：
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_1.position BETWEEN 5000 AND 5500
UNION
SELECT *
FROM table_1
INNER JOIN table_2 ON table_1.id = table_2.table1_id
WHERE table_2.position BETWEEN 5000 AND 5500;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/330811.html