mysql - 具有多重连接的数据库搜索

Question

我有一个 MySQL 数据库，我想执行更大的搜索。
我在其中一个表中有大约 10k 条记录，预计会增长，但增长缓慢。
最大的问题是，要执行搜索，我必须使用 4 个 JOINS 进行查询，我认为这会导致搜索速度很慢。
所以这里是一些示例结构:

[table records]
id INT unsigned PRIMARY KEY auto_increment
description text
label INT unsigned
type INT unsigned
price DECIMAL

[table records_labels]
id INT unsigned PRIMARY KEY auto_increment
label varchar

[table records_types]
id INT unsigned PRIMARY KEY auto_increment
type varchar

[table records_serial]
id INT unsigned PRIMARY KEY auto_increment
serial varchar
record INT unsigned

[table records_barcode]
id INT unsigned PRIMARY KEY auto_increment
barcode varchar
record INT unsigned

事情是这样进行的:
我运行一个查询，选择records.id、records.description、records.price、records_labels.label、records_types.type、records_serial.serial、records_barcode.barcode;所以完整的查询是这样的:

SELECT records.id, records.description, records.price, records_labels.label, records_types.type, records_serial.serial, records_barcode.barcode FROM records JOIN records_labels ON records_labels.id = records.label JOIN records_types ON records_types.id = records.type LEFT JOIN records_serial ON records_serial.record = record.id LEFT JOIN records_barcode ON records_barcode.record = record.id WHERE records_serial.serial LIKE %SEARCH_TERM% OR records_barcode.barcode LIKE %SEARCH_TERM%

我认为这里的解决方案是索引，但我对此不太熟悉。
那么，如何加速和优化此类查询呢？

Answer 1

索引记录(可选，但推荐)

CREATE INDEX ilabel ON records (`label`);
CREATE INDEX itype ON records (`type`);

修复records_label

ALTER TABLE records_label MODIFY label INT(10) UNSIGNED NULL;
CREATE INDEX ilabel ON records_label (`label`);

修复records_types

ALTER TABLE records_types MODIFY `type` INT(10) UNSIGNED NULL;
CREATE INDEX itype ON records_types (`type`);

搜索

SELECT r.id, r.description, r.price, rl.label, 
    rt.`type`, records_serial.`serial`, records_barcode.barcode 
FROM records r
INNER JOIN records_labels rl ON rl.id = r.label 
INNER JOIN records_types rt ON rt.id = r.`type` 
WHERE 
    r.id IN (
        SELECT rs.record
        FROM records_serial rs
        WHERE rs.`serial` LIKE '%SEARCH_TERM%' 
    )
    OR 
    r.id IN (
        SELECT rb.record
        FROM records_barcode rb
        WHERE rb.barcode LIKE '%SEARCH_TERM%'
    );

我对你的 where 子句无能为力。如果您热衷于将其更改为 LIKE 'SEARCH_TERM%' 之类的内容，那么 Like %% 会破坏任何性能，那么您可以创建下面的索引

CREATE INDEX iserial ON records_serial (`serial`(10));
CREATE INDEX ibarcode ON records_barcode (`barcode`(10));

它还可以进一步改进，但通过这些更改，我相信您可以实现您想要的目标。 ;-)