MySQL 8.0 查询丢数据BUG

发现MySQL在某些特定情况下会丢数据，8.0.18、8.0.24、8.0.25 经测均有此问题

复现

1.创建测试表

CREATE TABLE `yq_test_bug` (
  `F_ID` varchar(32) NOT NULL COMMENT '主键ID',
  `U_ID` varchar(32)  DEFAULT NULL COMMENT '三方ID',
  `P_USER_ID` varchar(32) DEFAULT NULL COMMENT '平台ID',
  `AY_ID` varchar(32) DEFAULT NULL COMMENT '活动ID',
  PRIMARY KEY (`F_ID` DESC) USING BTREE,
  KEY `IDX_P_USER_ID` (`P_USER_ID`) USING BTREE,
  KEY `IDX_AY_ID` (`AY_ID`) USING BTREE,
  KEY `IDX_U_ID` (`U_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2.写入临时数据

写入300多行数据，只写少量数据的话，当时未能复现出来

INSERT INTO yq_test_bug (`F_ID`, `U_ID`, `P_USER_ID`, `AY_ID`) VALUES ('20210519189001139', 'X436708R', NULL, '20210519104001208'), ('20210519189001133', 'X440276E',
NULL, '20210519104001208'), ('20210519189001130', 'X440276E', NULL, '20210519104001208'), ('20210519187001121', 'X440276E', NULL, '20210519104001208'), ('20210519185001148',
'X440276E', NULL, '20210519104001208'), ('20210519184001115', 'X440276E', NULL, '20210519104001208'), ('20210519184001112', 'X440276E', NULL, '20210519104001208'),
('20210519183001142', 'X440276E', NULL, '20210519104001208'), ('20210519182001151', 'X436708R', NULL, '20210519104001208'), ('20210519181001145', 'X440276E', NULL,
'20210519104001208'), ('20210519181001136', 'X440276E', NULL, '20210519104001208'), ('20210519181001127', 'X436708R', NULL, '20210519104001208'), ('20210519181001118', 'X440276E',
NULL, '20210519104001208'), ('20210519180001124', 'X440276E', NULL, '20210519104001208'), ('20210519175001106', 'X436708R', NULL, '20210519104001208'), ('20210519173001109',
'X436708R', NULL, '20210519104001208'), ('20210519168001097', 'X436708R', NULL, '20210519104001208'), ('20210519163001090', 'X436708R', NULL, '20210519104001208'),
('20210519161001100', 'X436708R', NULL, '20210519104001208'), ('20210519161001082', 'X93308PM', NULL, '20210514142000690'), ('20210519159001035', 'X457712G', NULL,
'20210519104001208'), ('20210519159001023', 'X457712G', NULL, '20210519104001208'), ('20210519159000964', 'X436708R', NULL, '20210519104001208'), ('20210519159000955', 'X436708R',
NULL, '20210519104001208'), ('20210519159000949', 'X436708R', NULL, '20210519104001208'), ('20210519158001060', 'X440276E', NULL, '20210519104001208'), ('20210519158001048',
'X440276E', NULL, '20210519104001208'), ('20210519158001011', 'X457712G', NULL, '20210519104001208'), ('20210519158001008', 'X457712G', NULL, '20210519104001208'),
('20210519157001069', 'X440276E', NULL, '20210519104001208'), ('20210519157001039', 'X440276E', NULL, '20210519104001208'), ('20210519157001005', 'X457712G', NULL,
'20210519104001208'), ('20210519156001051', 'X440276E', NULL, '20210519104001208'), ('20210519156001026', 'X457712G', NULL, '20210519104001208'), ('20210519156001014', 'X457712G',
NULL, '20210519104001208'), ('20210519156000967', 'X436708R', NULL, '20210519104001208'), ('20210519156000958', 'X436708R', NULL, '20210519104001208'), ('20210519156000952',
'X436708R', NULL, '20210519104001208'), ('20210519155001042', 'X440276E', NULL, '20210519104001208'), ('20210519155000937', 'X436708R', NULL, '20210519104001208'),
('20210519154001063', 'X440276E', NULL, '20210519104001208'), ('20210519154001054', 'X440276E', NULL, '20210519104001208'), ('20210519154001029', 'X457712G', NULL,
'20210519104001208'), ('20210519154001017', 'X457712G', NULL, '20210519104001208'), ('20210519154000943', 'X436708R', NULL, '20210519104001208'), ('20210519154000940', 'X436708R',
NULL, '20210519104001208'), ('20210519153000961', 'X436708R', NULL, '20210519104001208'), ('20210519152001032', 'X457712G', NULL, '20210519104001208'), ('20210519152000946',
'X436708R', NULL, '20210519104001208'), ('20210519151001057', 'X440276E', NULL, '20210519104001208'), ('20210519151001020', 'X457712G', NULL, '20210519104001208'),
('20210519150001066', 'X440276E', NULL, '20210519104001208'), ('20210519150001045', 'X440276E', NULL, '20210519104001208'), ('20210519109000744', 'X440276E', NULL,
'20210511130000141'), ('20210519108000763', 'X95352MJ', NULL, '20210514142000690'), ('20210519107000752', 'X436708R', NULL, '20210511130000141'), ('20210519107000749', 'X440276E',
NULL, '20210511130000141'), ('20210519102000741', 'X440276E', NULL, '20210511130000141'), ('20210518183000735', 'X440276E', NULL, '20210511130000141'), ('20210518180000738',
'X436708R', NULL, '20210511130000141'), ('20210518177000716', 'X436708R', NULL, '20210511130000141'), ('20210518175000726', 'X440276E', NULL, '20210511130000141'),
('20210518175000702', 'X440276E', NULL, '20210511130000141'), ('20210518174000713', 'X440276E', NULL, '20210511130000141'), ('20210518174000705', 'X440276E', NULL,
'20210511130000141'), ('20210518172000719', 'X440276E', NULL, '20210511130000141'), ('20210518171000732', 'X440276E', NULL, '20210511130000141'), ('20210518170000729', 'X440276E',
NULL, '20210511130000141'), ('20210518145000616', 'X92598WF', NULL, '20210512131000013'), ('20210518142000615', 'X92598WF', NULL, '20210512131000013'), ('20210517116000569',
'X446772M', NULL, '20210511130000141'), ('20210517115000593', 'X91618LR', NULL, '20210512131000013'), ('20210517112000594', 'X91618LR', NULL, '20210512131000013'),
('20210517079000566', 'X446772M', NULL, '20210511130000141'), ('20210514190000533', 'X446772M', NULL, '20210511130000141'), ('20210514189000491', 'X458556J', NULL,
'20210514152000763'), ('20210514188000439', 'X446772M', NULL, '20210511130000141'), ('20210514186000508', 'X458556J', NULL, '20210514159000757'), ('20210514186000504', 'X458556J',
NULL, '20210514159000757'), ('20210514186000500', 'X458556J', NULL, '20210514159000757'), ('20210514186000485', 'X458556J', NULL, '20210514152000763'), ('20210514186000481',
'X458556J', NULL, '20210514152000763'), ('20210514186000477', 'X458556J', NULL, '20210514152000763'), ('20210514185000516', 'X458556J', NULL, '20210514159000757'),
('20210514185000512', 'X458556J', NULL, '20210514159000757'), ('20210514185000489', 'X458556J', NULL, '20210514152000763'), ('20210514184000493', 'X458556J', NULL,
'20210514152000763'), ('20210514181000506', 'X458556J', NULL, '20210514159000757'), ('20210514181000502', 'X458556J', NULL, '20210514159000757'), ('20210514181000483', 'X458556J',
NULL, '20210514152000763'), ('20210514181000479', 'X458556J', NULL, '20210514152000763'), ('20210514180000514', 'X458556J', NULL, '20210514159000757'), ('20210514180000510',
'X458556J', NULL, '20210514159000757'), ('20210514180000498', 'X458556J', NULL, '20210514159000757'), ('20210514180000487', 'X458556J', NULL, '20210514152000763'),
('20210514180000475', 'X458556J', NULL, '20210514152000763'), ('20210514179000376', 'X92418PR', NULL, '20210514152000763'), ('20210514179000372', 'X92418PR', NULL,
'20210514152000763'), ('20210514179000368', 'X92418PR', NULL, '20210514152000763'), ('20210514179000364', 'X92418PR', NULL, '20210514152000763'), ('20210514179000285', 'X458540M',
NULL, '20210514152000763'), ('20210514178000380', 'X92418PR', NULL, '20210514152000763'), ('20210514178000297', 'X458540M', NULL, '20210514152000763'), ('20210514178000293',
'X458540M', NULL, '20210514152000763'), ('20210514178000289', 'X458540M', NULL, '20210514152000763'), ('20210514177000301', 'X458540M', NULL, '20210514152000763'),
('20210514174000374', 'X92418PR', NULL, '20210514152000763'), ('20210514174000370', 'X92418PR', NULL, '20210514152000763'), ('20210514174000366', 'X92418PR', NULL,
'20210514152000763'), ('20210514174000287', 'X458540M', NULL, '20210514152000763'), ('20210514173000378', 'X92418PR', NULL, '20210514152000763'), ('20210514173000324', 'X446772M',
NULL, '20210511130000141'), ('20210514173000295', 'X458540M', NULL, '20210514152000763'), ('20210514173000291', 'X458540M', NULL, '20210514152000763'), ('20210514173000283',
'X458540M', NULL, '20210514152000763'), ('20210514172000382', 'X92418PR', NULL, '20210514152000763'), ('20210514172000299', 'X458540M', NULL, '20210514152000763'),
('20210514169000196', 'X458540M', NULL, '20210514159000757'), ('20210514166000214', 'X458540M', NULL, '20210514159000757'), ('20210514166000210', 'X458540M', NULL,
'20210514159000757'), ('20210514166000206', 'X458540M', NULL, '20210514159000757'), ('20210514165000202', 'X458540M', NULL, '20210514159000757'), ('20210514165000198', 'X458540M',
NULL, '20210514159000757'), ('20210514161000212', 'X458540M', NULL, '20210514159000757'), ('20210514161000208', 'X458540M', NULL, '20210514159000757'), ('20210514161000166',
'X446772M', NULL, '20210511130000141'), ('20210514160000204', 'X458540M', NULL, '20210514159000757'), ('20210514160000200', 'X458540M', NULL, '20210514159000757'),
('20210514152000127', 'X446772M', NULL, '20210511130000141'), ('20210513189000579', 'X457712G', NULL, '20210513175000484'), ('20210513189000527', 'X457712G', NULL,
'20210513174000467'), ('20210513189000472', 'X440276E', NULL, '20210513174000467'), ('20210513189000461', 'X440276E', NULL, '20210513175000484'), ('20210513188000573', 'X457712G',
NULL, '20210513175000484'), ('20210513188000569', 'X457712G', NULL, '20210513175000484'), ('20210513188000556', 'X92550CP', NULL, '20210513175000484'), ('20210513188000543',
'X457712G', NULL, '20210513174000467'), ('20210513188000533', 'X457712G', NULL, '20210513174000467'), ('20210513188000482', 'X440276E', NULL, '20210513174000467'),
('20210513188000468', 'X440276E', NULL, '20210513174000467'), ('20210513187000564', 'X92550CP', NULL, '20210513175000484'), ('20210513187000560', 'X92550CP', NULL,
'20210513175000484'), ('20210513187000552', 'X92550CP', NULL, '20210513175000484'), ('20210513187000529', 'X457712G', NULL, '20210513174000467'), ('20210513187000478', 'X440276E',
NULL, '20210513174000467'), ('20210513187000447', 'X440276E', NULL, '20210513175000484'), ('20210513187000443', 'X440276E', NULL, '20210513175000484'), ('20210513187000428',
'X436708R', NULL, '20210513175000484'), ('20210513186000585', 'X457712G', NULL, '20210513175000484'), ('20210513186000455', 'X440276E', NULL, '20210513175000484'),
('20210513186000451', 'X440276E', NULL, '20210513175000484'), ('20210513186000436', 'X436708R', NULL, '20210513175000484'), ('20210513186000432', 'X436708R', NULL,
'20210513175000484'), ('20210513186000424', 'X436708R', NULL, '20210513175000484'), ('20210513185000581', 'X457712G', NULL, '20210513175000484'), ('20210513185000548', 'X92550CP',
NULL, '20210513175000484'), ('20210513185000539', 'X457712G', NULL, '20210513174000467'), ('20210513185000474', 'X440276E', NULL, '20210513174000467'), ('20210513185000440',
'X436708R', NULL, '20210513175000484'), ('20210513184000545', 'X457712G', NULL, '20210513174000467'), ('20210513184000535', 'X457712G', NULL, '20210513174000467'),
('20210513184000464', 'X440276E', NULL, '20210513174000467'), ('20210513183000575', 'X457712G', NULL, '20210513175000484'), ('20210513183000571', 'X457712G', NULL,
'20210513175000484'), ('20210513183000470', 'X440276E', NULL, '20210513174000467'), ('20210513182000566', 'X92550CP', NULL, '20210513175000484'), ('20210513182000562', 'X92550CP',
NULL, '20210513175000484'), ('20210513182000558', 'X92550CP', NULL, '20210513175000484'), ('20210513182000554', 'X92550CP', NULL, '20210513175000484'), ('20210513182000550',
'X92550CP', NULL, '20210513175000484'), ('20210513182000541', 'X457712G', NULL, '20210513174000467'), ('20210513182000531', 'X457712G', NULL, '20210513174000467'),
('20210513182000480', 'X440276E', NULL, '20210513174000467'), ('20210513182000466', 'X440276E', NULL, '20210513174000467'), ('20210513182000449', 'X440276E', NULL,
'20210513175000484'), ('20210513182000445', 'X440276E', NULL, '20210513175000484'), ('20210513181000587', 'X457712G', NULL, '20210513175000484'), ('20210513181000476', 'X440276E',
NULL, '20210513174000467'), ('20210513181000457', 'X440276E', NULL, '20210513175000484'), ('20210513181000453', 'X440276E', NULL, '20210513175000484'), ('20210513181000434',
'X436708R', NULL, '20210513175000484'), ('20210513181000430', 'X436708R', NULL, '20210513175000484'), ('20210513181000426', 'X436708R', NULL, '20210513175000484'),
('20210513180000583', 'X457712G', NULL, '20210513175000484'), ('20210513180000577', 'X457712G', NULL, '20210513175000484'), ('20210513180000537', 'X457712G', NULL,
'20210513174000467'), ('20210513180000459', 'X440276E', NULL, '20210513175000484'), ('20210513180000438', 'X436708R', NULL, '20210513175000484'), ('20210513180000422', 'X436708R',
NULL, '20210513175000484'), ('20210513179000340', 'X440276E', NULL, '20210513141000385'), ('20210513178000346', 'X440276E', NULL, '20210513141000385'), ('20210513176000352',
'X440276E', NULL, '20210513141000385'), ('20210513175000342', 'X440276E', NULL, '20210513141000385'), ('20210513174000348', 'X440276E', NULL, '20210513141000385'),
('20210513173000354', 'X440276E', NULL, '20210513141000385'), ('20210513173000336', 'X440276E', NULL, '20210513141000385'), ('20210513172000344', 'X440276E', NULL,
'20210513141000385'), ('20210513172000338', 'X440276E', NULL, '20210513141000385'), ('20210513170000350', 'X440276E', NULL, '20210513141000385'), ('20210513169000277', 'X456940N',
NULL, '20210513163000413'), ('20210513166000275', 'X456940N', NULL, '20210513163000413'), ('20210513159000268', 'X440276E', NULL, '20210512167000075'), ('20210513158000264',
'X440276E', NULL, '20210512167000075'), ('20210513157000260', 'X440276E', NULL, '20210512167000075'), ('20210513156000254', 'X440276E', NULL, '20210512167000075'),
('20210513153000266', 'X440276E', NULL, '20210512167000075'), ('20210513153000262', 'X440276E', NULL, '20210512167000075'), ('20210513152000256', 'X440276E', NULL,
'20210512167000075'), ('20210513151000258', 'X440276E', NULL, '20210512167000075'), ('20210513150000252', 'X440276E', NULL, '20210512167000075'), ('20210513150000250', 'X440276E',
NULL, '20210512167000075'), ('20210513138000121', 'X440276E', NULL, '20210512167000075'), ('20210513137000135', 'X440276E', NULL, '20210512167000075'), ('20210513136000131',
'X440276E', NULL, '20210512167000075'), ('20210513135000127', 'X440276E', NULL, '20210512167000075'), ('20210513134000123', 'X440276E', NULL, '20210512167000075'),
('20210513133000137', 'X440276E', NULL, '20210512167000075'), ('20210513132000133', 'X440276E', NULL, '20210512167000075'), ('20210513131000129', 'X440276E', NULL,
'20210512167000075'), ('20210513131000119', 'X440276E', NULL, '20210512167000075'), ('20210513130000125', 'X440276E', NULL, '20210512167000075'), ('20210513129000039', 'X440276E',
NULL, '20210512167000075'), ('20210513129000033', 'X440276E', NULL, '20210512167000075'), ('20210513129000027', 'X440276E', NULL, '20210512167000075'), ('20210513129000012',
'X440276E', NULL, '20210512167000075'), ('20210513128000008', 'X440276E', NULL, '20210512167000075'), ('20210513126000018', 'X440276E', NULL, '20210512167000075'),
('20210513126000014', 'X440276E', NULL, '20210512167000075'), ('20210513126000006', 'X440276E', NULL, '20210512167000075'), ('20210513125000041', 'X440276E', NULL,
'20210512167000075'), ('20210513125000035', 'X440276E', NULL, '20210512167000075'), ('20210513125000029', 'X440276E', NULL, '20210512167000075'), ('20210513125000020', 'X440276E',
NULL, '20210512167000075'), ('20210513124000016', 'X440276E', NULL, '20210512167000075'), ('20210513122000031', 'X440276E', NULL, '20210512167000075'), ('20210513122000022',
'X440276E', NULL, '20210512167000075'), ('20210513121000043', 'X440276E', NULL, '20210512167000075'), ('20210513121000037', 'X440276E', NULL, '20210512167000075'),
('20210513121000010', 'X440276E', NULL, '20210512167000075'), ('20210513121000004', 'X440276E', NULL, '20210512167000075'), ('20210513120000025', 'X440276E', NULL,
'20210512167000075'), ('20210513109000217', 'X440276E', NULL, '20210512167000075'), ('20210513109000199', 'X446772M', NULL, '20210511138000085'), ('20210513109000187', 'X456940N',
NULL, '20210513108000229'), ('20210513109000161', 'X456940N', NULL, '20210513108000229'), ('20210513109000158', 'X456940N', NULL, '20210513108000229'), ('20210513109000145',
'X456940N', NULL, '20210513108000229'), ('20210513109000132', 'X456940N', NULL, '20210513108000229'), ('20210513109000122', 'X456940N', NULL, '20210513108000229'),
('20210513109000116', 'X456940N', NULL, '20210513108000229'), ('20210513109000107', 'X456940N', NULL, '20210513108000229'), ('20210513109000101', 'X456940N', NULL,
'20210513108000229'), ('20210513109000096', 'X436708R', NULL, '20210511138000085'), ('20210513108000221', 'X440276E', NULL, '20210512167000075'), ('20210513108000213', 'X440276E',
NULL, '20210512167000075'), ('20210513108000209', 'X440276E', NULL, '20210512167000075'), ('20210513108000193', 'X456940N', NULL, '20210513108000229'), ('20210513108000181',
'X456940N', NULL, '20210513108000229'), ('20210513108000173', 'X456940N', NULL, '20210513108000229'), ('20210513108000168', 'X456940N', NULL, '20210513108000229'),
('20210513108000164', 'X456940N', NULL, '20210513108000229'), ('20210513108000155', 'X456940N', NULL, '20210513108000229'), ('20210513108000152', 'X456940N', NULL,
'20210513108000229'), ('20210513108000142', 'X456940N', NULL, '20210513108000229'), ('20210513108000137', 'X456940N', NULL, '20210513108000229'), ('20210513108000119', 'X456940N',
NULL, '20210513108000229'), ('20210513108000111', 'X456940N', NULL, '20210513108000229'), ('20210513108000094', 'X96704AE', NULL, '20210511138000085'), ('20210513107000205',
'X440276E', NULL, '20210512167000075'), ('20210513107000196', 'X456940N', NULL, '20210513108000229'), ('20210513107000185', 'X456940N', NULL, '20210513108000229'),
('20210513107000182', 'X456940N', NULL, '20210513108000229'), ('20210513107000176', 'X456940N', NULL, '20210513108000229'), ('20210513107000170', 'X456940N', NULL,
'20210513108000229'), ('20210513107000149', 'X456940N', NULL, '20210513108000229'), ('20210513107000146', 'X456940N', NULL, '20210513108000229'), ('20210513107000139', 'X456940N',
NULL, '20210513108000229'), ('20210513107000133', 'X456940N', NULL, '20210513108000229'), ('20210513107000126', 'X456940N', NULL, '20210513108000229'), ('20210513107000123',
'X456940N', NULL, '20210513108000229'), ('20210513107000113', 'X456940N', NULL, '20210513108000229'), ('20210513107000102', 'X456940N', NULL, '20210513108000229'),
('20210513106000197', 'X456940N', NULL, '20210513108000229'), ('20210513106000191', 'X456940N', NULL, '20210513108000229'), ('20210513106000179', 'X456940N', NULL,
'20210513108000229'), ('20210513106000159', 'X456940N', NULL, '20210513108000229'), ('20210513106000134', 'X456940N', NULL, '20210513108000229'), ('20210513106000114', 'X456940N',
NULL, '20210513108000229'), ('20210513106000112', 'X456940N', NULL, '20210513108000229'), ('20210513105000188', 'X456940N', NULL, '20210513108000229'), ('20210513105000169',
'X456940N', NULL, '20210513108000229'), ('20210513105000165', 'X456940N', NULL, '20210513108000229'), ('20210513105000162', 'X456940N', NULL, '20210513108000229'),
('20210513105000156', 'X456940N', NULL, '20210513108000229'), ('20210513105000153', 'X456940N', NULL, '20210513108000229'), ('20210513105000143', 'X456940N', NULL,
'20210513108000229'), ('20210513105000138', 'X456940N', NULL, '20210513108000229'), ('20210513105000130', 'X456940N', NULL, '20210513108000229'), ('20210513105000128', 'X456940N',
NULL, '20210513108000229'), ('20210513105000127', 'X456940N', NULL, '20210513108000229'), ('20210513105000124', 'X456940N', NULL, '20210513108000229'), ('20210513105000120',
'X456940N', NULL, '20210513108000229'), ('20210513104000215', 'X440276E', NULL, '20210512167000075'), ('20210513104000194', 'X456940N', NULL, '20210513108000229'),
('20210513104000183', 'X456940N', NULL, '20210513108000229'), ('20210513104000177', 'X456940N', NULL, '20210513108000229'), ('20210513104000174', 'X456940N', NULL,
'20210513108000229'), ('20210513104000171', 'X456940N', NULL, '20210513108000229'), ('20210513104000150', 'X456940N', NULL, '20210513108000229'), ('20210513104000140', 'X456940N',
NULL, '20210513108000229'), ('20210513104000105', 'X456940N', NULL, '20210513108000229'), ('20210513104000103', 'X456940N', NULL, '20210513108000229'), ('20210513103000223',
'X440276E', NULL, '20210512167000075'), ('20210513103000219', 'X440276E', NULL, '20210512167000075'), ('20210513103000211', 'X440276E', NULL, '20210512167000075'),
('20210513103000207', 'X440276E', NULL, '20210512167000075'), ('20210513103000186', 'X456940N', NULL, '20210513108000229'), ('20210513103000160', 'X456940N', NULL,
'20210513108000229'), ('20210513103000147', 'X456940N', NULL, '20210513108000229'), ('20210513103000144', 'X456940N', NULL, '20210513108000229'), ('20210513103000135', 'X456940N',
NULL, '20210513108000229'), ('20210513103000131', 'X456940N', NULL, '20210513108000229'), ('20210513103000129', 'X456940N', NULL, '20210513108000229'), ('20210513103000117',
'X456940N', NULL, '20210513108000229'), ('20210513103000115', 'X456940N', NULL, '20210513108000229'), ('20210513103000099', 'X456940N', NULL, '20210513108000229'),
('20210513102000192', 'X456940N', NULL, '20210513108000229'), ('20210513102000180', 'X456940N', NULL, '20210513108000229'), ('20210513102000166', 'X456940N', NULL,
'20210513108000229'), ('20210513102000163', 'X456940N', NULL, '20210513108000229'), ('20210513102000157', 'X456940N', NULL, '20210513108000229'), ('20210513102000154', 'X456940N',
NULL, '20210513108000229'), ('20210513102000125', 'X456940N', NULL, '20210513108000229'), ('20210513102000121', 'X456940N', NULL, '20210513108000229'), ('20210513102000109',
'X456940N', NULL, '20210513108000229'), ('20210513102000108', 'X456940N', NULL, '20210513108000229'), ('20210513102000106', 'X456940N', NULL, '20210513108000229'),
('20210513102000104', 'X456940N', NULL, '20210513108000229'), ('20210513101000189', 'X456940N', NULL, '20210513108000229'), ('20210513101000172', 'X456940N', NULL,
'20210513108000229'), ('20210513101000167', 'X456940N', NULL, '20210513108000229'), ('20210513101000151', 'X456940N', NULL, '20210513108000229'), ('20210513101000141', 'X456940N',
NULL, '20210513108000229'), ('20210513101000136', 'X456940N', NULL, '20210513108000229'), ('20210513101000118', 'X456940N', NULL, '20210513108000229'), ('20210513101000100',
'X456940N', NULL, '20210513108000229'), ('20210513101000098', 'X456940N', NULL, '20210513108000229'), ('20210513100000195', 'X456940N', NULL, '20210513108000229'),
('20210513100000190', 'X456940N', NULL, '20210513108000229'), ('20210513100000184', 'X456940N', NULL, '20210513108000229'), ('20210513100000178', 'X456940N', NULL,
'20210513108000229'), ('20210513100000175', 'X456940N', NULL, '20210513108000229'), ('20210513100000148', 'X456940N', NULL, '20210513108000229'), ('20210513100000110', 'X456940N',
NULL, '20210513108000229'), ('20210512177000011', 'X446772M', NULL, '20210511138000085'), ('20210512169000141', 'X91188MA', NULL, '20210512131000013'), ('20210512167000142',
'X91188MA', NULL, '20210512131000013'), ('20210512164000148', 'X93274VX', NULL, '20210512131000013'), ('20210512162000149', 'X93274VX', NULL, '20210512131000013'),
('20210512117000058', 'X456940N', NULL, '20210512112000258'), ('20210512106000043', 'X456940N', NULL, '20210512102000186'), ('20210511179000034', 'X446772M', NULL,
'20210511138000085'), ('20210511168000023', 'X446772M', NULL, '20210511138000085'), ('20210510179000039', 'X455656A', NULL, '20210508137000487'), ('20210510174000042', 'X455656A',
NULL, '20210508137000487'), ('20210510167000017', 'X446772M', NULL, '20210508137000487'), ('20210510166000022', 'X446772M', NULL, '20210508137000487'), ('20210510163000019',
'X446772M', NULL, '20210508137000487'), ('20210510157000011', 'X446772M', NULL, '20210508137000487'), ('20210510155000015', 'X446772M', NULL, '20210508137000487'),
('20210510149000007', 'X446772M', NULL, '20210508137000487'), ('20210510135000005', 'X446772M', NULL, '20210508137000487'), ('20210510134000003', 'X446772M', NULL, '20210508137000487');

3.验证查看

-- 根据主键查询，能获得一条数据
root@yq [test1]> select * from yq_test_bug where u_id = 'X91618LR' and ay_id = '20210512131000013' and f_id = '20210517115000593';
+-------------------+----------+-----------+-------------------+
| F_ID              | U_ID     | P_USER_ID | AY_ID             |
+-------------------+----------+-----------+-------------------+
| 20210517115000593 | X91618LR | NULL      | 20210512131000013 |
+-------------------+----------+-----------+-------------------+
1 row in set (0.00 sec)

-- 去除主键条件，少了一个条件，结果竟然没有数据了
root@yq [test1]> select * from yq_test_bug where u_id = 'X91618LR' and ay_id = '20210512131000013';
Empty set (0.00 sec)

-- 实际上应当存在两条数据的
root@yq [test1]> select * from yq_test_bug where u_id = 'X91618LR';
+-------------------+----------+-----------+-------------------+
| F_ID              | U_ID     | P_USER_ID | AY_ID             |
+-------------------+----------+-----------+-------------------+
| 20210517115000593 | X91618LR | NULL      | 20210512131000013 |
| 20210517112000594 | X91618LR | NULL      | 20210512131000013 |
+-------------------+----------+-----------+-------------------+
2 rows in set (0.00 sec)

猜想

怀疑是优化器走错导致的

root@yq [test1]> desc select * from yq_test_bug where u_id = 'X91618LR' and ay_id = '20210512131000013';
+----+-------------+-------------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table       | partitions | type        | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | yq_test_bug | NULL       | index_merge | IDX_USER_ID,IDX_AY_ID | IDX_USER_ID,IDX_AY_ID | 131,131 | NULL |    1 |   100.00 | Using intersect(IDX_USER_ID,IDX_AY_ID); Using where |
+----+-------------+-------------+------------+-------------+-----------------------+-----------------------+---------+------+------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

root@yq [test1]> set optimizer_switch='index_merge=off';
Query OK, 0 rows affected (0.00 sec)

root@yq [test1]> show variables like '%optimizer_switch%';
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@yq [test1]> select * from yq_test_bug where u_id = 'X91618LR' and ay_id = '20210512131000013';
+-------------------+----------+-----------+-------------------+
| F_ID              | U_ID     | P_USER_ID | AY_ID             |
+-------------------+----------+-----------+-------------------+
| 20210517115000593 | X91618LR | NULL      | 20210512131000013 |
| 20210517112000594 | X91618LR | NULL      | 20210512131000013 |
+-------------------+----------+-----------+-------------------+
2 rows in set (0.00 sec)

怀疑是由于主键倒序导致的，将主键改为正常顺序能解决此问题
修改对应索引也可以绕开这个问题

root@yq [test1]> alter table yq_test_bug drop index IDX_AY_ID;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

 
root@yq [test1]> select * from yq_test_bug where u_id = 'X91618LR' and ay_id = '20210512131000013';
+-------------------+----------+-----------+-------------------+
| F_ID              | U_ID     | P_USER_ID | AY_ID             |
+-------------------+----------+-----------+-------------------+
| 20210517115000593 | X91618LR | NULL      | 20210512131000013 |
| 20210517112000594 | X91618LR | NULL      | 20210512131000013 |
+-------------------+----------+-----------+-------------------+
2 rows in set (0.00 sec)

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,716评论 4赞 364
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,558评论 1赞 294
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,431评论 0赞 244
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,127评论 0赞 209
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,511评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,692评论 1赞 222
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,915评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,664评论 0赞 202
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,412评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,616评论 2赞 245
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,105评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,424评论 2赞 254
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,098评论 3赞 238
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,096评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,869评论 0赞 197
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,748评论 2赞 276
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,641评论 2赞 271

MySQL 8.0 查询丢数据BUG

复现

1.创建测试表

2.写入临时数据

猜想

推荐阅读更多精彩内容