Database Performance Optimization: 100× Speed Gains Without Changing SQL

Unified Understanding

Performance problems rarely stem from a single slow SQL; they are usually the result of request models, cache strategy, connection‑pool sizing, transaction boundaries, read/write paths, index design, storage layout, replication lag, traffic governance, and architectural evolution. High‑quality optimization aims to keep most requests out of the database and ensure the remaining ones follow the correct data path.

Unified Scenario – E‑commerce Order Center

Business Background

2 application servers

MySQL single master

Redis single instance

~500k daily orders

Peak read QPS 3000, write QPS 500

During a promotion the following chain of problems appears:

Product detail page repeatedly queries order rights, causing duplicate reads.

Order list uses deep pagination LIMIT 100000, 20, crushing the index.

Payment callbacks share the same connection pool, slowing reads.

Backend analytics continuously occupy OLTP resources.

Some interfaces call external services inside a transaction, holding the DB connection far longer than the SQL execution.

Optimization Goals

Only mandatory writes and strong‑consistent reads hit the master.

High‑frequency repeat reads are served by local cache or Redis.

Query paths use correct indexes, pagination, and replica routing.

Database connections become a scarce, well‑controlled resource.

Complex analytics are detached from the transactional path.

Each architectural upgrade yields deterministic benefits without adding uncontrolled complexity.

Correct Optimization Order

The proper sequence is:

Monitoring & Diagnosis

Eliminate useless requests

Optimize access paths

Optimize storage structures

Scale read/write capacity

Govern distributed complexity

Four Diagnostic Questions

Is the latency in DB execution or in connection acquisition, queuing, serialization, or network?

Is the bottleneck CPU, disk IOPS, lock contention, connection count, or replication lag?

Is the slowdown caused by a few heavy SQLs or a flood of light SQLs?

Is the pressure from genuine business growth or from cache misses, task retries, or background batch jobs?

Recommended Metric Panel

Throughput : QPS, TPS, active sessions – check if near capacity.

Latency : Avg, P95, P99, timeout count – detect long tail.

Connections : Active, waiting, acquisition time – identify app‑side blockage.

Locks : Row‑wait, deadlock count, hold time – assess transaction conflict.

Cache : Hit rate, penetration rate, hot keys – validate cache effectiveness.

Replica : Master‑slave lag, replication errors, replay backlog – verify read‑write separation viability.

Storage : Buffer pool hit rate, redo write rate, disk IOPS – match hardware to parameters.

Diagnostic Flow

Interface alarm / P99 jitter.

Inspect application metrics: thread‑pool, connection‑pool, timeouts.

Inspect DB metrics: CPU, IOPS, lock wait, slow‑log.

Identify hot scenarios (list, detail, search, callback, task).

Confirm if cache / routing / pagination / replica can solve them.

Only then touch indexes or table structures.

Key idea: don’t rewrite SQL first; first locate the layer where the problem originates.

First Principle – Reduce Requests Entering the DB

The most expensive part of a request is the total cost of connections, locks, buffer‑pool usage, redo logging, and replication, not the CPU time of a single SQL. Shrinking the total request volume hitting the database is the top priority.

Read‑Traffic Layering

For an order system, reads can be classified:

Ultra‑high‑frequency hot data (order status, payment result, entitlement summary).

Short‑lived repeat reads (refresh order detail, return to list page).

Medium‑frequency ordinary reads (user order pagination).

Low‑frequency complex queries (admin analytics, reports, export).

Reasonable architecture:

User request → Application service → Local cache (Caffeine) → Redis → Read replica / master → Binlog / event stream → ES / data‑warehouse / read model

Production‑Grade Two‑Level Cache Implementation

@Service
public class OrderQueryService {
    private final Cache<String, OrderSnapshot> localCache;
    private final StringRedisTemplate redisTemplate;
    private final OrderRepository orderRepository;
    private final DistributedLockClient lockClient;
    private final ObjectMapper objectMapper;

    public OrderQueryService(Cache<String, OrderSnapshot> localCache,
                             StringRedisTemplate redisTemplate,
                             OrderRepository orderRepository,
                             DistributedLockClient lockClient,
                             ObjectMapper objectMapper) {
        this.localCache = localCache;
        this.redisTemplate = redisTemplate;
        this.orderRepository = orderRepository;
        this.lockClient = lockClient;
        this.objectMapper = objectMapper;
    }

