遇到的问题

有同事反应服务器CPU过高，一看截图基本都是100%了，my god，这可是大问题，赶紧先看看。

让同事查看系统进程，发现是SQLServer的CPU占用比较高。首先想到的是不是报表生成的时候高，因为这块之前出现过问题，关掉服务程序，还是高。难道是客户端程序引发的？但是这么多的客户端连接，难不成每个都叫人关闭，很简单，把网络断开即可。网络断开之后，CPU立马下降。那么问题到底在哪里呢，是时候祭出我们的利器了——SQLServer Profiler。

...

今天中午，有朋友叫我帮他看一下数据库，操作系统是Windows2008R2 ,数据库是SQL2008R2 64位

64G内存，16核CPU

硬件配置还是比较高的，他说服务器运行的是金蝶K3软件，数据库实例里有多个数据库

现象

他说是这几天才出现的，而且在每天的某一个时间段才会出现CPU占用高的情况

内存占用不太高，只占用了30个G

CPU占用100%

Management Studio首次出现在MSSQL2005中，到MSSQL2008中已经成为了一个更成功的产品。其中在SSMS2008中最重要的特性如下：

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引:

 .尝试下面的技巧以避免优化器错选了表扫描：

·   使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。

·   对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。

           SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

            WHERE t1.col_name=t2.col_name；

·   用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。

 1. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

      如：

      select id from t where num is null

     NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时 NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默  认值。

     不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列    就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

      此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

      select id    from t where num=0

 2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

   MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。 可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT id FROM t WHERE col LIKE 'Mich%';”这个查询将使用索引，但“SELECT id FROM t WHERE col  LIKE '%ike';”这个查询不会使用索引。

 3. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

    如：

    select id from t where num=10 or num=20

    可以这样查询： select id from t where num=10 union all select id from t where num=20

 4 .in 和 not in 也要慎用，否则会导致全表扫描，

    如：

   select id from t where num in(1,2,3)

   对于连续的数值，能用 between 就不要用 in 了：

   select id from t where num between 1 and 3

  5.下面的查询也将导致全表扫描：

       select id from t where name like '%abc%' 或者

       select id from t where name like '%abc' 或者

       若要提高效率，可以考虑全文检索。

      而select id from t where name like 'abc%' 才用到索引

 7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推 迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

    select id from t where num=@num

   可以改为强制查询使用索引： select id from t with(index(索引名)) wherenum=@num

 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

 如：

  select id from t where num/2=100

应改为:

 select id from t where num=100*2

9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

如：

select id from t where substring(name,1,3)='abc'--name

select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，

如需要生成一个空表结构：

 select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

一、业务发展驱动数据发展

　　随着网站业务的不断发展，用户量的不断增加，数据量成倍地增长，数据库的访问量也呈线性地增长。特别是在用户访问高峰期间，并发访问量突然增大，数据库的负载压力也会增大，如果架构方案不够健壮，那么数据库服务器很有可能在高并发访问负载压力下宕机，造成数据访问服务的失效，从而导致网站的业务中断，给公司和用户造成双重损失。那么，有木有一种方案能够解决此问题，使得数据库不再因为负载压力过高而成为网站的瓶颈呢？答案肯定是有的。

　　目前，大部分的主流关系型数据库都提供了主从热备功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。

　　利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。

二、MySQL数据复制原理

　　刚刚我们了解了关系型数据库的读写分离能够实现数据库的主从架构，那么主从架构中最重要的数据复制又是怎么一回事呢？MySQL作为最流行的关系型数据库之一，通过了解MySQL的数据复制流程，会使得我们对主从复制的认知会有一定的帮助。

　　从上图来看，整体上有如下三个步凑：

　　（1）Master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

　　（2）Slave将Master的二进制日志事件(binary log events)拷贝到它的中继日志(relay log)；

PS：从图中可以看出，Slave服务器中有一个I/O线程(I/O Thread)在不停地监听Master的二进制日志(Binary Log)是否有更新：如果没有它会睡眠等待Master产生新的日志事件；如果有新的日志事件(Log Events)，则会将其拷贝至Slave服务器中的中继日志(Relay Log)。

　　（3）Slave重做中继日志(Relay Log)中的事件，将Master上的改变反映到它自己的数据库中。

PS：从图中可以看出，Slave服务器中有一个SQL线程(SQL Thread)从中继日志读取事件，并重做其中的事件从而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

三、MySQL主从复制实战

3.1 实验环境总览与准备工作

　　（1）实验环境

　　①服务器环境：本次我们主要借助VMware Workstation搭建一个三台Windows Server 2003组成的MySQL服务器集群，其中一台作为Master服务器（IP：192.168.80.10），其余两台均作为Slave服务器（IP：192.168.80.11,192.168.80.12）。

