数据库培训

开始亿阳信通股份有限公司数据库培训DB组万瑾主要内容主要内容1.数据库系统维护2.WNMSDB系统运行3.现场维护原则与流程主要内容数据库系统维护数据库系统维护•简要系统维护•系统检查•参数设置•SQL语句及优化•经验介绍环境变量和配置文件简单系统维护主要环境变量和配置文件环境变量：INFORMIXDIR/opt/informixINFORMIXSERVERnmserver2LD_LIBRARY_PATH$INFORMIXDIR/libONCONFIGonconfig服务器表文件$INFORMIXDIR/etc/sqlhostsnmserveronipcshm10.0.0.18001nmserver2ontlitcp10.0.0.18002参数文件etc/onconfig数据库启停(1)简单系统维护数据库启停(1)•1.onmode-k•2.oninit(-v)•3.onmode–m•4.onmode–s(u)•5.oninit-s•6.onmode–k(y)OnlineOfflineQuiescent123456数据库启停(2)简单系统维护数据库启停(2)•oninit•oninit–v•log文件：$INFORMIXDIR/online.log•失败常见原因：–Shm分配错误–数据库设备错误–环境变量或配置文件错误–处理临时表空间错误状态检查简单系统维护状态检查•onstat一般：–空间：–d锁：-k日志：-l用户：-u事务：-xIO:-giov-giofSQL:-gsql……物理检查简单系统维护物理检查•oncheck数据库：–cc索引：–cI数据：-cD……结构脚本简单系统维护结构脚本•dbschema从数据库中导出数据库对象的脚本，如：dbschema–ddbname-ttablenamefilenamedbschema–ddbname-fprocnamefilename•dbaccess运行脚本：dbaccessdbnamesqlfile.sqldbaccessdbname-!SQLStatement!数据操纵简单系统维护数据操纵•表级数据出数据库：UNLOADTO‘sqlfile.sql’SELECT*FROMTABLENAME;入数据库：LOADFROM‘sqlfile.sql’INSERTINTOTABLENAME;•数据库级数据拷贝出数据的结构和数据：dbexport拷贝入数据库的结构和数据：dbimport空间管理(1)简单系统维护空间管理(1)dbspace操作系统文件原始设备盘区chunk数据库对象（表、索引、存储过程等）dbspacen:nn:ndbobjects空间管理(2)简单系统维护空间管理(2)•创建数据库或表在某个DBspace上createdatabasedbnameindbspacenamecreatetabletabname(…)…indbspacename•创建DBspaceonspaces–a–cdbspace–p…•给DBspace增加空间onspaces–adbspace–p…•查看空间利用状态onstat-d常用命令列表简单系统维护常用命令列表•数据库服务器：oninit•数据操纵：dbaccess•结构导出：dbschema•状态检查：onstat•数据库状态改变：onmode•数据库参数：onmonitor•informix查错：finderr•数据库空间操作：onspaces数据库状态查看(1)系统检查数据库状态查看(1)•onstat–InformixDynamicServerVersion7.31.UC2A--On-Line--Up5days02:19:30--480320Kbytes•onstat–gsqlSessSQLCurrentIsoLockSQLErrISAMErrF.E.VersIdStmttypeDatabaseLvlMode15891SELECTcookdb2DRNotWait009.2315888SELECTcookdb2DRNotWait009.23•onstat–gsql(ses)sessid进一步查看某一进程的详细信息•onstat–u查看进程占用锁的个数以及此进程对数据的读写量数据库状态查看(2)系统检查数据库状态查看(2)•onstat–k查看锁的信息•onstat–l查看物理和逻辑日志状态•onstat–p重点查看：Reads%cached应尽量保证在95%以上，Writes%cached保证在85%以上。•onstat–gseg查看数据库系统占用共享内存的情况，应该保证少于3块的共享内存区域占用。•onstat–giof查看各chunk文件的读写量和读写率数据库状态查看(3)系统检查数据库状态查看(3)•onstat–dDbspacesaddressnumberflagsnchunksfchunkflagsownername253c61501111Ninformixrootdbs253c71002200121NTinformixtpmdbs1253c7700101105NinformixtmndbsChunksaddresschk/dbsoffsetsizefreebpagesflagspathname253c62101/10500000498659PO-/inf_disk/rootchk253c63e02/20250001505PO-/inf_disk/tmpchk1.chk253c6d8013/10010000007PO-/inf_disk/tmnchk2.chk253c6e6014/10010000002PO-/inf_disk/tmnchk3.chk数据库物理检查系统检查数据库物理检查•oncheck-cc数据库目录结构检查：oncheck–ccdatabase•oncheck–cI数据库索引结构检查：oncheck–cIdatabase:tablename#indexname•oncheck–cD数据库数据结构检查：oncheck–cDdbname:tablename•oncheck–ceextent空间检查：oncheck-ce•oncheck–p生成检查的报告而不进行修复操作系统检查系统检查操作系统检查•df-k查看各文件系统的使用情况•ps-ef查看操作系统正在运行的进程情况•vmstat查看操作系统的内存使用情况•iostat查看操作系统的磁盘使用情况参数设置参数设置•BUFFERS此参数指定Informix占用共享内存的大小。通常，Informix推荐此参数设置为物理内存的20%~25%•NUMCPUVPS此参数指定CPU虚处理器个数（virtualprocessors），在多CPU系统上，指定NUMCPUVPS不能超过CPU的个数（CPU-1)•LRUS表示总BUFFERS可分成多少个队列，LRU队列将缓冲区分成修改和未用两个部分，LRUS越多，就能更好地支持大量用户，减少bufwaits。Informix推荐LRUS的计算公式为：LRUS=max(4,(no.ofcpuVPs))•CLEANERS定义清理修改缓冲区地Informix线程数，建议CLEANERS值等于LRU数，如果指定较小值，校验时间可能大大增加参数设置参数设置•LOGSIZEInformix提供的LOGSIZE的范围为：200～(ROOTSIZE-PHYSFILE-512-(63*pagesize/1024))/LOGFILES单位为K。•LOGBUFFER此参数指定共享内存区中三个逻辑日志缓存的大小，Informix建议设置为pagesize的倍数（SunSolaris系统的pagesize为2K）如16K、32K，对于高负荷的系统可设置为64K•PDQPRIORITY控制使用并行查询资源的参数，可有效地提高大数据量插入的效率•……基本SQL语句SQL语句及优化基本SQL语句•CREATE•SELECT•UPDATE•DELETE•……优化原则SQL语句及优化优化原则•提高where子句的选择性•updatestatistics统计更新•提高索引的性能•减小连接和排序的影响问题•速度快的大表查询一定是使用了索引的查询•表上建立的索引不一定会被使用到•小表上没有必要建立索引•最好是看查询计划确定优化结果提高WHERE子句的选择性SQL语句及优化提高WHERE子句的选择性•避免复杂的正则表达式wherenamenotlike“z%”•避免子串匹配wherename[4,6]“60”•避免列计算wherelevel+30=3400whereweekday(some_date)=0•缩小选择范围whereobject_class=101UPDATESTATISTICSSQL语句及优化UPDATESTATISTICS•对全库对象updatestatistics•对某个数据库对象updatestatisticsfortabletablename;updatestatisticsforprocedureprocname;•指定统计更新的级别LOWmode,MEDIUMmode,HIGHmode•定期运行/opt/WNMS31DB/task/updstat_cmupdstat_fmupdstat_ologupdstat_pm提高索引的性能SQL语句及优化提高索引的性能•使用组合索引如索引在table(a,b,c)可以使用索引：•wherea0•wherea=90anda=2•wherea=0andb80•wherea=0andb=20andc=40不可以使用索引：•whereb=10•wherec=330•wherea0andb=10•使用聚簇索引•定时检查和重建索引减小连接和排序的影响SQL语句及优化减小连接和排序的影响•避免或简化排序操作表关联个数最好不要超过四个•使用并行排序设置PDQPRIORITY参数•使用临时表减小排序范围查看数据库锁方法经验介绍查看数据库锁方法•查找系统表被锁•首先通过以下语句找到systables表的partnum（取十六进制）；selecttabname,hex(partnum)fromsystableswheretabname='systables';•使用onstat-k查找tblsnum=hex(partnum)且rowid=0的Lock显示（如果存在的话），取得其owner值（十六进制）；•使用上面取到的owner值用onstat-u获取sessid•使用onstat-gsessessid查看实际锁表的语句•其他表方法相同•分片表的partnum需要从sysfragments系统表中的partn中取出系统长事务经验介绍查找造成长事务的SQL•查找当前长事务的报告日志得到Transactionid，这里是：d69452grep'AbortingLongTransaction:'online.log|tail-113:03:46AbortingLongTransaction:tx0xd69452dcusername:informixuid:1001•根据第一步的Transactionid察看具体的事务，得到threadsid，这里是：d91de2d8onstat-x|grepd69452d69452dcA----d91de2d820COMMIT0•根据第二步得threadsid察看具体的线程信息，得到sessionid，这里是：36988onstat-u|grep91de2d8d91de2d8Y--P---36988informix2a06051800155720•根据第三步得到的sessionid来查看具体的SQL语句onstat-gses36988或•用onstat-gsql查看所有SQL进程，SQLErr为213的那个进程，实际就是引起长事务的进程检查大表和对大表的维护经验