SQL Server 高性能写入的一些总结

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

1.1.2 正文

========

假设，我们要设计一个博客系统，其中包含一个用户表（User），它用来存储用户的账户名、密码、显示名称和注册日期等信息。

由于时间的关系，我们已经把User表设计好了，它包括账户名、密码（注意：这里没有考虑隐私信息的加密存储）、显示名称和注册日期等，具体设计如下：

– =============================================

– Author: JKhuang

– Create date: 7/8/2012

– Description: A table stores the user information.

– =============================================

CREATE TABLE [dbo].[jk_users](

– This is the reference to Users table, it is primary key.

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[user_login] [varchar](60) NOT NULL,

[user_pass] [varchar](64) NOT NULL,

[user_nicename] [varchar](50) NOT NULL,

[user_email] [varchar](100) NOT NULL,

[user_url] [varchar](100) NOT NULL,

– This field get the default from function GETDATE().

[user_registered] [datetime] NOT NULL CONSTRAINT [DF_jk_users_user_registered] DEFAULT (getdate()),

[user_activation_key] [varchar](60) NOT NULL,

[user_status] [int] NOT NULL CONSTRAINT [DF_jk_users_user_status] DEFAULT ((0)),

[display_name] [varchar](250) NOT NULL

)

图1 Users表设计

上面，我们定义了Users表，它包含账户名、密码、显示名称和注册日期等10个字段，其中，ID是一个自增的主键，user_resistered用来记录用户的注册时间，它设置了默认值GETDATE()。

接下来，我们将通过客户端代码实现数据存储到Users表中，具体的代码如下：

Creates a database connection.

var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN1”].ToString());

conn.Open();

This is a massive SQL injection vulnerability,

don’t ever write your own SQL statements with string formatting!

string sql = String.Format(

@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)

VALUES (‘{0}’, ‘{1}’, ‘{2}’, ‘{3}’, ‘{4}’, ‘{5}’, ‘{6}’, ‘{7}’)",

userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);

var cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

Because this call to Close() is not wrapped in a try/catch/finally clause,

it could be missed if an exception occurs above. Don’t do this!

conn.Close();

代码中的问题

---

上面，我们使用再普通不过的ADO.NET方式实现数据写入功能，但大家是否发现代码存在问题或可以改进的地方呢？

首先，我们在客户端代码中，创建一个数据库连接，它需要占用一定的系统资源，当操作完毕之后我们需要释放占用的系统资源，当然，我们可以手动释放资源，具体实现如下：

Creates a database connection.

var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN1”].ToString());

conn.Open();

This is a massive SQL injection vulnerability,

don’t ever write your own SQL statements with string formatting!

string sql = String.Format(

@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)

VALUES (‘{0}’, ‘{1}’, ‘{2}’, ‘{3}’, ‘{4}’, ‘{5}’, ‘{6}’, ‘{7}’)",

userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);

var cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

If throws an exception on cmd dispose.

cmd.Dispose();

conn can’t be disposed.

conn.Close();

conn.Dispose();

假如，在释放SqlCommand资源时抛出异常，那么在它后面的资源SqlConnection将得不到释放。我们仔细想想当发生异常时，可以通过try/catch捕获异常，所以无论是否发生异常都可以使用finally检查资源是否已经释放了，具体实现如下：

SqlCommand cmd = null;

SqlConnection conn = null;

try

{

Creates a database connection.

conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN1”].ToString());

conn.Open();

This is a massive SQL injection vulnerability,

don’t ever write your own SQL statements with string formatting!

string sql = String.Format(

@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)

VALUES (‘{0}’, ‘{1}’, ‘{2}’, ‘{3}’, ‘{4}’, ‘{5}’, ‘{6}’, ‘{7}’)",

userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);

cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

}

finally

{

Regardless of whether there is an exception,

we will dispose the resource.

if (cmd != null) cmd.Dispose();

if (conn != null) conn.Dispose();

}

通过上面的finally方式处理了异常情况是很普遍的，但为了更安全释放资源，使得我们增加了finally和if语句，那么是否有更简洁的方法实现资源的安全释放呢？

其实，我们可以使用using语句实现资源的释放，具体实现如下：

using语句：定义一个范围，将在此范围之外释放一个或多个对象。

string sql = String.Format(

@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)

