oracle 大数据常见优化 &分页查询

参考某微信社区博主，此文为温故知新；原参考博主账号与链接已丢失

分页

1.limit

语句样式：select * from table limit m,n

适用场景：适用于数据量较少的情况（元组、百/千）

缺点：全表扫描，速度劣势，有的数据库结果集返回不稳定。limit限制是从结果集的M位置处取出N条输出，其余抛弃。

2.基于索引再排序

语句样式：select * from table where id_pk > (pageNum*10) order by id_pk ace limit M

适用场景：适用于数据量多的情况（上万），最好order by的列对象是主键或唯一，使得结果集是稳定的

3.基于索引使用prepare（第一个问号表示pageNum,第二个?表示每页元组数）

语句样式：PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk >(? *? ) ORDER BY id_pk ASC LIMIT M

适用场景：大数据量

原因：索引扫描，速度快，prepare更快

4.利用mysql支持order操作可以利用索引快速定位部分元组，避免全表扫描

eg:读取第1000到1019行元组

select * from table where pk>=1000 order by pk ASC limit 0,20

oracle 大数据常见优化

1.避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引

2.尽量避免在where子句对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描

eg：

num is null 更为 num = 0（设置默认值）

添加、修改默认值：alter table table_name modify column_name default 具体内容;

删除默认值：alter table table_name modify column_name default null;

替换is null 的方法

1.NVL select NVL(product_code,-1) from table.ms_goods --若product_code为空，则返回-1， NVL(eExpression1, eExpression2) 输出结果： 如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1的计算结果不是 null 值，则返回 eExpression1。

eExpression1 和 eExpression2可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回NULL.。

2.NULLIF（） 如果两个指定的表达式相等，则返回空值。 语法NULLIF ( expression1 , expression2 )

3.COALESCE() Oracle COALESCE函数语法为COALESCE(表达式1,表达式2,…,表达式n)，n>=2,此表达式的功能为返回第一个不为空的表达式，如果都为空则返回空值。（所有表达式必须为同一类型或者能转换成同一类型。）

4.DECODE() decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) 该函数的含义如下：

IF 条件=值1

THEN RETURN(返回值1)

ELSIF

条件=值2

THEN

RETURN(返回值2)

…

ELSIF

条件=值n

THEN

RETURN(返回值n)

ELSE RETURN(缺省值)

END IF

3.尽量避免在where 子句中使用！=或<>操作符，否则引擎放弃使用索引而进行全表扫描

4.尽量避免在where子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描 可用 union all（全连接）替换

eg:
select  *  from table where num = 10 or num =32
替换为
select  * from table where num=10
nuion all
select * from table where num=20

5.慎用in 和not in，否则会导致全表扫描;like也将导致全表查询，instir替代

对于连续的数值，使用between

6.如果在where子句使用参数，也会导致全表扫描

select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

7.应避免在where子句中对字段进行表达式操作，会全表扫描

select id from t where num/2=100
应改为:
select id from t where num=100*2

8.避免在where子句中对字段进行函数操作，会使引擎放弃使用索引而进行全表扫描

=to_date 也是函数

9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。（采用函数处理的字段不能利用索引）

10.在使用索引字段作为条件，如果索引为复合索引（组合索引），那么必须使用该索引中的第一个字段作为条件时才能保证系统使用该索引，否则索引将不会使用；索引顺序尽量与表中字段顺序一致；

索引不是越多越好，一个表的索引最好不要超过6个

窄索引：索引列为1—2列的索引

宽索引：索引超过2列的索引

11.用exists 代替in

12.尽可能避免更新（簇表）clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会消耗相当大的资源

13.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符

14.尽可能使用varchar/nvarchar 代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次较小字段内搜索效率更高

char的长度是固定的，varchar的长度是可以变化的() varchar:按字节存储数据

varchar(6)，最多能存储6个字节的数据，比如“哈哈哈” nvarchar:按字符存储数据

nvarchar(6),最多能存储6个字符/中文数据，如“哈哈哈哈哈哈”；nvarchar(m)最大存储的实际长度=n*m（n根据编码方式而定），如果nvarchar存储的是英文字符，也是根据编码方式存储n的字节长度。用nvarchar存储英文字符，会浪费一半以上的存储空间

15.尽量避免SELECT * FROM，用具体字段代替*；尽量使用表变量代替临时表；避免频繁创建和删除临时表，以减少系统表资源的消耗

16.在新建临时表时，如果一次性插入数据量很大，那么可以使用select into 代替create，避免造成大量log；如果数据量不大，为了缓和系统表的资源，应先create table,然后insert

17.尽量避免使用游标（cursor），因为游标的效率较差，如果游标操纵的数据超过1万行，那么就应该考虑改写

18.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC

SET NOCOUNT ON：阻止在结果集中返回显示受T-SQL语句影响的行计数信息。 SET ONCOUNT ON 不返回计数 SET

NOCOUNT OFF 返回计数 当SET NOCOUNT ON时候，将不向客户端发送存储过程每个语句的DONE_IN_proc消息，如果存储过程中包含一些并不返回实际数据的语句，网络通信流量便会大量减少，可以显著提高应用程序性能；

T-sql
T-SQL即 Transact-SQL,是标准SQL语言的扩展,是SQL Server的核心,在SQL的的基础上添加了变量,运算符,函数和流程控制等新内容,
T-SQL 语言的组成
数据定义语言（DDL Data Definition Language）:SQL让用户定义存储数据的结构和组织，以及数据项之间的关系
数据检索语言：SQL允许用户或应用程序从数据库中检索存储的数据并使用它
数据操纵语言（DML ，Data Manipulation Language）:SQL 允许用户或应用程序通过添加新数据、删除旧数据和修改以前存储的数据对数据库进行更新
数据控制语言（DCL，Data Control Language）：可以使用SQL来限制用户检索、添加和修改数据的能力，保护存储的数据不被未授权的用户所访问
    数据共享：可以使用SQL来协调多个并发用户共享数据，确保他们不会相互干扰
    数据完整性：SQL在数据库中定义完整性约束条件，使它不会由不一致的更新或系统失败而遭到破坏

19.尽量避免大事务操作，提高系统并发能力；尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理