怎样创建自己公司的网站,短链接生成网,wordpress图片小工具栏,重庆大渡口网站建设解决方案MySQL 查询 limit 100000000, 10 和 limit 10 速度一样快吗#xff1f; MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。
server层有很多模块#xff0c;其中需要关注的是执行器是用于跟存储引擎打交道的组件。
执行器可以通过调用存储引擎提供的接口 MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。
server层有很多模块其中需要关注的是执行器是用于跟存储引擎打交道的组件。
执行器可以通过调用存储引擎提供的接口将一行行数据取出当这些数据完全符合要求比如满足其他where条件则会放到结果集中最后返回给调用mysql的客户端。
两种查询方式。对应 limit offset, size 和 limit size 两种方式。
而其实 limit size 相当于 limit 0, size。也就是从0开始取size条数据。
也就是说两种方式的区别在于offset是否为0。
1. 以主键索引的 LIMIT 10 为例
LIMIT 10 查询的是结果集的前 10 行数据。这种情况下MySQL 只需要扫描并返回前 10 行数据操作相对简单且快速。
SELECT * FROM table_name LIMIT 10;server层会调用innodb的接口在innodb里的主键索引中获取到第0到10条完整行数据依次返回给server层并放到server层的结果集中返回给客户端。
2. 以主键索引的 LIMIT 100000000, 10 为例
LIMIT 100000000, 10 查询的是从结果集的第 100000001 行开始的 10 行数据。这种情况下MySQL 需要先扫描前 100000000 行数据并丢弃然后再返回接下来的 10 行数据。这种操作会导致大量的行扫描和丢弃操作效率很低。
SELECT * FROM table_name LIMIT 100000000, 10;server层会调用innodb的接口由于这次的offset100000000会在innodb里的主键索引中获取到第0到100000000 10条完整行数据返回给server层之后根据offset的值挨个抛弃最后只留下最后面的size条也就是10条数据放到server层的结果集中返回给客户端。
可以看出当offset非0时server层会从引擎层获取到很多无用的数据而获取的这些无用数据都是要耗时的。
以非主键索引的 LIMIT 10 为例
SELECT * FROM table_name LIMIT 10;server层会调用innodb的接口在innodb里的非主键索引中获取到第0条数据对应的主键id后回表到主键索引中找到对应的完整行数据然后返回给server层server层将其放到结果集中返回给客户端。
而当offset0时且offset的值较小时逻辑也类似区别在于offset0时会丢弃前面的offset条数据。
也就是说非主键索引的limit过程比主键索引的limit过程多了个回表的消耗。
但当offset变得非常大时比如600万此时执行explain。
可以看到type那一栏显示的是ALL也就是全表扫描。
这是因为server层的优化器会在执行器执行sql语句前判断下哪种执行计划的代价更小。
很明显优化器在看到非主键索引的600w次回表之后摇了摇头还不如全表一条条记录去判断算了于是选择了全表扫描。
因此当limit offset过大时非主键索引查询非常容易变成全表扫描。是真性能杀手。
性能比较
LIMIT 10MySQL 只需扫描前 10 行数据快速返回结果性能高。LIMIT 100000000, 10MySQL 需要扫描前 100000000 行数据并丢弃然后返回接下来的 10 行数据性能低。
优化大偏移量的查询
当你需要从一个大偏移量开始查询时可以采用以下优化方法
1. 使用覆盖索引
确保查询使用了覆盖索引这样可以减少全表扫描的开销。
SELECT col1, col2 FROM table_name FORCE INDEX (index_name) WHERE indexed_column value LIMIT 100000000, 10;2. 通过主键范围查询
使用主键范围查询先查找主键值再根据主键值进行查询避免大偏移量的扫描。
第一步找到起始主键值
SELECT id FROM table_name ORDER BY id LIMIT 100000000, 1;-- 假设起始主键值为 start_id
-- 第二步基于主键值进行查询
SELECT * FROM table_name WHERE id start_id LIMIT 10;3. 使用延迟关联Deferred Join
先根据索引字段查找主键再根据主键进行关联查询从而避免大偏移量的扫描。
-- 第一步查找主键值
SELECT id FROM table_name ORDER BY id LIMIT 100000000, 10;-- 第二步基于主键进行关联查询
SELECT * FROM table_name WHERE id IN (id1, id2, ..., id10);4. 使用分页技术
对于用户分页的场景通常需要记录当前页和页数采用基于主键或其他唯一标识的分页技术避免使用 LIMIT 大偏移量查询。
结论
MySQL 中 LIMIT 100000000, 10 和 LIMIT 10 的查询速度并不一样。大偏移量的查询会导致性能问题可以通过覆盖索引、主键范围查询、延迟关联等方法进行优化。