　　②客户机环境：本次我们在Windows 7宿主机（IP：192.168.80.1）编写一个C#控制台程序，对MySQL服务器进行基本的CRUD访问测试。

　　（2）准备工作

　　　　下载MySQL文件：http://dev.mysql.com/downloads/mysql/5.5.html#downloads

　　　　这里我们选择5.5版本，为了节省时间，直接选择了Archive免安装版本。又由于虚拟机中的Windows Server 2003是32位，所以选择了32-bit的Archive版本进行使用。

　　　　下载完成后，将三个压缩包分别拷贝至Master（IP：192.168.80.10）、Slave1（IP：192.168.80.11）及Slave2（IP：192.168.80.12）中。

3.2 配置MySQL主服务器

　　（1）将MySQL文件拷贝到Master服务器，并解压到一个指定文件夹。这里我放在了：C:\MySQLServer\mysql-5.5.40-win32

　　（2）新建一个配置文件，取名为：my-master.ini，添加以下内容：

 1 [client]
 2 port=3306
 3 default-character-set=utf8
 4 
 5 [mysqld]
 6 port=3306
 7 
 8 #character_set_server=utf8 一定要这样写;
 9 character_set_server=utf8
10 
11 #解压目录
12 basedir=C:\MySQLServer\mysql-5.5.40-win32
13 
14 #解压目录下data目录,必须为data目录
15 datadir=C:\MySQLServer\mysql-5.5.40-win32\data
16 
17 #sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这个有问题，在创建完新用户登录时报错
18 sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
19 
20 #主服务器的配置
21 #01.开启二进制日志
22 log-bin=master-bin
23 #02.使用二进制日志的索引文件
24 log-bin-index=master.bin.index
25 #03.为服务器添加唯一的编号
26 server-id=1

　　（3）将my-master.ini传送到Master服务器中mysql所在的文件夹中，并在命令行中将其注册为Windows服务：（这里要转到mysql的bin文件夹中进行操作，因为没有设置环境变量）

　　（4）启动mysql服务，并设为自启动类型；

　　（5）使用root账号登陆mysql，创建一个具有复制权限的用户；（此时root是没有密码的，直接回车即可）

　　（6）在Slave1或Slave2上通过远程登录Master上的mysql测试新建用户是否可以登录；

3.3 配置MySQL从服务器

　　（1）同Master服务器，将MySQL文件拷贝解压到指定文件夹下；

　　（2）新建一个配置文件，取名为：my-slave.ini，添加以下内容：

[client]
port=3306
default-character-set=utf8

[mysqld]
port=3306

#character_set_server=utf8 一定要这样写;
character_set_server=utf8

#解压目录
basedir=C:\MySQLServer\mysql-5.5.40-win32

#解压目录下data目录,必须为data目录
datadir=C:\MySQLServer\mysql-5.5.40-win32\data


#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 这个有问题，在创建完新用户登录时报错
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#从服务器的配置
#01.为服务器添加唯一的编号
server-id=2
#02.开启中继日志
relay-log=slave-relay-log-bin
#03.使用中继日志的索引文件
relay-log-index=slave-relay-log-bin.index

PS：这里server-id要确保唯一，我们这里Master（192.168.80.10）的server-id=1，那么Slave1（192.168.80.11）就设置其server-id=2，Slave2（192.168.80.12）则设置其server-id=3。

　　（3）将my-slave.ini传送到Slave1和Slave2服务器中mysql所在的文件夹中，并在命令行中将其注册为Windows服务：（这里要转到mysql的bin文件夹中进行操作，因为没有设置环境变量）

　　（4）分别启动两台Slave的mysql服务，步凑同master所述；当然，也可以在cmd中输入命令：net start MySQL

　　（5）分别使用两台Slave的root账号登陆mysql，通过指定的语句配置主从关系设置；

　　（6） 为了方便后面的测试，这里我们在Master上通过root进入mysql，创建一个测试用的数据库和数据表；

　　（7）还要创建一个用户，这个用户具有对所有数据库的增删查改的权限，以便用来进行测试；

3.4 编写C#程序测试主从复制结构

　　（1）下载mysql for .net开发包，添加对mysql.data.dll的引用

　　（2）在控制台程序中写代码访问Master服务器，并查看程序运行结果；

　　①数据库连接部分：

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="mysqlmaster" 
         connectionString="server=192.168.80.10;database=dbtest;uid=sa;password=123456"/>
    </connectionStrings>
</configuration>

