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之间平衡。