Oracle 数据库表性能优化

找了一圈尾巴

已于 2023-04-15 17:22:24 修改

阅读量1.8k

点赞数

分类专栏：数据库优化文章标签：数据库 oracle 性能优化

于 2023-04-15 17:17:28 首次发布

本文链接：https://blog.csdn.net/weixin_41645817/article/details/130170918

版权

数据库优化专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Oracle 数据库表性能优化

最近在一次工作过程中，遇到了oralce 表性能慢的问题。一个历史表，一个月将近1000多万的数据量，想查询这个表的数据，只使用了一个简单的语句，却一个多小时都查不出来。于是决定对Oracle 的这张表的性能进行一下优化。本人不是一个专门搞数据库这块的dba，所以只能粗浅的以自己了解到的知识，以及网上搜的一些资料，来对其进行优化尝试。

下面，我将从以下几点出发，逐一对这个表进行问题排查和优化，并分享一些优化思路：

数据库索引
表分区
统计信息
表收缩
并行查询
表重建

一. 数据库索引

想必一提到表性能优化，数据库索引必然是我们程序员所关注的重点。索引的建立是跟业务息息相关的，我们需要了解到这个表现有的程序怎么用，以及以后的程序要怎么用，从而规划出适合这个表的最合适的索引。而根据阿里java规范，一个表的索引最好不要超过6个，因为建立索引后，表的插入，修改，删除性能都会大大受到影响。因为每次对表进行 DML 操作时，都需要同时对索引进行维护。而如果索引的数量太多或者太大，就会对表操作的性能产生负面影响。
建立索引后，如何正确的使用索引也是需要特别注意的，我们需要尽量去保证SQL语句的简洁性，书写完SQL语句后，需要在执行计划中对语句进行分析测试，去确保这个语句是可以正确使用到我们所建立的索引的。下面我将列出一些关于索引的正确使用，和不正确使用的例子。

索引生效场景:
1. INDEX_COLUMN = ?
2. INDEX_COLUMN > ?
3. INDEX_COLUMN >= ?
4. INDEX_COLUMN < ?
5. INDEX_COLUMN <= ?
6. INDEX_COLUMN between ? and ?
7. INDEX_COLUMN in (?,?)
8. INDEX_COLUMN like ?||‘%’
9. T1.INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）
索引失效场景:
1. INDEX_COLUMN <> ?
2. INDEX_COLUMN not in (?,?)
3. funcation （INDEX_COLUMN) = ? （函数运算后的字段）
4. INDEX_COLUMN + 1 = ?
5. INDEX_COLUMN || ‘a’ = ?
6. INDEX_COLUMN like ‘%’||?
7. INDEX_COLUMN like ‘%’ || ? || ‘%’ （含前导模糊查询的like 语法不能使用索引）
8. INDEX_COLUMN is null （B-TREE 索引里不保存字段为 NULL 值记录，因此 is null 不能使用索引）
9. NUMBER_INDEX_COLUMN = ‘12345’ ； CHAR_INDEX_COLUMN = 12345 （Oracle 在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段进行隐式转换，相当于加了一层函数处理）
10. a.INDEX_COLUMN = a.COLUMN_1 （给索引查询的值应是已知字段，不能是未知字段）

在这里我想举一个我之前未正确使用索引的例子: 在面对 CREATE_TIME 这样的时间字段的时候，ORACLE 中我总是喜欢使用 to_char(CREATE_TIME,‘yyyyMMdd’) 去格式化这个字段，然后查询指定日期。而这样的查询方式，其实会导致CREATE_TIME 这个字段被 to_char() 函数包着，那这个语句也是无法使用索引的，从而导致查询变慢。而正确的时间字段查询方式应该是使用between and ，或者是使用 CREATE_TIME >= date ‘2023-04-15’ 这样的方式去查询时间段，从而保证CREATE_TIME 时间字段索引的有效性。
最后，再分享一下创建索引和重写索引的语句

建立索引语句
CREATE INDEX index_name ON table_name (column1, column2);
重新建立索引语句
alter index XXX rebuild

分享这两个简单语句的目的，其实还有个小故事。如今的数据库操作软件是有很多的，比如plsql，navicat，dataGrip等等，每款软件都可以以视图化的方式去创建表或者索引，以及执行更多的操作。但是，之前工作的时候，我就尝试使用plsql 的创建索引的方式，去给某个大表加索引，结果，plsql 就卡死了，重启后又执行了多次都没反应。。。后来，我在我马上认为这个大表可能已经建不了索引的时候，我就尝试了下使用索引语句去建立索引，结果就建成功了。所以自打那次后，我就认为最基本的语句操作要比视图化的页面操作靠谱多了，也更高效，更快速，这也就是为什么程序员更喜欢用doc 命令去操作电脑的原因哦！

表分区

oracle 的表分区有两个策略，分别是 RANGE 和 LIST

创建range分区表

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 
    VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 
    VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 
    VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 
    VALUES LESS THAN('2013-Jan-01')
);

添加range 分区
ALTER TABLE sales ADD PARTITION q1_2013 VALUES LESS THAN('01-APR-2013');
创建 LIST 分区表

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

添加 LIST 分区

ALTER TABLE sales ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

创建完分区表后，我们就可以针对分区进行一些操作：

