oracle
一.什么是oracle分区,什么时候用,通常有哪几种类型
(1) 分区的实质是把一张大表的数据按照某种规则使用多张子表来存储。
然后这多张子表使用统一的表名对外提供服务,子表实际对用户不可见。类似于在多张子表上建立一个视图然后用户直接使用该视图来访问数据。
(2)Oracle分区在什么情况下使用
当一张表的数据到达上亿行的时候,表的性能会严重降低,这个时候就需要用到分区了,通过划分成多个小表,并在每个小表上建立本地索引可以大大缩小索引数据文件的大小从而更快的定位到目标数据来提升访问性能。
分区除了可以用来提升访问性能外还因为可以指定分区所用的表空间,因此也用来做数据的生命周期管理当前需要繁使用的活跃数据可以放到访问速度更快但价格也更贵的存储设备上,而2、3年前的历史数据,或者叫冷数据可以放到更廉价、速度更低的设备上。从而降低存储用。
(3)Oracl的分区可以分:列表分区、范围分区、散列分区(哈希分区)、复合分区。
列表分区
1)、列表分区明确指定了根据某字段的某个具体值进行分区,而不是像范围分区那样根据字段的值范围来分的。
范围分区
1)、就是根据数据库表中某一字的值的范围来划分分区。
2)、数据中有空值, Oracle机制会自动将其规划到 maxvalue的分区中。
散列分区区(哈希分区)
1)、根据字段的hash值进行均匀分布可能地实现各分区所散列的数据相等
2)、散列分区即为哈希分区, Oracle采用哈希码技术分区,具体分区如何由 Oracle说的算,也可能我下一次搜索就不是这个数据了
复合分区
根据范围分区后,每个分区内的数据再列地分布在几个表空间中,这样我们就要使用复合分区。复合分区是先使用范围分区,然后在每个分区同再使用散列分区的一种分区方法
二.千万级别的数据库,如何提升查询效率?
1.从数据库设计上
2.从SQL优化上
3.从物理硬件上
1、对查询进行优化,应尽量避兔全表扫描,首先应考虑在 where及 order by涉及的列上建立索引
2、应尽量避兔在 where子句中对字段进行null值判断否则将导致引放弃使用索引而进行全表扫捕如: select id from t where num is null可以在num上设置默认值0确保表中num列没有nul值,然后这样查询: select d from t where num=0
3、井不是所有索引对查询都有效SQL是根据表中数来进行查询优化的,当索引列有大量数据重复时,查询可能不会去利用索引如一表中有字段sex,male emale几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用
4、索引并不是越多越好,索引同然可以提高相应的 select的效率,但同时也降低了 insert及 update的效率,因为 insert或 update时有可能会重建索引所以怎样建索引需要重考虑,视具体情况而定一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。
5、应尽可能的避免更新索引数据烈因为索引数据列的顺序就是表记录的物理存储顺序一旦该列值改变将导致整个表记录的顺序的调整会耗费相当大的资源。
6、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低询和连接的性能,并会增加存储开销。这是因为引在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了
7、尽可能的使用 varchar/ nvarchar代char/ nchar,因为首先变长字段存储空间小,可以节存空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
8、尽量使用表变量来代临时表。如果表变量包含大量数据,请注意索引非常有限(只有主型
9、避免创建和删除临时表,以减少系统表资源的消耗
10、临时表并不是不可使用,适当地使用它们可以使某些例程更有效;例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。
11、在新建临时表时,如果一次性插入数据量很大那么可以使用 select into代 create table,通免造成大量log,以提高速度如果数据量不大,为了缓系统表的资源,应先 create table,然后 insert。
12、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table,然后2 drop table,这样可以兔系统表的较长时间锁定
从SQL语句方面
1、应尽量避免在 where子句中使用!=或<>操作符,否则将引放弃使用索引而进行全表扫描。
2、应尽量免在 where子句中使用r来连接条件,否则将导致引警放弃使用引而进行全表扫描,如: select id from t where num=10 or num20可以这样查询: select id from t where num=10 union all select id from t where num=20
3、in和 not in也要慎用,否则会导致全表扫描,如: select id from t where num In(1,2,3)对于连续的数值,能用 between就不要用in了: select id from t where num between 1 and 3
4、下面的询也将导致全表扫描: select id from t where name like %abc%
5、如果在 where子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: select id from t where nun=@num可以改为强制查询使用索引: select id from twith( index(索引名) where num@num
6、应尽量避免在 where子句中对字段进行表达式操作,这将导致引放弃使用索引而进行全表扫描。
如: select id from t where nun/2=100应改为: select id from t where num=100*2
7、应尽量避免在 where子句中对字段进行函数操作,这将导致引放弃使用索引而进行全表扫描
如: select id from t where substring(name, 1,3)='abc' --name以abc开头的id
应改为: select id from t where name like 'abc% '
不要在 where子句中的“=”左边进行数、算术运算或其他表达式运算,否则系将可能无法正确使用索引。
8、不要一些没有意义的查询
如需要生成一个空表结构: select col1,col2into# t from t where10这关代码不会返回任何结果集,但是会消耗系統源的,应改成这样: create table#t(
9、很多时候用 exists代in是一个好的选择
select num from a where num in(select num from b)
用下面的语句换: select num from a where exists( select 1 from b where num=a.num)
10、不要使用 select * from,用的字列表' * ',不要返回用不到的任何字段。
11、尽量避兔使用游标,因为游标的效率较差,如果游标操作的数据超过1万行那么就应该考虑改写
12、尽量避兔向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理
13、尽量避兔大事务操作,提高系统并发能力。
物理优化方面说
1、 Oracle的运行环境(网络,硬件等)
2、使用合适的优化器