Database Types, Bottlenecks, Optimization Strategies and Scaling Techniques

1. Database Classification

Databases are broadly divided into two categories: traditional relational databases (e.g., MySQL, Oracle, SQLServer, PostgreSQL) and non‑relational databases such as HBase (column‑store), MongoDB (document), Redis (key‑value), and Lucene (search engine).

2. Bottlenecks and Optimization of Relational Databases

2.1 Why the Architecture Needs Adjustment

Data growth is often exponential.

Read‑write separation and distributed deployment are required because a single machine reaches performance limits.

NoSQL and search engines are needed for special scenarios.

Analytical systems cannot be satisfied by a single relational DB.

Deployment requirements such as same‑city or cross‑city disaster recovery.

2.2 Problems Encountered

2.2.1 Performance

Query performance.

Write/update latency.

Concurrency and data volume.

2.2.2 Functionality

New features (LBS/JSON/特殊业务场景).

Data safety (strong/weak consistency).

Big‑data analysis.

Search and other functions.

3. Storage Selection for Different Business Scenarios

3.1 Why Add a Redis Layer When MySQL Already Has Cache

Redis provides an in‑memory KV store that can serve hot data with sub‑millisecond latency, offloading read pressure from MySQL.

3.2 Database Query Overhead

The most time‑consuming steps in a query are establishing the TCP connection, generating the execution plan, opening the table, scanning data from disk, and closing the connection.

3.2.1 SQL Parsing Example

# 1 SELECT id, name, price FROM products WHERE id IN (1,2,3,...,30000);   # (1‑2s)
# 2 Convert the above into 30000 individual statements
SELECT id, name, price FROM products WHERE id = 1;
... 
SELECT id, name, price FROM products WHERE id = 30000;   # (2‑3s)
# 3 Convert the first statement into an OR chain
SELECT id, name, price FROM products WHERE id = 1 OR id = 2 OR ... OR id = 30000;   # (8‑10s)

The OR‑heavy statement is slow because the parser must process a huge expression.

3.2.2 Primary‑Key Query Example (InnoDB)

SELECT * FROM t WHERE id = ?;

A normally configured server can reach about 400 000 QPS for such queries.

3.2.3 Non‑Primary‑Key Query Example

SELECT * FROM t WHERE name = ?;

MySQL uses a secondary index to locate the primary key, then fetches the full row, which involves multiple I/O operations.

3.2.4 Large Field Issues

Storing large TEXT/BLOB fields can cause high query cost, cache pollution, and even hot‑page eviction. Options include moving large fields to a separate table or using a different storage engine.

3.2.5 Cache Utilization

InnoDB’s default page size is 16 KB; the buffer pool caches pages, but only a portion is usable for data because part of it holds dirty pages and write buffers. Effective caching is crucial for read‑heavy internet services.

3.2.6 Cache Problems

Cache miss.

Cache penetration.

Cache expiration.

Cache consistency.

3.2.7 Choosing the Right Index

Use EXPLAIN to check Extra: Using where; Using filesort. If the index length exceeds 256 bytes, ORDER BY may fall back to filesort, degrading performance.

3.2.8 Index Usage Example

# name varchar(512)
# key idx_name(name(100))
SELECT * FROM comment WHERE name >= 'destiny' ORDER BY name ASC LIMIT 100;

EXPLAIN shows a range scan with Using where; Using filesort, indicating the index is not fully covering the ORDER BY.

3.3 Write Overhead

Strict durability requirements make writes expensive.

Log files need fsync, limited by hardware.

Scaling write throughput is difficult.

Master‑slave consistency adds network latency.

3.4 High‑Concurrency Write Scenarios

3.4.1 Flash Sales (秒杀)

Extreme write concurrency.

Optimizations: cache, token bucket, queue, semaphore, optimistic lock.

Hot‑resource isolation.

Database thread pool.

InnoDB kernel optimizations (AliSQL).

3.4.2 Private Messages / In‑site Notifications

High write volume with accompanying reads.

Separate system and personal messages.

Delay writes via queue/cache for rate limiting.

3.4.3 Music Play Count

Massive write volume.

Updates dominate inserts.

Database must auto‑scale.

Eventual consistency is acceptable.

3.5 Deadlocks and Timeouts

InnoDB lock timeout defaults to 5 s.

Deadlocks are detected and rolled back automatically.

Timeouts usually stem from missing or inefficient indexes.

Deadlocks indicate business logic problems.

3.6 Concurrency Control (Locks)

Pessimistic Lock

BEGIN;
SELECT count FROM tb WHERE id = ? FOR UPDATE;
-- do something
UPDATE tb SET count = count - ? WHERE id = ?;
COMMIT;

Optimistic Lock

BEGIN;
SELECT count FROM tb WHERE id = ?;
UPDATE tb SET count = count - ? WHERE id = ? AND count = :count;
COMMIT;

4. Database Modularization and Sharding

4.1 Limitations of a Single‑Server Deployment

Hardware bottlenecks (CPU, memory, network, I/O, capacity).

Scalability concerns for future growth.

Higher risk of total service failure.

Performance improvements require read‑write separation, hot‑cold data separation, critical‑path isolation, and graceful degradation.

4.2 Common Splitting Solutions

4.2.1 Read‑Write Separation

Deploy a master‑slave cluster: the master handles writes, slaves handle reads. Replication lag and routing mechanisms are the main challenges.

Replication Lag

Lag can be seconds or minutes; solutions include directing post‑write reads to the master, fallback reads to the master on miss, or limiting read‑write separation to non‑critical workloads.

Routing Mechanism

Two approaches: embed logic in application code or use a middleware layer that abstracts read‑write routing.

Application‑Level Routing

Custom data‑access layer handles connection management; simple but language‑specific and harder to maintain.

Middleware Routing

Independent system provides SQL‑compatible protocol, handling routing transparently; supports multiple languages but adds complexity and potential bottleneck.

4.2.2 Distributed Architecture

When data exceeds tens of millions of rows, a single node’s storage becomes a bottleneck. Solutions include vertical partitioning (splitting rarely used large columns into separate tables) and horizontal sharding (splitting rows across multiple nodes), which introduces routing, join, and count complexities.

For more details, see the original blog: https://destinywang.github.io/blog/2019/01/19/关系型数据库的瓶颈与优化