最近想留出一些时间对系统中的一些sql语句做一下优化,包括索引的优化。在此贴出一些测试sql 和测试结果。
#mysql5.1
#表中数据100000条
#表t_group_topic.id 自增主键
#以下通过不同的查询方式 和 在created_at 有无索引测试结果
select * from t_group_topic order by created_at desc limit 0,40
taking 312ms no index
taking 0.9ms yes index
select * from t_group_topic order by created_at desc limit 50000,40
taking 627ms no index
taking 624ms yes index
select * from t_group_topic order by created_at desc limit 90000,40
taking 937 ms no index
taking 956 ms yes index
select x.* from t_group_topic x inner join
(select id from t_group_topic order by created_at desc limit 0,40 ) y on x.id = y.id
taking 316 ms no
taking 0.8ms yes
m t_group_topic x inner join
(select id from t_group_topic order by created_at desc limit 50000,40) y on x.id = y.id
taking 312ms no
taking 19.5ms yes
select x.* from t_group_topic x inner join
(select id from t_group_topic order by created_at desc limit 90000,40) y on x.id = y.id
taking 312 ms no
taking 30.2ms yes
从以上信息可以看出,大数据分页查询时最好先过滤要查询的id 再用id做查询条件进行查询。这样可以充分的利用索引。
对order by 关键字构建适当的索引,可以大大提高查询速度。