Feathers.js + Sequelize 高级查询与优化 - 构建高性能应用

发布时间：2024-07-30
作者：一介布衣
标签：Feathers.js, Sequelize, 高级查询, 性能优化, 事务

前言

前面两篇文章我们学习了 Feathers.js 与 Sequelize 的集成和模型设计，今天咱们来深入学习高级查询和性能优化。说实话，会用 ORM 和用好 ORM 是两个概念，很多性能问题都是因为不合理的查询导致的。

我记得有一次线上系统突然变慢，查了半天发现是一个 N+1 查询问题，本来一个查询就能解决的，结果执行了几百个查询。后来学会了 Sequelize 的各种优化技巧，再也没有遇到过类似的问题。

今天我就带大家学习 Sequelize 的高级查询技巧和性能优化策略，让你的应用跑得飞快。

高级查询技巧

1. 复杂条件查询

// src/services/posts/posts.class.js
const { Op } = require('sequelize');

class PostsService extends Service {
  async find(params) {
    const { query = {} } = params;
    
    const sequelizeOptions = {
      where: {},
      include: [],
      order: [['publishedAt', 'DESC']]
    };

    // 全文搜索
    if (query.search) {
      sequelizeOptions.where[Op.or] = [
        { title: { [Op.iLike]: `%\${query.search}%` } },
        { content: { [Op.iLike]: `%\${query.search}%` } },
        { excerpt: { [Op.iLike]: `%\${query.search}%` } }
      ];
      delete query.search;
    }

    // 日期范围查询
    if (query.dateFrom || query.dateTo) {
      sequelizeOptions.where.publishedAt = {};
      
      if (query.dateFrom) {
        sequelizeOptions.where.publishedAt[Op.gte] = new Date(query.dateFrom);
        delete query.dateFrom;
      }
      
      if (query.dateTo) {
        sequelizeOptions.where.publishedAt[Op.lte] = new Date(query.dateTo);
        delete query.dateTo;
      }
    }

    // 数值范围查询
    if (query.viewCountMin || query.viewCountMax) {
      sequelizeOptions.where.viewCount = {};
      
      if (query.viewCountMin) {
        sequelizeOptions.where.viewCount[Op.gte] = parseInt(query.viewCountMin);
        delete query.viewCountMin;
      }
      
      if (query.viewCountMax) {
        sequelizeOptions.where.viewCount[Op.lte] = parseInt(query.viewCountMax);
        delete query.viewCountMax;
      }
    }

    // 数组包含查询（JSON 字段）
    if (query.hasKeyword) {
      sequelizeOptions.where.metaKeywords = {
        [Op.contains]: [query.hasKeyword]
      };
      delete query.hasKeyword;
    }

    // 复杂的关联查询
    if (query.authorName) {
      sequelizeOptions.include.push({
        model: this.app.service('users').Model,
        as: 'author',
        where: {
          [Op.or]: [
            { username: { [Op.iLike]: `%\${query.authorName}%` } },
            { firstName: { [Op.iLike]: `%\${query.authorName}%` } },
            { lastName: { [Op.iLike]: `%\${query.authorName}%` } }
          ]
        }
      });
      delete query.authorName;
    }

    // 标签筛选（多对多关联）
    if (query.tags) {
      const tags = Array.isArray(query.tags) ? query.tags : [query.tags];
      
      sequelizeOptions.include.push({
        model: this.app.service('tags').Model,
        as: 'tags',
        where: { slug: { [Op.in]: tags } },
        through: { attributes: [] }
      });
      
      // 确保包含所有指定标签
      sequelizeOptions.having = this.Model.sequelize.where(
        this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('tags.id')),
        tags.length
      );
      
      delete query.tags;
    }

