数据库优化(数据库自身的优化，数据库表优化，程序操作优化)

数据库优化(数据库自身的优化，数据库表优化，程序操作优化)

一、增加次数据文件，设置文件自动增长（粗略数据分区）

1. 增加次数据文件

从SQL SERVER
2005开始，数据库不默认生成NDF数据文件，一般情况下有一个主数据文件（MDF）就够了，但是有些大型的数据库，由于信息很多，而且查询频繁，所以为了提高查询速度，可以把一些表或者一些表中的部分记录分开存储在不同的数据文件里
由于CPU和内存的速度远大于硬盘的读写速度，所以可以把不同的数据文件放在不同的物理硬盘里，这样执行查询的时候，就可以让多个硬盘同时进行查询，以充分利用CPU和内存的性能，提高查询速度。
在这里详细介绍一下其写入的原理，数据文件（MDF、NDF）和日志文件（LDF）的写入方式是不一样的：

数据文件：SQL
Server按照同一个文件组里面的所有文件现有空闲空间的大小，按这个比例把新的数据分布到所有有空间的数据文件里，如果有三个数据文件A.MDF，B.NDF，C.NDF，空闲大小分别为200mb，100mb，和50mb，那么写入一个70mb的东西，他就会向ABC三个文件中一次写入40、20、10的数据，如果某个日志文件已满，就不会向其写入

日志文件：日志文件是按照顺序写入的，一个写满，才会写入另外一个

由上可见，如果能增加其数据文件NDF,有利于大数据量的查询速度，但是增加日志文件却没什么用处。

2. 设置文件自动增长（大数据量，小数据量无需设置）

在SQL Server
2005中，默认MDF文件初始大小为5MB，自增为1MB，不限增长，LDF初始为1MB，增长为10%，限制文件增长到一定的数目，一般设计中，使用SQL自带的设计即可，但是大型数据库设计中，最好亲自去设计其增长和初始大小，如果初始值太小，那么很快数据库就会写满，如果写满，在进行插入会是什么情况呢？当数据文件写满，进行某些操作时，SQL
Server会让操作等待，直到文件自动增长结束了，原先的那个操作才能继续进行。如果自增长用了很长时间，原先的操作会等不及就超时取消了（一般默认的阈值是15秒），不但这个操作会回滚，文件自动增长也会被取消。也就是说，这一次文件没有得到任何增大，增长的时间根据自动增长的大小确定的，如果太小，可能一次操作需要连续几次增长才能满足，如果太大，就需要等待很长时间，所以设置自动增长要注意一下几点：

1）要设置成按固定大小增长，而不能按比例。这样就能避免一次增长太多或者太少所带来的不必要的麻烦。建议对比较小的数据库，设置一次增长50 MB到100
MB。对大的数据库，设置一次增长100 MB到200 MB。

2）要定期监测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大，或者是期望的比例。

3）设置文件最大值，以免SQL Server文件自增长用尽磁盘空间，影响操作系统。

4）发生自增长后，要及时检查新的数据文件空间分配情况。避免SQL Server总是往个别文件写数据。

因此，对于一个比较繁忙的数据库，推荐的设置是开启数据库自动增长选项，以防数据库空间用尽导致应用程序失败，但是要严格避免自动增长的发生。同时，尽量不要使用自动收缩功能。

3. 数据和日志文件分开存放在不同磁盘上

数据文件和日志文件的操作会产生大量的I/O。在可能的条件下，日志文件应该存放在一个与数据和索引所在的数据文件不同的硬盘上以分散I/O，同时还有利于数据库的灾难恢复。

二、表分区，索引分区 （优化①粗略的进行了表分区，优化②为精确数据分区）

1. 为什么要表分区？

当一个表的数据量太大的时候，我们最想做的一件事是什么？将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了

原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据。

SQL SERVER 2005中，引入了表分区的概念，
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区,当一个表里的数据很多时，可以将其分拆到多个的表里,因为要扫描的数据变得更少
,查询可以更快地运行,这样操作大大提高了性能,表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表

2. 什么时候使用分区表：

1、表的大小超过2GB。

2、表中包含历史数据，新的数据被增加到新的分区中。

3. 表分区的优缺点

表分区有以下优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能.

4. 表分区的操作三步走

4.1 创建分区函数

CREATE PARTITION FUNCTION xx1(int)

AS RANGE LEFT FOR VALUES (10000, 20000);

注释：创建分区函数：myRangePF2，以INT类型分区，分三个区间，10000以内在A 区，1W-2W在B区，2W以上在C区.

