乐闻世界logo
6月2日 01:50

How to optimize TypeORM performance? Best practices including query optimization, caching strategies, and batch operations

Performance optimization is an important aspect of TypeORM development. Proper optimization strategies can significantly improve application response speed and database efficiency. This article details various TypeORM performance optimization techniques and best practices.

Query Optimization

1. Avoid N+1 Query Problem

The N+1 query problem is a common performance issue where 1 main query is executed followed by N related queries for each result.

typescript
// ❌ Bad: N+1 query
const users = await userRepository.find();
for (const user of users) {
  const posts = await postRepository.find({ where: { authorId: user.id } });
  user.posts = posts;
}

// ✅ Good: Use relation query
const users = await userRepository.find({
  relations: ['posts']
});

// ✅ Better: Use QueryBuilder
const users = await userRepository
  .createQueryBuilder('user')
  .leftJoinAndSelect('user.posts', 'post')
  .getMany();

2. Selective Field Loading

Query only needed fields to reduce data transfer.

typescript
// ❌ Query all fields
const users = await userRepository.find();

// ✅ Query only needed fields
const users = await userRepository.find({
  select: ['id', 'name', 'email']
});

// Use QueryBuilder to select fields
const users = await userRepository
  .createQueryBuilder('user')
  .select(['user.id', 'user.name', 'user.email'])
  .getMany();

3. Use Indexes to Optimize Queries

Add database indexes for frequently queried conditions.

typescript
@Entity()
@Index(['email']) // Single column index
@Index(['status', 'createdAt']) // Composite index
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  @Index() // Add index using decorator
  email: string;

  @Column()
  status: string;

  @Column()
  createdAt: Date;
}

// Or create index using migration
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.createIndex(
    'user',
    new TableIndex({
      name: 'IDX_USER_EMAIL',
      columnNames: ['email'],
      isUnique: true,
    })
  );
}

4. Use LIMIT and OFFSET for Pagination

typescript
// Basic pagination
const page = 1;
const pageSize = 10;
const users = await userRepository.find({
  skip: (page - 1) * pageSize,
  take: pageSize,
  order: {
    createdAt: 'DESC'
  }
});

// Use QueryBuilder for pagination
const users = await userRepository
  .createQueryBuilder('user')
  .skip((page - 1) * pageSize)
  .take(pageSize)
  .orderBy('user.createdAt', 'DESC')
  .getMany();

// Get total count and paginated data
const [users, total] = await userRepository.findAndCount({
  skip: (page - 1) * pageSize,
  take: pageSize,
});

5. Use Cursor Pagination

For large datasets, cursor pagination is more efficient than OFFSET pagination.

typescript
// Cursor-based pagination
async function getUsersWithCursor(cursor: number, limit: number = 10) {
  const query = userRepository
    .createQueryBuilder('user')
    .orderBy('user.id', 'ASC')
    .limit(limit + 1); // Fetch one extra to check if there's a next page

  if (cursor) {
    query.where('user.id > :cursor', { cursor });
  }

  const users = await query.getMany();
  const hasNextPage = users.length > limit;
  
  if (hasNextPage) {
    users.pop(); // Remove the extra one
  }

  return {
    data: users,
    nextCursor: hasNextPage ? users[users.length - 1].id : null,
    hasNextPage
  };
}

Cache Optimization

1. Enable Query Cache

typescript
// Enable cache in DataSource configuration
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  cache: {
    type: 'redis',
    options: {
      host: 'localhost',
      port: 6379,
    },
    duration: 30000, // Cache for 30 seconds
  },
});

// Use cache in queries
const users = await userRepository.find({
  cache: {
    id: 'users_list',
    milliseconds: 60000, // Cache for 60 seconds
  }
});

// Use QueryBuilder cache
const users = await userRepository
  .createQueryBuilder('user')
  .cache(60000) // Cache for 60 seconds
  .getMany();

2. Entity Cache

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  @Cache() // Enable entity cache
  email: string;
}

// Automatically use cache when querying
const user = await userRepository.findOne({ 
  where: { id: 1 },
  cache: true
});

3. Custom Cache Strategy

typescript
class CacheService {
  private cache = new Map<string, { data: any, expires: number }>();

  async get<T>(key: string): Promise<T | null> {
    const item = this.cache.get(key);
    if (!item) return null;
    
    if (Date.now() > item.expires) {
      this.cache.delete(key);
      return null;
    }
    
    return item.data;
  }

