发表文章

[最新] mysql查询很慢优化方法1

zengmingen 3月前 1

解决方法:

关联的字段建索引。

具体分析如下:


举例:

表格:培训学生表,班级报名表

需求:查询出学生报了哪些班级

两表有个关联字段“CD”(学生学号)。

视图sql:

SELECT
	`t_px_stu`.`PX_STU_PK` AS `PX_STU_PK`,
	`t_px_stu`.`SYS_USER_PK` AS `SYS_USER_PK`,
	`t_px_stu`.`CD` AS `CD`,
	`t_px_stu`.`NM` AS `NM`,
	`t_px_stu`.`MOB` AS `MOB`,
	`t_px_stu`.`PHONE` AS `PHONE`,
	`t_px_stu`.`KINS_PHONE` AS `KINS_PHONE`,
	`t_px_stu`.`SEX` AS `SEX`,
	`t_px_stu`.`AGE` AS `AGE`,
	`t_px_stu`.`ADDRESS` AS `ADDRESS`,
	`t_px_stu`.`ID_CARD` AS `ID_CARD`,
	`t_px_stu`.`POLI_CD` AS `POLI_CD`,
	`t_px_stu`.`POLI_NM` AS `POLI_NM`,
	`t_px_stu`.`EDU_CD` AS `EDU_CD`,
	`t_px_stu`.`EDU_NM` AS `EDU_NM`,
	`t_px_stu`.`NATU_CD` AS `NATU_CD`,
	`t_px_stu`.`NATU_NM` AS `NATU_NM`,
	`t_px_stu`.`IMG_URL` AS `IMG_URL`,
	`t_px_stu`.`RMKS` AS `RMKS`,
	`t_px_stu`.`CAT_CD` AS `CAT_CD`,
	`t_px_stu`.`CAT_NM` AS `CAT_NM`,
	`t_px_stu`.`STAT_CD` AS `STAT_CD`,
	`t_px_stu`.`STAT_NM` AS `STAT_NM`,
	`t_px_stu`.`CRT_TM` AS `CRT_TM`,
	`t_px_stu`.`CRT_BY` AS `CRT_BY`,
	`t_px_stu`.`UPD_TM` AS `UPD_TM`,
	`t_px_stu`.`UPD_BY` AS `UPD_BY`,
	`t_px_stu`.`EDIT_FLAG` AS `EDIT_FLAG`,
	`t_px_clazz_apply`.`ARG1` AS `ARG1`
FROM
		`t_px_stu` LEFT JOIN `t_px_clazz_apply` ON
				`t_px_stu`.`CD` =`t_px_clazz_apply`.`CD`

ORDER BY
	`t_px_stu`.`CRT_TM` DESC

执行时间:37.621秒

查看sql解析


t_px_stu表查询了6167次,这是没办法的,它是主表,需要逐条去查。

t_px_clazz_apply 查询了22127次,这是需要优化的。这张表总共才22458行,优化的地方找到了。

优化方法:

给t_px_clazz_apply 的cd字段建索引。

create index t_px_clazz_apply_cd_index on t_px_clazz_apply(cd);


再执行,执行时间:0.187秒

查看sql解析:



t_px_stu表查询了6167次,这是没办法的,它是主表,需要逐条去查。

t_px_clazz_apply 查询了3次,索引起作用了。


相关推荐
最新评论 (0)
返回
发表文章
zengmingen
文章数
642
评论数
0
注册排名
567157