机房建设数据库文档

IDC机房建设数据库文档马飞2016-11-1617目录一、数据库概况3二、数据库设计42.1 数据库设计理念42.2 数据库设计优势42.2.1 节省存储空间42.2.2 数据安全性高42.2.3 高性能表设计52.2.4 优化表间关系52.2.5 避免复杂查询62.2.6 节省网络流量62.2.7 应用程序访问62.2.8 高并发设计72.2.9未来数据增长7三、数据库安装83.1 Oracle11g安装83.1.1 操作系统参数修改83.1.2 数据库安装准备103.1.3 数据库安装过程113.1.4 建库后实例检查133.2 MySQL安装133.2.1 卸载rpm包133.2.2 收集mysql对应的文件夹133.2.3 删除mysql对应的文件夹143.2.4 下载mysql安装程序143.2.5 解压mysql安装程序143.2.6 添加用户和组143.2.7 复制解压到本地软件目录143.2.8 安装数据库14四、数据库管理154.1 数据库主机监控154.2 数据库日志监控154.3 数据库表空间监控164.4 数据库低性能SQL监控164.5 数据库低性能SQL监控164.6 数据库锁资源监控16五、数据库安全165.1 数据库设计保证数据安全165.2 数据库备份策略保证数据安全175.3 数据库代码上保证数据安全175.4 建立安全策略保证数据安全175.4.1 系统安全策略175.4.2 数据安全策略185.4.3 用户安全策略18六、数据库优化196.1 数据库优化运维方法196.1.1 优化全表扫描性能196.1.2 建立索引改善查询性能206.1.3 通过绑定变量优化查询236.1.4 通过OWI定位性能瓶颈246.1.5 通过动态SQL优化查询246.1.6 通过分区优化大表查询246.2 优化查询方法256.2.1 改写NOT IN优化查询256.2.2 使用循环嵌套优化多表查询266.2.3 复杂视图优化方法266.2.4 通过HINT优化多表查询266.2.5 通过WITH AS子句优化多表查询276.3 慢查询定位分析276.3.1 锁资源定位和分析276.3.2 大事务定位及分析286.3.3 性能低语句检测方法28一、数据库概况本文从数据库安装、数据库设计、数据库管理、数据库优化、数据库安全等方面详细介绍了数据库的相关内容。数据库安装一章详细介绍了常用数据库的安全部署方法。数据库设计一章中介绍了如何设计一个安全、高性能、高并发的数据库。 数据库管理一章中介绍了数据库管理方面日常运维的工作内容。数据库优化一章介绍了常用的数据库优化技巧。二、数据库设计2.1 数据库设计理念我们的设计理念是从优化物理存储设计、业务表间关系、业务数据存储、消除业务表冗余数据、创建合适的主键，外键，唯一性约束、查询频繁列创建索引等方面来进行数据库设计。我们的目标是建立一个高安全性、高性能、高并发的数据库设计模型。我们分别从节省存储空间、高安全性、高性能表设计、优化表间关系、节省网络流量、应用程序设计、高并发设计、满足未来数据增长多个维度对业务数据库进行设计。2.2 数据库设计优势2.2.1 节省存储空间（1）设计方法通过分析具体业务，为表中的每一列设计满足业务要求的最小的数据类型，使存储该列占用的空间最小，每一列存储最小化，每行也将占用更少空间，使得整个表变得更小。（2）设计优势n 节省存储空间n 节省内存空间n 提升读写性读2.2.2 数据安全性高（1）设计方法业务主表中存储在业务主要信息及子表状态信息，每一个业务主表中都指派一个ID作为主键，业务子表中存储主表ID信息及子表业务信息。假使只有子表信息泄露，主表信息未泄露，由于子表中只存有主表ID信息，无法知道子表中信息的含义，因此无主表数据子表的数据没有任何意义。我们只需要对业务主表进行安全监控就可以。大大降低了系统数据安全监控的成本。（2）设计优势n 减少数据冗余n 提高数据安全n 简化数据管理2.2.3 高性能表设计（1）设计方法表中只存储代码，这在存储设计时就已经考虑到了。设计系统代码表用于存储各类代码与名称映射关系，减少每行数据大小。业务表中尽量少存储中文或不存储中文。这样一来表中基本上存储的都是数字或字符编码，存储相同数据占用更少的空间。查询时只需要将代码映射为名称即可，代码与名称映射都是通过主键进行返回，性能不是问题。（2）设计优势n 节省存储空间n 降低网络带宽n 提高查询性能n 减少数据冗余2.2.4 优化表间关系（1）设计方法通过对业务分析，对各类相关联信息进行主从表设计，主表上除存储共性数据外，还存储子表各类状态信息。子表只存储与主表的主键及子表相关业务数据。在子表上创建外键，并在外键上创建索引。（2）设计优势n 查询相关数据从主表获取，尽可能避免表关联。n 外链上增加索引从而提升主从表关联性能2.2.5 避免复杂查询（1）设计方法业务数据从主表可获取，若查询用到从表数据，可通过外键关联获取。这样设计可使得相同类型数据出口只有一张表，只有个性化数据可从从表获取。主从表设计避免复杂查询。（2）设计优势n 避免使用复杂查询而性能下降。n 降低程序开发难度，简化查询语句。n 便于系统日常运维。n 减少数据冗余2.2.6 节省网络流量（1）设计方法表的字段设计为满足业务需要的最小数据类型，表中尽可能地存储代码值。使得全表扫描及索引扫描的性能大大幅提高。同时在业务层面上使得查询结果集变得更小，占用网络带宽小，使得业务访问速试快，同时相同的网络环境可以支持更多的应用会话。（2）设计优势n 节省网络流量n 高并发会话支持n 高并发业务支持n 高性能查询2.2.7 应用程序访问（1）设计方法不同业务不同属性根据业务实际情况进行非空属性设计, 可确保最高效地使用索引来优化查询。通过在的常用列上创建索引可提高常用列访问数据的性能。 表名设计尽可能简单，方便记忆，便于开发及后期运维。 （2）设计优势n 避免空值操作提升查询性能n 简化开发代码编写n 简化日常运维操作n 减少业务锁定时间n 提升系统并发能力2.2.8 高并发设计（1）设计方法通过把表设计为最小存储，使得创建的索引也将变小。通过在表上常用列创建索引，使得查询数据时尽可能使用索引来获取数据，磁盘读减少，内存占用率降低，从而提高查询性能。查询性能提高了，进行更新、删除、插入操作时锁定时间将减少，相同时间内可支持更多并发会话访问共享资源。（2）设计优势n 减少业务锁定时间n 提升系统并发能力n 提高查询性能2.2.9未来数据增长（1）表被设计为尽可能小，数据行尽可能小，可以确保数据库数据增长量变小，可以满足未来多年的业务增长需要。节省增加存储的开销。（2）子表中去掉了容余信息，使得子表变得比主表要小得多。（3）假使原来每天100M数据增长量，优化后的数据库可能占用不到1M，甚至几K。大大节省存储空间。（4）既使未来几年内表中数据量增长到千万级别，由于表小。则全表扫描性能也不会差。若性能出现问题，则可以通过分区表来解决。（5）查询单位或个人信息只从单位主表或个人主表查询，基本表中记录了所有单位个人信息或子表状态信息，尽可能满足各种查询需要。最大程序地避免了表关联的代价。既使需要关联，由于在主键、外键上都有索引且表和索引都很小，且常用列已创建了索引，因此性能也会非常好。（6） 由于数据增长量减小，未来三五年内数据库的性能依然表现非常好，可减少采购存储的开销。除非并发量极聚增长，磁盘读写是性能瓶劲时只能更換转速更快的磁盘。CPU和内存在未来几年内不需要更換，因为数据行小，计算量小,同时内存占用量也小。使得相同大小的内存可以容纳更多业务的数据。三、数据库安装3.1 Oracle11g安装Oracle数据库版本：Oracle3.1.1操作系统参数修改（1）limits.conf文件修改修改用户的SHELL的限制，修改/etc/security/limits.conf文件输入命令：vi /etc/security/limits.conf，按i键进入编辑模式，将下列内容加入该文件。oracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536编辑完成后按Esc键，输入“:wq”存盘退出（2）login文件修改修改/etc/pam.d/login 文件，输入命令：vi /etc/pam.d/login，按i键进入编辑模式，将下列内容加入该文件。session required /lib/security/pam_limits.sosession required pam_limits.so编辑完成后按Esc键，输入“:wq”存盘退出（3）sysctl.conf文件修改修改linux内核，修改/etc/sysctl.conf文件，输入命令: vi /etc/sysctl.conf ，按i键进入编辑模式，将下列内容加入该文件fs.file-max = 6815744 fs.aio-max-nr = 1048576 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 4194304 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576编辑完成后按Esc键，输入“:wq”存盘退出。（4）sysctl.conf文件生效要使 /etc/sysctl.conf 更改立即生效，执行以下命令。 输入：sysctl -p 显示如下：linux: # sysctl -p net.ipv4.icmp_echo_ignore_broadcasts = 1 net.ipv4.conf.all.rp_filter = 1 fs.file-max = 6815744 fs.aio-max-nr = 1048576 kernel.shmall = 2097152 kernel.shmmax = 2147483648 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 4194304 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576（5） /etc/profile文件修改编辑 /etc/profile ，输入命令：vi /etc/profile，按i键进入编辑模式，将下列内容加入该文件。if $USER = oracle ; then if $SHELL = /bin/ksh ; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi编辑完成后按Esc键，输入“:wq”存盘退出。3.1.2数据库安装准备（1）创建用户和组创建相关用户和组，作为软件安装和支持组的拥有者。创建Oracle用户和密码,输入命令：groupadd oinstallgroupadd dbauseradd -g oinstall -g dba -m oraclepasswd oracle然后会让你输入密码，密码任意输入2次，但必须保持一致，回车确认。检查用户所在组是不正确groups oracle如果不正确，则进行修改usermod -g oinstall -G dba oracle（2）创建数据库软件目录创建数据库软件目录和数据文件存放目录，目录的位置，根据自己的情况来定，注意磁盘空间即可，这里我把其放到oracle用户下,例如：输入命令：mkdir -p /opt/app/oradatamkdir -p /opt/app/oracle/product（3）更改目录属主更改目录属主为Oracle用户所有，输入命令：chown -R oracle:oinstall /opt/app（4）配置环境变量配置oracle用户的环境变量，首先，切换到新创建的oracle用户下,输入：su oracle ，然后直接在输入： vi .bash_profile按i编辑 .bash_profile,进入编辑模式，增加以下内容：export ORACLE_BASE=/home/oracle/app export ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/db_1 export ORACLE_SID=orcl export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib编辑完成后按Esc键，输入“:wq”存盘退出3.1.3数据库安装过程（1）解压安装程序当上述系统要求操作全部完成后，注销系统，在图形界面以Oracle用户登陆。首先将下载的Oracle安装包复制到linux中，用SSH其他ftp工具拷贝。打开一个终端，运行unzip命令解压oracle安装文件，如：输入命令：unzip linux.x64_11gR2_database_1of2.zipunzip linux.x64_11gR2_database_2of2.zip解压完成后 cd 进入其解压后的目录database输入命令：cd database（2）编辑静默响应文件（db_install.rsp）oracle.install.option=INSTALL_DB_SWONLYORACLE_HOSTNAME=UNIX_GROUP_NAME=dbaINVENTORY_LOCATION=/opt/app/oraInventorySELECTED_LANGUAGES=en,zh_CN,zh_TWORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1ORACLE_BASE=/opt/app/oracleoracle.install.db.InstallEdition=EEoracle.install.db.EEOptionsSelection=trueoracle.install.db.DBA_GROUP=oinstalloracle.install.db.OPER_GROUP=oracle.install.db.config.starterdb.type=GENERAL_PURPOSEoracle.install.db.config.starterdb.globalDBName=orcloracle.install.db.config.starterdb.SID=orcloracle.install.db.config.starterdb.characterSet=AL32UTF8oracle.install.db.config.starterdb.memoryOption=trueoracle.install.db.config.starterdb.memoryLimit=512oracle.install.db.config.starterdb.installExampleSchemas=trueoracle.install.db.config.starterdb.enableSecuritySettings=trueoracle.install.db.config.starterdb.password.ALL=jusfounoracle.install.db.config.starterdb.control=DB_CONTROLoracle.install.db.config.starterdb.automatedBackup.enable=falseoracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGEoracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGEoracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/opt/app/oradataoracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGEoracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=MYORACLESUPPORT_USERNAME=zhdn_791005163.comMYORACLESUPPORT_PASSWORD=MAFEIcnnui791005SECURITY_UPDATES_VIA_MYORACLESUPPORT=falseDECLINE_SECURITY_UPDATES=true（3）静默安装数据库软件su - oraclecd database./runInstaller -silent -force -responseFile /home/oracle/database/response/db_install.rsp（4）编辑静默响应文件（dbca.rsp）修改仅安装数据库的响应文件/home/oracle/etc/dbca.rspGDBNAME=orcl.java-linux-test /78 行全局数据库的名字=SID+主机域名SID=orcl /149行 SIDCHARACTERSET=AL32UTF8 /415行编码NATIONALCHARACTERSET=UTF8 /425行编码（5）静默安装数据库$ORACLE_HOME/bin/dbca -silent -responseFile /home/oracle/database/response/dbca.rsp（6）配置静默网络$ORACLE_HOME/bin/netca -silent -responseFile /home/oracle/database/response/netca.rsp3.1.4 建库后实例检查（1）实例检查ps -ef | grep ora_ | grep -v grep（1）监听检查lsnrctl status3.2 MySQL安装3.2.1 卸载rpm包roothadoop61 # rpm -qa | grep -i mysqlmysql-5.1.73-5.el6_7.1.x86_64mysql-server-5.1.73-5.el6_7.1.x86_64perl-DBD-MySQL-4.013-3.el6.x86_64mysql-devel-5.1.73-5.el6_7.1.x86_64mysql-libs-5.1.73-5.el6_7.1.x86_64rpm -ev mysql-devel-5.1.73-5.el6_7.1.x86_64rpm -ev mysql-server-5.1.73-5.el6_7.1.x86_64rpm -ev perl-DBD-MySQL-4.013-3.el6.x86_64rpm -ev mysql-5.1.73-5.el6_7.1.x86_64roothadoop61 # rpm -e -nodeps mysql-libs-5.1.73-5.el6_7.1.x86_64warning: /etc/f saved as /etc/f.rpmsave/fmwind/article/details/387975933.2.2 收集mysql对应的文件夹roothadoop61 # find / -name mysql/var/lib/mysql/var/lib/mysql/mysql3.2.3 删除mysql对应的文件夹rm -rf /var/lib/mysql3.2.4 下载mysql安装程序3.2.5 解压mysql安装程序tar -zxvf mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz3.2.6 添加用户和组groupadd mysqluseradd -r -g mysql mysql3.2.7复制解压到本地软件目录cp -r mysql-5.6.31-linux-glibc2.5-x86_64 /usr/local/mysql3.2.8安装数据库进入安装mysql软件目录：cd /usr/local/mysql修改当前目录拥有者为mysql用户：chown -R mysql:mysql ./安装数据库：mkdir -p /data/mysql/datachown -R mysql.mysql /data/mysql#若是最小安装系统，还需要安装以下包：yum -y install perl perl-develyum -y install libaio./scripts/mysql_install_db -basedir=/usr/local/mysql -datadir=/data/mysql/data -user=mysql修改配置文件：cp /usr/local/mysql/f /etc/f#vi /etc/fmysqldbasedir=/usr/local/mysqldatadir=/data/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTIOcharacter-set-server=utf8mysqldefault-character-set=utf8clientdefault-character-set=utf8启动脚本放到开机初始化目录cp support-files/mysql.server /etc/init.d/mysql启动mysql服务：service mysql start查看mysql进程：ps -ef|grep mysql四、数据库管理4.1 数据库主机监控通过编写脚本，或使用监控软件对数据库系统的CPU、内存、可用空间进行监控，达到阀值发送邮件或短信通知相关人员。4.2 数据库日志监控管理员每天定是查看数据库日志，发现异常活动及时分析原理并进行处理。4.3 数据库表空间监控管理员每天查看数据库表空间使用情况，空间不足及时进行扩充或查找占用空间对象进行分析处理。4.4 数据库低性能SQL监控管理员每天查看运行性能低下的SQL脚本，提供优化建议，及时发送给开发人员进行修改程序。4.5数据库低性能SQL监控管理员每天查看运行性能低下的SQL脚本，提供优化建议，及时发送给开发人员进行修改程序。4.6数据库锁资源监控管理员每天定时查看数据库中的锁资源的使用情况，对于等待时间过长或锁定时间过长的锁进行分析，及时与技术和功能人员沟通了解具体业务使用情况，并对这一类锁定的会话进行分析并提供解决方案。五、数据库安全5.1数据库设计保证数据安全从数据库设计上保证数据的安全。在数据库设计初期需要考虑表设计安全。业务主表与从表间的联系均通过序列产生的ID值进行关联，不表中存储有实际含义的数据，尽量将字段值存储为自定义代码。整个系统中如果没有主表，所有从表数据均无任何意义。如果没有代码表，所有表中存储的数据的含义也不得而知。只要加强对业务主表及代码表的安全监控即可保证整个数据的安全，极大地降低了安全维护的成本。5.2数据库备份策略保证数据安全从数据库的备份策略上保证数据的安全。根据业务系统实际情况，制定出符合实际情况的可操作性的备份恢复方案。提供一套与生产环境一样的演练环境，定期对数据库进行灾难恢复演练。定期与生产环境数据进行同步，确保在要求的时间内可以对各类数据问题进行恢复。5.3 数据库代码上保证数据安全从数据库代码编写上确保核心业务逻辑安全。存储过程中的核心代码编写为动态的，一方面是操作灵活，可维护性好；同时将动态的脚本存储在表中，并对表中的数据行进行加密存储。应用程序调用时首先读取表中数据，然后对读取到的内容进行解密，最后转换成实际的脚本执行。这样即使数据泄密了，也无法取得核心业务代码。5.4 建立安全策略保证数据安全通过建立系统安全策略、数据安全策略、用户安全策略、口令管理策略、审计策来确保数据的安全。5.4.1 系统安全策略系统安全策略包括如下内容：n 数据库用户的管理n 用户的监别n 操作系统的安全（1） 数据库用户的管理数据库用户是访问Oracle数据库中信息的途径。因此应该对数据库用户进行严格的安全管理。（2） 用户的鉴别 Oracle使用数据库口令、宿主操作系统、网络服务来鉴别数据库用户。（3） 操作系统的安全对于执行Oracle和任何数据库应用的操作系统环境来说，还应该考虑如下安全问题：l 数据库管理员必须具有操作系统权限，以便创建和删除文件。l 一般数据库用户不应该具用操作系统权限，以便创建或删除小涉及数据库文件。5.4.2数据安全策略数据安全策略包括在对象层上控制和访问使用数据库的机制。数据库安全策略应该确定，哪些用户能访特殊的模式对象、允许每个用户在对象上所做的特殊的动作类型。实现数据安全的方法包括系统权限和对象权限、或通过角色实现。角色中楞以授于用户的组合在一起的一组权限。视图也能实现数据安全，因为视图的定义能限制对表中数据的访问。它们可以排除包含敏感数据的列。5.4.3用户安全策略对所有类型的数据库用户，都需要考虑如下安全问题：l 口令的安全l 权限管理（1） 口令的安全如果用户鉴别是由数据库来管理的，那么安全管理员就应该开发出一个开安策略，以维护数据库访问的安全。为了更好的保证口令的机密性，对于客户/服务器，和服务器/服务器连接来说，可以将Oracle配置成使用加密后的口令。（2） 权限管理安全管理员应该考虑所类类型数据库用户的权限管理问题。例如:在一个有许多用户名的数据库中，使用角色来管理用户可用的权限是非常有益的。六、数据库优化6.1数据库优化运维方法6.1.1 优化全表扫描性能全表扫描是指Oracle在访问目标表里的数据时，会从该表占用的第一个区（EXTENT）的第一个块（BLOCK）开始扫描，一直扫描到该表的高水位线（HWM，High Water Mark），这段范围内所有的数据块Oracle都须读到。当然，Oracle会对这期间读到的所有数据施加目标SQL的Where条件中指定的过滤条件，最后只返回那些满足条件的数据。（1）调整参数优化全表扫描初始化参数DB_FILE_MULTIBLOCK_READ_COUNT的值指定了一次最多可读取的数据块数目。在Oracle10gR2以前的版本中，DBA必须根据DB_BLOCK_SIZE参数，以及应用系统的特性，来调整DB_FILE_MULTIBLOCK_READ_COUNT参数。该参数值将影响CBO产生何种SQL执行计划。在Oracle10gR2之后的版本中，Oracle数据库已经可以根据系统的IO能力以及BUFFER CACHE的大小来动态调整该参数值。（2）并行查询优化全表扫描通过并行查询来提高全表扫描的性能。一种是使用HINT加在特定的SQL语句上，另一种是对对象设置并行度属性。前者的优点是带有一定的强制性和针对性，指定特定的SQL语句进行并行处理。该方法优点是易于控制并行度，缺点是带有很强的强制力，当数据量偏小的时候，使用并行优势不大。后者通过对象的属性指定并行度，将并行作为一种执行手段，提供给优化器进行选择。CBO会根据系统中资源的情况和数据的实际情况生成执行计划。（3）ROWID扫描优化全表扫描ROWID扫描是指Oracle在访问目标表里的数据时，直接通过数据所在的ROWID去定位并访问这些数据。ROWID表示的是Oracle中数据行记录所在的物理存储地址，也就是说ROWID实际上是和Oracle中数据块里的行记录一一对应的。对千万行数据的大表进行增删查改时，使用ROWID扫描替代全表扫描有以下好处。该方法常常在报表业务或批处理业务中使用。使用该方法有以下好处。（1）将大查询变为多个高性能小查询，可增加日志表便于跟踪进度，同时可降低业务高峰期执行全表扫描时出现“快照过旧”的错误，降低回滚段争用。高并发环境下由于降低了冲突，减少了SQL执行时的等待时间，尽而提高SQL语句性能。（2）将大事务化为一个个快速小事务可降低全表更新时资源消耗严重、锁定时间长的问题。（3）业务高峰期可减少全表扫描时与其它会话的冲突，减少热块等待事件。6.1.2 建立索引改善查询性能索引从类型上可分为B*TREE索引和BITMAP索引，前者不存储空值，后者可以存储空值。从索引列的个数上可分为单列索引和复合索引。对于单列索引，只要一条记录中索引字段值为空，那么索引不会保存这条记录信息。而对于复合索引，只要有一列值为非空，那么索引就会包含这条记录。频繁查询列上设计为非空属性，即使该列可能为空也可以为其设置一个默认值，并在列上创建索引。可有效避免空值操作导致的全表扫描，使用该列上的索引来进行高效查询。由于B*TREE索引中不存储空值，若在有空值的列上创建索引，会导致某些情况下无法使用索引。主键和唯一键自动具有索引，但应该在外键上创建索引。（1） 唯一索引当表中某列或多列组合值为唯一且非空时，应在该列或组合列上创建主键。如果表中列或列组合值具有唯一性同时可能有空值存在则在该列或组合列上可创建唯一键。在创建主键或唯一键时时系统自动创建了一个唯一索引对象，当通过键值来访问表中少量数据时，数据库首先访问索引对象来定位具体数据，然后通过指针来获取表中数据，避免进行全表扫描，从而达到提高性能的目的。当访问表中大量数据时，走索引不一定能提高查询性能。索引是否能提高查询性能受到各种因素的影响，如优化器，聚簇因子、HINT、统计信息等。（2）非唯一性索引范围扫描在表中非主键或唯一键的列上可建立普通B*TREE索引，此时如果在查询条件中引用该列或列组合作为条件，那么在访问表中少量数据时优化器会使用该列上的索引，从而提高查询效率。反向索引反向索引是在高并发环境下为了避免索引块争用，或索引块事物槽争用而采用的方法。通过反向索引把原来连续在一个索引块中的数据分布到不同的索引块中，达到降低索引块冲突，减少索引块事物槽争用的目的。降序索引降序索引是Oracle 8i里面新出现的一种索引，是B*TREE的另一个衍生物,它的变化就是列在索引中的储存方式从升序变成了降序，在某些场合下降序索引将会起作用。需要注意的地方是要设置INIT.ORA里面的COMPATIBLE参数为8.1.0或以上，否则创建时DESC关键字将被忽略。跳跃扫描索引跳跃式扫描（INDEX SKIP SCAN）适用于所有类型的复合B树索引（包括唯一性索引和非唯一性索引），它使那些在WHERE条件中没有对目标索引的前导列指定查询条件但同时又对该索引的非前导列指定了查询条件的目标SQL依然可以用上该索引，这就像是在扫描该索引时跳过了它的前导列，直接从该索引的非前导列开始扫描一样，这也是索引跳跃式扫描中跳跃（SKIP）一词的含义。全索引扫描为实现全表扫描Oracle需读取表中所有的行，并检查每一行是否满足语句的Where限制条件，一个多块读操作可以使一次I/O能读取多块数据块(DB_BLOCK_MULTIBLOCK_READ_COUNT参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，在这种访问模式下，每个数据块只被读一次。与全表扫描对应，也有相应的全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到。快速全索引扫描快速全扫描索引（INDEX FAST FULL SCAN）与全索引扫描（INDEX FULL SCAN）很类似，一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。实际上，对于一个仅仅引用索引中一部分列的查询来说，Oracle会把索引看作一个包含一些混合无用数据的很小的表(比如存储的一些rowid和无意义的分支块)。这意味着Oracle能够按照物理块顺序来读取索引段，并使用多块读取，在读取的同时拋弃分支块。（3）非唯一性索引位图索引位图索引适用于键值大量重复的列的查询,索引块的一个索引行中存储键值和起止ROWID,以及这些键值的位置编码，位置编码中的每一位表示键值对应的数据行的有无。一个位图索引块可能指向的是几十甚至成百上千行数据的位置。这种方式存储数据,相对于B*TREE索引,占用的空间非常小,创建和使用非常快。当根据键值查询时,可以根据起始ROWID和位图状态,快速定位数据。当根据键值做AND,OR或IN(X,Y)查询时,直接用索引的位图进行与或运算,快速得出结果行数据。当SELECT COUNT(XX) 时,可以直接访问索引就快速得出统计数据。函数索引函数索引支持基于用户自定义或表达式的索引，它有索引计算列的能力，它易于使用并且提供计算好的值，在不修改应用程序的逻辑上提高了查询性能。函数与表达式必须具有确定性，不正确地在函数上应用DETERNIMISTIC子句，并使用此函数创建索引，将会导致索引返回错误的结果。使用基于函数的索引有几个先决条件：必须拥有QUERY REWRITE（本模式下）或GLOBAL QUERY REWRITE（其他模式下）权限。必须使用基于成本的优化器，基于规则的优化器将被忽略。必须设置以下两个系统参数：QUERY_REWRITE_ENABLED=TRUEQUERY_REWRITE_INTEGRITY=TRUSTED(或ENFORCED)6.1.3 通过绑定变量优化查询绑定变量是非常重要的。如果业务系统在某一段时间内执行的相似的SQL语句（除条件值不一样外，其它部分都一样），这样的SQL语句会进行硬解析，每一个SQL执行时都会重新进行解析。如果这样的SQL语句在业务高峰期频繁执行，则会大量地进行硬解析，系统会出现大量与LIBRARY CACHE相关的等待事件。此时许多会话处于等待状态，同时硬解析占用大量服务器CPU和内存资源，表现在前端就是应用变慢甚至可能会出现超时错误。通过将硬解析SQL代码改为绑定变量的形式。相似的SQL语句只解析一次，其它SQL可共用该SQL第一次解析时的执行计划。这样可以消除由硬解析产生的LIBRARY CACHE相关等待事件，消除此类SQL执行时的等待时间，提高SQL语句性能。由于程序开发人员发现为查询使用字符串连接比绑定变量更加容易，没有对频繁发生的业务SQL语句没有进行绑定变量处理造成此类问题。不要依赖像CURSOR_SHARING这样的帮助，因它们会引入它们自己的问题。6.1.4 通过OWI定位性能瓶颈OWI（Oracle Wait Interface)是关注进程瓶劲的一项性能追踪方法。这种方法包括对I/O操作、锁定、锁存器、后台进程活动、网络延迟等的等待。它追踪进程在每个瓶瓶上花费的等待次数和时间，通过减少甚至消除主要瓶劲的影响而提高性能。6.1.5 通过动态SQL优化查询当编写PL/SQL程序时，如果PL/SQL块只是用于完成某些特定的功能，那么可以使用静态SQL；如果PL/SQL块需要灵活地处理各种数据操作，那么应该使用动态SQL。通过使用动态SQL技术，通过逻辑生成要执行的脚本，可以将复杂业务进行简化。有利于对SQL语句进行优化，有利用于索引技术的使用。该技术常常应用于批处理业务，如报表统计，数据清洗、数据采集等。6.1.6 通过分区优化大表查询当一张业务表数据量达到GB以上，或行数达千万级以上，且该表在业务高峰期查询频繁，满足以上条件时，需要考虑对该表进行分区。一般来说多数情况下通过时间来进行范围分区（可按月，或按天），需要根据自身业务情况决定。如果按范围分区后每个区分数据量依然很大，可以考虑在每个分区再创建子分区。表分区后，查询表中数据时，需要将分区键作为查询的第一个条件，后面再加其它条件，这样才可以定位到具体的区分进行数据检索。由于访问的是一个分区，访问数据量较全表小，因而性能得到提升。可以根据业务自身情况，为每个分区创建本地索引，或对整个分区表创建全局索引来对查询进一步进行优化。使用分区的优点：（1）增加可用性：如果表的某个分区出现故障，其它分区的数据仍然可用。（2）改善IO性能：可为不同分区指定不同表空间以分散磁盘读写操作。（3）提升查询性能：通过分区键可以定位到子分区进行查询，而不需要访问其它数据，因此性能得到提升。6.2优化查询方法查询优化器是SQL引擎的构成组件之一，用途是及时提供高效的查询计划。任何SQL语句执行时，查询优化器会产生最高效地执行计划。查询优化器分为两种:基于规则优化器(RBO)和基于成本的优化器(CBO)。RBO是Oracle通过内定的规则生成执行计划，CBO则是根据各种数据库统计信息及表、索引等的统计信息来生成执行计划。CBO比RBO更加智能，能够根据不同环境生成不同执行计划。下文介绍常见的各种优化查询的方法。6.2.1 改写NOT IN优化查询NOT IN 关键字在子查询中使用时，如果主表很大，当没有创建合适的索引，这种情况下效率将是非常低的。主表每查询一行，子查询都需要执行一次，即使创建了索引也不建议使用。NOT IN 关键字一般使用在小表查询中，Where条件不含子查询的情况。建议将NOT IN进行改写为NOT EXISTS,或改为外连接，或改为集合操作，或调整为PL/SQL块来实现。6.2.2 使用循环嵌套优化多表查询多表连接使用最多的就是循环嵌套连接。由于大多数情况都是查询表中部分数据，因此使用情况较多。在循环嵌套多表连接中，一定要选择小表为驱动表，这样可以减少循环次数，提高性能。同时要在表间连接条件列上创建索引，加快连接速度提高性能。并在选择性高的列或表达式上创建索引，确保优化器可以直接进行ACCESS方式存取数据，减少数据块访问，提高查询性能。6.2.3 复杂视图优化方法复杂视图是指在视图定义中包括了聚合函数、分析函数、用户自定义函数、多表关联、子查询等。此类视图由于在某些特定条件下优化器无法将视图外的谓词条件推送至视图内的基表上，即使在基表的列上创建索引也无法使用索引。本文对该类视图提出一种优化方案，可以显著提高该类视图的性能。表的数据量越大，查询数据越少，性能提高越是明显。6.2.4 通过HINT优化多表查询Hint是Oracle提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。特殊情况下，尤其是复杂视图查询性能低时，若通过收集最新统计信息无法使查询加快时，可以结合表自身数据，定制符合自身的执行计划，告知优化器哪些表采用什么连接方式，以及数据连接时是否采用索引等，以满足自身个性化需求。采用此种方式需要不断反复修改查询执行计划，并反复试验，观察每次执行计划情况，选择最优执行计划。该方法也有局限性，修改SQL代码必定涉及修改程序或数据库代码。若是存储过程需重新编译包。若是程序中调用则需要修改代码。建议复杂视图的查询可以改为存储过程中调用动态SQL的方式，这样不论以后SQL如何修改并不需要编译包，直接修改表中存储的SQL语句即可生效。常用HINT说明详见附录。6.2.5 通过WITH AS子句优化多表查询把重复用到的SQL语句放在WITH AS里面，取一个别名，后面的查询就可以用它，这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了。