???????
????????????????е?????????????MySQL?????????????Order by??伴??????????????????????????????????Order by???Group by???Distinct??????????????????????????????SQL??????????????????????????????MySQL??????????????????????????????????????????????“???”??????????????????????????????????????????????
????1.????????????????
??????????SQL??????????????????????????????????????????????????????????????????????????????????????????????潨?????????????????????????????????????SQL??????????????????Щ?????SQL???????ЩSQL??????????????????????ЩSQL?????????t1?????????key1(key_part1??key_part2)??key2(key2)
????a.?????????????????????SQL
????SELECT * FROM t1 ORDER BY key_part1??key_part2;
????SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
????SELECT * FROM t1 WHERE key_part1 > constant ORDER BY key_part1 ASC;
????SELECT * FROM t1 WHERE key_part1 = constant1 AND key_part2 > constant2 ORDER BY key_part2;
????b.?????????????????????SQL
????//????????????????У???????????????
????SELECT * FROM t1 ORDER BY key_part1??key_part2?? key2;
????//???????????????????????￡???????????????
????SELECT * FROM t1 ORDER BY key_part2?? key_part1;
????//????????￡???????????????
????SELECT * FROM t1 ORDER BY key_part1 DESC?? key_part2 ASC;
????//key_part1???Χ?????key_part2??????????????
????SELECT * FROM t1 WHERE key_part1> constant ORDER BY key_part2;
????2.??????????
????????????????????????????SQL????????ò?????????????????????????????SQL????м???л????“Using filesort”???????????????filesort??????ζ????????????????п????????????????????sort_buffer_size????????????С?????MySQL???????????????3?????????????????????????????????????漰3???????????????????鰱??????????????????SQL??????￡?
????CREATE TABLE t1(id int?? col1 varchar(64)?? col2 varchar(64)?? col3 varchar(64)?? PRIMARY KEY(id)??key(col1??col2));
????SELECT col1??col2??col3 FROM t1 WHERE col1>100 ORDER BY col2;
????a.????????
????(1).???t1?л??????WHERE????????
????(2).???????????????????????+?????(id??col2)???????sort buffer
????(3).???sort buffer????????????????????(id??col2)????????????????sort buffer????????????????????????С?(??????????????????????)
????(4).???????в???????????????????ù鰱??????????????????м?????????
????(5).?????????????????????????????????????????????
????(6).?????????(id??col2)?????????id????SELECT??????????(col1??col2??col3)
????(7).?????????????????????
??????????????????????????????????????sort buffer?????????????????(id??col2)??????buffer???С??sort_buffer_size???????????????????????????IO?????????(id??col2)???????????(col1??col2??col3)?????????????????col2???????id????????????????id???(col1??col2??col3)???????????????IO??????????MySQL?????????????????????????id???????????????????????????С?????read_rnd_buffer_size????????????????????????IO?????IO??
????b.???????
?????????????????????????????????????IO??????????????????????????????????IO??????????????????sort buffer????(id??col2)??????(col1??col2??col3)??????sort buffer?а??????????????????Σ?????????????????????????????????????????????????????????С??sort buffer???????(col1??col2??col3)????С??(id??col2)?????sort buffer????????????????д???????????????IO?????MySQL???????max_length_for_sort_data????е????????С??max_length_for_sort_data?????????????????????????????ó??????????
????c.???????????
???????????????????????????????????????????????????????????????????????????????????????????????????????5.6?汾???Order by limit M??N?????????????????????????????μ??????--??????У????????????????????????????????????????limit M??N ????????????????????????????????????????????M+N??????sort buffer??伴???????M??N??С??????????????????sort buffer??????????????????????й鰱?????????????????????????????е?????????С??N?????????????????С?????????е???????????N??????
????3.???????????
????????1
????Mysql??5.5????5.6?????????????????????
????????????????
????create table t1(id int primary key?? c1 int?? c2 varchar(128));
????insert into t1 values(1??1??'a');
????insert into t1 values(2??2??'b');
????insert into t1 values(3??2??'c');
????insert into t1 values(4??2??'d');
????insert into t1 values(5??3??'e');
????insert into t1 values(6??4??'f');
????insert into t1 values(7??5??'g');