4.2 创建分区架构

CREATE PARTITION SCHEME myRangePS2

AS PARTITION xx1

TO (a, b, c);

注释：在分区函数XX1上创建分区架构：myRangePS2，分别为A,B,C三个区间

A,B,C分别为三个文件组的名称，而且必须三个NDF隶属于这三个组，文件所属文件组一旦创建就不能修改

4.3 对表进行分区

常用数据规范–数据空间类型修改为：分区方案，然后选择分区方案名称和分区列列表，结果如图所示：

也可以用sql语句生成

CREATE TABLE [dbo].[AvCache](

[AVNote] [varchar](300) NULL,

[bb] [int] IDENTITY(1,1)

) ON [myRangePS2](bb); --注意这里使用[myRangePS2]架构，根据bb分区

4.4 查询表分区

SELECT *, $PARTITION.[myRangePF2](bb) FROM dbo.AVCache  

这样就可以清楚的看到表数据是如何分区的了

4.5 创建索引分区

三、分布式数据库设计

分布式数据库系统是在集中式数据库系统的基础上发展起来的，理解起来也很简单，就是将整体的数据库分开，分布到各个地方，就其本质而言，分布式数据库系统分为两种：1.数据在逻辑上是统一的，而在物理上却是分散的，一个分布式数据库在逻辑上是一个统一的整体，在物理上则是分别存储在不同的物理节点上，我们通常说的分布式数据库都是这种2.逻辑是分布的，物理上也是分布的，这种也成联邦式分布数据库，由于组成联邦的各个子数据库系统是相对“自治”的，这种系统可以容纳多种不同用途的、差异较大的数据库，比较适宜于大范围内数据库的集成。

分布式数据库较为复杂，在此不作详细的使用和说明，只是举例说明一下，现在分布式数据库多用于用户分区性较强的系统中，如果一个全国连锁店，一般设计为每个分店都有自己的销售和库存等信息，总部则需要有员工，供应商，分店信息等数据库，这类型的分店数据库可以完全一致，很多系统也可能导致不一致，这样，各个连锁店数据存储在本地，从而提高了影响速度，降低了通信费用，而且数据分布在不同场地，且存有多个副本，即使个别场地发生故障，不致引起整个系统的瘫痪。
但是他也带来很多问题，如：数据一致性问题、数据远程传递的实现、通信开销的降低等，这使得分布式数据库系统的开发变得较为复杂，只是让大家明白其原理，具体的使用方式就不做详细的介绍了。

四、整理数据库碎片

如果你的表已经创建好了索引，但性能却仍然不好，那很可能是产生了索引碎片，你需要进行索引碎片整理。

1. 什么是索引碎片?

由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。

2. 如何知道是否发生了索引碎片?

在SQLServer数据库，通过DBCC ShowContig或DBCC ShowContig(表名)检查索引碎片情况，指导我们对其进行定时重建整理。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200819093111184.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FhMjUyODg3Nzk4Nw==,size_16,color_FFFFFF,t_70#pic_center)
通过对扫描密度（过低），扫描碎片（过高）的结果分析，判定是否需要索引重建，主要看如下两个：
Scan Density [Best Count:Actual Count]-扫描密度［最佳值:实际值］：DBCC
SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

解决方式：

• 利用DBCC INDEXDEFRAG整理索引碎片

• 利用DBCC DBREINDEX重建索引。

两者区别调用微软的原话如下：

DBCC INDEXDEFRAG

命令是联机操作，所以索引只有在该命令正在运行时才可用，而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
重新创建聚集索引将对数据进行重新组织，其结果是使数据页填满。填满程度可以使用 FILLFACTOR
选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。也就是说，要想获得好的效果，还是得用重建索引，所以决定重建索引。

五、数据库表优化

1. 设计规范化表，消除数据冗余

数据库范式是确保数据库结构合理，满足各种查询需要、避免数据库操作异常的数据库设计方式。满足范式要求的表，称为规范化表，范式产生于20世纪70年代初，一般表设计满足前三范式就可以，在这里简单介绍一下前三范式

先给大家看一下百度百科给出的定义：

第一范式（1NF）无重复的列

所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式（2NF）属性

在1NF的基础上，非码属性必须完全依赖于码[在1NF基础上消除非主属性对主码的部分函数依赖]

第三范式（3NF）属性

在1NF基础上，任何非主属性不依赖于其它非主属性[在2NF基础上消除传递依赖]