    // 排序优化
    if (query.$sort) {
      const sortOptions = [];
      
      Object.entries(query.$sort).forEach(([field, direction]) => {
        if (field === 'popularity') {
          // 自定义排序：按热度排序
          sortOptions.push([
            this.Model.sequelize.literal(
              '(view_count + like_count * 5 + comment_count * 10)'
            ),
            direction === 1 ? 'ASC' : 'DESC'
          ]);
        } else if (field === 'authorName') {
          // 关联字段排序
          sortOptions.push([
            { model: this.app.service('users').Model, as: 'author' },
            'username',
            direction === 1 ? 'ASC' : 'DESC'
          ]);
        } else {
          sortOptions.push([field, direction === 1 ? 'ASC' : 'DESC']);
        }
      });
      
      sequelizeOptions.order = sortOptions;
      delete query.$sort;
    }

    params.sequelize = sequelizeOptions;
    
    return super.find(params);
  }

  // 高级聚合查询
  async getPostsAnalytics(params = {}) {
    const { groupBy = 'month', year = new Date().getFullYear() } = params;
    
    let dateFormat;
    switch (groupBy) {
      case 'day':
        dateFormat = '%Y-%m-%d';
        break;
      case 'week':
        dateFormat = '%Y-%u';
        break;
      case 'month':
        dateFormat = '%Y-%m';
        break;
      case 'year':
        dateFormat = '%Y';
        break;
      default:
        dateFormat = '%Y-%m';
    }

    return this.Model.findAll({
      where: {
        status: 'published',
        publishedAt: {
          [Op.gte]: new Date(`\${year}-01-01`),
          [Op.lt]: new Date(`\${year + 1}-01-01`)
        }
      },
      attributes: [
        [this.Model.sequelize.fn('DATE_FORMAT', this.Model.sequelize.col('published_at'), dateFormat), 'period'],
        [this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('id')), 'postsCount'],
        [this.Model.sequelize.fn('SUM', this.Model.sequelize.col('view_count')), 'totalViews'],
        [this.Model.sequelize.fn('AVG', this.Model.sequelize.col('view_count')), 'avgViews'],
        [this.Model.sequelize.fn('SUM', this.Model.sequelize.col('like_count')), 'totalLikes'],
        [this.Model.sequelize.fn('SUM', this.Model.sequelize.col('comment_count')), 'totalComments']
      ],
      group: [this.Model.sequelize.fn('DATE_FORMAT', this.Model.sequelize.col('published_at'), dateFormat)],
      order: [[this.Model.sequelize.literal('period'), 'ASC']]
    });
  }

  // 复杂的子查询
  async getTopAuthors(params = {}) {
    const { limit = 10, minPosts = 5 } = params;
    
    return this.app.service('users').Model.findAll({
      include: [
        {
          model: this.Model,
          as: 'posts',
          where: { status: 'published' },
          attributes: []
        }
      ],
      attributes: [
        'id',
        'username',
        'firstName',
        'lastName',
        'avatar',
        [this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('posts.id')), 'postsCount'],
        [this.Model.sequelize.fn('SUM', this.Model.sequelize.col('posts.view_count')), 'totalViews'],
        [this.Model.sequelize.fn('AVG', this.Model.sequelize.col('posts.view_count')), 'avgViews']
      ],
      group: ['users.id'],
      having: this.Model.sequelize.where(
        this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('posts.id')),
        Op.gte,
        minPosts
      ),
      order: [
        [this.Model.sequelize.literal('totalViews'), 'DESC'],
        [this.Model.sequelize.literal('postsCount'), 'DESC']
      ],
      limit
    });
  }
}

2. 原生 SQL 查询

// 复杂的原生查询
class AnalyticsService {
  constructor(app) {
    this.app = app;
    this.sequelize = app.get('sequelizeClient');
  }

