千萬級别資料查詢難點

資料庫性能：在分頁查詢中，通常需要跳過前面的資料行，然後擷取指定數量的資料行。對于千萬級别的資料，如果沒有适當的索引或查詢優化，每次查詢都需要掃描大量的資料，導緻查詢性能下降。針對偏移量大導緻查詢速度慢：這可能導緻較慢的查詢響應時間或高負載的資料庫伺服器。
資料排序：通常情況下，分頁查詢需要按照某個字段進行排序，以確定結果的順序是一緻的。對于大規模資料集，進行排序操作可能會對性能造成影響，尤其是如果沒有适當的索引來支援排序操作。
資料一緻性：當進行分頁查詢時，如果有新的資料被插入或舊資料被删除，可能會導緻分頁結果的不一緻性。例如，如果在你查詢的期間有新的資料插入到前面的頁中，而你隻查詢後面的頁，可能會導緻某些資料的丢失或重複。

解決這些難點的方法包括：

使用适當的索引：為查詢語句中涉及到的字段建立适當的索引，以加速資料檢索。尤其是需要進行排序的字段，應該優先考慮建立索引。
查詢優化：編寫高效的查詢語句，避免不必要的操作和子查詢，合理使用JOIN操作，減少查詢的資料量和複雜度。
使用資料庫緩存：考慮使用緩存機制，如Redis或Memcached，緩存已經查詢過的結果集，減少資料庫的通路次數。
分頁優化：根據具體業務需求和場景，考慮合适的分頁政策。例如，可以使用遊标分頁（Cursor-based Pagination）而不是傳統的基于頁碼的分頁，以提高性能和避免資料一緻性問題。
資料庫拆分和分布式架構：如果資料量非常龐大且單台資料庫無法滿足需求，可以考慮将資料進行拆分，并采用分布式資料庫架構，以提高查詢性能和擴充性。

綜上所述，分頁查詢千萬級别的資料需要注意資料庫性能、資料排序和資料一緻性等難點。通過優化查詢語句、使用适當的索引、合理分頁政策以及采用緩存和分布式架構等方法，可以改善查詢性能和應對資料量的挑戰。

1 先給結論

對于1千萬的資料查詢，主要關注分頁查詢過程中的性能

針對偏移量大導緻查詢速度慢：

先對查詢的字段建立唯一索引

根據業務需求，先定位查詢範圍（對應主鍵id的範圍，比如大于多少、小于多少、IN）

查詢時，将第2步确定的範圍作為查詢條件

針對查詢資料量大的導緻查詢速度慢：

查詢時，減少不需要的列，查詢效率也可以得到明顯提升一次盡可能按需查詢較少的資料條數借助nosql緩存資料等來減輕mysql資料庫的壓力

2 準備資料

2.1 建立表

