mysql中order by和limit混用时出现的问题

2019独角兽企业重金招聘Python工程师标准>>>

sql：

SELECT
	D.authen_state,
	D.developer_id,
	D.real_name,
	D.address,
	D.phone_num,
	D.developer_id_type,
	D.enterprise_detail_id,
	D.create_time,
	D.update_time,
	D.is_avail
FROM
	aisv_g_developer_detail D
ORDER BY
	authen_state ASC
LIMIT 30,10

问题：分页查询数据，不同页之中出现了相同的数据。

原因：这是Mysql5.7以上的优化

官方文档：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.
One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

翻译：如果你将Limit row_count与order by混用，mysql会找到排序的row_count行后立马返回，而不是排序整个查询结果再返回。如果是通过索引排序，会非常快；如果是文件排序，所有匹配查询的行（不带Limit的）都会被选中，被选中的大多数或者全部会被排序，直到limit要求的row_count被找到了。如果limit要求的row_count行一旦被找到，Mysql就不会排序结果集中剩余的行了。

如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

根据这个我们大概知道为什么会分页不准了，因为sql中的authen_state存在相同的行，在实际执行时返回结果对应的行的顺序是不确定的。

解决方案：如果想在limit存在或者不存在的情况下，都保证结果相同，可以在后面再加上一个排序条件，例如id字段是唯一的，可以在排序字段中额外加上以确保顺序稳定。

所以最终的sql如下：

SELECT
	D.authen_state,
	D.developer_id,
	D.real_name,
	D.address,
	D.phone_num,
	D.developer_id_type,
	D.enterprise_detail_id,
	D.create_time,
	D.update_time,
	D.is_avail
FROM
	aisv_g_developer_detail D
ORDER BY
	authen_state ASC,developer_id
LIMIT 30,10

 

转载于:https://my.oschina.net/u/3676955/blog/1923238