性能优化 #

TiDB 性能优化方向
┌─────────────────────────────────────────────────────────────┐
│                                                             │
│   SQL层面                                                   │
│   ├── 查询优化                                             │
│   ├── 索引优化                                             │
│   ├── 表结构优化                                           │
│   └── 事务优化                                             │
│                                                             │
│   系统层面                                                   │
│   ├── 配置调优                                             │
│   ├── 资源配置                                             │
│   ├── 并发控制                                             │
│   └── 内存管理                                             │
│                                                             │
│   架构层面                                                   │
│   ├── 集群规划                                             │
│   ├── 数据分布                                             │
│   ├── 读写分离                                             │
│   └── HTAP优化                                             │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 1;

-- 详细执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- 分析慢查询
SELECT 
    Query_time,
    Mem_max,
    Query,
    Digest
FROM INFORMATION_SCHEMA.SLOW_QUERY
ORDER BY Query_time DESC
LIMIT 10;

-- 优化1: 避免全表扫描
-- ✗ 不推荐
SELECT * FROM orders;
-- ✓ 推荐
SELECT id, user_id, amount FROM orders LIMIT 1000;

-- 优化2: 使用索引
-- ✗ 不推荐
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- ✓ 推荐
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 优化3: 避免SELECT *
-- ✗ 不推荐
SELECT * FROM orders WHERE user_id = 1;
-- ✓ 推荐
SELECT id, user_id, amount FROM orders WHERE user_id = 1;

-- 优化4: 使用覆盖索引
CREATE INDEX idx_user_amount ON orders(user_id, amount);
SELECT user_id, amount FROM orders WHERE user_id = 1;

-- 优化5: 分页优化
-- ✗ 不推荐
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 1000000;
-- ✓ 推荐
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 强制使用索引
SELECT /*+ USE_INDEX(orders idx_user_id) */ 
* FROM orders WHERE user_id = 1;

-- 强制Join方式
SELECT /*+ HASH_JOIN(orders, users) */ 
o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 设置并行度
SELECT /*+ PARALLEL(8) */ 
* FROM large_table;

-- 内存限制
SELECT /*+ MEMORY_QUOTA(1 GB) */ 
* FROM large_table GROUP BY col1;

-- 选择性高的列建索引
SELECT 
    COUNT(DISTINCT user_id) / COUNT(*) AS selectivity
FROM orders;
-- selectivity 接近1，适合建索引

-- 复合索引顺序
-- 查询模式: WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    ROWS_READ
FROM INFORMATION_SCHEMA.TIDB_INDEX_USAGE
ORDER BY ROWS_READ DESC;

-- 查找未使用的索引
SELECT 
    s.TABLE_NAME,
    s.INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN INFORMATION_SCHEMA.TIDB_INDEX_USAGE u
    ON s.TABLE_SCHEMA = u.TABLE_SCHEMA
    AND s.TABLE_NAME = u.TABLE_NAME
    AND s.INDEX_NAME = u.INDEX_NAME
WHERE s.NON_UNIQUE = 1
  AND u.ROWS_READ IS NULL;

-- 并发配置
SET SESSION tidb_distsql_scan_concurrency = 15;
SET SESSION tidb_index_lookup_concurrency = 15;
SET SESSION tidb_hash_join_concurrency = 5;

-- 内存配置
SET SESSION tidb_mem_quota_query = 1073741824;  -- 1GB

-- MPP配置
SET SESSION tidb_allow_mpp = ON;
SET SESSION tidb_enforce_mpp = ON;
SET SESSION tidb_max_tiflash_threads = 4;

# tikv.toml
[raftstore]
sync-log = true
capacity = "100GB"

[rocksdb]
max-open-files = 4096
max-background-jobs = 8

[rocksdb.defaultcf]
block-size = "64KB"
write-buffer-size = "128MB"

[coprocessor]
region-max-size = "144MB"
region-split-size = "96MB"

# pd.toml
[schedule]
max-replicas = 3
leader-schedule-limit = 4
region-schedule-limit = 2048
hot-region-schedule-limit = 4

-- 大事务拆分
-- ✗ 不推荐
BEGIN;
UPDATE orders SET status = 'processed' WHERE status = 'pending';
COMMIT;

-- ✓ 推荐: 分批处理
BEGIN;
UPDATE orders SET status = 'processed' 
WHERE status = 'pending' LIMIT 1000;
COMMIT;

-- 高冲突场景使用悲观事务
SET SESSION tidb_txn_mode = 'pessimistic';

-- 低冲突场景使用乐观事务
SET SESSION tidb_txn_mode = 'optimistic';

-- 查看慢查询
SELECT * FROM INFORMATION_SCHEMA.SLOW_QUERY 
WHERE Time > NOW() - INTERVAL 1 HOUR
ORDER BY Query_time DESC;

-- 查看正在执行的SQL
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 
WHERE TIME > 5;

-- 查看统计信息
SHOW STATS_META WHERE table_name = 'orders';

-- 分析执行计划
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

-- 查看统计信息状态
SHOW STATS_HEALTHY;

-- 更新统计信息
ANALYZE TABLE orders;