使用Mysql的索引进行排序可以大大提高Mysql的性能,使用索引排序的原则是
按照建立索引的顺序查询和排序,执行如下sql
create table t1(
id int not null auto_increment,
uname varchar(32) not null,
tag1 int not null,
tag2 int not null,
tag3 int not null,
PRIMARY KEY(id)
)
create index idx_t1_tag1_tag2_tag3 on t1(tag1,tag2,tag3)
insert into t1(uname,tag1,tag2,tag3)
values
('aaa',11,23,33),
('bbb',12,2,99),
('ccc',9,3,999),
('ddd',99,99,99),
('eee',199,199,199),
('fff',299,299,299),
('ggg',99,19,9),
('hhh',1,4,12),
('jjj',1,3,121),
('kkk',1,3,16),
('lll',1,2,12)
可以看到t1表有一个复合索引,tag1、tag2、tag3,所谓的按照索引顺序查询和排序指在查询即select列表中按照tag1->tag2->tag3的顺序查询,order by之后一样
explain select tag1,tag2 from t1 ORDER BY tag1,tag2
如果是where和order by 组合起来后字段依然是“有序”的,那么依然会使用索引排序
explain select tag1,tag2 from t1 where tag1 = 1 ORDER BY tag2,tag3