【面试篇】:千万数据下如何优化limit

【面试篇】:千万数据下如何优化limit

前言：在面试的时候，很容易被问到，在面对千万数据如何进行处理，而我们在日常开发中也不容易接触到千万级别的数据，所以，本文将在千万的数据量下分析如何优化limit。

1、limit 知识复习

1.1、语法

在MySQL中Limit有两种语法：

 limit offset, rowslimit rows

其中offset表示偏移量，rows表示要返回的记录条数。

1.2、limit 存在的问题

当我们用 limit 1000000, 10 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。

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 准备数据

创建一个存储过程，代码如下：

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGINDECLARE i INT DEFAULT 1;DECLARE userId INT DEFAULT 10000000;set @execSql = 'INSERT INTO `test`.`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 DOset @attr = "'测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性'";set @execData = concat(@execData, "(", userId + i, ", '10.0.69.175', '用户登录操作'", ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ",", @attr, ")");if i % 1000 = 0thenset @stmtSql = concat(@execSql, @execData,";");prepare stmt from @stmtSql;execute stmt;DEALLOCATE prepare stmt;commit;set @execData = "";elseset @execData = concat(@execData, ",");end if;SET i=i+1;END WHILE;END;;
DELIMITER ;

执行存储过程，我的电脑大概跑了50分钟，创建了10785000条数据

3、百万数据量下

3.1 未建立联合索引

当没有为子查询或内连接表涉及的字段id和op_data创建联合索引时，执行下面SQL：

#1 、百万数据下
#1.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 1067 ms 
#1.2 子查询优化 ，id有序情况使用 、op_data未建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 1040 ms
#1.3 内连接优化 ，id有序无序都可使用、op_data未建立联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #1036ms

可以看到，速度并没有变快，因为此时仍然进行了全表扫描

3.2 建立联合索引

创建联合索引，涉及的字段为id，op_data。

CREATE INDEX id_op_data_index ON test.`user_operation_log`(id, op_data) ;

执行同样的SQL：

#2 、百万数据下
#2.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 1067 ms 
#2.2 子查询优化 ，id有序情况使用 、子查询涉及字段建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 70ms
#2.3 内连接优化 ，id有序无序都可使用、连接表创建联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #42ms

可以看到速度有了明显提升，这是因为子查询和内连接都使用到了id_op_data_index这个联合索引，找到符合要求的数据，在查询需要的字段。

3.3 建立联合索引且只查询需要字段

只查询只要的字段，执行相同SQL：

#3 、百万数据下
#3.1 直接查询
SELECT id,op_data FROM `user_operation_log` WHERE op_data = '用户登录操作' LIMIT 1000000, 10  # 970 ms 
#3.2 子查询优化 ，id有序情况使用 、子查询涉及字段建立联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT id,op_data FROM `user_operation_log` WHERE id >= (SELECT id FROM   user_operation_log  WHERE op_data = '用户登录操作'  LIMIT 1000000 , 1 ) LIMIT 10  # 57ms
#3.3 内连接优化 ，id有序无序都可使用、连接表创建联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT t.id,t.op_data FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作' LIMIT 1000000 , 10) temp ON t.id = temp.id  #22ms

可以看到，速度会进一步提升，以inner join 为例子，MySQL在执行的时候，会选择左右两个表中的小表作为驱动表，大表作为被驱动表，在驱动表中取出数据放到join_buffer中可以根据索引去匹配被驱动表，满足条件的放到结果集中。在这原表user_operation_log 是被驱动表，而需要查询的字段，和连接条件都在id_op_data_index这个联合索引中，会使用索引覆盖，而不会去回表，从而加快速度。

4、千万数据量下

4.1 未建立联合索引

#4 、千万级数据下
#4.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 29075 ms
#4.2 子查询查询、id有序情况使用 、op_data未建立联合索引SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 19004 ms
#4.3 内连接优化、id有序无序都可使用、op_data未建立联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 20041 ms

可以看到，数据量过千万之后，时间飙升，所以千万不要说用limit直接查询。

4.2 建立联合索引

#5 、千万级数据下
#5.1 直接查询
SELECT * FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 28025 ms
#5.2 子查询查询、id有序情况使用、子查询涉及字段建立联合索引
SELECT * FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 1898 ms
#5.3 内连接优化、id有序无序都可使用、连接表创建联合索引
SELECT t.* FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 1741 ms

原理与上面一样，都是利用索引。

4.3 建立联合索引且只查询需要字段

#6 、千万级数据下
#6.1 直接查询
SELECT id,op_data FROM `user_operation_log` WHERE op_data = '用户登录操作'   LIMIT 10000000, 10  # 2056 ms
#6.2 子查询查询、id有序情况使用、子查询涉及字段建立联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT id,op_data FROM `user_operation_log` WHERE id >= (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 1 ) LIMIT 10  # 1860 ms
#6.3 内连接优化、id有序无序都可使用、连接表创建联合索引、只查询需要的字段（这里以建立了联合索引的为例）
SELECT t.id,t.op_data FROM user_operation_log t INNER JOIN (SELECT id FROM  user_operation_log WHERE op_data = '用户登录操作'  LIMIT 10000000 , 10) temp ON t.id = temp.id ; # 1707 ms

可以看到，还是需要一秒多才能查询出来，但是在真正的生产环境下，服务器配置比我电脑上的一个虚拟机高得多，况且还会搭建集群，如果这时候还慢的话，那还可以做分库分表处理，数据量在百万的情况下，我的电脑执行都能达到几十毫秒，还是非常快的。

5、总结

• 如果主键id有序，可以用子查询优化
• 如果主键id是无序的，可以用inner join来优化
• 子查询或者内连接中涉及的字段都要创建为联合索引
• 使用索引覆盖减少回表