MySQL High-Concurrency Optimization: From Diagnosis to Sharding with Seckill Case Study
A layered MySQL optimization guide for high-concurrency scenarios covering bottleneck diagnosis, SQL/index tuning, transaction/lock optimization, a three-layer seckill inventory implementation (Redis Lua, MQ async, sharded buckets), caching patterns, read-write separation, connection pooling, InnoDB tuning, and sharding trade-offs, with real production metrics showing P99 latency dropping from 1.2s to 85ms.
Optimization Philosophy: Make MySQL Do Less Work
The core principle: high-concurrency optimization isn't about making MySQL run faster, but making it do less work. The recommended optimization sequence is: locate bottlenecks → optimize SQL/indexes → converge transactions → cache/MQ peak-shaving → connection pool & parameter tuning → read-write separation → finally sharding. Sharding introduces architectural complexity that often outweighs the original slow-query problem.
1. Diagnose Before Treating: Four Bottleneck Types
High concurrency problems are never single-cause. The author asks three diagnostic questions first: which layer is the bottleneck (CPU, IO, locks, connections)? What are the top 5 slow queries? What is current QPS/TPS vs machine capacity? Four distinct bottleneck types require completely different first actions:
Connection pool exhaustion : Typical symptoms: "Get connection timeout", max_connections saturated. Root cause: Thread context-switch overhead spikes. First action: Shrink pool, add fast-fail.
Slow SQL dragging CPU : Typical symptoms: CPU >80% sustained, slow query log full. Root cause: Missing indexes, table scans, filesort. First action: EXPLAIN + add indexes.
Write flush can't keep up : Typical symptoms: IO util >70%, commits slow. Root cause: redo/binlog sync, lock contention. First action: Tune flush policy + batch + shard.
Single table hits ceiling : Typical symptoms: RT climbs from tens to hundreds of ms. Root cause: B+ tree depth increases, buffer pool hit rate drops. First action: Archive + read-write separation + sharding.
A real case: order service RT spiked from 50ms to 2000ms, Threads_running >100 but CPU only 30%. The culprit was connection pool oversizing (200 → 50 fixed it). Connection pool too large actually slows things down due to context switching.
2. SQL & Indexes: Highest ROI Layer
2.1 Why B+ Tree Makes Indexes Fast
InnoDB uses B+ trees: non-leaf nodes store only index entries (not data), so more keys fit per page → tree height stays 3-4 levels for tens of millions of rows → 3-4 disk I/Os per lookup. Leaf nodes have sequential pointers, ideal for range scans.
2.2 EXPLAIN: Watch Four Fields
type hierarchy: system > const > eq_ref > ref > range > index > ALL
excellent ──────────────────────────────→ full table scantype : Danger signal ALL → Full table scan, red line.
key : Danger signal NULL → Index not used.
rows : Danger signal millions → Estimated scan volume too large.
Extra : Danger signal Using filesort / Using temporary → Sort/disk temp table, CPU killer; Using index is good.
2.3 Covering Index: Eliminate Random I/O
Put all queried columns into a composite index to turn "index → PK → lookup" into a single step. Example:
-- Slow: only user_id indexed, amount/status need lookup
CREATE INDEX idx_user ON orders(user_id);
-- Fast: covering index, Extra shows Using index → zero lookups
CREATE INDEX idx_user_status_time_cover ON orders(user_id, status, create_time, amount);Covering index can boost single-SQL performance 2-5x, highest cost-benefit optimization.
2.4 Composite Index & Leftmost Prefix
For idx(a,b,c): (a) ✅, (a,b) ✅, (a,b,c) ✅, (a,c) ⚠️ only uses a, (b,c) ❌ doesn't use index. Rule: equality conditions first, range conditions last, sort columns immediately after. High-cardinality WHERE columns go leftmost.
2.5 Index Failure Checklist
WHERE DATE(create_time) = '2026-10-22' : ❌ Function on column; rewrite as range.
WHERE name LIKE '%张%' : ❌ Leading wildcard.
WHERE phone = 13800138000 (phone is varchar) : ❌ Implicit type conversion.
WHERE status != 1 : ⚠️ Negation/range usually full scan.
WHERE a = 1 OR b = 2 : ⚠️ Both sides need indexes.
ORDER BY unindexed column : ❌ Triggers Using filesort.
Implicit conversion (varchar compared to number) is a silent killer — index becomes useless, slow queries surge without obvious cause.
2.6 Deep Pagination: LIMIT 1000000,20
MySQL scans and discards the first 1,000,020 rows. Two solutions:
Delayed join (when UI requires jump-to-page): subquery fetches 20 PKs via covering index, then joins back for 20 rows. Requires (create_time, id) composite index; subquery must not use SELECT *.
Cursor pagination (best for infinite scroll): WHERE id > #{lastId} ORDER BY id LIMIT 20 with (user_id, id) index. Cost constant regardless of page depth. Trade-off: cannot jump to arbitrary page; product changed "jump to page 87" to "next page", dropping RT from 3s to 5ms.
2.7 Large Table Index Creation Pitfalls
ALTER TABLE t_order ADD INDEX idx_user_cursor (user_id, id), ALGORITHM=INPLACE, LOCK=NONE;Online DDL (5.6+) allows concurrent DML but still needs brief exclusive MDL lock at start/end.
If a long transaction holds MDL, DDL blocks and all subsequent queries pile up → cascade failure. Check for long transactions before adding indexes.
For huge tables, use gh-ost (binlog-based, low master impact) or pt-online-schema-change (trigger-based, write amplification).
3. Transactions & Locks: Silent Killers
3.1 Transactions Must Be Short
Mixing RPC calls inside @Transactional is the #1 connection pool killer. Example:
// ❌ Bad: RPC inside transaction holds connection 200ms doing nothing
@Transactional
public OrderVO createOrder(Long userId) {
Order order = buildOrder(userId);
orderMapper.insert(order);
UserInfo user = userClient.getUser(userId); // remote call 200ms
return toVO(order, user);
}
// ✅ Good: fetch external data first, transaction only does DB work
public OrderVO createOrder(Long userId) {
UserInfo user = userClient.getUser(userId);
return txTemplate.execute(status -> {
Order order = buildOrder(userId);
orderMapper.insert(order);
return toVO(order, user);
});
}Rule: no RPC, no MQ, no unrelated queries, no large loops inside transactions. Every extra 100ms holding a connection eats pool capacity under load.
3.2 Isolation Level & Lock Wait Timeout
Use READ COMMITTED + binlog_format=ROW (standard for internet transactions). REPEATABLE READ's gap locks expand lock range when update condition misses index, dramatically increasing deadlocks.
innodb_lock_wait_timeout must be small: 1-3s (conservative 5-10s). Default 50s lets a hot row stall the entire Tomcat thread pool.
3.3 Deadlock Resolution: Order, Not Sleep
Fixed lock order: batch-update multiple SKUs by ascending skuId — cross-locking is the #1 deadlock source.
Shorten transactions → less lock hold time → lower collision probability.
Drop to RC isolation → gap locks largely disappear.
Retry only on 1213 (deadlock) / 1205 (lock timeout) with limited exponential backoff. Inventory shortage, invalid params → fail fast; infinite retries amplify traffic.
Using sleep and infinite retries to mask deadlocks buries the bomb deeper. Regularly review LATEST DETECTED DEADLOCK section in SHOW ENGINE INNODB STATUS.
3.4 Prevent Oversell: One SQL Replaces Three Code Blocks
"Check-then-deduct" always oversells — two concurrent requests both see stock=1, both pass check, both deduct.
// ❌ Classic TOCTOU race
Integer stock = stockMapper.selectStock(skuId);
if (stock >= qty) {
stockMapper.updateStock(skuId, stock - qty); // oversell guaranteed under concurrency
}Correct: push judgment into the same SQL, let InnoDB row lock guarantee atomicity:
<update id="deduct">
UPDATE sku_stock
SET stock = stock - #{num}
WHERE sku_id = #{skuId}
AND stock >= #{num}
</update>@Transactional(rollbackFor = Exception.class, timeout = 2)
public void deductStock(Long skuId, int num) {
int rows = stockMapper.deduct(skuId, num);
if (rows == 0) {
throw new BizException("Out of stock"); // affected rows = 0 means deduct failed
}
}The WHERE stock >= num is a natural CAS — no extra version column needed for inventory (version suits "read-modify-write whole row" scenarios). Use affected rows to determine success; transaction timeout 2s for fast failure. sku_id must have index, otherwise condition locks many rows.
Pessimistic lock (SELECT ... FOR UPDATE) : High-concurrency behavior: Serial queue, two round-trips + long lock hold, throughput drops. Best for: Low concurrency, strong consistency.
Optimistic lock (version CAS) : High-concurrency behavior: High throughput when conflicts low; conflict storms under high contention. Best for: Read-heavy, low conflict.
Conditional atomic UPDATE ... WHERE stock >= n : High-concurrency behavior: Judgment + deduct inside row lock, no retry storms. Best for: Inventory, balance deduction.
Why not FOR UPDATE? High contention serializes connections/threads, throughput falls. Why not version optimistic lock? Massive CAS failures → retry storm worse than original problem.
4. Write Hotspot Battle: Seckill Inventory Implementation
4.1 Core Problem: Row Lock "Single-Plank Bridge"
UPDATE goods SET stock = stock - 1 WHERE id = 123 AND stock > 0;At 8000 QPS all requests contend for the same row's X lock → serial queue. Even 1ms lock hold builds massive wait queue, CPU spikes, connection pool saturates, cascade failure. Adding machines doesn't help — hotspot remains on same row.
4.2 Funnel Model: MySQL Only Handles Real Orders
Core idea: MySQL doesn't need to withstand 10k QPS; it only needs to handle "real completed orders". 99% of requests should be rejected at gateway/cache layers, not queued for DB row locks. Three-layer defense:
Redis Lua atomic pre-deduct → filters 99% invalid traffic
MQ async order placement → flattens traffic spikes
MySQL conditional update as final safety net
4.3 Layer 1: Redis Lua Atomic Pre-Deduct
GET then DECR are two commands — concurrency interleaves. Lua makes "check + deduct + deduplication" atomic in one RTT:
-- KEYS[1] = stock:{skuId}:{bucketId} inventory
-- KEYS[2] = dedup:{skuId}:{userId} idempotency/purchase limit
-- ARGV[1] = qty ARGV[2] = TTL seconds
if redis.call('EXISTS', KEYS[2]) == 1 then
return -2 -- duplicate / already purchased
end
local stock = tonumber(redis.call('GET', KEYS[1]) or '-1')
if stock < 0 then return -3 end -- not warmed up
if stock < tonumber(ARGV[1]) then return 0 end -- sold out
redis.call('DECRBY', KEYS[1], ARGV[1])
redis.call('SET', KEYS[2], ARGV[1], 'EX', ARGV[2])
return 1 -- pre-deduct successJava side maps return codes to precise frontend messages. Critical pitfalls:
Redis Cluster: multiple keys must land in same slot → use hash tags: stock:{sku123}:0, dedup:{sku123}:user1
Lua guarantees atomic execution but not rollback on error — keep scripts short, no big loops; rely on compensation reconciliation.
Redis is a shield, not the ledger: AOF everysec may lose seconds, master-slave async failover may lose data. Final correctness must come from DB conditional update + unique index (explained later).
4.4 Layer 2: MQ Async Order + Idempotent Persistence
Pre-deduct successes go to MQ; DB consumes at its own pace. Consumer code:
// Outer layer NO transaction; duplicate messages caught outside transaction
public void onOrderMessage(OrderMsg msg) {
try {
orderTxService.createAndDeduct(msg);
} catch (DuplicateKeyException e) {
log.info("Duplicate message, idempotent skip: {}", msg.getOrderNo());
}
}
@Transactional(rollbackFor = Exception.class)
public void createAndDeduct(OrderMsg msg) {
// 1. Write order first: uk_order_no unique index guarantees idempotency
orderMapper.insert(msg.toOrder());
// 2. Then deduct inventory: conditional update, failure throws → full rollback
if (stockMapper.deduct(msg.getSkuId(), msg.getBucketId(), msg.getQty()) != 1) {
throw new BizException(STOCK_NOT_ENOUGH);
}
detailMapper.batchInsert(msg.getItems());
}Two blood-lesson pitfalls:
Must write idempotent record (order) FIRST, then deduct inventory. Reverse order causes duplicate deductions on MQ retry.
Catching DuplicateKeyException INSIDE @Transactional is a trap: Spring marks transaction rollback-only, commit throws UnexpectedRollbackException. Correct: catch outside transaction or use REQUIRES_NEW isolation.
Failure handling: on DB persistence failure, compensate by rolling back Redis pre-deduct (with idempotency key). Batch consumption via @KafkaListener with spring.kafka.listener.type=batch reduces DB pressure further.
4.5 Layer 3: Inventory Bucketing — Split One Hot Row into N
If Redis pre-deduct still leaves significant write volume, shard the same product's inventory into multiple buckets; requests randomly route, lock contention drops to 1/N:
CREATE TABLE stock_segment (
sku_id BIGINT NOT NULL,
seg_no TINYINT NOT NULL, -- split 1 row into 10-16 rows
stock INT NOT NULL,
PRIMARY KEY (sku_id, seg_no)
) ENGINE=InnoDB;
-- Deduct: randomly pick a non-empty bucket
UPDATE stock_segment
SET stock = stock - #{num}
WHERE sku_id = #{skuId}
AND seg_no = #{segNo}
AND stock >= #{num};
-- Returns 0 rows → try another segment (limited retries), all fail = sold out
-- Total inventory = SUM(stock); sold-out judgment uses Redis, not this aggregationprivate static final int SEG_COUNT = 16;
public boolean deductSeg(Long skuId, int num) {
int seg = ThreadLocalRandom.current().nextInt(SEG_COUNT);
return stockSegMapper.deductSeg(skuId, seg, num) > 0;
}Row lock contention drops from "1 row 8000 concurrent" to "16 rows ~500 each" — an order of magnitude reduction. Implementation gotchas:
Redis bucketing AND MySQL bucketing must match. Only Redis bucketing leaves DB row as hotspot — wasted effort.
Order persistence must record bucket_id; cancellation/timeout refund MUST return to original bucket, otherwise inventory drifts.
Bucket imbalance causes "total stock available but assigned bucket empty": limited cross-bucket probes (2-3 retries) + background rebalancing + tail bucket fallback.
Bucket count = hotspot QPS / single-row safe TPS, with 2x headroom, calibrated by load test.
4.6 Cloud Hot-Row Optimization
If using cloud RDS (AliCloud RDS/PolarDB), enable Hot Row Optimization — lock queuing/merging at storage layer. Self-hosted: shorten transactions + batch merge deductions (group by SKU, sort by ID, batches of 50-200, single transaction <50ms). In-memory merging risks data loss — must persist + reconcile.
4.7 Consistency Guarantees
Redis deduct succeeds, MQ send fails : Transactional messages / local message table + scheduled compensation rollback
Order placed but unpaid : Delayed message cancels order; state machine CAS: UPDATE t_order SET status=2 WHERE order_no=? AND status=1; refund with idempotency key
Redis vs DB inventory drift : Every 5 min reconcile using DB as source of truth; refund with idempotency key prevents duplicate credit
Cancel message redelivery causes duplicate refund : Order state machine CAS + refund idempotency key + return to original bucket
Three-layer summary: Redis blocks 99% invalid traffic → MQ flattens spike → MySQL row-lock SQL is final baseline. Database is the single source of truth; Redis is just a read-acceleration shield.
Why DB deduct again if Redis succeeded? Redis is performance component, not ledger — persistence may lose data, failover may lose data, Lua doesn't roll back. Final correctness requires DB unique index + conditional update. Why not Redis distributed lock for inventory? Locks forcibly serialize concurrency; Redis single command/Lua is already atomic. Redlock has clock/GC pause controversies.
5. Read Pressure: Multi-Level Cache & Three Cache Problems
5.1 Multi-Level Cache Architecture
Local Caffeine cache absorbs single-machine hotspots, cuts 90% Redis calls; super-hot keys use key sharding + API rate limiting. Baseline Cache-Aside with penetration/avalanche defenses built in:
public ProductVO getProduct(Long id) {
String key = "product:" + id;
String json = redis.opsForValue().get(key);
if (json != null) {
return EMPTY_FLAG.equals(json) ? null : JSON.parseObject(json, ProductVO.class);
}
ProductVO vo = productMapper.selectById(id);
if (vo == null) {
redis.opsForValue().set(key, EMPTY_FLAG, 60, TimeUnit.SECONDS); // short TTL empty value → prevent penetration
return null;
}
long ttl = 1800 + ThreadLocalRandom.current().nextLong(300); // random jitter → prevent avalanche
redis.opsForValue().set(key, JSON.toJSONString(vo), ttl, TimeUnit.SECONDS);
return vo;
}5.2 Cache Penetration, Breakdown, Avalanche
Penetration : Symptom: Query non-existent key → requests hit DB. Solution: Bloom filter intercept + cache empty value (short TTL)
Breakdown : Symptom: Single hot key expires → traffic blasts DB. Solution: Mutex rebuild / logical expiration async refresh
Avalanche : Symptom: Mass keys expire simultaneously. Solution: Random TTL jitter / multi-level cache fallback
MySQL 8.0 removed Query Cache because it cached full result sets — any table change invalidated entire cache, causing invalidation storms that hurt write-heavy workloads. Replacement: Buffer Pool (caches physical pages, finer granularity) + application-level Redis.
5.3 Breakdown Defense: Mutex Rebuild vs Logical Expiration
Weapon 1: Mutex + Double-Check (Redisson, guarantees strong consistency — reads new value):
public Order getOrder(Long orderId) {
String key = "order:" + orderId;
Order order = (Order) redisTemplate.opsForValue().get(key);
if (order != null) return order;
RLock lock = redissonClient.getLock("lock:order:" + orderId);
try {
lock.lock(3, TimeUnit.SECONDS);
order = (Order) redisTemplate.opsForValue().get(key); // double-check
if (order != null) return order;
order = orderMapper.selectById(orderId); // single request backfills
if (order != null) {
long ttl = 30 + ThreadLocalRandom.current().nextInt(60);
redisTemplate.opsForValue().set(key, order, ttl, TimeUnit.MINUTES);
}
return order;
} finally {
if (lock.isHeldByCurrentThread()) lock.unlock();
}
}Weapon 2: Logical Expiration + Async Rebuild (trade brief staleness for zero blocking, ideal for flash sales):
public GoodsDTO getGoods(Long skuId) {
GoodsDTO local = LOCAL_CACHE.get(skuId, k -> null); // Caffeine local cache
if (local != null) return local;
RedisData rd = redis.get(GOODS_KEY + skuId); // value carries logical expire time, physical never expires
if (rd == null) return loadAndCache(skuId); // cold start
if (rd.getExpireAt() > System.currentTimeMillis()) {
LOCAL_CACHE.put(skuId, rd.getData());
return rd.getData();
}
// Logical expired: grab mutex, winner async rebuilds, losers return stale value
String lockKey = "lock:goods:" + skuId;
if (redis.setNx(lockKey, "1", 10, TimeUnit.SECONDS)) {
try {
CACHE_REBUILD_EXECUTOR.submit(() -> {
GoodsDTO fresh = goodsMapper.selectById(skuId);
redis.set(GOODS_KEY + skuId, new RedisData(fresh, nowPlus(30, MINUTES)));
LOCAL_CACHE.put(skuId, fresh);
});
} finally { redis.del(lockKey); }
}
return rd.getData(); // tolerate brief staleness for zero blocking
}Mutex rebuild : Consistency: Always reads latest value. Throughput: Non-lock-holders wait. Use case: Consistency-sensitive reads.
Logical expiration : Consistency: Briefly reads stale value. Throughput: Zero blocking, stable P99. Use case: Flash sale hotspots, can tolerate seconds of staleness.
Why not "SETNX fail then sleep retry"? Blocks threads, kills connection pool. Logical expiration trades consistency for throughput — decide upfront if business can accept it.
5.4 Cache-DB Consistency: Don't Chase Strong Consistency, Aim for Controllable Eventual Consistency
Preferred: Update DB → Delete cache (not update). Delete twice with delay to cover replication/concurrent read windows:
@Transactional(rollbackFor = Exception.class)
public void updateUser(User user) {
userMapper.updateById(user); // 1. Update DB
redisTemplate.delete("user:" + user.getId()); // 2. Immediate delete
// 3. Delayed second delete covers master-slave sync / concurrent read window
scheduledExecutor.schedule(() -> redisTemplate.delete("user:" + user.getId()), 500, TimeUnit.MILLISECONDS);
}Higher requirement: Canal masquerades as slave to subscribe binlog, change events via MQ drive cache invalidation — business code fully decoupled.
❌ Never "delete cache then update DB" — concurrent reads re-populate stale value, dirty data lives full TTL.
❌ Don't gamble on delayed double-delete — delay time guessed is gambling. Binlog subscription + TTL fallback + scheduled reconciliation is production-grade combo.
Local cache invalidation via Redis Pub/Sub broadcast across instances.
6. Read-Write Separation: Prevent "Just-Written Can't Be Read" Incidents
6.1 Transparent Implementation: Zero Business Code Intrusion
ThreadLocal + AbstractRoutingDataSource with AOP annotation auto-switching:
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> HOLDER = new ThreadLocal<>();
public static void set(String type) { HOLDER.set(type); }
public static String get() { return HOLDER.get(); }
public static void clear() { HOLDER.remove(); }
}
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.get();
}
}Custom @ReadFromSlave annotation + AOP aspect auto-switches — business code untouched. Preferred for small/medium teams.
6.2 Master-Slave Lag: The Dark Pit You Must Face
Read from slave immediately after write → stale data → "user's new order not found" = production incident. Strategies by scenario:
Force master for read-after-write : Scenario: Query order right after placing. Details: Simplest: @Transactional(readOnly=false), hint routing, or session stickiness
GTID position wait : Scenario: Need precise replication catch-up. Details: WAIT_FOR_EXECUTED_GTID_SET until slave catches up
Semi-sync replication : Scenario: Higher consistency needs. Details: At least one slave ACKs before commit, reduces lag window
Parallel replication : Scenario: High master write concurrency. Details: Increase replica_parallel_workers / slave_parallel_workers
Fallback to master on lag threshold : Scenario: Slave lag exceeds threshold (e.g., 3s). Details: Better master takes extra load than user sees dirty data
Cascading replication : Scenario: Many slaves, master binlog pressure. Details: Master → Slave → Slave, offloads master replication
Accept stale reads : Scenario: Product listings, comments. Details: Short inconsistency tolerable, go straight to slave
One-liner: read-after-write goes to master, lag-tolerant goes to slave. Never "write then immediately read slave".
6.3 Architecture Evolution Path
Typical scaling progression: single DB → add cache → add read replicas → add MQ for async → sharding. Each layer added only when previous layer saturates — the rhythm emphasized throughout.
7. Connection Pool & Batch Writes: Counter-Intuitive Traps
7.1 Connection Pool: Bigger Isn't Better
Pool = restaurant seats, requests = diners. Too few → queue; too many → waiters (CPU) run around, service slows. Beyond CPU cores, context-switch cost explodes — more connections = worse latency, not more throughput. That's why 200 connections performed worse than 20.
HikariCP core params aren't guesswork:
spring:
datasource:
url: jdbc:mysql://db:3306/trade?rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=500&characterEncoding=utf8mb4
hikari:
maximum-pool-size: 20 # formula: CPU cores × 2 + disk count, start ≤50
minimum-idle: 20 # critical path matches maximum, avoid peak-time connection creation
connection-timeout: 3000 # fast fail if no connection, don't wait 30s
validation-timeout: 1000
idle-timeout: 600000
max-lifetime: 1740000 # must be < MySQL wait_timeout, leave 30s margin
keepalive-time: 60000
leak-detection-threshold: 60000 # connection leak detectionmaximumPoolSize estimated by (CPU × 2) + disks, typically 10-30, calibrated by load test inflection point.
Total connections = pool size × app instances. 20 × 50 instances = 1000 connections → DB overwhelmed. Sum across all instances must be < MySQL max_connections, leaving 20% ops margin.
maxLifetime < MySQL wait_timeout, else server-side close leaves app with dead connections.
connection-timeout 3s fast-fail + circuit breaker — queuing only amplifies avalanche.
Leak detection controversial: enable 60s threshold during debugging; some argue high-concurrency production shouldn't run long due to pool traversal overhead. We run 60s + monitor overhead, decide per system.
7.2 Batch Write: Write-Path Cost-Effectiveness King
// ❌ 1000 network round-trips + 1000 transaction commits
for (OrderLog log : logs) { orderLogMapper.insert(log); }<!-- ✅ Single SQL batch insert, network round-trips and commits reduced to 1/N -->
<insert id="batchInsert" parameterType="list">
INSERT INTO order_log(order_id, op, create_time) VALUES
<foreach collection="list" item="i" separator=",">
(#{i.orderId}, #{i.op}, now())
</foreach>
</insert>Or MyBatis BATCH mode:
sqlSessionTemplate.execute(session -> {
OrderItemMapper mapper = session.getMapper(OrderItemMapper.class);
for (OrderItem item : list) { mapper.insert(item); } // ExecutorType.BATCH
session.flushStatements();
return null;
});Real data: 10k single inserts 6.2s → batch 100/batch 0.4s, 15x speedup.
Three hard constraints:
Batch size ≤ 500-1000 rows to avoid max_allowed_packet and long transactions.
JDBC URL MUST include rewriteBatchedStatements=true — driver merges multiple INSERT into VALUES (),(),(), cutting network round-trips from N to 1, 5-10x write throughput boost.
It ONLY rewrites batch INSERT; batch UPDATE not merged — don't expect it to optimize inventory deduction.
8. InnoDB Parameters: Final Polish
Prerequisite: parameter tuning is the LAST gain, only after SQL/index optimization. Reverse order = putting cart before horse.
# Memory (dedicated DB, don't co-locate with app)
innodb_buffer_pool_size = 12G # 60-75% physical RAM, dedicated OLTP up to 80%, leave ≥2G for OS
innodb_buffer_pool_instances = 8 # split buffer pool, reduce concurrent access lock contention
# IO Flush (performance vs durability trade-off)
innodb_flush_log_at_trx_commit = 2 # finance/trading use 1; tolerate second-level loss use 2, 30%+ perf gain
sync_binlog = 1000 # group commit reduces fsync; pair with above, finance uses dual-1
# Concurrency & Locks
transaction_isolation = READ-COMMITTED # lower isolation, drastically reduces lock conflicts
innodb_autoinc_lock_mode = 2 # auto-inc lock optimization, batch insert perf doubles
innodb_lock_wait_timeout = 5 # high concurrency: don't let lock wait kill requests
max_connections = 2000 # peak +20% headroom, but app pool must be far smaller
thread_cache_size = 100 # reuse connection threads, reduce creation overhead
# SSD Specific
innodb_flush_neighbors = 0 # SSD must disable; mechanical disks need neighbor page flush
innodb_io_capacity = 2000 # SSD don't use default 200; NVMe 2000-5000+
# Temp Tables
tmp_table_size = 64M
max_heap_table_size = 64M # reduce Created_tmp_disk_tablesCritical trade-offs and traps:
Dual-1 for safety : innodb_flush_log_at_trx_commit=1 + sync_binlog=1 is data safety baseline. Switching to 2/1000 gives clear TPS boost but power loss may lose seconds of transactions — forbidden for trading/payment , fine for logs/tracking.
Buffer Pool hit rate is core KPI : monitor via Innodb_buffer_pool_read_requests, target ≥99% (min 99.5%). Persistent low hit rate = insufficient memory, add RAM before anything else.
Chunk size multiple trap : innodb_buffer_pool_size must be integer multiple of innodb_buffer_pool_chunk_size × instances, else startup silently rounds down — you think 12G but actually get 11G.
9. Sharding: Last Resort, Not Silver Bullet
9.1 When to Actually Shard
Problem-driven, not hype-driven, not mystical "20M rows" numbers. Real triggers:
Single table sustained >10-20M rows (some scenarios 50M), B+ tree depth increases, RT visibly climbs.
Connection pool at 50 still frequent "get connection timeout".
Single library write throughput capped: Threads_running high, binlog sync lag, lock contention up, single DB TPS sustained >5000.
Disk capacity maxed, archiving doesn't relieve.
Conversely, if just occasional slow queries: first do three things: optimize SQL, add indexes, add read replicas . Many "must shard" problems are actually missing one composite index.
9.2 Shard Key Choice: Wrong Key = Wasted Effort
user_id : Preferred. Reason: C-end queries naturally aggregate, even distribution.
order_id + user_id combo : Recommended. Reason: Route by user, order_no globally unique.
create_time : Avoid. Reason: Time-increasing → all writes hit latest shard, creates hotspot.
UUID : Avoid. Reason: String hash uneven, index efficiency low.
Auto-increment id : Caution. Reason: New data concentrates on last shard, loses user-based query ability.
status (low cardinality) : Avoid. Reason: Data skew + cross-shard aggregation.
Need query by both buyer and seller? Two approaches: embed user_id shard bits into order_no (gene method: shard = buyer_id % 16 encoded in order_no suffix) so "query by order_no" also routes precisely; seller dimension list queries via Canal → ES heterogeneous index, no cross-shard scan.
9.3 ShardingSphere-JDBC Configuration
Java stack: ShardingSphere-JDBC preferred — embedded in app, no centralized node, zero extra network/deployment cost, sharding logic at JDBC layer, minimal ops overhead.
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0: {type: com.zaxxer.hikari.HikariDataSource, jdbc-url: jdbc:mysql://db0:3306/order, username: app, password: ***}
ds1: {type: com.zaxxer.hikari.HikariDataSource, jdbc-url: jdbc:mysql://db1:3306/order, username: app, password: ***}
rules:
sharding:
tables:
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..3} # 2 DBs × 4 tables = 8 shards
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: db-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: tbl-inline
key-generate-strategy:
column: order_id
key-generator-name: snowflake # distributed PK, not UUID
sharding-algorithms:
db-inline: {type: INLINE, props: {algorithm-expression: ds$->{user_id%2}}}
tbl-inline: {type: INLINE, props: {algorithm-expression: t_order_$->{order_id%4}}}
key-generators:
snowflake: {type: SNOWFLAKE, props: {worker-id: 1}}
props:
sql-show: falseCustom sharding algorithm (e.g., user_id modulo routing):
public class OrderDbSharding implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> dbNames, PreciseShardingValue<Long> shardingValue) {
long userId = shardingValue.getValue();
return "ds" + (userId % dbNames.size());
}
}Hash modulo = even distribution, simple; range sharding = time-range queries but prone to hotspots. Choose per scenario.
9.4 Four Inevitable Costs — Think Before Acting
Cross-shard JOIN becomes distributed problem : forbid full scans without shard key; denormalize wide tables to avoid cross-shard joins, or assemble in app layer.
Local transaction becomes distributed transaction : selection per comparison table below.
Deep pagination must merge all shard results : cross-shard ORDER BY + LIMIT 100000 explodes memory and RT. Solutions: force shard key routing to single shard, each shard takes top N then in-memory merge TopK, per-shard cursor + merge, or offload to ES search_after.
Resharding is a major project : modulo sharding requires full data migration.
Distributed transaction comparison:
Seata XA : Consistency: Strong. Performance: Low, long resource locks. Fit: Financial strong consistency.
Seata AT : Consistency: Near-strong. Performance: Medium. Fit: General business; warning: core trading blindly using AT makes global lock new hotspot.
Local message table / transactional messages : Consistency: Eventual. Performance: High. Fit: Non-core paths, internet business default.
9.5 Resharding, Global ID, Data Skew
Resharding : prefer power-of-two scaling (2→4→8), migrate via dual-write + gradual cutover. Safer: plan 32 logical shards at launch, place fewer on physical machines, resharding = logical shard migration. Consistent hashing reduces migration volume.
Global ID : Snowflake must handle clock backtrack + workerId allocation; segment mode recommends Leaf dual-buffer, issues IDs even during DB jitter. Snowflake/Leaf also avoids auto-increment PK hotspots and page splits.
Data skew : pre-launch load test shard row count deviation within ±15%. Hot shard appears → split separately, introduce secondary sharding, shard key salting hash(uid + suffix), or cache hot data.
9.6 Alternatives That Extend Life Without Sharding
Vertical split : Scenario: Large fields (details, images) slow scans. Key point: Cold columns to sub-table, main table slim, more rows per page.
Partition table / archive : Scenario: Logs, historical orders. Key point: Time-based partition, cold data to OSS / History DB.
Read replicas : Scenario: Read pressure high. Key point: Horizontal read scaling, cheaper than sharding.
Non-core feature degradation : Scenario: Flash sale survival. Key point: Reviews, recommendations off first, protect trading core.
Archive, partition, optimize indexes first — then talk sharding. Done right, you may never need sharding.
10. Load Test Verification & Release Loop: No Naked Runs
Optimization isn't done until: load test + canary + rollback plan.
Watch P99/P999, error rate, Threads_running, lock waits, replication lag — NOT average RT (averages lie). Full-chain load test + shadow tables + trace propagation if possible; JMeter/Gatling/sysbench for before/after capacity comparison.
Real seckill system before/after:
API P99 : Before: 1.2s → After: 85ms
DB Write QPS : Before: 3200 (saturated) → After: 600 (headroom)
Oversell : Before: 3 orders → After: 0
Deadlocks : Before: 12/hour → After: 0
Slow SQL : Before: 800/min → After: <5/min
Another order DB governance: P99 2100ms → 95ms, DB QPS 30k → 4k, slow queries zero. One 2.1s full scan + filesort → composite index + delayed join → 12ms.
Release five-step: canary ramp → kill-switch (queue page/read-only mode) → monitoring alerts → reconciliation safety net → rollback ready.
Long-term prevention beyond hotspot fixes:
Slow query threshold 100ms + alerting.
Mandatory EXPLAIN review before SQL deployment.
SQL governance platform to auto-block full table scans if feasible.
11. Summary: Cheat Sheet + Heartfelt Advice
Locate first → watch CPU/IO/Threads_running/slow SQL, don't guess parameters
Index king → B+ tree 3-4 levels, composite leftmost prefix, covering index avoids lookup, don't let functions kill index
SQL cleanup → deep pagination via delayed join or cursor, batch over loops, less SELECT *
Transaction short → connections ≈ cores×2, no RPC in TX, RC+ROW binlog, lower lock timeout
Hotspot scatter → Redis Lua pre-deduct → MQ async → inventory bucketing → SQL atomic deduct fallback
Read via cache → multi-level + three-problem defense, update DB then delete cache + Canal fallback
Params last → buffer pool 60-75%, SSD raise io_capacity, flush policy per business tolerance
Architecture restraint → read-write separation → sharding (problem-driven, shard key user_id)
Safety net mindset → DB always single source of truth, consistency via reconciliation not trustThree mindsets worth more than any keyword: restraint to diagnose before acting, initiative to articulate trade-offs, closed-loop driven by data. The most traffic-resilient architecture isn't the most complex — it's the one that eliminates every "unnecessary database access".
High-concurrency optimization essence: not making MySQL run faster, but making it do less work.
Two actionable takeaways: 1) Save Chapter 2 diagnostic commands and Chapter 9 parameter checklist — next alert, follow them directly. 2) If your system hasn't reached sharding scale, perfect indexes and transactions first — ROI is far larger than you think.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
