-- 假設(shè) users 表有 email 列，并在 email 上建了普通索引
CREATE INDEX idx_users_email ON users(email);

-- 查詢時使用 UPPER 函數(shù)
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- 創(chuàng)建基于 UPPER(email) 的函數(shù)索引
CREATE INDEX idx_users_email_upper ON users(UPPER(email));

-- 現(xiàn)在查詢可以命中索引
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

// UserRepository.java
public interface UserRepository extends JpaRepository<User, Long> {
    // 使用 @Query 注解顯式調(diào)用函數(shù)索引
    @Query("SELECT u FROM User u WHERE UPPER(u.email) = UPPER(:email)")
    Optional<User> findByEmailIgnoreCase(@Param("email") String email);
}

EXPLAIN (ANalyze, BUFFERS)
SELECT * FROM users WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- 在 product_name 上有索引
CREATE INDEX idx_products_name ON products(product_name);

-- 能用索引
SELECT * FROM products WHERE product_name LIKE 'iPhone%';

-- 不能用索引
SELECT * FROM products WHERE product_name LIKE '%Phone';

-- 啟用 pg_trgm 擴(kuò)展
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 創(chuàng)建 GIN 索引（適合高并發(fā)讀）
CREATE INDEX idx_products_name_trgm ON products USING GIN (product_name gin_trgm_ops);

-- 現(xiàn)在以下查詢也能走索引
SELECT * FROM products WHERE product_name LIKE '%Phone%';

<!-- ProductMapper.xml -->
<select id="searchProducts" resultType="Product">
    SELECT * FROM products
    WHERE product_name LIKE CONCAT('%', #{keyword}, '%')
</select>

-- user_id 是 BIGINT 類型，有索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 錯誤：傳入字符串 '123'
SELECT * FROM orders WHERE user_id = '123';  -- 隱式轉(zhuǎn)換為 text → bigint

-- 正確：傳入數(shù)字 123
SELECT * FROM orders WHERE user_id = 123;

// ? 錯誤：使用字符串參數(shù)
String sql = "SELECT * FROM orders WHERE user_id = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, "123"); // 傳入字符串

// ? 正確：使用 Long
stmt.setLong(1, 123L); // 傳入 Long

@Query("SELECT o FROM Order o WHERE o.userId = :userId")
List<Order> findByUserId(@Param("userId") Long userId); // 不要用 String

-- 創(chuàng)建復(fù)合索引
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- ? 可用索引
SELECT * FROM orders WHERE status = 'shipped';

-- ? 可用索引
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2023-01-01';

-- ? 無法使用索引
SELECT * FROM orders WHERE created_at > '2023-01-01';

// 使用 Spring Data JPA 的 Specification
public class OrderSpecs {
    public static Specification<Order> byStatusAndDate(String status, LocalDate date) {
        return (root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            if (status != null) {
                predicates.add(cb.equal(root.get("status"), status));
            }
            if (date != null) {
                predicates.add(cb.greaterThan(root.get("createdAt"), date.atStartOfDay()));
            }
            // 注意：只有 status 有值時，索引才可能被使用
            return cb.and(predicates.toArray(new Predicate[0]));
        };
    }
}

-- 有索引
CREATE INDEX idx_users_status ON users(status);

-- ? 可能不走索引
SELECT * FROM users WHERE status != 'inactive';

-- ? 改寫為 IN 或具體值
SELECT * FROM users WHERE status IN ('active', 'pending');

// ? 不推薦
List<User> users = userRepository.findByStatusNot("inactive");

// ? 推薦：明確列出有效狀態(tài)
List<String> activeStatuses = Arrays.asList("active", "pending", "verified");
List<User> users = userRepository.findByStatusIn(activeStatuses);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);

-- ? 可能不走索引
SELECT * FROM users WHERE email = 'a@example.com' OR phone = '1234567890';

-- ? 每個子查詢都能走索引
SELECT * FROM users WHERE email = 'a@example.com'
UNION
SELECT * FROM users WHERE phone = '1234567890';

<select id="findByEmailOrPhone" resultType="User">
    SELECT * FROM (
        SELECT * FROM users WHERE email = #{email}
        UNION ALL
        SELECT * FROM users WHERE phone = #{phone}
    ) AS combined
</select>

-- 更新單表統(tǒng)計
ANALYZE users;

-- 更新整個數(shù)據(jù)庫
ANALYZE;

-- 查看表的行數(shù)估計是否準(zhǔn)確
SELECT relname, reltuples FROM pg_class WHERE relname = 'users';

-- 查看列的最常見值（MCV）
SELECT attname, most_common_vals FROM pg_stats 
WHERE tablename = 'users' AND attname = 'status';

@Transactional
public void bulkImportUsers(List<User> users) {
    userRepository.saveAll(users);
    
    // 手動觸發(fā) ANALYZE（謹(jǐn)慎使用，生產(chǎn)環(huán)境建議由 DBA 控制）
    jdbcTemplate.execute("ANALYZE users");
}

-- 有索引
CREATE INDEX idx_orders_amount ON orders(amount);

-- ? 無法使用索引
SELECT * FROM orders WHERE amount * 1.1 > 100;

-- ? 改寫為
SELECT * FROM orders WHERE amount > 100 / 1.1;

// Controller 層預(yù)計算
double threshold = 100.0 / 1.1;
List<Order> orders = orderRepository.findByAmountGreaterThan(threshold);

-- 普通索引包含 NULL，體積大
CREATE INDEX idx_users_email ON users(email);

-- 更優(yōu)：只索引非空 email
CREATE INDEX idx_users_email_not_null ON users(email) WHERE email IS NOT NULL;

-- 查詢非空 email 時效率更高
SELECT * FROM users WHERE email = 'user@example.com';

-- 只索引未完成的訂單
CREATE INDEX idx_orders_pending ON orders(order_id) WHERE status IN ('pending', 'processing');

-- 查詢時自動使用
SELECT * FROM orders WHERE status = 'pending';

public interface OrderRepository extends JpaRepository<Order, Long> {
    // Spring Data JPA 會自動使用部分索引（如果存在）
    List<Order> findByStatus(String status);
}

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- 更詳細(xì)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = 'user@example.com';

@Test
void testIndexUsage() {
    String explainSql = "EXPLAIN (ANALYZE, BUFFERS) " +
        "SELECT * FROM users WHERE email = 'test@example.com'";
    List<String> plan = jdbcTemplate.queryForList(explainSql, String.class);
    plan.forEach(System.out::println);
}

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename, indexname;