Boost Database Performance: 4 Key Optimization Dimensions Explained

Architecture Optimization

In high‑concurrency scenarios, optimizing the architecture yields the most noticeable gains. Common techniques include distributed caching, read/write separation, and horizontal sharding, each suited to different workloads.

Distributed Cache

Introduce a cache layer (e.g., Redis or Memcached) between the application and the database. Queries first check the cache; if a hit occurs, the result returns immediately, otherwise the database is queried and the result is cached, dramatically reducing database load.

When using a cache, be aware of three pitfalls:

Cache penetration : Queries for nonexistent keys repeatedly hit the database. Cache breakdown : A hot key expires and a flood of requests bypass the cache, overwhelming the database. Cache avalanche : Many keys expire simultaneously, causing a surge of database traffic.

Read/Write Separation

Deploy a primary‑replica setup where the primary handles writes and replicas serve reads. This is effective when the workload is read‑heavy, allowing linear scaling of read capacity by adding replicas. Ensure automatic failover and monitor replica lag to avoid consistency issues.

Horizontal Sharding

When a single database reaches capacity limits, split data across multiple shards using a sharding key. This reduces per‑shard size and improves write throughput. Choose the sharding key carefully; sometimes auxiliary query services like Elasticsearch are needed for non‑sharded queries.

Hardware Optimization

Database performance is tightly bound to storage speed. Upgrading from mechanical HDDs to PCIe SSDs can increase throughput from ~100 MB/s to up to 3 GB/s and IOPS from a few hundred to several hundred thousand. When budget permits, replacing disks is a straightforward way to cut query latency.

DB Instance Parameter Optimization

Instance‑level tuning complements architectural and hardware improvements. Follow three guiding principles: keep the redo log large enough, allocate sufficient buffer cache, and ensure enough connections.

Key tuning actions per database:

Oracle : Set SGA_TARGET / MEMORY_TARGET to 70‑80% of RAM, configure DB_CACHE_SIZE similarly, adjust SHARED_POOL_SIZE to 4‑16 GB, and size PROCESSES, SESSIONS, OPEN_CURSORS to ~120% of expected connections.

MySQL (InnoDB) : Allocate INNODB_BUFFER_POOL_SIZE to 50‑80% of RAM, set innodb_log_buffer_size to 16‑32 MB, tune max_connections for peak load, and choose innodb_flush_log_at_trx_commit = 2 for a performance‑safety trade‑off.

PostgreSQL : Set SHARED_BUFFERS to 10‑25% of RAM, CACHE_BUFFER_SIZE to 50‑60%, configure wal_buffer to 8‑64 MB, and increase max_connections to ~120% of expected connections. Raise maintenance_work_mem (e.g., 512 MB) and work_mem (8‑16 MB) for better maintenance and query performance.

DaMeng : Allocate MEMORY_TARGET / MEMORY_POOL to 90% of RAM, set BUFFER to 60%, and size MAX_BUFFER to 70% of RAM. Adjust max_sessions similarly to other RDBMS.

SQL Optimization

SQL tuning focuses on indexing, query rewriting, and avoiding costly constructs. Key practices include:

Use appropriate indexes: high‑selectivity columns, frequently used in WHERE clauses, and join keys.

Prefer UNION ALL over UNION to skip duplicate elimination.

Avoid SELECT * to enable covering indexes.

Index join columns.

Simplify complex queries into multiple short statements when possible.

Do not write WHERE 1=1 or ORDER BY RAND().

Execution Plan

Analyzing the execution plan (via EXPLAIN) reveals bottlenecks. Important columns include type (join algorithm), possible_keys, key (chosen index), rows (estimated rows examined), and extra (warnings such as Using filesort or Using temporary).

Practical Optimization Walkthrough

Original schema consists of three tables a, b, and c. The target query joins them and filters by a time window around the current time.

SELECT a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
FROM a, b, c
WHERE a.seller_name = b.seller_name
  AND b.user_id = c.user_id
  AND c.user_id = 17
  AND a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL -600 MINUTE)
                      AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
ORDER BY a.gmt_create;

Initial execution time was 0.21 s with a full‑table‑scan plan, temporary tables, and sorting.

Optimization steps:

Align data types: change b.user_id and c.user_id from VARCHAR(50) to INT to avoid implicit conversion.

Add indexes on b.user_id and c.user_id.

Add an index on a.seller_name (used in the join).

Create a composite index covering gmt_create, seller_name, and seller_id to eliminate temporary tables and sorting.

Corresponding DDL changes:

ALTER TABLE b MODIFY `user_id` INT(10) DEFAULT NULL;
ALTER TABLE c MODIFY `user_id` INT(10) DEFAULT NULL;
ALTER TABLE c ADD INDEX `idx_user_id`(`user_id`);
ALTER TABLE b ADD INDEX `idx_user_id_sell_name`(`user_id`,`seller_name`);
ALTER TABLE a ADD INDEX `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

After these changes, execution time dropped to 0.01 s (≈20× faster) and the plan switched to an index‑based scan. A warning indicated that the gmt_create column was still a string, causing an implicit conversion.

Fix the column type:

ALTER TABLE a MODIFY "gmt_create" DATETIME DEFAULT NULL;

Re‑checking the plan confirmed the index usage and no further warnings. The final performance remained at 0.01 s.

SQL Optimization Summary

Inspect the execution plan with EXPLAIN.

If warnings appear, run SHOW WARNINGS to identify issues.

Review table structures and existing indexes.

Based on the plan, decide whether to add/modify indexes, rewrite the query, or change column types.

Apply the necessary DDL changes and query rewrites.

Validate the new execution time and plan.

If results are unsatisfactory, repeat from step 4.

Conclusion

Database performance can be improved through four complementary dimensions: architecture (caching, read/write separation, sharding), hardware (fast SSDs), instance parameter tuning, and SQL query optimization. No single “silver bullet” exists; the optimal strategy depends on the specific workload and system characteristics.