IDC机房建设数据库文档.docx
《IDC机房建设数据库文档.docx》由会员分享，可在线阅读，更多相关《IDC机房建设数据库文档.docx（24页珍藏版）》请在冰豆网上搜索。
IDC机房建设数据库文档
IDC机房建设数据库文档
马飞
2016-11-16~17
目录
一、数据库概况3
二、数据库设计4
2.1数据库设计理念4
2.2数据库设计优势4
2.2.1节省存储空间4
2.2.2数据安全性高4
2.2.3高性能表设计5
2.2.4优化表间关系5
2.2.5避免复杂查询6
2.2.6节省网络流量6
2.2.7应用程序访问6
2.2.8高并发设计7
2.2.9未来数据增长7
三、数据库安装8
3.1Oracle11g安装8
3.1.1操作系统参数修改8
3.1.2数据库安装准备10
3.1.3数据库安装过程11
3.1.4建库后实例检查13
3.2MySQL安装13
3.2.1卸载rpm包13
3.2.2收集mysql对应的文件夹13
3.2.3删除mysql对应的文件夹14
3.2.4下载mysql安装程序14
3.2.5解压mysql安装程序14
3.2.6添加用户和组14
3.2.7复制解压到本地软件目录14
3.2.8安装数据库14
四、数据库管理15
4.1数据库主机监控15
4.2数据库日志监控15
4.3数据库表空间监控16
4.4数据库低性能SQL监控16
4.5数据库低性能SQL监控16
4.6数据库锁资源监控16
五、数据库安全16
5.1数据库设计保证数据安全16
5.2数据库备份策略保证数据安全17
5.3数据库代码上保证数据安全17
5.4建立安全策略保证数据安全17
5.4.1系统安全策略17
5.4.2数据安全策略18
5.4.3用户安全策略18
六、数据库优化19
6.1数据库优化运维方法19
6.1.1优化全表扫描性能19
6.1.2建立索引改善查询性能20
6.1.3通过绑定变量优化查询23
6.1.4通过OWI定位性能瓶颈24
6.1.5通过动态SQL优化查询24
6.1.6通过分区优化大表查询24
6.2优化查询方法25
6.2.1改写NOTIN优化查询25
6.2.2使用循环嵌套优化多表查询26
6.2.3复杂视图优化方法26
6.2.4通过HINT优化多表查询26
6.2.5通过WITHAS子句优化多表查询27
6.3慢查询定位分析27
6.3.1锁资源定位和分析27
6.3.2大事务定位及分析28
6.3.3性能低语句检测方法28
一、数据库概况
本文从数据库安装、数据库设计、数据库管理、数据库优化、数据库安全等方面详细介绍了数据库的相关内容。
数据库安装一章详细介绍了常用数据库的安全部署方法。
数据库设计一章中介绍了如何设计一个安全、高性能、高并发的数据库。
数据库管理一章中介绍了数据库管理方面日常运维的工作内容。
数据库优化一章介绍了常用的数据库优化技巧。
二、数据库设计
2.1数据库设计理念
我们的设计理念是从优化物理存储设计、业务表间关系、业务数据存储、消除业务表冗余数据、创建合适的主键，外键，唯一性约束、查询频繁列创建索引等方面来进行数据库设计。
我们的目标是建立一个高安全性、高性能、高并发的数据库设计模型。
我们分别从节省存储空间、高安全性、高性能表设计、优化表间关系、节省网络流量、应用程序设计、高并发设计、满足未来数据增长多个维度对业务数据库进行设计。
2.2数据库设计优势
2.2.1节省存储空间
（1）设计方法
通过分析具体业务，为表中的每一列设计满足业务要求的最小的数据类型，使存储该列占用的空间最小，每一列存储最小化，每行也将占用更少空间，使得整个表变得更小。
（2）设计优势
⏹节省存储空间
⏹节省内存空间
⏹提升读写性读
2.2.2数据安全性高
（1）设计方法
业务主表中存储在业务主要信息及子表状态信息，每一个业务主表中都指派一个ID作为主键，业务子表中存储主表ID信息及子表业务信息。
假使只有子表信息泄露，主表信息未泄露，由于子表中只存有主表ID信息，无法知道子表中信息的含义，因此无主表数据子表的数据没有任何意义。
我们只需要对业务主表进行安全监控就可以。
大大降低了系统数据安全监控的成本。
（2）设计优势
⏹减少数据冗余
⏹提高数据安全
⏹简化数据管理
2.2.3高性能表设计
（1）设计方法
表中只存储代码，这在存储设计时就已经考虑到了。
设计系统代码表用于存储各类代码与名称映射关系，减少每行数据大小。
业务表中尽量少存储中文或不存储中文。
这样一来表中基本上存储的都是数字或字符编码，存储相同数据占用更少的空间。
查询时只需要将代码映射为名称即可，代码与名称映射都是通过主键进行返回，性能不是问题。
（2）设计优势
⏹节省存储空间
⏹降低网络带宽
⏹提高查询性能
⏹减少数据冗余
2.2.4优化表间关系
（1）设计方法
通过对业务分析，对各类相关联信息进行主从表设计，主表上除存储共性数据外，还存储子表各类状态信息。
子表只存储与主表的主键及子表相关业务数据。
在子表上创建外键，并在外键上创建索引。
（2）设计优势
⏹查询相关数据从主表获取，尽可能避免表关联。
⏹外链上增加索引从而提升主从表关联性能
2.2.5避免复杂查询
（1）设计方法
业务数据从主表可获取，若查询用到从表数据，可通过外键关联获取。
这样设计可使得相同类型数据出口只有一张表，只有个性化数据可从从表获取。
主从表设计避免复杂查询。
（2）设计优势
⏹避免使用复杂查询而性能下降。
⏹降低程序开发难度，简化查询语句。
⏹便于系统日常运维。
⏹减少数据冗余
2.2.6节省网络流量
（1）设计方法
表的字段设计为满足业务需要的最小数据类型，表中尽可能地存储代码值。
使得全表扫描及索引扫描的性能大大幅提高。
同时在业务层面上使得查询结果集变得更小，占用网络带宽小，使得业务访问速试快，同时相同的网络环境可以支持更多的应用会话。
（2）设计优势
⏹节省网络流量
⏹高并发会话支持
⏹高并发业务支持
⏹高性能查询
2.2.7应用程序访问
（1）设计方法
不同业务不同属性根据业务实际情况进行非空属性设计,可确保最高效地使用索引来优化查询。
通过在的常用列上创建索引可提高常用列访问数据的性能。
表名设计尽可能简单，方便记忆，便于开发及后期运维。
（2）设计优势
⏹避免空值操作提升查询性能
⏹简化开发代码编写
⏹简化日常运维操作
⏹减少业务锁定时间
⏹提升系统并发能力
2.2.8高并发设计
（1）设计方法
通过把表设计为最小存储，使得创建的索引也将变小。
通过在表上常用列创建索引，使得查询数据时尽可能使用索引来获取数据，磁盘读减少，内存占用率降低，从而提高查询性能。
查询性能提高了，进行更新、删除、插入操作时锁定时间将减少，相同时间内可支持更多并发会话访问共享资源。
（2）设计优势
⏹减少业务锁定时间
⏹提升系统并发能力
⏹提高查询性能
2.2.9未来数据增长
（1）表被设计为尽可能小，数据行尽可能小，可以确保数据库数据增长量变小，可以满足未来多年的业务增长需要。
节省增加存储的开销。
（2）子表中去掉了容余信息，使得子表变得比主表要小得多。
（3）假使原来每天100M数据增长量，优化后的数据库可能占用不到1M，甚至几K。
大大节省存储空间。
（4）既使未来几年内表中数据量增长到千万级别，由于表小。
则全表扫描性能也不会差。
若性能出现问题，则可以通过分区表来解决。
（5）查询单位或个人信息只从单位主表或个人主表查询，基本表中记录了所有单位个人信息或子表状态信息，尽可能满足各种查询需要。
最大程序地避免了表关联的代价。
既使需要关联，由于在主键、外键上都有索引且表和索引都很小，且常用列已创建了索引，因此性能也会非常好。
（6）由于数据增长量减小，未来三五年内数据库的性能依然表现非常好，可减少采购存储的开销。
除非并发量极聚增长，磁盘读写是性能瓶劲时只能更換转速更快的磁盘。
CPU和内存在未来几年内不需要更換，因为数据行小，计算量小,同时内存占用量也小。
使得相同大小的内存可以容纳更多业务的数据。
三、数据库安装
3.1Oracle11g安装
Oracle数据库版本：
Oracle11.2.0.8
3.1.1操作系统参数修改
（1）limits.conf文件修改
修改用户的SHELL的限制，修改/etc/security/limits.conf文件
输入命令：
vi/etc/security/limits.conf，按i键进入编辑模式，将下列内容加入该文件。
oraclesoftnproc2047
oraclehardnproc16384
oraclesoftnofile1024
oraclehardnofile65536
编辑完成后按Esc键，输入“:
wq”存盘退出
（2）login文件修改
修改/etc/pam.d/login文件，输入命令：
vi/etc/pam.d/login，按i键进入编辑模式，
将下列内容加入该文件。
sessionrequired/lib/security/pam_limits.so
sessionrequiredpam_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=25032000100128
net.ipv4.ip_local_port_range=900065500
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=25032000100128
net.ipv4.ip_local_port_range=900065500
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-p16384
ulimit-n65536
else
ulimit-u16384-n65536
fi
fi
编辑完成后按Esc键，输入“:
wq”存盘退出。
3.1.2数据库安装准备
（1）创建用户和组
创建相关用户和组，作为软件安装和支持组的拥有者。
创建Oracle用户和密码,输入命令：
groupaddoinstall
groupadddba
useradd-goinstall-gdba-moracle
passwdoracle
然后会让你输入密码，密码任意输入2次，但必须保持一致，回车确认。
检查用户所在组是不正确
groupsoracle
如果不正确，则进行修改
usermod-goinstall-Gdbaoracle
（2）创建数据库软件目录
创建数据库软件目录和数据文件存放目录，目录的位置，根据自己的情况来定，注意磁盘空间即可，这里我把其放到oracle用户下,例如：
输入命令：
mkdir-p/opt/app/oradata
mkdir-p/opt/app/oracle/product
（3）更改目录属主
更改目录属主为Oracle用户所有，输入命令：
chown-Roracle:
oinstall/opt/app
（4）配置环境变量
配置oracle用户的环境变量，首先，切换到新创建的oracle用户下,
输入：
su–oracle，然后直接在输入：
vi.bash_profile
按i编辑.bash_profile,进入编辑模式，增加以下内容：
exportORACLE_BASE=/home/oracle/app
exportORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/db_1
exportORACLE_SID=orcl
exportPATH=$PATH:
$HOME/bin:
$ORACLE_HOME/bin
exportLD_LIBRARY_PATH=$ORACLE_HOME/lib:
/usr/lib
编辑完成后按Esc键，输入“:
wq”存盘退出
3.1.3数据库安装过程
（1）解压安装程序
当上述系统要求操作全部完成后，注销系统，在图形界面以Oracle用户登陆。
首先将下载的Oracle安装包复制到linux中，用SSH其他ftp工具拷贝。
打开一个终端，运行unzip命令解压oracle安装文件，如：
输入命令：
unziplinux.x64_11gR2_database_1of2.zip
unziplinux.x64_11gR2_database_2of2.zip
解压完成后cd进入其解压后的目录database
输入命令：
cddatabase
（2）编辑静默响应文件（db_install.rsp）
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_HOSTNAME=
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/opt/app/oraInventory
SELECTED_LANGUAGES=en,zh_CN,zh_TW
ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/opt/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=true
oracle.install.db.DBA_GROUP=oinstall
oracle.install.db.OPER_GROUP=
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.globalDBName=orcl
oracle.install.db.config.starterdb.SID=orcl
oracle.install.db.config.starterdb.characterSet=AL32UTF8
oracle.install.db.config.starterdb.memoryOption=true
oracle.install.db.config.starterdb.memoryLimit=512
oracle.install.db.config.starterdb.installExampleSchemas=true
oracle.install.db.config.starterdb.enableSecuritySettings=true
oracle.install.db.config.starterdb.password.ALL=jusfoun
oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.automatedBackup.enable=false
oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/opt/app/oradata
oracle.install.db.config.starterdb.storage=FILE_SYSTEM_STORAGE
oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=
MYORACLESUPPORT_USERNAME=zhdn_791005@
MYORACLESUPPORT_PASSWORD=MAFEIcnnui791005
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
（3）静默安装数据库软件
su-oracle
cddatabase
./runInstaller-silent-force-responseFile/home/oracle/database/response/db_install.rsp
（4）编辑静默响应文件（dbca.rsp）
修改仅安装数据库的响应文件/home/oracle/etc/dbca.rsp
GDBNAME="orcl.java-linux-test"//78行全局数据库的名字=SID+主机域名
SID="orcl"//149行SID
CHARACTERSET="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.rsp
3.1.4建库后实例检查
（1）实例检查
ps-ef|grepora_|grep-vgrep
（1）监听检查
lsnrctlstatus
3.2MySQL安装
3.2.1卸载rpm包
[root@hadoop61~]#rpm-qa|grep-imysql
mysql-5.1.73-5.el6_7.1.x86_64
mysql-server-5.1.73-5.el6_7.1.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-devel-5.1.73-5.el6_7.1.x86_64
mysql-libs-5.1.73-5.el6_7.1.x86_64
rpm-evmysql-devel-5.1.73-5.el6_7.1.x86_64
rpm-evmysql-server-5.1.73-5.el6_7.1.x86_64
rpm-evperl-DBD-MySQL-4.013-3.el6.x86_64
rpm-evmysql-5.1.73-5.el6_7.1.x86_64
[root@hadoop61~]#rpm-e--nodepsmysql-libs-5.1.73-5.el6_7.1.x86_64
warning:
/etc/fsavedas/etc/f.rpmsave
3.2.2收集mysql对应的文件夹
[root@hadoop61~]#find/-namemysql
/var/lib/mysql
/var/lib/mysql/mysql
3.2.3删除mysql对应的文件夹
rm-rf/var/lib/mysql
3.2.4下载mysql安装程序
3.2.5解压mysql安装程序
tar-zxvfmysql-5.6.31-linux-glibc2.5-x86_64.tar.gz
3.2.6添加用户和组
groupaddmysql
useradd-r-gmysqlmysql
3.2.7复制解压到本地软件目录
cp-rmysql-5.6.31-linux-glibc2.5-x86_64/usr/local/mysql
3.2.8安装数据库
进入安装mysql软件目录：
cd/usr/local/mysql
修改当前目录拥有者为mysql用户：
chown-Rmysql:
mysql./
安装数据库：
mkdir-p/data/mysql/data
chown-Rmysql.mysql/data/mysql
#若是最小安装系统，还需要安装以下包：
yum-yinstallperlperl-devel
yum-yinstalllibaio
./scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/mysql/data--user=mysql
修改配置文件：
cp/usr/local/mysql/f/etc/f
#vi/etc/my.
升级会员 