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)