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。
推荐阅读：https://blog.youkuaiyun.com/s1040342522/article/details/88638363
优化二
添加联合索引
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！！！
推荐阅读：https://www.cnblogs.com/developer_chan/p/9225638.html
优化五
添加单列索引（operation_time），卧槽，3s！！！原来我一开始的方向就错了，sql执行慢，原因是检索字段没有建立索引，导致异常耗时！！
推荐阅读：http://www.mamicode.com/info-detail-2785311.html
真实艰难优化过程。还是要深入了解原理才能解决sql的优化问题啊！
333
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
