七、数据库监控与诊断

7.1 慢查询日志配置

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 使用pt-query-digest分析慢查询
-- pt-query-digest /var/log/mysql/slow.log > slow_report.txt

7.2 实时监控SQL

-- 查看当前正在执行的SQL
SELECT 
    id,
    user,
    db,
    command,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 查看事务运行情况
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_sec,
    trx_query
FROM information_schema.innodb_trx
ORDER BY duration_sec DESC;

-- 查看锁等待
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    TIMESTAMPDIFF(SECOND, r.trx_started, NOW()) wait_sec
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id;

7.3 表状态分析

-- 查看表大小
SELECT 
    table_schema,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    table_rows,
    ROUND(data_length / table_rows, 2) AS avg_row_size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
ORDER BY size_mb DESC
LIMIT 20;

-- 查看索引使用统计
SELECT 
    index_name,
    cardinality,
    (cardinality / table_rows) * 100 AS selectivity
FROM information_schema.statistics
WHERE table_name = 'orders'
ORDER BY selectivity DESC;

八、NoSQL与NewSQL选型

8.1 何时使用NoSQL

8.2 缓存策略实战

// 缓存穿透、击穿、雪崩处理

@Component
public class CacheService {

    // 1. 缓存穿透（查询不存在的数据）：布隆过滤器 + 缓存空值
    @Autowired
    private BloomFilter bloomFilter;

    public Order getOrder(Long id) {
        // 布隆过滤器过滤不存在ID
        if (!bloomFilter.mightContain(id)) {
            return null;
        }

        String cacheKey = "order:" + id;
        Order order = redisTemplate.opsForValue().get(cacheKey);
        if (order != null) {
            // 空值标记
            if (order.getId() == -1L) {
                return null;
            }
            return order;
        }

        order = orderMapper.selectById(id);
        if (order == null) {
            // 缓存空值，过期时间短（5分钟）
            redisTemplate.opsForValue().set(cacheKey, new Order(-1L), 300, TimeUnit.SECONDS);
            return null;
        }

        redisTemplate.opsForValue().set(cacheKey, order, 3600, TimeUnit.SECONDS);
        return order;
    }

    // 2. 缓存击穿（热点key过期）：互斥锁
    public Order getOrderWithMutex(Long id) {
        String cacheKey = "order:" + id;
        Order order = redisTemplate.opsForValue().get(cacheKey);
        if (order != null) {
            return order;
        }

        // 尝试获取分布式锁
        String lockKey = "lock:order:" + id;
        Boolean locked = redisTemplate.opsForValue()
            .setIfAbsent(lockKey, "1", 10, TimeUnit.SECONDS);

        if (Boolean.TRUE.equals(locked)) {
            try {
                // 双重检查
                order = redisTemplate.opsForValue().get(cacheKey);
                if (order != null) {
                    return order;
                }
                order = orderMapper.selectById(id);
                redisTemplate.opsForValue().set(cacheKey, order, 3600, TimeUnit.SECONDS);
                return order;
            } finally {
                redisTemplate.delete(lockKey);
            }
        } else {
            // 等待其他线程加载
            Thread.sleep(50);
            return getOrderWithMutex(id);
        }
    }

    // 3. 缓存雪崩：过期时间加随机值
    public void cacheOrder(Order order) {
        String cacheKey = "order:" + order.getId();
        int baseTtl = 3600;
        int randomTtl = ThreadLocalRandom.current().nextInt(300, 600);
        redisTemplate.opsForValue().set(cacheKey, order, baseTtl + randomTtl, TimeUnit.SECONDS);
    }
}

8.3 分库分表 vs TiDB（NewSQL）

九、实战案例：千万级订单系统优化

9.1 优化前状态
数据量：订单表5000万行

QPS：峰值3000

平均响应：800ms

慢查询：每天2000+条

9.2 优化步骤

-- Step 1: 索引优化（去除冗余，添加必要）
-- 删除未使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE table_name = 'orders';
DROP INDEX idx_unused ON orders;

-- 添加复合索引覆盖80%查询
CREATE INDEX idx_user_time_status ON orders(user_id, create_time, status);
CREATE INDEX idx_shop_time ON orders(shop_id, create_time);

-- Step 2: 冷热数据分离
-- 热数据（最近3个月）保留在主表
-- 冷数据（>3个月）迁移到history表
INSERT INTO orders_history SELECT * FROM orders WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);
DELETE FROM orders WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);
-- 使用分区表简化
ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    ...
);

-- Step 3: 读写分离
-- 写操作主库，读操作从库
@RoutingWith(DataSourceType.SLAVE)
public List<Order> getOrdersByUser(Long userId) {
    return orderMapper.selectByUserId(userId);
}

-- Step 4: 应用层缓存
@Cacheable(value = "order", key = "#id", unless = "#result == null")
public Order getOrder(Long id) {
    return orderMapper.selectById(id);
}

-- Step 5: SQL改写
-- 原SQL（使用子查询，性能差）
SELECT * FROM orders 
WHERE user_id IN (SELECT user_id FROM users WHERE vip_level = 5)
ORDER BY create_time DESC
LIMIT 100;

-- 改写为JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.vip_level = 5
ORDER BY o.create_time DESC
LIMIT 100;

记住数据库优化的黄金法则：硬件解决不了的问题，架构可以；架构解决不了的问题，业务可以。 始终保持对数据的敬畏之心，每一次变更都要有回滚预案。
来源：
http://fndvx.cn/