-- 假設(shè)我們的查詢(xún)語(yǔ)句
SELECT * FROM product_reviews 
WHERE product_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- 第N頁(yè)的查詢(xún)公式
SELECT * FROM table 
ORDER BY sort_field 
LIMIT (page_num - 1) * page_size, page_size;

@Service
@Transactional(readOnly = true)
public class ProductReviewService {
    @Autowired
    private ProductReviewMapper reviewMapper;
    /**
     * 獲取商品評(píng)論列表（修復(fù)版）
     * @param productId 商品ID
     * @param pageNum 頁(yè)碼（從1開(kāi)始）
     * @param pageSize 每頁(yè)大小
     * @return 分頁(yè)結(jié)果
     */
    public Page<ProductReviewDTO> getProductReviews(Long productId, int pageNum, int pageSize) {
        // 參數(shù)校驗(yàn)
        if (productId == null || productId <= 0) {
            throw new IllegalArgumentException("Invalid product ID");
        }
        if (pageNum <= 0 || pageSize <= 0 || pageSize > 100) {
            throw new IllegalArgumentException("Invalid pagination parameters");
        }
        // 創(chuàng)建分頁(yè)對(duì)象
        Page<ProductReview> page = new Page<>(pageNum, pageSize);
        // 構(gòu)建查詢(xún)條件
        LambdaQueryWrapper<ProductReview> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(ProductReview::getProductId, productId)
                   .eq(ProductReview::getStatus, ReviewStatus.NORMAL.getStatus())
                   // 關(guān)鍵修復(fù)：添加唯一字段作為第二排序條件
                   .orderByDesc(ProductReview::getCreatedAt)
                   .orderByDesc(ProductReview::getReviewId);
        // 執(zhí)行分頁(yè)查詢(xún)
        Page<ProductReview> resultPage = reviewMapper.selectPage(page, queryWrapper);
        // 轉(zhuǎn)換為DTO對(duì)象并補(bǔ)充用戶(hù)信息
        List<ProductReviewDTO> dtoList = resultPage.getRecords().stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
        return new Page<>(resultPage.getCurrent(), resultPage.getSize(), resultPage.getTotal())
                .setRecords(dtoList);
    }
    private ProductReviewDTO convertToDTO(ProductReview review) {
        ProductReviewDTO dto = new ProductReviewDTO();
        dto.setReviewId(review.getReviewId());
        dto.setProductId(review.getProductId());
        dto.setUserId(review.getUserId());
        dto.setRating(review.getRating());
        dto.setReviewText(review.getReviewText());
        dto.setCreatedAt(review.getCreatedAt());
        dto.setHelpfulCount(review.getHelpfulCount());
        // 異步補(bǔ)充用戶(hù)頭像和昵稱(chēng)（實(shí)際項(xiàng)目中可能通過(guò)緩存或RPC調(diào)用）
        UserBasicInfo userInfo = getUserBasicInfo(review.getUserId());
        dto.setUserAvatar(userInfo.getAvatar());
        dto.setUserNickname(userInfo.getNickname());
        return dto;
    }
    /**
     * 獲取用戶(hù)基本信息（簡(jiǎn)化實(shí)現(xiàn)）
     */
    private UserBasicInfo getUserBasicInfo(Long userId) {
        // 實(shí)際項(xiàng)目中這里會(huì)調(diào)用用戶(hù)服務(wù)或查詢(xún)緩存
        return new UserBasicInfo("avatar_url_" + userId, "user_" + userId);
    }
}

-- 第1頁(yè)查詢(xún)
SELECT * FROM product_reviews 
WHERE product_id = 12345 AND status = 1
ORDER BY created_at DESC, review_id DESC 
LIMIT 0, 10;

-- 第2頁(yè)查詢(xún)
SELECT * FROM product_reviews 
WHERE product_id = 12345 AND status = 1
ORDER BY created_at DESC, review_id DESC 
LIMIT 10, 10;

