公海赌船网址正文转自,大家曾经把User表设计好了

本文转自:http://www.cnblogs.com/rush/archive/2012/08/31/2666090.html

1.1.1 摘要

  在开辟进度中,大家日常会越过系统品质瓶颈难题,而引起这一标题原因能够多多,有十分大可能率是代码相当不足神速、有极大希望是硬件或互连网难题,也是有极大可能率是数据库设计的标题。

  本篇博文将针对有的常用的数据库品质调休方法开展介绍,而且,为了编写制定高效的SQL代码,大家要求调节一些骨干代码优化的技术,所以,大家将从部分基本优化本事进行介绍。

1.1.1 摘要

在支付进程中,我们日常会遇见系统个性瓶颈难题,而引起这一标题由来能够多多,有不小可能率是代码远远不够飞快、有异常的大概率是硬件或网络难点,也会有望是数据库设计的标题。

本篇博文将本着部分常用的数据库质量调休方法进行介绍,而且,为了编写制定高效的SQL代码,大家供给调控一些骨干代码优化的手艺,所以,大家将从部分基本优化本领实行介绍。

  本文目录

本文目录

  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图1
Users表设计

  下边,大家定义了Users表,它富含账户名、密码、显示名称和登记日期等12个字段,个中,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();

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
)

公海赌船网址 2

图1 Users表设计

地点,大家定义了Users表,它含有账户名、密码、呈现名称和挂号日期等13个字段,当中,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秒(笔者的机器很破了),那样系统品质依旧得以满足广大商家的须求了。

  借使,用户央求量增大了,我们还是能够有限支撑系统能满意需要呢?事实上,大家不应有满意于现成的种类质量,因为我们知道代码的实行功用还会有一点都不小的进级空间。

  接下去,将尤其介绍代码改革的办法。

公海赌船网址 3图2
数据写入Users表

  为了使数据库获得更加快的写入速度,咱们必须询问数据库在开始展览写入操作时的要害耗时。

代码中的难点

上边,我们利用再常见可是的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();
}

上边,大家往数据库中写入了一千0条数据,实践时间为
7.136秒(小编的机器很破了),那样系统品质依然得以满意众多商家的须要了。

若是,用户央求量增大了,大家还是能够有限帮衬系统能满意须求呢?事实上,大家不应有满意于现存的系统性子,因为我们清楚代码的试行功效还大概有比较大的晋升空间。

接下去,将进而介绍代码改良的方法。

公海赌船网址 4

图2 多少写入Users表

为了使数据库得到更加快的写入速度,大家必须询问数据库在进展写入操作时的机要耗费时间。

  数据库品质花费

数据库质量开支

  连接时间

  当大家施行conn.Open()时,首先,必须树立物理通道(比如套接字或命名管道),必须与服务器进行初次握手,必须解析连接字符串消息,必须由服务器对延续实行身份验证,必须运转物检疫查以便在当前事情中注册,等等

  这一多种操作大概要求一两分钟时间,假使大家每一次实践conn.Open()都有拓展这一层层操作是很耗时的,为了使张开的一而再费用最低,ADO.NET使用称为连接池的优化措施。

  连接池:减少新连接须要张开的次数,只要用户在连接上调用Open()方法,池进度就能检讨池中是或不是有可用的连日,若是某些池连接可用,那么将该连接重临给调用者,而不是创制新连接;应用程序在该连接上调用Close()Dispose()时,池进度会将连接重回到运动连接池集中,而不是真的关闭连接,连接重临到池中之后,就可以在下一个Open调用中重复使用。

连接时间

当大家实行conn.Open()时,首先,必须树立物理通道(比如套接字或命名管道),必须与服务器进行初次握手,必须剖析连接字符串消息,必须由服务器对连年实行身份验证,必须运转物检疫查以便在前段时间工作中注册,等等

这一种类操作可能须要一两分钟时间,假若我们每一遍实践conn.Open()都有进展这一多种操作是很耗时的,为了使展开的连日花费最低,ADO.NET使用称为连接池的优化措施。