VALUES (‘{0}’, ‘{1}’, ‘{2}’, ‘{3}’, ‘{4}’, ‘{5}’, ‘{6}’, ‘{7}’)",

userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);

Creates a database connection.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN1”].ToString()))

using (var cmd = new SqlCommand(sql, conn))

{

Your code here.

}

上面的代码使用了using语句实现资源的释放，那么是否所有对象都可以使用using语句实现释放呢？

只有类型实现了IDisposable接口并且重写Dispose()方法可以使用using语句实现资源释放，由于SqlConnection和SqlCommand实现了IDisposable接口，那么我们可以使用using语句实现资源释放和异常处理。

在客户端代码中，我们使用拼接SQL语句方式实现数据写入，由于SQL语句是动态执行的，所以恶意用户可以通过拼接SQL的方式实施SQL注入攻击。

对于SQL注入攻击，我们可以通过以下方式防御：

• 正则表达校验用户输入

• 参数化存储过程

• 参数化SQL语句

• 添加数据库新架构

• LINQ to SQL

接下来，我们将通过参数化SQL语句防御SQL注入攻击，大家也可以使用其他的方法防御SQL注入攻击，具体实现代码如下：

Creates a database connection.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN1”].ToString()))

{

conn.Open();

string sql = string.Format(

@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email,

user_status,display_name, user_url, user_activation_key)");

using (var cmd = new SqlCommand(sql, conn))

{

Parameterized SQL to defense injection attacks

cmd.Parameters.Add(“@user_login”, userLogin);

cmd.Parameters.Add(“@user_pass”, userPass);

cmd.Parameters.Add(“@user_nicename”, userNicename);

cmd.Parameters.Add(“@user_email”, userEmail);

cmd.Parameters.Add(“@user_status”, userStatus);

cmd.Parameters.Add(“@display_name”, displayName);

cmd.Parameters.Add(“@user_url”, userUrl);

cmd.Parameters.Add(“@user_activation_key”, userActivationKey);

cmd.ExecuteNonQuery();

}

}

上面通过参数化SQL语句和using语句对代码进行改进，现在代码的可读性更强了，而且也避免了SQL注入攻击和资源释放等问题。

接下来，让我们简单的测试一下代码执行时间，首先我们在代码中添加方法Stopwatch.StartNew()和Stopwatch.Stop()来计算写入代码的执行时间，具体代码如下：

calc insert 10000 records consume time.

var sw = Stopwatch.StartNew();

Creates a database connection.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN2”].ToString()))

{

conn.Open();

int cnt = 0;

while (cnt++ < 10000)

{

string sql = string.Format(@"INSERT INTO jk_users

(user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)

VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key)");

using (var cmd = new SqlCommand(sql, conn))

{

Parameterized SQL to defense injection attacks

cmd.Parameters.Add(“@user_login”, userLogin);

cmd.Parameters.Add(“@user_pass”, userPass);

cmd.Parameters.Add(“@user_nicename”, userNicename);

cmd.Parameters.Add(“@user_email”, userEmail);

cmd.Parameters.Add(“@user_status”, userStatus);

cmd.Parameters.Add(“@display_name”, displayName);

cmd.Parameters.Add(“@user_url”, userUrl);

cmd.Parameters.Add(“@user_activation_key”, userActivationKey);

cmd.ExecuteNonQuery();

}

}

}

sw.Stop();

}

上面，我们往数据库中写入了10000条数据，执行时间为 7.136秒（我的机器很破了），这样系统性能还是可以满足许多公司的需求了。

假如，用户请求量增大了，我们还能保证系统能满足需求吗？事实上，我们不应该满足于现有的系统性能，因为我们知道代码的执行效率还有很大的提升空间。

接下来，将进一步介绍代码改善的方法。

图2 数据写入Users表

为了使数据库获得更快的写入速度，我们必须了解数据库在进行写入操作时的主要耗时。

数据库性能开销

---

连接时间

当我们执行conn.Open()时，首先，必须建立物理通道（例如套接字或命名管道），必须与服务器进行初次握手，必须分析连接字符串信息，必须由服务器对连接进行身份验证，必须运行检查以便在当前事务中登记，等等

这一系列操作可能需要一两秒钟时间，如果我们每次执行conn.Open()都有进行这一系列操作是很耗费时间的，为了使打开的连接成本最低，ADO.NET使用称为连接池的优化方法。

