mysql5.6时间问题_关于mysql5.6 的排序问题.-CSDN博客

本文链接：https://blog.csdn.net/weixin_34532234/article/details/113223972

本文探讨了MySQL5.6与5.5在处理非唯一排序字段时的不同行为，5.6版在内存排序优化上有所改变，可能导致相同查询返回不同结果。在SQL标准中，重复值的排序顺序未定义，这解释了不同版本间的差异。为确保一致性，建议在排序语句中添加唯一字段。

摘要由CSDN通过智能技术生成

mysql 5.6 的排序进行了优化. 同样的sql , 在5.5 跟5.6 上可能得到不同的结果:

CREATE TABLE `TestCase2` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`aValue` decimal(19,2) NOT NULL,

`accuracyClassType_id` bigint(20) NOT NULL,

`productType_id` bigint(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `FKF58064BD791396` (`productType_id`),

KEY `FKF58064BDED5076` (`accuracyClassType_id`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

INSERT INTO `TestCase2` (`id`, `aValue`, `accuracyClassType_id`, `productType_id`)

VALUES

(1,2.00,3,2),

(2,3.00,3,2),

(3,4.00,2,3),

(4,5.00,2,3),

(5,6.00,2,3),

(6,8.00,2,3),

(7,10.00,2,3),

(8,12.00,2,3),

(9,16.00,2,3),

(10,20.00,2,3),

(11,6.00,2,4),

(12,8.00,2,4),

(13,10.00,2,4),

(14,12.00,2,4),

(15,5.00,2,2),

(16,6.00,2,2);

select * from Testcase2 order by aValue desc;

# as you can see mysql has added a fallback sorting because aValue is not unique, which is ok

# the first four id's of the results are: 10, 9, 14, 8

select * from Testcase2 order by aValue desc limit 4;

# as expected the result id's (based on the order by) are : 10, 9, 14, 8

select * from Testcase2 order by aValue desc limit 3;

# which surprisingly results in the following id's based on the order by: 10, 9, 8 ???????

# expecting id's: 10, 9, 14 (see query with limit 4)[19 Mar 2014 13:34] Miguel SolorzanoThank you for the bug report.

mysql 5.1 > select * from Testcase2 order by aValue desc limit 4;

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

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

| 10 | 20.00 | 2 | 3 |

| 9 | 16.00 | 2 | 3 |

| 14 | 12.00 | 2 | 4 |

| 8 | 12.00 | 2 | 3 |

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

4 rows in set (0.00 sec)

mysql 5.1 > select * from Testcase2 order by aValue desc limit 3;

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

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

| 10 | 20.00 | 2 | 3 |

| 9 | 16.00 | 2 | 3 |

| 14 | 12.00 | 2 | 4 |

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

3 rows in set (0.00 sec)

mysql 5.6 > select * from Testcase2 order by aValue desc limit 4;

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

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

| 10 | 20.00 | 2 | 3 |

| 9 | 16.00 | 2 | 3 |

| 14 | 12.00 | 2 | 4 |

| 8 | 12.00 | 2 | 3 |

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

4 rows in set (0.00 sec)

mysql 5.6 > select * from Testcase2 order by aValue desc limit 3;

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

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

| 10 | 20.00 | 2 | 3 |

| 9 | 16.00 | 2 | 3 |

| 8 | 12.00 | 2 | 3 |

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

3 rows in set (0.00 sec)

mysql 5.5 跟 5.6 两个版本对排序的实现:

Mysql 5.5 willl

- allocate buffer space for 1000 rows

- scan the table, inserting each row into the buffer

- sort the buffer, this requires about 10.000 comparisons

- return the first 3 (or 4) rowsMysql

5.6 will

- allocate buffer space for 3 (or 4) rows

- scan the table, maintaining a collection of 3 (or 4) "winners" this requires about 2000 comparisons

- sort final set of "winners", this requires about 6 comparisons

- return the result

sql 标准里对于重复值的排序字段, 应该出哪行记录是没有定义的, 这就完全取决于代码的实现了.

我们的升级测试,可能会涉及到 5.5 与5.6 的结果对比, 这里可能会引起疑问.

这是正常的. 如果确实需要严格的排序,实现5.6 跟5.5 同样的显示结果.

需要修改sql 语句, 在排序子句中加上一个唯一字段.

参考:

https://bugs.mysql.com/bug.php?spm=5176.100239.blogcont27649.4.tlI76p&id=72076

http://didrikdidrik.blogspot.co.uk/