您可以使用CHECK GLOBAL INDEX语句检查主表和索引表的数据是否完全一致,并修订不一致的数据。

语法

CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]
参数 说明
gsi_name 需要校验的全局二级索引名。
tbl_name 全局二级索引所在的主表,非必选。若您输入具体主表名称,将会检查索引和主表的索引关系是否正确。
extra_cmd 保留的额外指令,目前支持如下指令:
  • -:不指定任何关键字时,即表示仅检查全局二级索引。
  • SHOW:显示指定GSI表的最近一次校验或订正的结果。
  • CORRECTION_BASED_ON_PRIMARY:进行索引订正,以主表为基准。
说明
  • 对全局二级索引进行校验或订正时会占用一定的系统资源,特别是订正操作会对主表或索引表分批加锁校验并订正,建议您在业务低谷期进行操作,更多关于GSI的使用方式,请参见使用全局二级索引
  • 对大表的GSI校验可能会花费较多的时间,可以使用HINT指定PURE_ASYNC_DDL_MODE纯异步DDL模式执行,请参见控制参数与行为

示例

  • 您可以使用如下语句进行校验:
    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 错误类型,取值范围如下:
  • MISSING:索引丢失
  • ORPHAN:孤儿索引
  • CONFLICT:索引数据不一致
  • ERROR_SHARD:数据分片位置错误
  • SUMMARY:结果汇总
STATUS 状态,取值范围如下:
  • FOUND:发现错误
  • REPAIRED:已修复
PRIMARY_KEY 主键。
DETAILS 错误的详细信息。