如何优化含多左连接的MySQL搜索查询？网页端查询耗时久的优化方案咨询

兄弟，我看了你的查询场景和代码，phpMyAdmin里跑正常但网页端要卡一分钟，核心问题还是查询本身的效率瓶颈，结合你的字段类型和搜索需求，给你几个落地的优化方案：

一、区分搜索类型，避免REGEXP浪费性能 #

你的搜索有纯数字（比如5321，匹配产品ID）和字符串（比如black shirt、shoes）两种场景，但原查询不管啥情况都用REGEXP，尤其是整数字段用REGEXP完全发挥不了索引的作用。可以先判断搜索内容类型，针对性生成查询条件：

$original_query = $_POST["query"];
$search = str_replace(",", "|", $original_query);
$conditions = [];

// 如果是纯数字，用等值查询代替REGEXP，速度快N倍
if (is_numeric($original_query)) {
    $num_val = (int)$original_query;
    $conditions[] = "table3.valueB = $num_val";
    $conditions[] = "table1.value1 = $num_val";
    $conditions[] = "table2.value1 = $num_val";
}

// 字符串场景再用REGEXP（或后面说的全文索引）
$conditions[] = "table3.valueA REGEXP '$search'";
$conditions[] = "table1.value2 REGEXP '$search'";
$conditions[] = "table2.value2 REGEXP '$search'";

$query = "SELECT table1.value2, table2.value2, table3.valueA, table3.valueB, table3.valueC, table3.valueD 
          FROM table3 
          LEFT JOIN table1 ON table3.valueB = table1.value1 
          LEFT JOIN table2 ON table3.valueB = table2.value1 
          WHERE " . implode(" OR ", $conditions) . " 
          LIMIT 10";

二、给关键字段加索引，这是提速核心 #

没有索引的话，MySQL会做全表扫描，数据量一大就慢到离谱：

1. 关联字段必须加索引：table3.valueB、table1.value1、table2.value1是整数关联字段，先确认有没有索引，没有的话赶紧加：

CREATE INDEX idx_table3_valueB ON table3(valueB);
CREATE INDEX idx_table1_value1 ON table1(value1);
CREATE INDEX idx_table2_value1 ON table2(value1);

2. 字符串字段用全文索引代替REGEXP：REGEXP做任意匹配时，普通索引根本没用，用MySQL的全文索引效率会高很多（InnoDB 5.6+支持）。给需要模糊搜索的varchar字段创建全文索引：

CREATE FULLTEXT INDEX idx_table3_valueA ON table3(valueA);
CREATE FULLTEXT INDEX idx_table1_value2 ON table1(value2);
CREATE FULLTEXT INDEX idx_table2_value2 ON table2(value2);

然后把查询里的REGEXP改成全文搜索语法，比如：

// 替换原来的REGEXP条件
$conditions[] = "MATCH(table3.valueA) AGAINST('$original_query' IN BOOLEAN MODE)";
$conditions[] = "MATCH(table1.value2) AGAINST('$original_query' IN BOOLEAN MODE)";
$conditions[] = "MATCH(table2.value2) AGAINST('$original_query' IN BOOLEAN MODE)";

这种方式适合“black shirt”“shoes”这类词汇搜索，比REGEXP精准还快。

三、优化JOIN类型，减少不必要的数据扫描 #

原查询用了LEFT JOIN，但你的条件里有OR table1.value2 REGEXP ...，如果table1里没有匹配的记录，table1.value2是NULL，REGEXP匹配NULL会返回false，所以其实可以把LEFT JOIN改成INNER JOIN，MySQL只会扫描有关联的记录，数据量直接减少：

SELECT ... 
FROM table3 
INNER JOIN table1 ON table3.valueB = table1.value1 
INNER JOIN table2 ON table3.valueB = table2.value1 
WHERE ...

如果业务上确实需要保留table3没有关联的记录，这条可以忽略。

四、缓存重复搜索结果，避免重复查库 #

如果很多用户搜相同的内容，比如“shoes”，可以把查询结果缓存起来，比如用Redis或者PHP的文件缓存，下次有人搜相同的词直接返回缓存，不用再查数据库，能瞬间把响应时间压下来。

五、检查网页端的数据库连接配置 #

有时候phpMyAdmin快是因为它用的本地数据库连接，而网页端用的远程连接，或者每次请求都重新建立连接，这也会增加耗时。可以试试用持久化连接，比如mysqli用mysqli_pconnect()代替mysqli_connect()。

内容的提问来源于stack exchange，提问作者Jack Yuan