　　②程序代码部分：在程序中首先显示user表内容（这时表是空的），然后会添加5条user信息，其中会修改第3条user信息的name为Edison Chou，最后会删除第5条user信息；

        static void Main(string[] args)
        {
            string connStr = ConfigurationManager.ConnectionStrings["mysqlmaster"]
                .ConnectionString;
            // 01.Query
            ShowUserData(connStr);
            // 02.Add a user to table
            for (int i = 0; i < 5; i++)
            {
                AddUserData(connStr, "TestUser" + (i + 1).ToString());
            }
            ShowUserData(connStr);
            // 03.Update a user on table
            UpdateUserData(connStr, 3, "EdisonChou");
            ShowUserData(connStr);
            // 04.Delete a user from table
            DeleteUserData(connStr, 5);
            ShowUserData(connStr);

            Console.ReadKey();
        }

        #region 01.Func:ShowUserData
        private static void ShowUserData(string connStr)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select * from user";
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("------------table:user------------");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader[0] + "-" + reader[1]);
                            }
                            Console.WriteLine("------------table:user------------");
                        }
                    }
                }
            }
        }
        #endregion

        #region 02.Func:AddUserData
        private static void AddUserData(string connStr, string userName)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "insert into user(name) values('" + userName + "')";
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Add User Successfully.");
                    }
                }
            }
        }
        #endregion

        #region 03.Func:UpdateUserData
        private static void UpdateUserData(string connStr, int userId,
    string userName)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "update user set name='" + userName
                        + "' where id=" + userId;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Update User Successfully.");
                    }
                }
            }
        }
        #endregion

        #region 04.Func:DeleteUserData
        private static void DeleteUserData(string connStr, int userId)
        {
            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "delete from user where id=" + userId;
                    int result = cmd.ExecuteNonQuery();
                    if (result > 0)
                    {
                        Console.WriteLine("Delete User Successfully.");
                    }
                }
            }
        }
        #endregion

　　③程序运行结果：

　　（3）在Slave1（192.168.80.11）和Slave2（192.168.80.12）上查看user表是否自动进行了数据同步；

　　①首先在Master上查看user表还剩哪些信息？

　　②其次在Slave1上查看user表是否进行了同步：

　　③最后在Slave2上查看user表是否进行了同步：

　　（4）初步尝试读写分离：一主一从模式的一个最简单的实现方式

　　①在Slave1上新建一个只具有读（select）权限的用户，这里取名为reader：

　　　　create user reader;

　　　　grant select on *.* to reader identified by '123456';

　　②新增一个mysqlslave的数据库连接字符串：

    <connectionStrings>
        <add name="mysqlmaster" 
             connectionString="server=192.168.80.10;database=dbtest;uid=sa;password=123456"/>
        <add name="mysqlslave"
             connectionString="server=192.168.80.11;database=dbtest;uid=reader;password=123456"/>
    </connectionStrings>

　　③新增一个枚举DbCommandType来记录读操作和写操作：

    public enum DbCommandType
    {
        Read,
        Write
    }

　　④修改读取数据表的代码判断是读操作还是写操作：

     private static void ShowUserData(DbCommandType commandType)
        {
            string connStr = null;
            if (commandType == DbCommandType.Write)
            {
                connStr = ConfigurationManager.ConnectionStrings["mysqlmaster"]
                    .ConnectionString;
            }
            else
            {
                connStr = ConfigurationManager.ConnectionStrings["mysqlslave"]
                    .ConnectionString;
            }

            using (MySqlConnection con = new MySqlConnection(connStr))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select * from user";
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("------------table:user------------");
                            while (reader.Read())
                            {
                                Console.WriteLine(reader[0] + "-" + reader[1]);
                            }
                            Console.WriteLine("------------table:user------------");
                        }
                    }
                }
            }
        }

PS：关于MySQL的读写分离实现，主要有以下几种方式：

一种是基于MySQL-Proxy做调度服务器模式，另一种是借助阿里巴巴开源项目Amoeba(变形虫)项目实现（这种方式貌似用的比较多），另外呢就是自己写一个类似于哈希算法的程序库来选择目标数据库；

学习小结

　　此次我们主要简单地学习了主从复制的一些相关概念，了解了MySQL在Windows下搭建主从复制架构的过程，最后通过改变程序方式使得一主一从模式下实现读写分离（虽然是很简单很粗陋的实现）。后续有空时，我会尝试在Linux下借助阿里巴巴开源项目Amoeba搭建真正的MySQL读写分离模式，到时也会将搭建的过程分享出来。虽然，我没有相关的真实实践经验，也有很多人跟我说“你这是在纸上谈兵”，我也知道“纸上得来终觉浅，绝知此事要躬行”，但在没毕业之前，我还是会做一些相关的初步了解性质的实践学习，也许以后到了公司，就会有真正的战场在等着我了。当然，如果你觉得我写这篇博客花了点心思，那就麻烦点个赞，谢谢啦！

参考资料

　　（1）李智慧，《大型网站技术架构-核心原理与案例分析》：http://item.jd.com/11322972.html