  // 用户活跃度分析
  async getUserActivityAnalysis(params = {}) {
    const { days = 30 } = params;
    
    const query = `
      WITH user_activity AS (
        SELECT 
          u.id,
          u.username,
          u.created_at as join_date,
          COUNT(DISTINCT p.id) as posts_count,
          COUNT(DISTINCT c.id) as comments_count,
          SUM(p.view_count) as total_views,
          MAX(GREATEST(p.created_at, c.created_at)) as last_activity
        FROM users u
        LEFT JOIN posts p ON u.id = p.author_id 
          AND p.created_at >= NOW() - INTERVAL :days DAY
        LEFT JOIN comments c ON u.id = c.author_id 
          AND c.created_at >= NOW() - INTERVAL :days DAY
        WHERE u.status = 'active'
        GROUP BY u.id, u.username, u.created_at
      ),
      activity_scores AS (
        SELECT 
          *,
          (posts_count * 10 + comments_count * 2) as activity_score,
          CASE 
            WHEN last_activity >= NOW() - INTERVAL 7 DAY THEN 'very_active'
            WHEN last_activity >= NOW() - INTERVAL 14 DAY THEN 'active'
            WHEN last_activity >= NOW() - INTERVAL 30 DAY THEN 'moderate'
            ELSE 'inactive'
          END as activity_level
        FROM user_activity
      )
      SELECT 
        id,
        username,
        join_date,
        posts_count,
        comments_count,
        total_views,
        last_activity,
        activity_score,
        activity_level,
        RANK() OVER (ORDER BY activity_score DESC) as rank
      FROM activity_scores
      ORDER BY activity_score DESC
      LIMIT 50;
    `;

    const [results] = await this.sequelize.query(query, {
      replacements: { days },
      type: this.sequelize.QueryTypes.SELECT
    });

    return results;
  }

  // 内容趋势分析
  async getContentTrends(params = {}) {
    const { months = 12 } = params;
    
    const query = `
      WITH monthly_stats AS (
        SELECT 
          DATE_FORMAT(published_at, '%Y-%m') as month,
          COUNT(*) as posts_count,
          AVG(view_count) as avg_views,
          SUM(view_count) as total_views,
          COUNT(DISTINCT author_id) as active_authors
        FROM posts 
        WHERE status = 'published' 
          AND published_at >= DATE_SUB(NOW(), INTERVAL :months MONTH)
        GROUP BY DATE_FORMAT(published_at, '%Y-%m')
      ),
      growth_rates AS (
        SELECT 
          month,
          posts_count,
          avg_views,
          total_views,
          active_authors,
          LAG(posts_count) OVER (ORDER BY month) as prev_posts,
          LAG(total_views) OVER (ORDER BY month) as prev_views
        FROM monthly_stats
      )
      SELECT 
        month,
        posts_count,
        ROUND(avg_views, 2) as avg_views,
        total_views,
        active_authors,
        CASE 
          WHEN prev_posts > 0 THEN 
            ROUND(((posts_count - prev_posts) / prev_posts * 100), 2)
          ELSE NULL 
        END as posts_growth_rate,
        CASE 
          WHEN prev_views > 0 THEN 
            ROUND(((total_views - prev_views) / prev_views * 100), 2)
          ELSE NULL 
        END as views_growth_rate
      FROM growth_rates
      ORDER BY month;
    `;

    const [results] = await this.sequelize.query(query, {
      replacements: { months },
      type: this.sequelize.QueryTypes.SELECT
    });

    return results;
  }

  // 标签相关性分析
  async getTagCorrelations(tagId) {
    const query = `
      WITH tag_posts AS (
        SELECT pt1.post_id
        FROM post_tags pt1
        WHERE pt1.tag_id = :tagId
      ),
      related_tags AS (
        SELECT 
          t.id,
          t.name,
          t.slug,
          COUNT(*) as co_occurrence,
          (COUNT(*) * 100.0 / (
            SELECT COUNT(*) FROM tag_posts
          )) as correlation_percentage
        FROM tag_posts tp
        JOIN post_tags pt ON tp.post_id = pt.post_id
        JOIN tags t ON pt.tag_id = t.id
        WHERE t.id != :tagId
        GROUP BY t.id, t.name, t.slug
        HAVING COUNT(*) >= 2
      )
      SELECT 
        id,
        name,
        slug,
        co_occurrence,
        ROUND(correlation_percentage, 2) as correlation_percentage
      FROM related_tags
      ORDER BY correlation_percentage DESC
      LIMIT 20;
    `;

    const [results] = await this.sequelize.query(query, {
      replacements: { tagId },
      type: this.sequelize.QueryTypes.SELECT
    });

    return results;
  }
}

事务处理

1. 基础事务

