为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【TiDB 版本】5.7.25-TiDB-v4.0.0 TiDB Server
【问题描述】sql查询时,同条件情况下,结果有时会返回空行,压测后结果 返回空行的概率为 0.3%~1%
以下为sql
SELECT
c
.community_id
,
c
.community
,
c
.is_delete
,
u
.partner_id
,
u.is_delete AS user_is_delete
FROM
partner_community
c
INNER JOIN partner_user
u
ON u
.partner_id
= c
.partner_id
WHERE
c
.community_id
= ‘9449’
LIMIT 1
以下为对应的表结构
CREATE TABLE partner_community
(
community_id
int(11) NOT NULL AUTO_INCREMENT,
province
varchar(255) DEFAULT ‘广东省’ COMMENT ‘省’,
city
varchar(255) DEFAULT ‘’ COMMENT ‘市’,
county
varchar(255) DEFAULT ‘’ COMMENT ‘区’,
town
varchar(255) DEFAULT ‘’ COMMENT ‘镇’,
community
varchar(255) DEFAULT ‘’ COMMENT ‘小区名’,
address
varchar(255) DEFAULT ‘’ COMMENT ‘详细地址’,
partner_id
int(11) DEFAULT 0 COMMENT ‘合伙人ID’,
create_time
int(10) DEFAULT NULL,
is_delete
enum(‘yes’,‘no’,‘move’) DEFAULT ‘no’,
lat
float(10,6) DEFAULT NULL COMMENT ‘纬度’,
long
float(10,6) DEFAULT NULL COMMENT ‘经度’,
alias
varchar(255) DEFAULT NULL COMMENT ‘小区后缀名’,
type
enum(‘big’,‘small’) DEFAULT ‘big’ COMMENT ‘大团长 小团长’,
big_partner_id
int(11) DEFAULT NULL COMMENT ‘大团长id’,
big_community_id
int(11) DEFAULT NULL COMMENT ‘大团长小区id’,
apply_type
enum(‘big’,‘small’) DEFAULT ‘big’ COMMENT ‘申请时团长属性。大团长 小团长’,
old_big_partner_id
int(11) DEFAULT NULL COMMENT ‘旧大团长’,
set_old_time
int(11) DEFAULT NULL COMMENT ‘设置旧大团长时间’,
ready_up
tinyint(1) DEFAULT 0 COMMENT ’ 1下周期升级大团长 2升级失败(可能原因:存在大团长)’,
ready_up_time
int(11) DEFAULT NULL COMMENT ‘升级时间’,
PRIMARY KEY (community_id
),
KEY partner_id
(partner_id
),
KEY big_community_id
(big_community_id
),
KEY big_partner_id
(big_partner_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=309450;
CREATE TABLE partner_user
(
partner_id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(255) DEFAULT ‘’ COMMENT ‘申请人’,
phone
varchar(255) DEFAULT ‘’ COMMENT ‘手机号码’,
password
varchar(255) DEFAULT ‘’,
invite_code
varchar(255) DEFAULT ‘’ COMMENT ‘邀请码’,
money
float(10,2) DEFAULT 0.00 COMMENT ‘用户余额’,
pass_code
varchar(255) DEFAULT ‘-1’ COMMENT ‘用于找回密码’,
province
varchar(255) DEFAULT ‘广东省’ COMMENT ‘省’,
city
varchar(255) DEFAULT ‘’ COMMENT ‘市’,
county
varchar(255) DEFAULT ‘’ COMMENT ‘区/县’,
town
varchar(255) DEFAULT ‘’ COMMENT ‘镇’,
last_login_time
int(10) DEFAULT 0,
reg_time
int(10) DEFAULT 0,
is_delete
enum(‘no’,‘ban’,‘yes’,‘leave’,‘ready_ban’) DEFAULT ‘no’ COMMENT ‘ban禁用(回收站还有) yes(删除) leave(请假)预备禁用(周期结束禁用)’,
headimg
varchar(255) DEFAULT ‘’ COMMENT ‘头像地址’,
invite_user
int(11) DEFAULT 0 COMMENT ‘上级合伙人ID’,
invite_user_2
int(11) DEFAULT 0 COMMENT ‘上上级合伙人ID’,
level
enum(‘4’,‘3’,‘2’,‘1’) DEFAULT ‘1’ COMMENT ‘钻石,资深,执行,见习’,
cycle_condition
enum(‘yes’,‘no’,‘a’) DEFAULT ‘no’ COMMENT ‘收入条件 满足后修改 升级后 重置’,
cycle_condition_count
int(11) DEFAULT 0 COMMENT ‘销售目标 已满足周期数’,
head_marketing
varchar(255) DEFAULT ‘’,
path
varchar(255) DEFAULT ‘’,
hm_id
int(10) DEFAULT 0 COMMENT ‘市场负责人ID’,
is_set_bank
tinyint(1) DEFAULT 1 COMMENT ‘是否修改提现信息’,
nickname
varchar(32) DEFAULT NULL COMMENT ‘昵称’,
is_initialize
tinyint(1) DEFAULT 0 COMMENT ‘是否初始化’,
delivery_name
varchar(32) DEFAULT NULL COMMENT ‘收货人’,
delivery_phone
varchar(32) DEFAULT NULL COMMENT ‘收货人手机’,
spare_phone
varchar(32) DEFAULT NULL COMMENT ‘备用手机’,
browse_number
int(11) DEFAULT 0 COMMENT ‘被浏览数’,
thumbs_number
int(11) DEFAULT 0 COMMENT ‘被点赞数’,
collection_number
int(11) DEFAULT 0 COMMENT ‘被收藏数’,
fans_number
int(11) DEFAULT 0 COMMENT ‘粉丝数量’,
follow_number
int(11) DEFAULT 0 COMMENT ‘我的关注数量’,
share_money
decimal(10,2) DEFAULT 0.00 COMMENT ‘打赏金额’,
share_number
int(11) DEFAULT 0 COMMENT ‘我的分享数量’,
remark
text DEFAULT NULL COMMENT ‘备注(请假)’,
type
tinyint(1) DEFAULT 1 COMMENT ‘合伙人类型 1 T+3 2 T+1’,
ready_type
tinyint(1) DEFAULT 1 COMMENT ‘预备类型’,
set_type_time
datetime DEFAULT NULL COMMENT ‘状态生效时间’,
ban_time
int(11) DEFAULT 0 COMMENT ‘禁用时间’,
percentage_1
decimal(5,2) DEFAULT 0.00 COMMENT ‘售后赔偿风险值,单位%’,
percentage_2
decimal(5,2) DEFAULT 0.00 COMMENT ‘售后补货风险值,单位%’,
is_tab
tinyint(1) DEFAULT 0 COMMENT ‘标记 0否 1是(扣运费)’,
no_group_count
int(6) NOT NULL DEFAULT 0 COMMENT ‘几期不开团’,
set_tab_time
int(11) DEFAULT NULL COMMENT ‘标记时间’,
set_ban_time
int(11) DEFAULT NULL COMMENT ‘标记禁用时间’,
share_code
varchar(64) DEFAULT NULL COMMENT ‘分享码’,
is_admin_leave
tinyint(1) DEFAULT 0 COMMENT ‘是否是后台标记的请假,0否1是’,
is_pay
tinyint(1) DEFAULT 0 COMMENT ‘是否设置支付密码’,
pay_password
varchar(32) DEFAULT ‘’ COMMENT ‘支付密码’,
PRIMARY KEY (partner_id
),
KEY invite_user
(invite_user
),
KEY invite_user_2
(invite_user_2
),
KEY invite_code
(invite_code
(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=309670;
若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。