数据库性能优化 #

// 3. 应用层读写分离实现
class ReadWriteSeparation {
  constructor(masterConfig, slaveConfigs) {
    this.master = mysql.createPool(masterConfig);
    this.slaves = slaveConfigs.map(config => mysql.createPool(config));
    this.currentSlaveIndex = 0;
  }
  
  async executeWrite(query, params) {
    console.log('Executing write query on master:', query);
    const [results] = await this.master.execute(query, params);
    return results;
  }
  
  async executeRead(query, params) {
    // 轮询选择从库
    const slave = this.slaves[this.currentSlaveIndex];
    this.currentSlaveIndex = (this.currentSlaveIndex + 1) % this.slaves.length;
    
    console.log('Executing read query on slave:', query);
    const [results] = await slave.execute(query, params);
    return results;
  }
  
  // 智能路由
  async execute(sql, params) {
    const sqlType = this.getSqlType(sql);
    
    if (this.isWriteQuery(sqlType)) {
      return await this.executeWrite(sql, params);
    } else {
      return await this.executeRead(sql, params);
    }
  }
  
  getSqlType(sql) {
    const trimmedSql = sql.trim().toUpperCase();
    if (trimmedSql.startsWith('SELECT')) return 'SELECT';
    if (trimmedSql.startsWith('INSERT')) return 'INSERT';
    if (trimmedSql.startsWith('UPDATE')) return 'UPDATE';
    if (trimmedSql.startsWith('DELETE')) return 'DELETE';
    if (trimmedSql.startsWith('REPLACE')) return 'REPLACE';
    return 'OTHER';
  }
  
  isWriteQuery(sqlType) {
    return ['INSERT', 'UPDATE', 'DELETE', 'REPLACE'].includes(sqlType);
  }
}

// 4. 分库分表路由实现
class DatabaseSharding {
  constructor(shards) {
    this.shards = shards;
    this.pools = {};
    
    // 创建连接池
    shards.forEach((config, index) => {
      this.pools[index] = mysql.createPool(config);
    });
  }
  
  // 基于用户ID的哈希分片
  getShardId(userId, shardCount = 4) {
    return userId % shardCount;
  }
  
  // 基于范围的分片
  getRangeShardId(value, ranges) {
    for (let i = 0; i < ranges.length; i++) {
      if (value <= ranges[i].max) {
        return ranges[i].shardId;
      }
    }
    return 0; // 默认分片
  }
  
  async executeByUserId(userId, query, params) {
    const shardId = this.getShardId(userId);
    const pool = this.pools[shardId];
    
    const [results] = await pool.execute(query, params);
    return results;
  }
  
  // 跨分片查询（需要合并结果）
  async executeAcrossShards(query, params) {
    const promises = Object.values(this.pools).map(pool => 
      pool.execute(query, params).catch(err => {
        console.error('Shard query error:', err);
        return [[]]; // 返回空结果避免中断其他分片
      })
    );
    
    const results = await Promise.all(promises);
    // 合并所有分片的结果
    return results.flat().flat();
  }
}

// 数据库性能监控类
class DatabasePerformanceMonitor {
  constructor(connection) {
    this.connection = connection;
    this.metrics = {};
  }
  
  async collectMetrics() {
    const metrics = {
      connections: await this.getConnectionMetrics(),
      queries: await this.getQueryMetrics(),
      locks: await this.getLockMetrics(),
      cache: await this.getCacheMetrics(),
      tables: await this.getTableMetrics()
    };
    
    this.metrics = metrics;
    return metrics;
  }
  
  async getConnectionMetrics() {
    const [rows] = await this.connection.execute(`
      SELECT 
        COUNT(*) as total_connections,
        SUM(IF(COMMAND = 'Sleep', 1, 0)) as sleeping_connections,
        MAX(TIME) as max_execution_time
      FROM information_schema.PROCESSLIST
    `);
    
    return rows[0];
  }
  
