大厂面试官：千万级数据量的表，如何进行快速查询，springmvc 源码书籍

END;

;

DELIMITER ;

[](

)开始测试

哥的电脑配置比较低：win10 标压渣渣 i5 读写约 500MB 的 SSD

由于配置低，本次测试只准备了 3148000 条数据，占用了磁盘 5G(还没建索引的情况下)，跑了 38min，电脑配置好的同学，可以插入多点数据测试

SELECT?count(1)?FROM?user_operation_log

返回结果：3148000

三次查询时间分别为：

• 14060 ms

• 13755 ms

• 13447 ms

[](

)普通分页查询

MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。

MySQL 分页查询语法如下：

SELECT?*?FROM?table?LIMIT?[offset,]?rows?|?rows?OFFSET?offset

• 第一个参数指定第一个返回

【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开：qq.cn.hn/FTf 免费领取

记录行的偏移量

• 第二个参数指定返回记录行的最大数目

下面我们开始测试查询结果：

SELECT?*?FROM?user_operation_log?LIMIT?10000,?10

查询 3 次时间分别为：

• 59 ms

• 49 ms

• 50 ms

这样看起来速度还行，不过是本地数据库，速度自然快点。

换个角度来测试

[](

)相同偏移量，不同数据量

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

查询时间如下：

| 数量 | 第一次 | 第二次 | 第三次 |

| --- | --- | --- | --- |

| 10 条 | 53ms | 52ms | 47ms |

| 100 条 | 50ms | 60ms | 55ms |

| 1000 条 | 61ms | 74ms | 60ms |

| 10000 条 | 164ms | 180ms | 217ms |

| 100000 条 | 1609ms | 1741ms | 1764ms |

| 1000000 条 | 16219ms | 16889ms | 17081ms |

从上面结果可以得出结束：数据量越大，花费时间越长

[](

)相同数据量，不同偏移量

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

| 偏移量 | 第一次 | 第二次 | 第三次 |

| --- | --- | --- | --- |

| 100 | 36ms | 40ms | 36ms |

| 1000 | 31ms | 38ms | 32ms |

| 10000 | 53ms | 48ms | 51ms |

| 100000 | 622ms | 576ms | 627ms |

| 1000000 | 4891ms | 5076ms | 4856ms |

从上面结果可以得出结束：偏移量越大，花费时间越长

SELECT?*?FROM?user_operation_log?LIMIT?100,?100

SELECT?id,?attr?FROM?user_operation_log?LIMIT?100,?100

[](

)如何优化

既然我们经过上面一番的折腾，也得出了结论，针对上面两个问题：偏移大、数据量大，我们分别着手优化

[](

)优化偏移量大问题

[](

)采用子查询方式

我们可以先定位偏移位置的 id，然后再查询数据

SELECT * FROM user_operation_log LIMIT 1000000,

10SELECT id FROM user_operation_log LIMIT 1000000,

1SELECT * FROM user_operation_log WHERE id >= (

SELECT id FROM user_operation_log LIMIT 1000000,

1

) LIMIT 10

查询结果如下：

| sql | 花费时间 |

| --- | --- |

| 第一条 | 4818ms |

| 第二条(无索引情况下) | 4329ms |

| 第二条(有索引情况下) | 199ms |

| 第三条(无索引情况下) | 4319ms |

| 第三条(有索引情况下) | 201ms |

从上面结果得出结论：

• 第一条花费的时间最大，第三条比第一条稍微好点

• 子查询使用索引速度更快

缺点：只适用于 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

)

[](

)采用 id 限定方式

这种方法要求更高些，id 必须是连续递增，而且还得计算 id 的范围，然后使用 between，sql 如下

SELECT??FROM?user_operation_log?WHERE?id?between?1000000?AND?1000100?LIMIT?100SELECT??FROM?user_operation_log?WHERE?id?>=?1000000?LIMIT?100

查询结果如下：

| sql | 花费时间 |

| --- | --- |

| 第一条 | 22ms |

| 第二条 | 21ms |

从结果可以看出这种方式非常快

注意：这里的 LIMIT 是限制了条数，没有采用偏移量