    public OrderSnapshot getOrder(String orderId) {
        String cacheKey = "order:detail:" + orderId;
        OrderSnapshot local = localCache.getIfPresent(cacheKey);
        if (local != null) {
            return local;
        }
        String cachedJson = redisTemplate.opsForValue().get(cacheKey);
        if (cachedJson != null) {
            OrderSnapshot snapshot = deserialize(cachedJson);
            localCache.put(cacheKey, snapshot);
            return snapshot;
        }
        String lockKey = "lock:rebuild:" + orderId;
        boolean locked = lockClient.tryLock(lockKey, 2_000, 5_000);
        if (!locked) {
            throw new ServiceBusyException("order cache rebuilding");
        }
        try {
            String secondRead = redisTemplate.opsForValue().get(cacheKey);
            if (secondRead != null) {
                OrderSnapshot snapshot = deserialize(secondRead);
                localCache.put(cacheKey, snapshot);
                return snapshot;
            }
            OrderSnapshot snapshot = orderRepository.findSnapshotByOrderId(orderId)
                    .orElseThrow(() -> new NotFoundException("order not found"));
            long ttlSeconds = 300 + ThreadLocalRandom.current().nextLong(60);
            redisTemplate.opsForValue().set(cacheKey, serialize(snapshot), ttlSeconds, TimeUnit.SECONDS);
            localCache.put(cacheKey, snapshot);
            return snapshot;
        } finally {
            lockClient.unlock(lockKey);
        }
    }

    private OrderSnapshot deserialize(String json) {
        try {
            return objectMapper.readValue(json, OrderSnapshot.class);
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("invalid cache payload", e);
        }
    }

    private String serialize(OrderSnapshot snapshot) {
        try {
            return objectMapper.writeValueAsString(snapshot);
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("serialize cache failed", e);
        }
    }
}

This implementation provides a two‑level cache barrier, lock‑protected hot‑key rebuild, random TTL jitter to avoid cache‑stampede, and fast‑fail when the lock cannot be obtained.

Cache Consistency Design

Three typical consistency models:

Strong consistency reads – e.g., payment success page must see the latest value.

Eventual consistency reads – tolerates tens to hundreds of ms delay, suitable for order list.

Near‑real‑time read model – seconds‑level delay acceptable for reporting.

Common practice in order systems:

Delete cache after transaction commit (instead of delete‑then‑write).

Asynchronously rebuild cache via message events or Binlog subscription.

Force strong‑read on write‑after‑read interfaces.

SQL Optimization Still Matters

Index Design Around Business Access Paths

For an order‑list query the typical filters are user_id, status, and descending create_time. A covering composite index should reflect that order:

CREATE INDEX idx_user_status_ctime ON t_order (user_id, status, create_time DESC);

The index avoids extra sorting and back‑table lookups.

Common Index Failure Reasons

Implicit conversion due to mismatched charset or column type.

Function calls on indexed columns.

Filter order not matching the left‑most prefix of a composite index.

Deep pagination forces scanning of massive irrelevant rows.

Mixing OLTP and OLAP workloads in a single table.

Why Deep Pagination Is Inherently Slow

Problematic query:

SELECT id, order_no, amount, status, create_time
FROM t_order
WHERE user_id = ?
ORDER BY create_time DESC
LIMIT 100000, 20;

The DB must still traverse the first 100 000 rows, incurring index walk, back‑table fetch, and sort costs.

Cursor‑based pagination is preferable:

SELECT id, order_no, amount, status, create_time
FROM t_order
WHERE user_id = ?
  AND create_time < ?
ORDER BY create_time DESC
LIMIT 20;

If arbitrary page jumps are required, the problem should be offloaded to a search‑oriented read model.

Separate Read Models Overload the Transaction Table

The transaction table should serve primary‑key lookups, user‑dimension list queries, and small‑range status filters. It should NOT handle complex fuzzy searches, large‑range aggregations, bulk exports, or heavy aggregations. Those workloads belong to heterogeneous read models built via Binlog or event bus into Elasticsearch, ClickHouse, or a data‑warehouse.

Connection Pool & Transaction Boundaries

Production incidents often show few slow SQLs but exhausted connection pools, causing massive request timeouts. The root cause is usually the application holding connections too long.

Four Crucial Hikari Parameters

spring:
  datasource:
    hikari:
      maximum-pool-size: 24
      minimum-idle: 8
      connection-timeout: 2000
      validation-timeout: 1000
      idle-timeout: 300000
      max-lifetime: 1500000
      leak-detection-threshold: 5000

