Database query performance improvement

Database Query Performance Optimization

Koa2, as a lightweight Node.js framework, often needs to handle database queries. Optimizing query performance can significantly improve application response speed and reduce server load. Below are some practical optimization strategies.

Index Optimization

Indexes are the most direct way to improve query speed. Proper indexes allow the database to quickly locate data, avoiding full table scans. For example, creating an index for frequently queried fields in MongoDB:

// Create an index for the username field in the users collection
db.users.createIndex({ username: 1 });

In MySQL, you can add indexes to columns often used in WHERE conditions:

-- Add an index to the email column in the users table
ALTER TABLE users ADD INDEX idx_email (email);

Note that more indexes are not always better. Excessive indexes increase the overhead of write operations because indexes also need to be updated during every insert, update, or delete operation.

Query Statement Optimization

Writing efficient query statements is key to improving performance. Avoid using SELECT * and only query the fields you need:

// Bad practice
const users = await User.find({});

// Good practice
const users = await User.find({}, 'username email createdAt');

For complex queries, use projections, sorting, and pagination appropriately:

// Get only the top 10 records of active users
const activeUsers = await User.find({ isActive: true })
  .select('username lastLogin')
  .sort({ lastLogin: -1 })
  .limit(10);

Batch Operations

Batch operations are much more efficient than single operations. For example, batch inserting data:

// Single insert (inefficient)
for (const item of data) {
  await Model.create(item);
}

// Batch insert (recommended)
await Model.insertMany(data);

When updating multiple documents, use batch updates:

// Update all documents that meet the criteria
await User.updateMany(
  { lastLogin: { $lt: weekAgo } },
  { $set: { isActive: false } }
);

Connection Pool Configuration

Database connections are expensive resources. Properly configuring a connection pool can avoid frequent creation and destruction of connections:

const Koa = require('koa');
const mysql = require('mysql2/promise');

// Create a connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  database: 'test',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

app.context.db = pool;

Caching Strategy

For data that doesn’t change frequently, caching can significantly reduce database queries:

const Redis = require('ioredis');
const redis = new Redis();

app.use(async (ctx, next) => {
  const cacheKey = `user:${ctx.params.id}`;
  let user = await redis.get(cacheKey);
  
  if (!user) {
    user = await User.findById(ctx.params.id);
    await redis.set(cacheKey, JSON.stringify(user), 'EX', 3600); // Cache for 1 hour
  } else {
    user = JSON.parse(user);
  }
  
  ctx.body = user;
});

Lazy Loading vs. Eager Loading

Choose the appropriate loading strategy based on the business scenario. The N+1 query problem is a common performance bottleneck:

// N+1 problem example
const posts = await Post.find();
const results = await Promise.all(posts.map(post => 
  User.findById(post.authorId)
));

// Solution using populate
const posts = await Post.find().populate('authorId');

Query Analysis Tools

Use database-provided analysis tools to identify slow queries:

// Enable slow query logging in MongoDB
db.setProfilingLevel(1, { slowms: 100 });

// MySQL slow query logging
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Database and Table Sharding Strategy

When a single table becomes too large, consider sharding. For example, sharding by time:

// Dynamically select a model based on the year
function getYearModel(year) {
  return mongoose.model(`Data_${year}`, dataSchema);
}

const currentYear = new Date().getFullYear();
const YearModel = getYearModel(currentYear);
const data = await YearModel.find({});

Database Selection Considerations

Different databases suit different scenarios. For read-heavy, write-light scenarios, consider:

• MongoDB replica sets
• MySQL master-slave replication
• Redis as a caching layer

Monitoring and Tuning

Establish a comprehensive monitoring system to promptly identify performance issues:

// Use prom-client to collect metrics
const client = require('prom-client');
const dbQueryDuration = new client.Histogram({
  name: 'db_query_duration_seconds',
  help: 'Duration of database queries in seconds',
  labelNames: ['operation', 'collection'],
  buckets: [0.1, 0.5, 1, 2, 5]
});

// Wrap database operations
async function timedQuery(operation, collection, queryFn) {
  const end = dbQueryDuration.startTimer({ operation, collection });
  try {
    return await queryFn();
  } finally {
    end();
  }
}

Transaction Optimization

Use transactions appropriately to avoid long-held connections:

// MySQL transaction example
const conn = await pool.getConnection();
try {
  await conn.beginTransaction();
  await conn.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
  await conn.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
  await conn.commit();
} catch (err) {
  await conn.rollback();
  throw err;
} finally {
  conn.release();
}

Data Archiving Strategy

Regularly archive historical data to keep the main table lean:

// Archive last month's data every month
async function archiveLastMonthData() {
  const lastMonth = new Date();
  lastMonth.setMonth(lastMonth.getMonth() - 1);
  
  const dataToArchive = await Data.find({
    createdAt: { $lt: lastMonth }
  });
  
  if (dataToArchive.length) {
    await ArchivedData.insertMany(dataToArchive);
    await Data.deleteMany({ _id: { $in: dataToArchive.map(d => d._id) } });
  }
}