连接池:收缩新连接须要开发的次数,只要用户在一连上调用
Open()方法,池进度就能够检查池中是或不是有可用的接连,如若某些池连接可用,那么将该连接再次来到给调用者,而不是创办新连接;应用程序在该连接上调用
Close()Dispose()
时,池进度会将接连重临到移动连接池聚集,而不是确实关闭连接,连接再次来到到池中之后,就能够在下一个Open 调用中重复使用。

  解析器的支付

  当大家向SQL Server传递SQL语句INSERT INTO
…时,它供给对SQL语句举行分析,由于SQL
Server解析器
施行进程高速,所以深入分析时间往往是能够忽略不计,但我们还能够因而选取存款和储蓄进度,而不是直SQL语句来减弱解析器的费用。

深入分析器的支付

当大家向SQL Server传递SQL语句INSERT INTO
…时,它需求对SQL语句举行剖判,由于SQL
Server解析器
实践进度高速,所以分析时间往往是可以忽略不计,但大家还能够由此选拔存款和储蓄进程,而不是直SQL语句来收缩剖析器的费用。

  数据库连接

  为了提供ACID(事务的五天性子),SQL
Server必须保证全部的数据库改动是逐步的。它是经过行使锁来保障该数据库插入、删除或更新操作之间不会互相争辩(关于数据库的锁请参考这里)。

  由于,大大多数据库都以面向多用户的遭受,当我们对User表举办插队操作时,也有成千上百的用户也在对User表实行操作,所以说,SQL
Server必须有限支撑那几个操作是严守原地张开的。

  那么,当SQL
Server正在做有所那个事情时,它会产生锁,以保障用户获得有意义的结果。SQL
Server保障每条语句实施时,数据库是截然可预测的(举例:预测SQL执市场价格势)和管理锁都亟需消耗一定的小运。

数据库连接

为了提供ACID(事务的几本性格),SQL
Server必须确认保证全数的数据库改造是雷打不动的。它是透过利用锁来担保该数据库插入、删除或更新操作之间不会互相争辩(关于数据库的锁请参谋这里)。

由于,大诸多数据库都以面向多用户的条件,当大家对User表进行扦插操作时,也是有成千上百的用户也在对User表进行操作,所以说,SQL
Server必须确定保障那个操作是有序进行的。

那就是说,当SQL
Server正在做有所那些业务时,它会时有爆发锁,以担保用户获得有含义的结果。SQL
Server保障每条语句试行时,数据库是全然可预测的(举例:预测SQL实施措施)和管理锁都亟需开销一定的日子。

  约束管理

  在插入数据时,每一种约束(如:外键、暗许值、SQL
CHECK等)须求十分的时间来检查测量检验数据是还是不是切合约束;由于SQL
Server为了保证各个插入、更新或删除的记录都合乎约束原则,所以,大家须要思考是否合宜在数据量大的表中扩充约束标准。

自律管理

在插入数据时,各个约束(如:外键、暗中同意值、SQL
CHECK等)须求卓殊的时日来检测数据是还是不是吻合约束;由于SQL
Server为了保证各种插入、更新或删除的笔录都适合约束原则,所以,我们要求思虑是否合宜在数据量大的表中扩张约束原则。

  Varchar

  VARCHARAV4是数据库常用的类型,但它也大概引致意外的性质耗费;每一次大家存款和储蓄可变长度的列,那么SQL
Server必须做越来越多的内部存款和储蓄器处理;字符串能够很轻巧地消耗数百字节的内部存款和储蓄器的,要是大家在三个VARCHAEnclave列中设置索引,那么SQL
Server推行B-树找寻时,就要求进行O(字符串长度)次相比,不过,整数字段相比较次数只受限于内部存款和储蓄器延迟和CPU频率。

Varchar

VARCHATucson是数据库常用的档期的顺序,但它也也许导致意外的性质费用;每一回大家存款和储蓄可变长度的列,那么SQL
Server必须做越来越多的内部存储器管理;字符串能够很轻巧地消耗数百字节的内存的,假诺大家在一个VARCHAPAJERO列中装置索引,那么SQL
Server实施B-树找寻时,就必要打开O(字符串长度)次相比较,然则,整数字段比较次数只受限于内部存款和储蓄器延迟和CPU频率。

  磁盘IO

  SQL Server最终会将数据写入到磁盘中,首先,SQL