@Service
@Transactional(readOnly = true)
public class CursorProductReviewService {
    @Autowired
    private ProductReviewMapper reviewMapper;
    /**
     * 游標(biāo)分頁(yè)獲取商品評(píng)論
     * @param productId 商品ID
     * @param cursor 游標(biāo)對(duì)象，包含上一頁(yè)最后一條記錄的時(shí)間和ID
     * @param pageSize 頁(yè)面大小
     * @return 評(píng)論列表
     */
    public List<ProductReviewDTO> getProductReviewsByCursor(
            Long productId, 
            ReviewCursor cursor, 
            int pageSize) {
        // 參數(shù)校驗(yàn)
        validateParameters(productId, pageSize);
        // 構(gòu)建查詢(xún)條件
        LambdaQueryWrapper<ProductReview> queryWrapper = buildBaseQuery(productId);
        // 添加游標(biāo)條件
        if (cursor != null) {
            queryWrapper.and(wrapper -> 
                wrapper.lt(ProductReview::getCreatedAt, cursor.getCreatedAt())
                       .or()
                       .apply("created_at = {0} AND review_id < {1}", 
                              cursor.getCreatedAt(), cursor.getReviewId())
            );
        }
        // 排序和限制
        queryWrapper.orderByDesc(ProductReview::getCreatedAt)
                   .orderByDesc(ProductReview::getReviewId)
                   .last("LIMIT " + pageSize);
        // 執(zhí)行查詢(xún)
        List<ProductReview> reviews = reviewMapper.selectList(queryWrapper);
        // 轉(zhuǎn)換為DTO
        return reviews.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }
    /**
     * 構(gòu)建基礎(chǔ)查詢(xún)條件
     */
    private LambdaQueryWrapper<ProductReview> buildBaseQuery(Long productId) {
        LambdaQueryWrapper<ProductReview> wrapper = new LambdaQueryWrapper<>();
        wrapper.eq(ProductReview::getProductId, productId)
               .eq(ProductReview::getStatus, ReviewStatus.NORMAL.getStatus());
        return wrapper;
    }
    /**
     * 驗(yàn)證參數(shù)
     */
    private void validateParameters(Long productId, int pageSize) {
        if (productId == null || productId <= 0) {
            throw new IllegalArgumentException("Invalid product ID");
        }
        if (pageSize <= 0 || pageSize > 50) {
            throw new IllegalArgumentException("Invalid page size");
        }
    }
    /**
     * 游標(biāo)對(duì)象定義
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public static class ReviewCursor {
        private LocalDateTime createdAt;
        private Long reviewId;
        /**
         * 從最后一條評(píng)論創(chuàng)建游標(biāo)
         */
        public static ReviewCursor fromLastReview(ProductReviewDTO lastReview) {
            return new ReviewCursor(lastReview.getCreatedAt(), lastReview.getReviewId());
        }
    }
}

-- 首頁(yè)查詢(xún)
SELECT * FROM product_reviews 
WHERE product_id = 12345 AND status = 1
ORDER BY created_at DESC, review_id DESC 
LIMIT 10;

-- 下一頁(yè)查詢(xún)（假設(shè)上一頁(yè)最后一條記錄：created_at='2024-03-15 14:30:00', review_id=1003）
SELECT * FROM product_reviews 
WHERE product_id = 12345 AND status = 1
  AND (
    created_at < '2024-03-15 14:30:00' 
    OR (created_at = '2024-03-15 14:30:00' AND review_id < 1003)
  )
ORDER BY created_at DESC, review_id DESC 
LIMIT 10;

-- 創(chuàng)建復(fù)合索引（針對(duì)方案一）
CREATE INDEX idx_product_status_created_review 
ON product_reviews (product_id, status, created_at DESC, review_id DESC);

-- 對(duì)于游標(biāo)分頁(yè)，可能需要額外的索引
CREATE INDEX idx_status_created_review 
ON product_reviews (status, created_at DESC, review_id DESC);

/**
 * KEYSET分頁(yè)實(shí)現(xiàn)
 */
