當使用了多個資料庫來提供服務時，最為關鍵的點是如何讓每一個資料庫比較均勻的承擔壓力，而不至於其中的某些資料庫壓力過大，某些資料庫沒什麼壓力。這其中的關鍵點之一就是拆分鍵的設計 ...
背景
最近在做項目的時候，有一個場景需要進行分頁查詢某個分類下的商品信息，發現對這塊不熟悉，故總結一下01 表結構
CREATE TABLE `tb_goods` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`item_id` int(11) NOT NULL COMMENT '商品id',
`category_id` int(11) NOT NULL COMMENT '分類id',
`spu_id` varchar(30) NOT NULL COMMENT 'spu_id',
`sku_id` varchar(30) NOT NULL COMMENT 'sku_id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
`last_update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uniq_item_id`(`item_id`),
UNIQUE KEY `idx_uniq_category_id_spu_id_sku_id`(`category_id`,`spu_id`,`sku_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品信息表';
插入數據
INSERT INTO `tb_goods` (`id`, `item_id`, `category_id`, `spu_id`, `sku_id`, `create_time`, `last_update_time`) VALUES (1, 1001, 106, '1', '1', '2023-08-08 15:03:31', '2023-08-08 15:07:47'), (2, 1002, 106, '2', '2', '2023-08-08 15:03:31', '2023-08-08 15:07:51'), (3, 1003, 106, '12', '21', '2023-08-08 15:03:31', '2023-08-08 15:07:53'), (4, 1004, 106, '13', '101', '2023-08-08 15:03:31', '2023-08-08 15:04:00'), (5, 1005, 106, '113', '131', '2023-08-08 15:03:31', '2023-08-08 15:04:00'), (6, 1006, 191, '173', '91', '2023-08-08 15:03:31', '2023-08-08 15:07:58'), (7, 1007, 106, '27', '97', '2023-08-08 15:03:31', '2023-08-08 15:04:00'), (8, 1008, 106, '46', '123', '2023-08-08 15:03:31', '2023-08-08 15:04:00'), (9, 1009, 106, '311', '1231', '2023-08-08 15:03:31', '2023-08-08 15:04:00'), (10, 1010, 106, '83', '81', '2023-08-08 15:03:31', '2023-08-08 15:04:00');
02 方案
2.1 使用 limit pageId,size
一開始的方案是使用 limit pageId,size pageId 表示開始的位置，size 表示分頁的數量，將商品的分類放在 where 條件里，具體的 sql 如下 select * from tb_goods where category_id = 106 limit 1,4 explain 了一下，發現有用到索引，線上應該很快的
2.2 使用 id 作為條件的一部分
將 id 作為條件的一部分，每次直接從指定的位置開始掃描，掃描到指定的數量後截止select * from tb_goods where category_id = 106 and id > 0 limit 4
同樣 `explain` 試試，目前只用到了
idx_uniq_category_id_spu_id_sku_id 這個索引且數據量很小，看不出區別
通過這次查詢的結果還發現了一個問題， id 為 2 的數據不見瞭如下圖所示，而在表裡是有這條數據的
查詢結果圖
表數據圖 這是什麼原因導致數據丟失了呢？ 查看
explain 中結果發現，這次查詢用到的索引是 idx_uniq_category_id_spu_id_sku_id
，而此索引 id 為 2 的列是排在後面的，將 limit 的數量調為 7，結果如下圖所示：
發現
id 為 2 的列剛好排在 id 為 4 的列後面，問題找到了 limit 的值不夠大，導致部分列沒有找到
將 limit 的值設置大一點是不是就可以了？
不行的，limit 是每次要進行查詢的數量，業務方下次傳入的起始位置是上一次的最大 id，這樣就會導致 id 為 2 的數據丟失了。
如何解決這個問題呢？
添加排序規則， 讓 limit 每次返回的數據相當從一個已經排序好的數據集中取固定的數量，對於此需求，每次返回的是id，在排序規則中 添加 id 就是可以了
select * from tb_goods where category_id = 106 and id > 0 order by id limit 4
id 為 2 的列可以查詢到了
03 limit 配合 order by 使用的坑
在查閱資料的時候發現 在 mysql 5.6 版本以上，對於 limit 語法，`order by` 排序 帶 limit 和不帶 limit， order by 排序返回的排序後的列表可能是不同的，如果需要前後返回的數據一致，需要在 order by 中添加能夠確定排序結果的列，比如id
3.1 重現 limit 配合 order by 使用 的問題
創建表CREATE TABLE `ratings` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`category` int(11) NOT NULL COMMENT '分類id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='等級表';
插入數據
INSERT INTO `ratings` (`id`, `category`) VALUES (1, 1), (2, 3), (3, 2), (4, 2), (5, 1), (6, 2), (7, 3), (8, 3), (9, 2), (10, 1);
第一次查詢不帶
limit ,具體 sql 如下：
select * from ratings order by category ;
查詢結果如下：
第二次查詢帶
limit ,具體 sql 如下：
select * from ratings order by category limit 5;查詢結果如下：
觀察上圖可以發現，id 為 10 和 id 為 5 的列的順序變了
3.2 原因
在MySQL 5.6的版本上，優化器在遇到order by limit語句的時候，做了一個優化，即使用了priority queue
使用 priority queue 的目的，就是在不能使用索引有序性的時候，如果要排序，並且使用了limit n，那麼只需要在排序的過程中，保留n條記錄即可， 因為 priority queue 使用了堆排序的排序方法，而堆排序是一個不穩定的排序方法，也就是相同的值可能排序出來的結果和讀出來的數據順序不一致
