前言
order by排序是我们在MySQL中查询数据时非常常用的语句,本篇文章我们将了解order by的执行过程以及如何优化,在此之前,大家需要对索引有所了解,不了解的朋友可以参考以前写的文章
MySQL优化一:索引原理
MySQL优化二:索引B+树能放多少索引指针?能放多少数据
建表及准备数据
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`c1` int(11) NULL DEFAULT NULL,
`c2` int(11) NULL DEFAULT NULL,
`c3` int(11) NULL DEFAULT NULL,
`c4` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_c1c2c3`(`c1`, `c2`, `c3`) USING BTREE,
UNIQUE INDEX `uk_c4`(`c4`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES (1, 1, 1, 1, 4);
INSERT INTO `t1` VALUES (2, 2, 1, 1, 5);
INSERT INTO `t1` VALUES (3, 2, 1, 2, 6);
INSERT INTO `t1` VALUES (4, 2, 2, 2, 7);
INSERT INTO `t1` VALUES (5, 2, 2, 3, 8);
INSERT INTO `t1` VALUES (6, 2, 3, 3, 9);
INSERT INTO `t1` VALUES (7, 4, 4, 3, 10);
INSERT INTO `t1` VALUES (8, 4, 4, 4, 11);
INSERT INTO `t1` VALUES (9, 5, 3, 4, 12);
INSERT INTO `t1` VALUES (10, 6, 4, 4, 13);