(转)大数据量下的SQL Server数据库优化

蔚落 2022-01-14 11:41 245阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

在SQL Server中，默认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总是往个别文件写数据。

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

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

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

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

为什么要表分区？

当一个表的数据量太大的时候，我们最想做的一件事是什么？将这个表一分为二或者更多分，但是表还是这个表，只是将其内容存储分开，这样读取就快了N倍了。原理：表数据是无法放在文件中的，但是文件组可以放在文件中，表可以放在文件组中，这样就间接实现了表数据存放在不同的文件中。能分区存储的还有：表、索引和大型对象数据 。SQL SERVER 2005中，引入了表分区的概念， 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区,当一个表里的数据很多时，可以将其分拆到多个的表里,因为要扫描的数据变得更少 ,查询可以更快地运行,这样操作大大提高了性能,表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

转载于:https://my.oschina.net/secyaher/blog/274467

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/03751d00f36d49bb9209cccb336847e6.png