MySQL 5.6 的排序進(jìn)行了優(yōu)化.  同樣的sql , 在5.5 跟5.6 上可能得到不同的結(jié)果:


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 兩個(gè)版本對(duì)排序的實(shí)現(xiàn): 
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 標(biāo)準(zhǔn)里對(duì)于重復(fù)值的排序字段, 應(yīng)該出哪行記錄是沒有定義的,  這就完全取決于代碼的實(shí)現(xiàn)了. 




我們的升級(jí)測(cè)試,可能會(huì)涉及到 5.5 與5.6 的結(jié)果對(duì)比,  這里可能會(huì)引起疑問. 


這是正常的. 如果確實(shí)需要嚴(yán)格的排序,實(shí)現(xiàn)5.6 跟5.5 同樣的顯示結(jié)果. 


需要修改sql 語句, 在排序子句中加上  一個(gè)唯一字段. 





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

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




本文題目：關(guān)于mysql5.6的排序問題.
當(dāng)前地址：http://weahome.cn/article/posped.html