Database Optimization Techniques: Performance Tuning Guide
Database performance is critical for application scalability. Here are proven optimization techniques. 1. Indexing Strategy When to Index -- Index frequently queried columns CREATE INDEX idx_user_email ON users(email); -- Index foreign keys CREATE INDEX idx_post_user_id ON posts(user_id); -- Composite indexes for multi-column queries CREATE INDEX idx_user_status_role ON users(status, role); When NOT to Index Columns with low cardinality (few unique values) Frequently updated columns Small tables (< 1000 rows) 2. Query Optimization Avoid SELECT * -- Bad SELECT * FROM users WHERE id = 123; -- Good SELECT id, name, email FROM users WHERE id = 123; Use LIMIT -- Always limit large result sets SELECT * FROM posts ORDER BY created_at DESC LIMIT 20; Avoid N+1 Queries // Bad: N+1 queries users.forEach(user => { const posts = db.query('SELECT * FROM posts WHERE user_id = ?', [user.id]); }); // Good: Single query with JOIN const usersWithPosts = db.query(` SELECT u.*, p.* FROM users u LEFT JOIN posts p ON u.id = p.user_id `); 3. Connection Pooling // Configure connection pool const pool = mysql.createPool({ connectionLimit: 10, host: 'localhost', user: 'user', password: 'password', database: 'mydb', waitForConnections: true, queueLimit: 0 }); 4. Caching Application-Level Caching // Cache frequently accessed data const cache = new Map(); async function getUser(id) { if (cache.has(id)) { return cache.get(id); } const user = await db.query('SELECT * FROM users WHERE id = ?', [id]); cache.set(id, user); return user; } Query Result Caching -- Use query cache (MySQL) SET GLOBAL query_cache_size = 67108864; SET GLOBAL query_cache_type = 1; 5. Database Schema Optimization Normalize Properly -- Avoid over-normalization -- Balance between normalization and performance Use Appropriate Data Types -- Use smallest appropriate type TINYINT instead of INT for small numbers VARCHAR(255) instead of TEXT when possible DATE instead of DATETIME when time not needed 6. Partitioning -- Partition large tables by date CREATE TABLE logs ( id INT, created_at DATE, data TEXT ) PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026) ); 7. Query Analysis EXPLAIN Plan EXPLAIN SELECT * FROM users WHERE email = '[email protected]'; Slow Query Log -- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; 8. Batch Operations // Bad: Multiple individual inserts users.forEach(user => { db.query('INSERT INTO users (name, email) VALUES (?, ?)', [user.name, user.email]); }); // Good: Batch insert const values = users.map(u => [u.name, u.email]); db.query('INSERT INTO users (name, email) VALUES ?', [values]); 9. Database Maintenance Regular Vacuuming (PostgreSQL) VACUUM ANALYZE; Optimize Tables (MySQL) OPTIMIZE TABLE users; 10. Monitoring Monitor query performance Track slow queries Monitor connection pool usage Watch for table locks Monitor disk I/O Best Practices Index strategically Optimize queries Use connection pooling Implement caching Normalize appropriately Use appropriate data types Partition large tables Analyze query performance Batch operations Regular maintenance Conclusion Database optimization requires: ...