关于mysql5.6 的排序问题
发布时间：2022-03-29 06:11:54 所属栏目：编程 来源：互联网
导读：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` big
|
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; +----+--------+----------------------+----------------+ | id | aValue | accuracyClassType_id | productType_id | +----+--------+----------------------+----------------+ | 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; +----+--------+----------------------+----------------+ | id | aValue | accuracyClassType_id | productType_id | +----+--------+----------------------+----------------+ | 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; +----+--------+----------------------+----------------+ | id | aValue | accuracyClassType_id | productType_id | +----+--------+----------------------+----------------+ | 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; +----+--------+----------------------+----------------+ | id | aValue | accuracyClassType_id | productType_id | +----+--------+----------------------+----------------+ | 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 语句, 在排序子句中加上 一个唯一字段. （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
