索引比数据大的多，这个正常吗？

OceanBase 技术问题
#1

【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】
【 使用版本 】4.2.1.8
【问题描述】从OCP中查看数据库详情，显示数据有100G，但索引有500G，这个正常么？还有就是如何统计表上的索引大小？
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

1 个赞
#3

日志采集场景: 未能明确问题的场景
日志采集命令: obdiag gather scene run --scene=observer.unknown

为了更好地了解和解决您提到的数据大小与索引大小不匹配的问题，我们建议您先使用 obdiag 工具进行一次全面的日志采集。由于当前 obdiag 没有专门针对索引大小异常的场景，因此选择未知问题场景进行信息收集。

另外，为了确定具体的问题，在此需要您提供一些信息：

  1. 请确认您的 OceanBase 数据库版本。
  2. 是否最近进行了大量的 DDL 操作（例如创建或删除索引）？
  3. 是否可以提供具体的表名以及这些表上的索引定义？
  4. 是否有定期执行过合并操作（compaction），如果有，请说明最近一次执行的时间。
  5. 是否可以提供通过 SQL 查询得到的更详细的存储统计信息，例如使用如下命令：
    SELECT table_name, index_name, index_length FROM oceanbase.__all_table WHERE index_type != 0;

在获取到上述信息后，我们可以进一步分析是否存在索引膨胀或其他潜在问题，并给出相应的解决方案。同时，如果您能分享采集回来的日志压缩包，这将有助于我们更快地定位问题。

附上敏捷诊断工具 obdiag 使用帮助链接

3 个赞
#5

如果表上创建了多个全局索引或复合索引，尤其是对大字段（如 VARCHAR 或 TEXT 类型）建立索引，会导致索引占用的空间远大于数据本身。

索引大小可以这样查询
select /*+ READ_CONSISTENCY(WEAK) */
t1.table_name,
round(sum(t2.data_size/1024/1024/1024), 2) data_size_gb ,
round(sum(t2.required_size)/1024/1024/1024, 2) required_size_gb
from dba_ob_table_locations t1,dba_ob_tablet_replicas t2
where
t1.svr_ip=t2.svr_ip
and t1.ls_id=t2.ls_id
and t1.tablet_id=t2.tablet_id
– and t1.role=‘leader’
and t1.database_name=‘database_name’
and (t1.table_name=‘table_name’
or t1.data_table_id in (select table_id from dba_ob_table_locations where table_name=‘table_name’ limit 1))
group by t1.table_name
order by 3 desc;

3 个赞
#6

看下表是否创建了很多索引，创建很多索引的情况下，索引比表大也是符合预期的

3 个赞
#7

有json数据吗？

3 个赞
#8

会有这种情况出现的

3 个赞
#9

这样查出来是整个表的大小，不太符合预期

2 个赞
#10

有的

2 个赞
#11

根据您的反馈，我理解您希望获取更详细的索引大小信息，而不仅仅是整个表的大小。为了帮助您更准确地统计特定表上的索引大小，您可以使用以下 SQL 查询来获得更细粒度的信息：

SELECT 
    table_name, 
    index_name, 
    data_length + index_length AS total_size 
FROM 
    oceanbase.__all_table 
WHERE 
    table_name = '您的表名' AND 
    index_type != 0;

请将 '您的表名' 替换为实际需要查询的表名称。此查询会返回指定表中每个索引的数据长度和索引长度之和，从而让您了解各个索引的具体占用空间。

此外，如果您想查看所有表及其索引的大小分布情况，可以去掉 WHERE 子句中的条件限制：

SELECT 
    table_name, 
    index_name, 
    data_length + index_length AS total_size 
FROM 
    oceanbase.__all_table 
WHERE 
    index_type != 0;

这将列出数据库内所有非聚集索引（即普通索引）的相关信息。

对于您提到的索引比数据大很多的情况，在某些情况下这是正常的，例如：

  • 如果存在大量唯一性约束或主键索引，这些类型的索引通常会占用较多的空间。
  • 当表中有高基数列（如 UUID 或者很长的字符串）作为索引时，也会导致索引文件较大。
  • 如果频繁更新被索引字段，可能会引起索引碎片化，进而增加索引存储需求。

然而，如果索引确实异常庞大，可能的原因包括但不限于：

  • 索引设计不合理，比如不必要的复合索引或者对低选择性的列创建了索引。
  • 数据库经历了大量的插入、删除操作后没有及时进行优化维护。
  • 存在重复或冗余索引。

建议您先通过上述 SQL 查询检查具体的索引大小，并结合业务逻辑评估现有索引是否合理。如果有明显不合理的索引设置，考虑对其进行调整或重构。同时，定期执行合并操作可以帮助减少因数据变动带来的索引膨胀问题。

以上内容基于 OceanBase 社区版的功能特性提供解答。如果您还有其他疑问或需要进一步的帮助，请随时告知。

更多信息请查看:

  1. 大小账号
  2. SQL 调优典型场景和案例
  3. DELETE_TABLE_STATS
  4. SQL 规范
  5. 转储和合并
  6. GATHER_TABLE_STATS
  7. IMPORT_SCHEMA_STATS
  8. IMPORT_TABLE_STATS
  9. CREATE_STAT_TABLE
  10. IMPORT_INDEX_STATS

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

1 个赞
#13

with table_id_cet as (
SELECT
DISTINCT table_id,
table_type,
L.SVR_IP,
L.DATABASE_NAME,
L.TABLE_NAME,
L.PARTITION_NAME,
L.SUBPARTITION_NAME,
L.TABLET_ID,
L.LS_ID,
L.ROLE
FROM
CDB_OB_TABLE_LOCATIONS L
WHERE
L.database_name = ‘{database_name}’
AND L.TABLE_NAME = ‘{table_name}’
)
select
L.table_name AS “名称”,
L.table_type,
round(sum(R.DATA_SIZE) / 1024 / 1024 / 1024, 2) as “数据量(GB)”,
round(sum(R.REQUIRED_SIZE) / 1024 / 1024 / 1024, 2) as “磁盘占用量(GB)”
from
(
select
*
from
table_id_cet
UNION ALL
SELECT
DISTINCT table_id,
table_type,
SVR_IP,
DATABASE_NAME,
INDEX_NAME as table_name,
PARTITION_NAME,
SUBPARTITION_NAME,
TABLET_ID,
LS_ID,
ROLE ROLE
FROM
CDB_OB_TABLE_LOCATIONS L
WHERE
data_table_id =(
select
DISTINCT table_id
from
table_id_cet
)
) L
LEFT JOIN CDB_OB_TABLET_REPLICAS R ON (
L.TABLET_ID = R.TABLET_ID
AND L.svr_ip = R.svr_ip
)
GROUP BY L.table_name,L.table_type;

1 个赞
#14

有这种情况啊

1 个赞
#15

这个是已知问题，已反馈研发老师了，感谢反馈

2 个赞
#16

这个得查一下索引视图

#17

如果你在刷积分 请发一起唠嗑这个版本

#18

是不是创建很多歌复核索引