面試官：一千萬的資料，你是怎麼查詢的？

joyCode發表於2023-03-31
面試

1 先給結論

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

  • 針對偏移量大導致查詢速度慢：
  1. 先對查詢的欄位建立唯一索引
  2. 根據業務需求，先定位查詢範圍（對應主鍵id的範圍，比如大於多少、小於多少、IN）
  3. 查詢時，將第2步確定的範圍作為查詢條件
  • 針對查詢資料量大的導致查詢速度慢：
  1. 查詢時，減少不需要的列，查詢效率也可以得到明顯提升
  2. 一次儘可能按需查詢較少的資料條數
  3. 藉助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，資料量太大，也會導致批次插入效率慢 

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，我這電腦配置不高，耗費了不少時間，應該個把小時吧

SELECT count(1) FROM `user_operation_log`;

image-20230331163130669

2.4 普通分頁查詢

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

MySQL分頁查詢語法如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
  • 第一個引數指定第一個返回記錄行的偏移量
  • 第二個引數指定返回記錄行的最大數目

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

SELECT * FROM `user_operation_log` LIMIT 10000, 10;

查詢3次時間分別為：

image-20230331164216737

image-20230331164250745

image-20230331164319760

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

換個角度來測試

相同偏移量，不同資料量

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;

image-20230331163554821

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

相同資料量，不同偏移量

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;

image-20230331163739605

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

3 如何最佳化

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

3.1 最佳化資料量大的問題

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

查詢結果如下：

image-20230331204013186

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

因此，針對查詢資料量大的問題：

  1. 查詢時，減少不需要的列，查詢效率也可以得到明顯提升
  2. 一次儘可能按需查詢較少的資料條數
  3. 藉助nosql快取資料等來減輕mysql資料庫的壓力

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

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

SELECT * 它不香嗎？

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

主要兩點：

  1. 用 “SELECT * “ 資料庫需要解析更多的物件、欄位、許可權、屬性等相關內容，在 SQL 語句複雜，硬解析較多的情況下，會對資料庫造成沉重的負擔。
  2. 增大網路開銷，* 有時會誤帶上如log、IconMD5之類的無用且大文字欄位，資料傳輸size會幾何增漲。特別是MySQL和應用程式不在同一臺機器，這種開銷非常明顯。

3.2 最佳化偏移量大的問題

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;

查詢結果如下：

image-20230331194706798

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

先EXPLAIN分析下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;

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

image-20230331195704778

image-20230331195904091

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

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

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

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

image-20230331195440793

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

再次EXPLAIN分析一下：

image-20230331200317237

image-20230331200209341

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

結論：

對於大表查詢，不要太相信主鍵索引能夠帶來多少的效能提升，老老實實根據查詢欄位，新增相應索引吧！！！

但是上面的方法只適用於==id是遞增==的情況，如果id不是遞增的，比如雪花演算法生成的id，得按照下面的方式：

注意：

  1. 某些 mysql 版本不支援在 in 子句中使用 limit，所以採用了多個巢狀select
  2. 但這種缺點是分頁查詢只能放在子查詢裡面
SELECT * FROM `user_operation_log` WHERE id IN (SELECT t.id FROM (SELECT id FROM `user_operation_log` LIMIT 1000000, 10) AS t);

查詢所花費時間如下：

image-20230331201356087

EXPLAIN一下

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

image-20230331201459758

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;

image-20230331202058138

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

注意：這裡的 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;

image-20230331202314419

image-20230331202334850

因此，針對分頁查詢，偏移量大導致查詢慢的問題：

  1. 先對查詢的欄位建立唯一索引
  2. 根據業務需求，先定位查詢範圍（對應主鍵id的範圍，比如大於多少、小於多少、IN）
  3. 查詢時，將第2步確定的範圍作為查詢條件
本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章