How to optimize the problem of slow query due to excessive data?

This article is participating in the Java Theme Month – Java Debug Notes event. For more information, see The Digg Project Java Theme Month – Java Debug Notes

Business scenarios:

T_ql_case primary table with 792,699 entries (790,000 entries), T_SX_SERVICE secondary table with 82,651 entries (80,000 entries), and T_SYS_ORGAN secondary table with 364 entries

Performance issues

Pagehleper The query speed of the pagination tool is too slow. The SQL query takes 120 seconds

The view + index solution is used to optimize SQL queries for this performance problem

Initially, SQL queries use inline join queries

SELECT
	`c`.`CASE_OID` AS `CASE_OID`,
	`c`.`CASE_NUMBER` AS `CASE_NUMBER`,
	`c`.`APPLY_PROJECT_NAME` AS `APPLY_PROJECT_NAME`,
	`c`.`APPLY_USER_NAME` AS `APPLY_USER_NAME`,
	`c`.`CASE_STATUS` AS `CASE_STATUS`,
	`c`.`APPLY_USER_TYPE` AS `APPLY_USER_TYPE`,
	`c`.`CREDENTIAL_NUMBER` AS `CREDENTIAL_NUMBER`,
	`c`.`ACCEPTANCE_DATE` AS `ACCEPTANCE_DATE`,
	`o`.`CODE` AS `CODE`,
	`o`.`NAME` AS `NAME`
FROM
	(
		(
			`t_ql_case` `c`
			JOIN `t_sx_service` `s`
		)
		JOIN `t_sys_organ` `o`
	)
WHERE
	(
		(
			`c`.`SERVICE_OID` = `s`.`SERVICE_OID`
		)
		AND (`c`.`ORGAN_OID` = `o`.`OID`)
		AND (`c`.`DEL_FLAG` = 'N'))Copy the code

Change to left-join query and design the three-table union query as view v_my_affairs

SELECT
	`c`.`CASE_OID` AS `CASE_OID`,
	`c`.`CASE_NUMBER` AS `CASE_NUMBER`,
	`c`.`APPLY_PROJECT_NAME` AS `APPLY_PROJECT_NAME`,
	`c`.`APPLY_USER_NAME` AS `APPLY_USER_NAME`,
	`c`.`CASE_STATUS` AS `CASE_STATUS`,
	`c`.`APPLY_USER_TYPE` AS `APPLY_USER_TYPE`,
	`c`.`CREDENTIAL_NUMBER` AS `CREDENTIAL_NUMBER`,
	`c`.`ACCEPTANCE_DATE` AS `ACCEPTANCE_DATE`,
	`o`.`CODE` AS `CODE`,
	`o`.`NAME` AS `NAME`
FROM
	t_ql_case c
LEFT JOIN t_sx_service s ON c.SERVICE_OID = s.SERVICE_OID
LEFT JOIN t_sys_organ o ON c.ORGAN_OID = o.OID
WHERE
	c.DEL_FLAG = 'N'Note: OID and SERVICE_OID have primary key indexesCopy the code

Add normal indexes to the three table query criteria

alter table t_ql_case  add INDEX DEL_FLAG(DEL_FLAG);
alter table t_ql_case  add INDEX ORGAN_OID(ORGAN_OID);
alter table t_ql_case  add INDEX SERVICE_OID(SERVICE_OID);
Copy the code

Query whether indexes of the t_QL_CASE table are added successfully

Use explain query execution plans to compare execution plans before and after adding indexes

Optimize the query type based on system>const>eq_ref>ref>range>index>ALL. In general, the query type should reach at least range level and preferably ref level

Compare the view performance of the inner connection and the left connection

Added index:

Inline view execution plan:

Left connected view execution plan:

Production environment:

Linux (running memory: 16GB), Mariadb (version 5.5)

The execution time of the inner connection view is 4.386s, and the number of data items is 674708

The execution time of the left link view is 4.523 seconds, and the number of data items is 753680

Add ORDER BY from v_my_affairs (internal join) view, execute time 8.666s, record number 674708:

Before and after ORDER BY was added: 4.28s was added, and the query time was doubled

The execution time after adding order BY in the left connected view is 8.430s, and the number of records is 753680

Before and after order BY was added: 3.907s was added, the query time was nearly doubled, and the query time was less than the internal join time

Request the production environment interface

It takes 1min48 seconds for an interface to complete a request before performance optimization, and 7.54 seconds for an interface to complete a request after internal connection view performance optimization

It takes 7.35 seconds for the interface to complete the request after the performance optimization in the left-link view is adopted

conclusion

The use of inline view, will lead to data leakage, so the left join view + index first SQL query