public List<ProductReviewDTO> getKeysetReviews(
        Long productId,
        Object[] lastKeyset, // [created_at, review_id]
        int pageSize) {
    StringBuilder sql = new StringBuilder();
    List<Object> params = new ArrayList<>();
    sql.append("SELECT pr.*, u.nickname, u.avatar ");
    sql.append("FROM product_reviews pr ");
    sql.append("LEFT JOIN users u ON pr.user_id = u.user_id ");
    sql.append("WHERE pr.product_id = ? AND pr.status = 1 ");
    params.add(productId);
    if (lastKeyset != null) {
        sql.append("AND (pr.created_at, pr.review_id) < (?, ?) ");
        params.add(lastKeyset[0]); // created_at
        params.add(lastKeyset[1]); // review_id
    }
    sql.append("ORDER BY pr.created_at DESC, pr.review_id DESC LIMIT ?");
    params.add(pageSize);
    // 執(zhí)行查詢(xún)...
    return namedParameterJdbcTemplate.query(sql.toString(), 
            params.toArray(), rowMapper);
}

-- 分析分頁(yè)查詢(xún)的執(zhí)行計(jì)劃
EXPLAIN SELECT * FROM product_reviews 
WHERE product_id = 12345 AND status = 1
ORDER BY created_at DESC, review_id DESC 
LIMIT 10;

// ? 正確的做法：始終包含唯一排序字段
queryWrapper.orderByDesc(ProductReview::getCreatedAt)
           .orderByDesc(ProductReview::getReviewId);
// ? 錯(cuò)誤的做法：僅使用非唯一字段排序
queryWrapper.orderByDesc(ProductReview::getCreatedAt);

-- ? 避免：全表掃描 + filesort
SELECT * FROM product_reviews 
ORDER BY created_at DESC 
LIMIT 10000, 10;

-- ? 優(yōu)化1：使用覆蓋索引
SELECT review_id, product_id, user_id, rating, created_at, helpful_count 
FROM product_reviews 
WHERE product_id = 12345 AND status = 1
ORDER BY created_at DESC, review_id DESC 
LIMIT 10;

-- ? 優(yōu)化2：延遲關(guān)聯(lián)（適用于復(fù)雜查詢(xún)）
SELECT pr.* FROM product_reviews pr
INNER JOIN (
    SELECT review_id FROM product_reviews 
    WHERE product_id = 12345 AND status = 1
    ORDER BY created_at DESC, review_id DESC 
    LIMIT 10000, 10
) tmp ON pr.review_id = tmp.review_id;