// src/services/posts/posts.class.js
class PostsService extends Service {
  async create(data, params) {
    const { user } = params;
    
    // 使用事务确保数据一致性
    const result = await this.Model.sequelize.transaction(async (t) => {
      // 创建文章
      const post = await super.create({
        ...data,
        authorId: user.id,
        slug: await this.generateSlug(data.title)
      }, { ...params, transaction: t });

      // 处理标签
      if (data.tags && data.tags.length > 0) {
        await this.handleTags(post.id, data.tags, t);
      }

      // 更新用户文章计数
      await this.app.service('users').Model.increment('postsCount', {
        where: { id: user.id },
        transaction: t
      });

      // 更新分类文章计数
      if (data.categoryId) {
        await this.app.service('categories').Model.increment('postsCount', {
          where: { id: data.categoryId },
          transaction: t
        });
      }

      return post;
    });

    return result;
  }

  async remove(id, params) {
    const { user } = params;
    
    return this.Model.sequelize.transaction(async (t) => {
      // 获取文章信息
      const post = await this.get(id, { ...params, transaction: t });
      
      // 检查权限
      if (post.authorId !== user.id && user.role !== 'admin') {
        throw new Error('无权限删除此文章');
      }

      // 删除文章标签关联
      await this.app.service('postTags').Model.destroy({
        where: { postId: id },
        transaction: t
      });

      // 删除评论
      await this.app.service('comments').Model.destroy({
        where: { postId: id },
        transaction: t
      });

      // 删除文章
      const result = await super.remove(id, { ...params, transaction: t });

      // 更新用户文章计数
      await this.app.service('users').Model.decrement('postsCount', {
        where: { id: post.authorId },
        transaction: t
      });

      // 更新分类文章计数
      if (post.categoryId) {
        await this.app.service('categories').Model.decrement('postsCount', {
          where: { id: post.categoryId },
          transaction: t
        });
      }

      return result;
    });
  }

  // 处理标签的辅助方法
  async handleTags(postId, tagSlugs, transaction) {
    const TagModel = this.app.service('tags').Model;
    const PostTagModel = this.app.service('postTags').Model;
    
    // 获取或创建标签
    const tags = [];
    for (const slug of tagSlugs) {
      let tag = await TagModel.findOne({
        where: { slug },
        transaction
      });
      
      if (!tag) {
        tag = await TagModel.create({
          name: slug.replace(/-/g, ' ').replace(/\b\w/g, l => l.toUpperCase()),
          slug: slug
        }, { transaction });
      }
      
      tags.push(tag);
    }

    // 创建文章标签关联
    const associations = tags.map(tag => ({
      postId: postId,
      tagId: tag.id
    }));

    await PostTagModel.bulkCreate(associations, { transaction });

    // 更新标签文章计数
    for (const tag of tags) {
      await TagModel.increment('postsCount', {
        where: { id: tag.id },
        transaction
      });
    }
  }
}

2. 高级事务模式

// 分布式事务处理
class OrderService {
  constructor(app) {
    this.app = app;
    this.sequelize = app.get('sequelizeClient');
  }

  async createOrder(orderData, params) {
    const { user } = params;
    
    // 使用托管事务
    return this.sequelize.transaction({
      isolationLevel: this.sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED
    }, async (t) => {
      try {
        // 1. 创建订单
        const order = await this.app.service('orders').create({
          ...orderData,
          userId: user.id,
          status: 'pending'
        }, { transaction: t });

        // 2. 处理订单项目
        const orderItems = [];
        for (const item of orderData.items) {
          // 检查库存
          const product = await this.app.service('products').Model.findByPk(
            item.productId,
            { 
              transaction: t,
              lock: t.LOCK.UPDATE  // 行级锁
            }
          );

          if (!product) {
            throw new Error(`产品 \${item.productId} 不存在`);
          }

          if (product.stock < item.quantity) {
            throw new Error(`产品 \${product.name} 库存不足`);
          }

          // 减少库存
          await product.decrement('stock', {
            by: item.quantity,
            transaction: t
          });

          // 创建订单项目
          const orderItem = await this.app.service('orderItems').create({
            orderId: order.id,
            productId: item.productId,
            quantity: item.quantity,
            price: product.price,
            total: product.price * item.quantity
          }, { transaction: t });

          orderItems.push(orderItem);
        }

        // 3. 计算总金额
        const totalAmount = orderItems.reduce((sum, item) => sum + item.total, 0);
        
        await this.app.service('orders').patch(order.id, {
          totalAmount: totalAmount
        }, { transaction: t });

        // 4. 创建支付记录
        const payment = await this.app.service('payments').create({
          orderId: order.id,
          amount: totalAmount,
          status: 'pending'
        }, { transaction: t });

        // 5. 发送通知（异步，不在事务中）
        setImmediate(() => {
          this.sendOrderNotification(order.id).catch(console.error);
        });

        return {
          order,
          orderItems,
          payment
        };

      } catch (error) {
        // 事务会自动回滚
        throw error;
      }
    });
  }

