InformixOnLine管理和优化Word文档格式.docx
- 文档编号:22129643
- 上传时间:2023-02-02
- 格式:DOCX
- 页数:22
- 大小:26.09KB
InformixOnLine管理和优化Word文档格式.docx
《InformixOnLine管理和优化Word文档格式.docx》由会员分享,可在线阅读,更多相关《InformixOnLine管理和优化Word文档格式.docx(22页珍藏版)》请在冰豆网上搜索。
九并发控制9
9.1并发类型9
9.2锁的分类9
9.3读并发9
9.3.1dirtyread9
9.3.2commitread10
1.1.3cursorstability10
9.3.4repeatableread10
9.3.5informix隔离级设置10
9.4写并发10
十索引12
10.1索引12
10.2类型12
10.3指导12
十一完整性13
11.1库中数据的正确性13
11.2完整性类型13
11.2.1Default值13
11.2.2NOTNULL13
11.2.3check约束13
11.2.4参照约束14
11.2.5Unique约束14
11.3定义方式14
11.4约束的检查模式14
十二模式和违例检查15
12.1数据库对象类型15
12.2模式15
12.3控制方法15
十三基于代价的查询优化器16
13.1三种连接策略16
13.2多表连接的过程16
13.3优化级别17
十四统计更新和数据分布信息18
14.1统计信息的成分18
14.1.1系统表18
14.1.2分布信息18
14.2模式18
14.3参数18
14.3.1resolution18
14.3.2confidence19
14.4建立分布信息的指导19
十五数据安全20
15.1授权机制20
15.1.1库级20
15.1.2表级20
15.1.3列级20
15.1.4存储过程20
15.2缺省授权20
15.3Grant语句20
15.4revoke21
15.5角色21
15.6分段授权21
十六视图22
16.1定义22
16.2创建22
16.3删除22
16.4WITHCHECKOPTION22
十七数据移动工具23
17.1DBexport23
17.2DBLoad23
17.3OnLoad23
十八SQL语句的优化24
一数据类型
一.1CHAR和VARCHAR
CHAR类型可以存储包括Tab和空格在内的各种字符和数字,但不能包括其它非打印字符。
CHAR类型的最大长度为32767个字节。
CHAR类型为固定长度,不足之处用空格填补。
VARCHAR类型存储变长字符数据,可存储0到255个字节的字符数据。
max-size参数设置数据项的最大长度,min-size选项(可选)设置数据项的最小长度。
在每个VARCHAR类型的实际数据之前,有1个字节的长度标志符用以标志该数据项的实际长度。
一.2数字类型
数字类型包括:
●INTEGER:
INTEGER类型数值占4个字节,取值范围为-2,147,483,647至+2,147,483,647。
●SMALLINT:
SMALLINT类型数值占2个字节,取值范围为-32,767至+32,767。
●FLOAT:
对应于C中的double数据类型。
●SMALLFLOAT:
对应于C中的float数据类型。
●DECIMAL/MONEY:
DECIMAL和MONEY数据类型可由用户指定其精度和范围。
DECIMAL类型的缺省精度和范围为(16,0)。
MONEY类型的缺省精度和范围为(16,2),可以通过环境变量DBMONEY改变MONEY数据类型的显示格式。
一.3SERIAL
SERIAL数据类型存放由系统按顺序分配给表中的每一行的数值,其类型为INTEGER型。
一.4DATE、DATETIME、INTERVAL
●DATE:
用以存储日历日期。
●DATETIME:
用以指定具体的时间值。
●INTERVAL:
用以指定时间的间隔范围。
一.5DBCENTURY
取年份的前两位,INFORMIX-OnLine7.2版以后提供。
一.6BinaryLargeObject
BLOB数据类型包括:
BYTE和TEXT。
二创建库和表
二.1创建库
创建一个库之前,首先要确定:
●日志模式
●存储位置
●是否遵从ANSI标准
语法:
CREATEDATABASEdb_name
[INdbspace_name]
[WITHLOG|WITHBUFFEREDLOG|WITHLOGMODEANSI]
二.2创建表
创建一个表之前,首先要确定:
●区间大小
●dbspace
●锁模式(页面锁、行级锁)
示例:
CREATETABLEorders{
order_numSERIALNOTNULL,
customer_numINTEGER,
order_dateDATE)
INdbspace1
EXTENDSIZE64
NEXTSIZE32
LOCKMODErow;
二.3创建临时表
临时表与永久表类似,只是临时表只在一次会话中有效,会话结束后,创建的临时表会自动删除。
同时,在系统表中不记录临时表的相关信息,也不允许用altertable命令对临时表进行修改。
CREATETEMPTABLEtemp_order(
order_numINTEGER)WITHNOLOG;
三更新表
可以通过altertable命令对已存在的表作以下修改:
1.增加一列
2.修改某列
3.删除一列
4.修改区间大小(只能修改NextExtendSize)
5.修改锁模式
ALTERTABLEtest_tab
DROPcol2
MODIFYcol1INTEGERNOTNULL,
ADDcol4INTEGER,
ADDcol3CHAR(20)BEFOREcol4;
ALTERTABLEorders
MODIFYNEXTSIZE300;
LOCKMODE(ROW);
四表空间回收
当一块区间分配给一个表后,该区间将不会被释放给其它表使用。
即使该区间不存放数据(如原存放数据被delete掉),该空间只能被分配给同一个表中插入的新记录使用。
可以通过修改表中的索引为聚集索引的方法,强迫调整表的物理结构,释放不用的空间给其它表使用。
【语法】:
ALTERINDEXitem_idxtoCLUSTER;
五重命名
五.1重命名一列
RENAMECOLUMNtable_name.old_col_nameTOnew_col_name;
五.2重命名一个表
RENAMETABLEold_table_nameTOnew_table_name;
五.3重命名一个库
该功能仅在7.10.UD1版之后有效。
RENAMEDATABASEold_db_nameTOnew_db_name;
六删除库和表
DROPDATABASEdb_name;
DROPTABLEtable_name;
七DBSchema工具
导出所有建库语句,包括CREATETABLE,CREATEINDEX,GRANT,CREATESYNONYM和CREATEVIEW等。
【选项】:
-d库名:
指定库名,必须包含;
-t表名:
只导出指定表的信息;
-ss包括锁模式、区间大小和dbspace名称;
八分段
八.1分段策略
八.1.1roundrobin
将数据等机率放到给dbspace中。
优点:
数据均匀分布;
update不会引起数据移动。
缺点:
对优化无帮助。
时机:
对数据分布不了解,经常使用顺序扫描。
【示例】:
创建分段:
CREATETABLEorders(
order_numSERIAL,
descriptionCHAR(20))
FRAGMENTBYROUNDROBININdbs1,dbs2,dbs3;
给未分段的表分段:
ALTERFRAGMENTONTABLEitems
INITFRAGMENTBYROUNDROBININdbs1,dbs2,dbs3;
给已分段的表增加分段:
ALTERFRAGMENTONTABLEcustomerADDdbs4;
八.1.2基于表达式(Expressionbased)
利于优化查询;
实现一定的备份策略;
在分段的基础上给用户授权;
均衡访问。
占用CPU资源。
对数据有一定的了解;
数据有一定的特点。
CREATETABLEtable1(
col_1SERIAL,
col_2CHAR(20),
…)
FRAGMENTBYEXPRESSION
col_1<
=10000ANDcol_1>
=1INdbs1,
=20000ANDcol_1>
10000INdbs2,
REMAINDERINdb3;
八.1.3散列分段
customer_numSERIAL,
lnameCHAR(20)
…)
MOD(customer_num,3)=0INdbs1,
MOD(customer_num,3)=1INdbs2,
MOD(customer_num,3)=2INdbs3;
八.2索引分段
CREATEINDEXidx1ONtable1(col_1)
10000INdbs1,
col_1>
=10000INdbs2;
九并发控制
九.1并发类型
●读并发。
●写并发。
九.2锁的分类
●共享锁:
读数据时给该数据上共享锁。
如果数据已被上了排它锁后,不能再上共享锁。
数据运行被上多个共享锁。
●排它锁:
数据被上了排它锁后,不能再上其它锁。
●修改锁:
类似与共享锁,但可以升级成排它锁
九.3读并发
对读操作有四种隔离级(IsolationLevel)。
九.3.1dirtyread
读操作时不检查数据是否被上锁,但有可能读到的是脏数据(dirtydata)。
SETISOLATIONTODIRTYREAD;
九.3.2commitread
首先尝试申请共享锁,但并不加锁。
SETISOLATIONTOCOMMITREAD;
九.3.3cursorstability
游标的当前记录加共享锁。
九.3.4repeatableread
整个事务中所有行均上共享锁,直至该事务结束。
九.3.5informix隔离级设置
无日志不能设置隔离级。
会话期间内都会起作用。
ANSI标准的缺省隔离级是repeatableread。
九.4写并发
锁的粒度:
被上锁对象的规模。
1.库级锁:
DATABASE库名EXCLUSIVE;
【解锁】:
必须执行CLOSEDATABASE语句,再重新打开数据库。
2.表级锁:
LOCKTABLE表名IN[SHARE|EXCLUSIVE]MODE;
UNLOCKTABLE表名;
3.页级锁:
建表时决定该表为页级锁还是行级锁。
上锁时对整个数据页上锁。
4.行级锁:
上锁时只对记录行上锁。
5.码级锁:
索引上设置
keyvalue
rowid
deleteflag
0=Notdeleted
1=Deleted
一十索引
一十.1索引
索引码:
建立索引好的列。
CREATEINDEX索引名ON表名(列名,…)
一十.2类型
1.唯一索引
2.重复索引
3.复合索引:
多列上建索引,最多16列,字段长度<
255bytes。
部分扫描,可以利用索引上的部分列。
4.聚集索引:
索引中的次序与表中的物理次序相同,适用于静态表。
一十.3指导
1.在经常连接的列上建索引;
2.选择唯一性高的列建索引;
3.在经常过滤的列上建索引;
4.对排序的列建索引;
5.避免在高重复度的列上建索引;
解决方法:
再增加一个列,构成复合索引。
6.维护较大的表不要建太多索引;
7.索引码长度越小越好;
一十一完整性
一十一.1库中数据的正确性
1.实体完整性:
通过主码限制表中数据各不相同。
2.参照完整性:
限制两表之间的关系。
3.语义完整性:
用户自定义完整性。
一十一.2完整性类型
一十一.2.1Default值
常数或函数。
BLOB列只能用NULL作Default值。
Serial列不能有Default值。
一十一.2.2NOTNULL
一十一.2.3check约束
一十一.2.4参照约束
1.普通参照:
2.自参照
3.多路参照
一十一.2.5Unique约束
一十一.3定义方式
1.列级:
约束字节跟在列定义之后。
2.表级
一十一.4约束的检查模式
1.立即约束检查:
在每条语句之后立刻检查。
2.延迟约束检查:
在事务结束前检查。
3.分离:
在无日志模式的情况下,每行操作之后作检查。
一十二模式和违例检查
对约束的破坏称为违例。
一十二.1数据库对象类型
1.约束
2.索引
3.触发器
一十二.2模式
1.enabled:
有效状态。
2.disabled:
对象存在,但不工作。
3.filtering:
除触发器外,其它均处于工作状态,但发现错误后,事务不回滚,而将错误记入违例日志。
一十二.3控制方法
setconstraint约束名,…disabled
一十三基于代价的查询优化器
一十三.1三种连接策略
1.nestedloopjoin:
嵌套循环连接。
扫描表1,用一个记录到表2中找匹配。
2.sortmergejoin:
排序归并连接。
先将表1和表2排序,在进行匹配。
3.hashjoin:
散列连接。
先在大的表建立hash表,每个散列值对应一个桶(bucket),hash表中排序,桶中不排序。
Informix的优化选项设置OPTCOMPIND可设置为0、1和2,其中:
【0】:
只考虑索引的使用,无索引时考虑hashjoin。
【1】:
处于repeatableread模式时,同0,否则同2。
【2】:
基于开销进行优化。
一十三.2多表连接的过程
1.检查所有的表,估计每个过滤器的选择度及索引。
2.估计两表连接的开销,去掉多余的开销。
如估计ab、ba、ac、ca…连接的开销,如ab的开销<
ba的开销,则去掉ba。
3.估计三表连接的开销。
4.……
一十三.3优化级别
1.精确优化:
寻找所有的优化路径。
2.非精确优化:
只保留最佳的连接,如ab、ba、ac、ca…的开销中只保留最佳的连接方式,并继续后续处理。
一十四统计更新和数据分布信息
一十四.1统计信息的成分
一十四.1.1系统表
【systables】:
估计顺序扫描的代价。
【sysindexs】:
估计索引扫描的代价。
【syscolumns】:
一十四.1.2分布信息
sysdistrib:
统计值的分布情况。
如何建立:
1.读出所有指定列上的值。
2.取出部分或所有记录进行排序。
3.确定范围的个数N=100/resolution
一十四.2模式
UPDATESTATISTICS[FORTABLE[tab_name]][LOW|HIGH]
缺省值为MEDIUM。
LOW模式不统计分布信息。
一十四.3参数
一十四.3.1resolution
解析度,缺省值:
high模式为0.5,medium模式为2.5。
一十四.3.2confidence
置信度,medium模式下样本的规模,缺省值为0.95,取值范围:
0.8~0.99。
一十四.4建立分布信息的指导
索引中第一列可以用high。
一十五数据安全
一十五.1授权机制
一十五.1.1库级
connect
resource
DBA
一十五.1.2表级
一十五.1.3列级
select、update、reference、all
一十五.1.4存储过程
execute
一十五.2缺省授权
一十五.3Grant语句
一十五.4revoke
不能按列revoke
一十五.5角色
role:
一组可以授予相同权限的用户。
激活角色:
setrole…
以一次会话为周期,每次会话结束后,须重新激活角色。
一十五.6分段授权
基于表达式的分段才可以进行分段授权。
一十六视图
一十六.1定义
一十六.2创建
不能用orderby、union、intotemp
一十六.3删除
一十六.4WITHCHECKOPTION
一十七数据移动工具
一十七.1DBexport
一十七.2DBLoad
一十七.3OnLoad
以二进制页的形式移动,只能在本地进行,不能跨网络。
一十八SQL语句的优化
●对于经常重复执行的语句,使用prepare;
●对于关联或子查询运算,应调整where条件,尽量使选择集收敛到最小;
●慎重编写子查询语句;
●经常使用一张大表的少量记录时,给这些记录常见临时表,用临时表替代大表上的运算;
●当确定无其它应用访问时,锁表;
●使用or进行连接时,如果条件字段不同,则无法使用索引,如:
SELECT*FROMtable1WHEREc1>
2orc2<
3(c1、c2上有索引),此时无法使用索引,可以修改为:
2
UNION
SELECT*FROMtable1WHEREc2<
3
●使用MATCHES、LIKE进行匹配查询,或使用子串匹配时,应尽量从第一个字符比起,负责无法使用索引;
●使用游标时,应尽量避免使用ORDERBY和GROUPBY子句,因为它们要生成临时表;
●应尽量避免相关子查询(IN语句),可将其转换为多表查询;
●尽量将过滤条件设在索引上;
●建复合索引要考虑其收敛速度,如“性别、姓名”复合索引不如“姓名、性别”复合索引的速度快;
●利用复合索引的有效性,如在a、b、c三个字段上建复合索引,那么在a上可以使用索引,ab、abc上也可以使用索引;
●合理使用索引,避免在大表上作顺序查找;
●大量地插入、删除或修改数据时,可先删除索引,完成后再重建索引。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- InformixOnLine 管理 优化