性能优化 #

性能优化方向
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   查询优化:                                                 │
│   ├── SQL语句优化                                          │
│   ├── 索引使用                                             │
│   └── 执行计划分析                                         │
│                                                             │
│   Schema优化:                                               │
│   ├── 表结构设计                                           │
│   ├── 索引设计                                             │
│   └── 分区设计                                             │
│                                                             │
│   配置优化:                                                 │
│   ├── 内存配置                                             │
│   ├── 并发配置                                             │
│   └── 存储配置                                             │
│                                                             │
│   架构优化:                                                 │
│   ├── 节点数量                                             │
│   ├── 数据分布                                             │
│   └── 多区域部署                                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 执行计划输出解读
EXPLAIN SELECT u.name, o.order_no
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = 'test@example.com';

-- 输出示例:
--        • hash join
--        │
--        ├── • scan
--        │     table: users@idx_email
--        │     spans: [/'test@example.com' - /'test@example.com']
--        │
--        └── • scan
--              table: orders@idx_user_id

-- 1. 避免 SELECT *
-- ✗ 不推荐
SELECT * FROM users;
-- ✓ 推荐
SELECT id, name, email FROM users;

-- 2. 使用索引列过滤
-- 确保索引列在 WHERE 条件中
SELECT * FROM users WHERE email = 'test@example.com';

-- 3. 避免函数导致索引失效
-- ✗ 不推荐
SELECT * FROM users WHERE UPPER(email) = 'TEST@EXAMPLE.COM';
-- ✓ 推荐
SELECT * FROM users WHERE email = 'test@example.com';

-- 4. 使用覆盖索引
CREATE INDEX idx_user_email_name ON users(email, name);
SELECT email, name FROM users WHERE email = 'test@example.com';

-- 5. 批量操作
-- ✗ 不推荐
INSERT INTO users (name) VALUES ('a');
INSERT INTO users (name) VALUES ('b');
INSERT INTO users (name) VALUES ('c');
-- ✓ 推荐
INSERT INTO users (name) VALUES ('a'), ('b'), ('c');

-- 6. 使用 LIMIT
SELECT * FROM users LIMIT 100;

-- 7. 避免 OR
-- ✗ 不推荐
SELECT * FROM orders WHERE user_id = 1 OR amount > 100;
-- ✓ 推荐
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE amount > 100;

-- 1. 确保连接列有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 2. 小表驱动大表
-- 让结果集小的表作为驱动表

-- 3. 使用覆盖索引减少回表
CREATE INDEX idx_user_order ON orders(user_id, order_no, amount);
SELECT user_id, order_no, amount FROM orders WHERE user_id = 1;

-- 4. 避免笛卡尔积
-- 确保所有表都有连接条件

-- 1. 选择性高的列适合索引
-- 唯一值多的列
CREATE INDEX idx_email ON users(email);

-- 2. 复合索引顺序
-- 等值条件在前，范围条件在后
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 3. 覆盖索引
-- 索引包含所有查询列
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT user_id, status, amount FROM orders WHERE user_id = 1;

-- 4. 部分索引
-- 只索引需要的行
CREATE INDEX idx_active ON users(email) WHERE status = 'active';

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查找未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%';

-- 查看索引大小
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 1. 选择合适的主键
-- 推荐: UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100)
);

-- 不推荐: 自增ID (热点问题)
CREATE TABLE users_bad (
    id BIGINT PRIMARY KEY DEFAULT unique_rowid(),
    name VARCHAR(100)
);

-- 2. 选择合适的数据类型
-- 使用最小的满足需求的类型
CREATE TABLE products (
    id UUID PRIMARY KEY,
    name VARCHAR(100),        -- 不是 TEXT
    price DECIMAL(10, 2),     -- 不是 FLOAT
    stock INT,                -- 不是 BIGINT
    status SMALLINT           -- 状态码用 SMALLINT
);

-- 3. 避免过多列
-- 宽表性能差，考虑垂直拆分

-- 4. 使用分区
CREATE TABLE logs (
    id UUID PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- 适当反范式，减少 JOIN

-- 范式设计
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

-- 反范式设计 (冗余用户名)
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID,
    user_name VARCHAR(100),  -- 冗余字段
    amount DECIMAL(10, 2),
    created_at TIMESTAMP
);

-- 查询时不需要 JOIN
SELECT id, user_name, amount FROM orders WHERE user_id = 1;

# 启动参数
cockroach start \
    --cache=8GB \           # 缓存大小 (总内存的 50%)
    --max-sql-memory=4GB    # SQL内存 (总内存的 25%)

-- 查看集群设置
SHOW ALL CLUSTER SETTINGS;

-- 调整设置
SET CLUSTER SETTING sql.defaults.distsql = 'on';
SET CLUSTER SETTING kv.raft_log.disable_synchronization_unsafe = 'false';

-- 并行查询
SET CLUSTER SETTING sql.distsql.temp_storage.workmem = '512MiB';

-- 调整Range大小
ALTER RANGE default CONFIGURE ZONE USING
    range_min_bytes = 134217728,   -- 128MB
    range_max_bytes = 536870912;   -- 512MB

-- 调整副本数
ALTER DATABASE mydb CONFIGURE ZONE USING
    num_replicas = 5;

-- 调整GC时间
ALTER DATABASE mydb CONFIGURE ZONE USING
    gc.ttlseconds = 3600;  -- 1小时

-- 查看慢查询
SELECT 
    query,
    count,
    mean_latency,
    max_latency
FROM crdb_internal.node_statement_statistics
ORDER BY mean_latency DESC
LIMIT 10;

-- 查看事务统计
SELECT * FROM crdb_internal.cluster_transactions;

-- 查看节点状态
SELECT * FROM crdb_internal.kv_node_status;

-- 查看Range统计
SELECT 
    range_id,
    round(statistics->>'key_bytes'::numeric / 1024 / 1024, 2) AS size_mb
FROM crdb_internal.ranges
ORDER BY size_mb DESC
LIMIT 10;

-- 查看锁等待
SELECT * FROM crdb_internal.cluster_locks;

-- 查看阻塞的事务
SELECT 
    id,
    status,
    num_retries,
    last_error
FROM crdb_internal.cluster_transactions
WHERE num_retries > 0;

-- 查看热点Range
SELECT 
    range_id,
    lease_holder,
    ROUND(statistics->>'writes_per_second'::numeric, 2) AS writes_per_sec
FROM crdb_internal.ranges
ORDER BY writes_per_sec DESC
LIMIT 10;

性能优化清单
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   Schema设计:                                               │
│   □ 使用UUID主键                                           │
│   □ 选择合适的数据类型                                     │
│   □ 适当反范式                                             │
│   □ 使用分区                                               │
│                                                             │
│   索引优化:                                                 │
│   □ 为查询条件创建索引                                     │
│   □ 使用复合索引                                           │
│   □ 使用覆盖索引                                           │
│   □ 定期检查未使用索引                                     │
│                                                             │
│   查询优化:                                                 │
│   □ 避免 SELECT *                                          │
│   □ 使用批量操作                                           │
│   □ 使用 LIMIT                                             │
│   □ 分析执行计划                                           │
│                                                             │
│   配置优化:                                                 │
│   □ 合理配置内存                                           │
│   □ 调整Zone设置                                           │
│   □ 监控集群状态                                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