为何同一条MariaDB查询在Sequelize与CLI执行耗时差异巨大？

数据库结构 #

meta_schema_version表字段 #

• id (int)
• namespace_id (int)
• version (varchar(10))
• schema (longtext)
• comment (mediumtext)
• created_at (datetime)

meta表字段 #

• id (int) [主键(PK)]
• meta_id (int)
• version_num (int)
• meta_schema_version_id (int) - [外键(FK)关联meta_schema_version]
• tag (varchar(30))
• tag_value (varchar(100))
• meta (longtext)
• comment (mediumtext)
• is_deleted (tinyint(1))
• updated_at (datetime)
• updated_by (varchar(100))

meta表索引 #

• id - 主键 - BTREE - 唯一
• meta_schema_version_id - 外键 - BTREE - 非唯一
• idx_config_id - BTREE - 非唯一

meta表数据量超过100万条

Sequelize分页查询代码 #

metas = await this.meta.findAndCountAll({
  attributes: ['id', 'tag', 'tagValue', ...versionAttribute],
  include: [
    {
      model: MetaSchemaVersion,
      attributes: ['version'],
    },
  ],
  where: {
    metaSchemaVersionId: schemaVersion.id,
    isDeleted: {
      [Op.eq]: 0,
    },
  },
  limit,
  offset,
});

实际生成的SQL语句 #

查询1（统计总数） #

SELECT count(Meta.id) AS count 
FROM meta AS Meta 
LEFT OUTER JOIN meta_schema_version AS metaSchemaVersion 
  ON Meta.meta_schema_version_id = metaSchemaVersion.id 
WHERE Meta.meta_schema_version_id = 1 
  AND Meta.is_deleted = 0;

查询2（分页数据） #

SELECT Meta.id, Meta.tag, Meta.tag_value AS tagValue, 
       Meta.comment, Meta.updated_at AS updatedAt, 
       Meta.updated_by AS updatedBy, Meta.meta_schema_version_id AS metaSchemaVersionId, 
       Meta.meta, metaSchemaVersion.id AS metaSchemaVersion.id, 
       metaSchemaVersion.version AS metaSchemaVersion.version 
FROM meta AS Meta 
LEFT OUTER JOIN meta_schema_version AS metaSchemaVersion 
  ON Meta.meta_schema_version_id = metaSchemaVersion.id 
WHERE Meta.meta_schema_version_id = 1 
  AND Meta.is_deleted = 0 
LIMIT 0, 20;

耗时对比 #

• Sequelize执行（慢查询日志记录）：
  • 查询1：00:00:00.612878
  • 查询2：00:00:00.894041
• MySQL CLI执行：两条查询耗时均约00:00:00.0005

SHOW PROFILE结果（慢查询） #