Server把多少写入到业务日志中,当实践备份时,事务日志相会併到世代的数据库文件中;这一名目大多操作由后台实现,它不会潜移暗化到数码查询的快慢,但各种事物都必须具有属于本人的磁盘空间,所以大家得以经过给业务日志和主数据文件分配独立的磁盘空间减弱IO开销,当然,最棒化解办法是尽可能收缩事务的数
量。

  正如我们所看到的,我们透过优化联接时间、 解析器的支付、
数据库联网、约束管理,、Varchar和磁盘IO等方法来优化数据库,接下去,我们将对日前的例证举行进一步的优化。

磁盘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秒。

公海赌船网址 5图3数据写入时间

运用存款和储蓄进度

前方例子中,我们把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秒。

公海赌船网址 6

图3多少写入时间

  使用数据库事务

  想想数据是不是足以延长写入到数据库中,是或不是可以批量地写入呢?如果同意延迟一段时间才写入到数据库中,那么大家能够动用Transaction来推迟数据写入。

  数据库事务是数据库管理类别实践进程中的四个逻辑单位,由二个点滴的数据库操作类别构成。
SQL Server确认保障业务实践成功后,数据写入到数据库中,反之,事务将回滚。

  尽管大家对数据库实行十三回单独的操作,那么SQL
Server就须要分配10次锁花费,但只要把这个操作都封装在七个事务中,那么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();
}

公海赌船网址 7图4
数据写入时间

利用数据库事务

研商数据是或不是能够延长写入到数据库中,是不是足以批量地写入呢?要是同意延迟一段时间才写入到数据库中,那么我们能够应用Transaction来推迟数码写入。

数据库事务是数据库管理连串施行进程中的一个逻辑单位,由三个个别的数据库操作种类构成。
SQL Server确认保障业务实行成功后,数据写入到数据库中,反之,事务将回滚。

只要大家对数据库进行10次独自的操作,那么SQL
Server就必要分配12回锁开销,但一旦把那些操作都封装在三个思想政治工作中,那么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();
}

公海赌船网址 8

图4 数据写入时间

  使用SqlBulkCopy

  通过采用工作封装了写入操作,当大家重国民党的新生活运动行代码,发掘数目写入的进度大大升高了,只需4.5109秒,由于二个事务只需分配二回锁财富,减弱了分红锁和数据库联网的耗费时间。

  当然,大家能够也选取SqlBulkCopy完成大气数额的写入操作,具体贯彻代码如下:

var sw = Stopwatch.StartNew();
//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    using (var bulkCopy = new SqlBulkCopy(conn))
    {
        //// Maping the data columns.
        bulkCopy.ColumnMappings.Add("user_login", "user_login");
        bulkCopy.ColumnMappings.Add("user_pass", "user_pass");
        bulkCopy.ColumnMappings.Add("user_nicename", "user_nicename");
        bulkCopy.ColumnMappings.Add("user_email", "user_email");
        bulkCopy.ColumnMappings.Add("user_url", "user_url");
        bulkCopy.ColumnMappings.Add("user_registered", "user_registered");
        bulkCopy.ColumnMappings.Add("user_activation_key", "user_activation_key");
        bulkCopy.ColumnMappings.Add("user_status", "user_status");
        bulkCopy.ColumnMappings.Add("display_name", "display_name");
        bulkCopy.DestinationTableName = "dbo.jk_users";
        //// Insert data into datatable.
        bulkCopy.WriteToServer(dataRows);
    }
    sw.Stop();
}

公海赌船网址 9图5
数据写入时间

  上边,大家经过作业和SqlBulkCopy实现多少批量写入数据库中,但实在,每一趟我们调用cmd.ExecuteNonQuery()方法都会发生叁个往来新闻,从客户端应用程序到数据库中,所以我们想是否留存一种办法只发送壹遍新闻就产生写入的操作呢?

使用SqlBulkCopy

由此运用工作封装了写入操作,当我们再一次运转代码,开掘数目写入的快慢大大升高了,只需4.5109秒,由于多少个业务只需分配贰遍锁能源,收缩了分配锁和数据库联网的耗费时间。

当然,大家得以也选用SqlBulkCopy实现大气数额的写入操作,首先大家创造数量行,然后利用SqlBulkCopy的WriteToServer()方法将数据行批量写入到表中,具体达成代码如下:

