一拳超人—写给码农看的数据库优化方法:everything is a file
Everything is a file是UNIX世界中的名言，指的是将系统中各种资源都认为是文件，通过字符串读写方式实现统一接口调用。 起这个标题仅为蹭一下名言热度，我们真正要聊的是数据库世界中的“everything is (in) a file”——此处翻译为：“啥都在一个文件里”。 说到文件，想必有少侠要冷笑一声：老生常谈——要说存储系统IO之类的问题了吧？是不是还要介绍一下RAID？ 少侠放心，这里不说存储系统IO问题，否则如何对得起如此清新脱俗的标题。只是顺便强调一下：无数书籍中提到关于数据库存储的性能，各种RAID、各种IO计算…这些是有道理的，也是无数雏儿踩雷后的战场总结（也有可能是遗言）。所以假如贵派藏经阁中硬盘灯总在卡拉拉不住闪，技术人员思维还停留在“好硬盘就是容量大的硬盘”以及“啥叫磁盘队列”上面，恩…呵呵。 当然，在一个萌妹子买电脑都知道要SSD硬盘的年代，如果还被存储性能给害死，实在太说不过去了。那么假设现在SSD村村通，我们是不是可以洗洗睡了？——真是抱歉，那样美好的事情是不存在的。 我们先讲一个码农最喜闻乐见的堆码故事： 单位中有一台存储设备用作日志记录。其定义与调用方式看码： 正确输出如下： 各位注意，Action-A与Action-B需要保证顺序，并且成对出现。让我们记下1000次日志写的运行时间：4秒。 某天领导突然灵光一闪用可爱的少女表情包对你说，据说多线程操作更快哦！ 我们知道，领导的恶意卖萌一定要重视。所以你写了一个多线程操作类： 然后修改一下调用方法 嗯！效果不错！两个线程，写入量是之前的2倍，而运行时间也在4秒，哇，领导英明，多线程果然更快哦！ 多么美好的一天！如果你们两个没有被老板咆哮的话。 天啊，程序输出不仅乱了顺序，更过分的是还出现了乱码！ 问题出现在哪里？聪明的同学们肯定已经得出答案： 笨！ IODevice类中IOWrite方法没有实现线程安全！ 加个Synchronized撒！ 嗯，说的对，IOWrite方法没有加Synchronized，导致两个线程 “串了”。问题就是这么简单，解决也是这么容易——但是，加完Synchronized后线程是安全了，程序执行时间在两个线程的情况下，从原先4秒悲催的变成了8秒——绕了一圈，挨了一顿咆哮，回到了原点。 领导语重心长的说：啊那啥，我们要学会积极的看事情，你看我们虽然折腾了一番，但是你获得加班工资了是吧…啊那啥，能不能又线程安全又快点?这事作为下半年重点技术攻关项目研究一下… 领导的尴尬，我们也要重视。所以我们研究一下Synchronized关键词。Synchronized实现线程安全的原理是通过在对象上加锁的方式，保护对同一个对象的方法调用，确保同一时间只能由一个线程执行。对于不能同时操作的对象，锁是必须的，否则就会出现以上“数据串了”之类的诡异现象。可以这么理解：线程安全是以并行变串行的代价换取来的。 多线程操作快不快，有很大一部分取决于资源调配中是否产生争用堵塞。有几种方案可以减少堵塞提高性能？只有两种： 1、提升调用对象方法的速度。 2、多出几个可操作对象，开辟新的通道。 (有人说还有一种是把锁砸了…呵呵，少侠你真是一身H练，天纵Y才…) 所谓道在那啥，所以我们给领导举了一个关于那啥的例子做总结： 这个世界上有一种痛苦来自于一群人争一个坑。解决问题最好的办法无非两个：其一是让坑上那位加快速度；其二是多设几坑。所谓线程安全是指门上有锁，你可以独自那啥完再一脸舒坦的出去;所谓线程不安全就是门上没锁，你要做好脸色发白的大汉推门而入的准备，以及与之共享一个坑位的觉悟。 大多数情况下，让坑上那位加快速度是不人道的，共享坑位也是不安全的（恩，确实不安全）。所以一般三观正常的做法，只能是扩展位置。领导听完总结，一声长叹：讨厌… 以上是一个悲伤的故事。书到此处，有些少侠要急了：弄了一个玄乎的标题，扯了一个故事，不是说数据库么？数据库呢？呢？呢？——啊，且让领导感慨人生去，老夫马上说数据库。 各位少侠都知道，SQL SERVER创建数据库时，行数据存储文件默认只会建立一个。 此时，“everything is (in) a file”。然而经过上面的故事，各位想想：那么多的表，那么多的数据，共用一个坑，啊不，文件……你难道不怀疑其中必有蹊跷？ 有没有蹊跷验证一下就知道。我们设计一个实验： 1、创建一个数据库，包含10个文件组，每文件组中1个文件，如图 （有细心的同学会注意到这里的文件都预先定义了大小。恩，这是为了不让文件扩展干扰实验结果。关于文件扩展，下次另开一篇文章细说。） 2、我们要做一个操作：建立200张表，对此200张表同时进行大量数据写入。 3、步骤2的操作分为两种情况： A、200张表位于一个文件(组) 中 B、200张表均摊在10个文件(组)中。 对比一下操作所需时间，看哪个更快。 这里将200张表的写入操作封装在“TableInsert”存储过程中，我们用一个小工具SQLQueryStress来同时开启200个会话进行。（建库脚本与存储过程脚本附在文章最后，有兴趣的同学可以自己做一下实验。这里重点提醒一下，请尽量不要将实验文件放在有其他IO操作的磁盘中，以免干扰。） 在咩叔的实验中，场景A与B都重复了10次，取中间值作为实验最终结果。 场景A：200张表集中在一个文件（组）中 场景B：200张表均摊在10个文件（组）中 我们重点关注每会话平均执行时间。看到区别了没有？场景A用时约30秒，场景B用时约8秒。哇哦，这可是同一块硬盘哦，为何差距如此之大？ 结合本文环境分析，恩，肯定是产生争用堵塞了对吧？那么争用在哪里？ 当我们执行场景A时，可以在SQL SERVER上另开一个会话，查询sys.dm_os_waiting_tasks视图（这张视图的官方定义为：返回有关正在等待某些资源的任务的等待队列的信息）。可以看到大量的PAGELATCH_UP等待。 呦嗬，怎么所有会话都在等待“16：1：202200”这个资源（不同实验环境此处结果会有不同）？这是个甚？ 简单解释一下，SQL SERVER表中的数据，是以“数据页”为最小单位进行存储。所谓PAGELATCH_UP，可以大致理解为：在对一个数据页进行编辑时，为防止被其他人“写串”而加的锁（你就把它当作数据库中的Synchronized关键字吧）。那么很明显，其所对应的“16：1：202200”对象就是一个“数据页”。 这位数据页为何如此吃香，一帮子人都要争他？我们要看看他里面装了什么。使用咒语： DBCC TRACEON(3604) DBCC PAGE(16,1,202200,3) 打开神秘的数据页，看看里面内容…. 哦！What is the F…眼睛，我的眼睛！ 别慌，稳住。本文中只需关心这一段： PFS页面…这又是个甚？ 看一下微软官方解释：“页可用空间 (PFS)”页记录每页的分配状态，是否已分配单个页以及每页的可用空间量。 PFS 对每页都有一个字节，记录该页是否已分配。如果已分配，则记录该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。 嗯，果然官方文档，一如既往的格调高以及看不懂是吧。不急，看个图就明白了。 你看，一图解千愁，明白了吧。简单点说，PFS页就是“仓库空间管理员”，他需要记录下各个数据页的空间使用状况，以备新数据进入时快速指定一个可用仓位。 那么我们就能明白，为何在实验中大量堵塞集中在这个页面上了。想想看，大量的表插入不就是“寻找可用空间并占用”么，PFS这位管理员岂会不忙？实验结果也就能理解：200个人排队，一个窗口快还是10个窗口快？ 在这个实验中，大量的线程争抢的是PFS对象。不幸的消息是：数据文件中类似的“仓库管理员”可不止PFS一个……(下次写篇文章说说系统数据库TempDB中的争抢) 好在很多时候看似高科技问题解决的办法却异常简单粗暴：加几个坑位呗。在我们的实验中，场景B就是将200张表平摊到了10个文件组，让拥堵的概率下降了90%。 看似简单粗暴的方法，却正击中了问题的核心：要么加快速度，要么增加通道——加快速度太为难，那么就增加通道。 请各位了解，此项简单粗暴操作优点在于： 一、基本没有任何代价： 1、数据库中增加几个文件组是没有代价的。 2、不需要增加硬件设备。 3、不需要对代码做任何修改。 二、延伸一步，做IO隔离 总有单个磁盘系统顶不住的一天，将大量IO操作密集表放在一个文件、一个磁盘系统是不明智的。一些IO热表，可以单独放在独立的文件组，此文件组可放置在独立的磁盘系统中。 记住，在数据库的世界中，every thing is (in) a file需要修改为：every thing is (in) many file 可惜在咩叔的观察中，大量企业的数据库还是处在every thing is (in) a file的“经典模式”下，白白放弃了简单提升性能的机会。希望有兴趣的少侠看完这篇文章后可以动手改变（有问题可以给咩叔留言）。 以下为本文相关数据库资料 sys.dm_os_waiting_tasks https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-sql?view=sql-server-2017 什么是PAGELATCH和PAGEIOLATCH https://blogs.msdn.microsoft.com/apgcdsd/2011/11/28/pagelatchpageiolatch/ 页和区体系结构指南 https://docs.microsoft.com/zh-cn/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-2017 以下为本文所用数据库实验脚本 建库脚本 CREATE DATABASE [FileTest] CONTAINMENT = NONE ON PRIMARY ( NAME = N'FileTest1', FILENAME = N'D:\FileTest\FileTest1.mdf' , SIZE = 2048000KB , FILEGROWTH = 10240KB ), FILEGROUP [FG2] ( NAME = N'FileTest2', FILENAME = N'D:\FileTest\FileTest2.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG3] ( NAME = N'FileTest3', FILENAME = N'D:\FileTest\FileTest3.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG4] ( NAME = N'FileTest4', FILENAME = N'D:\FileTest\FileTest4.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG5] ( NAME = N'FileTest5', FILENAME = N'D:\FileTest\FileTest5.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG6] ( NAME = N'FileTest6', FILENAME = N'D:\FileTest\FileTest6.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG7] ( NAME = N'FileTest7', FILENAME = N'D:\FileTest\FileTest7.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG8] ( NAME = N'FileTest8', FILENAME = N'D:\FileTest\FileTest8.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG9] ( NAME = N'FileTest9', FILENAME = N'D:\FileTest\FileTest9.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ), FILEGROUP [FG10] ( NAME = N'FileTest10', FILENAME = N'D:\FileTest\FileTest10.ndf' , SIZE = 204800KB , FILEGROWTH = 10240KB ) LOG ON ( NAME = N'FileTest_log', FILENAME = N'D:\FileTest\FileTest_log.ldf' , SIZE = 4096000KB , FILEGROWTH = 102400KB) GO --设置恢复模式为简单，去掉文件扩展干扰 ALTER DATABASE [FileTest] SET RECOVERY SIMPLE WITH NO_WAIT GO 存储过程脚本 CREATE PROC [dbo].[TableInsert] @InOneFile BIT=1 --是否将所有操作集中在一个文件中 1：YES 0：NO AS BEGIN --文件组名字 DECLARE @FileGroupName VARCHAR(50) IF (@InOneFile=1 OR @@SPID%10=0) --如果@InOneFile开关=1或者归类编号=0，则放到PRIMARY文件组中 BEGIN SET @FileGroupName='[PRIMARY]' END ELSE BEGIN SET @FileGroupName='FG'+CONVERT(VARCHAR(2),(@@SPID%10+1)) --对应文件组FG2~10 END DECLARE @TableName VARCHAR(50)='Temp'+CONVERT(VARCHAR(10),@@SPID) --以TempXXX为表名称 IF EXISTS (SELECT TOP 1 * FROM SYS.tables WHERE name=@TableName ) --如果表已存在，删除之 BEGIN EXEC ('DROP TABLE '+@TableName) END --创建表，并往表中插入1000条记录。每条记录约为8K（正好占用一个数据页） EXEC (' CREATE TABLE '+@TableName+' ( C1 INT PRIMARY KEY, C2 CHAR(8000) ) ON '+@FileGroupName+' DECLARE @i INT=0 WHILE(@i<1000) BEGIN INSERT INTO '+@TableName+' VALUES(@i,''AAANNNNSSSSMMMMDDDD''); SET @i=@i+1; END --DROP TABLE '+@TableName ) END GO