  async getQueryMetrics() {
    const [rows] = await this.connection.execute(`
      SELECT 
        COUNT(*) as total_queries,
        AVG(query_time) as avg_query_time,
        MAX(query_time) as max_query_time,
        SUM(rows_examined) as total_rows_examined
      FROM mysql.slow_log
      WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
    `);
    
    return rows[0];
  }
  
  async getLockMetrics() {
    const [rows] = await this.connection.execute(`
      SELECT 
        COUNT(*) as lock_waits,
        AVG(wait_time) as avg_wait_time
      FROM performance_schema.data_lock_waits
    `);
    
    return rows[0];
  }
  
  async getCacheMetrics() {
    const [rows] = await this.connection.execute(`
      SELECT 
        VARIABLE_VALUE as buffer_pool_size
      FROM information_schema.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'
    `);
    
    return rows[0];
  }
  
  async getTableMetrics() {
    const [rows] = await this.connection.execute(`
      SELECT 
        SUM(TABLE_ROWS) as total_rows,
        SUM(DATA_LENGTH) as total_data_size,
        SUM(INDEX_LENGTH) as total_index_size
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = DATABASE()
    `);
    
    return rows[0];
  }
  
  async detectPerformanceIssues() {
    const metrics = await this.collectMetrics();
    const issues = [];
    
    // 检查连接数过多
    if (metrics.connections.total_connections > 800) {
      issues.push(`High connection count: ${metrics.connections.total_connections}`);
    }
    
    // 检查睡眠连接过多
    if (metrics.connections.sleeping_connections > 500) {
      issues.push(`High sleeping connection count: ${metrics.connections.sleeping_connections}`);
    }
    
    // 检查平均查询时间过长
    if (metrics.queries.avg_query_time > 1) {
      issues.push(`High average query time: ${metrics.queries.avg_query_time}s`);
    }
    
    return issues;
  }
}

// 2. 电商系统查询优化实现
class EcommerceDatabaseOptimizer {
  constructor(connection) {
    this.connection = connection;
  }
  
  // 优化商品搜索查询
  async searchProducts(searchParams) {
    const {
      keywords = '',
      categoryId,
      minPrice,
      maxPrice,
      sortBy = 'relevance',
      page = 1,
      limit = 20
    } = searchParams;
    
    // 使用全文索引优化关键词搜索
    let query = `
      SELECT 
        p.id,
        p.name,
        p.price,
        p.category_id,
        p.rating,
        p.review_count,
        i.thumbnail_url,
        MATCH(p.name, p.description) AGAINST(? IN NATURAL LANGUAGE MODE) as relevance_score
      FROM products p
      LEFT JOIN product_images i ON p.id = i.product_id AND i.is_primary = 1
      WHERE 1=1
    `;
    
    const params = [keywords];
    
    if (keywords) {
      query += ` AND MATCH(p.name, p.description) AGAINST(? IN NATURAL LANGUAGE MODE)`;
      params.push(keywords);
    }
    
    if (categoryId) {
      query += ' AND p.category_id = ?';
      params.push(categoryId);
    }
    
    if (minPrice) {
      query += ' AND p.price >= ?';
      params.push(minPrice);
    }
    
    if (maxPrice) {
      query += ' AND p.price <= ?';
      params.push(maxPrice);
    }
    
    // 使用游标分页而不是 OFFSET
    const offset = (page - 1) * limit;
    query += ` ORDER BY ${this.getSortExpression(sortBy)} LIMIT ? OFFSET ?`;
    params.push(limit, offset);
    
    const [results] = await this.connection.execute(query, params);
    return results;
  }
  
  getSortExpression(sortBy) {
    switch(sortBy) {
      case 'price_asc': return 'p.price ASC';
      case 'price_desc': return 'p.price DESC';
      case 'rating': return 'p.rating DESC, p.review_count DESC';
      case 'newest': return 'p.created_at DESC';
      case 'relevance': return 'relevance_score DESC';
      default: return 'p.id DESC';
    }
  }
  
