PolarDB性能调优实战：高并发场景下QPS提升300%的五个关键策略

// 应用层连接池优化配置（Spring Boot + HikariCP）
@Configuration
public class DataSourceConfig {
    
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariDataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        // 核心参数：根据服务实例数动态计算
        int maxPoolSize = (int) Math.min(
            Runtime.getRuntime().availableProcessors() * 2, 
            20 // 单实例最大连接数
        );
        dataSource.setMaximumPoolSize(maxPoolSize);
        dataSource.setMinimumIdle(maxPoolSize / 2);
        // 关键：设置连接等待超时，避免线程无限等待
        dataSource.setConnectionTimeout(500); // 500ms
        dataSource.setValidationTimeout(500);
        // 启用连接泄漏检测
        dataSource.setLeakDetectionThreshold(30000); // 30秒
        return dataSource;
    }
}

# 阿里云PolarDB控制台配置DBProxy
1. 开启数据库代理功能
2. 配置连接池参数：
   - 连接池模式：会话级连接池
   - 最大连接数：设置为PolarDB实例连接数的80%
   - 空闲连接回收：300秒
3. 启用事务拆分功能，减少长事务对连接的占用

UPDATE inventory SET stock = stock - 1 
WHERE product_id = 123 AND stock > 0;

// 热点数据更新优化方案
@Service
public class InventoryService {
    
    @Autowired
    private RedisTemplate<String, Integer> redisTemplate;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // 本地缓存（Guava Cache）
    private LoadingCache<Long, AtomicInteger> localCache = CacheBuilder.newBuilder()
        .maximumSize(1000)
        .expireAfterWrite(5, TimeUnit.SECONDS)
        .build(new CacheLoader<Long, AtomicInteger>() {
            @Override
            public AtomicInteger load(Long productId) {
                return new AtomicInteger(getStockFromDB(productId));
            }
        });
    
    public boolean deductStock(Long productId, int quantity) {
        // 1. 尝试本地缓存扣减（无锁操作）
        try {
            AtomicInteger stock = localCache.get(productId);
            int current = stock.get();
            if (current >= quantity && stock.compareAndSet(current, current - quantity)) {
                return true;
            }
        } catch (ExecutionException e) {
            // ignore
        }
        
        // 2. 本地缓存失败，尝试Redis原子操作
        String key = "inventory:" + productId;
        Long result = redisTemplate.execute(
            (RedisCallback<Long>) connection -> 
                connection.getNativeConnection().decrBy(key.getBytes(), quantity)
        );
        
        if (result != null && result >= 0) {
            return true;
        } else if (result != null) {
            // 库存不足，回滚Redis操作
            redisTemplate.opsForValue().increment(key, quantity);
            return false;
        }
        
        // 3. Redis不可用，走数据库（最后防线）
        return deductStockFromDB(productId, quantity);
    }
    
    private boolean deductStockFromDB(Long productId, int quantity) {
        int updated = jdbcTemplate.update(
            "UPDATE inventory SET stock = stock - ? WHERE product_id = ? AND stock >= ?",
            quantity, productId, quantity
        );
        return updated > 0;
    }
}

# PolarDB参数组优化
innodb_buffer_pool_size = 70% of total memory
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 2  # 平衡性能与持久性
innodb_thread_concurrency = 0       # 允许自动调整

// 基于读写分离的智能路由策略
public class SmartRoutingDataSource extends AbstractRoutingDataSource {
    
    private final ThreadLocal<ReadStrategy> readStrategyHolder = new ThreadLocal<>();
    
    @Override
    protected Object determineCurrentLookupKey() {
        ReadStrategy strategy = readStrategyHolder.get();
        if (strategy == null) {
            return "master"; // 默认走主库
        }
        
        // 根据策略和当前延迟决定数据源
        double slaveDelay = getSlaveDelay();
        if (strategy == ReadStrategy.STRONG && slaveDelay > 0.5) {
            return "master"; // 强一致性要求，延迟超过500ms走主库
        } else if (strategy == ReadStrategy.EVENTUAL && slaveDelay > 2.0) {
            return "master"; // 最终一致性，但延迟过大也走主库
        }
        return "slave";
    }
    
    public void setReadStrategy(ReadStrategy strategy) {
        readStrategyHolder.set(strategy);
    }
    
    private double getSlaveDelay() {
        // 通过SHOW SLAVE STATUS获取延迟
        // 或使用阿里云PolarDB提供的监控API
        return 0.8; // 示例值
    }
    
    public enum ReadStrategy {
        MASTER,      // 强制主库
        STRONG,      // 强一致性（允许小延迟）
        EVENTUAL,    // 最终一致性
        BALANCED     // 平衡模式（默认）
    }
}
// 使用示例
@Transactional(readOnly = true)
public UserOrder getOrderDetails(Long orderId) {
    // 设置读策略为强一致性
    dataSource.setReadStrategy(ReadStrategy.STRONG);
    return orderMapper.selectOrder(orderId);
}

# PolarDB复制优化参数
binlog_row_image = minimal
sync_binlog = 1000
innodb_flush_log_at_trx_commit = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16

-- 使用PolarDB的性能视图定位慢查询
SELECT * FROM information_schema.slow_query 
WHERE start_time > NOW() - INTERVAL 1 HOUR
ORDER BY query_time DESC 
LIMIT 20;

-- 使用EXPLAIN分析关键查询
EXPLAIN SELECT * FROM orders 
WHERE user_id = 123 
  AND order_status = 'PAID'
  AND create_time > '2023-01-01'
ORDER BY create_time DESC 
LIMIT 20;

-- 查看DAS索引建议（通过控制台或API）
SELECT * FROM das_recommend_index 
WHERE db_name = 'your_db' AND table_name = 'orders';

SELECT product_name, price, quantity 
FROM order_items 
WHERE order_id = 12345;

// 错误示例：拼接SQL
String sql = "SELECT * FROM users WHERE id = " + userId;
// 正确示例：参数化查询
String sql = "SELECT * FROM users WHERE id = ?";
jdbcTemplate.query(sql, new Object[]{userId}, ...);

-- 传统分页（大偏移量性能差）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
-- 优化后的分页（基于游标）
SELECT * FROM orders 
WHERE create_time < '2023-06-01 12:00:00' 
ORDER BY create_time DESC 
LIMIT 20;

-- 低效的NOT IN子查询
SELECT * FROM products 
WHERE id NOT IN (SELECT product_id FROM orders WHERE status = 'CANCELLED');
-- 优化为LEFT JOIN
SELECT p.* FROM products p
LEFT JOIN orders o ON p.id = o.product_id AND o.status = 'CANCELLED'
WHERE o.id IS NULL;

-- 开启并行查询（适合分析型查询）
SET SESSION parallel_degree = 4;
SELECT COUNT(*) FROM large_table 
WHERE create_time BETWEEN '2023-01-01' AND '2023-06-01';