优化Node.js中Sequelize查询PostgreSQL大表的性能

我有一个使用Sequelize作为ORM与PostgreSQL数据库交互的Node.js应用，需从一张含百万级记录、15-20列的大表中，基于日期范围和可选过滤器查询数据，当前采用原生SQL实现。即使默认限制返回10条数据，查询仍耗时30-40秒，以下是查询函数代码及控制器调用方式：

原查询函数代码 #

exports.getactivationData = async function (params, pagination = true) {
    try {
        
        let dbModels = await db.Models();

        let query = `SELECT * FROM my_large_table WHERE date("createdAt") BETWEEN '${params.startDate}' AND '${params.endDate}'`;

        if (!pagination) {
            query = `SELECT count(*) FROM my_large_table WHERE date("createdAt") BETWEEN '${params.startDate}' AND '${params.endDate}'`;
        }

        if (params.filter1) {
            query += ` AND sensitive_column_id = '${params.sensitiveFilterId}'`;
        }

        if (params.filter2) {
            query += ` AND sensitive_column_value = '${params.sensitiveFilterValue}'`;
        }

        if (pagination && params.limit) {
            query += ` ORDER BY "${params.sortBy}" ${params.sortType} `;
            query += ` LIMIT '${params.limit}' OFFSET '${params.offset}'`;
        }

        const myLargeTableData = await db.sequelize.query(query, {
            model: dbModels.myLargetableModel
        });

        return promiseAdapter.resolve(myLargeTableData);
    } catch (error) {
        return promiseAdapter.reject(error);
    }
}

控制器调用代码 #

let data = await reportmodel.getactivationData (params);
// For count
let resultCount = await reportmodel.getactivationData (params, false);

核心优化点 #

1. 修复SQL注入风险+使用参数化查询 #

当前直接拼接参数到SQL字符串，不仅有严重的SQL注入漏洞，还会让PostgreSQL无法复用查询计划。改用Sequelize的参数绑定或ORM查询语法，避免手动拼接SQL。

2. 避免函数包裹索引字段，让索引生效 #

原代码中date("createdAt")会导致createdAt上的索引失效，数据库必须全表扫描计算date值。直接用timestamp范围匹配：

WHERE "createdAt" >= :startDate AND "createdAt" < :endDate

注意：如果params.endDate是YYYY-MM-DD格式，需要将其加1天（比如2024-05-20改成2024-05-21），才能包含当天所有时间的记录

3. 创建针对性复合索引 #

根据查询的过滤和排序条件，创建复合索引，实现索引覆盖避免回表：

• 若常用查询为日期范围+filter1+排序：

  CREATE INDEX idx_my_large_table_date_filter1 
  ON my_large_table ("createdAt", sensitive_column_id) 
  INCLUDE (column1, column2, 需要查询的其他列);
  

• 若同时用到filter2：

  CREATE INDEX idx_my_large_table_date_filters 
  ON my_large_table ("createdAt", sensitive_column_id, sensitive_column_value) 
  INCLUDE (column1, column2, 需要查询的其他列);
  

4. 不要SELECT *，只查询需要的列 #

大表有15-20列，SELECT *会加载所有列数据，增加IO和内存开销，只明确列出业务需要的字段。

5. 优化分页逻辑，替换OFFSET为键集分页 #

OFFSET在大数据量时会跳过大量记录，性能极差。改用键集分页（基于排序字段的游标）：
比如按createdAt+主键排序，以上一页最后一条的createdAt和主键作为游标查询下一页：

SELECT column1, column2 FROM my_large_table 
WHERE "createdAt" > :lastCreatedAt AND id > :lastId
ORDER BY "createdAt" ASC, id ASC
LIMIT :limit

6. 合并数据查询与计数，减少数据库往返 #

原代码分两次调用查询数据和计数，增加了数据库连接开销。用Sequelize的findAndCountAll可以一次获取数据和总数。

7. 优化PostgreSQL配置 #

检查postgresql.conf中的关键参数：

• shared_buffers：设置为服务器内存的25%左右
• work_mem：增加到64MB或更高，用于排序和哈希操作
• maintenance_work_mem：增加到256MB，用于创建索引等维护操作

优化后的代码示例 #

const { Op } = require('sequelize');

exports.getactivationData = async function (params, pagination = true) {
    try {
        const dbModels = await db.Models();
        const whereClause = {
            "createdAt": {
                [Op.gte]: params.startDate,
                [Op.lt]: new Date(params.endDate).setDate(new Date(params.endDate).getDate() + 1)
            }
        };

        if (params.filter1) {
            whereClause.sensitive_column_id = params.sensitiveFilterId;
        }
        if (params.filter2) {
            whereClause.sensitive_column_value = params.sensitiveFilterValue;
        }

        if (!pagination) {
            const count = await dbModels.myLargetableModel.count({ where: whereClause });
            return promiseAdapter.resolve(count);
        }

        const queryOptions = {
            where: whereClause,
            attributes: ['id', 'column1', 'column2', 'createdAt'], // 只选需要的列
            order: [[params.sortBy, params.sortType]],
            limit: params.limit
        };

        // 键集分页替换offset
        if (params.lastCursor) {
            whereClause[Op.and] = [
                { "createdAt": { [Op.gt]: params.lastCursor.createdAt } },
                { id: { [Op.gt]: params.lastCursor.id } }
            ];
            delete queryOptions.offset;
        } else {
            queryOptions.offset = params.offset;
        }

        const data = await dbModels.myLargetableModel.findAll(queryOptions);
        return promiseAdapter.resolve(data);
    } catch (error) {
        return promiseAdapter.reject(error);
    }
}

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