/// <summary>
/// Gets the data rows.
/// </summary>
/// <returns></returns>
DataRow[] GetDataRows(int rowCnt)
{
    //// Creates a custom table.
    var dt = new DataTable("jk_users");
    dt.Columns.Add(new DataColumn("user_login", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_pass", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_nicename", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_email", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_url", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_registered", typeof(System.DateTime)));
    dt.Columns.Add(new DataColumn("user_activation_key", typeof(System.String)));
    dt.Columns.Add(new DataColumn("user_status", typeof(System.Int32)));
    dt.Columns.Add(new DataColumn("display_name", typeof(System.String)));

    //// Initializes data row.
    var dr = dt.NewRow();
    dr["user_login"] = "JK_RUSH";
    dr["user_pass"] = "D*<1C2jK#-";
    dr["user_nicename"] = "JK";
    dr["user_email"] = "jkhuang@gamil.com";
    dr["user_status"] = 1;
    dr["display_name"] = "JK_RUSH";
    dr["user_url"] = "http://www.cnblogs.com/rush";
    dr["user_activation_key"] = "347894102386";
    dr["user_registered"] = DateTime.Now;

    //// Creates data row array.
    var dataRows = new DataRow[rowCnt];
    for (int i = 0; i < rowCnt; i++)
    {
        dataRows[i] = dr;
    }

    return dataRows;
}

面前,我们定义了GetDataRows()方法用来创制数量行,首先大家创建了贰个自定义表,给该表增加相应的数据列,这里大家把数据列都命名称为相应于表中列名,当然,名字能够分歧,那时大家就有贰个疑难了,那么数据库如何把自定义数据列和表中数据列对应起来吧?其实,大家必要调用ColumnMappings.Add方法创造起自定义数据列和表中数据列的应和关系,接下去,我们调用SqlBulkCopy的WriteToServer()方法将数据行写入表中。

//// Creates 10001 data rows. 
var dataRows = GetDataRows(10001);
var sw = Stopwatch.StartNew();

//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    using (var bulkCopy = new SqlBulkCopy(conn))
    {
        //// Maping the data columns.
        bulkCopy.ColumnMappings.Add("user_login", "user_login");
        bulkCopy.ColumnMappings.Add("user_pass", "user_pass");
        bulkCopy.ColumnMappings.Add("user_nicename", "user_nicename");
        bulkCopy.ColumnMappings.Add("user_email", "user_email");
        bulkCopy.ColumnMappings.Add("user_url", "user_url");
        bulkCopy.ColumnMappings.Add("user_registered", "user_registered");
        bulkCopy.ColumnMappings.Add("user_activation_key", "user_activation_key");
        bulkCopy.ColumnMappings.Add("user_status", "user_status");
        bulkCopy.ColumnMappings.Add("display_name", "display_name");
        bulkCopy.DestinationTableName = "dbo.jk_users";
        //// Insert data into datatable.
        bulkCopy.WriteToServer(dataRows);
    }
    sw.Stop();
}

公海赌船网址 10

 

 

 

图5 数据写入时间

地方,我们经过作业和SqlBulkCopy落成多少批量写入数据库中,但实在,每回我们调用cmd.ExecuteNonQuery()方法都会时有发生一个往返新闻,从客户端应用程序到数据库中,所以我们想是或不是存在一种办法只发送二回音讯就到位写入的操作呢?

  使用表参数

  借使,大家利用SQL Server 2009,它提供叁个新的效果与利益表变量(Table
Parameters
)能够将全方位表数据汇聚成二个参数字传送递给存款和储蓄进度或SQL语句。它的举世瞩目品质开支是将数据汇总成参数(O(数据量))。

  今后,我们修改在此以前的代码,在SQL
Server中定义我们的表变量,具体定义如下:

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Declares a user table paramter.
-- =============================================
CREATE TYPE jk_users_bulk_insert AS TABLE (
    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)
)

  上边,大家定义了一个表参数jk_users_bulk_insert,接着大家定义四个存储进度接受表参数jk_users_bulk_insert,具体定义如下:

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Creates a stored procedure, receive
-- a jk_users_bulk_insert argument.
-- =============================================
CREATE PROCEDURE sp_insert_jk_users 
@usersTable jk_users_bulk_insert READONLY 
AS
INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, user_registered) 
SELECT user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, GETDATE() 
FROM @usersTable

  接下大家在客户端代码中,调用存款和储蓄进度还要将表作为参数格局传递给存款和储蓄进度。

