首页 > 代码库 > 分页优化一例
分页优化一例
ndb引擎 每次都要orderby来保持顺序
user_id是索引列
原始的代码
SELECT
sc_user_id userId,
sc_user_nm userName,
sc_user_sex userSex,
sc_user_real_name userRealName,
sc_mobile_no mobileNo,
email email,
scL_register_dts registerDts,
scL_account_balance accountBalance,
scL_recharge_amt rechargeAmt,
scL_invest_amt investAmt,
scX_redemption_amt redemptionAmt,
scx_freeze_amt freezeAmt,
scx_freeze_amtf withdrawAmt,
create_dtss createDts,
update_dtstime updateDts
FROM tbL_custdserv_7user
WHERE update_dtstime >= CONCAT(‘20141021‘,‘000000‘)
ORDER BY sc_user_nm
LIMIT 35950,50;
执行时间 7.45秒
========================================================================
优化后代码
SELECT
a.sc_user_id userId,
a.sc_user_nm userName,
a.sc_user_sex userSex,
a.sc_user_real_name userRealName,
a.sc_mobile_no mobileNo,
a.email email,
a.scL_register_dts registerDts,
a.scL_account_balance accountBalance,
a.scL_recharge_amt rechargeAmt,
a.scL_invest_amt investAmt,
a.scX_redemption_amt redemptionAmt,
a.scx_freeze_amt freezeAmt,
a.scx_freeze_amtf withdrawAmt,
a.create_dtss createDts,
a.update_dtstime updateDts
FROM tbL_custdserv_7user a
JOIN
(SELECT
sc_user_id,
sc_user_nm
FROM tbL_custdserv_7user
WHERE update_dtstime >= CONCAT(‘20141021‘,‘000000‘)
ORDER BY sc_user_nm
LIMIT 35950,50) b
ON a.sc_user_id = b.user_id
执行时间 0.2秒
尝试了 on a.sc_user_nm = b.sc_user_nm 失败
本文出自 “深山有dba” 博客,请务必保留此出处http://19801021.blog.51cto.com/7262596/1567147
分页优化一例