数据库优化

数据库优化

 

1.主要性能瓶颈
 A.磁盘I/O
 B.内存
 C.网络占用
 B.CPU处理

2.造成瓶颈的可能性有哪些
 连接次数
 磁盘读取量
 磁盘写入量
 传输数据块大小
 存储量大小
 响应时间快慢

3.行业需求主要有哪些（一般情况下）
 门户系统——面向大众，连接次数很多，存储量很小，读很频密，写的可能性低，响应时间要求高
 博客系统——面向大众，连接次数较多，存储量较小，读较频密，写入量中等，传输数据块大，响应时间要求一般
 财务系统——内部用较多，连接次数少，传输数据大，读写周期长，响应时间要求一般
 
4.数据库类型差异
 关系型数据库
 XML数据库

5.什么情况容易引发数据库性能故障
 数据库逻辑结构不合理
 索引设计不合理
 数据计算冲突
 连接次数过多
 读取数据块过大

SQL优化——
通用优化手段——
{
查询目标集尽可能的小
避免排序和尽可能少排序，大数据量排序用临时表分段去排，内存操作比硬盘操作快多了

}

索引——合理的索引设计建立在对各种查询的分析和预测上
{
索引定义：
 索引是表数据抽象出来的隐藏表，无法直接使用，但在操作定义了索引的字段的时候自动使用这张隐藏表来加速操作。

索引类型：
 1.BTree索引，BTree根据内容将数据划分为集合和子集来工作，适合含有大量不同值的字段，如ID、Name
 2.位图索引，位图索引的排序方式对于只包含很少不同值的字段更为有效，适合只包含很少不同值的字段，如性别、是或非

索引的优点：
 1.当然是速度，大大地加快检索速度
 2.加快表之间的连接速度
 3.减少分组和排序时间
 4.使用优化隐藏器提高系统性能
 5.唯一性索引（unique）可确保数据的唯一性

索引的缺点：
 1.索引会影响增删改的速度，当数据库进行增删改操作时，索引也要进行动态维护，如果增删改的需求比较大，那么最好减  少索引，否则在大数据量情况下，动态维护索引占用的时间将会很多。
 2.建立索引就像给数据加了一个唯一编号字段（当然没那么简单），也是要占用物理存储空间的，聚集索引占用的更大（表  副本 + 索引页 约等于表大小的120%），如果索引建立太多将会使数据库变得庞大。
 3.索引将在实际使用中由于索引变更而在索引页产生索引碎片和排列逻辑混乱，需要定期整理索引

索引的创建规则：
 1.常用查询字段创建索引，如主键、外键，但当主键是ID并且该ID非其它表的外键时，最好将它只设置为唯一非聚集索引，  而将使用比较多的查询字段，例如Name之类 设置为聚集索引
 2.尽量选择使用可能最频密的列为组合索引的前导列，使关键查询形成索引覆盖，而经常同时存取多列，且每列都含有重复  值则需要考虑建立组合索引
 3.经常需要范围搜索（Between、>、<、>=、<=）的字段创建聚簇索引，如绩效统计系统的上下班时段计算
 4.经常需要排序（Order By、Group By）的字段创建聚簇索引，如绩效统计系统的业绩排序
 5.进行多表连接查询的时候尽量使用带有索引的表
 6.避免在where条件存在对字段的类型转换、运算等操作语句，where条件对字段操作的话，SQL将无法使用索引，整个语句的  搜索都将用表搜索完成
 7.in和or子句常会使工作表、索引失去应有效果，如果不会产生大量重复值的话，可以考虑稍微牺牲一点内存，将子句拆开  ，拆开的子句尽量去使用索引来提高效率
 8.如果SQL使用频率很高，又无法很好地使用索引，那么最好将SQL做成存储过程来提高效率
 非查询关键字段远离索引，如员工表的“住址”之类
 9.数据量很少的字段远离索引，如中小型公司的“部门名称”，但是得考虑查询的可能性，比较低的话，使用其主键与查询  高的表做个关联就够了
 10.数据类型为text、image、bit之类大数据集的字段尽量不建立索引，占用数据库空间太大，效果也不明显
 11.当增删改性能需求大于查询需求的时候不建立索引，索引对增删改的性能影响很大

解决办法：
 1.数据库分离成查询专用数据库和修改专用数据库，两个数据库分别进行优化处理。就是用硬件或空间来交换效率，相对硬  件资源、网络资源的要求就会提升。
 2.如果硬件无法提升，空间和网络资源有余（需要控制硬件支出），那么将查询操作尽量集中到一个查询专用关联表，整表  都是主键和外键，专用于优化查询，牺牲部分硬盘空间换取一定程序的高效。

优化方法：
 1.设计数据库的时候根据系统的检索要求充分考虑索引覆盖
 2.如果数据库支持，使用索引优化向导
 3.定期整理索引碎片，甚至重建索引

索引的使用和操作：
 建立索引
 CREATE INDEX <索引名> on <表名>(<字段名>)——index默认为BTree索引
 CREATE INDEX <索引名> on <表名>(<字段名>,<字段名>)——建立BTree类型组合索引
 CREATE bitmap INDEX <索引名> on <表名>(<字段名>)——建立位图索引
 CREATE UNIQUE INDEX <索引名>

 删除索引
 DROP INDEX <表名或视图名>.<索引名>——SQL Server
 DROP INDEX <索引名>——Oracle和DB2
 ALTER TABLE <表名> DROP INDEX <索引名>——MySQL

 sp_helpindex <表名>——查看索引信息
 sp_rename
 dbcc showcontig——显示表信息（包括索引碎片情况）
 dbcc dbreindex——重建指定数据库中一个或多个索引
 dbcc indexdefrag——整理指定表或视图的聚集索引或辅助索引的碎片
 
}

