开发日常小结（44）：记一次现网sql执行效率优化过程

最新推荐文章于 2024-06-24 20:08:34 发布

后台技术汇

最新推荐文章于 2024-06-24 20:08:34 发布

阅读量175

点赞数

分类专栏：后台工作总结文章标签： sql sql优化

本文链接：https://blog.csdn.net/qq_29166327/article/details/103065180

版权

后台工作总结专栏收录该内容

34 篇文章 0 订阅

订阅专栏

2019年11月14日

昨晚测试提了一个bug：详情是管理后台的数据查询超时了，我重新在beta环境测试了查询sql，不出意外的复现了。

现网nginx配置了HTTP请求的超时时间为60s，就是说请求超过60s还没有返回响应，那么就会断开连接。

于是我拷贝了sql，单独拎出来分析......终于将这个坑填完了。

数据表结构准备

-- log记录表
CREATE TABLE `eh_organization_member_logs` (
  `id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'id of the record',
  `namespace_id` int(11) DEFAULT '0',
  `organization_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL COMMENT 'organization member target id (type user)',
  `contact_name` varchar(64) DEFAULT NULL,
  `contact_type` tinyint(4) DEFAULT '0' COMMENT '0: mobile, 1: email',
  `contact_token` varchar(128) DEFAULT NULL COMMENT 'phone number or email address',
  `operation_type` tinyint(4) DEFAULT '0' COMMENT '0-退出企业 1-加入企业',
  `request_type` tinyint(4) DEFAULT '0' COMMENT '0-管理员操作 1-用户操作',
  `operate_time` datetime DEFAULT NULL,
  `operator_uid` bigint(20) NOT NULL,
  `contact_description` text,
  `reject_content` text,
  `organization_member_record_id` bigint(20) DEFAULT NULL COMMENT '企业认证时，记录目标对象的eh_organization_members表记录主键id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 企业用户表
CREATE TABLE `organization_members` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id of the record',
  `organization_id` bigint(20) NOT NULL,
  `target_type` varchar(32) DEFAULT NULL COMMENT 'untrack, user',
  `target_id` bigint(20) NOT NULL COMMENT 'target user id if target_type is a user',
  `member_group` varchar(32) DEFAULT NULL COMMENT 'pm group the member belongs to',
  `contact_name` varchar(64) DEFAULT NULL,
  `contact_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: mobile, 1: email',
  `contact_token` varchar(128) DEFAULT NULL COMMENT 'phone number or email address',
  `contact_description` text,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0: inactive, 1: confirming, 2: active',
  `group_id` bigint(20) DEFAULT '0' COMMENT 'refer to the organization id',
  `employee_no` varchar(128) DEFAULT NULL,
  `avatar` varchar(2048) DEFAULT NULL,
  `group_path` varchar(128) DEFAULT NULL COMMENT 'refer to the organization path',
  `gender` tinyint(4) DEFAULT '0' COMMENT '0: undisclosured, 1: male, 2: female',
  `update_time` datetime DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `integral_tag1` bigint(20) DEFAULT NULL,
  `integral_tag2` bigint(20) DEFAULT NULL,
  `integral_tag3` bigint(20) DEFAULT NULL,
  `integral_tag4` bigint(20) DEFAULT NULL,
  `integral_tag5` bigint(20) DEFAULT NULL,
  `string_tag1` varchar(128) DEFAULT NULL,
  `string_tag2` varchar(128) DEFAULT NULL,
  `string_tag3` varchar(128) DEFAULT NULL,
  `string_tag4` varchar(128) DEFAULT NULL,
  `string_tag5` varchar(128) DEFAULT NULL,
  `namespace_id` int(11) DEFAULT '0',
  `visible_flag` tinyint(4) DEFAULT '0' COMMENT '0 show 1 hide',
  `group_type` varchar(64) DEFAULT NULL COMMENT 'ENTERPRISE, DEPARTMENT, GROUP, JOB_POSITION, JOB_LEVEL, MANAGER',
  `creator_uid` bigint(20) DEFAULT NULL,
  `operator_uid` bigint(20) DEFAULT NULL,
  KEY `fk_eh_orgm_owner` (`organization_id`),
  KEY `i_eh_corg_group` (`member_group`),
  KEY `i_target_id` (`target_id`),
  KEY `i_contact_token` (`contact_token`),
  KEY `group_type_index` (`group_type`),
  KEY `group_path_index` (`group_path`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1999984295450 DEFAULT CHARSET=utf8mb4;

业务查询sql

SELECT
	organization_member_logs.`id`,
	organization_member_logs.`namespace_id`,
	organization_member_logs.`organization_id`,
	organization_member_logs.`user_id`,
	organization_member_logs.`contact_name`,
	organization_member_logs.`contact_type`,
	organization_member_logs.`contact_token`,
	organization_member_logs.`operation_type`,
	organization_member_logs.`request_type`,
	organization_member_logs.`operate_time`,
	organization_member_logs.`operator_uid`,
	organization_member_logs.`contact_description`,
	organization_member_logs.`reject_content`,
	organization_member_logs.`organization_member_record_id`
FROM
	organization_member_logs
WHERE
	(
		organization_member_logs.`organization_id` IN (
			1054881,1055350,1055356,1055489,1055553,1055555,1055557,1055559,1055561,1055600,1055638,1056008,1056052,1056055,1056056,1056125,1056807,1056943,1056943,1055489,1056944,1056944,1055350,1056946,1056946,1056946,1056055,1056056,1056125,1056951,1056951,1056952,1056952,1056953,1056953,1056954,1056954,1056955,1056955,1056956,1056956,1056957,1056957,1056958,1056958,1056946,1056946,1056959,1056959,1056960,1056960,1056961,1056961,1056962,1056962,1056963,1056963,1056964,1056964,1056965,1056965,1056966,1056966,1056967,1056967,1056968,1056968,1056969,1056969,1056970,1056970,1056972,1056972,1056973,1056973,1056974,1056974,1056975,1056975,1056976,1056976,1056977,1056977,1056978,1056978,1056979,1056979,1056980,1056980,1056981,1056981,1056982,1056982,1056983,1056983,1056985,1056985,1056986,1056986,1056987,1056987,1056988,1056988,1056989,1056989,1056990,1056990,1056991,1056991,1056992,1056992,1056993,1056993,1056994,1056994,1056995,1056995,1056996,1056996,1056997,1056997,1056998,1056998,1056999,1056999,1057000,1057000,1057001,1057001,1057002,1057002,1057003,1057003,1057004,1057004,1057005,1057005,1057006,1057006,1057007,1057007,1057008,1057008,1057009,1057009,1057010,1057010,1057011,1057011,1057012,1057012,1057013,1057013,1057014,1057014,1057015,1057015,1057016,1057016,1057056,1057211,1057243,1057246,1057249,1058281,1058283,1058470,1058484,1058485,1058486,1058490,1058491,1058494,1058495,1058499,1058500,1058677,1058703,1058704,1058708,1058709,1058967,1059319,1059320,1059321,1059368,1059468,1059490,1059547,1059718,1059721,1059788,1059789,1059800,1059804,1059809,1059828,1059862,1059912,1060002,1060007,1060007,1060009,1060009,1060231,1060548,1060549,1060551,1060552,1060553,1060572,1060573,1060686,1060812,1060812,1060830,1060830,1060839,1060840,1060914,1060916,1061182,1061182,1061183,1061183,1061184,1061184,1061182,1061202,1061202,1061203,1061204,1061204,1061205,1061205,1061205,1061206,1061206,1061207,1061207,1061208,1061208,1061209,1061209,1061210,1061210,1057243,1061202,1061211,1061211,1061212,1061212,1061213,1061213,1061214,1061214,1061215,1061215,1061216,1061216,1061217,1061218,1061218,1060916,1060914,1060840,1060839,1060839,1060686,1060553,1060553,1060552,1060551,1060551,1060551,1061267,1061267,1061420,1061420,1061421,1061421,1061424,1061424,1061424,1060549,1060548,1060231,1060231,1060002,1059912,1059862,1059828,1058703,1058703,1061438,1061438,1061438,1061465,1061465,1061466,1061467,1061467,1061467,1061466,1061466,1057249,1061617,1061617,1061618,1061618,1061618,1061207,1061619,1061619,1062074,1062074,1062075,1062126,1062126,1062075,1062141,1062142,1062152,1062152,1056807,1056807,1062820,1062820,1063684,1063756,1063768,1063768,1063768,1063885,1063885,1063910,1063911,1063929,1063911,1063910,1063942,1063942,1064247,1064326,1064326,1064448,1064449,1064453,1064454,1064455,1064502,1064527,1064528,1064744,1064822,1064857,1064890,1065085,1065085,1064890,1065114,1065117,1065117,1065117,1065117,1065117,1065117,1065117,1065117,1065117,1065117,1065156,1065156,1065212,1065212,1065114,1065114,1065114,1065114,1065114,1065354,1065354,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065449,1065449,1065593,1065593,1065596,1065596,1065667,1065667,1065816,1065816,1065816,1065667,1065593,1065596,1065449,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065375,1065354,1065212,1065156,1065114,1065114,1065114,1065114,1065114,1065085,1064890,1064822,1064744,1064528,1064527,1064502,1064455,1064454,1064454,1064453,1064449,1064448,1064326,1064247,1064247,1064247,1064247,1063942,1063911,1063910,1063885,1063768,1063768,1063756,1064857,1063684,1062152,1062142,1062142,1062142,1062141,1062141,1062141,1062126,1062075,1062074,1061617,1061466,1061466,1061467,1061467,1061465,1061424,1061424,1061421,1061420,1062820,1060916,1061618,1061618,1060914,1060812,1060686,1060573,1060573,1060573,1060572,1060572,1060572,1060572,1060553,1060553,1060552,1060551,1060551,1060551,1060549,1060548,1060231,1060231,1060002,1059862,1059828,1059809,1059804,1059800,1059800,1059800,1059800,1059800,1059800,1059800,1059789,1059788,1059788,1059721,1059721,1059721,1059547,1059547,1059547,1059547,1059547,1059547,1059490,1059468,1059368,1061438,1061438,1059321,1059320,1059319,1058967,1058703,1058703,1058500,1058499,1058499,1058495,1058495,1058494,1058494,1058491,1058490,1058490,1058486,1058485,1058484,1058484,1061267,1058470,1058709,1058709,1058709,1058704,1056993,1061204,1061205,1056943,1056943,1056943,1056943,1056943,1056943,1056943,1056943,1056943,1056943,1056943,1057243,1061206,1056807,1056807,1061207,1061208,1061209,1061209,1058708,1058708,1056008,1057211,1057211,1057246,1056125,1056125,1061210,1061184,1061619,1056056,1056056,1056055,1056055,1057249,1056052,1061202,1061202,1061202,1061211,1061212,1061213,1060009,1060009,1060007,1060007,1060007,1060007,1060007,1061183,1061214,1061214,1061182,1061215,1061215,1061216,1061216,1061216,1061218,1061218,1055489,1055489,1055489,1055489,1055350,1055350,1065980,1065980,1065980,1066019,1066019
		)
		AND organization_member_logs.`operation_type` = 1
		AND EXISTS (
			SELECT
				1 AS `one`
			FROM
				organization_members
			WHERE
				(
					organization_members.`target_id` = organization_member_logs.`user_id`
					AND organization_members.`organization_id` = organization_member_logs.`organization_id`
					AND organization_members.`target_type` = 'USER'
					AND organization_members.`status` <> 0
					AND organization_members.`status` <> 4
				)
		)
	)
ORDER BY
	organization_member_logs.`operate_time` DESC
LIMIT 11 OFFSET 0;

现网执行效果

跑出了85s的好成绩（血流不止）

优化过程

语义分析

sql的IN 元素有600+
where条件使用了EXISTS
order排序字段没有建立索引

优化一

考虑到IN的使用，使用EXISTS替代IN。耗时91.5s。

优化二

添加联合索引

ALTER TABLE organization_member_logs ADD INDEX orgid_uid ( organization_id, user_id);

但是explain后，IN语法不会使用索引，原因是用select之后使用了函数内部转换，mysql是不支持函数索引的。

添加联合索引（operation_time）之后，还是这么慢。。耗时105s

优化三

计划使用内联表的形式，直接与子查询进行组合查询，这种写法相当于IN子查询写法，而且效率有不少的提高。

SELECT
	a.`id`,
	a.`id`,
	a.`namespace_id`,
	a.`organization_id`,
	a.`user_id`,
	a.`contact_name`,
	a.`contact_type`,
	a.`contact_token`,
	a.`operation_type`,
	a.`request_type`,
	a.`operate_time`,
	a.`operator_uid`,
	a.`contact_description`,
	a.`reject_content`,
	a.`organization_member_record_id`
FROM
	`organization_member_logs` a,
	(SELECT
				member_id
			FROM
				organization_community_requests
			WHERE
				community_id = 240111044332063658
			AND member_status = 3
			AND member_type = 'organization') b
WHERE
	(
		a.organization_id = b.member_id
		AND a.`operation_type` = 1
		AND EXISTS (
			SELECT
				1 AS `one`
			FROM
				organization_members
			WHERE
				(
					organization_members.`target_id` = a.`user_id`
					AND organization_members.`organization_id` = a.`organization_id`
					AND organization_members.`target_type` = 'USER'
					AND organization_members.`status` <> 0
					AND organization_members.`status` <> 4
				)
		)
	)
ORDER BY
	a.`operate_time` DESC
LIMIT 11 OFFSET 0;

但查询结果是耗时181s。

explain 发现上面的sql已经使用了索引

PRIMARY eh_organization_member_logs range orgid_uid orgid_uid 9 1728 Using index condition; 
Using where DEPENDENT SUBQUERY eh_organization_members ref fk_eh_orgm_owner,i_target_id i_target_id 8 ehcore.eh_organization_member_logs.user_id 1 Using where

但还是这么慢。。why？

优化四

无意中去掉 order by字句排序，仅仅使用3.7s！！！

优化五

添加单列索引（operation_time），卧槽，3s！！！原来我一开始的方向就错了，sql执行慢，原因是检索字段没有建立索引，导致异常耗时！！

真实艰难优化过程。还是要深入了解原理才能解决sql的优化问题啊！

后台技术汇

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
开发日常小结（44）：记一次现网sql执行效率优化过程

2019年11月14日昨晚测试提了一个bug：详情是管理后台的数据查询超时了，我重新在beta环境测试了查询sql，不出意外的复现了。现网nginx配置了HTTP请求的超时时间为60s，就是说请求超过60s还没有返回响应，那么就会断开连接。于是我拷贝了sql，单独拎出来分析......终于将这个坑填完了。目录数据表结构准备业务查询sql现网执行效果优...
复制链接

扫一扫

专栏目录