【 TiDB 使用环境】生产环境
【 TiDB 版本】6.5.1
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【附件：截图/日志/监控】
SELECT
db_name,
table_name,
ROUND(SUM(CASE WHEN IS_INDEX = 1 THEN total_size / cnt ELSE 0 END),2) AS Approximate_Size_Index,
ROUND(SUM(CASE WHEN IS_INDEX = 0 THEN total_size / cnt ELSE 0 END),2) AS Approximate_Size_data,
ROUND(
SUM(CASE WHEN IS_INDEX = 1 THEN total_size / cnt ELSE 0 END) /
(SELECT ROUND(AVG(value), 2)
FROM METRICS_SCHEMA.store_size_amplification
WHERE value > 0),
2
) AS Disk_Size_Index,
ROUND(
SUM(CASE WHEN IS_INDEX = 0 THEN total_size / cnt ELSE 0 END) /
(SELECT ROUND(AVG(value), 2)
FROM METRICS_SCHEMA.store_size_amplification
WHERE value > 0),
2
) AS Disk_Size_data,
ROUND(SUM(total_size / cnt), 2) Approximate_Size,
ROUND(SUM(total_size / cnt / (SELECT
ROUND(AVG(value), 2)
FROM
METRICS_SCHEMA.store_size_amplification
WHERE
value > 0)),
2) Disk_Size
FROM
(SELECT
db_name,
table_name,
region_id,
IS_INDEX,
SUM(Approximate_Size) total_size,
COUNT(*) cnt
FROM
information_schema.TIKV_REGION_STATUS
GROUP BY db_name , table_name , region_id,IS_INDEX) tabinfo
GROUP BY db_name , table_name;
统计TIKV_REGION_STATUS表发现索引占用的磁盘容量比数据占用的磁盘多很多 这是为啥？
此方法只是预估，并不能准确判断占用大小
首先楼上说的对，这个是预估的容量，不是准确占用。
然后看具体情况吧，假如一个表3个字段，你排列组合可以建7条索引。
这种极端情况下，索引比表的数据多也不奇怪吧。
重点分析那个表的索引占比高。找出挑的来看看具体是什么情况。
你把你索引占表比例大的表结构贴出来看下
CREATE TABLE aaa (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’,
unique_key varchar(200) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
shop_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘’,
brand_id int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘’,
tid varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
oid varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
order_status tinyint(2) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
step_trade_status tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
is_live tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
product_no varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
num_iid varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
skc_no varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
sku_sn varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
sku_id varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
pro_title varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
order_sku_sn varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
pro_cid varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
pro_type tinyint(3) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
virtual_sku_sn varchar(100) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘’,
virtual_skc_no varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
market_price decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
goods_price decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
transaction_price decimal(14, 2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘’,
num int(10) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘’,
order_sales_discount decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
expand_card_expand_price_used_suborder decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
expand_card_basic_price_used_suborder decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
adjust_fee decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
discount decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
total_fee decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
discount_part decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
discount_total decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
divide_order_fee decimal(20, 8) NOT NULL DEFAULT ‘0.00000000’ COMMENT ‘’,
post_fee decimal(20, 8) DEFAULT ‘0’ COMMENT ‘’,
created datetime NOT NULL COMMENT ‘’,
pay_time datetime DEFAULT NULL COMMENT ‘’,
shipping_time datetime DEFAULT NULL COMMENT ‘’,
add_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘’,
receiver_city varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘’ COMMENT ‘’,
should_pay decimal(20, 8) DEFAULT ‘0’ COMMENT ‘’,
PRIMARY KEY (shop_id, oid)
/*T![clustered_index] CLUSTERED */,
UNIQUE KEY idx_unique (id),
KEY idx_tid (tid),
KEY idx_sku_id (sku_id),
KEY idx_sku_sn (sku_sn),
KEY idx_pay_time (pay_time),
KEY idx_created (created),
KEY idx_mix_1 (
shop_id,
num_iid,
sku_id,
sku_sn,
step_trade_status,
pay_time
),
KEY idx_mix_2 (
shop_id,
sku_sn,
pay_time,
step_trade_status
),
KEY idx_mix_3 (update_time, step_trade_status),
KEY idx_unique_key (unique_key)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci
这个表的索引比数据预估的容量还多
索引存的是索引的值加主键，你这索引确实太多了
可以考虑调用pdserver接口获取，然后在程序处理下