连接池：减少新连接需要打开的次数，只要用户在连接上调用 Open()方法，池进程就会检查池中是否有可用的连接，如果某个池连接可用，那么将该连接返回给调用者，而不是创建新连接；应用程序在该连接上调用 Close()或Dispose() 时，池进程会将连接返回到活动连接池集中，而不是真正关闭连接，连接返回到池中之后，即可在下一个 Open 调用中重复使用。

解析器的开销

当我们向SQL Server传递SQL语句INSERT INTO …时，它需要对SQL语句进行解析，由于SQL Server解析器执行速度很快，所以解析时间往往是可以忽略不计，但我们仍然可以通过使用存储过程，而不是直SQL语句来减少解析器的开销。

数据库连接

为了提供ACID（事务的四个特性），SQL Server必须确保所有的数据库更改是有序的。它是通过使用锁来确保该数据库插入、删除或更新操作之间不会相互冲突（关于数据库的锁请参考这里）。

由于，大多数数据库都是面向多用户的环境，当我们对User表进行插入操作时，也许有成千上百的用户也在对User表进行操作，所以说，SQL Server必须确保这些操作是有序进行的。

那么，当SQL Server正在做所有这些事情时，它会产生锁，以确保用户获得有意义的结果。SQL Server保证每条语句执行时，数据库是完全可预测的（例如：预测SQL执行方式）和管理锁都需要耗费一定的时间。

约束处理

在插入数据时，每个约束（如：外键、默认值、SQL CHECK等）需要额外的时间来检测数据是否符合约束；由于SQL Server为了保证每个插入、更新或删除的记录都符合约束条件，所以，我们需要考虑是否应该在数据量大的表中增加约束条件。

Varchar

VARCHAR是数据库常用的类型，但它也可能导致意想不到的性能开销；每次我们存储可变长度的列，那么SQL Server必须做更多的内存管理；字符串可以很容易地消耗数百字节的内存的，如果我们在一个VARCHAR列中设置索引，那么SQL Server执行B-树搜索时，就需要进行O(字符串长度)次比较，然而，整数字段比较次数只受限于内存延迟和CPU频率。

磁盘IO

SQL Server最终会将数据写入到磁盘中，首先，SQL Server把数据写入到事务日志中，当执行备份时，事务日志会合并到永久的数据库文件中；这一系列操作由后台完成，它不会影响到数据查询的速度，但每个事物都必须拥有属于自己的磁盘空间，所以我们可以通过给事务日志和主数据文件分配独立的磁盘空间减少IO开销，当然，最好解决办法是尽可能减少事务的数量。

正如大家所看到的，我们通过优化联接时间、 解析器的开销、 数据库联接、约束处理,、Varchar和磁盘IO等方法来优化数据库，接下来，我们将对前面的例子进行进一步的优化。

使用存储过程

---

前面例子中，我们把SQL代码直接Hardcode在客户端代码中，那么，数据库就需要使用解析器解析客户端中SQL语句，所以我们可以改用使用存储过程，从而，减少解析器的时间开销；更重要的一点是，由于SQL是动态执行的，所以我们修改存储过程中的SQL语句也无需重新编译和发布程序。

User表中的字段user_registered设置了默认值（GETDATE()），那么我们通过消除表默认值约束来提高系统的性能，简而言之，我们需要提供字段user_registered的值。

接下来，让我们省去User表中的默认值约束和增加存储过程，具体代码如下：

– =============================================

– Author: JKhuang

– Create date: 08/16/2012

– Description: Creates stored procedure to insert

– data into table jk_users.

– =============================================

ALTER PROCEDURE [dbo].[SP_Insert_jk_users]

@user_login varchar(60),

@user_pass varchar(64),

@user_nicename varchar(50),

@user_email varchar(100),

@user_url varchar(100),

@user_activation_key varchar(60),

@user_status int,

@display_name varchar(250)

AS

BEGIN

SET NOCOUNT ON;

– The stored procedure allows SQL server to avoid virtually all parser work

INSERT INTO jk_users

(user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key, user_registered)

VALUES (@user_login, @user_pass, @user_nicename, @user_email, @user_status, @display_name, @user_url, @user_activation_key, GETDATE());

END

上面我们定义了存储过程SP_Insert_jk_users向表中插入数据，当我们重新执行代码时，发现数据插入的时间缩短为6.7401秒。