[
  { "Status": "Starting", "Duration": "0.000026" },
  { "Status": "Opening tables", "Duration": "0.000025" },
  { "Status": "System lock", "Duration": "0.000003" },
  { "Status": "table lock", "Duration": "0.000004" },
  { "Status": "Opening tables", "Duration": "0.000002" },
  { "Status": "After opening tables", "Duration": "0.000100" },
  { "Status": "closing tables", "Duration": "0.000003" },
  { "Status": "Unlocking tables", "Duration": "0.000003" },
  { "Status": "closing tables", "Duration": "0.000073" },
  { "Status": "checking permissions", "Duration": "0.000005" },
  { "Status": "Opening tables", "Duration": "0.000013" },
  { "Status": "After opening tables", "Duration": "0.000004" },
  { "Status": "System lock", "Duration": "0.000004" },
  { "Status": "table lock", "Duration": "0.000006" },
  { "Status": "init", "Duration": "0.000033" },
  { "Status": "Optimizing", "Duration": "0.000018" },
  { "Status": "Statistics", "Duration": "0.000074" },
  { "Status": "Preparing", "Duration": "0.000024" },
  { "Status": "Executing", "Duration": "0.000002" },
  { "Status": "Sending data", "Duration": "0.880704" },
  { "Status": "End of update loop", "Duration": "0.000016" },
  { "Status": "Query end", "Duration": "0.000003" },
  { "Status": "Commit", "Duration": "0.000005" },
  { "Status": "closing tables", "Duration": "0.000003" },
  { "Status": "Unlocking tables", "Duration": "0.000002" },
  { "Status": "closing tables", "Duration": "0.000043" },
  { "Status": "Starting cleanup", "Duration": "0.000003" },
  { "Status": "Freeing items", "Duration": "0.000010" },
  { "Status": "Updating status", "Duration": "0.000015" },
  { "Status": "Logging slow query", "Duration": "0.000006" },
  { "Status": "Opening tables", "Duration": "0.000016" },
  { "Status": "System lock", "Duration": "0.000002" },
  { "Status": "table lock", "Duration": "0.000003" },
  { "Status": "Opening tables", "Duration": "0.000002" },
  { "Status": "After opening tables", "Duration": "0.000059" },
  { "Status": "closing tables", "Duration": "0.000002" },
  { "Status": "Unlocking tables", "Duration": "0.000002" },
  { "Status": "closing tables", "Duration": "0.000005" },
  { "Status": "Reset for next command", "Duration": "0.000238" }
]

原因分析与解决办法 #

核心原因 #

从PROFILE结果看，Sending data阶段耗时占比超过99%，说明数据库在数据读取/传输环节存在瓶颈，结合SQL和执行环境差异，主要可能是以下几点：

1. 会话参数差异：Sequelize连接与CLI的数据库会话参数（如optimizer_switch、事务隔离级别、字符集）不同，导致MySQL选择了低效执行计划。
2. 冗余JOIN操作：SQL中的LEFT OUTER JOIN meta_schema_version属于冗余操作——WHERE条件已通过meta_schema_version_id=1过滤关联记录，且仅需获取version字段，额外的JOIN会大幅增加大表查询的开销。
3. 索引覆盖不足：meta表现有索引仅单独包含meta_schema_version_id，查询需同时过滤is_deleted=0，无联合索引覆盖这两个条件，导致数据库需要回表查询，增加IO开销。
4. 事务上下文影响：Sequelize可能在事务内执行查询（PROFILE中出现Commit步骤），而CLI默认是自动提交模式，事务会带来额外的一致性检查和日志写入开销。

解决步骤 #

1. 统一会话参数配置
   在Sequelize连接和CLI中分别执行SHOW VARIABLES LIKE '%optimizer%'、SHOW VARIABLES LIKE 'tx_isolation'、SHOW VARIABLES LIKE 'character_set%'，找出参数差异，将Sequelize连接的参数调整为与CLI一致的优化配置。

2. 移除冗余JOIN，单独获取版本信息
   由于schemaVersion.id已知，无需通过JOIN获取version字段，可单独查询后附加到结果中：

   // 先单独查询版本信息
   const schemaVersionInfo = await this.metaSchemaVersion.findByPk(schemaVersion.id, { attributes: ['version'] });
   // 执行分页查询时不关联表
   const metas = await this.meta.findAndCountAll({
     attributes: ['id', 'tag', 'tagValue', ...versionAttribute],
     where: {
       metaSchemaVersionId: schemaVersion.id,
       isDeleted: { [Op.eq]: 0 },
     },
     limit,
     offset,
   });
   // 将version附加到结果中
   metas.rows.forEach(row => row.dataValues.metaSchemaVersion = { version: schemaVersionInfo.version });
   

3. 添加联合索引，覆盖查询条件
   创建联合索引减少回表操作：

   CREATE INDEX idx_meta_schema_deleted ON meta(meta_schema_version_id, is_deleted);
   

4. 避免不必要的事务
   确认查询是否在不必要的事务中执行，若无需事务，移除事务包裹，改为自动提交模式执行。

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