PolarDB:INVISIBLE INDEX

Background information

PolarDB-X allows you to set the visibility of an index to VISIBLE or INVISIBLE. If you set the visibility of an index to INVISIBLE, the optimizer does not use the index.

Creating or deleting indexes on a large table can take a significant amount of time. However, you can complete this operation in a quick and efficient manner by changing the visibility of indexes in the table. You can evaluate the impact of index deletion on your business by setting the visibility of an index to INVISIBLE without the need to delete the index.

Prerequisites

The version of the PolarDB-X instance is 5.4.17-16835173 or later. For information about how to view instance versions, see View and update the version of an instance.

Usage notes

• The INVISIBLE INDEX feature is available for databases in AUTO mode and DRDS mode.

• The INVISIBLE INDEX feature supports only global secondary indexes. Local indexes are not supported.

• Changing the visibility of an index does not affect the manner in which the index is maintained. The index data changes when the data of the primary table is updated, regardless of whether the visibility of the index is set to INVISIBLE or VISIBLE. If an index has a unique constraint, changing the visibility of the index does not affect the constraint.

• If you set the visibility of an index to INVISIBLE and use the FORCE INDEX hint on the index in a query, the hint does not take effect.

Examples

• Create a table without specifying the VISIBLE or INVISIBLE keyword for an index. By default, the visibility of an index is set to VISIBLE.

  create table t_order (
    `id` bigint(11),
    `order_id` varchar(20),
    `buyer_id` varchar(20),
    global index `g_order_id` (order_id) partition by key(order_id)
  ) partition by hash(`id`);

• Specify the visibility of an index when you create a table.

  create table t_order (
    `id` bigint(11),
    `order_id` varchar(20),
    `buyer_id` varchar(20),
    global index `g_order_id` (order_id) partition by key(order_id) invisible
  ) partition by hash(`id`);

• Add an index to a table and specify the visibility of the index. If you do not specify the VISIBLE or INVISIBLE keyword, the index is visible.

  alter table t_order add global index g_buyer_id(buyer_id) partition by hash(buyer_id) invisible;

• Change the visibility of an index.

  -- Change the visibility of the index to INVISIBLE.
  alter table t_order alter index `g_order_id` invisible;
  -- Change the visibility of the index to VISIBLE.
  alter table t_order alter index `g_order_id` visible;

FAQ

• After I set the visibility of an index to INVISIBLE, which aspects should I check to evaluate the impact of index deletion on my business?

  You can check several aspects, including but not limited to the following ones:

  • If the business load remains unchanged, check whether the CPU and memory metrics of the PolarDB-X instance vary significantly.

  • Check whether unexpected slow SQL queries occur.

  • If you are conducting a stress test, check whether the test results vary significantly.

  • If you have used the FORCE INDEX hint to specify the index in query statements, check whether these queries become slow queries.

• If you change the visibility of an index to INVISIBLE, all SQL statements that use the index are affected. How do I view the execution result of a specific SQL statement that does not use the index?

  In this case, you do not need to use the invisible index. You can use the IGNORE INDEX hint to instruct the query optimizer to ignore the index when executing a query.