突然发现有一个表数据都存在,但是查单条不出来结果,删除索引后,又能查,创建索引以后又查不了。

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:3.0.5
  • 【问题描述】:突然发现有一个表数据都存在,但是查单条不出来结果,删除索引后,又能查单条了,创建索引以后又查不了。给个思路,怎么解决这个问题呢。

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。

麻烦提供或者看下执行计划,explain analyze

是这个么?

能提供一下表结构以及SQL语句吗? 提供的图片分别是有索引和没有索引的情况下的么

CREATE TABLE `data_repair_logs` (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'id(生成,短11)',
  `type` tinyint(3) unsigned NOT NULL COMMENT '类型(0存储空间)',
  `linkid1` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '关联ID1',
  `linkid2` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '关联id2',
  `linkid3` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '关联id3',
  `old` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '原值',
  `new` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '新值',
  `intro` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '备注',
  `created_at` timestamp NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idlink2` (`linkid2`),
  KEY `idlink3` (`linkid3`),
  KEY `idlink1` (`linkid1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `data_repair_logs` (`id`, `type`, `linkid1`, `linkid2`, `linkid3`, `old`, `new`, `intro`, `created_at`) VALUES ('12nkesxhgqw', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-81486577', '2841333638', '', '2019-11-14 01:55:50'),
('12xq6lzvtvs', '0', 'cet5khp6vc', 'user.send.size.', '', '-13824', '0', '', '2019-11-14 09:40:32'),
('12xtgn3pgqw', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-394113', '2949079463', '', '2019-11-14 09:53:51'),
('12xyefos10k', '0', '11443ktvz0o', 'user.send.size.', '', '-80993', '0', '', '2019-11-14 10:23:19'),
('143o2c3iolk', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-8746', '3905217368', '', '2019-11-18 12:37:22'),
('14aaqfshs08', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-24839639', '3175315245', '', '2019-11-19 03:07:50'),
('14adl58jy88', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-9886468', '3159042976', '', '2019-11-19 03:17:49'),
('14adl5i43d4', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-125252', '3090613698', '', '2019-11-19 03:24:46'),
('14alhm7jxmw', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-822902', '3048413264', '', '2019-11-19 05:21:57'),
('14alhmh44dg', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-152411', '3042624225', '', '2019-11-19 06:10:46'),
('14dzgzhmo08', '0', 'uefwbgsc2s', 'user.send.size.', '', '1879130', '1876011', '', '2019-11-19 11:45:42'),
('14e4x1o8poo', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-2984994', '3548175882', '', '2019-11-19 12:02:36'),
('14k9gss0bvo', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-3761686', '3115868062', '', '2019-11-20 02:57:33'),
('14kqtgehqtk', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-2787', '3145044140', '', '2019-11-20 03:30:24'),
('14o0m5ifle0', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-67283116', '3683982738', '', '2019-11-20 11:21:29'),
('14w1jehfcx0', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-140495010', '5061920051', '', '2019-11-21 05:54:11'),
('14xgxx5gd8o', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-269132464', '5388846940', '', '2019-11-21 09:56:39'),
('154ra8f9yxw', '0', '11443ktvz0o', 'user.myfile.size.', '', '-21642', '228650771', '', '2019-11-22 02:12:52'),
('154sdi4ii3o', '0', '11443ktvz0o', 'user.myfile.size.', '', '228650771', '578064', '', '2019-11-22 02:19:19'),
('15zuaj3m7t4', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-4241336', '5509815459', '', '2019-11-25 02:50:23'),
('15zuaj3nmdk', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-4241336', '5509815459', '', '2019-11-25 02:50:23'),
('15zuaj3p0y0', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-4241336', '5509815459', '', '2019-11-25 02:50:25'),
('15zuaj3qfig', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-4241336', '5509815459', '', '2019-11-25 02:50:26'),
('15zuaj3ru2w', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-4241336', '5509815459', '', '2019-11-25 02:50:26'),
('1629qglvua0', '0', '11443ktvz0o', 'user.myfile.size.', '', '-228072707', '228072707', '', '2019-11-25 08:30:05'),
('1629qgm5o94', '0', 'm39vehe9g1', 'user.send.size.', '', '-517170', '11705587589', '', '2019-11-25 08:30:32'),
('162r936cmq0', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-164204002', '3972677182', '', '2019-11-25 09:40:08'),
('162r936e1ag', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-164204002', '3972677182', '', '2019-11-25 09:40:08'),
('16kcnahn678', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-166631927', '0', '', '2019-11-27 02:43:13'),
('16ohqas2yo8', '0', '11443ktvz0o', 'user.myfile.size.', '', '-7048092016', '0', '', '2019-11-27 12:30:39'),
('16uvlpl82lg', '0', '11443ktvz0o', 'user.send.size.', '', '-30089', '0', '', '2019-11-28 03:19:52'),
('16wjohq6dk4', '0', 'avjyai4ruw', 'user.myfile.size.', '', '-83053457', '0', '', '2019-11-28 08:32:30'),
('16xwk357zew', '0', 'cet5khp6vc', 'user.myfile.size.', '', '-62463883', '8996360', '', '2019-11-28 10:24:43'),
('16ynokvu494', '0', 'avjyai4ruw', 'user.myfile.size.', '', '-338021', '14377893', '', '2019-11-28 12:30:43'),
('16z4xn25jig', '0', 'um4ksiu6tk', 'user.send.size.', '', '-926243600', '744622473', '', '2019-11-29 01:10:51'),
('pj6ehhbaq0', '0', 'cf44648z60', 'user.myfile.size.', '', '-10244831', '292434', '', '2019-09-28 10:41:14'),
('proikn28so', '0', 'cvcy1kge5k', 'user.collect.size.', '', '-19867824004', '98698976', '', '2019-09-29 06:42:02'),
('saja2sf4f8', '0', 'd9h6ljmgg3', 'user.send.size.', '', '-20592257', '0', '', '2019-10-08 11:14:23');

删除的是 这个索引

然后就可以正常,查询了,如果有这个索引,就查不了。

麻烦查询语句提供一下,我在我本地测试一下

SELECT * from data_repair_logs WHERE linkid1='cet5khp6vc'

我在我本地测试没有复现你的问题,版本也是 v3.0.5,你能提供一下 SQL 在你的环境上两种情况下的执行计划么

上面那个窗口是 没有idlink1 索引的情况

下面那个窗口是有 idlink1 的情况 。

两种情况下的执行计划(explain analyze)是怎么样的

试下在有索引的情况下,执行一下 analyze table 重新收集一下统计信息,因为从执行计划的 range 范围看,这个range 范围有问题:

https://pingcap.com/docs-cn/stable/reference/sql/statements/analyze-table/#analyze-table

然后再执行一下 SQL 进行查询

麻烦导出一下对应表的统计信息看下

https://pingcap.com/docs-cn/stable/reference/performance/statistics/#导出统计信息

具体语法怎么写的,我这样写,没详情。

这样执行可以的。可以通过下面的命令查看analyze 的情况:

SHOW ANALYZE STATUS [ShowLikeOrWhere];

顺便麻烦在有索引的情况执行执行:

SELECT * from data_repair_logs WHERE linkid1 like 'cet5khp6vc%';

的情况