  // 批量操作事务
  async bulkUpdatePrices(updates, params) {
    return this.sequelize.transaction(async (t) => {
      const results = [];
      
      // 批量更新，但保持事务一致性
      for (const update of updates) {
        const product = await this.app.service('products').patch(
          update.id,
          { price: update.newPrice },
          { transaction: t }
        );
        
        // 记录价格变更历史
        await this.app.service('priceHistory').create({
          productId: update.id,
          oldPrice: update.oldPrice,
          newPrice: update.newPrice,
          changedBy: params.user.id,
          reason: update.reason
        }, { transaction: t });
        
        results.push(product);
      }
      
      return results;
    });
  }

  // 异步通知方法
  async sendOrderNotification(orderId) {
    try {
      const order = await this.app.service('orders').get(orderId, {
        query: { $populate: ['user', 'items'] }
      });
      
      // 发送邮件通知
      await this.app.service('mailer').create({
        to: order.user.email,
        template: 'order-confirmation',
        data: { order }
      });
      
      // 发送短信通知
      if (order.user.phone) {
        await this.app.service('sms').create({
          to: order.user.phone,
          message: `您的订单 \${order.id} 已创建成功`
        });
      }
    } catch (error) {
      console.error('发送订单通知失败:', error);
    }
  }
}

性能优化

1. 查询优化

// 查询优化策略
class OptimizedPostsService extends Service {
  constructor(options, app) {
    super(options, app);
    this.app = app;
    
    // 查询缓存
    this.queryCache = new Map();
    this.cacheTimeout = 5 * 60 * 1000; // 5分钟
  }

  async find(params) {
    const { query = {} } = params;
    
    // 生成缓存键
    const cacheKey = this.generateCacheKey(query);
    
    // 检查缓存
    if (this.queryCache.has(cacheKey)) {
      const cached = this.queryCache.get(cacheKey);
      if (Date.now() - cached.timestamp < this.cacheTimeout) {
        return cached.data;
      }
      this.queryCache.delete(cacheKey);
    }

    const sequelizeOptions = {
      // 只选择需要的字段
      attributes: [
        'id', 'title', 'slug', 'excerpt', 'featuredImage',
        'status', 'publishedAt', 'viewCount', 'likeCount', 'commentCount'
      ],
      
      include: [
        {
          model: this.app.service('users').Model,
          as: 'author',
          attributes: ['id', 'username', 'firstName', 'lastName', 'avatar']
        },
        {
          model: this.app.service('categories').Model,
          as: 'category',
          attributes: ['id', 'name', 'slug', 'color']
        }
      ],
      
      // 优化排序
      order: [['publishedAt', 'DESC']],
      
      // 限制结果数量
      limit: Math.min(query.$limit || 20, 100)
    };

    // 避免 N+1 查询
    if (query.$populate && query.$populate.includes('tags')) {
      sequelizeOptions.include.push({
        model: this.app.service('tags').Model,
        as: 'tags',
        attributes: ['id', 'name', 'slug', 'color'],
        through: { attributes: [] }
      });
    }

    params.sequelize = sequelizeOptions;
    
    const result = await super.find(params);
    
    // 缓存结果
    this.queryCache.set(cacheKey, {
      data: result,
      timestamp: Date.now()
    });
    
    return result;
  }

