mysql实战优化之二:limit优化(大表翻页查询时) sql优化

mysql的表test中有20105119行数据。
建立索引:data_status,place_cargo_status

场景1:

SELECT
id,
resource_id,
resource_type,
...
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',arrive_work_day,send_work_day,1,cargo_arrive_time),
load_zone_code,
cargo_send_batch,
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',send_work_day,send_work_day,2,cargo_arrive_time),
cargo_arrive_next_batch,
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',send_work_day,arrive_next_work_day,3,cargo_arrive_time),
next_zone_code,
...
FROM
test
WHERE
data_status=1 and place_cargo_status=1
LIMIT 0,10000

结果:查询时间为:7.360s

场景1:

SELECT
id,
resource_id,
...
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',arrive_work_day,send_work_day,1,cargo_arrive_time),
load_zone_code,
cargo_send_batch,
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',send_work_day,send_work_day,2,cargo_arrive_time),
cargo_arrive_next_batch,
F_OMCS_LINK_GET_DAY_BY_WORKDAY('2017-03-13',send_work_day,arrive_next_work_day,3,cargo_arrive_time),
...
FROM
test
WHERE
data_status=1
LIMIT 0,10000

结果:查询时间为:7.111s

场景三:

select * from test
WHERE
data_status=1 and place_cargo_status=1
LIMIT 0,10000

结果:查询时间为0.141s
场景四:

select * from test
WHERE
data_status=1
LIMIT 0,10000

查询时间为0.140s

 

查看执行计划:

场景四的执行计划:

优化一:

如上type=all,是因为data_status是varchar型的,为其加单引号后,如下:

 

优化二:使用主键翻页,

 

 测试结果如下:

select * from tt_lk_place_cargo where data_status='1' and id between  20000000 and 20030000;

结果:使用时间0.381s

 

 

posted on 2016-01-24 00:06  duanxz  阅读(718)  评论(0编辑  收藏  举报