视图——
{
视图定义：
 视图是由一张或多张表数据组合而成的虚拟表，对于数据库本身来说，它是内置的SQL语句。它对数据库外界来说跟实际的表 没区别，可以直接进行操作，多用来简化常用组合查询。也有为了安全性考虑，为了隐藏部分数据而专门做视图的情况。

视图的优点：
 1.数据可自由定制，可以针对最常用数据集中查询
 2.简化操作，视图本身就是一个查询结果，复杂查询可以由视图完成，对视图本身的简单操作就可以得到复杂查询的结果
 3.当数据库变更或者应用程序逻辑发生了变更，可以使用视图对两者的变化进行兼容，保持各自独立性，减低变更成本
 4.可以按使用者权限创建一个专用视图，可以将使用者和底层数据分离开来，增强数据库安全性

视图的使用和操作：
 创建视图
 CREATE VIEW <视图名> AS
  SELECT <字段名>,<字段名> FROM <表名>,<表名> WHERE <条件>

 修改视图
 ALERT VIEW <视图名> AS
  SELECT <字段名>,<字段名> FROM <表名>,<表名> WHERE <条件>

 复杂视图之排序
 CREATE VIEW <视图名> AS
  SELECT <字段名>,<字段名> FROM <表名>,<表名> WHERE <条件> ORDER BY <条件> <ASC|DESC>

 调用系统存储过程更改视图名
 SP_RENAME VIEW <旧视图名>,<新视图名>

 查看视图信息
 SP_HELP
 SP_HELPTEXT
 SP_DEPENDS

 删除视图
 DROP VIEW <视图A>,<视图B>

视图可选参数：
 加密——WITH ENCRYPTION（对你的视图查询语句进行加密，以保证数据库只有你自己能操作）
 CREATE VIEW <视图名> AS WITH ENCRYPTION SELECT <字段名>,<字段名> FROM <表名>,<表名>

 条件检查——WITH CHECK OPTION（将根据WHERE条件子句来判断选择，不符合数据的修改将被拒绝）
 CREATE VIEW <视图名> AS SELECT <字段名>,<字段名> FROM <表名>,<表名> WHERE <条件> WITH CHECK OPTION

}

存储过程——
{
存储过程定义：
 顾名思义就是存储在数据库里面的操作过程——的定义，也可以认为是被数据库自身所接受（编译并优化）的SQL

存储过程的优点：
 1.快速，它是被数据库已编译且优化过的
 2.灵活，它更像程序而不是SQL语句，可以完成复杂的判断运算
 3.降低网络资源占用，远程应用服务器只要调用一下存储过程就可以完成复杂动作，减少了复杂SQL语句的传输
 4.应用服务和数据库之间的缓冲，部分运算规则可以做成存储过程，当数据库改动或者应用程序更改可以将两者分离开

存储过程的分类：
 1.以“SP_”开头的是系统存储过程，用来进行系统的设定或者信息查看
  例如：SP_HELP、SP_HELPTEXT、SP_HELPINDEX
 2.以“XP_”开头的是扩展存储过程，用来调用操作系统提供的功能
  例如：XP_CMDSHELL 'ping 192.168.0.1'
 3.用户自己定义的存储过程，这才是我们所说的存储过程

存储过程的使用：
 创建存储过程
 CREATE PROCEDURE [<拥有者>.]<存储过程名> [参数1....参数1024]
 [WITE <RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION>]
 [FOR REPLICATION]
 AS 程序行
 
 样例：
 出版商图书管理系统：
 需求1——指定使用人为作者（author），而不是出版商（publisher）
 需求2——存储过程加密
 需求3——订单履历表.以往卖出数量 + 订单记录表.本月卖出数量 = 返回值：销售总量
 需求4——书表.单价 × 销售总量 = 返回值：总销售额
 需求5——书表.成本 × 销售总量 = 返回值：总成本
 需求6——总销售额 - 总成本 = 返回值：纯利润
 需求7——纯利润 × 合约表.作者利润比例 = 返回值：作者收益
 
 Oracle写法——
 CREATE PROCEDURE author.p_book_profit
  @b_id int
  @b_sale_count int output
  @b_total_sales money output
  @b_total_cost money output
  @b_total_profit money output
  @b_author_profit money output
 WITH ENCRYPTION
 AS
  SELECT
   @b_sale_count,
   @b_total_sales=(price * @b_sale_count),
   @b_total_cost=(cost * @b_sale_count),
   @b_total_profit=(@b_total_sales-@b_total_cost)
   @b_author_profit=(@b_total_profit * per)
  FROM
   (SELECT @b_sale_count=(COUNT(o_r.*) + COUNT(o_n.*))
   FROM order_recode o_r,order_now o_n
   WHERE @b_id = o_n.bookid and o_n.bookid = o_r.bookid),
   (SELECT books.price price, books.cost cost
   FROM books
   WHERE @b_id=books.bookid),
   (SELECT pactum.profit_author_per per
   FROM pactum
   WHERE @b_id=pactum.bookid)
 
 SQLServer写法——
}

 

表分区

{

}

 

 

 

Oracle优化——

{

nologging

注释优化

}

 

未完成......