MySQL order by 排序结果不正确

MySQL order by 排序结果不正确新建一张测试表：CREATE TABLE `tb1` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `a` decimal(19,2) NOT NULL,   `acid` bigint(20) NOT NULL,   `prid` bigint(20) NOT NULL,   PRIMARY KEY (`id`),   KEY `idx_prid` (`prid`),   KEY `idx_acid` (`acid`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;字段 a 没有索引，插入测试数据：INSERT INTO `tb1` (`id`, `a`, `acid`, `prid`) 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);查看表数据：([yoon]> select * from tb1;+----+-------+------+------+| id | a     | acid | prid |+----+-------+------+------+|  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 |+----+-------+------+------+根据非索引字段且有重复数据的字段 a 进行 order by 排序：([yoon]> select * from tb1 order by a desc ;+----+-------+------+------+| id | a     | acid | prid |+----+-------+------+------+| 10 | 20.00 |    2 |    3 ||  9 | 16.00 |    2 |    3 || 14 | 12.00 |    2 |    4 ||  8 | 12.00 |    2 |    3 || 13 | 10.00 |    2 |    4 ||  7 | 10.00 |    2 |    3 || 12 |  8.00 |    2 |    4 ||  6 |  8.00 |    2 |    3 || 11 |  6.00 |    2 |    4 || 16 |  6.00 |    2 |    2 ||  5 |  6.00 |    2 |    3 ||  4 |  5.00 |    2 |    3 || 15 |  5.00 |    2 |    2 ||  3 |  4.00 |    2 |    3 ||  2 |  3.00 |    3 |    2 ||  1 |  2.00 |    3 |    2 |+----+-------+------+------+order by 和 limit 一起使用：([yoon]> select * from tb1 order by a desc limit 4;+----+-------+------+------+| id | a     | acid | prid |+----+-------+------+------+| 10 | 20.00 |    2 |    3 ||  9 | 16.00 |    2 |    3 || 14 | 12.00 |    2 |    4 ||  8 | 12.00 |    2 |    3 |+----+-------+------+------+为 a 字段添加索引：([yoon]> alter table tb1 add index idx_a(a);Query OK, 0 rows affected (0.05 sec)([yoon]> select * from tb1 order by a desc limit 4;+----+-------+------+------+| id | a     | acid | prid |+----+-------+------+------+| 10 | 20.00 |    2 |    3 ||  9 | 16.00 |    2 |    3 || 14 | 12.00 |    2 |    4 ||  8 | 12.00 |    2 |    3 |+----+-------+------+------+排序的时候再添加一个字段 id ：([yoon]> select * from tb1 order by a desc,id desc limit 4;+----+-------+------+------+| id | a     | acid | prid |+----+-------+------+------+| 10 | 20.00 |    2 |    3 ||  9 | 16.00 |    2 |    3 || 14 | 12.00 |    2 |    4 ||  8 | 12.00 |    2 |    3 |+----+-------+------+------+对于一个非唯一字段，无论是否含有索引，结果集都是不确定的。如果业务逻辑对分页或者order by结果集有比较高的严格要求 ，请记得利用唯一键排序。