欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Mysql Sql语句优化之 LIMIT

程序员文章站 2022-07-07 20:03:06
添加小编微信带您进入Java交流社区小编微信：372787553 备注进群概述在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？这时候大家是不是都会想到分页查询?确实是这样接下来让我们来看看Mysql Limit的那些事LIMIT语法MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。SELECT * FROM table LIMIT....

添加小编微信带您进入Java交流社区
小编微信：372787553 备注进群

概述

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？

这时候大家是不是都会想到分页查询?确实是这样接下来让我们来看看Mysql Limit的那些事

LIMIT语法

MySQL的limit基本用法很简单。limit接收1或2个整数型参数，如果是2个参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。

SELECT * FROM table LIMIT [offset,] rows

优化LIMIT

如果从结果集中只需要指定数量的行，则LIMIT在查询中使用子句，而不是获取整个结果集并丢弃多余的数据。

MySQL有时会优化具有子句和无 子句的查询：LIMIT row_count HAVING

  • 如果仅使用来选择几行 LIMIT，则在通常情况下，MySQL倾向于使用全表扫描，因此在某些情况下会使用索引。

  • 如果结合使用 ，则MySQL在找到排序结果的第一行后即停止排序 ，而不是对整个结果进行排序。如果通过使用索引进行排序，这将非常快。如果必须完成文件排序，则在找到第一个之前，将选择所有与查询匹配的不带子句的行，并对其中的大多数或全部进行排序 。找到初始行后，MySQL不会对结果集的其余部分进行排序。LIMIT *row_count*``ORDER BYrow_countLIMITrow_count

    此行为的一种体现是，ORDER BY带有和不带有 查询的查询 LIMIT可能以不同的顺序返回行，如本节后面所述。

  • 如果结合使用，MySQL 将在 找到唯一行后立即停止。LIMIT row_count DISTINCTrow_count

  • 在某些情况下，GROUP BY可以通过按顺序读取索引（或对索引进行排序）然后计算汇总直到索引值更改来解决a。在这种情况下，不会计算任何不必要的 值。LIMIT *row_count*GROUP BY`

  • MySQL一旦向客户端发送了所需的行数，它将立即终止查询，除非您使用 SQL_CALC_FOUND_ROWS。在这种情况下，可以使用检索行数SELECT FOUND_ROWS()。

  • LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于获取使用MySQL API的应用程序中结果列元数据的类型的结果列的类型。在 mysql客户端程序中，您可以使用该 --column-type-info选项显示结果列类型。

  • 如果服务器使用临时表来解析查询，则它将使用该子句来计算所需的空间。LIMIT *row_count*

  • 如果未使用索引，ORDER BY 但LIMIT也存在子句，则优化器可以避免使用合并文件，并使用内存中filesort操作对内存中的行进行排序 。

如果多行在列中具有相同的值ORDER BY，则服务器可以*以任何顺序返回这些行，并且根据整体执行计划，这样做的方式可能有所不同。换句话说，这些行的排序顺序相对于无序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此ORDER BY 带有和不带有查询的查询LIMIT可能以不同顺序返回行。考虑以下查询，该查询按category列排序，但对于id和 rating列不确定：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |     1 |   4.5 |
|  5 |     1 |   3.2 |
|  3 |     2 |   3.7 |
|  4 |     2 |   3.5 |
|  6 |     2 |   3.5 |
|  2 |     3 |   5.0 |
|  7 |     3 |   2.7 |
+----+----------+--------+

包含LIMIT可能会影响每个category值中的行顺序。例如，这是一个有效的查询结果：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |     1 |   4.5 |
|  5 |     1 |   3.2 |
|  4 |     2 |   3.5 |
|  3 |     2 |   3.7 |
|  6 |     2 |   3.5 |
+----+----------+--------+

在每种情况下，行均按ORDER BY列排序，这是SQL标准所需的全部。

如果重要的是要确保使用和不使用相同的行顺序，则LIMIT在ORDER BY子句中包括其他列以使顺序确定。例如，如果id值是唯一的，则可以通过如下排序使给定category值的行 按id顺序显示 ：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |     1 |   4.5 |
|  5 |     1 |   3.2 |
|  3 |     2 |   3.7 |
|  4 |     2 |   3.5 |
|  6 |     2 |   3.5 |
|  2 |     3 |   5.0 |
|  7 |     3 |   2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |     1 |   4.5 |
|  5 |     1 |   3.2 |
|  3 |     2 |   3.7 |
|  4 |     2 |   3.5 |
|  6 |     2 |   3.5 |
+----+----------+--------+

为什么 Limit 后的值越大性能越差？

假设有一张表index_test,表内存有1004258条数据，这时我需要取出前39000条数据？

这是大家脑海中肯定会出现这样一条SQL：

SELECT  *FROM  index_test tt  ORDER BY id  LIMIT 39000

Sql的执行时间如下：

Mysql Sql语句优化之 LIMIT

大家可以看到耗时：10s多

来让我们看一下执行计划：

Mysql Sql语句优化之 LIMIT

如果您不知道如何查看执行计划，请阅读Mysql执行计划全解，SQL优化必备技能
这时我们在看 **type 字段的值时ALL，**让我们在复习以下ALL的含义:

对来自先前表的行的每个组合进行全表扫描。如果该表是未标记的第一个表 const，则通常不好，在其他所有情况下通常 非常糟糕。通常，可以ALL通过添加索引来避免这种情况，这些 索引允许基于早期表中的常量值或列值从表中检索行。

本文地址：https://blog.csdn.net/weixin_38937840/article/details/107168538

上一篇： 尘对雨说

下一篇： Mysql中的增删改查补充

推荐阅读