Mybatis-PageHelper自定义count查询

1、问题描述

使用分页插件pagehelper实现分页功能，有个接口查询速度慢。

2、问题原因

排查问题发现是因为在分页时，查询总条数的sql执行慢，而查询总条数的sql是在分页插件执行时，在主sql外层嵌套一层得到：SELECT COUNT(*) FROM (主sql) table_count；

主SQL：

SELECT
	a.id,
	ent_name,
	a.ent_org_code,
	ent_org_file,
	ent_org_file_name,
	a.ent_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 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 ON a.ent_org_code = q.ent_org_code
WHERE
	a.audit_flag = 1
AND a.history_flag = 0
GROUP BY
	a.ent_org_code

分页插件查询总条数时拼接的SQL：

SELECT
	count(0)
FROM
	(
		SELECT
			a.id,
			ent_name,
			a.ent_org_code,
			ent_org_file,
			ent_org_file_name,
			a.ent_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 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 ON a.ent_org_code = q.ent_org_code
		WHERE
			a.audit_flag = 1
		AND a.history_flag = 0
		GROUP BY
			a.ent_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查询语句。
参考：PageHelper增加手写 count 查询支持
最终使用自定义的count查询SQL：

SELECT
	count(0)
FROM
	(
		SELECT
			0
		FROM
			ent_basic_info a
		INNER JOIN ent_qualifi_info q ON a.ent_org_code = q.ent_org_code
		WHERE
			a.audit_flag = 1
		AND a.history_flag = 0
		GROUP BY
			a.ent_org_code
	) table_count;

结果：原先由插件拼接的count查询sql时间为1.4s左右，使用自定义的count查询sql时间在0.4s左右。