数据库三级(2.运维优化、安全管理)

一.数据库性能优化

1.硬件层面

文件存储位置

将数据文件和索引文件放置在不同磁盘上，可以同时利用不同磁盘的IO

调整内存分配

调整磁盘IO

每次IO最小化

调整竞争

控制连接数

减少访问文件的竞争

增加CPU

如果数据库服务器中运行队列数量超过逻辑CPU数量说明CPU已经饱和

RAID

可以考虑将RAID1改为RAID5

分布式数据库

集中式数据库的查询处理中，主要代价是CPU代价和I/O代价。在分布式数据库系统中，由于数据分布在多个不同的场地上，需要在各站点中传输数据，所以通信代价尤为关键。

2.应用层面

存储过程

分割表

水平分割是根据数据行的使用特点进行分割，分割之后所得的所有表的结构都相同，而存储的数据不同。水平分割会给应用增加复杂度，特别在查询所有数据时需要Union（并）操作；大部分情况是局部使用，只有很少情况才全局查询时适合使用此方法。而垂直分割是根据列的特点进行分割，分割之后所得的所有表中除了都含有主码列外其余列都不同，其查询时会减少I/O次数，但其缺点是查询所有数据时需要Join (连接）操作。

重组重构

• 数据库性能优化会涉及到数据库的重组和重构。数据库的重组并不修改数据库原有设计的逻辑结构和物理结构
• 而数据库的重构则不同，它是指部分修改数据库的模式和内模式。

冗余列：去规范化

它的作用是在查询时减少连接操作，避免使用聚合函数。由于增加了派生性冗余字段，破坏了规范化规则。

派生性冗余列

增加派生列指增加的列来自其它表中的数据，由它们计算生成。可以减少聚合函数的使用

索引重建

在大量更新带索引的字段之后， 除了删除或创建索引，以及非聚簇索引和聚簇索引的相互转换外，通过重建索引可以改进系统的性能。无需重启数据库

如果在索引码上执行大量删除操作，索引页可能会出现空间的浪费，而这些被浪费的空间需要在重建索引后才能提高数据库的性能。

新增汇总表

将频繁使用的统计操作的中间结果或最终结果存储在汇总表中，当用户发出汇总需求时，即可直接从汇总表中获取数据，降低了数据访问量以及汇总操作的CPU计算量。
同时也可以把这个结果放在缓存如Redis中

物化视图

物化视图是包括一个查询结果的数据库对象。物化视图是预先计算并保存表连接或聚集等耗时较多的操作结果。这样在查询时大大提高了读取速度，特别适用于多个数据量较大的表进行连接操作及分布式数据库中需要进行分布在多站点的表进行连接操作时使用。

3.索引优化

建立索引的目的是为了提高查询效率，但是在“写”操作（插入、更新、删除）时索引的存在会增加系统开销降低数据库性能

应用系统层面的SQL优化

散列索引

二.运维

维护工作主要包括：数据库的转储和恢复；数据库的安全性和完整性控制；数据库性能的监控分析和改进；数据库的重组和重构。

监控

根据监控对象的不同，监控分析可以分为两种：
（1）对数据库架构体系的监控
主要监控内容：空间基本信息、空间使用率与剩余空间大小、空间是否具有自动扩展的能力、哪些表的扩展将引起空间的扩展，以及段的占用空间与区间数等。
（2）对数据库性能的监控
主要监控内容：数据缓冲区的命中率、库缓冲、用户锁、锁与等待、回滚段、临时段使用情况、索引的使用情况、等待事件和共享池等。
用户登录次数及执行的语句不属于监控内容，

三.安全管理

1.用户

guest数据库用户

guest是SQL Server中一个特殊的数据库用户。主要用于提供匿名访问。可以给guest进行授权
除guest用户外，所有的数据库用户都必须对应一个登录账户

sa管理员

最高权限，可以给其他用户授权

1.2角色

角色是一组具有相同权限的用户集合。用户定义的角色属于数据库一级的角色，用户定义的角色的成员可以是数据库的用户，也可以是用户定义的角色。只要权限没有被拒绝过，则角色中成员的权限就是其所在角色的权限加上他们自己所具有的权限。
角色包含多个成员、数据库可以有多个角色、可以为角色进行授权

2.授权GRANT

无条件单更新

设SQL Server 2008某数据库中有表T1(c1, c2, c3)，并仅对U1执行了如下授权语句：
GRANT UPDATE ON T1 TO U1
现有下列数据操作语句：
Ⅰ. UPDATE T1 SET c1 = 100
Ⅱ. UPDATE T1 SET c1 = c1 + 100
Ⅲ. UPDATE T1 SET c1 = c3 + c1
Ⅳ. UPDATE T1 SET c1 = 100 WHERE c2 = ‘A’
上述语句中，U1有权执行的是 A 。

只授权了UPDATE，不能带WHERE条件，除非授权SELECT

2.1拒绝DENY

同上

2.2收权（撤回）REVOKE

3.建立权限CREATE（语句级别）

在SQL Server 2008中，若要授予用户U1在DB1数据库中具有建表权限，下列语句正确的是 GRANT CREATE TABLE TO U1 无需指定DB1

可以创建表 视图 数据库等，只需要指定用户

4.身份验证

SQL Server 2008的身份验证模式有"Windows身份验证模式"和"混合身份验证模式"两种。混合身份验证模式是指SQL Server允许Windows授权用户和SQL授权用户登录到SQL Server数据库服务器。