记一次主键查询乱序

前言

今天日常开发中，一条简单的不能再简单的查询，却莫名出现了主键乱序的情况，虽然加了order by之后完美解决，但是还是想究其原因。

复现一下

表结构：

CREATE TABLE `tb_test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `room_id` int NOT NULL,
  `user_id` int NOT NULL,
  `amount` decimal(10, 2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `tb_test_room_id_user_id_index`(`room_id`, `user_id`) USING BTREE
);

插入数据(多插入几条数据，确保使用索引比全表扫描快，这样才会使用到索引 )

INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (1, 1, 1, 99.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (2, 1, 4, 80.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (3, 1, 2, 28.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (4, 2, 2, 77.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (5, 2, 1, 66.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (6, 2, 5, 98.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (7, 3, 1, 55.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (8, 3, 3, 98.00);
INSERT INTO `tb_test` (`id`, `room_id`, `user_id`, `amount`) VALUES (9, 3, 2, 66.00);

大概就构成了这样一个表

id	room_id	user_id	amount
1	1	1	99.00
2	1	4	80.00
3	1	2	28.00
4	2	2	77.00
5	2	1	66.00
6	2	5	98.00
7	3	1	55.00
8	3	3	98.00
9	3	2	66.00

执行SQL查询room_id为1的记录SELECT * FROM tb_test where room_id = 1，根据猜想，查询出来的记录应该为：

id	room_id	user_id	amount
1	1	1	99.00
2	1	4	80.00
3	1	2	28.00

然而实际上是：

id	room_id	user_id	amount
1	1	1	99.00
3	1	2	28.00
2	1	4	80.00

可以看出，查询的时候主键乱序了，查询的结果是根据user_id排序的，而非安装原来的主键排序

猜想

tb_test以room_id和user_id建立了索引，查询的时候使用了room_id = 1的查询条件，复合索引的最左匹配原则，所以使用了索引tb_test_room_id_user_id_index进行查询。

证明

使用EXPLAIN 命令分析一下上述SQL：EXPLAIN SELECT * FROM tb_test where room_id = 1

根据结果可知，确实使用了索引tb_test_room_id_user_id_index，而索引中，默认使用room_id和user_id进行排序，所以就有了主键乱序的情况。

解决

如文章开头所讲，添加order by即可，在所有你期望有顺序的查询中，都应该加上order by来进行排序，以避免上述情况。