› 推特 ›阿里云技术文档›正文

CHECK GLOBAL INDEX_实用 SQL 语句_SQL 手册_分布式关系型数据库 DRDS

admin 阿里云技术文档 2020-02-11 199 0
阿里云服务器优惠

CHECK GLOBAL INDEX

CHECK GLOBAL INDEX

对全局二级索引进行检查或订正，主要用于检查主表和索引表的数据是否完全一致。GSI的使用参考 DRDS 全局二级索引使用文档

  • 主要检查4种类型的数据不一致：数据分片错误、索引丢失、孤儿索引和索引数据不一致；
  • 对于发现的错误进行数据订正。

对大表的GSI校验可能会花费较多的时间，可以使用 hint 指定 PURE_ASYNC_DDL_MODE 纯异步 DDL 模式执行，参考 DDL 任务管理-控制参数与行为

语法：

  1. CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]
  • gsi_name 为对应的需要校验的全局二级索引名
  • tbl_name 为对应全局二级索引所在的主表，可省略，指定的时候会检查索引和主表的索引关系是否正确
  • extra_cmd 为保留的额外指令，目前有以下几种
extra_cmd 定义
- 不指定任何关键字时，即表示仅检查全局二级索引
SHOW 显示指定GSI表的最近一次校验或订正的结果
CORRECTION_BASED_ON_PRIMARY 进行索引订正，以主表为基准

注意：对全局二级索引进行校验或订正时会占用一定的系统资源，特别是订正操作会对主表或索引表分批加锁校验并订正，请在业务低谷期进行操作

样例

  1. -- 校验没有发现错误
  2. mysql> CHECK GLOBAL INDEX `g_i_check`;
  3. +-------------+------------+--------+-------------+-----------------------------+
  4. | GSI_TABLE   | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS                     |
  5. +-------------+------------+--------+-------------+-----------------------------+
  6. | `g_i_check` | SUMMARY    | --     | --          | OK (7025/7025 rows checked) |
  7. +-------------+------------+--------+-------------+-----------------------------+
  8. 1 row in set (1.40 sec)
  10. -- 校验发现错误
  11. mysql> CHECK GLOBAL INDEX g_i_check;
  12. +-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. | GSI_TABLE   | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS                                                                                                                                                                                                                                                                                                                                                                                        |
  14. +-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  15. | `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}}                                                                                                                                                                                              |
  16. | `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}} |
  17. | `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}}                                                                                                                                                                                           |
  18. | `g_i_check` | SUMMARY    | --     | --          | 3 error found (7025/7025 rows checked)                                                                                                                                                                                                                                                                                                                                                         |
  19. +-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  20. 4 rows in set (1.92 sec)
  22. -- 订正
  23. mysql> CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;
  24. +-------------+------------+--------+-------------+------------------------------------------------------------------------+
  25. | GSI_TABLE   | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS                                                                |
  26. +-------------+------------+--------+-------------+------------------------------------------------------------------------+
  27. | `g_i_check` | SUMMARY    | --     | --          | Done. Use SQL: { CHECK GLOBAL INDEX `g_i_check` SHOW; } to get result. |
  28. +-------------+------------+--------+-------------+------------------------------------------------------------------------+
  29. 1 row in set (1.40 sec)
  31. -- 查看最近一次校验或订正的报告
  32. mysql> CHECK GLOBAL INDEX `g_i_check` SHOW;
  33. +-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  34. | GSI_TABLE   | ERROR_TYPE | STATUS   | PRIMARY_KEY | DETAILS                                                                                                                                                                                                                                                                                                                                                                                        |
  35. +-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  36. | `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}}                                                                                                                                                                                           |
  37. | `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}} |
  38. | `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}}                                                                                                                                                                                              |
  39. | `g_i_check` | SUMMARY    | --       | --          | 3 error found (7025/7026 rows checked.) Finish time: 2020-01-13 14:41:51.0                                                                                                                                                                                                                                                                                                                     |
  40. +-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  41. 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 错误的详细信息
版权声明

本文仅代表作者观点，不代表本站立场。
本文系作者授权发表，未经许可，不得转载。

评论

-----