  async set<T>(key: string, data: T, ttl: number = 60000): Promise<void> {
    this.cache.set(key, {
      data,
      expires: Date.now() + ttl
    });
  }

  async invalidate(key: string): Promise<void> {
    this.cache.delete(key);
  }
}

// Use custom cache
const cacheService = new CacheService();

async function getUserWithCache(userId: number) {
  const cacheKey = `user:${userId}`;
  let user = await cacheService.get<User>(cacheKey);
  
  if (!user) {
    user = await userRepository.findOne({ where: { id: userId } });
    await cacheService.set(cacheKey, user, 60000); // Cache for 60 seconds
  }
  
  return user;
}

Batch Operation Optimization

1. Batch Insert

typescript
// ❌ Bad: Insert one by one
for (const userData of usersData) {
  const user = userRepository.create(userData);
  await userRepository.save(user);
}

// ✅ Good: Batch insert
const users = userRepository.create(usersData);
await userRepository.save(users);

// ✅ Better: Use QueryBuilder
await userRepository
  .createQueryBuilder()
  .insert()
  .into(User)
  .values(usersData)
  .execute();

// ✅ Best: Use native SQL batch insert
const values = usersData.map(u => `('${u.name}', '${u.email}')`).join(',');
await userRepository.query(
  `INSERT INTO user (name, email) VALUES ${values}`
);

2. Batch Update

typescript
// ❌ Bad: Update one by one
for (const user of users) {
  user.status = 'active';
  await userRepository.save(user);
}

// ✅ Good: Use QueryBuilder batch update
await userRepository
  .createQueryBuilder()
  .update(User)
  .set({ status: 'active' })
  .where('id IN :ids', { ids: users.map(u => u.id) })
  .execute();

// ✅ Better: Use native SQL
const ids = users.map(u => u.id).join(',');
await userRepository.query(
  `UPDATE user SET status = 'active' WHERE id IN (${ids})`
);

3. Batch Delete

typescript
// ❌ Bad: Delete one by one
for (const user of users) {
  await userRepository.delete(user.id);
}

// ✅ Good: Use QueryBuilder batch delete
await userRepository
  .createQueryBuilder()
  .delete()
  .from(User)
  .where('id IN :ids', { ids: users.map(u => u.id) })
  .execute();

// ✅ Better: Use native SQL
const ids = users.map(u => u.id).join(',');
await userRepository.query(
  `DELETE FROM user WHERE id IN (${ids})`
);

Connection Pool Optimization

1. Configure Connection Pool

typescript
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  extra: {
    connectionLimit: 10, // Maximum connections
    acquireTimeout: 60000, // Connection acquisition timeout
    timeout: 60000, // Query timeout
    waitForConnections: true, // Wait for available connections
    queueLimit: 0, // Queue limit, 0 means unlimited
  },
});

2. Monitor Connection Pool Status

typescript
class ConnectionPoolMonitor {
  constructor(private dataSource: DataSource) {}

  getPoolStats() {
    const pool = this.dataSource.driver.master;
    return {
      totalConnections: pool.pool._allConnections.length,
      freeConnections: pool.pool._freeConnections.length,
      queuedRequests: pool.pool._connectionQueue.length,
    };
  }

  logPoolStats() {
    const stats = this.getPoolStats();
    console.log('Connection Pool Stats:', stats);
  }
}

// Monitor connection pool periodically
const monitor = new ConnectionPoolMonitor(dataSource);
setInterval(() => monitor.logPoolStats(), 60000);

Lazy Loading Optimization

1. Use Lazy Loading

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Post, post => post.author)
  posts: Promise<Post[]>; // Use Promise for lazy loading
}

// Related data not loaded when querying
const user = await userRepository.findOne({ where: { id: 1 } });

// Load only when needed
const posts = await user.posts;

2. Control Lazy Loading Depth

typescript
// Set maximum lazy loading depth
const dataSource = new DataSource({
  type: 'mysql',
  // ... other configurations
  maxQueryExecutionTime: 1000, // Maximum query execution time
});

Query Analysis and Monitoring

1. Enable Query Logging

typescript
const dataSource = new DataSource({
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: 'password',
  database: 'myapp',
  entities: [User, Post],
  logging: true, // Enable logging
  maxQueryExecutionTime: 1000, // Log queries over 1 second
});

2. Use Query Analyzer

typescript
class QueryAnalyzer {
  private queries: Map<string, { count: number, totalTime: number }> = new Map();

