# 连接数据库
$ ksql -U fgedu -d fgedudb
# 创建测试表
CREATE TABLE fgedu_employee (
id INTEGER PRIMARY KEY,
name VARCHAR(50),
age INTEGER,
department_id INTEGER,
salary NUMERIC(10,2)
);
# 创建索引
CREATE INDEX idx_fgedu_employee_department_id ON fgedu_employee(department_id);
# 插入测试数据
INSERT INTO fgedu_employee VALUES (1, ‘John’, 30, 1, 5000.00);
INSERT INTO fgedu_employee VALUES (2, ‘Jane’, 25, 1, 4500.00);
INSERT INTO fgedu_employee VALUES (3, ‘Robert’, 35, 2, 6000.00);
INSERT INTO fgedu_employee VALUES (4, ‘Alice’, 28, 2, 5500.00);
INSERT INTO fgedu_employee VALUES (5, ‘Bob’, 32, 3, 5800.00);
# 使用INDEX Hint
EXPLAIN ANALYZE SELECT /*+ INDEX(fgedu_employee idx_fgedu_employee_department_id) */ * FROM fgedu_employee WHERE department_id = 1;
# 使用FULL Hint
EXPLAIN ANALYZE SELECT /*+ FULL(fgedu_employee) */ * FROM fgedu_employee WHERE department_id = 1;
# 使用PARALLEL Hint
EXPLAIN ANALYZE SELECT /*+ PARALLEL(fgedu_employee 4) */ * FROM fgedu_employee;

# 连接数据库
$ ksql -U fgedu -d fgedudb
# 创建存储过程
CREATE OR REPLACE FUNCTION fgedu_get_employees(p_department_id INTEGER)
RETURNS TABLE(id INTEGER, name VARCHAR(50), age INTEGER, salary NUMERIC(10,2))
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY EXECUTE
‘SELECT /*+ INDEX(fgedu_employee idx_fgedu_employee_department_id) */ id, name, age, salary
FROM fgedu_employee
WHERE department_id = $1′
USING p_department_id;
END;
$$;
# 执行存储过程
SELECT * FROM fgedu_get_employees(1);
id | name | age | salary
—-+——+—–+——–
1 | John | 30 | 5000.00
2 | Jane | 25 | 4500.00

# 编辑kingbase.conf文件
$ vi /kingbase/app/kingbase.conf
# 优化器相关参数
optimizer_mode = ‘all_rows’ # 优化模式
optimizer_index_caching = 0.9 # 索引缓存比例
optimizer_index_cost_adj = 10 # 索引成本调整
# 重启数据库使参数生效
$ systemctl restart kingbase
# 查看参数设置
$ ksql -U system -d fgedudb -c “SHOW optimizer_mode;”
optimizer_mode
—————-
all_rows
$ ksql -U system -d fgedudb -c “SHOW optimizer_index_caching;”
optimizer_index_caching
————————
0.9

# 连接数据库
$ ksql -U system -d fgedudb
# 查看Hint功能是否启用
SHOW enable_hints;
enable_hints
————–
on
# 启用Hint功能（如果未启用）
SET enable_hints = on;
# 验证Hint功能
EXPLAIN ANALYZE SELECT /*+ FULL(fgedu_employee) */ * FROM fgedu_employee;

# 连接数据库
$ ksql -U system -d fgedudb
# 查看SQL语句的执行计划
EXPLAIN ANALYZE SELECT /*+ INDEX(fgedu_employee idx_fgedu_employee_department_id) */ * FROM fgedu_employee WHERE department_id = 1;
# 比较不同Hint的执行效果
EXPLAIN ANALYZE SELECT * FROM fgedu_employee WHERE department_id = 1;
EXPLAIN ANALYZE SELECT /*+ INDEX(fgedu_employee idx_fgedu_employee_department_id) */ * FROM fgedu_employee WHERE department_id = 1;
EXPLAIN ANALYZE SELECT /*+ FULL(fgedu_employee) */ * FROM fgedu_employee WHERE department_id = 1;
# 查看SQL语句的执行统计信息
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
WHERE query LIKE ‘%fgedu_employee%’
ORDER BY total_exec_time DESC
LIMIT 10;

# 连接数据库
$ ksql -U system -d fgedudb
# 查看优化器统计信息
SELECT
relname,
n_live_tup,
n_dead_tup,
last_analyze
FROM pg_stat_user_tables
WHERE relname = ‘fgedu_employee’;
# 查看索引使用情况
SELECT
indexrelname,更多学习教程公众号风哥教程itpux_com
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = ‘fgedu_employee’;