MySQL中int与varchar字段关联查询慢，求最优比较及优化方案

你的SQL执行慢主要有三个核心原因：

1. 字段拼接函数导致索引失效；
2. 不同类型字段关联触发隐式转换，浪费索引；
3. NOT IN子查询在大结果集下性能低下。

以下是具体优化措施：

1. 修复类型不匹配问题 #

assets.id是int类型，gridconfigs.classId是varchar类型，直接关联会触发隐式类型转换，导致classId的索引无法被使用。必须显式转换类型：

• MySQL：CAST(g.classId AS UNSIGNED)
• PostgreSQL：CAST(g.classId AS INTEGER)
• Oracle：TO_NUMBER(g.classId)

2. 替换NOT IN为更高效的写法 #

NOT IN在结果集较大时性能差，且子查询返回NULL会导致逻辑异常，推荐用NOT EXISTS或LEFT JOIN替代：

用NOT EXISTS改写 #

SELECT 
    a.id, a.type, a.path, a.filename
FROM 
    assets a
WHERE 
    a.type = 'folder' 
    -- 优化后的路径过滤条件
    AND (a.path LIKE '/product%' OR (a.path = LEFT('/product', LENGTH(a.path)) AND a.filename LIKE SUBSTRING('/product', LENGTH(a.path)+1) || '%'))
    AND NOT EXISTS (
        SELECT 1 
        FROM gridconfigs g 
        WHERE g.type='asset' 
            AND g.name='Photo Attributes'
            AND CAST(g.classId AS UNSIGNED) = a.id
    );

用LEFT JOIN改写 #

SELECT 
    a.id, a.type, a.path, a.filename
FROM 
    assets a
LEFT JOIN gridconfigs g 
    ON CAST(g.classId AS UNSIGNED) = a.id
    AND g.type='asset' 
    AND g.name='Photo Attributes'
WHERE 
    a.type = 'folder' 
    -- 优化后的路径过滤条件
    AND (a.path LIKE '/product%' OR (a.path = LEFT('/product', LENGTH(a.path)) AND a.filename LIKE SUBSTRING('/product', LENGTH(a.path)+1) || '%'))
    AND g.id IS NULL; -- 过滤无匹配的记录

3. 优化路径过滤逻辑，利用索引 #

原SQL中concat_ws("", a.path, a.filename) LIKE "/product%"使用函数拼接字段，完全无法利用path和filename的索引，两种优化方式：

方式1：创建表达式/生成列索引（推荐） #

如果数据库支持，给拼接后的字段创建索引：

-- MySQL：创建存储生成列并加索引
ALTER TABLE assets ADD COLUMN full_path VARCHAR(255) GENERATED ALWAYS AS (concat_ws("", path, filename)) STORED;
CREATE INDEX idx_assets_full_path ON assets(full_path);

-- PostgreSQL：创建表达式索引
CREATE INDEX idx_assets_full_path ON assets((concat_ws('', path, filename)));

之后过滤条件直接改为：

a.full_path LIKE '/product%'

方式2：拆解过滤逻辑（无需改表） #

如果不能修改表结构，把拼接后的模糊匹配拆成对path和filename的组合判断，尽可能利用path的索引：

(a.path LIKE '/product%' OR (LEFT(a.path, LENGTH(a.path)) = LEFT('/product', LENGTH(a.path)) AND a.filename LIKE SUBSTRING('/product', LENGTH(a.path)+1) || '%'))

4. 补充必要的联合索引 #

• 给assets表加联合索引：CREATE INDEX idx_assets_type_path ON assets(type, path);，覆盖type过滤和path的匹配，减少回表扫描。
• 给gridconfigs表加联合索引：CREATE INDEX idx_grid_type_name_class ON gridconfigs(type, name, classId);，让子查询的过滤和关联直接走索引。

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