一拳超人—写给码农看的数据库优化方法:everything is a file

简介: 这个世界上有一种痛苦来自于一群人争一个坑。解决问题最好的办法无非两个:其一是让坑上那位加快速度;其二是多设几坑。所谓线程安全是指门上有锁,你可以独自那啥完再一脸舒坦的出去;所谓线程不安全就是门上没锁,你要做好脸色发白的大汉推门而入的准备,以及与之共享一个坑位的觉悟。

Everything is a file是UNIX世界中的名言,指的是将系统中各种资源都认为是文件,通过字符串读写方式实现统一接口调用。

起这个标题仅为蹭一下名言热度,我们真正要聊的是数据库世界中的“everything is (in) a file”——此处翻译为:“啥都在一个文件里”。

说到文件,想必有少侠要冷笑一声:老生常谈——要说存储系统IO之类的问题了吧?是不是还要介绍一下RAID?

少侠放心,这里不说存储系统IO问题,否则如何对得起如此清新脱俗的标题。只是顺便强调一下:无数书籍中提到关于数据库存储的性能,各种RAID、各种IO计算…这些是有道理的,也是无数雏儿踩雷后的战场总结(也有可能是遗言)。所以假如贵派藏经阁中硬盘灯总在卡拉拉不住闪,技术人员思维还停留在“好硬盘就是容量大的硬盘”以及“啥叫磁盘队列”上面,恩…呵呵。

2e0db1a2c12806df697de7fbf4bfb99e88207983


当然,在一个萌妹子买电脑都知道要SSD硬盘的年代,如果还被存储性能给害死,实在太说不过去了。那么假设现在SSD村村通,我们是不是可以洗洗睡了?——真是抱歉,那样美好的事情是不存在的

我们先讲一个码农最喜闻乐见的堆码故事:

单位中有一台存储设备用作日志记录。其定义与调用方式看码:

   307635fb3eb97d790139917c8c8f8f42bbef1236

 


b72547f83464827a399c5baadfe4d10fa8850e7c



正确输出如下:

   a799d56eacc38a1e21b2914a6623dee5afda595a

各位注意,Action-A与Action-B需要保证顺序,并且成对出现。让我们记下1000次日志写的运行时间:4

某天领导突然灵光一闪用可爱的少女表情包对你说,据说多线程操作更快哦!


   945dc8bed11fcf94059c6055233f1036e6e632e5


我们知道,领导的恶意卖萌一定要重视。所以你写了一个多线程操作类:

3e3e1881c70d071a656638a5dbfa2639dd124838


然后修改一下调用方法

d8ad7deaf85334d2e21e7036772ee9857e41b84a


嗯!效果不错!两个线程,写入量是之前的2倍,而运行时间也在4秒,哇,领导英明,多线程果然更快哦!

多么美好的一天!如果你们两个没有被老板咆哮的话。


d71a2999ce2d0f717328b9fc038f62693e6eb014


天啊,程序输出不仅乱了顺序,更过分的是还出现了乱码


6ae6b7ae4979e6420330ebb5d1917e0ef2002502


问题出现在哪里?聪明的同学们肯定已经得出答案:

笨! IODevice类中IOWrite方法没有实现线程安全! 加个Synchronized撒!

嗯,说的对,IOWrite方法没有加Synchronized,导致两个线程 “串了”。问题就是这么简单,解决也是这么容易——但是,加完Synchronized后线程是安全了,程序执行时间在两个线程的情况下,从原先4秒悲催的变成了8秒——绕了一圈,挨了一顿咆哮,回到了原点。


3cf697960ccab6f5ccf0cc025b4ac023ae79e27f

领导语重心长的说:啊那啥,我们要学会积极的看事情,你看我们虽然折腾了一番,但是你获得加班工资了是吧…啊那啥,能不能又线程安全又快点?这事作为下半年重点技术攻关项目研究一下…

领导的尴尬,我们也要重视。所以我们研究一下Synchronized关键词。Synchronized实现线程安全的原理是通过在对象上加锁的方式,保护对同一个对象的方法调用,确保同一时间只能由一个线程执行。对于不能同时操作的对象,锁是必须的,否则就会出现以上“数据串了”之类的诡异现象。可以这么理解:线程安全是以并行变串行的代价换取来的

多线程操作快不快,有很大一部分取决于资源调配中是否产生争用堵塞。有几种方案可以减少堵塞提高性能?只有两种:

1、提升调用对象方法的速度。

2、多出几个可操作对象,开辟新的通道。

(有人说还有一种是把锁砸了…呵呵,少侠你真是一身H练,天纵Y才…)

