Mybatis-PageHelper⾃定义count查询
1、问题描述
使⽤分页插件pagehelper实现分页功能，有个接⼝查询速度慢。
2、问题原因
排查问题发现是因为在分页时，查询总条数的sql执⾏慢，⽽查询总条数的sql是在分页插件执⾏时，在主sql外层嵌套⼀层得到：SELECT COUNT(*) FROM (主sql) table_count；
主SQL：
SELECT
a.id,
ent_name,
<_org_code,
ent_org_file,
ent_org_file_name,
<_unit_class,
ent_unit_type,
ent_establish_date,
ent_address,
ent_provence,
ent_city,
ent_county,
ent_regist_capital,
ent_busi_license_num,
ent_busi_license_file,
ent_busi_license_file_name,
ent_economic_type,
ent_account_bank,
ent_legal_rep,
ent_res_person,
ent_tech_director,
ent_charge_departm,
ent_approval_departm,
ent_contact_man,
ent_contact_tel,
ent_remark,
ent_in_out,
ent_construction_type,
website,
email,
ent_belong_to,
'' AS ent_brief_introduction,
'' AS pic1,
'' AS pic_name1,
'' AS pic2,
'' AS pic_name2,
a.audit_flag,
a.history_flag,
disable_flag,
warning_flag,
loginflag,
is_local_ent,
is_city_in,
'' AS pic3,
'' AS pic_name3,
'' AS pic4,
'' AS pic_name4,
'' AS pic5,
'' AS pic_name5,
'' AS pic6,
'' AS pic_name6,
'' AS pic_name6,
'' AS pic7,
'' AS pic_name7,
'' AS pic8,
'' AS pic_name8,
'' AS pic9,
'' AS pic_name9,
postal_code,
id_card_type_num,
legal_man_idcard,
legal_man_duty,
legal_man_pro_title,
fact_reg_prin,
principal_unit_num,
office_phone,
fax,
is_proxy_enterprise,
is_new_supervisor,
is_cost_enterprise,
0 AS hasBlackList
FROM
ent_basic_info a
INNER JOIN ent_qualifi_info q _org_code = q.ent_org_code WHERE
a.audit_flag = 1
AND a.history_flag = 0
GROUP BY
<_org_code
分页插件查询总条数时拼接的SQL：
SELECT
count(0)
分页查询插件
FROM
(
SELECT
a.id,
ent_name,
<_org_code,
ent_org_file,
ent_org_file_name,
<_unit_class,
ent_unit_type,
ent_establish_date,
ent_address,
ent_provence,
ent_city,
ent_county,
ent_regist_capital,
ent_busi_license_num,
ent_busi_license_file,
ent_busi_license_file_name,
ent_economic_type,
ent_account_bank,
ent_legal_rep,
ent_res_person,
ent_tech_director,
ent_charge_departm,
ent_approval_departm,
ent_contact_man,
ent_contact_tel,
ent_remark,
ent_in_out,
ent_construction_type,
website,
email,
email,
ent_belong_to,
'' AS ent_brief_introduction,
'' AS pic1,
'' AS pic_name1,
'' AS pic2,
'' AS pic_name2,
a.audit_flag,
a.history_flag,
disable_flag,
warning_flag,
loginflag,
is_local_ent,
is_city_in,
'' AS pic3,
'' AS pic_name3,
'' AS pic4,
'' AS pic_name4,
'' AS pic5,
'' AS pic_name5,
'' AS pic6,
'' AS pic_name6,
'' AS pic7,
'' AS pic_name7,
'' AS pic8,
'' AS pic_name8,
'' AS pic9,
'' AS pic_name9,
postal_code,
id_card_type_num,
legal_man_idcard,
legal_man_duty,
legal_man_pro_title,
fact_reg_prin,
principal_unit_num,
office_phone,
fax,
is_proxy_enterprise,
is_new_supervisor,
is_cost_enterprise,
0 AS hasBlackList
FROM
ent_basic_info a
INNER JOIN ent_qualifi_info q _org_code = q.ent_org_code
WHERE
a.audit_flag = 1
AND a.history_flag = 0
GROUP BY
<_org_code
) table_count;
可以看出拼接之后的sql最终查询结果是count，但由于主SQL查询慢，导致整个count的查询慢，在主SQL中select的列值对于整个count 查询来说没有关系，但却影响了count查询的速度，所以可以通过优化主SQL中的select列来优化count查询的sql。
但是，这个count查询的sql是由插件为我们拼接成的，我们只负责编写主SQL语句，想要优化count的sql中的select列值要相应的修改我们的主SQL，但这不符合我们相要的结果，因为我们的主sql中的select列值都是最终结果需要的。
3、解决⽅案
pagehelper的2017-08-01版本更新新增了⾃定义的count查询，⽤户可以⾃定义count查询语句，来替换插件为我们拼接的count查询语句。
参考：
最终使⽤⾃定义的count查询SQL：
SELECT
count(0)
FROM
(
SELECT
FROM
ent_basic_info a
INNER JOIN ent_qualifi_info q _org_code = q.ent_org_code
WHERE
a.audit_flag = 1
AND a.history_flag = 0
GROUP BY
<_org_code
) table_count;
结果：原先由插件拼接的count查询sql时间为1.4s左右，使⽤⾃定义的count查询sql时间在0.4s左右。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。