1. 数据库世界的"三驾马车"

如果把数据库比作交通工具，SQLite就是便捷的折叠自行车，MySQL是经济实惠的共享汽车，PostgreSQL则像全地形的越野车。作为现代软件开发中最重要的数据容器，这三种数据库在不同场景中发挥着独特价值。我们手机里的健身APP可能用SQLite记录步数，刷短视频时背后是MySQL处理播放记录，银行转账交易则依赖PostgreSQL保障事务安全。本文将带您穿梭于不同技术栈之间，通过真实的代码示例感受它们的温度。

2. SQLite：轻装上阵的数据伴侣

2.1 嵌入式开发实战（Python技术栈）

import sqlite3
import datetime

# 创建内存数据库（无需物理文件）
conn = sqlite3.connect(':memory:')

# 构建健身记录表（字段注释直接写在DDL中）
conn.execute('''
    CREATE TABLE fitness_log (
        id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自增主键
        activity_type TEXT CHECK(activity_type IN ('running','swimming')),  -- 枚举约束
        duration INTEGER NOT NULL,  -- 运动时长（分钟）
        calories INTEGER DEFAULT 0,  -- 消耗卡路里
        record_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 自动记录时间
    )
''')

# 插入晨跑数据（省略列名示例）
conn.execute("INSERT INTO fitness_log VALUES (NULL, 'running', 30, 240, ?)", 
           (datetime.datetime.now(),))

# 查询最近三条记录（展示简单查询）
cursor = conn.execute('''
    SELECT activity_type, duration 
    FROM fitness_log 
    ORDER BY record_time DESC 
    LIMIT 3
''')
print(cursor.fetchall())

conn.close()

特征分析：

  • 无需独立服务进程，通过文件或内存直接交互
  • 单文件存储结构便于移动设备管理
  • 事务处理采用文件锁机制，适合低频写入场景

2.2 移动端数据缓存示例（Python伪代码）

# 模拟手机应用的本地缓存机制
class LocalCache:
    def __init__(self):
        self.db = sqlite3.connect('/data/user/0/com.example.app/databases/cache.db')
        self._create_table()
    
    def _create_table(self):
        '''创建缓存表，存储临时网络数据'''
        self.db.execute('''
            CREATE TABLE IF NOT EXISTS api_cache (
                url TEXT PRIMARY KEY,
                response_text TEXT,
                expire_time INTEGER  -- Unix时间戳
            )
        ''')
    
    def save_cache(self, url, data, ttl):
        '''保存带过期时间的缓存'''
        self.db.execute('''
            INSERT OR REPLACE INTO api_cache 
            VALUES (?, ?, ?)
        ''', (url, data, int(time.time()) + ttl))
    
    def get_valid_cache(self, url):
        '''获取未过期的缓存'''
        cursor = self.db.execute('''
            SELECT response_text 
            FROM api_cache 
            WHERE url=? AND expire_time > ?
        ''', (url, int(time.time())))
        return cursor.fetchone()

3. MySQL：Web应用的中流砥柱

3.1 电商用户系统设计（Python+MySQL技术栈）

import mysql.connector

config = {
    'user': 'web_user',
    'password': 'securePass123!',
    'host': 'mysql-cluster.mydomain.com',
    'database': 'ecommerce',
    'port': 3306
}

conn = mysql.connector.connect(**config)

# 创建带索引的用户表
conn.cursor().execute('''
    CREATE TABLE IF NOT EXISTS users (
        user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password_hash CHAR(60) NOT NULL,  -- bcrypt加密存储
        last_login TIMESTAMP NULL,
        INDEX idx_username (username)  -- 用户名搜索优化
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
''')

# 批量插入测试用户
users = [
    ('john_doe', '$2b$12$...'),
    ('jane_smith', '$2b$12$...')
]
conn.cursor().executemany('''
    INSERT INTO users (username, password_hash)
    VALUES (%s, %s)
''', users)

# 分页查询示例
def get_users(page=1, per_page=10):
    offset = (page-1)*per_page
    cursor = conn.cursor(dictionary=True)
    cursor.execute('''
        SELECT user_id, username 
        FROM users 
        ORDER BY user_id ASC 
        LIMIT %s OFFSET %s
    ''', (per_page, offset))
    return cursor.fetchall()

3.2 读写分离配置片段（Python示例）

from mysql.connector import pooling

# 创建连接池（区分读写节点）
read_config = {**config, 'host': 'read01.mysql-cluster'}
write_config = {**config, 'host': 'write01.mysql-cluster'}

read_pool = pooling.MySQLConnectionPool(pool_name="read_pool",
                                      pool_size=5,
                                      **read_config)

write_pool = pooling.MySQLConnectionPool(pool_name="write_pool",
                                       pool_size=3,
                                       **write_config)

def execute_query(sql, params=None):
    """执行读操作"""
    conn = read_pool.get_connection()
    # ...执行查询逻辑...

def execute_update(sql, params=None):
    """执行写操作"""
    conn = write_pool.get_connection()
    # ...执行更新逻辑...

4. PostgreSQL：复杂业务的数据堡垒

