九、Oracle SQL（优化专题&分表分区）

14. 优化专题

14.1 查询频繁，数据量大

14.1.1 索引

使用时机： 表中经常查询的字段可以考虑添加索引。

联合索引：若能确认多个条件会同时使用时，可以将这几个条件作为联合索引。
单列索引：若条件查询时，这几个条件不是同时用到的话，还是单列做索引比较好些。

例子：
　　如：id,name,income三列需要做索引
　　查询时同时使用：联合索引要比单列索引要快。
　　查询时只用到一部分：单列索引要比联合索引快，但是实际上联合索引还是要比没有索引快。

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。 且尽可能的让字段顺序与索引顺序相一致。
若某列中存在大量重复数据（如状态，支付类型等列），那么建立索引对效率没啥影响。
在定义 primary key 或 unique 约束后系统自动在相应的列上创建索引。

14.1.2 用select 具体列代替 select *

查询时哪怕很多列也不要使用select*这种写法，这样会全表扫描。
应该是用到哪列就在哪列上面加入索引，然后查询时，写清楚要select的列，这样可以调用到对应列的索引，效率会高很多。

14.2 查询条件注意事项

14.2.1 null值处理

使用时机： 当某列可能存在空值时，考虑使用默认值。

PS：在 where 子句中对字段进行 null 值判断，将导致引擎放弃使用索引而进行全表扫描！

例子：　select id from t where num is null

　　可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

14.2.2 OR、!=、<>、like 优化

使用时机： 当查询中的过滤条件存在这几种过滤方式时，会导致引擎放弃使用索引而进行全表扫描 ，可以考虑以下优化方案。

例子：
- OR优化：
  select id from t where num=10
  union all
  select id from t where num=20
- LIKE优化：
  尽量不要在where条件中使用两边都是%的like模糊查询，这样会导致全表扫描，实在不行在字段后面进行模糊匹配。如like 'li%'

14.2.3 IN 优化

使用时机：当查询中用到in的条件进行过滤时，可以考虑以下优化方案。

例子：

IN优化：
对于连续的区间数据：使用between
select * from t_order where id between 2 and 3
对于不连续连续的区间数据：使用exists
select num from a where exists(select 1 from b where num=a.num)

14.3 字段类型的选择

字符型优化

若某列只存在数值型的数据，则该列的类型应设置为数值类型，这会降低查询和连接的性能，并会增加存储开销。
尽可能的使用 varchar（可变长度）代替 char （固定长度），因为首先可变长度字段存储空间小，可以节省存储空间。
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

14.4 新增、修改优化

勤用commit

多使用commit来释放回滚点，对性能会有所提升，出错回滚时数据量也会相对少很多。

14.5 删除操作

删除操作有3种，先简单了解一下

14.5.1 drop table

1）属于DDL

2）不可回滚

3）不可带where

4）表的结构和内容都删除

5）删除速度快

使用时机： 不再需要一张表的时候，用drop

14.5.2 truncate table

1）属于DDL

2）不可回滚

3）不可带where

4）表内容删除

5）删除速度快

使用时机： 保留表而删除所有数据的时候用truncate

14.5.3 delete from

1）属于DML

2）可回滚

3）可带where

4）根据where对表内容删除

5）删除速度慢，需要逐行删除

使用时机： 想删除部分数据行时候，用delete，并且带上where子句

14.6 SQL书写

使用大写： Oracle服务器总是先将小写字母转成大写后，才执行，所以使用大写时能减少Oracle的一步操作。

15. 分表分区

15.1 前言

大数据量的查询，不仅查询速度非常慢，而且还会导致数据库经常宕机，在尝试添加索引及查询方式修改后，还有没有更有效的解决方案呢？

分库、分表、分区这些概念咱就应该了解一下。

15.2 分表

假如一个大型商城有一个订购关系表，每个用户的订单都落在这个表里面，那么时间一长，要进行查询的时候，肯定慢得要死，这样的系统给客户用，那就凉凉思密达了...

拆分思想

咱可以对这个总表进行拆分，例如对年进行拆分，每个年表只存一年的记录，那么这样数据就分散开来了，每个表的数据就少很多，根据具体情况还可以按月分，按日分等等...

PS：注意，分表使用时，查询语句里要进行动态拼装查询的表名。

15.3 分区

15.3.1 简介

年表创建过后，查询就是查询年表中的数据，可是虽然分表了，但是年表中的数据量仍然很大，查询速度虽然有提升，但并不能满足用户的要求，此时考虑在年表中再根据月份进行分区。

定义： 表分区后，逻辑上仍然是一张表，只不过将表中的数据在物理上存放到多个表空间上。

目的： 这样在查询数据时，会查询相应分区的数据，减少SQL操作的数据量，避免了全表扫描，从而提升查询效率。

15.3.2 水平分区

对行进行分区，举个例子来说，就是一个表中有1000万条数据，每100万条数据划一个分区，这样就将表中数据分到10个分区中去。

PS：水平分区要通过某个特定的属性列进行分区，如用时间先orderBy后再分区。

15.3.3 垂直分区

对列进行分区，减少表的宽度，从而提升查询效率。

比如一个学生表中，有他相关的信息列，还有论文列以CLOB存储，可以把这些不经常使用的CLOB划分到另一个分区，需要访问时再调用它。

代码样例

--水平分区

--按照订单时间进行水平分区
CREATE TABLE ORDER_ACTIVITIES
(
    ORDER_ID      NUMBER(7) NOT NULL,
    ORDER_DATE    DATE,
    TOTAL_AMOUNT NUMBER,
    CUSTOTMER_ID NUMBER(7),
    PAID   CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
  PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACE ORD_TS01,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
  PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
);

--按省份进行分区
CREATE  TABLE  ListTable
(
    id    INT  PRIMARY  KEY ,
    name  VARCHAR (20),
    area  VARCHAR (10)
)
PARTITION  BY  LIST (area)
(
    PARTITION  part1 VALUES ('guangdong','beijing') TABLESPACE  Part1_tb,
    PARTITION  part2 VALUES ('shanghai','nanjing')  TABLESPACE  Part2_tb
);


--垂直分区

PS：垂直分区思路是将写入操作比较频繁的数据表，把这个两个表分离出来，放在不同的服务器
PS：可以理解为外键的关联方式（不知道准不准确...）

--查询分区数据
select * from 表 partition(分区名) where 条件;

附：
Oracle亿级数据查询处理
 Oracle 分区表使用和查询
 垂直分区+水平分区

转载自：有梦想的肥宅

posted @ 2020-03-24 15:50 bret_chen 阅读(657) 评论(0) 编辑收藏举报

刷新页面返回顶部

Bret_Chen