-- 創(chuàng)建測試表
CREATE TABLE t_orders (
    order_id INT,
    order_time TIMESTAMP,
    amount NUMERIC(10,2)
);
-- 插入100萬條測試數(shù)據(jù)
INSERT INTO t_orders 
VALUES (generate_series(1,1000000), 
        CURRENT_TIMESTAMP - (random()*365)::INT, 
        random()*1000);

-- 無索引時(shí)查詢：全表掃描，耗時(shí)較長
EXPLAIN ANALYZE 
SELECT * FROM t_orders WHERE order_time > '2024-01-01';
-- 執(zhí)行結(jié)果：Seq Scan on t_orders (cost=0.00..22000.00 rows=300000 width=20) (actual time=0.03..500.12 ms)

-- 創(chuàng)建Btree索引
CREATE INDEX idx_orders_time ON t_orders USING btree(order_time);

-- 有索引時(shí)查詢：索引掃描，耗時(shí)顯著降低
EXPLAIN ANALYZE 
SELECT * FROM t_orders WHERE order_time > '2024-01-01';
-- 執(zhí)行結(jié)果：Index Scan using idx_orders_time on t_orders (cost=0.43..8000.00 rows=300000 width=20) (actual time=0.05..80.36 ms)

-- 創(chuàng)建表達(dá)式索引（忽略大小寫查詢）
CREATE INDEX idx_emp_upper_name ON emp (upper(ename));

-- 查詢時(shí)直接使用表達(dá)式，觸發(fā)索引
EXPLAIN ANALYZE 
SELECT * FROM emp WHERE upper(ename) = 'SMITH';

-- 創(chuàng)建聯(lián)合索引（order_time過濾性強(qiáng)，放在左側(cè)）
CREATE INDEX idx_orders_time_amount ON t_orders (order_time, amount);

-- 有效查詢：命中聯(lián)合索引（使用前導(dǎo)列order_time）
SELECT * FROM t_orders WHERE order_time > '2024-01-01' AND amount > 500;

-- 無效查詢：未使用前導(dǎo)列，無法命中索引
SELECT * FROM t_orders WHERE amount > 500;

-- 1. 前匹配：Btree索引
CREATE INDEX idx_emp_name_pattern ON emp (ename text_pattern_ops);
SELECT * FROM emp WHERE ename LIKE 'SM%';

-- 2. 后匹配：reverse()表達(dá)式索引
CREATE INDEX idx_emp_name_reverse ON emp (reverse(ename) collate "C");
SELECT * FROM emp WHERE reverse(ename) LIKE reverse('%ITH'); -- 等價(jià)于ename LIKE '%ITH'

-- 3. 中間匹配：TRGM索引
CREATE EXTENSION sys_trgm; -- 啟用插件
CREATE INDEX idx_emp_name_trgm ON emp USING gin(ename gin_trgm_ops);
SELECT * FROM emp WHERE ename LIKE '%MIT%';

-- 查看索引使用情況（idx_scan為0表示未使用）
SELECT relname AS 表名, indexrelname AS 索引名, idx_scan AS 掃描次數(shù) 
FROM sys_stat_user_indexes 
ORDER BY idx_scan;

-- 重建索引（優(yōu)化索引結(jié)構(gòu)）
REINDEX INDEX idx_orders_time;

-- 全表VACUUM（釋放刪除數(shù)據(jù)的空間，確保覆蓋索引生效）
VACUUM ANALYZE t_orders;

-- 原始查詢：優(yōu)化器選擇Nested Loop，耗時(shí)較長
EXPLAIN ANALYZE
SELECT o.order_id, c.cust_name 
FROM t_orders o JOIN t_customers c 
ON o.cust_id = c.cust_id 
WHERE o.order_time > '2024-01-01';

-- 使用HINT強(qiáng)制HashJoin，提升效率
EXPLAIN ANALYZE
SELECT /*+HashJoin(o c)*/ 
o.order_id, c.cust_name 
FROM t_orders o JOIN t_customers c 
ON o.cust_id = c.cust_id 
WHERE o.order_time > '2024-01-01';

-- 1. 磁盤I/O優(yōu)化（SSD磁盤可降低隨機(jī)讀成本）
SET random_page_cost = 2.0; -- 默認(rèn)4.0，SSD建議2.0-3.0
SET seq_page_cost = 0.5;    -- 默認(rèn)1.0，SSD建議0.5-1.0

-- 2. CPU性能優(yōu)化（高性能CPU可降低CPU代價(jià)系數(shù)）
SET cpu_tuple_cost = 0.005;  -- 默認(rèn)0.01，CPU強(qiáng)可調(diào)至0.005
SET cpu_operator_cost = 0.001; -- 默認(rèn)0.0025，CPU強(qiáng)可調(diào)至0.001

-- 查看當(dāng)前work_mem配置
SHOW work_mem;

-- 臨時(shí)調(diào)整work_mem（應(yīng)對復(fù)雜排序查詢）
SET work_mem = '64MB';

-- 永久配置（kingbase.conf）
work_mem = 32MB;          -- 默認(rèn)1MB，復(fù)雜查詢建議32MB-128MB
maintenance_work_mem = 256MB; -- 維護(hù)操作（如CREATE INDEX）內(nèi)存，默認(rèn)16MB

-- 1. 全局并行配置（kingbase.conf）
max_worker_processes = 16;         -- 最大后臺進(jìn)程數(shù)，建議等于CPU核心數(shù)
max_parallel_workers = 8;          -- 最大并行worker數(shù)
max_parallel_workers_per_gather = 4; -- 單查詢最大并行worker數(shù)

-- 2. 臨時(shí)開啟并行查詢（HINT方式）
EXPLAIN ANALYZE
SELECT /*+Parallel(t_orders 4)*/ 
COUNT(*) FROM t_orders WHERE order_time > '2024-01-01';

-- 創(chuàng)建大表（1000萬行）
CREATE TABLE t_sales (
    sale_id INT,
    sale_date DATE,
    amount NUMERIC(10,2)
);
INSERT INTO t_sales 
VALUES (generate_series(1,10000000), 
        CURRENT_DATE - (random()*365)::INT, 
        random()*2000);

-- 關(guān)閉并行：單進(jìn)程執(zhí)行，耗時(shí)較長
SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE 
SELECT sale_date, SUM(amount) 
FROM t_sales 
GROUP BY sale_date;
-- 執(zhí)行結(jié)果：HashAggregate (cost=200000.00..210000.00 rows=365 width=12) (actual time=1500.23..1800.56 ms)

-- 開啟并行（4個(gè)worker）：多進(jìn)程并行聚集，耗時(shí)降低
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE 
SELECT /*+Parallel(t_sales 4) ParallelHashagg*/
sale_date, SUM(amount) 
FROM t_sales 
GROUP BY sale_date;
-- 執(zhí)行結(jié)果：Finalize HashAggregate (cost=120000.00..130000.00 rows=365 width=12) (actual time=500.12..600.34 ms)