Unlock 10× Faster Queries: The Database Performance Optimization Pyramid Explained

In a programming career, 80 % of performance problems stem from the database; a single slow SQL can cripple a system, while proper hardware can boost performance more than tenfold.

Database Performance Optimization Pyramid Model

The "performance optimization pyramid" consists of five layers, each contributing incremental gains:

Application layer optimization (10‑20% gain)
↑
SQL statement optimization (30‑50% gain)
↑
Index design optimization (40‑80% gain)
↑
Database configuration optimization (20‑40% gain)
↑
Hardware resource optimization (50‑200% gain)

First Layer: Practical SQL Optimization Techniques

1.1 Avoid Full‑Table Scans

❌ Wrong example:

-- 这样的查询会让DBA想打人
SELECT * FROM orders WHERE create_time > '2024-01-01';

✅ Correct example:

-- 使用索引，指定具体字段
SELECT order_id, user_id, amount
FROM orders
WHERE create_time >= '2024-01-01'
  AND create_time < '2024-02-01'
  AND status = 'completed';

Performance comparison: query time drops from 12 s to 0.03 s, a 400× improvement.

1.2 Golden Rules for JOIN Optimization

-- Before: Cartesian product disaster
SELECT u.name, o.amount
FROM users u, orders o
WHERE u.id = o.user_id
  AND u.status = 'active';

-- After: Explicit INNER JOIN
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.create_time >= CURDATE() - INTERVAL 30 DAY;

1.3 Subquery vs EXISTS Performance Showdown

-- Slow: subquery
SELECT *
FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

-- Fast: EXISTS
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
      AND o.amount > 1000
);

Tested on 1 million rows: EXISTS is 60 % faster than IN.

Second Layer: The Art of Index Design

2.1 Composite Index Best Practices

Indexes are not "more is better"; they must target high‑cardinality columns first.

-- Wrong: single‑column indexes
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_create_time ON orders(create_time);

-- Correct: composite index matching query pattern
CREATE INDEX idx_user_status_time ON orders(user_id, status, create_time);

Three principles for composite indexes:

Place high‑selectivity columns first.

Put range‑query columns last.

Prioritize the most frequently used filter conditions.

2.2 Common Index‑Invalidating Traps

Function on indexed column

-- ❌
SELECT * FROM orders WHERE YEAR(create_time) = 2024;

Use a range condition instead:

SELECT * FROM orders
WHERE create_time >= '2024-01-01'
  AND create_time < '2025-01-01';

Implicit type conversion

-- ❌ user_id stored as INT, compared to string
SELECT * FROM orders WHERE user_id = '123';

-- ✅
SELECT * FROM orders WHERE user_id = 123;

Leading wildcard in LIKE

-- ❌
SELECT * FROM users WHERE name LIKE '%张%';

-- ✅
SELECT * FROM users WHERE name LIKE '张%';

2.3 Power of Covering Indexes

-- Normal query (needs back‑table lookup)
SELECT user_id, amount FROM orders WHERE status = 'completed';

-- Create covering index
CREATE INDEX idx_status_cover ON orders(status, user_id, amount);
-- Now the query is satisfied entirely from the index.

Result: query speed improves 3‑5×, I/O drops 80 %.

Third Layer: Database Parameter Tuning

3.1 Core MySQL Parameters

# my.cnf production recommendations
[mysqld]
innodb_buffer_pool_size = 16G
innodb_log_file_size   = 2G
innodb_log_files_in_group = 2
max_connections        = 2000
max_connect_errors     = 100000
tmp_table_size         = 256M
max_heap_table_size    = 256M
sort_buffer_size       = 4M
read_buffer_size       = 2M
read_rnd_buffer_size   = 8M
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2
innodb_flush_method    = O_DIRECT

3.2 Monitoring Key Metrics

Buffer pool hit rate > 99 %.

Reasonable QPS/TPS ratio.

Slow queries < 1 % of total.

Lock wait time < 100 ms.

