Boost Database Performance: 4 Key Optimization Strategies from Architecture to SQL

Introduction

When asked how to optimize a database, many think only of SQL tuning, but true optimization spans multiple layers. This article examines four dimensions: architecture, hardware, database instance parameters, and SQL.

1. Architecture Optimization

1.1 Distributed Cache

Introduce a cache layer (Redis or Memcached) between the application and the database to reduce read traffic. Queries first check the cache; a hit returns data immediately, a miss falls back to the database and populates the cache. Beware of cache penetration, cache breakdown, and cache avalanche.

1.2 Read‑Write Separation

Deploy a master‑slave setup: the master handles writes, multiple slaves serve reads. This linearly increases read capacity for read‑heavy workloads. Data is synchronized via binlog, and high availability requires automatic failover and handling of potential master‑slave inconsistencies.

1.3 Horizontal Sharding

Split large tables across multiple databases using a sharding key. When a single database becomes a bottleneck, sharding reduces per‑node data volume and improves write throughput. Non‑sharded queries may be offloaded to a search engine such as Elasticsearch.

1.4 Architecture Summary

Read‑write separation solves read‑performance problems.

Horizontal sharding addresses massive data volume.

Distributed caching often yields the greatest performance gain in high‑concurrency, large‑data scenarios.

2. Hardware Optimization

Disk I/O dominates database performance. Comparing three storage types clarifies the impact on throughput and IOPS.

Mechanical HDD: ~100‑200 MB/s, 100‑200 IOPS

SATA SSD: ~200‑500 MB/s, 30 000‑50 000 IOPS

PCIe SSD: ~900 MB/s‑3 GB/s, hundreds of thousands IOPS

Upgrading to faster storage can dramatically reduce query latency when budget permits.

3. DB Instance Parameter Optimization

Beyond hardware, instance settings are crucial. Follow the rule “log size adequate, cache large enough, connections sufficient.” Use write‑ahead logging (RedoLog) and a buffer cache to turn random writes into sequential writes, improving durability and throughput.

Configuration examples for Oracle, MySQL (InnoDB), PostgreSQL, and DM (达梦) are illustrated with screenshots.

4. SQL Optimization

4.1 General Tips

Use appropriate indexes; avoid over‑indexing.

Prefer UNION ALL to UNION.

Avoid SELECT * statements.

Index join columns.

Simplify complex statements.

Do not use WHERE 1=1.

Avoid ORDER BY RAND() patterns.

4.2 Execution Plan

Use EXPLAIN to view the execution plan, identify bottlenecks, and check warnings with SHOW WARNINGS.

4.3 Practical Case Study

Original schema (tables a, b, c) and a sample query are shown. Initial execution time was 0.21 s with a full‑table scan.

CREATE TABLE `a` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `seller_id` bigint(20) DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id` varchar(50) DEFAULT NULL,
    `user_name` varchar(100) DEFAULT NULL,
    `sales` bigint(20) DEFAULT NULL,
    `gmt_create` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` varchar(50) DEFAULT NULL,
    `order_id` varchar(100) DEFAULT NULL,
    `state` bigint(20) DEFAULT NULL,
    `gmt_create` varchar(30) DEFAULT NULL,
    PRIMARY KEY (`id`)
);

Optimizations applied:

Align data types (convert user_id to int) and add indexes.

Create composite indexes to eliminate temporary tables and sorting.

Convert gmt_create to DATETIME for proper range queries.

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`);
ALTER TABLE a MODIFY `gmt_create` datetime DEFAULT NULL;

After these changes, execution time dropped to 0.01 s (≈20× faster) and the plan switched from full scans to index scans. Remaining warnings about date format were resolved by fixing the column type.

Conclusion

Database performance can be improved by addressing architecture, hardware, instance parameters, and SQL. No single “silver bullet” exists; the optimal combination depends on workload characteristics and resource constraints.