Explanation: maximum-pool-size caps concurrent DB connections. connection-timeout decides fast‑fail vs long wait when no connection is available. max-lifetime prevents silent server‑side connection reclamation. leak-detection-threshold surfaces connections that are held excessively long.

Avoid Long‑Running External Calls Inside Transactions

Typical anti‑pattern:

@Transactional
public void createOrder(CreateOrderCommand command) {
    Order order = orderFactory.create(command);
    orderRepository.save(order);
    couponClient.freeze(command.getCouponId());
    marketingClient.recordNewOrder(command.getUserId());
    messageProducer.send("order-created", order.getOrderNo());
}

Better approach – keep the transaction limited to local consistency and use an Outbox table for asynchronous propagation:

@Transactional
public String createOrder(CreateOrderCommand command) {
    Order order = orderFactory.create(command);
    orderRepository.save(order);
    OrderOutboxEvent event = OrderOutboxEvent.created(order.getOrderNo(), order.getUserId());
    outboxRepository.save(event);
    return order.getOrderNo();
}

Dispatcher:

@Component
public class OrderOutboxDispatcher {
    @Scheduled(fixedDelay = 500)
    public void dispatch() {
        List<OrderOutboxEvent> events = outboxRepository.pullPending(200);
        for (OrderOutboxEvent event : events) {
            try {
                messagePublisher.publish("order-created", event.getPayload());
                outboxRepository.markSent(event.getId());
            } catch (Exception ex) {
                outboxRepository.markRetry(event.getId(), ex.getMessage());
            }
        }
    }
}

Benefits: shortened transaction time, controlled DB connection usage, reliable retry, and isolation of external failures.

Read‑Write Separation – More Than Adding a Slave

When It Helps

Read traffic overwhelms the master.

Read load far exceeds write load.

Most reads can tolerate a short delay.

If the write path itself is the bottleneck, read‑write separation offers limited relief.

Explicit Read Routing

Three read categories in the order system:

Write‑after‑read – must hit master.

Normal detail reads – can go to replica or cache.

Backend list / report reads – prefer read‑only replica or dedicated read model.

Implementation via a thread‑local routing context and a custom annotation:

public class DbRouteContext {
    private static final ThreadLocal<Boolean> FORCE_MASTER = ThreadLocal.withInitial(() -> Boolean.FALSE);
    public static void forceMaster() { FORCE_MASTER.set(Boolean.TRUE); }
    public static boolean isForceMaster() { return FORCE_MASTER.get(); }
    public static void clear() { FORCE_MASTER.remove(); }
}

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ForceMasterRead {}

@Aspect
@Component
public class ForceMasterReadAspect {
    @Around("@annotation(ForceMasterRead)")
    public Object around(ProceedingJoinPoint jp) throws Throwable {
        try {
            DbRouteContext.forceMaster();
            return jp.proceed();
        } finally {
            DbRouteContext.clear();
        }
    }
}

Compensating for Master‑Slave Lag

Force master for write‑after‑read interfaces.

Strong‑consistent reads first check cache; cache is updated or invalidated by the master path.

Allow eventual consistency for non‑critical queries.

Set replica‑lag thresholds; automatically divert traffic when exceeded.

Sharding – When and How to Choose a Sharding Key

Signals That Merit Sharding

Master write QPS constantly near the upper bound.

Table size and index size noticeably degrade query performance and DDL windows.

Backup / restore / DDL time becomes unacceptable.

Clear shard dimension exists, e.g., user_id or tenant_id.

Problems that should NOT be solved by sharding include cache miss, deep pagination, backend scans, or oversized transactions.

Sharding‑Key Considerations

All primary query paths must carry the shard key.

Future queries may need to filter by order number, time range, or merchant – assess impact.

Cross‑shard aggregation, sorting, and joins must be planned.

Example sharding algorithm using user_id as the key (4 DBs, 16 tables):

public class UserIdShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    private static final int DB_COUNT = 4;
    private static final int TABLE_COUNT = 16;
    @Override
    public String doSharding(Collection<String> targets, PreciseShardingValue<Long> value) {
        long userId = value.getValue();
        long dbIndex = userId % DB_COUNT;
        long tableIndex = (userId / DB_COUNT) % TABLE_COUNT;
        return "ds" + dbIndex + ".t_order_" + tableIndex;
    }
}

Routing After Sharding

