MySQL 千万级别的数据查询方法 (优化偏移量大问题)

先创建test表

CREATE TABLE `test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(64) 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;

创建数据脚本

DELIMITER ;;
CREATE PROCEDURE batch_insert_2()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE userId INT DEFAULT 10000000;
 set @execSql = 'INSERT INTO `test`(`user_id`) VALUES';
 set @execData = '';
  WHILE i<=10000000 DO
  set @execData = concat(@execData, "(", userId + i, ")");
  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 ;

调用存储过程

call batch_insert_2()

通过以上步骤保证了表中有足够多的数据，接下来就是查询方法

1.确保有主键，并且给主键加上唯一索引

ALTER TABLE test 
ADD UNIQUE INDEX `idx_id`(`id`) USING BTREE;

2.如果主键是自增，可以使用以下SQL

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

3.如果主键不是自增，可以使用以下SQL

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

通过修改主键和SQL写法，查询效率大大的增加，亲测有用