CHECK GLOBAL INDEX
CHECK GLOBAL INDEX
对全局二级索引进行检查或订正,主要用于检查主表和索引表的数据是否完全一致。GSI的使用参考 DRDS 全局二级索引使用文档
- 主要检查4种类型的数据不一致:数据分片错误、索引丢失、孤儿索引和索引数据不一致;
- 对于发现的错误进行数据订正。
对大表的GSI校验可能会花费较多的时间,可以使用 hint 指定 PURE_ASYNC_DDL_MODE 纯异步 DDL 模式执行,参考 DDL 任务管理-控制参数与行为
语法:
CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]
- gsi_name 为对应的需要校验的全局二级索引名
- tbl_name 为对应全局二级索引所在的主表,可省略,指定的时候会检查索引和主表的索引关系是否正确
- extra_cmd 为保留的额外指令,目前有以下几种
extra_cmd | 定义 |
---|---|
- | 不指定任何关键字时,即表示仅检查全局二级索引 |
SHOW | 显示指定GSI表的最近一次校验或订正的结果 |
CORRECTION_BASED_ON_PRIMARY | 进行索引订正,以主表为基准 |
注意:对全局二级索引进行校验或订正时会占用一定的系统资源,特别是订正操作会对主表或索引表分批加锁校验并订正,请在业务低谷期进行操作
样例
-- 校验没有发现错误
mysql> CHECK GLOBAL INDEX `g_i_check`;
+-------------+------------+--------+-------------+-----------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+-----------------------------+
| `g_i_check` | SUMMARY | -- | -- | OK (7025/7025 rows checked) |
+-------------+------------+--------+-------------+-----------------------------+
1 row in set (1.40 sec)
-- 校验发现错误
mysql> CHECK GLOBAL INDEX g_i_check;
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | ORPHAN | FOUND | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | CONFLICT | FOUND | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | MISSING | FOUND | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7025 rows checked) |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (1.92 sec)
-- 订正
mysql> CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| `g_i_check` | SUMMARY | -- | -- | Done. Use SQL: { CHECK GLOBAL INDEX `g_i_check` SHOW; } to get result. |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
1 row in set (1.40 sec)
-- 查看最近一次校验或订正的报告
mysql> CHECK GLOBAL INDEX `g_i_check` SHOW;
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | MISSING | REPAIRED | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | CONFLICT | REPAIRED | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | ORPHAN | REPAIRED | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7026 rows checked.) Finish time: 2020-01-13 14:41:51.0 |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)
注意:如果数据存在多种错误,同一行数据会报多个 ERROR_TYPE
列名描述:
列名 | 描述 |
---|---|
GSI_TABLE | 全局二级索引名 |
ERROR_TYPE | 错误类型(MISSING:索引丢失 ORPHAN:孤儿索引 CONFLICT:索引数据不一致 ERROR_SHARD:数据分片位置错误 SUMMARY:结果汇总) |
STATUS | 状态(FOUND:发现错误 REPAIRED:已修复) |
PRIMARY_KEY | 主键 |
DETAILS | 错误的详细信息 |
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论