通俗的给大家解释一下（可能不是最科学、最准确的理解）

第一范式：属性(字段)的原子性约束，要求属性具有原子性，不可再分割；

第二范式：记录的惟一性约束，要求记录有惟一标识，每条记录需要有一个属性来做为实体的唯一标识。

第三范式：属性(字段)冗余性的约束，即任何字段不能由其他字段派生出来，在通俗点就是：主键没有直接关系的数据列必须消除(消除的办法就是再创建一个表来存放他们，当然外键除外)

如果数据库设计达到了完全的标准化，则把所有的表通过关键字连接在一起时，不会出现任何数据的复本(repetition)。标准化的优点是明显的，它避免了数据冗余，自然就节省了空间，也对数据的一致性(consistency)提供了根本的保障，杜绝了数据不一致的现象，同时也提高了效率。

2. 适当的冗余，增加计算列

数据库设计的实用原则是：在数据冗余和处理速度之间找到合适的平衡点

满足范式的表一定是规范化的表，但不一定是最佳的设计。很多情况下会为了提高数据库的运行效率，常常需要降低范式标准：适当增加冗余，达到以空间换时间的目的。比如我们有一个表，产品名称，单价，库存量，总价值。这个表是不满足第三范式的，因为“总价值”可以由“单价”乘以“数量”得到，说明“金额”是冗余字段。但是，增加“总价值”这个冗余字段，可以提高查询统计的速度，这就是以空间换时间的作法。合理的冗余可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。

其中"总价值"就是一个计算列，在数据库中有两种类型：数据列和计算列，数据列就是需要我们手动或者程序给予赋值的列，计算列是源于表中其他的数据计算得来，比如这里的"总价值"

在SQL中创建计算列：

create table table1  
(  
	number decimal(18,4),  
	price money,  
	Amount as number*price --这里就是计算列  
)

也可以再表设计中，直接手动添加或修改列属性即可：如下图

是否持久性，我们也需要注意：

如果是’否’，说明这列是虚拟列，每次查询的时候计算一次，而且那么它是不可以用来做check,foreign key或not null约束。

如果是’是’,就是真实的列，不需要每次都计算，可以再此列上创建索引等等。

3. 索引

索引是一个表优化的重要指标，在表优化中占有极其重要的成分，所以将单独写一章”SQL索引一步到位“去告诉大家如何建立和优化索引

4. 主键和外键的必要性

主键与外键的设计，在全局数据库的设计中，占有重要地位。 因为：主键是实体的抽象，主键与外键的配对，表示实体之间的连接。

主键：根据第二范式，需要有一个字段去标识这条记录，主键无疑是最好的标识，但是很多表也不一定需要主键，但是对于数据量大，查询频繁的数据库表，一定要有主键，主键可以增加效率、防止重复等优点。

主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面。

外键：外键作为数据库对象，很多人认为麻烦而不用，实际上，外键在大部分情况下是很有用的，理由是：外键是最高效的一致性维护方法

数据库的一致性要求，依次可以用外键、CHECK约束、规则约束、触发器、客户端程序，一般认为，离数据越近的方法效率越高。
谨慎使用级联删除和级联更新，级联删除和级联更新作为SQL SERVER
2000当年的新功能，在2005作了保留，应该有其可用之处。我这里说的谨慎，是因为级联删除和级联更新有些突破了传统的关于外键的定义，功能有点太过强大，使用前必须确定自己已经把握好其功能范围，否则，级联删除和级联更新可能让你的数据莫名其妙的被修改或者丢失。从性能看级联删除和级联更新是比其他方法更高效的方法。

5. 存储过程、视图、函数的适当使用

很多人习惯将复杂操作都放在应用程序层，但如果你要优化数据访问性能，将SQL代码移植到数据库上(使用存储过程，视图，函数和触发器)也是一个很大的改进原因如下：

• 存储过程减少了网络传输、处理及存储的工作量，且经过编译和优化，执行速度快，易于维护，且表的结构改变时，不影响客户端的应用程序

• 使用存储过程，视图，函数有助于减少应用程序中SQL复制的弊端，因为现在只在一个地方集中处理SQL

• 使用数据库对象实现所有的TSQL有助于分析TSQL的性能问题，同时有助于你集中管理TSQL代码，更好的重构TSQL代码

6. 传说中的‘三少原则’

• 数据库的表越少越好

• 表的字段越少越好

• 字段中的组合主键、组合索引越少越好

当然这里的少是相对的，是减少数据冗余的重要设计理念。

