yashanb教程FG196-YashanDB全局索引优化
本文档风哥主要介绍YashanDB全局索引优化相关知识，包括YashanDB全局索引的概念、架构、优势、规划、设计、实现、监控、故障排查、生产案例与实战讲解等内容，风哥教程参考YashanDB官方文档性能调优内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 YashanDB全局索引的概念
YashanDB全局索引是指在分区表上创建的索引，索引条目包含来自所有分区的数据，而不仅仅是单个分区的数据。全局索引的索引键值在整个表范围内是唯一的，或者按照一定的顺序排列，与表的分区方式无关。更多视频教程www.fgedu.net.cn
- 全局索引是在分区表上创建的索引，包含所有分区的数据
- 全局索引的索引键值在整个表范围内是唯一的或有序的
- 全局索引与表的分区方式无关，索引条目分布在索引的各个分区中
- 全局索引可以加速跨分区的查询
1.2 YashanDB全局索引的架构
YashanDB全局索引的架构包括：
- 索引结构：全局索引使用B-tree结构，与普通索引类似
- 索引分区：全局索引可以是分区的或非分区的。分区全局索引的分区方式可以与表的分区方式不同
- 索引条目：每个索引条目包含索引键值和对应的rowid，rowid包含分区号和行在分区内的位置
- 维护机制：当表中的数据发生变化时，全局索引需要相应地更新
1.3 YashanDB全局索引的优势
YashanDB全局索引的优势包括：
- 跨分区查询：可以加速跨分区的查询，特别是当查询条件涉及多个分区时
- 唯一约束：可以在整个表范围内强制执行唯一约束
- 排序操作：可以加速需要排序的操作，因为索引已经按照一定的顺序排列
- 范围查询：可以加速范围查询，特别是当查询范围跨越多个分区时
- 灵活性：索引的分区方式可以与表的分区方式不同，提供更大的灵活性
Part02-生产环境规划与建议
2.1 YashanDB全局索引规划
YashanDB全局索引规划建议：
– 查询模式：根据查询模式选择是否使用全局索引
– 数据分布：考虑数据的分布情况，选择合适的索引键
– 维护成本：考虑全局索引的维护成本，包括空间和时间开销
– 性能需求：根据性能需求，选择合适的索引类型和分区方式
– 可用性：考虑全局索引的可用性，特别是在分区维护操作期间
# 全局索引规划流程
1. 分析查询模式：分析应用程序的查询模式，确定是否需要跨分区查询
2. 评估数据分布：评估表中数据的分布情况，选择合适的索引键
3. 估算索引大小：估算全局索引的大小，确保有足够的存储空间
4. 评估维护成本：评估全局索引的维护成本，包括插入、更新和删除操作的开销
5. 制定索引策略：根据分析结果，制定合适的全局索引策略
6. 测试和验证：通过测试验证全局索引的性能和可用性
# 全局索引适用场景
– 跨分区查询频繁的场景
– 需要在整个表范围内强制执行唯一约束的场景
– 需要加速范围查询的场景
– 需要加速排序操作的场景
– 数据分布不均匀的场景
2.2 YashanDB全局索引设计
YashanDB全局索引设计建议：
– 索引键选择：选择高选择性的列作为索引键
– 索引类型：根据查询需求选择合适的索引类型（B-tree、Bitmap等）
– 分区策略：选择合适的索引分区策略
– 存储管理：合理管理索引的存储空间
– 维护策略：制定合理的索引维护策略
# 全局索引设计步骤
1. 选择索引键：选择高选择性的列作为索引键，避免使用低选择性的列
2. 确定索引类型：根据查询需求选择合适的索引类型
3. 设计分区策略：如果使用分区全局索引，选择合适的分区策略
4. 估算索引大小：估算索引的大小，确保有足够的存储空间
5. 制定维护计划：制定索引的维护计划，包括重建、重组等操作
6. 测试和优化：通过测试优化索引设计
# 全局索引设计最佳实践
– 选择高选择性的列作为索引键
– 避免在索引键中包含过多的列
– 合理设置索引的存储参数
– 定期维护索引，确保索引的健康状态
– 监控索引的使用情况，及时调整索引策略
2.3 YashanDB全局索引最佳实践
YashanDB全局索引最佳实践：
# 1. 索引键选择
– 选择高选择性的列：高选择性的列能够减少索引扫描的范围，提高查询性能
– 选择经常用于查询条件的列：这样可以充分利用索引，加速查询
– 选择适合排序的列：如果查询需要排序，选择适合排序的列作为索引键
– 避免使用低选择性的列：低选择性的列会导致索引扫描范围过大，影响性能
# 2. 索引类型选择
– B-tree索引：适用于大多数查询场景，特别是等值查询和范围查询
– Bitmap索引：适用于低选择性列，特别是在数据仓库环境中
– 函数索引：适用于基于函数的查询
– 分区索引：适用于大表，提高管理和查询性能
# 3. 分区策略选择
– 范围分区：适用于按时间或连续值分区的场景
– 列表分区：适用于按离散值分区的场景
– 哈希分区：适用于数据分布均匀的场景
– 复合分区：适用于复杂的数据分布场景
# 4. 存储管理
– 合理设置表空间：为索引设置单独的表空间，便于管理
– 合理设置存储参数：根据索引的大小和访问模式，设置合理的存储参数
– 定期监控空间使用：定期监控索引的空间使用情况，及时扩展表空间
# 5. 维护策略
– 定期重建索引：定期重建索引，消除索引碎片，提高索引性能
– 定期收集统计信息：定期收集索引的统计信息，确保优化器能够生成正确的执行计划
– 监控索引使用情况：监控索引的使用情况，及时调整索引策略
– 处理索引失效：及时处理索引失效的情况，确保索引的可用性
Part03-生产环境项目实施方案
3.1 YashanDB全局索引实现
3.1.1 创建全局索引
# 1. 创建分区表
SQL> CREATE TABLE fgedu.orders (
2 order_id NUMBER PRIMARY KEY,
3 order_date DATE,
4 customer_id NUMBER,
5 amount NUMBER
6 )
7 PARTITION BY RANGE (order_date) (
8 PARTITION p202501 VALUES LESS THAN (TO_DATE(‘2025-02-01’, ‘YYYY-MM-DD’)),
9 PARTITION p202502 VALUES LESS THAN (TO_DATE(‘2025-03-01’, ‘YYYY-MM-DD’)),
10 PARTITION p202503 VALUES LESS THAN (TO_DATE(‘2025-04-01’, ‘YYYY-MM-DD’)),
11 PARTITION p202504 VALUES LESS THAN (TO_DATE(‘2025-05-01’, ‘YYYY-MM-DD’)),
12 PARTITION p202505 VALUES LESS THAN (TO_DATE(‘2025-06-01’, ‘YYYY-MM-DD’)),
13 PARTITION p202506 VALUES LESS THAN (TO_DATE(‘2025-07-01’, ‘YYYY-MM-DD’))
14 );
Table created.
# 2. 创建全局索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id) GLOBAL;
Index created.
# 3. 创建分区全局索引
SQL> CREATE INDEX idx_orders_amount ON fgedu.orders(amount)
2 GLOBAL PARTITION BY RANGE (amount) (
3 PARTITION p_amount_0_1000 VALUES LESS THAN (1000),
4 PARTITION p_amount_1000_5000 VALUES LESS THAN (5000),
5 PARTITION p_amount_5000_10000 VALUES LESS THAN (10000),
6 PARTITION p_amount_10000_MAX VALUES LESS THAN (MAXVALUE)
7 );
Index created.
# 4. 创建唯一全局索引
SQL> CREATE UNIQUE INDEX idx_orders_order_id ON fgedu.orders(order_id) GLOBAL;
Index created.
3.1.2 维护全局索引
# 1. 重建全局索引
SQL> ALTER INDEX idx_orders_customer_id REBUILD;
Index altered.
# 2. 重建分区全局索引的特定分区
SQL> ALTER INDEX idx_orders_amount REBUILD PARTITION p_amount_0_1000;
Index altered.
# 3. 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(‘FGEDU’, ‘IDX_ORDERS_CUSTOMER_ID’);
PL/SQL procedure successfully completed.
# 4. 监控索引使用情况
SQL> SELECT index_name, table_name, uniqueness, status
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
INDEX_NAME TABLE_NAME UNIQUENESS STATUS
——————– ————– ———- ——–
IDX_ORDERS_CUSTOMER_ID ORDERS NONUNIQUE VALID
IDX_ORDERS_AMOUNT ORDERS NONUNIQUE VALID
IDX_ORDERS_ORDER_ID ORDERS UNIQUE VALID
# 5. 查看索引的分区情况
SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 WHERE index_name = ‘IDX_ORDERS_AMOUNT’;
INDEX_NAME PARTITION_NAME STATUS
——————– ——————– ——–
IDX_ORDERS_AMOUNT P_AMOUNT_0_1000 USABLE
IDX_ORDERS_AMOUNT P_AMOUNT_1000_5000 USABLE
IDX_ORDERS_AMOUNT P_AMOUNT_5000_10000 USABLE
IDX_ORDERS_AMOUNT P_AMOUNT_10000_MAX USABLE
3.1.3 优化全局索引
# 1. 调整索引存储参数
SQL> ALTER INDEX idx_orders_customer_id STORAGE (
2 INITIAL 10M
3 NEXT 10M
4 PCTINCREASE 0
5 );
Index altered.
# 2. 使用并行创建索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id)
2 GLOBAL
3 PARALLEL 4;
Index created.
# 3. 使用NOLOGGING选项创建索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id)
2 GLOBAL
3 NOLOGGING;
Index created.
# 4. 监控索引性能
SQL> SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR
——————– —— ———– ————- —————–
IDX_ORDERS_CUSTOMER_ID 2 100 500 800
IDX_ORDERS_AMOUNT 2 200 1000 1500
IDX_ORDERS_ORDER_ID 1 50 1000 100
# 5. 分析索引使用情况
SQL> SELECT sql_id, sql_text, executions, buffer_gets, elapsed_time
2 FROM v$sql
3 WHERE sql_text LIKE ‘%orders%’
4 AND sql_text LIKE ‘%customer_id%’;
SQL_ID SQL_TEXT EXECUTIONS BUFFER_GETS ELAPSED_TIME
————- —————————————————- ———- ———– ————
a1b2c3d4e5f6 SELECT * FROM fgedu.orders WHERE customer_id = 123 10 50 100000
f6e5d4c3b2a1 SELECT * FROM fgedu.orders WHERE customer_id BETWEEN 100 AND 200 5 100 200000
3.2 YashanDB全局索引监控
3.2.1 YashanDB全局索引监控命令
# 1. 查看索引基本信息
SQL> SELECT index_name, table_name, uniqueness, status, partitioning_type
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
# 2. 查看索引分区信息
SQL> SELECT index_name, partition_name, status, high_value
2 FROM user_ind_partitions
3 WHERE index_name = ‘IDX_ORDERS_AMOUNT’;
# 3. 查看索引统计信息
SQL> SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
# 4. 查看索引使用情况
SQL> SELECT * FROM v$object_usage
2 WHERE index_name = ‘IDX_ORDERS_CUSTOMER_ID’;
# 5. 监控索引维护操作
SQL> SELECT sid, serial#, opname, sofar, totalwork, elapsed_seconds
2 FROM v$session_longops
3 WHERE opname LIKE ‘%Index%’;
# 6. 查看索引空间使用情况
SQL> SELECT segment_name, segment_type, tablespace_name, bytes/1024/1024 AS size_mb
2 FROM user_segments
3 WHERE segment_name LIKE ‘IDX_ORDERS%’;
# 7. 查看索引碎片情况
SQL> SELECT index_name, leaf_blocks, empty_blocks, num_rows, distinct_keys
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
3.2.2 YashanDB全局索引监控脚本
# global_index_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 全局索引监控脚本
# 输出文件
output_file=”/tmp/global_index_monitor_$(date +%Y%m%d_%H%M%S).log”
echo “开始监控全局索引” > ${output_file}
echo “监控时间: $(date)” >> ${output_file}
echo “” >> ${output_file}
# 查看索引基本信息
echo “===== 索引基本信息 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT index_name, table_name, uniqueness, status, partitioning_type
FROM user_indexes
WHERE table_name = ‘ORDERS’;
EXIT;
EOF
echo “” >> ${output_file}
# 查看索引分区信息
echo “===== 索引分区信息 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT index_name, partition_name, status, high_value
FROM user_ind_partitions
WHERE index_name IN (SELECT index_name FROM user_indexes WHERE table_name = ‘ORDERS’);
EXIT;
EOF
echo “” >> ${output_file}
# 查看索引统计信息
echo “===== 索引统计信息 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor
FROM user_indexes
WHERE table_name = ‘ORDERS’;
EXIT;
EOF
echo “” >> ${output_file}
# 查看索引空间使用情况
echo “===== 索引空间使用情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT segment_name, segment_type, tablespace_name, bytes/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name LIKE ‘IDX_ORDERS%’;
EXIT;
EOF
echo “” >> ${output_file}
# 查看索引碎片情况
echo “===== 索引碎片情况 ====” >> ${output_file}
sqlplus -s / as sysdba << EOF >> ${output_file}
SELECT index_name, leaf_blocks, empty_blocks, num_rows, distinct_keys
FROM user_indexes
WHERE table_name = ‘ORDERS’;
EXIT;
EOF
echo “” >> ${output_file}
echo “全局索引监控完成，结果保存至：${output_file}” >> ${output_file}
echo “全局索引监控完成，结果保存至：${output_file}”
# 检查索引状态
invalid_index_count=$(sqlplus -s / as sysdba << EOF
SET HEAD OFF
SET FEEDBACK OFF
SELECT COUNT(*) FROM user_indexes WHERE table_name = 'ORDERS' AND status != 'VALID';
EXIT;
EOF
)
if [ $invalid_index_count -gt 0 ]; then
echo "发现无效索引！数量: $invalid_index_count"
# 发送告警
echo "索引告警：发现 $invalid_index_count 个无效索引" | mail -s "YashanDB全局索引告警" admin@fgedu.net.cn
fi
3.3 YashanDB全局索引故障排查
3.3.1 YashanDB全局索引故障排查步骤
# 1. 发现问题
– 通过监控系统发现索引问题
– 通过告警信息发现索引问题
– 通过用户反馈发现索引问题
# 2. 分析问题
– 查看索引状态
– 查看索引统计信息
– 查看索引使用情况
– 查看索引维护操作
– 查看系统日志
# 3. 确定原因
– 索引失效
– 索引碎片过多
– 索引统计信息过期
– 索引空间不足
– 系统资源不足
# 4. 采取处理措施
– 重建索引
– 收集统计信息
– 扩展表空间
– 调整索引参数
– 优化查询语句
# 5. 验证处理结果
– 检查索引状态是否恢复
– 检查查询性能是否改善
– 检查系统资源使用是否正常
# 6. 预防措施
– 定期维护索引
– 定期收集统计信息
– 监控索引使用情况
– 合理设置索引参数
– 优化查询语句
3.3.2 YashanDB全局索引故障排查工具
# 1. 数据库视图
– user_indexes：查看索引基本信息
– user_ind_partitions：查看索引分区信息
– user_ind_columns：查看索引列信息
– user_segments：查看索引空间使用情况
– v$object_usage：查看索引使用情况
– v$session_longops：查看索引维护操作
– dba_indexes：查看所有索引信息
# 2. 诊断工具
– EXPLAIN PLAN：查看执行计划
– DBMS_XPLAN：显示执行计划
– DBMS_STATS：收集统计信息
– DBMS_INDEX：索引管理
– Enterprise Manager：企业管理器
# 3. 操作系统工具
– top：查看系统资源使用情况
– iostat：查看I/O使用情况
– vmstat：查看虚拟内存使用情况
– netstat：查看网络使用情况
# 4. 第三方工具
– Toad：数据库管理工具
– SQL Developer：SQL开发工具
– Navicat：数据库管理工具
– Prometheus + Grafana：监控和可视化工具
Part04-生产案例与实战讲解
4.1 YashanDB全局索引优化案例一
案例背景：某企业数据库系统中的分区表查询性能不佳，需要通过全局索引优化来提高查询性能。
# 1. 环境信息
– 数据库版本：YashanDB 19c
– 操作系统：Oracle Linux 9.3
– 硬件配置：8核CPU，32GB内存，1TB SSD
– 业务类型：OLTP
# 2. 问题描述
– 分区表fgedu.orders的跨分区查询性能不佳
– 用户反映查询响应时间长
– 监控系统显示索引使用效率低
# 3. 故障排查
# 查看表结构
SQL> DESC fgedu.orders;
Name Null? Type
————– ——– ————-
ORDER_ID NOT NULL NUMBER
ORDER_DATE NOT NULL DATE
CUSTOMER_ID NOT NULL NUMBER
AMOUNT NOT NULL NUMBER
# 查看分区情况
SQL> SELECT partition_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = ‘ORDERS’;
PARTITION_NAME HIGH_VALUE
————– ——————–
P202501 TO_DATE(‘ 2025-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202502 TO_DATE(‘ 2025-03-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202503 TO_DATE(‘ 2025-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202504 TO_DATE(‘ 2025-05-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202505 TO_DATE(‘ 2025-06-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202506 TO_DATE(‘ 2025-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
# 查看现有索引
SQL> SELECT index_name, table_name, uniqueness, partitioning_type
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
INDEX_NAME TABLE_NAME UNIQUENESS PARTITIONING_TYPE
——————– ————– ———- —————–
IDX_ORDERS_ORDER_ID ORDERS UNIQUE LOCAL
# 查看查询执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM fgedu.orders
3 WHERE customer_id = 123
4 AND order_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————-
Plan hash value: 1234567890
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 100 | 1000 (5)| 00:00:01 |
| 1 | PARTITION RANGE ALL | | 10 | 100 | 1000 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ORDERS | 10 | 100 | 1000 (5)| 00:00:01 |
——————————————————————————-
# 4. 处理措施
# 创建全局索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id) GLOBAL;
Index created.
# 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘FGEDU’, ‘ORDERS’);
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(‘FGEDU’, ‘IDX_ORDERS_CUSTOMER_ID’);
# 查看优化后的执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM fgedu.orders
3 WHERE customer_id = 123
4 AND order_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————-
Plan hash value: 9876543210
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 10 | 100 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ORDERS | 10 | 100 | 10 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN| IDX_ORDERS_CUSTOMER_ID| 10 | | 5 (0)| 00:00:01 |
——————————————————————————-
# 5. 验证结果
# 测试查询性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.orders
2 WHERE customer_id = 123
3 AND order_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
10 rows selected.
Elapsed: 00:00:00.01
# 对比优化前后的性能
# 优化前：Elapsed: 00:00:01.23
# 优化后：Elapsed: 00:00:00.01
# 6. 预防措施
# 定期维护索引
– 定期重建索引，消除索引碎片
– 定期收集统计信息，确保优化器能够生成正确的执行计划
– 监控索引使用情况，及时调整索引策略
# 优化查询语句
– 避免全表扫描，尽量使用索引
– 合理使用索引提示，指导优化器使用正确的索引
– 优化查询条件，减少索引扫描范围
# 系统配置优化
– 增加共享池大小，提高索引缓存效率
– 优化I/O配置，提高索引访问速度
– 合理设置PGA大小，提高排序性能
4.2 YashanDB全局索引优化案例二
案例背景：某电商系统中的分区表需要在整个表范围内强制执行唯一约束，需要通过全局索引来实现。
# 1. 环境信息
– 数据库版本：YashanDB 19c
– 操作系统：Oracle Linux 9.3
– 硬件配置：16核CPU，64GB内存，2TB SSD
– 业务类型：OLTP，电商系统
# 2. 问题描述
– 分区表fgedu.users需要在整个表范围内强制执行唯一约束
– 本地索引无法满足唯一约束的需求
– 用户反映插入数据时出现重复键错误
# 3. 故障排查
# 查看表结构
SQL> DESC fgedu.users;
Name Null? Type
————– ——– ————-
USER_ID NOT NULL NUMBER
USERNAME NOT NULL VARCHAR2(50)
EMAIL NOT NULL VARCHAR2(100)
REG_DATE NOT NULL DATE
# 查看分区情况
SQL> SELECT partition_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = ‘USERS’;
PARTITION_NAME HIGH_VALUE
————– ——————–
P2025Q1 TO_DATE(‘ 2025-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P2025Q2 TO_DATE(‘ 2025-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P2025Q3 TO_DATE(‘ 2025-10-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P2025Q4 TO_DATE(‘ 2026-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
# 查看现有索引
SQL> SELECT index_name, table_name, uniqueness, partitioning_type
2 FROM user_indexes
3 WHERE table_name = ‘USERS’;
INDEX_NAME TABLE_NAME UNIQUENESS PARTITIONING_TYPE
——————– ————– ———- —————–
IDX_USERS_USER_ID USERS UNIQUE LOCAL
# 尝试在本地索引上创建唯一约束
SQL> ALTER TABLE fgedu.users ADD CONSTRAINT uk_users_username UNIQUE(username) USING INDEX LOCAL;
ERROR at line 1:
ORA-14039: partitioned index must be local unless partitioned by the same partitioning key
# 4. 处理措施
# 创建全局唯一索引
SQL> CREATE UNIQUE INDEX idx_users_username ON fgedu.users(username) GLOBAL;
Index created.
# 添加唯一约束
SQL> ALTER TABLE fgedu.users ADD CONSTRAINT uk_users_username UNIQUE(username) USING INDEX idx_users_username;
Table altered.
# 测试唯一约束
SQL> INSERT INTO fgedu.users (user_id, username, email, reg_date) VALUES (1, ‘user1’, ‘user1@fgedu.net.cn’, SYSDATE);
1 row created.
SQL> INSERT INTO fgedu.users (user_id, username, email, reg_date) VALUES (2, ‘user1’, ‘user1@fgedu.net.cn’, SYSDATE);
ERROR at line 1:
ORA-00001: unique constraint (FGEDU.UK_USERS_USERNAME) violated
# 5. 验证结果
# 检查索引状态
SQL> SELECT index_name, table_name, uniqueness, status
2 FROM user_indexes
3 WHERE table_name = ‘USERS’;
INDEX_NAME TABLE_NAME UNIQUENESS STATUS
——————– ————– ———- ——–
IDX_USERS_USER_ID USERS UNIQUE VALID
IDX_USERS_USERNAME USERS UNIQUE VALID
# 检查约束状态
SQL> SELECT constraint_name, constraint_type, status
2 FROM user_constraints
3 WHERE table_name = ‘USERS’;
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
——————– ————— ——–
SYS_C0012345 PRIMARY KEY VALID
UK_USERS_USERNAME UNIQUE VALID
# 测试跨分区查询
SQL> SELECT * FROM fgedu.users WHERE username = ‘user1’;
USER_ID USERNAME EMAIL REG_DATE
———- ——– ——————- ———-
1 user1 user1@fgedu.net.cn 2026-04-01
Elapsed: 00:00:00.01
# 6. 预防措施
# 定期维护全局索引
– 定期重建全局索引，消除索引碎片
– 定期收集统计信息，确保优化器能够生成正确的执行计划
– 监控全局索引的使用情况，及时调整索引策略
# 优化插入操作
– 批量插入数据时，使用FORALL语句提高性能
– 避免在插入操作中使用复杂的触发器
– 合理设置回滚段大小，确保插入操作的可靠性
# 系统配置优化
– 增加共享池大小，提高索引缓存效率
– 优化I/O配置，提高索引访问速度
– 合理设置PGA大小，提高排序性能
4.3 YashanDB全局索引优化案例三
案例背景：某金融系统中的分区表需要加速范围查询，需要通过全局索引来优化查询性能。
# 1. 环境信息
– 数据库版本：YashanDB 19c
– 操作系统：Oracle Linux 9.3
– 硬件配置：12核CPU，48GB内存，1.5TB SSD
– 业务类型：OLTP，金融系统
# 2. 问题描述
– 分区表fgedu.transactions的范围查询性能不佳
– 用户反映查询响应时间长
– 监控系统显示索引使用效率低
# 3. 故障排查
# 查看表结构
SQL> DESC fgedu.transactions;
Name Null? Type
————– ——– ————-
TRANSACTION_ID NOT NULL NUMBER
TRANS_DATE NOT NULL DATE
AMOUNT NOT NULL NUMBER
CUSTOMER_ID NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(20)
# 查看分区情况
SQL> SELECT partition_name, high_value
2 FROM user_tab_partitions
3 WHERE table_name = ‘TRANSACTIONS’;
PARTITION_NAME HIGH_VALUE
————– ——————–
P202501 TO_DATE(‘ 2025-02-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202502 TO_DATE(‘ 2025-03-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202503 TO_DATE(‘ 2025-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202504 TO_DATE(‘ 2025-05-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202505 TO_DATE(‘ 2025-06-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
P202506 TO_DATE(‘ 2025-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)
# 查看现有索引
SQL> SELECT index_name, table_name, uniqueness, partitioning_type
2 FROM user_indexes
3 WHERE table_name = ‘TRANSACTIONS’;
INDEX_NAME TABLE_NAME UNIQUENESS PARTITIONING_TYPE
——————– ————– ———- —————–
IDX_TRANSACTION_ID TRANSACTIONS UNIQUE LOCAL
# 查看查询执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM fgedu.transactions
3 WHERE amount BETWEEN 1000 AND 5000
4 AND trans_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————-
Plan hash value: 1234567890
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1000 | 10000 | 5000 (5)| 00:00:01 |
| 1 | PARTITION RANGE ALL | | 1000 | 10000 | 5000 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TRANSACTIONS | 1000 | 10000 | 5000 (5)| 00:00:01 |
——————————————————————————-
# 4. 处理措施
# 创建分区全局索引
SQL> CREATE INDEX idx_transactions_amount ON fgedu.transactions(amount)
2 GLOBAL PARTITION BY RANGE (amount) (
3 PARTITION p_amount_0_1000 VALUES LESS THAN (1000),
4 PARTITION p_amount_1000_5000 VALUES LESS THAN (5000),
5 PARTITION p_amount_5000_10000 VALUES LESS THAN (10000),
6 PARTITION p_amount_10000_MAX VALUES LESS THAN (MAXVALUE)
7 );
Index created.
# 收集统计信息
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(‘FGEDU’, ‘TRANSACTIONS’);
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS(‘FGEDU’, ‘IDX_TRANSACTIONS_AMOUNT’);
# 查看优化后的执行计划
SQL> EXPLAIN PLAN FOR
2 SELECT * FROM fgedu.transactions
3 WHERE amount BETWEEN 1000 AND 5000
4 AND trans_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
—————————————-
Plan hash value: 9876543210
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 1000 | 10000 | 50 (0)| 00:00:01 |
| 1 | PARTITION RANGE ITERATOR | | 1000 | 10000 | 50 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TRANSACTIONS | 1000 | 10000 | 50 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | IDX_TRANSACTIONS_AMOUNT| 1000 | | 25 (0)| 00:00:01 |
——————————————————————————-
# 5. 验证结果
# 测试查询性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.transactions
3 WHERE amount BETWEEN 1000 AND 5000
4 AND trans_date BETWEEN TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-06-30’, ‘YYYY-MM-DD’);
1000 rows selected.
Elapsed: 00:00:00.05
# 对比优化前后的性能
# 优化前：Elapsed: 00:00:05.23
# 优化后：Elapsed: 00:00:00.05
# 6. 预防措施
# 定期维护全局索引
– 定期重建全局索引，消除索引碎片
– 定期收集统计信息，确保优化器能够生成正确的执行计划
– 监控全局索引的使用情况，及时调整索引策略
# 优化查询语句
– 避免全表扫描，尽量使用索引
– 合理使用索引提示，指导优化器使用正确的索引
– 优化查询条件，减少索引扫描范围
# 系统配置优化
– 增加共享池大小，提高索引缓存效率
– 优化I/O配置，提高索引访问速度
– 合理设置PGA大小，提高排序性能
Part05-风哥经验总结与分享
5.1 YashanDB全局索引优化经验总结
YashanDB全局索引优化经验总结：
- 合理选择索引类型：根据查询模式和业务需求，合理选择全局索引或本地索引
- 优化索引键选择：选择高选择性的列作为索引键，提高索引效率
- 合理设置索引分区：根据数据分布情况，合理设置索引分区策略
- 定期维护索引：定期重建索引，消除索引碎片，提高索引性能
- 收集统计信息：定期收集索引的统计信息，确保优化器能够生成正确的执行计划
- 监控索引使用情况：监控索引的使用情况，及时调整索引策略
- 优化查询语句：优化查询语句，充分利用索引，提高查询性能
- 合理配置系统：合理配置系统资源，提高索引访问速度
5.2 YashanDB全局索引优化检查清单
– [ ] 索引类型选择是否合理
– [ ] 索引键选择是否高选择性
– [ ] 索引分区策略是否合理
– [ ] 索引维护计划是否制定
– [ ] 统计信息收集是否定期执行
– [ ] 索引使用情况是否监控
– [ ] 查询语句是否优化
– [ ] 系统配置是否合理
– [ ] 索引空间使用是否监控
– [ ] 索引性能是否定期评估
# 全局索引维护检查清单
– [ ] 索引是否定期重建
– [ ] 统计信息是否定期收集
– [ ] 索引碎片是否定期清理
– [ ] 索引状态是否正常
– [ ] 索引空间是否充足
– [ ] 索引使用效率是否评估
– [ ] 索引策略是否定期调整
– [ ] 索引相关的问题是否及时处理
5.3 YashanDB全局索引优化工具推荐
YashanDB全局索引优化常用工具：
- 数据库工具：SQL*Plus、SQL Developer、PL/SQL Developer
- 诊断工具：EXPLAIN PLAN、DBMS_XPLAN、DBMS_STATS、DBMS_INDEX
- 监控工具：Enterprise Manager、Prometheus + Grafana、Zabbix
- 性能分析工具：AWR、ASH、ADDM、SQL Tuning Advisor
- 脚本工具：Shell脚本、Python脚本、Perl脚本
- 第三方工具：Toad、Navicat、Oracle SQL Developer
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