所谓道在那啥,所以我们给领导举了一个关于那啥的例子做总结:

c6aadc2ed69b59284139b2757351ae273870203a

这个世界上有一种痛苦来自于一群人争一个坑。解决问题最好的办法无非两个:其一是让坑上那位加快速度;其二是多设几坑。所谓线程安全是指门上有锁,你可以独自那啥完再一脸舒坦的出去;所谓线程不安全就是门上没锁,你要做好脸色发白的大汉推门而入的准备,以及与之共享一个坑位的觉悟。

大多数情况下,让坑上那位加快速度是不人道的,共享坑位也是不安全的(恩,确实不安全)。所以一般三观正常的做法,只能是扩展位置。领导听完总结,一声长叹:讨厌…

以上是一个悲伤的故事。书到此处,有些少侠要急了:弄了一个玄乎的标题,扯了一个故事,不是说数据库么?数据库呢?呢?呢?——啊,且让领导感慨人生去,老夫马上说数据库。

各位少侠都知道,SQL SERVER创建数据库时,行数据存储文件默认只会建立一个。


   643735cbba20eadf3f3cf9e34065309c3efbe8e1


此时,everything is (in) a file”。然而经过上面的故事,各位想想:那么多的表,那么多的数据,共用一个坑,啊不,文件……你难道不怀疑其中必有蹊跷?

023a1289e3fa020d70d2de03fa243d4204b5e83d


有没有蹊跷验证一下就知道。我们设计一个实验:

1、创建一个数据库,包含10个文件组,每文件组中1个文件,如图

178b2132e74bf1e9288661fa63251c22126d10ac

有细心的同学会注意到这里的文件都预先定义了大小。恩,这是为了不让文件扩展干扰实验结果。关于文件扩展,下次另开一篇文章细说。

2、我们要做一个操作:建立200张表,对此200张表同时进行大量数据写入。

3、步骤2的操作分为两种情况:

A、200张表位于一个文件(组) 中

B、200张表均摊在10个文件(组)中。

对比一下操作所需时间,看哪个更快。

e7507a5658cd14b74783fdb8afee443ee0f676a2