4.1 GIS数据处理示例（Python+PostgreSQL）

import psycopg2
from geoalchemy2 import WKTElement

conn = psycopg2.connect(
    dbname='gis_db',
    user='postgres',
    password='strongPassword',
    host='geo-cluster.example.com'
)

# 创建空间数据表
conn.cursor().execute('''
    CREATE TABLE delivery_routes (
        route_id SERIAL PRIMARY KEY,
        driver_id INTEGER REFERENCES drivers(id),
        path GEOGRAPHY(LINESTRING) NOT NULL,
        coverage_area GEOGRAPHY(POLYGON)
    )
''')

# 插入配送路线（WKT格式）
route_line = WKTElement('LINESTRING(116.404 39.915, 121.473 31.230)')
conn.cursor().execute('''
    INSERT INTO delivery_routes (driver_id, path)
    VALUES (%s, %s)
''', (123, route_line))

# 半径查询优化
def find_routes_in_radius(lng, lat, radius):
    cursor = conn.cursor()
    cursor.execute('''
        SELECT route_id 
        FROM delivery_routes 
        WHERE ST_DWithin(
            path::geography,
            ST_MakePoint(%s, %s)::geography,
            %s
        )
    ''', (lng, lat, radius))
    return cursor.fetchall()

4.2 JSONB字段处理示例

# 创建动态表单存储表
conn.cursor().execute('''
    CREATE TABLE dynamic_forms (
        form_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        form_data JSONB NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
        EXCLUDE USING GIN (form_data jsonb_path_ops)
    )
''')

# 插入嵌套JSON数据
medical_record = {
    "patient": {"id": "P123", "name": "张三"},
    "diagnosis": {
        "codes": ["ICD-10:J45"],
        "description": "慢性阻塞性肺病"
    },
    "medications": [
        {"name": "沙丁胺醇", "dose": "2mg/day"}
    ]
}
conn.cursor().execute('''
    INSERT INTO dynamic_forms (form_data)
    VALUES (%s)
''', (psycopg2.extras.Json(medical_record),))

# 路径查询示例
def search_medication(med_name):
    cursor = conn.cursor()
    cursor.execute('''
        SELECT form_data->'patient'->>'name'
        FROM dynamic_forms 
        WHERE form_data @> '{"medications": [{"name": %s}]}'
    ''', (med_name,))
    return cursor.fetchall()

5. 应用场景矩阵

SQLite最佳实践：

  • 移动应用本地存储（如聊天记录缓存）
  • 单机软件配置管理（如IDE插件设置）
  • 物联网设备日志存储（如智能手环）
  • CI/CD测试环境临时数据库

MySQL主力战场：

  • 电子商务系统（用户订单管理）
  • 内容管理系统（WordPress后台）
  • 实时统计报表（网站访问计数）
  • 社交网络基础架构（好友关系存储）

PostgreSQL专家模式：

  • 金融交易系统（ACID严格保障）
  • GIS地理信息系统（空间数据处理）
  • 科研数据分析（复杂计算需求）
  • 时序数据处理（扩展timescaledb）

6. 三维度对比分析

架构维度：

  • SQLite采用库文件直连模式
  • MySQL使用客户端/服务端模型
  • PostgreSQL引入多进程架构

并发控制：

| 数据库     | 锁机制           | MVCC支持 | 最大连接数 |
|------------|------------------|----------|------------|
| SQLite     | 文件锁           | 部分     | 单连接写入 |
| MySQL      | 行级锁（InnoDB） | 支持     | 数千级     |
| PostgreSQL | 行级锁           | 完善支持 | 数万级     |

扩展能力：

  • SQLite：通过扩展实现部分GIS功能
  • MySQL：通过中间件实现分库分表
  • PostgreSQL：原生支持JSONB、全文搜索等

7. 选型决策树

  1. 是否需要独立服务进程？

    • 是 → MySQL/PostgreSQL
    • 否 → SQLite

  2. 预期并发写入量？

    • < 10次/秒 → SQLite可能胜任
    • 10-1000次 → MySQL

    • 1000次 → PostgreSQL

  3. 是否需要复杂查询？

    • 基础CRUD → SQLite/MySQL
    • 空间计算/窗口函数 → PostgreSQL

  4. 部署环境限制？

    • 移动端/嵌入式 → SQLite
    • 云原生环境 → 按需选择集群方案

8. 避坑指南

SQLite注意事项：

  • 避免在多进程环境下同时写入
  • VACUUM定期整理数据库碎片
  • 使用WAL模式提升并发性能

MySQL调优技巧：

  • 合理配置InnoDB缓冲池
  • 定期分析慢查询日志
  • 使用GTID模式进行主从复制

PostgreSQL最佳实践：

  • 合理设置work_mem参数
  • 为常用查询创建部分索引
  • 使用pg_stat_statements监控性能

9. 混合架构案例

某智慧农业系统采用分层存储：

终端传感器 → SQLite本地缓存 → 边缘网关（MySQL分片） → 中心云平台（PostgreSQL分析集群）

这种架构组合发挥各自优势：SQLite保障断网可用性、MySQL处理高并发采集、PostgreSQL实现数据分析。