配置分页后查询效率巨低

[rationalol]

Description

环境win11
jdk20
APIJSON7
MYSQL 5.7

在日志中打印出的SQL语句如下

SELECT
	* 
FROM
	(
	SELECT
		`Call_record`.*,
		`Sale_customer`.`phone_number` AS `contract_number`,
		`Sale_customer`.`company_name`,
		`Sale_customer`.`rank`,
		`Sale_customer`.`notice`,
		`Sale_customer`.`region`,
		`Temp_salesperson`.`created_at` AS `join_date`,
		`Temp_salesperson`.`name`,
		`Temp_salesperson`.`team` 
	FROM
		`herui`.`Call_record` AS `Call_record`
		INNER JOIN `herui`.`Sale_customer` AS `Sale_customer` ON `Sale_customer`.`phone_number` = `Call_record`.`phone_number`
		INNER JOIN `herui`.`Temp_salesperson` AS `Temp_salesperson` ON `Temp_salesperson`.`salesperson_id` = `Call_record`.`operator_id` 
	) AS `Call_record` 
	LIMIT 20

执行时间是0.187s

[rationalol]

@TommyLemon

[TommyLemon]

@rationalol 只要是查数组都一定分页，query:2 导致多了 count 查询，你用子查询本身也会降低效率。
可以 @Explain: true 或者控制台看 SQL 执行日志，把 SQL 直接放数据库执行试试
https://github.com/Tencent/APIJSON/blob/master/Document.md#3.2

[rationalol]

因为配置了分页所以他会进行一次COUNT查询,这个COUNT查询在数据库中查询时间很长,导致拖垮了这个接口的整体性能

SELECT
	count(*) AS count 
FROM
	(
	SELECT
		`Call_record`.*,
		`Sale_customer`.`phone_number` AS `contract_number`,
		`Sale_customer`.`company_name`,
		`Sale_customer`.`rank`,
		`Sale_customer`.`notice`,
		`Sale_customer`.`region`,
		`Sale_customer`.`legal_person`,
		`Temp_salesperson`.`created_at` AS `join_date`,
		`Temp_salesperson`.`name`,
		`Temp_salesperson`.`team` 
	FROM
		`herui`.`Call_record` AS `Call_record`
		INNER JOIN `herui`.`Sale_customer` AS `Sale_customer` ON `Sale_customer`.`phone_number` = `Call_record`.`phone_number`
	INNER JOIN `herui`.`Temp_salesperson` AS `Temp_salesperson` ON `Temp_salesperson`.`salesperson_id` = `Call_record`.`operator_id` 
	) AS `Call_record`

解决方案也很简单 数据库加个索引就完事了

CREATE INDEX idx_call_record_phone_number ON `herui`.`Call_record` (`phone_number`);
CREATE INDEX idx_sale_customer_phone_number ON `herui`.`Sale_customer` (`phone_number`);

[TommyLemon]

像移动端 APP/H5 网页的列表/网格，一般是上滑加载更多，不需要返回总数等信息，查数据就行了，查不到数据就知道是最后一页了。query 不传或传 0 就不会有 SQL count 查询了