  // 预加载相关数据
  async findWithPreload(params) {
    const { query = {} } = params;
    
    return this.Model.findAll({
      where: query,
      include: [
        {
          model: this.app.service('users').Model,
          as: 'author',
          include: [
            {
              model: this.app.service('userProfiles').Model,
              as: 'profile'
            }
          ]
        },
        {
          model: this.app.service('categories').Model,
          as: 'category'
        },
        {
          model: this.app.service('tags').Model,
          as: 'tags',
          through: { attributes: [] }
        },
        {
          model: this.app.service('comments').Model,
          as: 'comments',
          where: { status: 'approved' },
          required: false,
          include: [
            {
              model: this.app.service('users').Model,
              as: 'author',
              attributes: ['id', 'username', 'avatar']
            }
          ]
        }
      ],
      order: [
        ['publishedAt', 'DESC'],
        [{ model: this.app.service('comments').Model, as: 'comments' }, 'createdAt', 'ASC']
      ]
    });
  }

  // 分页优化
  async findWithCursor(params) {
    const { query = {} } = params;
    const { cursor, limit = 20 } = query;
    
    const whereClause = { status: 'published' };
    
    // 游标分页
    if (cursor) {
      whereClause.id = { [Op.lt]: cursor };
    }
    
    const posts = await this.Model.findAll({
      where: whereClause,
      include: ['author', 'category'],
      order: [['id', 'DESC']],
      limit: limit + 1  // 多查一个用于判断是否有下一页
    });
    
    const hasNextPage = posts.length > limit;
    if (hasNextPage) {
      posts.pop();  // 移除多查的那一个
    }
    
    return {
      data: posts,
      hasNextPage,
      nextCursor: hasNextPage ? posts[posts.length - 1].id : null
    };
  }

  generateCacheKey(query) {
    return JSON.stringify(query);
  }

  // 清理缓存
  clearCache() {
    this.queryCache.clear();
  }
}

2. 数据库连接优化

// src/sequelize-optimized.js
const { Sequelize } = require('sequelize');

module.exports = function (app) {
  const config = app.get('sequelize');
  
  const sequelize = new Sequelize(config.database, config.username, config.password, {
    ...config,
    
    // 连接池优化
    pool: {
      max: 20,          // 最大连接数
      min: 5,           // 最小连接数
      acquire: 60000,   // 获取连接超时时间
      idle: 10000,      // 连接空闲时间
      evict: 1000       // 检查空闲连接间隔
    },
    
    // 查询优化
    dialectOptions: {
      // PostgreSQL 优化
      statement_timeout: 30000,
      idle_in_transaction_session_timeout: 30000,
      
      // MySQL 优化
      acquireTimeout: 60000,
      timeout: 60000,
      
      // 启用 SSL（生产环境）
      ssl: process.env.NODE_ENV === 'production' ? {
        require: true,
        rejectUnauthorized: false
      } : false
    },
    
    // 日志优化
    logging: process.env.NODE_ENV === 'development' ? 
      (sql, timing) => {
        if (timing > 1000) {  // 慢查询警告
          console.warn(`慢查询 (\${timing}ms):`, sql);
        }
      } : false,
    
    // 基准测试
    benchmark: process.env.NODE_ENV === 'development',
    
    // 查询优化
    define: {
      // 默认不使用时间戳
      timestamps: true,
      // 使用下划线命名
      underscored: true,
      // 禁用复数表名
      freezeTableName: true
    },
    
    // 事务优化
    transactionType: 'IMMEDIATE',
    isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED,
    
    // 重试配置
    retry: {
      max: 3,
      match: [
        /SQLITE_BUSY/,
        /ECONNRESET/,
        /SequelizeConnectionError/,
        /SequelizeConnectionRefusedError/,
        /SequelizeHostNotFoundError/,
        /SequelizeHostNotReachableError/,
        /SequelizeInvalidConnectionError/,
        /SequelizeConnectionTimedOutError/
      ]
    }
  });

  // 连接事件监听
  sequelize.authenticate()
    .then(() => {
      console.log('✅ Sequelize 数据库连接成功');
      
      // 监控连接池状态
      setInterval(() => {
        const pool = sequelize.connectionManager.pool;
        console.log(`连接池状态: 使用中 \${pool.used}/\${pool.size}, 等待中 \${pool.pending}`);
      }, 60000);  // 每分钟检查一次
    })
    .catch(err => {
      console.error('❌ Sequelize 数据库连接失败:', err);
      process.exit(1);
    });

  app.set('sequelizeClient', sequelize);
  
  return sequelize;
};

