语法
CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]
| 参数 | 说明 |
gsi_name |
需要校验的全局二级索引名。 |
tbl_name |
全局二级索引所在的主表，非必选。若您输入具体主表名称，将会检查全局二级索引表和主表间的索引关系是否正确。 |
extra_cmd |
保留的额外指令，目前支持如下指令：
|
说明
- 对全局二级索引表中的数据进行校验或订正时会占用一定的系统资源，特别是订正操作会对主表或索引表内数据分批加锁并订正，建议您在业务低谷期进行操作。更多关于GSI的使用方式，请参见全局二级索引。
示例
- 您可以使用如下语句进行校验：
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)
-
- 若校验发现错误，则返回如下结果：
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)
-
说明 如果数据存在多种错误，同一行数据会报多个ERROR_TYPE值。
- 您可以使用如下语句进行订正：
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)
表 1. 列名说明
| 列名 | 说明 |
| GSI_TABLE | 全局二级索引名。 |
| ERROR_TYPE | 错误类型，取值范围如下：
|
| STATUS | 状态，取值范围如下：
|
| PRIMARY_KEY | 主键。 |
| DETAILS | 错误的详细信息。 |