Queries by order_no need either an index table mapping order numbers to user_id or embed routing information in the order number itself. The index‑table approach is more maintainable.

InnoDB Parameter Tuning – Amplifying Gains

Key InnoDB Settings

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
transaction_isolation = READ-COMMITTED

Impact summary: innodb_buffer_pool_size determines how much hot data and indexes stay in memory. innodb_log_file_size influences write throughput and checkpoint behavior. innodb_flush_log_at_trx_commit balances durability vs performance. transaction_isolation directly affects lock contention and consistency semantics.

Preconditions for Effective Tuning

Only tune after the access path, caching, and transaction boundaries are already optimal. Otherwise larger buffers merely postpone failure.

Process Knowledge – Turning Optimization into a Reusable Pipeline

Requirement Review

Identify the main query path.

Spot obvious hot data.

Decide cacheability of reads.

Clarify pagination, sorting, fuzzy search, export needs.

Assess transaction scope and cross‑system involvement.

Estimate write amplification on the master.

Pre‑Release Review

Run EXPLAIN on core SQL.

Verify indexes match real access patterns.

Confirm cache warm‑up or hot‑key protection.

Check connection‑pool, thread‑pool, timeout, retry configs.

Define write‑after‑read consistency strategy.

Ensure slow‑log, connection, and replica‑lag monitoring are in place.

Incident Handling

Immediately apply rate‑limiting to protect the pool and master.

Identify hotspot interfaces and abnormal traffic sources.

Classify the issue: connection, lock, I/O, or query.

Activate cache, degradation, read‑only replica, or background task shedding.

Only then adjust indexes, parameters, or perform DDL changes.

Post‑Mortem & Evolution

Beyond "SQL fixed", answer:

Why wasn’t the traffic cached or split earlier?

Why did the connection pool amplify the impact?

Why didn’t monitoring catch replica lag or hot‑key early?

Do we need read‑model separation, Outbox, replica governance, or sharding plans?

Evolution Path From Single‑Node to Distributed Governance

Stage 1 – Single‑Node : focus on correct indexes, cache, tight transaction boundaries, and monitoring.

Stage 2 – Cache Layer + Read‑Write Separation : add local cache, Redis, master‑slave replication, and write‑after‑read contracts.

Stage 3 – Heterogeneous Read Models : offload complex search to Elasticsearch, statistics to ClickHouse, sync via Binlog.

Stage 4 – Sharding : when write capacity hits limits, introduce sharding key, routing, index tables, and migration plans.

Stage 5 – Distributed Data Governance : multi‑region, multi‑tenant, multi‑engine coordination; focus on data lineage, traffic scheduling, storage responsibilities, consistency levels, and disaster‑recovery drills.

Production Checklist – Is the System Ready?

Access‑Path Checks

Core queries have proper indexes.

No deep pagination, full‑table fuzzy search, or massive sorting.

High‑frequency reads are intercepted by cache.

Write‑after‑read interfaces define strong‑consistency strategy.

Concurrency Governance

Connection pool limits and short timeouts are configured.

Thread pool size matches DB capacity.

Interface‑level rate‑limiting, circuit‑breaker, and degradation are in place.

Hot‑key protection and cache‑stampede safeguards exist.

Data Consistency Checks

Cache update policy (delete‑then‑write vs write‑then‑delete) includes fallback.

Business visibility of master‑slave lag.

Message delivery via Outbox or reliable event mechanisms.

Routing strategy for non‑shard‑key queries after sharding.

Observability Checks

Slow‑query log and Top‑SQL dashboard.

Connection‑pool active, waiting, and acquisition‑time metrics.

Master‑slave lag and replication error alerts.

Cache hit rate, penetration rate, and hot‑key monitoring.

Conclusion – Let the Database Do What It’s Good At

The database excels at managing transactional truth data, providing durable storage, and delivering stable OLTP under well‑defined access models. It is not suited for serving all repeat reads, handling complex analytics, or absorbing long transactions, deep pagination, cache misses, and unbounded concurrency.

Mature database performance optimization is a disciplined system of:

Keeping cacheable data out of the DB.

Ensuring only short‑lived, local‑consistency work stays inside a transaction.

Diverting read‑heavy traffic away from the master.

Isolating workloads so they don’t drag each other down.

Layering data storage to avoid piling heterogeneous queries on a single table.

When a team graduates from "optimizing a single SQL" to "governing an entire data‑access chain", database performance becomes a controllable, scalable asset rather than a hidden bottleneck.