Optimizing Slow Queries, Sharding and Cache Consistency for Appointment System

Case Background

A provincial medical appointment platform integrates 40 hospitals. After two years the system shows three major problems: extremely slow historical record queries, read pressure that saturates the primary database, and occasional cache inconsistencies.

Material 1 – Slow Historical Query

The appointment table has 1.2 billion rows. A typical query retrieves the latest 20 records for a patient within a one‑year window:

SELECT appointment_id, hospital_id, doctor_id, status, create_time
FROM appointment
WHERE patient_id = ?
  AND create_time BETWEEN ? AND ?
ORDER BY create_time DESC
LIMIT 20;

Only a single‑column index on create_time exists, causing large row scans and high CPU/I‑O during peak hours.

Material 2 – Read Pressure on Primary

~90 % of requests are read‑only (doctor schedules, appointment details, history). All reads and writes go to the primary instance; connections approach the limit during peaks. Adding two replicas introduces a replication‑lag problem: a newly created appointment may not be visible on the replica immediately.

Material 3 – Table Growth

Even after index tuning and archiving, the appointment table continues to grow rapidly. The most frequent queries are:

By patient_id to fetch a patient’s history.

By appointment_id to locate a specific record.

Regulatory reports also require province‑wide daily appointment counts and hospital rankings.

Material 4 – Cache Inconsistency

Doctor schedules are cached in Redis. Updates first modify the database, then update the cache outside the same transaction, leading to occasional stale cache entries.

Material 5 – Three Cache Anomalies

At 08:00 a large batch of schedule keys expire simultaneously, causing a sudden surge of database reads (cache avalanche).

Attackers repeatedly request random, non‑existent doctor_id values, bypassing the cache and hammering the database (cache penetration).

A popular expert’s schedule key expires, and thousands of concurrent requests hit the database to rebuild the same cache entry (cache thundering‑herd).

Problem 1 – Index and SQL Optimization (6 pts)

Evidence: 1.2 billion rows, equality filter on patient_id, range filter on create_time, ordering by create_time, limit 20, only a create_time index.

Use the slow‑query log and EXPLAIN to verify that the query scans many rows and performs a filesort.

Design a composite index idx_appointment_patient_time (patient_id, create_time) so the engine can locate a patient’s rows first, then apply the time range and ordering without a separate sort.

Consider a covering index only if all selected columns fit comfortably; adding all five columns would increase storage, cache pressure, and write cost.

Validate the index’s effect with the execution plan and benchmark the reduction in scanned rows, CPU, and I/O.

Key takeaway: a well‑chosen composite index reduces row scans but incurs extra disk space and write overhead.

Problem 2 – Replication and Read‑Write Separation (6 pts)

Architecture:

Application → Data Access Layer → Primary (writes, critical reads)
                               ↘︎ Replication ↙︎
                         Replica 1 (schedule reads)   Replica 2 (reports)

Replication creates data copies; it does not automatically expose them for reads. Read‑write separation routes writes to the primary and reads to replicas.

Why the immediate‑read‑miss occurs: after a successful write the change has not yet been replayed on the replica (replication lag), so a read routed to the replica returns stale data.

Mitigation strategies:

Route “read‑after‑write” queries to the primary for a short window.

Return the newly created record directly from the write response.

Wait for the replica to catch up to a specific binlog position before reading.

Adding replicas alone does not equal high availability; you also need health‑checks, failover orchestration, leader election, connection routing, split‑brain protection, backup‑restore drills, etc.

Problem 3 – Horizontal Sharding (8 pts)

Justification: 12 billion rows and continued growth make a single table a bottleneck even after indexing.

Sharding key selection:

Most queries are WHERE patient_id = …, so patient_id is a natural shard key.

Assume uniform distribution and no single patient creates a hotspot.

Routing logic: shard_id = hash(patient_id) % N. The same patient’s history stays on one shard, allowing a single‑shard lookup.

Handling appointment_id lookups:

Encode the shard identifier inside the global appointment_id.

Maintain a mapping table (appointment_id → shard_id) or a global index service.

Require the API to pass patient_id together with appointment_id for routing.

Cross‑shard reporting (province‑wide counts, rankings) must aggregate results from all shards or feed the data into a separate data‑warehouse for analytical queries.

Sharding costs (choose at least three):

Cross‑shard joins and aggregations become complex.

Distributed transactions require 2‑PC, TCC, Saga, or eventual consistency.

Global unique IDs are needed to avoid collisions.

Re‑sharding and data migration during scaling.

Potential data skew if patient_id distribution is uneven.

Increased operational complexity (routing middleware, monitoring).

Problem 4 – Cache Consistency (7 pts)

Authority: the relational database holds the definitive schedule and appointment facts; Redis is a rebuildable cache.

Cache‑aside read flow:

Request → Redis
  ├─ Hit → return cached value
  └─ Miss → query DB → write back to Redis → return

Cache‑aside write flow (preferred):

Update DB transaction → if success, delete related Redis key → next read repopulates cache

Why delete‑instead‑of‑update:

Cache may be derived from multiple tables/fields; updating it directly risks inconsistency.

Deletion forces a single, consistent rebuild path.

Remaining risks and mitigations:

Cache‑delete failure → retry, set reasonable TTL.

Short window where stale cache is read → use version stamps, delayed double‑delete, or optimistic locking.

Reliable invalidation via transaction messages, CDC/binlog subscription, or local message tables.

Critical operations such as slot‑count decrement must be performed atomically in the database (e.g.,

UPDATE schedule SET remaining = remaining-1 WHERE schedule_id = ? AND remaining > 0

) and cannot rely solely on cached values.

Problem 5 – Three Cache Anomalies (8 pts)

Cache avalanche – bulk keys expire together. Mitigation: add random jitter to TTL, pre‑warm caches, use multi‑level caches, apply rate‑limiting and circuit‑breaker.

Cache penetration – requests for non‑existent doctor_id. Mitigation: validate parameters, employ a Bloom filter to filter guaranteed‑absent IDs, cache empty results with short TTL, rate‑limit suspicious traffic.

Cache thundering‑herd – a single hot key expires, thousands of concurrent requests hit DB. Mitigation: use a distributed mutex so only one request rebuilds the cache, apply logical expiration with background refresh, pre‑warm hot expert schedules, and limit back‑source traffic.

Overall Solution Flow

Diagnose slow SQL via logs and EXPLAIN.

Add (patient_id, create_time) composite index; evaluate covering index need.

Deploy primary‑replica architecture with read‑write separation; route post‑write reads to primary to avoid replication lag.

Shard the appointment table by patient_id; ensure routing for appointment_id via embedded shard ID or mapping service.

Use cache‑aside pattern for schedule data; delete cache after DB commit, retry on failure, and protect critical slot‑decrement with DB‑level atomic updates.

Guard against cache avalanche, penetration, and thundering‑herd with random TTL, Bloom filters, empty‑value caching, mutexes, and logical expiration.