MySQL5.6中limit的工作机制和order by limit优化原理

MySQL5.6中Limit的工作机制

如果你仅需要在一个结果集中返回特定的几行，通常是使用limit，而不是取回整个结果集再舍去不需要的数据，MySQL通常按照如下的方式优化一个包含limit row_count或HAVING的语句：

◎只有limit

如果你只通过limit返回少量的行，那么正常情况下mysql会使用全盘扫描，有些场合会使用索引，以下是使用了覆盖索引的情况：

以下是使用全表扫描的情况：

 

◎order by和limit

如果你order by和limit一起使用，那么mysql在排序结果中找到最初的row_count行之后就会完成这条语句，而不是对整个结果集进行排序。如果使用了索引排序，它就非常快地完成。如果整个filesort必须都做完的话，那么在找到最初的row_count行之前，匹配该查询的所有行都将被select，并且做sort操作。如果这些行找到了，mysql将不会对剩余的结果集进行排序。

◎distinct和limit

当limit row_count和distinct一起使用时，MySQL在找到最初的unique的row_count行之后就会停止检索。

◎group by和limit

在某些场合下，group by会用于某些key行的排序，并且计算汇总信息，这时如果使用limit row_count的话将不会计算任何额外的grup by值。

◎SQL_CALC_FOUND_ROWS和limit

只要MySQL已经返回了需要的行数给客户端，它将终止这个查询，除非你在查询中使用了SQL_CALC_FOUND_ROWS。

◎limit 0的用法

Limit 0会非常快地返回一个空结果，这个功能可被应用于检测一条SQL的合法性。

◎临时表和limit

如果服务器在查询中使用了临时表，它会使用limit row_count语句来计算需求的空间大小。

 

Order by和Limit混合使用引起的问题

如果在order by语句中返回的结果集有很多行，那么非排序的列的返回结果是不确定的，即随机的，所以如果配合limit的话每次返回的结果集的顺序是不固定的，比如下面这个例子

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以后，可发现id列和rating列和之前的结果集顺序有出入：

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你需要的那几列了：

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 |

+----+----------+--------+

 

Order by和limit一起使用的优化原理

从MySQL5.6.2版本以后，优化器将更加智能地处理下面形式的查询了

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这种在很大的结果集中只返回很少的行数的查询类型在web应用中非常常见，比如

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

 

排序缓存有一个参数是sort_buffer_size，如果这个参数大小足够上面范例中的N行的排序结果集(如果M也被定义，那就是M+N行的结果集大小)，那么服务器将会避免一个文件排序操作，使得排序完全在内存中完成。

内存排序+limit原理

1 扫描表，在内存中插入那些被选择排序的列的数据到一个排好序的队列中，比如order by col1，col2，则插入col1和col2列的数据。如果队列满了，则挤出排序在末尾的数据。

2 返回队列中的前N行记录，如果M也被定义，则调到第M行开始返回后续的N行记录。

 

文件排序+limit原理

1扫描表，重复步骤2和3，直到表的结尾

2选中这些行数直到排序缓存被填满

3在排序缓存中写入第一个N行（如果M被定义，则M+N行）到一个排序文件中。

 

两者比较

在内存中排序和使用文件排序相比，扫描表的代价几乎是一样的，不同的是其他的开销：

内存排序的方法在插入数据到一个有序队列中会牵扯到更多的cpu资源，而文件排序会消耗更多的磁盘IO，优化器在考虑两者的平衡性上会主要考虑N的值大小

http://blog.csdn.net/cug_jiang126com/article/details/42246721