7. 分割你的表，减小表尺寸

如果你发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，以该表主键的某个值为界线，将该表的记录水平分割为两个表。

如果你若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表

8. 字段设计原则

字段是数据库最基本的单位，其设计对性能的影响是很大的。需要注意如下：

• 数据类型尽量用数字型，数字型的比较比字符型的快很多。

• 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

• 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。

• 少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

• 自增字段要慎用，不利于数据迁移

六、程序操作优化

1. 操作符优化

1. IN、NOT IN 操作符

IN和EXISTS 性能有外表和内表区分的，但是在大数据量的表中推荐用EXISTS 代替IN 。

Not IN 不走索引的是绝对不能用的，可以用NOT EXISTS 代替

1.2 IS NULL 或IS NOT NULL操作

索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可

1.3 <> 操作符（不等于）

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 用其它相同功能的操作运算代替，如 a<>0 改为 a>0 or a<0 a<>’’ 改为 a>’’

1.4 用全文搜索搜索文本数据，取代like搜索

全文搜索始终优于like搜索：

• 全文搜索让你可以实现like不能完成的复杂搜索，如搜索一个单词或一个短语，搜索一个与另一个单词或短语相近的单词或短语，或者是搜索同义词;

• 实现全文搜索比实现like搜索更容易(特别是复杂的搜索);

2. SQL语句优化

2.1 在查询中不要使用 select

为什么不能使用，地球人都知道，但是很多人都习惯这样用，要明白能省就省，而且这样查询数据库不能利用“覆盖索引”了

2.2 尽量写WHERE子句

尽量不要写没有WHERE的SQL语句

2.3 注意SELECT INTO后的WHERE子句

因为SELECT INTO把数据插入到临时表，这个过程会锁定一些系统表，如果这个WHERE子句返回的数据过多或者速度太慢，会造成系统表长期锁定，诸塞其他进程。

2.4 对于聚合查询，可以用HAVING子句进一步限定返回的行

** 避免使用临时表**

• 除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;

• 大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。

2.5 减少访问数据库的次数

程序设计中最好将一些常用的全局变量表放在内存中或者用其他的方式减少数据库的访问次数

2.6 尽量少做重复的工作

尽量减少无效工作，但是这一点的侧重点在客户端程序，需要注意的如下：

• 控制同一语句的多次执行，特别是一些基础数据的多次执行是很多程序员很少注意的

• 减少多次的数据转换，也许需要数据转换是设计的问题，但是减少次数是程序员可以做到的。

• 杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销。

• 合并对同一表同一条件的多次UPDATE，比如

  UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’  
  UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’  
  

  这两个语句应该合并成以下一个语句

  UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’  
  WHERE EMP_ID=’ VPA30890F’
  

• UPDATE操作不要拆成DELETE操作+INSERT操作的形式，虽然功能相同，但是性能差别是很大的。

• 不要写一些没有意义的查询，比如

  SELECT * FROM EMPLOYEE WHERE 1=2
  

3. where使用原则

3.1 在下面两条select语句中:

select * from table1 where field1<=10000 and field1>=0;  
select * from table1 where field1>=0 and field1<=10000;  

如果数据表中的数据field1都>=0,则第一条select语句要比第二条select语句效率高的多，因为第二条select语句的第一个条件耗费了大量的系统资源。
第一个原则：在where子句中应把最具限制性的条件放在最前面。

3.2 在下面的select语句中:

select * from tab where a=… and b=… and c=…;  

若有索引index(a,b,c)，则where子句中字段的顺序应和索引中字段顺序一致。
第二个原则：where子句中字段的顺序应和索引中字段顺序一致。

3.3 以下假设在field1上有唯一索引I1，在field2上有非唯一索引I2。

select field3,field4 from tb where field1=‘sdf’ 快  
select * from tb where field1=‘sdf’ 慢，  

因为后者在索引扫描后要多一步ROWID表访问。

select field3,field4 from tb where field1>=‘sdf’ 快  
select field3,field4 from tb where field1>‘sdf’ 慢  

因为前者可以迅速定位索引。

select field3,field4 from tb where field2 like ‘R%’ 快  
select field3,field4 from tb where field2 like ‘%R’ 慢，  

因为后者不使用索引。

3.4 使用函数如：

select field3,field4 from tb where upper(field2)='RMN’	--不使用索引 

如果一个表有两万条记录，建议不使用函数；如果一个表有五万条以上记录，严格禁止使用函数！两万条记录以下没有限制。