查看表数据存储、分布在哪些tikv节点上

[niegang]

【 TiDB 使用环境】生产环境
【 TiDB 版本】V4.0.16
【复现路径】SQL查询未得到结果
【遇到的问题：想知道某个表的数据分布、存储在哪些kv节点上
【资源配置】14台TIKV节点的集群 （16c 64 2T *14）
【附件：截图/日志/监控】

[啦啦啦啦啦]

试下SHOW TABLE xxx REGIONS

[Kongdom]

库：INFORMATION_SCHEMA
表：TIKV_REGION_PEERS，TIKV_REGION_STATUS，TIKV_STORE_STATUS

如何看1个region 分布在哪几个tikv上

库：INFORMATION_SCHEMA 表：TIKV_REGION_PEERS，TIKV_REGION_STATUS，TIKV_STORE_STATUS 需要什么信息上面的三个表做下表关联就行了 类似如下，可以再加一些其他的限定条件 select r.REGION_ID,r.IS_LEARNER,r.IS_LEADER,s.ADDRESS from TIKV_REGION_PEERS r…

[niegang]

@啦啦啦啦啦

SHOW TABLE xxx REGIONS

SHOW TABLE xxx REGIONS 查询后，只能看到如下信息 没有region所在tikv节点信息

[tidb菜鸟一只 (小菜一颗)]

SELECT DISTINCT a.db_name,a.TABLE_NAME,a.REGION_ID,c.STORE_ID,b.ADDRESS FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS a ,INFORMATION_SCHEMA.TIKV_STORE_STATUS b,INFORMATION_SCHEMA.TIKV_REGION_PEERS c
WHERE c.IS_LEADER=‘1’
AND c.STORE_ID=b.STORE_ID
AND a.REGION_ID=c.REGION_ID
AND table_name=‘b’;

[啦啦啦啦啦]

有LEADER_STORE_ID就能知道在那个tikv上了

[wzf0072]

这个SQL能查询Region IS_LEADER所在节点信息，去掉 c.IS_LEADER =‘1’就可以了：
SELECT DISTINCT a.db_name,a.TABLE_NAME,a.REGION_ID,c.STORE_ID,b.ADDRESS FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS a ,INFORMATION_SCHEMA.TIKV_STORE_STATUS b,INFORMATION_SCHEMA.TIKV_REGION_PEERS c
WHERE
– c.IS_LEADER='1’AND
c.STORE_ID=b.STORE_ID
AND a.REGION_ID=c.REGION_ID
AND table_name=‘b’
order by a.REGION_ID

[我是咖啡哥]

不显示tiflash节点加r.IS_LEARNER=0

SELECT
  rs.`DB_NAME`,
  rs.`TABLE_NAME`,
  s.ADDRESS,
  r.`IS_LEADER`,
  r.`IS_LEARNER`,
  COUNT(r.REGION_ID)
FROM
  INFORMATION_SCHEMA.TIKV_REGION_PEERS r,
  INFORMATION_SCHEMA.TIKV_STORE_STATUS s,
  INFORMATION_SCHEMA.tikv_region_status rs
WHERE r.STORE_ID = s.STORE_ID
  AND r.`REGION_ID` = rs.`REGION_ID`
  AND rs.`DB_NAME` = 'xxx'
  AND rs.`TABLE_NAME` = 'tablename'
  AND r.`IS_LEARNER`=0
GROUP BY rs.`DB_NAME`,
  rs.`TABLE_NAME`,
  s.`ADDRESS`,
  r.`IS_LEADER`,
  r.`IS_LEARNER`
  ORDER BY r.`IS_LEADER`;

is_leader可以看是否leader region

[niegang]

@我是咖啡哥

SELECT
  rs.`DB_NAME`,
  rs.`TABLE_NAME`,
  s.ADDRESS,
  r.`IS_LEADER`,
  r.`IS_LEARNER`,
  COUNT(r.REGION_ID)
FROM
  INFORMATION_SCHEMA.TIKV_REGION_PEERS r,
  INFORMATION_SCHEMA.TIKV_STORE_STATUS s,
  INFORMATION_SCHEMA.tikv_region_status rs
WHERE r.STORE_ID = s.STORE_ID
  AND r.`REGION_ID` = rs.`REGION_ID`
  AND rs.`DB_NAME` = 'xxx'
  AND rs.`TABLE_NAME` = 'tablename'
  AND r.`IS_LEARNER`=0
GROUP BY rs.`DB_NAME`,
  rs.`TABLE_NAME`,
  s.`ADDRESS`,
  r.`IS_LEADER`,
  r.`IS_LEARNER`
  ORDER BY r.`IS_LEADER`;

执行成功！ 厉害

[niegang]

执行失败。。

[niegang]

好的~ 感谢

[niegang]

我试试

[我是咖啡哥]

表名前后那个引号有问题