@SpringBootTest
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class ProductReviewServiceTest {
    @Autowired
    private ProductReviewService reviewService;
    @Autowired
    private ProductReviewMapper reviewMapper;
    private static final Long TEST_PRODUCT_ID = 99999L;
    @BeforeEach
    void setUp() {
        // 清理測(cè)試數(shù)據(jù)
        reviewMapper.delete(new LambdaQueryWrapper<ProductReview>()
                .eq(ProductReview::getProductId, TEST_PRODUCT_ID));
    }
    @Test
    @Order(1)
    void testPaginationNoDuplicates() {
        // 準(zhǔn)備測(cè)試數(shù)據(jù)：創(chuàng)建多條相同時(shí)間的評(píng)論
        prepareTestDataWithSameTimestamp();
        // 查詢(xún)第1頁(yè)和第2頁(yè)
        Page<ProductReviewDTO> page1 = reviewService.getProductReviews(TEST_PRODUCT_ID, 1, 5);
        Page<ProductReviewDTO> page2 = reviewService.getProductReviews(TEST_PRODUCT_ID, 2, 5);
        // 驗(yàn)證無(wú)重復(fù)數(shù)據(jù)
        Set<Long> page1Ids = page1.getRecords().stream()
                .map(ProductReviewDTO::getReviewId)
                .collect(Collectors.toSet());
        Set<Long> page2Ids = page2.getRecords().stream()
                .map(ProductReviewDTO::getReviewId)
                .collect(Collectors.toSet());
        Set<Long> intersection = new HashSet<>(page1Ids);
        intersection.retainAll(page2Ids);
        assertTrue(intersection.isEmpty(), 
                "分頁(yè)結(jié)果存在重復(fù)數(shù)據(jù): " + intersection);
    }
    @Test
    @Order(2)
    void testPaginationOrderStability() {
        // 多次查詢(xún)同一頁(yè)，驗(yàn)證結(jié)果一致性
        Page<ProductReviewDTO> firstResult = reviewService.getProductReviews(TEST_PRODUCT_ID, 1, 5);
        for (int i = 0; i < 5; i++) {
            Page<ProductReviewDTO> currentResult = reviewService.getProductReviews(TEST_PRODUCT_ID, 1, 5);
            assertEquals(firstResult.getRecords().size(), currentResult.getRecords().size(),
                    "第" + (i+1) + "次查詢(xún)結(jié)果數(shù)量不一致");
            // 驗(yàn)證ID順序一致
            List<Long> firstIds = firstResult.getRecords().stream()
                    .map(ProductReviewDTO::getReviewId)
                    .collect(Collectors.toList());
            List<Long> currentIds = currentResult.getRecords().stream()
                    .map(ProductReviewDTO::getReviewId)
                    .collect(Collectors.toList());
            assertEquals(firstIds, currentIds, "第" + (i+1) + "次查詢(xún)結(jié)果順序不一致");
        }
    }
    @Test
    @Order(3)
    void testEdgeCases() {
        // 測(cè)試邊界情況
        assertThrows(IllegalArgumentException.class, 
                () -> reviewService.getProductReviews(null, 1, 10));
        assertThrows(IllegalArgumentException.class, 
                () -> reviewService.getProductReviews(-1L, 1, 10));
        assertThrows(IllegalArgumentException.class, 
                () -> reviewService.getProductReviews(TEST_PRODUCT_ID, 0, 10));
        assertThrows(IllegalArgumentException.class, 
                () -> reviewService.getProductReviews(TEST_PRODUCT_ID, 1, 0));
        assertThrows(IllegalArgumentException.class, 
                () -> reviewService.getProductReviews(TEST_PRODUCT_ID, 1, 101));
    }
    private void prepareTestDataWithSameTimestamp() {
        // 創(chuàng)建多條具有相同created_at的測(cè)試評(píng)論
        LocalDateTime now = LocalDateTime.now().withNano(0); // 移除納秒部分
        Random random = new Random();
        for (int i = 1; i <= 15; i++) {
            ProductReview review = new ProductReview();
            review.setReviewId(null); // 自增
            review.setProductId(TEST_PRODUCT_ID);
            review.setUserId(1000L + i);
            review.setRating(3 + random.nextInt(3)); // 3-5星
            review.setReviewText("Test review " + i);
            review.setCreatedAt(now); // 相同的時(shí)間戳
            review.setHelpfulCount(random.nextInt(20));
            review.setStatus(ReviewStatus.NORMAL.getStatus());
            reviewMapper.insert(review);
        }
    }
}

ALTER TABLE your_table ADD COLUMN id BIGINT AUTO_INCREMENT PRIMARY KEY FIRST;

SELECT * FROM your_table ORDER BY create_time DESC, ROWID;

queryWrapper.orderByDesc(Entity::getCreateTime)
           .orderByAsc(Entity::getField1)
           .orderByAsc(Entity::getField2);

SELECT *, ROW_NUMBER() OVER (ORDER BY create_time DESC) as rn
FROM your_table
ORDER BY rn
LIMIT 10;

# 查找所有分頁(yè)查詢(xún)中的ORDER BY語(yǔ)句
find src/main/java -name "*.java" -exec grep -l "orderBy.*DESC\|orderBy.*ASC" {} \;

-- 查找可能存在重復(fù)排序字段的表
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'your_database'
  AND DATA_TYPE IN ('datetime', 'timestamp', 'date')
  AND COLUMN_NAME REGEXP 'creat|updat|time';