删除分区 ALTER TABLE SALES DROP PARTITION P3;
删除子分区 ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
截断分区 ALTER TABLE SALES TRUNCATE PARTITION P2;
合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下命令实现了P1 P2分区的合并
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
接合分区
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下命令进行接合分区
ALTER TABLE SALES COALESCA PARTITION;
重命名表分区 ALTER TABLE SALES RENAME PARTITION P21 TO P2;

如何正确使用分区

使用 PARTITION 关键字 SELECT * FROM SALES PARTITION(europe)
查询条件带分区键 SELECT * FROM SALES where country = ''

一般情况下我都会使用查询条件带分区键的方式使用分区表，以减少要查的数据不在某个分区表中，而造成了数据丢失的情况。

表统计信息

对于大表，需要及时统计表的信息，让优化器更准确地选择查询执行计划，提高查询速度。具体而言，它会计算并更新该表中的各列的统计信息（如列的最小值、最大值、平均值等），以便优化查询操作。这些命令通常可以提高数据库的性能，因为当查询语句需要访问某些列时，数据库不必在查询时重新计算它们的统计信息，从而可以提高查询的速度。下面是关于处理表统计信息的语句：

表统计：ANALYZE TABLE table_name COMPUTE STATISTICS
索引统计： ANALYZE INDEX index_name COMPUTE STATISTICS;
表统计 + 索引统计 + 列统计：ANALYZE TABLE table_name COMPUTE STATISTICS for table for all indexes for all columns;
查询统计信息：select NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN from user_tables where table_name= ' table_name' ;
根据统计信息进行数据库空间水位分析

SELECT table_name,
       ROUND((blocks * 8/1024), 2) "高水位空间 M",
       ROUND((num_rows * avg_row_len / 1024/1024), 2) "真实使用空间 M",
       ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) M",
       ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -blocks * 8 * 10 / 100), 2) "浪费空间 M",
       ((blocks * 8-(num_rows * avg_row_len / 1024))/1024)/(blocks * 8/1024) "浪费空间 %"
  FROM user_tables
 WHERE table_name = 'table_name'

一般情况下，浪费空间大于百分之25%，则需要重新对表磁盘空间进行整理，我这里推荐一种方法，使用 Oracle 10g提供的一个功能：表收缩操作。

表收缩

从10g开始，oracle开始提供 Shrink 的命令，假如我们的表空间中支持自动段空间管理 (ASSM) ,就可以使用这个特性缩小段，即降低HWM。这里需要强调一点，10g的这个新特性，仅对ASSM表空间有效，否则会报 ORA-10635: Invalid segment or tablespace type。 的错误。

使用 shrink 命令必须开启行迁移功能，步骤如下：

alter table table_name_1 enable row movement ;
这个语句的作用是启用表的行迁移功能。当表启用了行迁移功能后，可以将表的行从一个表空间移动到另一个表空间，或者重新分配行的存储空间。这可以帮助优化表的存储和性能，同时也可以在需要时对表的结构进行调整。
alter table table_name_1 shrink space cascade;
这条SQL语句的作用是收缩表 table_name_1 的磁盘空间，并且同时级联收缩其所有的索引和分区。收缩磁盘空间的过程会把表中未使用的数据页或数据块空间释放掉，从而最大限度地减小表的物理存储空间，提高数据库的性能和效率。
alter table table_name_1 disable row movement ;
执行完毕后关闭表的行迁移功能。

shrink 命令

使用表收缩的shrink 命令分为两个阶段：

数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

所以如果我们想整理某个表的磁盘空间，但这个表却在某个业务时间段内经常使用，我们先执行
shrink space compact 这个命令，先执行shrink 命令的第一步：数据重组步骤，然后在业务不繁忙的时候，再去执行 shrink 命令。

并行查询

在某些情况下，我们可以尝试开启Oracle 的并行查询功能，以提高查询速率。但一定要经过执行计划的分析测试，以确保并行查询可以提高语句的查询速度，而不是适得其反。下面是几种使用并行查询的方式：

使用提示暗示hints式（临时有效）

SELECT /*+ PARALLEL(a 4),(b 4)*/ a.msisdn_id,b.copyright_id,FROM musicdw.user_list partition(p1) a JOIN musicdw.song_list b ON 1 = 1;

多表关联时的多表并行场景
select /*+parallel(table_name1,num1) parallel(table_name2,num2)*/ count(*) from table_name1, table_name2;
并行DDL式 (会话生命周期有效)

alter session enable parallel dml;
alter session enable parallel query;
或者
ALTER SESSION FORCE PARALLEL DML PARALLEL 5;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 5;

修改对象式 (长期有效)

alter table table_name parallel 4; --强制为4个并发也可以更多。
alter table table_name parallel; --让系统自己动态的调整。

表重建

系统修复最简单直接有效的方式就是重启，而表修复最直接有效的方式就是重新建表。在进行表性能优化时，如果尝试了很多方式都没有效果，不如就重新建表吧。重新建一张大表虽然耗费的时间可能很长，也可能这种行为会被人所病垢不明就里，却不失为一个最简单解决问题的方式哈。

今天的分享就到这里，希望能对看完的你有所帮助。后续我会做一个ChatGPT 的项目，使用ChatGPT帮助我更好的整理计划和文档，祝自己好运。

参考文献

https://www.php.cn/oracle/489595.html oracle怎么给表增加分区
https://blog.csdn.net/weixin_36303305/article/details/116388233 oracle alter table after,收缩表alter table shrink space