1# 优化前：O(n^2)
 2def find_duplicate(arr):
 3    for i in range(len(arr)):
 4        for j in range(i+1, len(arr)):
 5            if arr[i] == arr[j]:
 6                return True
 7    return False
 8
 9# 优化后：O(n)
10def find_duplicate(arr):
11    seen = set()
12    for num in arr:
13        if num in seen:
14            return True
15        seen.add(num)
16    return False

1# 使用缓存避免重复计算
2def fibonacci(n, memo={}):
3    if n <= 1:
4        return n
5    if n not in memo:
6        memo[n] = fibonacci(n-1, memo) + fibonacci(n-2, memo)
7    return memo[n]

1# 优化前
2result = []
3for item in large_list:
4    if item['status'] == 'active':
5        result.append(item['id'])
6
7# 优化后：使用列表推导式
8result = [item['id'] for item in large_list if item['status'] == 'active']

1# 优化前：字符串拼接
2result = ""
3for item in items:
4    result += item
5
6# 优化后：使用join
7result = "".join(items)

 1// 优化前：频繁创建对象
 2String result = "";
 3for (int i = 0; i < 10000; i++) {
 4    result += i;
 5}
 6
 7// 优化后：使用StringBuilder
 8StringBuilder sb = new StringBuilder();
 9for (int i = 0; i < 10000; i++) {
10    sb.append(i);
11}
12String result = sb.toString();

1-- 创建单列索引
2CREATE INDEX idx_users_email ON users(email);
3
4-- 创建复合索引
5CREATE INDEX idx_users_status_created ON users(status, created_at);
6
7-- 创建唯一索引
8CREATE UNIQUE INDEX idx_users_username ON users(username);

 1-- 1. 索引列上使用函数
 2SELECT * FROM users WHERE DATE(created_at) = '2026-01-01';
 3
 4-- 2. 索引列上使用LIKE前缀模糊匹配
 5SELECT * FROM users WHERE username LIKE '%test';
 6
 7-- 3. 索引列上使用OR
 8SELECT * FROM users WHERE status = 1 OR role = 'admin';
 9
10-- 4. 索引列上使用!=
11SELECT * FROM users WHERE status != 1;
12
13-- 5. 类型不匹配
14SELECT * FROM users WHERE id = '123'; -- id是int类型

1-- 优化前
2SELECT * FROM users WHERE status = 1;
3
4-- 优化后
5SELECT id, name, email FROM users WHERE status = 1;

1-- 避免全表扫描
2SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

1-- 优化前：子查询
2SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);
3
4-- 优化后：JOIN
5SELECT o.* FROM orders o
6JOIN users u ON o.user_id = u.id
7WHERE u.status = 1;

1-- 使用ROLLUP代替多次GROUP BY
2SELECT category, SUM(amount) FROM orders
3GROUP BY category WITH ROLLUP;

 1# my.cnf
 2[mysqld]
 3# 连接数
 4max_connections = 1000
 5
 6# 查询缓存（MySQL 8.0已移除）
 7query_cache_type = OFF
 8
 9# 缓冲池大小（建议设置为内存的70%-80%）
10innodb_buffer_pool_size = 8G
11
12# 日志文件大小
13innodb_log_file_size = 1G
14
15# 排序缓冲
16sort_buffer_size = 2M
17
18# 连接缓冲
19join_buffer_size = 2M

1-- 根据用户ID分表
2CREATE TABLE orders_0 (LIKE orders);
3CREATE TABLE orders_1 (LIKE orders);
4CREATE TABLE orders_2 (LIKE orders);
5CREATE TABLE orders_3 (LIKE orders);
6
7-- 根据ID路由
8SELECT * FROM orders_{id % 4} WHERE id = 12345;

1-- 用户表拆分
2CREATE TABLE users_basic (id, username, email, password);
3CREATE TABLE users_profile (id, nickname, avatar, bio);
4CREATE TABLE users_settings (id, theme, language, timezone);

 1def get_user(user_id):
 2    # 先从缓存获取
 3    cache_key = f"user:{user_id}"
 4    user = redis.get(cache_key)
 5    
 6    if user:
 7        return json.loads(user)
 8    
 9    # 缓存不存在，从数据库获取
10    user = db.query("SELECT * FROM users WHERE id = %s", user_id)
11    
12    if user:
13        # 写入缓存
14        redis.set(cache_key, json.dumps(user), ex=3600)
15    
16    return user

 1# 使用互斥锁防止缓存击穿
 2def get_user(user_id):
 3    cache_key = f"user:{user_id}"
 4    user = redis.get(cache_key)
 5    
 6    if user:
 7        return json.loads(user)
 8    
 9    # 使用锁
10    lock_key = f"lock:user:{user_id}"
11    if redis.set(lock_key, "1", nx=True, ex=30):
12        try:
13            user = db.query("SELECT * FROM users WHERE id = %s", user_id)
14            if user:
15                redis.set(cache_key, json.dumps(user), ex=3600)
16        finally:
17            redis.delete(lock_key)
18    
19    return redis.get(cache_key)

1# 设置随机过期时间防止缓存雪崩
2def set_cache(key, value, base_expire=3600):
3    # 添加随机偏移
4    expire = base_expire + random.randint(0, 300)
5    redis.set(key, value, ex=expire)

 1用户请求
 2    ↓
 3本地缓存（进程内）
 4    ↓ 未命中
 5Redis缓存
 6    ↓ 未命中
 7数据库
 8    ↓
 9写入Redis缓存
10    ↓
11写入本地缓存

1from functools import lru_cache
2
3@lru_cache(maxsize=1000)
4def get_config(key):
5    return db.query("SELECT value FROM config WHERE key = %s", key)

 1http {
 2    upstream backend {
 3        server server1.example.com;
 4        server server2.example.com;
 5        server server3.example.com;
 6    }
 7
 8    server {
 9        listen 80;
10
11        location / {
12            proxy_pass http://backend;
13        }
14    }
15}

1单体架构
2    ↓
3微服务架构
4    ├── 用户服务
5    ├── 订单服务
6    ├── 商品服务
7    ├── 支付服务
8    └── 通知服务

 1# 使用消息队列异步处理
 2def create_order(order_data):
 3    # 1. 创建订单
 4    order = db.create(order_data)
 5    
 6    # 2. 发送消息到队列
 7    rabbitmq.publish('order.created', order.id)
 8    
 9    # 3. 返回响应
10    return order
11
12# 消费者处理
13def handle_order_created(order_id):
14    # 发送邮件通知
15    send_email(order_id)
16    
17    # 更新库存
18    update_inventory(order_id)
19    
20    # 记录日志
21    log_order(order_id)

1用户请求
2    ↓
3CDN节点（缓存静态资源）
4    ↓ 未命中
5源服务器
6    ↓
7返回资源
8    ↓
9CDN缓存资源

1-- 慢查询：查询用户订单列表
2SELECT o.id, o.order_no, o.amount, u.name, u.email
3FROM orders o, users u
4WHERE o.user_id = u.id
5AND o.status = 1
6AND o.created_at >= '2026-01-01'
7ORDER BY o.created_at DESC
8LIMIT 100;