图3数据写入时间

使用数据库事务

---

想想数据是否可以延长写入到数据库中，是否可以批量地写入呢？如果允许延迟一段时间才写入到数据库中，那么我们可以使用Transaction来延迟数据写入。

数据库事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。 SQL Server确保事务执行成功后，数据写入到数据库中，反之，事务将回滚。

如果我们对数据库进行十次独立的操作，那么SQL Server就需要分配十次锁开销，但如果把这些操作都封装在一个事务中，那么SQL Server只需要分配一次锁开销。

calc insert 10000 records consume time.

var sw = Stopwatch.StartNew();

Creates a database connection.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[“SQLCONN2”].ToString()))

{

conn.Open();

int cnt = 0;

SqlTransaction trans = conn.BeginTransaction();

while (cnt++ < 10000)

{

using (var cmd = new SqlCommand(“SP_Insert_jk_users”, conn))

{

Parameterized SQL to defense injection attacks

cmd.CommandType = CommandType.StoredProcedure;

Uses transcation to batch insert data.

To avoid lock and connection overhead.

cmd.Transaction = trans;

cmd.Parameters.Add(“@user_login”, userLogin);

cmd.Parameters.Add(“@user_pass”, userPass);

cmd.Parameters.Add(“@user_nicename”, userNicename);

cmd.Parameters.Add(“@user_email”, userEmail);

cmd.Parameters.Add(“@user_status”, userStatus);

cmd.Parameters.Add(“@display_name”, displayName);

cmd.Parameters.Add(“@user_url”, userUrl);

cmd.Parameters.Add(“@user_activation_key”, userActivationKey);

cmd.ExecuteNonQuery();

}

}

If no exception, commit transcation.

trans.Commit();

}

sw.Stop();

}

图4 数据写入时间

使用SqlBulkCopy

---

通过使用事务封装了写入操作，当我们重新运行代码，发现数据写入的速度大大提高了，只需4.5109秒，由于一个事务只需分配一次锁资源，减少了分配锁和数据库联接的耗时。

当然，我们可以也使用SqlBulkCopy实现大量数据的写入操作，首先我们创建数据行，然后使用SqlBulkCopy的WriteToServer()方法将数据行批量写入到表中，具体实现代码如下：

///

/// Gets the data rows.

///

///

DataRow[] GetDataRows(int rowCnt)

最后

即使是面试跳槽，那也是一个学习的过程。只有全面的复习，才能让我们更好的充实自己，武装自己，为自己的面试之路不再坎坷！今天就给大家分享一个Github上全面的Java面试题大全，就是这份面试大全助我拿下大厂Offer，月薪提至30K！

我也是第一时间分享出来给大家，希望可以帮助大家都能去往自己心仪的大厂！为金三银四做准备！
一共有20个知识点专题，分别是：

Dubbo面试专题

JVM面试专题

Java并发面试专题

Kafka面试专题

MongDB面试专题

MyBatis面试专题

MySQL面试专题

Netty面试专题

RabbitMQ面试专题

Redis面试专题

Spring Cloud面试专题

SpringBoot面试专题

zookeeper面试专题

常见面试算法题汇总专题

计算机网络基础专题

设计模式专题

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
-fG8dduPR-1714767686968)]

MyBatis面试专题

[外链图片转存中…(img-m9s7ilRI-1714767686968)]

MySQL面试专题

[外链图片转存中…(img-SOK1xJTv-1714767686969)]

Netty面试专题

[外链图片转存中…(img-Ot8Uy6oA-1714767686969)]

RabbitMQ面试专题

[外链图片转存中…(img-26eSYOpD-1714767686969)]

Redis面试专题

[外链图片转存中…(img-kV8EOvxS-1714767686969)]

Spring Cloud面试专题

[外链图片转存中…(img-xffzTmL5-1714767686969)]

SpringBoot面试专题

[外链图片转存中…(img-0WXmVk8X-1714767686969)]

zookeeper面试专题

[外链图片转存中…(img-5PvKhglF-1714767686970)]

常见面试算法题汇总专题

[外链图片转存中…(img-BTFF9Rag-1714767686970)]

计算机网络基础专题

[外链图片转存中…(img-zGiXQRs0-1714767686970)]

设计模式专题

[外链图片转存中…(img-jiJApRVG-1714767686970)]
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！