var sw = Stopwatch.StartNew();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    //// Invokes the stored procedure.
    using (var cmd = new SqlCommand("sp_insert_jk_users", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        //// Adding a "structured" parameter allows you to insert tons of data with low overhead
        var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt };
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}
sw.Stop();

  以后,大家再一次施行写入操作发掘写入作用与SqlBulkCopy非常。

使用表参数

若果,我们使用SQL Server 2008,它提供三个新的效应表变量(Table
Parameters
)可以将一切表数据汇聚成贰个参数字传送递给存款和储蓄进程或SQL语句。它的注意质量成本是将数据聚焦成参数(O(数据量))。

现行反革命,大家修改之前的代码,在SQL Server中定义我们的表变量,具体定义如下:

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Declares a user table paramter.
-- =============================================
CREATE TYPE jk_users_bulk_insert AS TABLE (
    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)
)

上面,大家定义了二个表参数jk_users_bulk_insert,接着大家定义一个存款和储蓄进度接受表参数jk_users_bulk_insert,具体定义如下:

-- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Creates a stored procedure, receive
-- a jk_users_bulk_insert argument.
-- =============================================
CREATE PROCEDURE sp_insert_jk_users 
@usersTable jk_users_bulk_insert READONLY 
AS

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

SELECT user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, GETDATE() 
FROM @usersTable

收到我们在客户端代码中,调用存款和储蓄进程还要将表作为参数情势传送给存款和储蓄进度。

var sw = Stopwatch.StartNew();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    //// Invokes the stored procedure.
    using (var cmd = new SqlCommand("sp_insert_jk_users", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        //// Adding a "structured" parameter allows you to insert tons of data with low overhead
        var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt };
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}

sw.Stop();

今天,大家重新推行写入操作发现写入功能与SqlBulkCopy拾壹分。

  1.1.3 总结

  本文通过博客系统用户表设计的例子,介绍我们在统一计划进度中轻巧犯的荒谬和代码的弱项,举例:SQL注入、数据库财富自由等问题;进而使用一些常
用的代码优化才具对代码举行优化,并且通过剖判数据库写入的天性费用(连接时间、解析器、数据库连接、约束管理、VARCHAXC90和磁盘IO),大家运用存储进度、数据库事务、SqlBulkCopy和表参数等情势下跌数据库的支付。

1.1.3总结

正文通过博客系统用户表设计的例子,介绍大家在统一筹划进度中易于犯的一无所长和代码的败笔,举个例子:SQL注入、数据库资源自由等题材;进而使用部分常用的代码优化技能对代码举办优化,并且经过剖析数据库写入的性情开支(连接时间、分析器、数据库连接、约束管理、VARCHAXC90和磁盘IO),大家应用存款和储蓄进度、数据库事务、SqlBulkCopy和表参数等格局下降数据库的开垦。

  参考

  [1] http://beginner-sql-tutorial.com/sql-query-tuning.htm

  [2]
http://www.dzone.com/links/r/sql_optimization_tipsquestions.html

  [3]
http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders—a-presentation.aspx

  [4]
http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

参考

[1] http://beginner-sql-tutorial.com/sql-query-tuning.htm

[2] http://www.dzone.com/links/r/sql_optimization_tipsquestions.html

[3]
http://blackrabbitcoder.net/archive/2010/11/11/c.net-little-wonders—a-presentation.aspx

[4]
http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

关于作者:

[作者]:JK_Rush从事.NET开发和热衷于开源高性能系统设计,通过博文交流和分享经验,欢迎转载,请保留原文地址,谢谢。 [出处]: http://www.cnblogs.com/rush/ [本文基于]: 署名-非商业性使用 3.0 许可协议发布,欢迎转载,演绎,但是必须保留本文的署名 JK_Rush (包含链接),且不得用于商业目的。如您有任何疑问或者授权方面的协商,请与我联系 。

 

相关文章