  // 优化用户订单查询
  async getUserOrders(userId, statusFilters = [], page = 1, limit = 10) {
    let query = `
      SELECT 
        o.id,
        o.order_number,
        o.total_amount,
        o.status,
        o.created_at,
        COUNT(oi.id) as item_count,
        SUM(oi.quantity * oi.unit_price) as calculated_total
      FROM orders_optimized o
      LEFT JOIN order_items oi ON o.id = oi.order_id
    `;
    
    const params = [userId];
    
    // 使用参数化查询防止 SQL 注入
    query += ' WHERE o.user_id = ?';
    
    if (statusFilters && statusFilters.length > 0) {
      query += ` AND o.status IN (${statusFilters.map(() => '?').join(',')})`;
      params.push(...statusFilters);
    }
    
    query += `
      GROUP BY o.id
      ORDER BY o.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    params.push(limit, (page - 1) * limit);
    
    const [orders] = await this.connection.execute(query, params);
    return orders;
  }
  
  // 批量订单处理优化
  async batchUpdateOrderStatus(orderIds, newStatus) {
    if (!orderIds || orderIds.length === 0) return 0;
    
    const placeholders = orderIds.map(() => '?').join(',');
    const query = `
      UPDATE orders_optimized 
      SET status = ?, updated_at = NOW() 
      WHERE id IN (${placeholders})
    `;
    
    const params = [newStatus, ...orderIds];
    const [result] = await this.connection.execute(query, params);
    
    return result.affectedRows;
  }
  
  // 数据归档
  async archiveOldOrders(archiveBeforeDate) {
    // 将旧订单迁移到归档表
    const archiveQuery = `
      INSERT INTO orders_archive 
      SELECT * FROM orders_optimized 
      WHERE created_at < ? AND status = 'delivered'
    `;
    
    await this.connection.execute(archiveQuery, [archiveBeforeDate]);
    
    // 删除已归档的订单
    const deleteQuery = `
      DELETE FROM orders_optimized 
      WHERE created_at < ? AND status = 'delivered'
    `;
    
    const [result] = await this.connection.execute(deleteQuery, [archiveBeforeDate]);
    return result.affectedRows;
  }
}

// 数据库性能优化检查清单
const databaseOptimizationChecklist = {
  // 架构优化
  architecture: [
    '实施读写分离',
    '设计合理的分库分表策略',
    '使用连接池管理连接',
    '配置主从复制',
    '实现数据归档策略',
    '使用缓存层'
  ],
  
  // 索引优化
  indexing: [
    '为经常查询的字段创建索引',
    '创建复合索引优化多字段查询',
    '使用覆盖索引避免回表',
    '定期分析索引使用情况',
    '删除未使用的索引',
    '为排序和分组字段创建索引'
  ],
  
  // 查询优化
  queries: [
    '使用 EXPLAIN 分析查询执行计划',
    '避免 SELECT * 查询',
    '优化 JOIN 查询',
    '使用参数化查询防止注入',
    '实现分页优化',
    '避免在 WHERE 子句中使用函数'
  ],
  
  // 配置优化
  configuration: [
    '调整缓冲区大小',
    '优化连接池配置',
    '设置合适的超时值',
    '配置慢查询日志',
    '启用查询缓存',
    '调整日志级别'
  ],
  
  // 监控优化
  monitoring: [
    '监控查询性能',
    '跟踪慢查询',
    '监控连接使用情况',
    '检查锁等待情况',
    '分析表统计信息',
    '定期性能基准测试'
  ],
  
  // 维护优化
  maintenance: [
    '定期更新统计信息',
    '优化表结构',
    '清理过期数据',
    '重建索引',
    '备份策略优化',
    '安全补丁更新'
  ]
};

// 数据库性能评分卡
class DatabasePerformanceScorecard {
  constructor() {
    this.score = 0;
    this.maxScore = 100;
  }
  
  calculateScore(completedItems, totalItems) {
    return Math.round((completedItems / totalItems) * 100);
  }
  
  generateReport() {
    const categories = Object.keys(databaseOptimizationChecklist);
    const report = {};
    
    categories.forEach(category => {
      const items = databaseOptimizationChecklist[category];
      report[category] = {
        total: items.length,
        completed: 0, // 需要根据实际情况填充
        score: 0
      };
    });
    
    return report;
  }
}