  logQuery(query: string, duration: number) {
    const key = query;
    if (!this.queries.has(key)) {
      this.queries.set(key, { count: 0, totalTime: 0 });
    }
    
    const stats = this.queries.get(key);
    stats.count++;
    stats.totalTime += duration;
  }

  getSlowQueries(threshold: number = 1000) {
    const slowQueries: Array<{ query: string, avgTime: number, count: number }> = [];
    
    for (const [query, stats] of this.queries.entries()) {
      const avgTime = stats.totalTime / stats.count;
      if (avgTime > threshold) {
        slowQueries.push({ query, avgTime, count: stats.count });
      }
    }
    
    return slowQueries.sort((a, b) => b.avgTime - a.avgTime);
  }
}

3. Use EXPLAIN to Analyze Queries

typescript
async function analyzeQuery(query: string) {
  const result = await dataSource.query(`EXPLAIN ${query}`);
  console.log('Query Analysis:', result);
  
  // Check if index is used
  const usingIndex = result.some(row => row.type === 'ref' || row.type === 'eq_ref');
  if (!usingIndex) {
    console.warn('Query not using index:', query);
  }
}

// Usage example
analyzeQuery('SELECT * FROM user WHERE email = "test@example.com"');

Database Optimization

1. Optimize Table Structure

typescript
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', length: 100 }) // Specify appropriate length
  name: string;

  @Column({ type: 'varchar', length: 255, unique: true })
  email: string;

  @Column({ type: 'tinyint', default: 1 }) // Use tinyint instead of boolean
  isActive: boolean;

  @Column({ type: 'int', unsigned: true }) // Use unsigned to save space
  age: number;

  @CreateDateColumn({ type: 'timestamp' })
  createdAt: Date;
}

2. Use Appropriate Data Types

typescript
// ✅ Good: Use appropriate data types
@Column({ type: 'tinyint' })
status: number; // 0-255

@Column({ type: 'smallint' })
score: number; // -32768 to 32767

@Column({ type: 'int' })
count: number; // -2147483648 to 2147483647

@Column({ type: 'bigint' })
largeNumber: number; // Large integer

@Column({ type: 'decimal', precision: 10, scale: 2 })
price: number; // Use decimal for amounts

3. Regular Database Maintenance

typescript
// Regularly optimize tables
async function optimizeTables() {
  const tables = ['user', 'post', 'comment'];
  
  for (const table of tables) {
    await dataSource.query(`OPTIMIZE TABLE ${table}`);
  }
}

// Regularly analyze tables
async function analyzeTables() {
  const tables = ['user', 'post', 'comment'];
  
  for (const table of tables) {
    await dataSource.query(`ANALYZE TABLE ${table}`);
  }
}

Performance Testing and Benchmarking

1. Query Performance Testing

typescript
import { performance } from 'perf_hooks';

async function benchmarkQuery(
  name: string,
  query: () => Promise<any>,
  iterations: number = 100
) {
  const times: number[] = [];
  
  for (let i = 0; i < iterations; i++) {
    const start = performance.now();
    await query();
    const end = performance.now();
    times.push(end - start);
  }
  
  const avgTime = times.reduce((a, b) => a + b, 0) / times.length;
  const minTime = Math.min(...times);
  const maxTime = Math.max(...times);
  
  console.log(`${name}:`);
  console.log(`  Average: ${avgTime.toFixed(2)}ms`);
  console.log(`  Min: ${minTime.toFixed(2)}ms`);
  console.log(`  Max: ${maxTime.toFixed(2)}ms`);
}

// Usage example
await benchmarkQuery('Find users with relations', async () => {
  await userRepository.find({ relations: ['posts'] });
});

Best Practices Summary

  1. Avoid N+1 queries: Use relation queries or QueryBuilder
  2. Selective field loading: Query only needed fields
  3. Use indexes: Add indexes for frequently queried conditions
  4. Implement pagination: Use LIMIT and OFFSET or cursor pagination
  5. Enable caching: Use cache for data that doesn't change often
  6. Batch operations: Use batch insert, update, delete
  7. Optimize connection pool: Configure connection pool parameters appropriately
  8. Monitor performance: Enable logging, analyze slow queries
  9. Optimize table structure: Use appropriate data types and lengths
  10. Regular maintenance: Optimize and analyze database tables

Through these optimization strategies, you can significantly improve the performance of TypeORM applications and provide a better user experience.

标签：TypeORM