Skip to content

ALTER REINDEX

语法说明

ALTER TABLE ... ALTER REINDEX 用于对向量表中的数据重新进行分区。

当向量表内的数据记录大幅增长时,原有的聚类中心集合可能不再适用。为此,我们必须对数据重新进行索引处理,旨在识别出新的聚类中心,并据此重新划分数据集。

Note

在重构索引期间,无法对该表进行数据插入操作。

LISTS 的理想值为:

  • 如果总行数<1000000:lists=总行数/1000
  • 如果总行数>1000000:lists=sqrt(总行数)

语法结构

> ALTER TABLE table_name ALTER REINDEX  index_name LISTS=XX

示例

SET GLOBAL experimental_ivf_index = 1;--需设置参数 experimental_ivf_index 值为 1(默认 0)才能使用向量索引
drop table if exists t1;
create table t1(n1 int,n2  vecf32(3));
insert into t1 values(1,"[1,2,3]"),(2,"[2,3,4]"),(3,"[3,4,5]");
create index idx_t1 using ivfflat on t1(n2)  lists=2 op_type "vector_l2_ops";

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
`n1` INT DEFAULT NULL,
`n2` VECF32(3) DEFAULT NULL,
KEY `idx_t1` USING ivfflat (`n2`) lists = 2  op_type 'vector_l2_ops' 
) |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Index_params                            | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
| t1    |          1 | idx_t1   |            1 | n2          | A         |           0 | NULL     | NULL   | YES  | ivfflat    |         |               | {"lists":"2","op_type":"vector_l2_ops"} | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-----------------------------------------+---------+------------+
1 row in set (0.00 sec)

mysql> alter table t1 alter reindex idx_t1 ivfflat lists=100;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
`n1` INT DEFAULT NULL,
`n2` VECF32(3) DEFAULT NULL,
KEY `idx_t1` USING ivfflat (`n2`) lists = 100  op_type 'vector_l2_ops' 
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-------------------------------------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Index_params                              | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-------------------------------------------+---------+------------+
| t1    |          1 | idx_t1   |            1 | n2          | A         |           0 | NULL     | NULL   | YES  | ivfflat    |         |               | {"lists":"100","op_type":"vector_l2_ops"} | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+-------------------------------------------+---------+------------+
1 row in set (0.01 sec)