DML压力测试
-- 创建测试表
CREATE TABLE hnsw_dml_test (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(200),
description VARCHAR2(4000),
features VECTOR(768),
last_updated DATE
);
-- 创建HNSW索引
CREATE VECTOR INDEX idx_hnsw_dml
ON hnsw_dml_test(features)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;
-- 初始数据：100万条记录
INSERT INTO hnsw_dml_test
SELECT
LEVEL,
'Product_' || LEVEL,
DBMS_RANDOM.STRING('X', 200),
VECTOR(768, RANDOM),
SYSDATE
FROM dual CONNECT BY LEVEL <= 1000000;
COMMIT;
DML性能测试脚本
-- 并发DML测试
DECLARE
TYPE id_array IS TABLE OF NUMBER;
v_ids id_array;
v_start_time NUMBER;
v_end_time NUMBER;
v_dml_count NUMBER := 0;
BEGIN
v_start_time := DBMS_UTILITY.get_time;
-- 模拟批量更新（50个并发会话）
FOR i IN 1..50 LOOP
DBMS_JOB.SUBMIT(
job => i,
what => 'BEGIN
FOR j IN 1..1000 LOOP
UPDATE hnsw_dml_test
SET features = VECTOR(768, RANDOM),
last_updated = SYSDATE
WHERE product_id = MOD(DBMS_RANDOM.VALUE(1,1000000), 100000) + 1;
IF MOD(j, 100) = 0 THEN COMMIT; END IF;
END LOOP;
END;'
);
END LOOP;
-- 等待所有作业完成
DBMS_LOCK.SLEEP(300); -- 5分钟
v_end_time := DBMS_UTILITY.get_time;
v_dml_count := 50000; -- 50会话 × 1000次/会话
DBMS_OUTPUT.PUT_LINE('DML操作总数: ' || v_dml_count);
DBMS_OUTPUT.PUT_LINE('总耗时: ' || (v_end_time - v_start_time)/100 || ' 秒');
DBMS_OUTPUT.PUT_LINE('平均TPS: ' ||
ROUND(v_dml_count / ((v_end_time - v_start_time)/100), 2));
END;
/
查询一致性验证
-- 在DML过程中验证查询一致性
SELECT COUNT(*) as inconsistent_records
FROM (
-- 使用HNSW索引查询
SELECT product_id
FROM hnsw_dml_test
WHERE VECTOR_DISTANCE(features,
VECTOR(768, RANDOM), COSINE) < 0.1
MINUS
-- 使用传统索引验证
SELECT product_id
FROM hnsw_dml_test t
WHERE EXISTS (
SELECT 1 FROM (
SELECT product_id,
VECTOR_DISTANCE(features,
VECTOR(768, RANDOM), COSINE) as dist
FROM hnsw_dml_test
ORDER BY dist
FETCH FIRST 100 ROWS ONLY
) v
WHERE v.product_id = t.product_id
)
);
-- 结果应为0，表示无数据不一致
