SQL中自增量字段Int型 identity(1,1)与Guid作为主键的性能比较

1.概述:

　　在我们的数据库设计中，数据库的主键是必不可少的，主键的设计对整个数据库的设计影响很大.我就对自动增量字段与Guid字段的性能作一下对比,欢迎大家讨论.

2.简介:

　　1.自增量字段

      自增量字段每次都会按顺序递增，可以保证在一个表里的主键不重复。除非超出了自增字段类型的最大值并从头递增，但这几乎不可能。使用自增量字段来做主键是非常简单的，一般只需在建表时声明自增属性即可。

      自增量的值都是需要在系统中维护一个全局的数据值，每次插入数据时即对此次值进行增量取值。当在当量产生唯一标识的并发环境中，每次的增量取值都必须最此全局值加锁解锁以保证增量的唯一性。这可能是一个并发的瓶颈，会牵扯一些性能问题。

　　在数据库迁移或者导入数据的时候自增量字段有可能会出现重复.

      如果要搞分布式数据库的话，这自增量字段就有问题了。因为，在分布式数据库中，不同数据库的同名的表可能需要进行同步复制。一个数据库表的自增量值，就很可能与另一数据库相同表的自增量值重复了。

　　2.uniqueidentifier(Guid)字段

　　 在MS Sql 数据库中可以在建立表结构是指定字段类型为uniqueidentifier,并且其默认值可以使用NewID()来生成唯一的Guid(全局唯一标识符).使用NewID生成的比较随机,如果是SQL 2005可以使用NewSequentialid()来顺序生成,在此为了兼顾使用SQL 2000使用了NewID().

　　Guid:指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的，其算法是通过以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字生成。其格式为:04755396-9A29-4B8C-A38D-00042C1B9028.

　　 Guid的优点就是生成的id比较唯一,不管是导出数据还是做分步开发都不会出现问题.然而它生成的id比较长,占用的数据库空间也比较多,随着外存价格的下降,这个也无需考虑.另外Guid不便于记忆,在这方面不如自动增量字段,在作调试程序的时候不太方便。

3.测试:

　　

1.测试环境　　

     操作系统:windows server 2003 R2 Enterprise Edition Service Pack 2

　　数据库:MS SQL 2008 Express

　　CPU:Intel(R) Pentium(R) 4 CPU 3.40GHz

　　内存:DDRⅡ 667  1G

　　硬盘:WD 80G

2.数据库脚本　　

CREATE TABLE [dbo].[Table_Guid](
    [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table_Guid_Guid]  DEFAULT (newid()),
    [Value] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Table_Guid] PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO     