　　（2）guisu，《高性能Mysql主从架构的复制原理及配置详解》：http://blog.csdn.net/hguisu/article/details/7325124

　　（3）Ghost，《高性能的MySQL主从复制架构》：http://www.uml.org.cn/sjjm/201211061.asp

　　（4）飞鸿无痕，《Amoeba搞定MySQL读写分离》：http://blog.chinaunix.net/uid-20639775-id-154600.html （此文讲解了如何借助Amoeba构建MySQL主从复制读写分离，值得阅读）

附件下载

　　（1）mysql-5.5.40（Archive版本）：http://pan.baidu.com/s/1c0u6X80

　　（2）相关配置文件（master与slave）：http://pan.baidu.com/s/1dDENI73

　　（3）C#测试程序DEMO：http://pan.baidu.com/s/1kT42gAz

本文从产品设计和架构角度分享了Microsoft内存数据库方面的使用经验，希望你在阅读本文之后能够了解这些新的对象、概念，从而更好地设计你的架构。

内存数据库，指的是将数据库的数据放在内存中直接操作。相对于存放在磁盘上，内存的数据读写速度要高出很多，故可以提高应用的性能。微软的SQL Server 2014已于2014年4月1日正式发布，SQL 2014一个主要的功能即为内存数据库。

下面，我将着重介绍使用SQL Server 2014内存数据库时需要注意的地方。

关于内存数据库

SQL Server 2014内存数据库针对传统的表和存储过程引入了新的结构： memory optimized table（内存优化表）和native stored procedure（本地编译存储过程）。

默认情况下Memory optimized table是完全持久的（即为durable memory optimized table），如传统的基于磁盘的表上的事务一样，并且完全持久的事务也是支持原子、一致、隔离和持久 (ACID) 的。所不同的是内存优化表的整个表的主存储是在内存中，即为从内存读取表中的行，和更新这些行数据到内存中。 并非像是传统基于磁盘的表按照数据库数据库页面装载数据库。内存优化表的数据同时还在磁盘上维护着另一个副本，但仅用于持续性目的。 在数据库恢复期间，内存优化的表中的数据再次从磁盘装载。 创建持久的内存优化表方法如下：

CREATE TABLE DurableTbl
(AccountNo     INT	    NOT NULL  PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 28713)
,CustName      VARCHAR(20)  NOT NULL
,Gender        CHAR         NOT NULL   /* M or F */
,CustGroup     VARCHAR(4)   NOT NULL   /* which customer group he/she belongs to */
,Addr          VARCHAR(50)  NULL       /* No address supplied is acceptable */
,Phone         VARCHAR(10)  NULL       /* Phone number */
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

除了默认持久的内存优化表之外，还支持non-durable memory optimized table（非持久化内存优化表），不记录这些表的日志且不在磁盘上保存它们的数据。 这意味着这些表上的事务不需要任何磁盘 IO，但如果服务器崩溃或进行故障转移，则无法恢复数据。创建非持久化内存优化表方法如下：

CREATE TABLE NonDurableTbl

(AccountNo     INT	    NOT NULL  PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 28713)
,CustName      VARCHAR(20)  NOT NULL
,Gender        CHAR         NOT NULL   /* M or F */
,CustGroup     VARCHAR(4)   NOT NULL   /* which customer group he/she belongs to */
,Addr          VARCHAR(50)  NULL       /* No address supplied is acceptable */
,Phone         VARCHAR(10)  NULL       /* Phone number */
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

Native compiled stored procedure（本地编译存储过程）是针对传统的存储过程而言的，是本机编译存储过程后生成DLL，由于本机编译是指将编程构造转换为本机代码的过程，这些代码由处理器指令组成，无需进一步编译或解释。与传统TSQL 相比，本机编译可提高访问数据的速度和执行查询的效率。故通过本机编译的存储过程，可在存储过程中提高查询和业务逻辑处理的效率。创建方法本地编译存储过程方法如下：

CREATE PROCEDURE dbo.usp_InsertNonDurableTbl
@AccountNo int,
@CustName nvarchar(20),
@Gender	char(1),
@CustGroup	varchar(4),
@Addr	varchar(50),
@Phone	varchar(10)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')      
BEGIN
	INSERT INTO [dbo].[DurableTbl]
           ([AccountNo]
           ,[CustName]
           ,[Gender]
           ,[CustGroup]
           ,[Addr]
           ,[Phone])
     VALUES (@AccountNo
           ,@CustName
           ,@Gender 
           ,@CustGroup 
           ,@Addr
           ,@Phone)
END
END
GO

内存数据库既可以包含内存优化表和本地编译存储过程，又可以包含基于磁盘的表和传统存储过程，各个对象之间数据存储、和访问的架构如下所示：

使用场景

