// MySQL連接池配置示例
@Configuration
publicclass MySQLConfig {
    
    @Bean
    public DataSource mysqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
        config.setUsername("root");
        config.setPassword("password");
        config.setMaximumPoolSize(100); // 連接數(shù)有限
        config.setConnectionTimeout(30000);
        returnnew HikariDataSource(config);
    }
}

// PostgreSQL連接池配置
@Configuration
publicclass PostgreSQLConfig {
    
    @Bean
    public DataSource postgresqlDataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/test");
        config.setUsername("postgres");
        config.setPassword("password");
        config.setMaximumPoolSize(200); // 支持更多連接
        config.setConnectionTimeout(30000);
        returnnew HikariDataSource(config);
    }
}

-- MySQL中，以下查詢(xún)無(wú)法有效使用索引
SELECT * FROM products 
WHERE tags LIKE '%electronics%' 
  AND price BETWEEN 100 AND 500 
  AND JSON_EXTRACT(attributes, '$.color') = 'red';

-- 1. B-Tree索引（基礎(chǔ)索引）
CREATEINDEX idx_account_time ON transaction_records(account_id, transaction_time);

-- 2. GIN索引（用于JSON、數(shù)組等復(fù)雜數(shù)據(jù)類(lèi)型）
CREATEINDEX idx_product_tags ON products USING GIN(tags);
CREATEINDEX idx_product_attributes ON products USING GIN(attributes);

-- 3. BRIN索引（用于時(shí)間序列數(shù)據(jù)）
CREATEINDEX idx_transaction_time_brin ON transaction_records USING BRIN(transaction_time);

-- 4. 部分索引（只索引部分?jǐn)?shù)據(jù)）
CREATEINDEX idx_active_users ONusers(user_id) WHEREstatus = 'ACTIVE';

-- PostgreSQL中，復(fù)雜的JSON查詢(xún)也能高效執(zhí)行
SELECT * FROM products 
WHERE tags @> ARRAY['electronics'] 
  AND price BETWEEN 100 AND 500 
  AND attributes @> '{"color": "red"}'::jsonb;

-- 這個(gè)查詢(xún)可以同時(shí)利用多個(gè)索引，并通過(guò)位圖掃描合并結(jié)果

-- MySQL中，這個(gè)復(fù)雜查詢(xún)需要多次子查詢(xún)，性能很差
SELECT
    u.user_id,
    u.username,
    (SELECTCOUNT(*) FROM orders o WHERE o.user_id = u.user_id) as order_count,
    (SELECTSUM(amount) FROM payments p WHERE p.user_id = u.user_id) as total_payment
FROMusers u
WHERE u.create_time > '2023-01-01'
ORDERBY order_count DESC
LIMIT100;

-- 使用CTE（公共表表達(dá)式）優(yōu)化復(fù)雜查詢(xún)
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    GROUPBY user_id
),
user_payments AS (
    SELECT user_id, SUM(amount) as total_payment
    FROM payments
    GROUPBY user_id
)
SELECT
    u.user_id,
    u.username,
    COALESCE(uo.order_count, 0) as order_count,
    COALESCE(up.total_payment, 0) as total_payment
FROMusers u
LEFTJOIN user_orders uo ON u.user_id = uo.user_id
LEFTJOIN user_payments up ON u.user_id = up.user_id
WHERE u.create_time > '2023-01-01'
ORDERBY uo.order_count DESCNULLSLAST
LIMIT100;

-- MySQL中的JSON操作較為繁瑣
SELECT 
    product_id,
    JSON_EXTRACT(properties, '$.dimensions.length') as length,
    JSON_EXTRACT(properties, '$.dimensions.width') as width
FROM products
WHERE JSON_EXTRACT(properties, '$.category') = 'electronics';

-- 創(chuàng)建包含復(fù)雜數(shù)據(jù)類(lèi)型的表
CREATETABLE products (
    idSERIAL PRIMARY KEY,
    nameVARCHAR(100) NOTNULL,
    price DECIMAL(10,2),
    tags TEXT[], -- 數(shù)組類(lèi)型
    dimensions JSONB, -- 二進(jìn)制JSON
    location POINT, -- 幾何類(lèi)型
    created_at TIMESTAMPTZ DEFAULTNOW()
);

-- 高效的復(fù)雜查詢(xún)
SELECT
    id,
    name,
    dimensions->>'length'aslength,
    dimensions->>'width'as width
FROM products
WHERE tags && ARRAY['electronics'] -- 數(shù)組包含查詢(xún)
AND dimensions @> '{"category": "electronics"}'-- JSON包含查詢(xún)
AND circle(location, 1000) @> point(40.7128, -74.0060); -- 幾何查詢(xún)

// Java中的事務(wù)示例
@Service
@Transactional
public class OrderService {
    
    public void createOrder(Order order) {
        // 高并發(fā)下可能出現(xiàn)鎖等待
        orderRepository.save(order);
        inventoryRepository.decrementStock(order.getProductId(), order.getQuantity());
        paymentRepository.createPayment(order.getOrderId(), order.getAmount());
    }
}

-- PostgreSQL支持更細(xì)粒度的鎖控制
BEGIN;

-- 使用SKIP LOCKED避免鎖等待
SELECT * FROM orders 
WHEREstatus = 'PENDING'
FORUPDATESKIPLOCKED
LIMIT10;

-- 在另一個(gè)會(huì)話中，同樣可以查詢(xún)其他待處理訂單
COMMIT;

// 模擬高并發(fā)訂單處理 - PostgreSQL實(shí)現(xiàn)
@Service
publicclass PostgreSQLOrderService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Transactional
    public void processOrderConcurrently(Order order) {
        // 使用PostgreSQL的特定優(yōu)化
        String sql = """
            WITH stock_update AS (
                UPDATE inventory 
                SET stock = stock - ? 
                WHERE product_id = ? AND stock >= ?
                RETURNING product_id
            ),
            order_insert AS (
                INSERT INTO orders (order_id, user_id, product_id, quantity, status) 
                VALUES (?, ?, ?, ?, 'PROCESSING')
                RETURNING order_id
            )
            SELECT order_id FROM order_insert
            """;
        
        // 執(zhí)行復(fù)雜事務(wù)
        jdbcTemplate.execute(sql);
    }
}

// 兼容性配置示例
@Configuration
publicclass MigrationConfig {
    
    // 使用兼容模式
    @Bean
    public PostgreSQLDialect postgreSQLDialect() {
        returnnew PostgreSQLDialect();
    }
    
    // 數(shù)據(jù)遷移工具配置
    @Bean
    public Flyway flyway() {
        return Flyway.configure()
                .dataSource(dataSource())
                .locations("classpath:db/migration/postgresql")
                .load();
    }
}