数据量过多，如何优化查询过慢问题？

本文正在参加「Java主题月 – Java Debug笔记活动」，详情查看掘力计划 Java 主题月 – Java Debug笔记

业务场景：

t_ql_case为主表，表数据量有792699条（79万），t_sx_service表为副表，表数据量为82651条（8万），t_sys_organ表为副表，表数据量有364条

性能问题

pagehleper分页工具查询速度过慢，单单SQL查询耗时为120秒

针对这个性能问题采用视图+索引解决方案来优化SQL查询

最初SQL查询使用内连接查询

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')
	)
复制代码

修改为左连接查询并把三表联合查询设计成视图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'
        
 备注：OID、SERVICE_OID已有主键索引
复制代码

将三个表查询条件添加普通索引

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);
复制代码

查询t_ql_case表相关字段添加索引是否成功