yashanb教程FG197-YashanDB本地索引使用
本文档风哥主要介绍YashanDB本地索引使用相关知识，包括YashanDB本地索引的概念、架构、优势、规划、设计、实现、监控、故障排查、生产案例与实战讲解等内容，风哥教程参考YashanDB官方文档性能调优内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 YashanDB本地索引的概念
YashanDB本地索引是指在分区表上创建的索引，索引的分区方式与表的分区方式相同，每个索引分区对应表的一个分区。本地索引的索引条目只包含对应表分区的数据，而不是整个表的数据。更多视频教程www.fgedu.net.cn
- 本地索引是在分区表上创建的索引，索引的分区方式与表的分区方式相同
- 每个索引分区对应表的一个分区，只包含对应表分区的数据
- 本地索引的分区键与表的分区键相同
- 本地索引可以加速单个分区内的查询
1.2 YashanDB本地索引的架构
YashanDB本地索引的架构包括：
- 索引结构：本地索引使用B-tree结构，与普通索引类似
- 索引分区：本地索引的分区方式与表的分区方式相同，每个索引分区对应表的一个分区
- 索引条目：每个索引分区只包含对应表分区的数据的索引条目
- 维护机制：当表中的数据发生变化时，只有对应分区的索引需要更新
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) LOCAL;
Index created.
# 3. 创建本地唯一索引
SQL> CREATE UNIQUE INDEX idx_orders_order_id ON fgedu.orders(order_id) LOCAL;
Index created.
# 4. 创建本地位图索引
SQL> CREATE BITMAP INDEX idx_orders_status ON fgedu.orders(status) LOCAL;
Index created.
3.1.2 维护本地索引
# 1. 重建本地索引
SQL> ALTER INDEX idx_orders_customer_id REBUILD;
Index altered.
# 2. 重建本地索引的特定分区
SQL> ALTER INDEX idx_orders_customer_id REBUILD PARTITION p202501;
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, partitioning_type
2 FROM user_indexes
3 WHERE table_name = ‘ORDERS’;
INDEX_NAME TABLE_NAME UNIQUENESS STATUS PARTITIONING_TYPE
——————– ————– ———- ——– —————–
IDX_ORDERS_CUSTOMER_ID ORDERS NONUNIQUE VALID LOCAL
IDX_ORDERS_ORDER_ID ORDERS UNIQUE VALID LOCAL
IDX_ORDERS_STATUS ORDERS NONUNIQUE VALID LOCAL
# 5. 查看索引的分区情况
SQL> SELECT index_name, partition_name, status
2 FROM user_ind_partitions
3 WHERE index_name = ‘IDX_ORDERS_CUSTOMER_ID’;
INDEX_NAME PARTITION_NAME STATUS
——————– ——————– ——–
IDX_ORDERS_CUSTOMER_ID P202501 USABLE
IDX_ORDERS_CUSTOMER_ID P202502 USABLE
IDX_ORDERS_CUSTOMER_ID P202503 USABLE
IDX_ORDERS_CUSTOMER_ID P202504 USABLE
IDX_ORDERS_CUSTOMER_ID P202505 USABLE
IDX_ORDERS_CUSTOMER_ID P202506 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 LOCAL
3 PARALLEL 4;
Index created.
# 3. 使用NOLOGGING选项创建索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id)
2 LOCAL
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_ORDER_ID 1 50 1000 100
IDX_ORDERS_STATUS 1 5 5 50
# 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_CUSTOMER_ID’;
# 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本地索引监控脚本
# local_index_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 本地索引监控脚本
# 输出文件
output_file=”/tmp/local_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 GLOBAL
# 查看查询执行计划
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-01-31’, ‘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 | 500 (5)| 00:00:01 |
| 1 | PARTITION RANGE SINGLE| | 10 | 100 | 500 (5)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ORDERS | 10 | 100 | 500 (5)| 00:00:01 |
——————————————————————————-
# 4. 处理措施
# 创建本地索引
SQL> CREATE INDEX idx_orders_customer_id ON fgedu.orders(customer_id) LOCAL;
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-01-31’, ‘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 | 5 (0)| 00:00:01 |
| 1 | PARTITION RANGE SINGLE | | 10 | 100 | 5 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ORDERS | 10 | 100 | 5 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | IDX_ORDERS_CUSTOMER_ID| 10 | | 2 (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-01-31’, ‘YYYY-MM-DD’);
10 rows selected.
Elapsed: 00:00:00.01
# 对比优化前后的性能
# 优化前：Elapsed: 00:00:00.50
# 优化后：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 GLOBAL
IDX_USERS_USERNAME USERS UNIQUE GLOBAL
# 测试分区维护操作
SQL> ALTER TABLE fgedu.users DROP PARTITION P2025Q1;
Table altered.
# 查看索引状态
SQL> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = ‘USERS’;
INDEX_NAME STATUS
——————– ——–
IDX_USERS_USER_ID UNUSABLE
IDX_USERS_USERNAME UNUSABLE
# 重建全局索引
SQL> ALTER INDEX idx_users_user_id REBUILD;
SQL> ALTER INDEX idx_users_username REBUILD;
# 4. 处理措施
# 删除全局索引
SQL> DROP INDEX idx_users_user_id;
SQL> DROP INDEX idx_users_username;
# 创建本地索引
SQL> CREATE UNIQUE INDEX idx_users_user_id ON fgedu.users(user_id) LOCAL;
SQL> CREATE UNIQUE INDEX idx_users_username ON fgedu.users(username) LOCAL;
# 测试分区维护操作
SQL> ALTER TABLE fgedu.users DROP PARTITION P2025Q2;
Table altered.
# 查看索引状态
SQL> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = ‘USERS’;
INDEX_NAME STATUS
——————– ——–
IDX_USERS_USER_ID VALID
IDX_USERS_USERNAME VALID
# 5. 验证结果
# 测试查询性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.users WHERE user_id = 123;
USER_ID USERNAME EMAIL REG_DATE
———- ——– ——————- ———-
123 user123 user123@fgedu.net.cn 2025-07-01
Elapsed: 00:00:00.01
# 测试分区维护操作时间
# 优化前：删除分区需要10分钟，重建索引需要15分钟，总时间25分钟
# 优化后：删除分区需要2分钟，无需重建索引，总时间2分钟
# 6. 预防措施
# 定期维护本地索引
– 定期重建本地索引，消除索引碎片
– 定期收集统计信息，确保优化器能够生成正确的执行计划
– 监控本地索引的使用情况，及时调整索引策略
# 优化分区维护操作
– 合理规划分区策略，减少分区维护操作的频率
– 使用在线分区维护操作，减少对系统的影响
– 合理安排分区维护操作的时间，避开业务高峰期
# 系统配置优化
– 增加共享池大小，提高索引缓存效率
– 优化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 GLOBAL
IDX_TRANSACTIONS_CUSTOMER_ID TRANSACTIONS NONUNIQUE GLOBAL
# 测试分区维护操作
SQL> ALTER TABLE fgedu.transactions DROP PARTITION P202501;
Table altered.
# 查看索引状态
SQL> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = ‘TRANSACTIONS’;
INDEX_NAME STATUS
——————– ——–
IDX_TRANSACTION_ID UNUSABLE
IDX_TRANSACTIONS_CUSTOMER_ID UNUSABLE
# 测试查询其他分区
SQL> SELECT * FROM fgedu.transactions WHERE trans_date BETWEEN TO_DATE(‘2025-02-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-02-28’, ‘YYYY-MM-DD’);
ERROR at line 1:
ORA-01502: index ‘FGEDU.IDX_TRANSACTIONS_CUSTOMER_ID’ or partition of such index is in unusable state
# 4. 处理措施
# 删除全局索引
SQL> DROP INDEX idx_transaction_id;
SQL> DROP INDEX idx_transactions_customer_id;
# 创建本地索引
SQL> CREATE UNIQUE INDEX idx_transaction_id ON fgedu.transactions(transaction_id) LOCAL;
SQL> CREATE INDEX idx_transactions_customer_id ON fgedu.transactions(customer_id) LOCAL;
# 测试分区维护操作
SQL> ALTER TABLE fgedu.transactions DROP PARTITION P202502;
Table altered.
# 查看索引状态
SQL> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = ‘TRANSACTIONS’;
INDEX_NAME STATUS
——————– ——–
IDX_TRANSACTION_ID VALID
IDX_TRANSACTIONS_CUSTOMER_ID VALID
# 测试查询其他分区
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.transactions WHERE trans_date BETWEEN TO_DATE(‘2025-03-01’, ‘YYYY-MM-DD’) AND TO_DATE(‘2025-03-31’, ‘YYYY-MM-DD’);
1000 rows selected.
Elapsed: 00:00:00.05
# 5. 验证结果
# 测试查询性能
SQL> SELECT * FROM fgedu.transactions WHERE customer_id = 123;
100 rows selected.
Elapsed: 00:00:00.01
# 测试分区维护操作期间的可用性
# 优化前：删除分区后索引变为不可用，其他分区的查询也会失败
# 优化后：删除分区后索引仍然可用，其他分区的查询正常执行
# 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，节假日休息