这里将200张表的写入操作封装在“TableInsert”存储过程中,我们用一个小工具SQLQueryStress来同时开启200个会话进行。(建库脚本与存储过程脚本附在文章最后,有兴趣的同学可以自己做一下实验。这里重点提醒一下,请尽量不要将实验文件放在有其他IO操作的磁盘中,以免干扰。

在咩叔的实验中,场景A与B都重复了10次,取中间值作为实验最终结果。

 

      场景A:200张表集中在一个文件(组)中

  cd90bd3b9b40a045955747b5c15cefc6d4a445e9

 

      场景B:200张表均摊在10个文件(组)中

  23815356dbfaf21190972464658b21ab3d2e3554

 

我们重点关注每会话平均执行时间。看到区别了没有?场景A用时约30秒,场景B用时约8秒。哇哦,这可是同一块硬盘哦,为何差距如此之大?

结合本文环境分析,恩,肯定是产生争用堵塞了对吧?那么争用在哪里?

当我们执行场景A时,可以在SQL SERVER上另开一个会话,查询sys.dm_os_waiting_tasks视图(这张视图的官方定义为:返回有关正在等待某些资源的任务的等待队列的信息)。可以看到大量的PAGELATCH_UP等待。

   fa02bbc6c8feece349a8dada138ef421611f5e17

 

   

  呦嗬,怎么所有会话都在等待“161202200”这个资源(不同实验环境此处结果会有不同)?这是个甚?

  简单解释一下,SQL SERVER表中的数据,是以“数据页”为最小单位进行存储。所谓PAGELATCH_UP,可以大致理解为:在对一个数据页进行编辑时,为防止被其他人“写串”而加的锁(你就把它当作数据库中的Synchronized关键字吧)。那么很明显,其所对应的“16:1:202200”对象就是一个“数据页”。

这位数据页为何如此吃香,一帮子人都要争他?我们要看看他里面装了什么。使用咒语:

DBCC TRACEON(3604)

DBCC PAGE(16,1,202200,3)

      打开神秘的数据页,看看里面内容….

      e2f8ede0ec9a7cb842516518ab67ed401790e14c

 

      哦!What is the F…眼睛,我的眼睛!

      a6638b93cba918e93aeb899d1f499dc4d21ad2f4


      别慌,稳住。本文中只需关心这一段:

 

  cf6e70aea3fa2674976f65bf7d76b76d913c1ad7


PFS页面…这又是个甚?

看一下微软官方解释:“页可用空间 (PFS)”页记录每页的分配状态,是否已分配单个页以及每页的可用空间量。 PFS 对每页都有一个字节,记录该页是否已分配。如果已分配,则记录该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。

嗯,果然官方文档,一如既往的格调高以及看不懂是吧。不急,看个图就明白了。

d0a702a4def8d79347bb6dfd9d0a655cbdf99616

你看,一图解千愁,明白了吧。简单点说,PFS页就是“仓库空间管理员”,他需要记录下各个数据页的空间使用状况,以备新数据进入时快速指定一个可用仓位。

那么我们就能明白,为何在实验中大量堵塞集中在这个页面上了。想想看,大量的表插入不就是“寻找可用空间并占用”么,PFS这位管理员岂会不忙?实验结果也就能理解:200个人排队,一个窗口快还是10个窗口快?

在这个实验中,大量的线程争抢的是PFS对象。不幸的消息是:数据文件中类似的“仓库管理员”可不止PFS一个……(下次写篇文章说说系统数据库TempDB中的争抢)

566735c449b73b7dc66382557cd145fb85206e4f

好在很多时候看似高科技问题解决的办法却异常简单粗暴:加几个坑位呗。在我们的实验中,场景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=--是否将所有操作集中在一个文件中 1YES  0NO

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

 

目录
相关文章
|
13天前
|
监控 关系型数据库 MySQL
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
MySQL技能完整学习列表12、性能优化——1、性能指标和监控——2、优化查询和数据库结构——3、硬件和配置优化
55 0
|
15天前
|
缓存 NoSQL PHP
百度搜索:蓝易云【如何使用PHP进行数据库索引优化?】
通过以上方法,你可以使用PHP进行数据库索引优化,提高数据库查询性能和整体应用性能。同时,定期维护数据库和优化查询语句也是保持数据库高性能的关键。
37 11
|
17天前
|
机器学习/深度学习 算法 数据库
深入浅出:利用Python与机器学习优化数据库性能
本文介绍了一种创新的方法,结合Python编程语言和机器学习技术,来优化数据库性能。传统的数据库性能优化方法往往依赖于数据库管理员(DBA)的经验和直觉,而本文所提出的方法通过自动化的方式,利用机器学习模型对数据库查询进行分析和优化,从而实现更高效、更智能的数据库性能管理。本文首先介绍了使用Python进行数据库操作的基础知识,然后详细阐述了如何应用机器学习算法来预测和改善数据库查询性能,最后通过一个实际案例展示了该方法的有效性。本文旨在为数据库管理员、开发者以及对数据库性能优化感兴趣的读者提供一种全新的视角和工具。
|
18天前
|
存储 大数据 数据库
深入理解数据库索引优化技术
本文将深入探讨数据库索引优化技术,包括索引的基本概念、常见的索引类型以及如何选择和创建适当的索引。通过了解索引的原理和优化策略,读者可以提高数据库查询性能并优化数据访问效率。
|
20天前
|
存储 缓存 监控
如何优化数据库查询性能
在现代应用程序开发中,数据库查询性能是一个至关重要的问题。本文将介绍一些常见的数据库查询性能优化技巧,帮助开发人员更好地使用各种数据库系统,并提高应用程序的响应速度和可扩展性。
|
20天前
|
缓存 数据库 索引
面试题17: 数据库优化
面试题17: 数据库优化
|
21天前
|
SQL 存储 缓存
如何通过优化SQL查询提升数据库性能
SQL查询是数据库的核心功能之一,对于大型数据量的应用程序来说,优化SQL查询可以显著提升数据库的性能。本文将介绍如何通过优化SQL查询语句来提升数据库的性能,包括索引优化、查询语句优化以及其他一些技巧。
|
22天前
|
缓存 数据库 开发者
构建高性能的数据库查询语句优化策略
数据库查询是开发过程中常见的性能瓶颈之一。本文将介绍构建高性能数据库查询语句的优化策略,包括索引的设计与使用、查询语句的编写技巧、连接的优化等方面,帮助开发者提升数据库查询的效率和响应速度。
|
23天前
|
存储 数据库 开发者
现代数据库管理系统中的索引设计与优化
索引是数据库管理系统中的重要组成部分,对于提高查询性能和数据访问效率至关重要。本文将探讨现代数据库管理系统中的索引设计与优化策略,包括索引类型、索引选择原则、索引优化技巧等。通过深入理解索引的内部工作原理和优化策略,开发者可以更好地利用索引提升数据库性能。
|
23天前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶实战:优化性能、提高安全性和实现高可用性
MySQL数据库进阶实战:优化性能、提高安全性和实现高可用性
38 0

热门文章

最新文章