CREATE TABLE [dbo].[Table_Id](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_Table_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

首先看一下测试代码：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Data;

namespace GuidTest
{
    class Program
    {
        string Connnection = @"server=pc13\sql2008express;database=testDemo;Integrated Security=true;";
        static void Main(string[] args)
        {
            Program app = new Program();
            int Count = 3000;
            List<long> idList = new List<long>(10);
            List<long> guidList = new List<long>(10);

            Console.WriteLine("数据记录数为{0}", Count);
            Console.WriteLine("-----------------------------------------");

            // Guid测试;
            Console.WriteLine("Guid测试");
            Stopwatch WatchGuid = new Stopwatch();
            Console.WriteLine("开始测试");
            Console.WriteLine("测试中");
            for (int i = 0; i < 10; i++)
            {
                WatchGuid.Start();
                // app.Guid_InsertTest(Count);
                // app.Guid_ReadToTable(Count);
                //app.Guid_Count();
                //查询第3000条记录;
                app.Guid_SelectById();          
                WatchGuid.Stop();
                guidList.Add(WatchGuid.ElapsedMilliseconds);
            }
            app.PrintTimer(guidList);
            Console.WriteLine("-----------------------------------------");

            ////自动id增长测试;
            //Console.WriteLine("自动增长id测试");
            //Stopwatch WatchId = new Stopwatch();
            //Console.WriteLine("开始测试");
            //Console.WriteLine("测试中");
            //for (int i = 0; i < 10; i++)
            //{
            //    WatchId.Start();
            //    //app.Id_InsertTest(Count);
            //    //app.Id_ReadToTable(Count);
            //    //app.Id_Count();
            //    //查询第3000条记录;
            //    app.Id_SelectById();
            //    WatchId.Stop();
            //    idList.Add(WatchId.ElapsedMilliseconds);
            //}
            //app.PrintTimer(idList);
            //Console.WriteLine("-----------------------------------------");


            Console.Read();
        }

        /// <summary>
        /// 输出时间；
        /// </summary>
        /// <param name="list"></param>
        private void PrintTimer(List<long> list)
        {
            Console.WriteLine();
            long sum = 0;
            for (int i = 0; i < list.Count; i++)
            {
                Console.WriteLine(string.Format("第{0}次使用时间(毫秒)：{1}", i + 1, list[i]));
                sum += list[i];
            }
            Console.WriteLine(string.Format("平均时间(毫秒）：{0}", sum / list.Count));
            Console.WriteLine("测试结束");
        }

        /// <summary>
        /// 自动增长id测试
        /// </summary>
        private void Id_InsertTest(int count)
        {
            Console.WriteLine("自动增长id的写入数据测试");
            string InsertSql = "insert into Table_Id ([Value]) values ({0})";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                conn.Open();
                SqlCommand com = new SqlCommand();
                for (int i = 0; i < count; i++)
                {
                    com.Connection = conn;
                    com.CommandText = string.Format(InsertSql, i);
                    com.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 将数据读到Table
        /// </summary>
        private void Id_ReadToTable(int count)
        {
            Console.WriteLine("自动增长id将数据读取到DataTable的测试");
            string ReadSql = "select top " + count.ToString() + " * from Table_Id";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                Console.WriteLine("数据记录数为:{0}", ds.Tables[0].Rows.Count);
            }
        }

        /// <summary>
        /// 数据记录行数测试
        /// </summary>
        private void Id_Count()
        {
            Console.WriteLine("自动增长id数据总记录数测试");
            string ReadSql = "select Count(*) from Table_Id";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                conn.Open();
                object CountResult = com.ExecuteScalar();
                conn.Close();
                Console.WriteLine("数据记录数为:{0}", CountResult);
            }
        }

        /// <summary>
        /// 根据id查询;
        /// </summary>
        private void Id_SelectById()
        {
            Console.WriteLine("自动增长id根据id查询测试");
            string ReadSql = "select * from Table_Id where Id=" + 215000;
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                conn.Open();
                object IdResult = com.ExecuteScalar();
                Console.WriteLine("Id为{0}", IdResult);
                conn.Close();
            }
        }

        /// <summary>
        /// Guid测试;
        /// </summary>
        private void Guid_InsertTest(int count)
        {
            Console.WriteLine("Guid写入数据测试");
            string InsertSql = "insert into Table_Guid ([Value]) values ({0})";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                conn.Open();
                SqlCommand com = new SqlCommand();
                for (int i = 0; i < count; i++)
                {
                    com.Connection = conn;
                    com.CommandText = string.Format(InsertSql, i);
                    com.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Guid格式将数据库读到Table
        /// </summary>
        private void Guid_ReadToTable(int count)
        {
            Console.WriteLine("Guid将数据读取到DataTable的测试");
            string ReadSql = "select top " + count.ToString() + " * from Table_GuID";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                SqlDataAdapter adapter = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                Console.WriteLine("数据记录为:{0}", ds.Tables[0].Rows.Count);
            }
        }

        /// <summary>
        /// 数据记录行数测试
        /// </summary>
        private void Guid_Count()
        {
            Console.WriteLine("将Guid数据类型读取到DataTable测试");
            string ReadSql = "select Count(*) from Table_Guid";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                conn.Open();
                object CountResult = com.ExecuteScalar();
                conn.Close();
                Console.WriteLine("数据记录为:{0}", CountResult);
            }
        }

        /// <summary>
        /// 根据Guid查询;
        /// </summary>
        private void Guid_SelectById()
        {
            Console.WriteLine("根据Guid查询的测试");
            string ReadSql = "select * from Table_Guid where Guid='9D54E0DC-4A07-4E00-8231-25BD21655145'";
            using (SqlConnection conn = new SqlConnection(Connnection))
            {
                SqlCommand com = new SqlCommand(ReadSql, conn);
                conn.Open();
                object IdResult = com.ExecuteScalar();
                Console.WriteLine("Guid为{0}", IdResult);
                conn.Close();
            }
        }
    }
}

为了消除上面的顾虑，每次仅使用一种方式测试（每次都注释不使用的代码）。

1.1.自增Id的写入测试。

 

1.2.Guid的写入测试。

2.1.自增Id的读取到DataTable测试

2.2.Guid的读取到DataTable测试

3.1.自增Id的数据总数统计

3.2.Guid数据总数统计

4.1.自增Id的数据总数统计（手动找到第3000条数据的id,然后查询）

4.2.Guid的数据总数统计（手动找到第3000条数据的id,然后查询）

以上测试均属本人电脑上的测试。每次的测试结果都是测试好几次，然后才取其中的一组相对平均的结果。

补充（不是我不总结，其实一些实际的应用已经在上一篇中总结过了，再整理一下吧）：
      1.测试的结果Guid作为主键在以上测试的性能还是优于自动增长Id的。对于Inner join的还没有测试。
      2.对于使用那种类型作为主键，还要根据具体的需要。在数据库迁移或者导入数据的时候自增量字段有可能会出现重复,这无疑是一场恶梦，而Guid格式无疑是首选。但是，使用Guid格式比较复杂，对于程序高度比较麻烦，毕竟Guid比较难记。
      3.自动增长的Id使用的是int型或者bigint型，它们分别占用存储空间为4个字节和8个字节，Guid是uniqueidentifier类型，它占用16个字节。从存储空间上来说，自动增长的Id更节省空间。      
      4.如果要搞分布式数据库的话，这自增量字段就有问题了。因为，在分布式数据库中，不同数据库的同名的表可能需要进行同步复制。一个数据库表的自增量值，就很可能与另一数据库相同表的自增量值重复了。

      我个人还是比较喜欢使用Guid作主键，因为它比较唯一，不管是任务时候它都是唯一的，数据库的导入与导出都不会出现主键重复的现象。
我个人的一些问题：

      1.我使用的是windows Live Writer写的文章，为了粘贴代码的方便性，我使用from Visual Studio插件粘贴代码，但是如果代码中含有中文，例如注释，粘贴后，每个汉字后面都会多出一个“？”，这个问题不知道怎么解决，我通过设置编码方式还是不能解决问题。

      2.在Windows Live Writer中怎样设置代码（打包后上传后）的下载的链接。

      另外：向喜欢数据库的园友，推荐一篇：SQL Server 查询处理中的各个阶段 
      关于自动增长Id与Guid的介绍请参见：据库中使用自增量字段与Guid字段主键的性能对比

      测试代码