IDC机房建设数据库文档.docx

1、IDC机房建设数据库文档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、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 数据库备

3、份策略保证数据安全 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 复杂视图

4、优化方法 266.2.4 通过HINT优化多表查询 266.2.5 通过WITH AS子句优化多表查询 276.3 慢查询定位分析 276.3.1 锁资源定位和分析 276.3.2 大事务定位及分析 286.3.3 性能低语句检测方法 28一、数据库概况本文从数据库安装、数据库设计、数据库管理、数据库优化、数据库安全等方面详细介绍了数据库的相关内容。数据库安装一章详细介绍了常用数据库的安全部署方法。数据库设计一章中介绍了如何设计一个安全、高性能、高并发的数据库。 数据库管理一章中介绍了数据库管理方面日常运维的工作内容。数据库优化一章介绍了常用的数据库优化技巧。二、数据库设计2.1 数据库设计理

5、念我们的设计理念是从优化物理存储设计、业务表间关系、业务数据存储、消除业务表冗余数据、创建合适的主键，外键，唯一性约束、查询频繁列创建索引等方面来进行数据库设计。我们的目标是建立一个高安全性、高性能、高并发的数据库设计模型。我们分别从节省存储空间、高安全性、高性能表设计、优化表间关系、节省网络流量、应用程序设计、高并发设计、满足未来数据增长多个维度对业务数据库进行设计。 2.2 数据库设计优势2.2.1 节省存储空间（1）设计方法通过分析具体业务，为表中的每一列设计满足业务要求的最小的数据类型，使存储该列占用的空间最小，每一列存储最小化，每行也将占用更少空间，使得整个表变得更小。（2）设计优势

6、 节省存储空间 节省内存空间 提升读写性读2.2.2 数据安全性高（1）设计方法业务主表中存储在业务主要信息及子表状态信息，每一个业务主表中都指派一个ID作为主键，业务子表中存储主表ID信息及子表业务信息。假使只有子表信息泄露，主表信息未泄露，由于子表中只存有主表ID信息，无法知道子表中信息的含义，因此无主表数据子表的数据没有任何意义。我们只需要对业务主表进行安全监控就可以。大大降低了系统数据安全监控的成本。（2）设计优势 减少数据冗余 提高数据安全 简化数据管理2.2.3 高性能表设计（1）设计方法表中只存储代码，这在存储设计时就已经考虑到了。设计系统代码表用于存储各类代码与名称映射关系，减

7、少每行数据大小。业务表中尽量少存储中文或不存储中文。这样一来表中基本上存储的都是数字或字符编码，存储相同数据占用更少的空间。查询时只需要将代码映射为名称即可，代码与名称映射都是通过主键进行返回，性能不是问题。（2）设计优势 节省存储空间 降低网络带宽 提高查询性能 减少数据冗余2.2.4 优化表间关系（1）设计方法通过对业务分析，对各类相关联信息进行主从表设计，主表上除存储共性数据外，还存储子表各类状态信息。子表只存储与主表的主键及子表相关业务数据。在子表上创建外键，并在外键上创建索引。（2）设计优势 查询相关数据从主表获取，尽可能避免表关联。 外链上增加索引从而提升主从表关联性能2.2.5

8、避免复杂查询（1）设计方法业务数据从主表可获取，若查询用到从表数据，可通过外键关联获取。这样设计可使得相同类型数据出口只有一张表，只有个性化数据可从从表获取。主从表设计避免复杂查询。（2）设计优势 避免使用复杂查询而性能下降。 降低程序开发难度，简化查询语句。 便于系统日常运维。 减少数据冗余2.2.6 节省网络流量（1）设计方法表的字段设计为满足业务需要的最小数据类型，表中尽可能地存储代码值。使得全表扫描及索引扫描的性能大大幅提高。同时在业务层面上使得查询结果集变得更小，占用网络带宽小，使得业务访问速试快，同时相同的网络环境可以支持更多的应用会话。（2）设计优势 节省网络流量 高并发会话支持

9、 高并发业务支持 高性能查询2.2.7 应用程序访问（1）设计方法不同业务不同属性根据业务实际情况进行非空属性设计, 可确保最高效地使用索引来优化查询。通过在的常用列上创建索引可提高常用列访问数据的性能。 表名设计尽可能简单，方便记忆，便于开发及后期运维。 （2）设计优势 避免空值操作提升查询性能 简化开发代码编写 简化日常运维操作 减少业务锁定时间 提升系统并发能力2.2.8 高并发设计（1）设计方法通过把表设计为最小存储，使得创建的索引也将变小。通过在表上常用列创建索引，使得查询数据时尽可能使用索引来获取数据，磁盘读减少，内存占用率降低，从而提高查询性能。查询性能提高了，进行更新、删除、插

10、入操作时锁定时间将减少，相同时间内可支持更多并发会话访问共享资源。（2）设计优势 减少业务锁定时间 提升系统并发能力 提高查询性能2.2.9未来数据增长（1）表被设计为尽可能小，数据行尽可能小，可以确保数据库数据增长量变小，可以满足未来多年的业务增长需要。节省增加存储的开销。（2）子表中去掉了容余信息，使得子表变得比主表要小得多。（3）假使原来每天100M数据增长量，优化后的数据库可能占用不到1M，甚至几K。大大节省存储空间。（4）既使未来几年内表中数据量增长到千万级别，由于表小。则全表扫描性能也不会差。若性能出现问题，则可以通过分区表来解决。（5）查询单位或个人信息只从单位主表或个人主表查询

11、，基本表中记录了所有单位个人信息或子表状态信息，尽可能满足各种查询需要。最大程序地避免了表关联的代价。既使需要关联，由于在主键、外键上都有索引且表和索引都很小，且常用列已创建了索引，因此性能也会非常好。（6） 由于数据增长量减小，未来三五年内数据库的性能依然表现非常好，可减少采购存储的开销。除非并发量极聚增长，磁盘读写是性能瓶劲时只能更換转速更快的磁盘。CPU和内存在未来几年内不需要更換，因为数据行小，计算量小,同时内存占用量也小。使得相同大小的内存可以容纳更多业务的数据。三、数据库安装3.1 Oracle11g安装Oracle数据库版本：Oracle11.2.0.83.1.1 操作系统参数修

12、改（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键进入编辑模式，

13、将下列内容加入该文件。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

14、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

15、 显示如下：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.rm

16、em_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

17、 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）创建数据库软件目录 创建数据库软件目录和数据文件

18、存放目录，目录的位置，根据自己的情况来定，注意磁盘空间即可，这里我把其放到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,进入编辑模式，增加以下内容：expo

19、rt 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用户登陆。首先将下载的Oracl

20、e安装包复制到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_LOCAT

21、ION=/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.st

22、arterdb.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.c

23、onfig.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.

24、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.

25、recoveryLocation=MYORACLESUPPORT_USERNAME=zhdn_791005MYORACLESUPPORT_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）编辑静默响应文件（

26、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/

27、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

28、_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_6

29、4warning: /etc/f saved as /etc/f.rpmsave3.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 mys

30、ql3.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/my.