传统基于磁盘的表，通常会遇到内存页面置换、死锁、造成了吞吐量有限、事务延迟较长等问题，内存数据库的内存优化表由于常驻内存，适用于低延迟、高并发、快速数据传输和装载等场景。各场景的使用、机制具体如下：

低延迟：由于内存优化表和本地编译存储过程直接生成DLL，本机编译可提高访问数据的速度和执行查询的效率响应速度快，作为参与处理业务逻辑的存储过程而言，大大降低了存储过程作为中间层执行和访问的效率。提高了应用的访问效率，降低了延迟性。

内存优化表的创建和装载过程如下：

本地编译存储过程的创建和装载过程如下：

对于基于磁盘的表和内存优化表，我们可以在以下示例中对比内存优化表：创建两个同样结构的表，一个为基于磁盘的表包含1700万条记录，当使用常规存储过程查询一条记录，查询时间为67ms；

另一个为内存优化表包含1亿条记录。当使用本地编译存储过程查询内存优化表，所需的执行时间不到1毫秒。

当我们进一步查看两个存储过程的执行计划，发现第一个已经使用聚集索引检索，第二个本地编译存储过程如所预期的，是基于内存优化表的索引检索。

高吞吐量：由于内存优化表直接从内存中读取、写入数据，当访问数据时，不再使用latch，故不同于基于磁盘的表，对于insert/update/delete的操作，latch争用、以及死锁问题随即消失。

与此同时，可大大提高了应用的吞吐量。 随着配置的增加，其性能呈直线上升。

快速数据传输、装载: 由于非持久化内存优化表仅常驻内存，并无基于磁盘的副本。当需要将一些外部数据通过ETL装载到内存数据库，可以使用无任何IO和logging的非持久化内存优化表作为过渡表，可有效的加快装载数据库的速度。

内存数据库设计与性能

并非所有的场景都可以利用到OLTP的内存数据库的优势，针对符合内存数据库使用场景的需求，需确定哪些对象适合转化为内存优化表和本地编辑存储过程，对于已经存在的系统的表对象，如何迁移这些对象。

选择合适的内存优化表

SQL Server 2014 提供了AMR即为Analysis, Migration and Reporting，此工具可来检测哪些基于磁盘的表和存储过程适合迁移到内存数据库中。下面的流程图给出了建议的工作流程：

经常用于做为核心基线的一些指标如下：

• SQL Server 的 CPU 占用率。
• SQL Server 的内存占用率。
• SQL Server 的 I/O 活动。
• 处理事务时，实例的事务吞吐量。

当已经确定哪些表需要调整为内存优化表，可针对内存优化顾问的“表内存优化顾问”所列出来的清单一一调整，且评估每个表对内存的使用量。

通常在实际生产环境中，为了保证服务的高可用性和数据的完整性、安全性，几乎很少有数据库为单实例结构，紧接着面临的问题是，如何实现内存数据库的高可用性。

内存数据库的高可用性

SQL 2014的内存数据库与现在有诸如群集、Alwayson、replication等高可用技术完全集成，故基于内存数据库的基础上，搭建SQL Server Alwayson Availability Group，考虑到同一数据中心带宽和网络延迟优于跨数据中心，可在同一数据中心采用同步模式作为高可用，不同数据库中心采用异步模式作为灾备。架构如下：

由于内存数据库本身常驻内存，在设计架构时需要注意不同高可用的局限性：

群集：考虑到数据库服务的高可用性，传统基于磁盘的数据库经常采用数据库群集保证应用服务的不间断性。同样内存数据库适用于数据库群集，故Active/Passive、Active/Active、以至于M/N（多个活动节点/多个被动节点）模式的群集均可考虑内存数据库，所需注意的是：

• 在故障切换时，由于内存优化表需要将所有数据装载到内存中，切换时间比基于磁盘的表时间略长。
• 非持久性内存优化表由于磁盘并未存放数据副本，在故障切换时，数据内容会被清空。

Alwayson: 在SQL 2012中出现的新功能Alwayson availability group可为数据库提供多个同步或者异步的数据库副本, 在SQL 14中内存数据库与Alwayson availability group可完全集成。依赖于Alwayson的部署向导，内存数据库可像传统数据库一样，快速加入Alwayson availability group中，所需注意的是：

• 在切换主从数据库时，切换时间较快，由于依赖于alwayson的事务日志记录的redo进程，无需从磁盘重新装载数据库到内存中。
• 若内存数据库中包含非持久性内存优化表，由于无法依赖于事务日志，非持久性内存优化表的数据仅存在于primary节点。

通常Alwayson也被使用于本地数据库的高可用性，和异地数据库的灾备场景，与内存优化表的结合在性能上，对于主从节点之间网络延迟、传递的事务的大小、以及内存数据库所在的磁盘是否较快，均可影响其性能。

