MYSQL Optimizing LIMIT Queries

      如果要指定查询的数据行数,在查询语句中使用limit子句,而不是获取所有数据行结果集,然后去掉没用的数据。

      MYSQL有时会对没有having子句,带有limit关键字的查询进行优化:

      1:如果用limit子句选择很少的行数据集,mysql会利用索引来代替全表扫描;

      2:如果组合使用limit 和order by 查询,mysql会在满足limit数量限制时停止sort,而不是对所有数据sort.如果利用index来进行排序,过程很快,但如果走的是filesort,所有 匹配该查询的结果集(except limit)都会被获取,在满足limit子句数据行找到之前,大部分(全部)数据都会被sort(即找到所有满足条件的数据行,然后排序找到满足limit的前几条数据,然后再停止)。

      3:一个order by查询带有和不带有limit的返回集可能以不同的顺序,下面有介绍:

      4:如果组合使用limit和distinct关键字,mysql在找到row_count的唯一数据行时,立刻停止。

      5:一些情况下,group by(order by)可以通过读取key的顺序,此时,limit row_count可以限制不必要计算的group by值。

      6: 当客户端收到指定行数的时候,会中断查询,除非使用了SQL_CALC_FOUND_ROWS。

      7: limit 0直接返回空集,可以用来检查查询是否合法。

      8: 当服务使用临时表,会使用limit子句来计算需要多少空间

 

      当在order by列中具有相同值的很多行时,mysql server会不确定的以任何顺序返回这些行数据,换句话说,排序结果的顺序对非order by列来说是不确定的

     

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 |
+----+----------+--------+
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 |
+----+----------+--------+

   如果要确定不适用limit情况下返回集的顺序(最好加上一个唯一列),如下:如果id 列为unique,可以这么使用:

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 |
+----+----------+--------+
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

 

     如果排序的个数N刚好能在sort buffer里面,那么服务就可以避免执行文件合并和并且把sort buffer当做一个优先级队列来处理

     1:  扫描表,把选中的行都插入队列中,如果队列满了把最后一个剔除掉。

      2:  然后返回前N行,如果有跳过M,那么先跳过M行,然后返回之后的N行记录。

    之前使用的处理方法

      1:  扫描表,重复下面的步骤直到结束

      2:输入select row直到sort buffer满。

      3: 写入前N行到buffer,然后把前N行合并到文件

      4:排序合并文件并返回前N行

    扫描表的花费和队列和文件合并一样,所以优化器在选择的时候是根据其他花费的

      1: 队列的方法会使用很多cpu来插入到队列

      2: 合并文件会使用IO来读写文件,cpu来排序

   优化器在行数和不同值N之间平衡。

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。