Connection usage < 80 %.

Fourth Layer: Hardware Investment‑Return Ratio

4.1 Storage Selection Strategy

Performance comparison (HDD vs SATA SSD vs NVMe SSD):

# Example numbers
HDD:      IOPS 100‑200, 150 MB/s, 10‑15 ms latency, low cost, cold data.
SATA SSD: IOPS 40K‑90K, 500 MB/s, 0.1 ms latency, medium cost, general workloads.
NVMe SSD: IOPS 200K‑1M, 3500 MB/s, 0.02 ms latency, high cost, high‑concurrency.

Moving MySQL data from HDD to NVMe SSD reduced average query latency from 200 ms to 15 ms—a 13× boost.

4.2 Memory Sizing Guidelines (example 64 GB server)

System reserve          : 8 GB  (12.5 %)
InnoDB buffer pool     : 45 GB (70 %)
Connections & temp tables: 8 GB (12.5 %)
Other applications     : 3 GB  (5 %)

Warning signs of insufficient memory: frequent disk I/O, buffer‑pool hit rate < 95 %, swapping.

4.3 CPU Recommendations

Core count: 16‑32 cores for high concurrency.

Frequency: ≥ 3.0 GHz.

L3 cache: ≥ 20 MB.

Architecture: x86_64 with SSE4.2 support.

Monitoring commands:

top -p $(pgrep mysql)
iostat -x 1
sar -u 1

Key CPU metrics: usage < 70 %, load average < core count, context switches < 1000 /s.

4.4 Network Tuning

# Example sysctl tweaks
echo 'net.core.rmem_max = 268435456' >> /etc/sysctl.conf
echo 'net.core.wmem_max = 268435456' >> /etc/sysctl.conf
echo 'net.ipv4.tcp_rmem = 4096 87380 268435456' >> /etc/sysctl.conf
echo 'net.ipv4.tcp_wmem = 4096 65536 268435456' >> /etc/sysctl.conf
echo 'net.core.netdev_max_backlog = 5000' >> /etc/sysctl.conf
sysctl -p

Fifth Layer: Architectural Performance Boost

5.1 Sharding Strategy

-- Horizontal sharding example by user_id modulo
CREATE TABLE orders_0 LIKE orders;
CREATE TABLE orders_1 LIKE orders;
CREATE TABLE orders_2 LIKE orders;
CREATE TABLE orders_3 LIKE orders;

5.2 Cache Layer Design

# Redis cache pattern
# 1. Check cache
# 2. If miss, query DB
# 3. Write result to cache with TTL 1h

Performance Monitoring and Diagnosis Toolbox

MySQL Monitoring Tools

# 1. Slow query analysis
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 2. Real‑time process list
SHOW PROCESSLIST;

# 3. InnoDB engine status
SHOW ENGINE INNODB STATUS;

# 4. Top statements by execution time
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

# 5. System‑level metrics
iostat -x 1
sar -u 1 10
free -h

Optimization Checklist

SQL‑Level

Avoid SELECT *, query only needed columns.

Use LIMIT to bound result sets.

Order WHERE conditions for selectivity.

Never apply functions to indexed columns.

Prefer explicit JOINs over Cartesian products.

Prefer EXISTS over IN for subqueries.

Replace OR with UNION when appropriate.

Index‑Level

Create indexes for all WHERE predicates.

Index ORDER BY columns.

Use covering indexes to avoid back‑table lookups.

Periodically analyze index usage.

Drop redundant indexes.

Design composite index column order wisely.

Configuration‑Level

Set innodb_buffer_pool_size appropriately.

Configure max_connections based on workload.

Adjust temporary table sizes.

Size log files for durability and performance.

Enable query cache only on MySQL 5.7‑ and earlier.

Hardware‑Level

Store data files on SSDs (NVMe preferred).

Provision sufficient RAM for buffer pool.

Choose CPUs that meet core and frequency needs.

Ensure network bandwidth matches traffic.

Verify disk I/O performance meets latency targets.