Replication: 复制是将数据和数据库对象从一个数据库复制和分发到另一个数据库，然后在数据库之间进行同步以保持一致性的一种技术。内存数据库中的内存优化表可作为单向事务性复制的订阅方，所需注意的是：

• 内存优化表的行数据限制在8060 bytes一下。
• 复制订阅方的数据类型要遵循内存优化表的限制。

数据库架构设计

由于持久性内存优化表需要在服务启动时，将数据装载到内存中，这涉及对现有RTO有一定量的影响。在设计内存数据库文件组的架构时，需注意完全持久的内存优化表的大小、以及装载数据的速度。

在由架构和业务数据量确定内存优化表的大小的前提下，可通过多个Container提升内存数据库的数据装载的速度。

由于每个Container包含着检查点文件对（Checkpoint File Pairs 即为CFPs），CFP 由数据文件和差异文件构成，内存优化表中的数据存储在 CFP 中。为提高数据库服务启动时RTO，在为内存优化数据库创建多个container时，可并行处理不同Container内的检查点文件对，即为提高装载数据到内存数据库的速度。

例如创建Container可在创建数据库时创建，或者一个或多个container添加到 MEMORY_OPTIMIZED_DATA 文件组，脚本如下所示：

CREATE DATABASE InMemory_DBTest ON  
 PRIMARY (NAME = [InMemory_DB_hk_fs_data], FILENAME = 'D:\InMemory_DBTest\InMemory_DB_data.mdf'), 

 FILEGROUP [InMemory_DB_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA 
 (NAME = [InMemory_DB_fs_dir],  FILENAME = 'D:\ InMemory_DBTest\ InMemory_DB_hk_fs_dir'),
 (NAME = [InMemory_DB_fs_dir2],  FILENAME = 'D:\ InMemory_DBTest\ InMemory_DB_hk_fs_dir2'),
 (NAME = [InMemory_DB_fs_dir3],  FILENAME = 'D:\ InMemory_DBTest\ InMemory_DB_hk_fs_dir3')
 LOG ON (name = [test_log], Filename='D:\ InMemory_DBTest\ InMemory_DB.ldf', size=100MB)
 COLLATE Welsh_100_BIN2 
Go

此外，并在不同的驱动器上分配这些Container，以实现更多带宽来将数据传输到内存中。由于内存数据库引擎会根据轮询法跨Container分发数据文件和差异文件，为提高Container对磁盘的带宽的性能，应在每个磁盘均衡数据文件和差异文件。

对于设计内存优化表时，需要考虑bucket的数量，一般来讲建议bucket的数量为预估表记录的1-2倍。

相对于磁盘，内存的数据读写速度要高出几个数量级，将数据保存在内存中相比从磁盘上访问能够极大地提高应用的性能。由于内存数据库是以牺牲内存资源为代价换取数据处理实时性的，以下图表显示了近些年计算机硬件（内存）飞速发展，为内存数据库的使用带来了可能性。

内存数据库在使用硬件资源与传统表有着一定的特殊性，为了提高内存数据库性能，对存储内存数据库的各方面的资源有着比传统数据库更高的要求。可参考如下具体需求：

内存：所有内存优化表是常驻内存的，因此需足够的物理内存来存储内存优化表。但这并不意味着需要将整个数据库放入内存中，而是仅将频繁访问的热数据常驻内存优化表中。且最高可以支持到256GB的数据量。

可使用如下脚本查看内存优化表的内存使用量：

select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats

磁盘：同样存在log和data两类文件。Log文件依然记录事务信息。针对于持久性的内存优化表，为了降低log IO的竞争、保证低延迟，一般建议至少SSD。

CPU: 可根据OLTP环境的负载考虑CPU的配置，如两个CPU socket支撑一个中等级别的服务器。

Network: 针对于单机的内存数据库，由于数据存储于数据库服务器的内存中，对于数据交互仍然为应用层到数据层的访问，如以往数据交互，对于网络并未有较高的依赖性。对于内存数据库应用于数据库高可用和异地灾备的情况下（如同步/异步模式的Always-on），同一数据中心的网络延迟，以及不同数据中的网络延迟对于使用与高可用性和灾备的内存数据库的事务有一定量的影响。

维护管理内存数据库

由于内存数据库对内存有着较大的依赖，在管理内存方面，可以考虑使用Resource governor来管理内存数据库。需注意如下：

• 通过指定Resource governor的hard limit（如80%）来确保其它内部资源和非内存优化表的内存使用量。
• 每个resource pool可以包含多个内存数据库，但是一个内存数据库在同一时刻只关联一个resource pool。

Memory Usage Report是SSMS自带的监控内存使用量的报表，可以快速的查看现有缓存的内存优化对象的使用情况：

备份在日常维护管理数据库中也极为重要，对持久性内存优化表，内存优化表作为数据库对象中的一部分，被包含在常规数据库备份策略中，故传统的全备、差异备份、日志备份策略无需更改，即可实现对内存优化表的备份。

毫无疑问，给表添加索引是有好处的，你要做的大部分工作就是维护索引，

在数据更改期间索引可能产生碎片，所以一些维护是必要的。碎片可能是你查询产生性能问题的来源。  

那么到底什么是索引碎片呢？索引碎片实际上有

2种形式：外部碎片和内部碎片。不管哪种碎片基本上都会影响索引内页的使用。这也许是因为页的逻辑顺序错误（即外部碎片）或每页存储的数据量少于数据页的容量（内部错误）。

无论索引

产生了哪种类型的碎片，你都会因为它而面临查询的性能问题。 

外部碎片 

当索引页不在逻辑顺序上时就会产生外部碎片。索引创建时，索引键按照逻辑顺序放在一组索引页上。当新数据插入索引时，新的键可能放在存在的键之间。为了让新的键按照正确的顺序插入，可能会创建新的索引页来存储需要移动的那些存在的键。这些新的索引页通常物理上不会和那些被移动的键原来所在的页相邻。创建新页的过程会引起索引页偏离逻辑顺序。 

下面的例子将比实际的言论更加清晰的解释这个概念。 

假定在任何另外的数据插入你的表之前存在索引上的结构如下 

（注：下面图片里应该是7和8，原文里是6和8）：

INSERT语句往索引里添加新的数据，假定添加的是5。

INSERT将引起新页创建，为了给5在原来的页上留出空间，7和8被移到了新页上。这个创建将引起索引页偏离逻辑顺序。 

在有特定搜索或者返回无序结果集的查询的情况下，偏离顺序的索引页不会引起问题。对于返回有序结果集的查询，搜索那些无序的索引页需要进行额外的处理。有序结果集的例子如查询返回4到10之间的记录。为了返回

7和8，查询不得不进行额外的页切换。虽然一个额外的页切换在一个长时间运行里是无关紧要的，然而想象一下一个有好几百页偏离顺序的非常大的表的情形。 

内部碎片 

当索引页没有用到最大量时就产生了内部碎片。虽然在一个有频繁数据插入的应用程序里这也许有帮助，然而设置一个fill factor（填充因子）会在索引页上留下空间，服务器内部碎片会导致索引尺寸增加，从而在返回需要的数据时要执行额外的读操作。这些额外的读操作会降低查询的性能。 

怎样确定索引是否有碎片？

SQLServer提供了一个数据库命令――DBCC SHOWCONTIG――来确定一个指定的表或索引是否有碎片。  

DBCC SHOWCONTIG 数据库平台命令，用来显示指定的表的数据和索引的碎片信息。 

DBCC SHOWCONTIG 权限默认授予 

sysadmin固定服务器角色或 db_owner 和 db_ddladmin

固定数据库角色的成员以及表的所有者且不可转让。

语法（SQLServer2008） 

DBCC SHOWCONTIG  

[ ( { table_name | table_id| view_name | view_id }  

[ , index_name | index_id ]  

)  

]  

[ WITH { ALL_INDEXES  

| FAST [ , ALL_INDEXES ]  

| TABLERESULTS [ , { ALL_INDEXES } ]  

[ , { FAST | ALL_LEVELS } ]  

} 

]

语法（

SQLServer7.0

） 

DBCC SHOWCONTIG  

[ ( table_id [,index_id ]  

)  

]

示例： 

显示数据库里所有索引的碎片信息 

SET NOCOUNT ON 

USE pubs 

DBCC SHOWCONTIG WITH ALL_INDEXES 

GO 

显示指定表的所有索引的碎片信息 

SET NOCOUNT ONUSE pubs 

DBCC SHOWCONTIG (authors) WITH ALL_INDEXES 

GO 

显示指定索引的碎片信息 

SET NOCOUNT ON 

USE pubs 

DBCC SHOWCONTIG (authors,aunmind) 

GO 

结果集  

DBCC SHOWCONTIG

将返回扫描页数、扫描扩展盘区数、遍历索引或表的页时，DBCC 语句从一个扩展盘区移动到其它扩展盘区的次数、每个扩展盘区的页数、扫描密度

（最佳值是指在一切都连续地链接的情况下，扩展盘区更改的理想数目）。 

DBCC SHOWCONTIG 

正在扫描 'authors' 表...表

: 'authors'

（

1977058079

）； 

索引

 ID: 1

，数据库 ID: 5 已执行 TABLE 级别的扫描。 

- 

扫描页数.....................................: 1

- 

扫描扩展盘区数...............................: 1

- 

扩展盘区开关数...............................: 0 

- 

每个扩展盘区上的平均页数.....................: 1.0 

- 

扫描密度［最佳值:实际值］....................: 100.00%［1:1］ 

逻辑扫描碎片.................................: 0.00% 

扩展盘区扫描碎片.............................: 0.00%  

每页上的平均可用字节数.......................: 6010.0 

平均页密度（完整）...........................: 25.75% 

DBCC 执行完毕。如果 DBCC 

输出了错误信息，请与系统管理员联系。 

寻找什么 

扫描页数：

如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数如果明显比你估计的页数要高说明存在内部碎片。 

扫描扩展盘区数：

用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG

返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，

说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。 

扩展盘区开关数：

该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。 

每个扩展盘区上的平均页数：

该数是扫描页数除以扫描扩展盘区数，一般是8小于8说明有外部碎片。 

扫描密度［最佳值实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则

说明有外部碎片。 

逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。 

扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。

该百分比应该是0％，高了则说明有外部碎片。 

每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor

（填充因子）。 

平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比

说明有内部碎片。 

备注 

DBCC SHOWCONTIG

实际上仅对那些大表有用。小表显示的结果根本不符合正常标准，因为他们也许没有由多于8个的页面组成。你在查看小表上执行DBCC SHOWCONTIG

的结果时应该忽略一些结果。在处理小表时只需关心扩展盘区开关

数、逻辑扫描碎片、每页上的平均可用字节数、平均页密度（完整）。 

DBCC SHOWCONTIG

默认输出的结果是：扫描页数、扫描扩展盘区数、扩展盘区开

关数、每个扩展盘区上的平均页数、扫描密度［最佳值实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。可以用

FAST和TABLERESULTS选项来控制这个输出结果。FAST选项指定执行索引的快速扫描，输出结果是最小的，该选项不读索引的叶或数据页且只返回扫描页数、扫描扩展盘区数、扫描密度［最佳值:实际值］、逻辑扫描碎片。 

TABLERESULTS选项将用行集的形式显示信息，将返回扩展盘区开关数、扫描密度［最佳值:实际值］、逻辑扫描碎片、扩展盘区扫描碎片、每页上的平均可用字节数、平均页密度（完整）。如果既指定FAST选项又指定TABLERESULTS

选项，那么将返回对象名、对象ID、索引名、索引ID，页数、扩展盘区开关数、扫描密度［最佳值:实际值］和逻辑扫描碎片。   ALL_INDEXES选项将显示指定表和试图的所有索引的结果，即使指定了一个索引。  ALL_LEVELS

选项指定是否为所处理的每个索引的每个级别产生输出（默认只输出索引的页级或表数据级的结果），并且只能与 TABLERESULTS 选项一起使用。

  解决碎片问题 

一旦你确定表或索引有碎片问题，那么你有4个选择去解决那些问题： 

1. 删除并重建索引 

2. 使用DROP_EXISTING子句重建索引 

3. 执行DBCC DBREINDEX 

4. 执行DBCC INDEXDEFRAG 

尽管每一个技术都能达到你整理索引碎片的最终目的，但各有各的优缺点。 

删除并重建索引 

用DROP INDEX和CREATE INDEX或ALTER TABLE来删除并重建索引有些缺陷包括在删除重建期间索引会消失。在索引删除重建时，对于查询它不在可用，

查询性能也许会受到明显的影响，直到重建索引为止。另一个潜在的缺陷是当都请求索引的时候会引起阻塞，直到重建索引为止。通过其他的处理也能解决阻塞，就是索引被使用的时候不删除索引。另一个主要的缺陷是在用

DROP INDEX和CREATE INDEX重建聚集索引时会引起非聚集索引重建两次。

删除聚集索引时非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。 删除并重建索引的确有一个好处就是通过重新排序索引页，使索引页紧凑并删除不需要的索引页来完全重建索引。你也许需要考虑那些内部和外部碎片都很高的

死锁和堵塞一直是性能测试执行中关注的重点。

下面是我整理的监控sql server数据库，在性能测试过程中是否出现死锁、堵塞的SQL语句，还算比较准备，留下来备用。

--每秒死锁数量

--查询当前阻塞

在压力测试过程中，不间断的按F5键执行上面的SQL语句，如果出现死锁或者堵塞现象，就会在执行结果中罗列出来。如果每次连续执行SQL，都有死锁或者堵塞出现，说明死锁或者堵塞的比较严重。

先找出死锁进程的spid，sp_lock，然后用dbcc inputbuffer(spid)

项目升级数据库由SQL2000升级到2008R2，今天对数据库表进行碎片扫描，发现有些表碎片较大，于是决定重建索引，联机帮助是最好的老师，将相关脚本摘录备后查。

参考sys.dm_db_index_physical_stats

• 检查索引碎片情况

• 使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 （来源于联机帮助）