CREATE TABLE `user_operation_log`  (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `ip` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `op_data` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   `attr12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.2 造資料腳本

采用批量插入，效率會快很多，而且每1000條數就commit，資料量太大，也會導緻批量插入效率慢

less複制代碼 DELIMITER ;;
 CREATE DEFINER=`root`@`%` PROCEDURE `batch_insert_log`()
 BEGIN
   DECLARE i INT DEFAULT 1;
   DECLARE userId INT DEFAULT 10000000;
  set @execSql = 'INSERT INTO `big_data`.`user_operation_log`(`user_id`, `ip`, `op_data`, `attr1`, `attr2`, `attr3`, `attr4`, `attr5`, `attr6`, `attr7`, `attr8`, `attr9`, `attr10`, `attr11`, `attr12`) VALUES';
  set @execData = '';
   WHILE i<=10000000 DO
    set @attr = "rand_string(50)";
   set @execData = concat(@execData, "(", userId + i, ", '110.20.169.111', '使用者登入操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");
   if i % 1000 = 0
   then
      set @stmtSql = concat(@execSql, @execData,";");
     prepare stmt from @stmtSql;
     execute stmt;
     DEALLOCATE prepare stmt;
     commit;
     set @execData = "";
    else
      set @execData = concat(@execData, ",");
    end if;
   SET i=i+1;
   END WHILE;
 END
 DELIMITER ;

delimiter $
 create function rand_string(n INT) 
 returns varchar(255) #該函數會傳回一個字元串
 begin 
 #chars_str定義一個變量 chars_str,類型是 varchar(100),預設值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  declare chars_str varchar(100) default
    'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  declare return_str varchar(255) default '';
  declare i int default 0;
  while i < n do 
    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i = i + 1;
    end while;
  return return_str;
 end $

2.3 執行存儲過程函數

因為模拟資料流量是1000W，我這電腦組態不高，耗費了不少時間，應該個把小時吧

sql複制代碼 SELECT count(1) FROM `user_operation_log`;

2.4 普通分頁查詢

MySQL 支援 LIMIT 語句來選取指定的條數資料， Oracle 可以使用 ROWNUM 來選取。

MySQL分頁查詢文法如下：

sql複制代碼 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

第一個參數指定第一個傳回記錄行的偏移量
第二個參數指定傳回記錄行的最大數目

下面我們開始測試查詢結果：

sql複制代碼 SELECT * FROM `user_operation_log` LIMIT 10000, 10;

查詢3次時間分别為：

這樣看起來速度還行，不過是本地資料庫，速度自然快點。

換個角度來測試

相同偏移量，不同資料量

SELECT * FROM `user_operation_log` LIMIT 10000, 10;
 SELECT * FROM `user_operation_log` LIMIT 10000, 100;
 SELECT * FROM `user_operation_log` LIMIT 10000, 1000;
 SELECT * FROM `user_operation_log` LIMIT 10000, 10000;
 SELECT * FROM `user_operation_log` LIMIT 10000, 100000;
 SELECT * FROM `user_operation_log` LIMIT 10000, 1000000;

從上面結果可以得出結束：資料量越大，花費時間越長（這不是廢話嗎？）

相同資料量，不同偏移量

sql複制代碼 SELECT * FROM `user_operation_log` LIMIT 100, 100;
 SELECT * FROM `user_operation_log` LIMIT 1000, 100;
 SELECT * FROM `user_operation_log` LIMIT 10000, 100;
 SELECT * FROM `user_operation_log` LIMIT 100000, 100;
 SELECT * FROM `user_operation_log` LIMIT 1000000, 100;

從上面結果可以得出結束：偏移量越大，花費時間越長

3 如何優化

既然我們經過上面一番的折騰，也得出了結論，針對上面兩個問題：偏移大、資料量大，我們分别着手優化

3.1 優化資料量大的問題

sql複制代碼SELECT * FROM `user_operation_log` LIMIT 1, 1000000
SELECT id FROM `user_operation_log` LIMIT 1, 1000000
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM `user_operation_log` LIMIT 1, 1000000

查詢結果如下：

上面模拟的是從1000W條資料表中，一次查詢出100W條資料，看起來性能不佳，但是我們正常業務中，很少有一次性從mysql中查詢出這麼多條資料量的場景。可以結合nosql緩存資料等等來減輕mysql資料庫的壓力。

是以，針對查詢資料量大的問題：

查詢時，減少不需要的列，查詢效率也可以得到明顯提升一次盡可能按需查詢較少的資料條數借助nosql緩存資料等來減輕mysql資料庫的壓力

第一條和第三條查詢速度差不多，這時候你肯定會吐槽，那我還寫那麼多字段幹啥呢，直接 * 不就完事了

注意本人的 MySQL 伺服器和用戶端是在同一台機器上，是以查詢資料相差不多，有條件的同學可以測測用戶端與MySQL分開

SELECT * 它不香嗎？

在這裡順便補充一下為什麼要禁止 SELECT *。難道簡單無腦，它不香嗎？

主要兩點：

用 "SELECT * " 資料庫需要解析更多的對象、字段、權限、
屬性等相關内容，在 SQL 語句複雜，硬解析較多的情況下，會對資料庫造成沉重的負擔。
增大網絡開銷，* 有時會誤帶上如log、IconMD5之類的無用且大文本字段，資料傳輸size會幾何增漲。特别是MySQL和應用程式不在同一台機器，這種開銷非常明顯。

3.2 優化偏移量大的問題

在分頁查詢中，當偏移量（Offset）較大時，可能會面臨性能問題。這是由于資料庫需要跳過大量的資料行才能擷取到指定的結果集。以下是一些優化分頁查詢偏移量大的常見方法：

使用遊标分頁：傳統的基于偏移量的分頁查詢會導緻資料庫掃描和跳過大量資料行，而遊标分頁（Cursor-based Pagination）通過使用記錄指針來定位和擷取資料，避免了大量的偏移操作。通過使用類似于"WHERE id > last_id"的條件，可以在每次查詢中僅傳回需要的資料行。
利用索引：確定分頁查詢的排序字段（通常是時間戳或唯一辨別符）上有适當的索引。這可以減少資料庫掃描的資料量，加快查詢速度。
預取資料：如果分頁查詢需要在每次查詢中傳回總數（例如用于顯示總頁數），可以考慮在另一個單獨的查詢中預先擷取總數，而不是在每次查詢中都進行計數操作。這樣可以減輕資料庫的負擔。
緩存結果：如果分頁查詢的結果集在一段時間内不會發生變化，可以考慮将結果緩存起來，以減少對資料庫的頻繁通路。使用适當的緩存機制，例如Redis或Memcached，可以提高查詢性能。
資料預處理：如果分頁查詢的資料在一定時間段内不會發生變化，可以通過預先計算和存儲結果集，而不是每次查詢都動态生成結果。這可以提高查詢的響應速度。
資料庫優化：通過優化資料庫的配置參數、索引、查詢語句和伺服器硬體等，可以提升資料庫的整體性能，進而減少分頁查詢偏移量大的影響。

需要根據具體的業務需求和資料特征選擇适當的優化政策。對于大規模資料集和偏移量較大的分頁查詢，考慮使用遊标分頁和适當的索引來減少資料庫的掃描和跳過操作。同時，結合緩存和資料預處理等技術，可以進一步提高分頁查詢的性能。

3.2.1 采用子查詢方式

我們可以先定位偏移位置的 id，然後再查詢資料

SELECT id FROM `user_operation_log` LIMIT 1000000, 1;
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10;

查詢結果如下：

這種查詢效率不理想啊！！！奇怪，id是主鍵，主鍵索引不應當查詢這麼慢啊？？？

先EXPLAIN分析下sql語句：

sql複制代碼EXPLAIN SELECT id FROM `user_operation_log` LIMIT 1000000, 1;
EXPLAIN SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM `user_operation_log` LIMIT 1000000, 1) LIMIT 10;

奇怪，走了索引啊，而且是主鍵索引，如下

帶着十萬個為什麼和千萬個不甘心，嘗試給主鍵再加一層唯一索引

ALTER TABLE `big_data`.`user_operation_log` 
ADD UNIQUE INDEX `idx_id`(`id`) USING BTREE;

由于資料量有1000W，是以，加索引需要等待一會兒，畢竟建立1000W條資料的索引，一般機器沒那麼快。

然後再次執行上面的查詢，結果如下：

天啊，這查詢效率的差距不止十倍！！！

再次EXPLAIN分析一下：

命中的索引不一樣，命中唯一索引的查詢，效率高出不止十倍。

結論：

對于大表查詢，不要太相信主鍵索引能夠帶來多少的性能提升，老老實實根據查詢字段，添加相應索引吧！！！

但是上面的方法隻适用于id是遞增的情況，如果id不是遞增的，比如雪花算法生成的id，得按照下面的方式：

注意：

某些 mysql 版本不支援在 in 子句中使用 limit，是以采用了多個嵌套select
但這種缺點是分頁查詢隻能放在子查詢裡面

SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t);

查詢所花費時間如下：

EXPLAIN一下

vbnet複制代碼EXPLAIN SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t);

3.2.2 采用 id 限定方式

這種方法要求更高些，id必須是連續遞增（注意是連續遞增，不僅僅是遞增哦），而且還得計算id的範圍，然後使用 between，sql如下

SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100;
SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100;

可以看出，查詢效率是相當不錯的

注意：這裡的 LIMIT 是限制了條數，沒有采用偏移量

還是EXPLAIN分析一下

EXPLAIN SELECT * FROM `user_operation_log` WHERE id between 1000000 AND 1000100 LIMIT 100;
EXPLAIN SELECT * FROM `user_operation_log` WHERE id >= 1000000 LIMIT 100;

是以，針對分頁查詢，偏移量大導緻查詢慢的問題：

先對查詢的字段建立唯一索引根據業務需求，先定位查詢範圍（對應主鍵id的範圍，比如大于多少、小于多少、IN）查詢時，将第2步确定的範圍作為查詢條件

一千萬以上的資料量，你是如何做查詢的？