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