3. 索引优化

// 索引优化建议
const indexOptimizations = {
  // 复合索引
  posts: [
    // 状态 + 发布时间（常用查询组合）
    {
      fields: ['status', 'published_at'],
      name: 'idx_posts_status_published'
    },
    
    // 作者 + 状态（作者文章列表）
    {
      fields: ['author_id', 'status'],
      name: 'idx_posts_author_status'
    },
    
    // 分类 + 状态 + 发布时间
    {
      fields: ['category_id', 'status', 'published_at'],
      name: 'idx_posts_category_status_published'
    },
    
    // 全文搜索索引（PostgreSQL）
    {
      fields: [sequelize.literal("to_tsvector('english', title || ' ' || content)")],
      using: 'gin',
      name: 'idx_posts_fulltext'
    }
  ],
  
  // 用户表索引
  users: [
    // 邮箱（唯一）
    {
      fields: ['email'],
      unique: true,
      name: 'idx_users_email'
    },
    
    // 用户名（唯一）
    {
      fields: ['username'],
      unique: true,
      name: 'idx_users_username'
    },
    
    // 状态 + 角色（管理查询）
    {
      fields: ['status', 'role'],
      name: 'idx_users_status_role'
    },
    
    // 创建时间（注册统计）
    {
      fields: ['created_at'],
      name: 'idx_users_created_at'
    }
  ],
  
  // 评论表索引
  comments: [
    // 文章 + 状态（文章评论列表）
    {
      fields: ['post_id', 'status'],
      name: 'idx_comments_post_status'
    },
    
    // 父评论（回复查询）
    {
      fields: ['parent_id'],
      name: 'idx_comments_parent'
    },
    
    // 作者 + 创建时间（用户评论历史）
    {
      fields: ['author_id', 'created_at'],
      name: 'idx_comments_author_created'
    }
  ]
};

// 自动创建索引的迁移
module.exports = {
  up: async (queryInterface, Sequelize) => {
    for (const [tableName, indexes] of Object.entries(indexOptimizations)) {
      for (const index of indexes) {
        try {
          await queryInterface.addIndex(tableName, index);
          console.log(`✅ 创建索引: \${index.name}`);
        } catch (error) {
          console.error(`❌ 创建索引失败 \${index.name}:`, error.message);
        }
      }
    }
  },
  
  down: async (queryInterface, Sequelize) => {
    for (const [tableName, indexes] of Object.entries(indexOptimizations)) {
      for (const index of indexes) {
        try {
          await queryInterface.removeIndex(tableName, index.name);
          console.log(`✅ 删除索引: \${index.name}`);
        } catch (error) {
          console.error(`❌ 删除索引失败 \${index.name}:`, error.message);
        }
      }
    }
  }
};

总结

通过这篇文章，我们深入学习了 Sequelize 的高级查询和性能优化：

✅ 高级查询技巧：

• 复杂条件查询和聚合
• 原生 SQL 查询应用
• 子查询和窗口函数

✅ 事务处理：

• 基础事务使用
• 分布式事务模式
• 事务隔离级别

✅ 性能优化：

• 查询缓存策略
• N+1 查询避免
• 连接池优化

✅ 数据库优化：

• 索引设计原则
• 查询性能监控
• 连接管理优化

掌握了这些高级技巧，你就能够构建出高性能、高可靠性的 Feathers.js + Sequelize 应用。

下一篇文章，我们将通过一个完整的电商项目来综合应用这些知识。

---

相关文章推荐：

• Feathers.js + Sequelize 模型设计与关联 - 构建复杂的数据关系
• Feathers.js + Sequelize 实战项目 - 构建完整的电商系统

有问题欢迎留言讨论，我会及时回复大家！