ADMIN CLEANUP INDEX

The ADMIN CLEANUP INDEX statement is used to delete redundant indexes from a table when the table has inconsistent data and index. Note that this syntax does not support foreign key constraints yet.

Synopsis

AdminCleanupStmt

ADMIN CLEANUP - 图1

TableNameList

ADMIN CLEANUP - 图2

  1. AdminCleanupStmt ::=
  2.     'ADMIN' 'CLEANUP' ( 'INDEX' TableName IndexName | 'TABLE' 'LOCK' TableNameList )
  4. TableNameList ::=
  5.     TableName ( ',' TableName )*

Examples

Assume that the tbl table in a database has inconsistent data and index due to some reasons (for example, some row data is lost in the cluster in a disaster recovery scenario):

  1. SELECT * FROM tbl;
  2. ERROR 1105 (HY000): inconsistent index idx handle count 3 isn't equal to value count 2
  4. ADMIN CHECK INDEX tbl idx ;
  5. ERROR 1105 (HY000): handle &kv.CommonHandle{encoded:[]uint8{0x1, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0xf8}, colEndOffsets:[]uint16{0xa}}, index:types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, collation:"utf8mb4_bin", b:[]uint8{0x0}, x:interface {}(nil)} != record:<nil>

It can be seen from the error message of the SELECT query that, the tbl table contains two rows of data and three rows of index data, which means inconsistent row and index data. At the same time, at least one index is in dangling state. In this case, you can use the ADMIN CLEANUP INDEX statement to delete the dangling index:

  1. ADMIN CLEANUP INDEX tbl idx;

The execution result is as follows:

  1. ADMIN CLEANUP INDEX tbl idx;
  2. +---------------+
  3. | REMOVED_COUNT |
  4. +---------------+
  5. |             1 |
  6. +---------------+

You can execute the ADMIN CHECK INDEX statement again to check the consistency of data and index, and verify whether the data has been restored to a normal state:

  1. ADMIN CHECK INDEX tbl idx;
  2. Query OK, 0 rows affected (0.01 sec)

ADMIN CLEANUP - 图3

Note

When the data and index are inconsistent due to the loss of replicas:

  • There might be a loss of both row data and index data. To restore the consistency, use the ADMIN CLEANUP INDEX and ADMIN RECOVER INDEX statements together.
  • The ADMIN CLEANUP INDEX statement is always executed in a single thread. When the table data is large, it is recommended to recover the index data by rebuilding the index.
  • When you execute the ADMIN CLEANUP INDEX statement, the corresponding table or index is not locked and TiDB allows other sessions to modify the table records at the same time. However, in this case, ADMIN CLEANUP INDEX might not be able to handle all table records correctly. Therefore, when you execute ADMIN CLEANUP INDEX, avoid modifying the table data at the same time.
  • If you use the enterprise edition of TiDB, you can submit a request to contact the support engineer for help.

The ADMIN CLEANUP INDEX statement is not atomic: if the statement is interrupted during execution, it is recommended to execute it again until it succeeds.

ADMIN CLEANUP - 图4

Note

When the data and index are inconsistent due to the loss of replicas:

  • There might be a loss of both row data and index data. To restore the consistency, use the ADMIN CLEANUP INDEX and ADMIN RECOVER INDEX statements together.
  • The ADMIN CLEANUP INDEX statement is always executed in a single thread. When the table data is large, it is recommended to recover the index data by rebuilding the index.
  • When you execute the ADMIN CLEANUP INDEX statement, the corresponding table or index is not locked and TiDB allows other sessions to modify the table records at the same time. However, in this case, ADMIN CLEANUP INDEX might not be able to handle all table records correctly. Therefore, when you execute ADMIN CLEANUP INDEX, avoid modifying the table data at the same time.
  • If you use the enterprise edition of TiDB, you can submit a request to contact the support engineer for help.

The ADMIN CLEANUP INDEX statement is not atomic: if the statement is interrupted during execution, it is recommended to execute it again until it succeeds.

MySQL compatibility

This statement is a TiDB extension to MySQL syntax.

See also