SQLSERVER聚集索引与非聚集索引的再次研究（上） 上篇主要说聚集索引 下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下） 由于本人还是SQLSERVER菜鸟一枚，加上一些实验的逻辑严谨性， 单写《SQLSERVER聚集索引与非聚集索引的再次研究（上）》就用
SQLSERVER聚集索引与非聚集索引的再次研究（上）
上篇主要说聚集索引
下篇的地址：SQLSERVER聚集索引与非聚集索引的再次研究（下）
由于本人还是SQLSERVER菜鸟一枚，加上一些实验的逻辑严谨性，
单写《SQLSERVER聚集索引与非聚集索引的再次研究（上）》就用了12个小时，两篇文章加起来最起码写了20个小时，
本人非常非常用心的努力完成这两篇文章，希望各位看官给点意见o(∩_∩)o
为了搞清楚索引内部工作原理和结构，真是千头万绪，这篇文章只是作为参考，里面的观点不一定正确
有一些问题，msdn里，网上的文章里，博客园里都有提到，但是这些问题的答案是正确的吗？其实有时候我自己都想知道答案
比如，画聚集索引的图，有一些人用表格来表示，但是他们正确吗？
以前知道聚集索引 非聚集索引是B树 二叉树结构，又知道执行计划图标很像二叉树很传神，但是还是觉得很抽象
这篇文章写完以后还是比较抽象但是最起码比以前清晰一些了
有很多问题不知道为什么，但是MSDN就是这样说的，既然说得这麽模糊不如自己做一下实验，验证一下MSDN的内容吧o(∩_∩)o
--------------------------------------------华丽的分割线---------------------------------------------
先来看一下索引的结构，文章里面的一些结构图都是自己画的一些草图，本人自认画得非常烂，希望各位看官谅解o(∩_∩)o
----------------------------------------------华丽的分割线---------------------------------------------------------
先创建一个表，保存DBCC IND的结果
<span> 1</span> <span>CREATE</span> <span>TABLE</span><span> DBCCResult ( </span><span> 2</span> PageFID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 3</span> PagePID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 4</span> IAMFID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 5</span> IAMPID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 6</span> ObjectID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 7</span> IndexID <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 8</span> PartitionNumber <span>NVARCHAR</span>(<span>200</span><span>), </span><span> 9</span> PartitionID <span>NVARCHAR</span>(<span>200</span><span>), </span><span>10</span> iam_chain_type <span>NVARCHAR</span>(<span>200</span><span>), </span><span>11</span> PageType <span>NVARCHAR</span>(<span>200</span><span>), </span><span>12</span> IndexLevel <span>NVARCHAR</span>(<span>200</span><span>), </span><span>13</span> NextPageFID <span>NVARCHAR</span>(<span>200</span><span>), </span><span>14</span> NextPagePID <span>NVARCHAR</span>(<span>200</span><span>), </span><span>15</span> PrevPageFID <span>NVARCHAR</span>(<span>200</span><span>), </span><span>16</span> PrevPagePID <span>NVARCHAR</span>(<span>200</span><span>) </span><span>17</span> )
创建一个聚集索引表
<span>1</span> <span>--</span><span>只有聚集索引</span> <span>2</span> <span>CREATE</span> <span>TABLE</span><span> Department( </span><span>3</span> DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>, </span><span>4</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>5</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span>6</span> Company <span>NVARCHAR</span>(<span>300</span><span>), </span><span>7</span> ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> (<span>getdate</span><span>()) </span><span>8</span> )
插入10W条记录
<span>1</span> <span>INSERT</span> <span>INTO</span> Department(name,<span>[</span><span>Company</span><span>]</span>,groupname) <span>VALUES</span>(<span>'</span><span>销售部</span><span>'</span>,<span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>,<span>'</span><span>销售组</span><span>'</span><span>) </span><span>2</span> <span>GO</span> <span>100000</span>
将DBCC IND的结果放入DBCCRESULT表
<span>1</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department,-1) </span><span>'</span>)
查询Department表中的页面情况
先说明一下：
PageType 分页类型： 1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面
IndexID 索引ID： 0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段
红色框部分都是需要关注的
第一个：IAM页不是只有堆表才有也不只是维护堆表中的数据页的连续，有索引的表都有，所以IAM页不只维护数据页，也维护索引页的连续，在下篇说到非聚集索引的时候
我会给出MSDN的解释和IAM页在聚集索引表，非聚集索引表中的情况
第二个：每个数据页的IndexID都是1，不是说数据页变成了索引页，而是说现在数据页已经属于聚集索引的一部分，不在堆里了
第三个：每个数据页的IndexLevel都是0，就是说数据页在聚集索引的最下层
第四个:索引页和数据页,前一页和后一页是首尾相连的,但是数据页和索引页不是首尾相连的，也就是说没有一个数据页的[PrevPagePID]指向14464页或3528页
那么在上面的聚集索引图片中为什麽会说索引页指向数据页呢？叶子节点就是数据页呢？
数据页的index level是0，那么就是说聚集索引的叶子节点就是数据页
上面索引页的结构
现在来看一下索引页里都有什么，运行下面的SQL语句
<span> 1</span> <span>DBCC</span> TRACEON(<span>3604</span>,<span>-</span><span>1</span><span>) </span><span> 2</span> <span>GO</span> <span> 3</span> <span> 4</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>3527</span>,<span>3</span><span>) </span><span> 5</span> <span>GO</span> <span> 6</span> <span> 7</span> <span> 8</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>3528</span>,<span>3</span><span>) </span><span> 9</span> <span>GO</span> <span>10</span> <span>11</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14464</span>,<span>3</span><span>) </span><span>12</span> <span>GO</span>
您们应该看到ChildPageId，所以上面我的图为什麽会这样画的原因，索引页连接着数据页，而且一个索引页指向多个数据页
DepartmentID是主键列，从1开始自增，那么从下图可以看出主键列数据是从最左边的索引节点（不是叶子节点）开始排序
这里有个问题：为什麽根节点只有两行？？？是不是根节点只作连接作用，所以只有两行 ，不过这个问题我也不清楚
聚集索引页里主键列DepartmentID上一行与下一行相差120条记录,一个数据页刚好容纳120条记录
KeyHashValue根据主键列的第一个字段而生成的，就算两个表完全一样，这个hash出来的KeyHashValue都不会一样
我创建了一个一模一样的表Department2，看到hash出来的值都不一样
而这个KeyHashValue我们就叫做键，也就是key-value中的key
------------------------------------------------------------华丽的分割线------------------------------------------------------
聚集索引怎麽找记录的？？？
这里要分两种情况：（1）聚集索引查找和（2）聚集索引扫描
（1）聚集索引查找
放大一下索引页
SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值，然后放在KeyHashValue列里
当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候，首先SQLSERVER根据where DepartmentID=110
将110加上一个范围值hash出一个值，这个值就是KeyHashValue的值，找到KeyHashValue=(f000ff86397c)的那条记录
然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值
其实这里的算法应该跟hash join是一样的，但是实际具体怎麽算的？本人就不清楚了，大家可以看一下hash join的原理
个人感觉在SQLSERVER里 key-value hash桶用途很广泛，执行计划、 hash join、 聚集索引都用到了
证明：这里我可以证明一下SQLSERVER聚集索引查找记录的流程
先到索引页里找到键值为KeyHashValue=XXX的那条记录，然后再到数据页里把实际数据读出来
运行下面的SQL语句，看一下SQLSERVER申请的锁就知道了
下面实验我在Department2表里做的，表数据和表结构和Department1一模一样
<span> 1</span> <span>--</span><span>只有聚集索引</span> <span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department2( </span><span> 3</span> DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>, </span><span> 4</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 5</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 6</span> Company <span>NVARCHAR</span>(<span>300</span><span>), </span><span> 7</span> ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> (<span>getdate</span><span>()) </span><span> 8</span> <span>) </span><span> 9</span> <span>10</span> <span>INSERT</span> <span>INTO</span> Department2(name,<span>[</span><span>Company</span><span>]</span>,groupname) <span>VALUES</span>(<span>'</span><span>销售部</span><span>'</span>,<span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>,<span>'</span><span>销售组</span><span>'</span><span>) </span><span>11</span> <span>GO</span> <span>100000</span> <span>12</span> <span>13</span> <span>14</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> Department2 </span><span>15</span> <span>16</span> <span>--</span><span>先清空[DBCCResult]表里的记录</span> <span>17</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span> <span>18</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department2,-1) </span><span>'</span><span>) </span><span>19</span> <span>20</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> <span>21</span> <span>22</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14471</span>,<span>3</span><span>) </span><span>23</span> <span>GO</span> <span>24</span> <span>25</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>4375</span>,<span>3</span><span>) </span><span>26</span> <span>GO</span> <span>27</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>4376</span>,<span>3</span><span>) </span><span>28</span> <span>GO</span>View Code
下面这个证明代码在《SQLSERVER企业级平台管理实践》里找的
<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span> <span> 4</span> <span>GO</span> <span> 5</span> <span>--</span><span>以下查询使用了聚集索引查找 ctrl+l</span> <span> 6</span> <span>BEGIN</span> <span>TRAN</span> <span> 7</span> <span>SELECT</span> GroupName <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department2</span><span>]</span> <span>WHERE</span> DepartmentID <span>IN</span>(<span>32641</span>,<span>361</span>,<span>32281</span><span>) </span><span> 8</span> <span> 9</span> <span>--</span><span>COMMIT TRAN</span> <span>10</span> <span>11</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span>--</span><span>要查询申请锁的数据库</span> <span>12</span> <span>GO</span> <span>13</span> <span>SELECT</span> <span>14</span> <span>[</span><span>request_session_id</span><span>]</span><span>, </span><span>15</span> c.<span>[</span><span>program_name</span><span>]</span><span>, </span><span>16</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname, </span><span>17</span> <span>[</span><span>resource_type</span><span>]</span><span>, </span><span>18</span> <span>[</span><span>request_status</span><span>]</span><span>, </span><span>19</span> <span>[</span><span>request_mode</span><span>]</span><span>, </span><span>20</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname, </span><span>21</span> p.<span>[</span><span>index_id</span><span>]</span> <span>22</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p </span><span>23</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span> <span>24</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span> <span>25</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span> <span>--</span><span>--要查询申请锁的数据库</span> <span>26</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>View Code
（2）聚集索引扫描
先drop掉Department2表，然后重新创建Department2表
<span> 1</span> <span>--</span><span>只有聚集索引</span> <span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department2( </span><span> 3</span> DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span> <span>PRIMARY</span> <span>KEY</span><span>, </span><span> 4</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 5</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 6</span> Company <span>NVARCHAR</span>(<span>300</span><span>), </span><span> 7</span> ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> (<span>getdate</span><span>()) </span><span> 8</span> <span>) </span><span> 9</span> <span>10</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> <span>11</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span> <span>12</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> <span>13</span> <span>BEGIN</span> <span>14</span> <span>INSERT</span> <span>INTO</span> Department3 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname ) </span><span>15</span> <span>VALUES</span> ( <span>'</span><span>销售部</span><span>'</span>, <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>销售组</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span><span>)) ) </span><span>16</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span> <span>17</span> <span>END</span> <span>18</span> <span>19</span> <span>20</span> <span>SELECT</span> <span>*</span> <span>FROM</span> Department2</span>View Code
证明：
<span> 1</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span> 2</span> <span>GO</span> <span> 3</span> <span>SET</span> <span>TRANSACTION</span> <span>ISOLATION</span> <span>LEVEL</span> <span>REPEATABLE</span> <span>READ</span> <span> 4</span> <span>GO</span> <span> 5</span> <span>--</span><span>以下查询使用了聚集索引查找 ctrl+l</span> <span> 6</span> <span>BEGIN</span> <span>TRAN</span> <span> 7</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department2</span><span>]</span> <span>WHERE</span> <span>[</span><span>GroupName</span><span>]</span> <span>=</span><span>'</span><span>销售组83421</span><span>'</span> <span> 8</span> <span> 9</span> <span>--</span><span>COMMIT TRAN</span> <span>10</span> <span>11</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span>--</span><span>要查询申请锁的数据库</span> <span>12</span> <span>GO</span> <span>13</span> <span>SELECT</span> <span>14</span> <span>[</span><span>request_session_id</span><span>]</span><span>, </span><span>15</span> c.<span>[</span><span>program_name</span><span>]</span><span>, </span><span>16</span> <span>DB_NAME</span>(c.<span>[</span><span>dbid</span><span>]</span>) <span>AS</span><span> dbname, </span><span>17</span> <span>[</span><span>resource_type</span><span>]</span><span>, </span><span>18</span> <span>[</span><span>request_status</span><span>]</span><span>, </span><span>19</span> <span>[</span><span>request_mode</span><span>]</span><span>, </span><span>20</span> <span>[</span><span>resource_description</span><span>]</span>,<span>OBJECT_NAME</span>(p.<span>[</span><span>object_id</span><span>]</span>) <span>AS</span><span> objectname, </span><span>21</span> p.<span>[</span><span>index_id</span><span>]</span> <span>22</span> <span>FROM</span> sys.<span>[</span><span>dm_tran_locks</span><span>]</span> <span>AS</span> a <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>partitions</span><span>]</span> <span>AS</span><span> p </span><span>23</span> <span>ON</span> a.<span>[</span><span>resource_associated_entity_id</span><span>]</span><span>=</span>p.<span>[</span><span>hobt_id</span><span>]</span> <span>24</span> <span>LEFT</span> <span>JOIN</span> sys.<span>[</span><span>sysprocesses</span><span>]</span> <span>AS</span> c <span>ON</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span>c.<span>[</span><span>spid</span><span>]</span> <span>25</span> <span>WHERE</span> c.<span>[</span><span>dbid</span><span>]</span><span>=</span><span>DB_ID</span>(<span>'</span><span>pratice</span><span>'</span>) <span>AND</span> a.<span>[</span><span>request_session_id</span><span>]</span><span>=</span><span>@@SPID</span> <span>--</span><span>--要查询申请锁的数据库</span> <span>26</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>request_session_id</span><span>]</span>,<span>[</span><span>resource_type</span><span>]</span>View Code
上图“以下查询使用了聚集索引查找”，由于本人写SQL代码的时候没有修改上面注释，大家可以不用理会
为什麽会有一个键锁，那么多的页锁，在徐海蔚老师的《SQLSERVER企业级平台管理实践》的书本里第361页说到
因为在有聚集索引的表格上，数据是直接存放在索引的最底层（叶子节点），所以要扫描整个表格里的数据，就要把整个聚集索引
扫描一遍。在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别
再看一下聚集索引查找的流程
SQLSERVER首先把每个数据页的头一条记录里的DepartmentID的值加上一定范围值hash出一个key值，然后放在KeyHashValue列里
当我要找DepartmentID为110的那条记录里的GroupName和Company的值的时候，首先SQLSERVER根据where DepartmentID=110
将110加上一个范围值hash出一个值，这个值就是KeyHashValue的值，找到KeyHashValue=(f000ff86397c)的那条记录
然后到数据页13791里找出DepartmentID为110的那条记录里的GroupName和Company的值
因为[GroupName]列不是索引列，所以根本找不到KeyHashValue值，所以这里只能使用扫描所有数据页的方法来找出记录，除非找到那条记录
不然SQLSERVER不会停止扫描数据页，所以才看到上图有那么多的页面上加了页锁，SQLSERVER需要逐个数据页逐个数据页去扫描就像堆表的全表扫描那样。
那个键锁我估计是当SQLSERVER找到那条记录之后，需要在
记录的所在页面（即是索引页指向那个记录的数据页的那一行）加上一个键锁，以防止别人删除索引页的那一行记录
但是聚集索引扫描是不是一定比聚集索引查找要差呢？这个不一定，要看实际情况o(∩_∩)o
那么非聚集索引扫描是不是跟聚集索引扫描一样，所要用的时间和资源与表扫描没有什么差别呢？？？
大家可以看一下《SQLSERVER聚集索引与非聚集索引的再次研究（下）》本人做的一个小实验
实验证明了《SQLSERVER企业级平台管理实践》里第363页说到的内容
索引扫描表明SQLSERVER正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，所以这里虽然也是扫描，但是
代价会比整表扫描要小很多
------------------------------------------------华丽的分割线--------------------------------------------------------------------
这里有一个问题：没有主键但是有聚集索引，索引页的列数不一样，会多了一列，而这个列（uniquifier）的作用在下面会讲到
这里创建Department3表
<span> 1</span> <span>--</span><span>只有聚集索引</span> <span> 2</span> <span>CREATE</span> <span>TABLE</span><span> Department3( </span><span> 3</span> DepartmentID <span>int</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 4</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 5</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span>, </span><span> 6</span> Company <span>NVARCHAR</span>(<span>300</span><span>), </span><span> 7</span> ModifiedDate <span>datetime</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> (<span>getdate</span><span>()) </span><span> 8</span> <span>) </span><span> 9</span> <span>10</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> CL_DepartmentID <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department3</span><span>]</span>(<span>[</span><span>DepartmentID</span><span>]</span><span>) </span><span>11</span> <span>12</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> <span>13</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span> <span>14</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> <span>15</span> <span>BEGIN</span> <span>16</span> <span>INSERT</span> <span>INTO</span> Department3 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname ) </span><span>17</span> <span>VALUES</span> ( <span>'</span><span>销售部</span><span>'</span>, <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>销售组</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span><span>)) ) </span><span>18</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span> <span>19</span> <span>END</span> <span>20</span> <span>21</span> <span>22</span> <span>SELECT</span> <span>*</span> <span>FROM</span><span> Department3 </span><span>23</span> <span>24</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span> <span>25</span> <span>26</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department3,-1) </span><span>'</span><span>) </span><span>27</span> <span>28</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> <span>29</span> <span>30</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>13861</span>,<span>3</span><span>) </span><span>31</span> <span>GO</span></span>View Code
可以看到只有聚集索引没有主键的表会比主键表多了一列uniquifier列，这个列的作用会在创建Department5表的时候讲到
-----------------------------------------------华丽的分割线-------------------------------------------------------
下面说一下，复合主键或者聚集索引建立在多个字段上，KeyHashValue只会根据第一个字段生成hash key
当你查询的时候where 后面的字段不包含创建聚集索引时的第一个字段或者复合主键的第一个字段就会聚集索引扫描而不是聚集索引查找
创建Department4表
<span> 1</span> <span>--</span><span>只有聚集索引</span> <span> 2</span> <span>CREATE</span> <span>TABLE</span> Department4 <span>--</span><span>包含复合主键DepartmentID 和Name</span> <span> 3</span> <span>( </span><span> 4</span> DepartmentID <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 5</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 6</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 7</span> Company <span>NVARCHAR</span>(<span>300</span><span>) , </span><span> 8</span> ModifiedDate <span>DATETIME</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> ( <span>GETDATE</span><span>() ) , </span><span> 9</span> <span>CONSTRAINT</span> <span>[</span><span>PK_Department4_1</span><span>]</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span> <span>10</span> ( DepartmentID <span>ASC</span><span>, </span><span>11</span> Name <span>ASC</span><span> ) </span><span>12</span> <span>WITH</span> ( PAD_INDEX <span>=</span> <span>OFF</span>, STATISTICS_NORECOMPUTE <span>=</span> <span>OFF</span>, IGNORE_DUP_KEY <span>=</span> <span>OFF</span><span>, </span><span>13</span> ALLOW_ROW_LOCKS <span>=</span> <span>ON</span>, ALLOW_PAGE_LOCKS <span>=</span> <span>ON</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>14</span> ) <span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span> <span>15</span> <span>16</span> <span>17</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> <span>18</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span> <span>19</span> <span>WHILE</span> <span>@i</span> <span> <span>100000</span> <span>20</span> <span>BEGIN</span> <span>21</span> <span>INSERT</span> <span>INTO</span> Department4 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname ) </span><span>22</span> <span>VALUES</span> ( <span>'</span><span>销售部</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>, <span>'</span><span>销售组</span><span>'</span><span> ) </span><span>23</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span> <span>24</span> <span>END</span> <span>25</span> <span>26</span> <span>27</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>28</span> <span>29</span> <span>30</span> <span>31</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span> <span>32</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department4,-1) </span><span>'</span><span>) </span><span>33</span> <span>34</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> <span>35</span> <span>36</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>7102</span>,<span>3</span><span>) </span><span>37</span> <span>GO</span></span>View Code
<span>1</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部6</span><span>'</span> <span>--</span><span>聚集索引扫描 因为name不是复合主键中的第一个字段</span> <span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部241</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span> <span>--</span><span>聚集索引查找</span> <span>3</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department4</span><span>]</span> <span>WHERE</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span> <span>--</span><span>聚集索引查找</span>
在建立聚集索引的时候在多个字段上建立聚集索引是没有任何意义的
因为聚集索引查找是根据建立索引的第一个字段来查找，索引扫描的时候会到数据页里扫描 ，而聚集索引的每一行只是一个数据页的范围值从而不能直接定位到要找的那条记录
所以只需要在数据表的一个字段上建立聚集索引就可以了，而究竟要在哪一个字段上建立聚集索引大家一定好好斟酌，本人建议那一个字段在order by中经常要排序的
因为数据页都已经按照聚集索引的第一个字段排好序的了
而不像非聚集索引的索引页跟数据表的记录一一对应，扫描的时候扫描索引页的每一行
大家可以对比一下聚集索引和非聚集索引页的结构
聚集索引页的结构
非聚集索引页的结构
非聚集索引页面的结构会在SQLSERVER聚集索引与非聚集索引的再次研究（下）里讲到
---------------------------------------------------------华丽的分割线-----------------------------------------------------
由于主键不允许重复值，那么就在表上创建一个不唯一的聚集索引，有人说在重复值很多的列上建立聚集索引没有意义
创建Department5表 在Company字段上建立聚集索引，Company字段的值全部都是"中国你好有限公司XX分公司"
<span> 1</span> <span>--</span><span>只有聚集索引</span> <span> 2</span> <span>USE</span> <span>[</span><span>pratice</span><span>]</span> <span> 3</span> <span>GO</span> <span> 4</span> <span>CREATE</span> <span>TABLE</span><span> Department5 </span><span> 5</span> <span>( </span><span> 6</span> DepartmentID <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 7</span> Name <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 8</span> GroupName <span>NVARCHAR</span>(<span>200</span>) <span>NOT</span> <span>NULL</span><span> , </span><span> 9</span> Company <span>NVARCHAR</span>(<span>300</span><span>) , </span><span>10</span> ModifiedDate <span>DATETIME</span> <span>NOT</span> <span>NULL</span> <span>DEFAULT</span> ( <span>GETDATE</span><span>() ) </span><span>11</span> <span>) </span><span>12</span> <span>13</span> <span>CREATE</span> <span>CLUSTERED</span> <span>INDEX</span> CL_Company <span>ON</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span>(<span>[</span><span>Company</span><span>]</span> <span>ASC</span><span>) </span><span>14</span> <span>15</span> <span>--</span><span>DROP TABLE [dbo].[Department5]</span> <span>16</span> <span>17</span> <span>DECLARE</span> <span>@i</span> <span>INT</span> <span>18</span> <span>SET</span> <span>@i</span><span>=</span><span>1</span> <span>19</span> <span>WHILE</span> <span>@i</span> <span> <span>10000</span> <span>20</span> <span>BEGIN</span> <span>21</span> <span>INSERT</span> <span>INTO</span> Department5 ( name, <span>[</span><span>Company</span><span>]</span><span>, groupname ) </span><span>22</span> <span>VALUES</span> ( <span>'</span><span>销售部</span><span>'</span><span>+</span><span>CAST</span>(<span>@i</span> <span>AS</span> <span>VARCHAR</span>(<span>200</span>)), <span>'</span><span>中国你好有限公司XX分公司</span><span>'</span>, <span>'</span><span>销售组</span><span>'</span><span> ) </span><span>23</span> <span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span> <span>24</span> <span>END</span></span>View Code
<span>1</span> <span>--</span><span>TRUNCATE TABLE [dbo].[DBCCResult]</span> <span>2</span> <span>INSERT</span> <span>INTO</span> DBCCResult <span>EXEC</span> (<span>'</span><span>DBCC IND(pratice,Department5,-1) </span><span>'</span><span>) </span><span>3</span> <span>4</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>DBCCResult</span><span>]</span> <span>ORDER</span> <span>BY</span> <span>[</span><span>PageType</span><span>]</span> <span>DESC</span> <span>5</span> <span>6</span> <span>DBCC</span> PAGE(<span>[</span><span>pratice</span><span>]</span>,<span>1</span>,<span>14516</span>,<span>3</span><span>) </span><span>7</span> <span>GO</span>View Code
在Department3表的时候讲到列（uniquifier），为什麽有主键的表没有这个列，而聚集索引的表有这个列，原因在于
主键列不能有重复值，必须是唯一的，而聚集索引允许有重复值，所以聚集索引需要增加列（uniquifier）来区分重复值
而且可以看到这里uniquifier列是没有规律的，不像Department表每隔120行记录在索引页里标记一行
看一下执行计划和执行结果
<span> 1</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span> 2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span> <span>WHERE</span> <span>[</span><span>Company</span><span>]</span><span>=</span><span>'</span><span>中国你好有限公司XX分公司</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>241</span> <span> 3</span> <span> 4</span> <span>SQL Server 分析和编译时间: </span><span> 5</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span><span> 毫秒。 </span><span> 6</span> <span> 7</span> (<span>1</span><span> 行受影响) </span><span> 8</span> <span> 9</span> <span>SQL Server 执行时间: </span><span>10</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
<span>1</span> <span>SET</span> <span>STATISTICS</span> TIME <span>ON</span> <span>2</span> <span>SELECT</span> <span>*</span> <span>FROM</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Department5</span><span>]</span> <span>WHERE</span> name<span>=</span><span>'</span><span>销售部106</span><span>'</span> <span>AND</span> <span>[</span><span>DepartmentID</span><span>]</span><span>=</span><span>106</span> <span>--</span><span>聚集索引扫描</span> <span>3</span> <span>SQL Server 执行时间: </span><span>4</span> CPU 时间 <span>=</span> <span>0</span> 毫秒，占用时间 <span>=</span> <span>0</span> 毫秒。
至于应不应该在重复值很多的列上建立聚集索引我这里也不敢妄下判断，因为实际环境和这里的测试环境不一样
在MSDN中的解释：http://msdn.microsoft.com/zh-cn/library/ms177484(v=SQL.105).aspx
如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见
还有一个，看一下叶子节点中的数据页，在每个数据页的每行记录中都有
Slot 101 Column 0 Offset 0x1d Length 4
UNIQUIFIER = 206
因为需要标记索引列中的唯一，所以需要在每行记录中增加一列UNIQUIFIER ，但是这一列在select * 表中数据的时候是select不出来的
还有人说UNIQUIFIER 是一个可变长度的字段，但是Length 4已经说明了是一个占用4字节的字段
<span> 1</span> PAGE: (<span>1</span>:<span>14517</span><span>) </span><span> 2</span> <span> 3</span> <span> 4</span> <span>BUFFER: </span><span> 5</span> <span> 6</span> <span> 7</span> BUF <span>@0x03EC9D64</span> <span> 8</span> <span> 9</span> bpage <span>=</span> <span>0x1A096000</span> bhash <span>=</span> <span>0x00000000</span> bpageno <span>=</span> (<span>1</span>:<span>14517</span><span>) </span><span> 10</span> bdbid <span>=</span> <span>5</span> breferences <span>=</span> <span>0</span> bUse1 <span>=</span> <span>4722</span> <span> 11</span> bstat <span>=</span> <span>0x3c00009</span> blog <span>=</span> <span>0x32159</span> bnext <span>=</span> <span>0x00000000</span> <span> 12</span> <span> 13</span> <span>PAGE HEADER: </span><span> 14</span> <span> 15</span> <span> 16</span> Page <span>@0x1A096000</span> <span> 17</span> <span> 18</span> m_pageId <span>=</span> (<span>1</span>:<span>14517</span>) m_headerVersion <span>=</span> <span>1</span> m_type <span>=</span> <span>1</span> <span> 19</span> m_typeFlagBits <span>=</span> <span>0x4</span> m_level <span>=</span> <span>0</span> m_flagBits <span>=</span> <span>0x200</span> <span> 20</span> m_objId (AllocUnitId.idObj) <span>=</span> <span>317</span> m_indexId (AllocUnitId.idInd) <span>=</span> <span>256</span> <span> 21</span> Metadata: AllocUnitId <span>=</span> <span>72057594058702848</span> <span> 22</span> Metadata: PartitionId <span>=</span> <span>72057594049462272</span> Metadata: IndexId <span>=</span> <span>1</span> <span> 23</span> Metadata: ObjectId <span>=</span> <span>1022626686</span> m_prevPage <span>=</span> (<span>1</span>:<span>14514</span>) m_nextPage <span>=</span> (<span>1</span>:<span>14518</span><span>) </span><span> 24</span> pminlen <span>=</span> <span>16</span> m_slotCnt <span>=</span> <span>102</span> m_freeCnt <span>=</span> <span>38</span> <span> 25</span> m_freeData <span>=</span> <span>7950</span> m_reservedCnt <span>=</span> <span>0</span> m_lsn <span>=</span> (<span>2568</span>:<span>5252</span>:<span>8</span><span>) </span><span> 26</span> m_xactReserved <span>=</span> <span>0</span> m_xdesId <span>=</span> (<span>0</span>:<span>0</span>) m_ghostRecCnt <span>=</span> <span>0</span> <span> 27</span> m_tornBits <span>=</span> <span>-</span><span>1007571449</span> <span> 28</span> <span> 29</span> <span>Allocation Status </span><span> 30</span> <span> 31</span> GAM (<span>1</span>:<span>2</span>) <span>=</span> ALLOCATED SGAM (<span>1</span>:<span>3</span>) <span>=</span> <span>NOT</span><span> ALLOCATED </span><span> 32</span> PFS (<span>1</span>:<span>8088</span>) <span>=</span> <span>0x60</span> MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (<span>1</span>:<span>6</span>) <span>=</span> <span>NOT</span><span> CHANGED </span><span> 33</span> ML (<span>1</span>:<span>7</span>) <span>=</span> <span>NOT</span><span> MIN_LOGGED </span><span> 34</span> <span> 35</span> Slot <span>0</span> Offset <span>0x60</span> Length <span>77</span> <span> 36</span> <span> 37</span> Record Type <span>=</span> PRIMARY_RECORD Record Attributes <span>=</span><span> NULL_BITMAP VARIABLE_COLUMNS </span><span> 38</span> <span> 39</span> Memory <span>Dump</span> <span>@0x0A64C060</span> <span> 40</span> <span> 41</span> <span>00000000</span>: <span>30001000</span> 6a000000 d42e7c01 fea10000 †<span>0</span>...j.....<span>|</span><span>..... </span><span> 42</span> <span>00000010</span>: 0600c004 0021003b 0047004d <span>00690000</span><span> †.....!.;.G.M.i.. </span><span> 43</span> <span>00000020</span>: 002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ. </span><span> 44</span> <span>00000030</span>: <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U. </span><span> 45</span> <span>00000040</span>: <span>90310030</span> <span>00360000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>6</span>....U.<span>~</span> <span> 46</span> <span> 47</span> Slot <span>0</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span> <span> 48</span> <span> 49</span> UNIQUIFIER <span>=</span> <span>105</span> <span> 50</span> <span> 51</span> Slot <span>0</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span> <span> 52</span> <span> 53</span> Company <span>=</span><span> 中国你好有限公司XX分公司 </span><span> 54</span> <span> 55</span> Slot <span>0</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span> <span> 56</span> <span> 57</span> DepartmentID <span>=</span> <span>106</span> <span> 58</span> <span> 59</span> Slot <span>0</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span> <span> 60</span> <span> 61</span> Name <span>=</span><span> 销售部106 </span><span> 62</span> <span> 63</span> Slot <span>0</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span> <span> 64</span> <span> 65</span> GroupName <span>=</span><span> 销售组 </span><span> 66</span> <span> 67</span> Slot <span>0</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span> <span> 68</span> <span> 69</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM </span><span> 70</span> <span> 71</span> Slot <span>1</span> Offset <span>0xad</span> Length <span>77</span> <span> 72</span> <span> 73</span> Record Type <span>=</span> PRIMARY_RECORD Record Attributes <span>=</span><span> NULL_BITMAP VARIABLE_COLUMNS </span><span> 74</span> <span> 75</span> Memory <span>Dump</span> <span>@0x0A64C0AD</span> <span> 76</span> <span> 77</span> <span>00000000</span>: <span>30001000</span> 6b000000 d42e7c01 fea10000 †<span>0</span>...k.....<span>|</span><span>..... </span><span> 78</span> <span>00000010</span><span>: 0600c004 0021003b 0047004d 006a0000 †.....!.;.G.M.j.. </span><span> 79</span> <span>00000020</span>: 002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ. </span><span> 80</span> <span>00000030</span>: <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U. </span><span> 81</span> <span>00000040</span>: <span>90310030</span> <span>00370000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>7</span>....U.<span>~</span> <span> 82</span> <span> 83</span> Slot <span>1</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span> <span> 84</span> <span> 85</span> UNIQUIFIER <span>=</span> <span>106</span> <span> 86</span> <span> 87</span> Slot <span>1</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span> <span> 88</span> <span> 89</span> Company <span>=</span><span> 中国你好有限公司XX分公司 </span><span> 90</span> <span> 91</span> Slot <span>1</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span> <span> 92</span> <span> 93</span> DepartmentID <span>=</span> <span>107</span> <span> 94</span> <span> 95</span> Slot <span>1</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span> <span> 96</span> <span> 97</span> Name <span>=</span><span> 销售部107 </span><span> 98</span> <span> 99</span> Slot <span>1</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span> <span> 100</span> <span> 101</span> GroupName <span>=</span><span> 销售组 </span><span> 102</span> <span> 103</span> Slot <span>1</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span> <span> 104</span> <span> 105</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM </span><span> 106</span> <span> 107</span> Slot <span>2</span> Offset <span>0xfa</span> Length <span>77</span> <span> 108</span> <span> 109</span> Record Type <span>=</span> PRIMARY_RECORD Record Attributes <span>=</span><span> NULL_BITMAP VARIABLE_COLUMNS </span><span> 110</span> <span> 111</span> Memory <span>Dump</span> <span>@0x0A64C0FA</span> <span> 112</span> <span> 113</span> <span>00000000</span>: <span>30001000</span> 6c000000 d42e7c01 fea10000 †<span>0</span>...l.....<span>|</span><span>..... </span><span> 114</span> <span>00000010</span><span>: 0600c004 0021003b 0047004d 006b0000 †.....!.;.G.M.k.. </span><span> 115</span> <span>00000020</span>: 002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ. </span><span> 116</span> <span>00000030</span>: <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U. </span><span> 117</span> <span>00000040</span>: <span>90310030</span> <span>00380000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>8</span>....U.<span>~</span> <span> 118</span> <span> 119</span> Slot <span>2</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span> <span> 120</span> <span> 121</span> UNIQUIFIER <span>=</span> <span>107</span> <span> 122</span> <span> 123</span> Slot <span>2</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span> <span> 124</span> <span> 125</span> Company <span>=</span><span> 中国你好有限公司XX分公司 </span><span> 126</span> <span> 127</span> Slot <span>2</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span> <span> 128</span> <span> 129</span> DepartmentID <span>=</span> <span>108</span> <span> 130</span> <span> 131</span> Slot <span>2</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span> <span> 132</span> <span> 133</span> Name <span>=</span><span> 销售部108 </span><span> 134</span> <span> 135</span> Slot <span>2</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span> <span> 136</span> <span> 137</span> GroupName <span>=</span><span> 销售组 </span><span> 138</span> <span> 139</span> Slot <span>2</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span> <span> 140</span> <span> 141</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM </span><span> 142</span> <span> 143</span> Slot <span>3</span> Offset <span>0x147</span> Length <span>77</span> <span> 144</span> <span> 145</span> Record Type <span>=</span> PRIMARY_RECORD Record Attributes <span>=</span><span> NULL_BITMAP VARIABLE_COLUMNS </span><span> 146</span> <span> 147</span> Memory <span>Dump</span> <span>@0x0A64C147</span> <span> 148</span> <span> 149</span> <span>00000000</span>: <span>30001000</span> 6d000000 d42e7c01 fea10000 †<span>0</span>...m.....<span>|</span><span>..... </span><span> 150</span> <span>00000010</span><span>: 0600c004 0021003b 0047004d 006c0000 †.....!.;.G.M.l.. </span><span> 151</span> <span>00000020</span>: 002d4efd 56604f7d <span>59096750</span> 966c51f8 †.<span>-</span><span>N.V`O}Y.gP.lQ. </span><span> 152</span> <span>00000030</span>: <span>53580058</span><span> 0006526c 51f85300 952e55e8 †SX.X..RlQ.S...U. </span><span> 153</span> <span>00000040</span>: <span>90310030</span> <span>00390000</span> 952e55c4 7e††††††††.<span>1.0</span>.<span>9</span>....U.<span>~</span> <span> 154</span> <span> 155</span> Slot <span>3</span> <span>Column</span> <span>0</span> Offset <span>0x1d</span> Length <span>4</span> <span> 156</span> <span> 157</span> UNIQUIFIER <span>=</span> <span>108</span> <span> 158</span> <span> 159</span> Slot <span>3</span> <span>Column</span> <span>1</span> Offset <span>0x21</span> Length <span>26</span> <span> 160</span> <span> 161</span> Company <span>=</span><span> 中国你好有限公司XX分公司 </span><span> 162</span> <span> 163</span> Slot <span>3</span> <span>Column</span> <span>2</span> Offset <span>0x4</span> Length <span>4</span> <span> 164</span> <span> 165</span> DepartmentID <span>=</span> <span>109</span> <span> 166</span> <span> 167</span> Slot <span>3</span> <span>Column</span> <span>3</span> Offset <span>0x3b</span> Length <span>12</span> <span> 168</span> <span> 169</span> Name <span>=</span><span> 销售部109 </span><span> 170</span> <span> 171</span> Slot <span>3</span> <span>Column</span> <span>4</span> Offset <span>0x47</span> Length <span>6</span> <span> 172</span> <span> 173</span> GroupName <span>=</span><span> 销售组 </span><span> 174</span> <span> 175</span> Slot <span>3</span> <span>Column</span> <span>5</span> Offset <span>0x8</span> Length <span>8</span> <span> 176</span> <span> 177</span> ModifiedDate <span>=</span> <span>07</span> <span>17</span> <span>2013</span> <span>11</span><span>:04PM </span><span> 178</span> <span> 179</span> Slot <span>4</span> Offset <span>0x194</span> Length <span>77</span> <span> 180</span> <span> 181</span> Record Typ
성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
MySQL 및 Mariadb를 비교하고 대조하십시오.Apr 26, 2025 am 12:08 AMMySQL과 Mariadb의 주요 차이점은 성능, 기능 및 라이센스입니다. 1. MySQL은 Oracle에 의해 개발되었으며 Mariadb는 포크입니다. 2. MariaDB는 높은 하중 환경에서 더 나은 성능을 발휘할 수 있습니다. 3. Mariadb는 더 많은 스토리지 엔진과 기능을 제공합니다. 4.MySQL은 듀얼 라이센스를 채택하고 MariaDB는 완전히 오픈 소스입니다. 선택할 때 기존 인프라, 성능 요구 사항, 기능 요구 사항 및 라이센스 비용을 고려해야합니다.
MySQL의 라이센스는 다른 데이터베이스 시스템과 어떻게 비교됩니까?Apr 25, 2025 am 12:26 AMMySQL은 GPL 라이센스를 사용합니다. 1) GPL 라이센스는 MySQL의 무료 사용, 수정 및 분포를 허용하지만 수정 된 분포는 GPL을 준수해야합니다. 2) 상업용 라이센스는 공개 수정을 피할 수 있으며 기밀이 필요한 상업용 응용 프로그램에 적합합니다.
MyISAM을 통해 언제 innodb를 선택 하시겠습니까?Apr 25, 2025 am 12:22 AMMyISAM 대신 InnoDB를 선택할 때의 상황에는 다음이 포함됩니다. 1) 거래 지원, 2) 높은 동시성 환경, 3) 높은 데이터 일관성; 반대로, MyISAM을 선택할 때의 상황에는 다음이 포함됩니다. 1) 주로 읽기 작업, 2) 거래 지원이 필요하지 않습니다. InnoDB는 전자 상거래 플랫폼과 같은 높은 데이터 일관성 및 트랜잭션 처리가 필요한 응용 프로그램에 적합하지만 MyISAM은 블로그 시스템과 같은 읽기 집약적 및 트랜잭션이없는 애플리케이션에 적합합니다.
MySQL에서 외국 키의 목적을 설명하십시오.Apr 25, 2025 am 12:17 AMMySQL에서 외국 키의 기능은 테이블 간의 관계를 설정하고 데이터의 일관성과 무결성을 보장하는 것입니다. 외국 키는 참조 무결성 검사 및 계단식 작업을 통해 데이터의 효과를 유지합니다. 성능 최적화에주의를 기울이고 사용할 때 일반적인 오류를 피하십시오.
MySQL의 다른 유형의 인덱스는 무엇입니까?Apr 25, 2025 am 12:12 AMMySQL에는 B-Tree Index, Hash Index, Full-Text Index 및 공간 인덱스의 네 가지 주요 인덱스 유형이 있습니다. 1.B- 트리 색인은 범위 쿼리, 정렬 및 그룹화에 적합하며 직원 테이블의 이름 열에서 생성에 적합합니다. 2. HASH 인덱스는 동등한 쿼리에 적합하며 메모리 저장 엔진의 HASH_Table 테이블의 ID 열에서 생성에 적합합니다. 3. 전체 텍스트 색인은 기사 테이블의 내용 열에서 생성에 적합한 텍스트 검색에 사용됩니다. 4. 공간 지수는 지리 공간 쿼리에 사용되며 위치 테이블의 Geom 열에서 생성에 적합합니다.
MySQL에서 인덱스를 어떻게 생성합니까?Apr 25, 2025 am 12:06 AMtoreateanindexinmysql, usethecreateindexstatement.1) forasinglecolumn, "createindexidx_lastnameonemployees (lastname);"2) foracompositeIndex를 사용하고 "createDexIdx_nameonemployees (forstName, FirstName);"3)을 사용하십시오
MySQL은 sqlite와 어떻게 다릅니 까?Apr 24, 2025 am 12:12 AMMySQL과 Sqlite의 주요 차이점은 설계 개념 및 사용 시나리오입니다. 1. MySQL은 대규모 응용 프로그램 및 엔터프라이즈 수준의 솔루션에 적합하며 고성능 및 동시성을 지원합니다. 2. SQLITE는 모바일 애플리케이션 및 데스크탑 소프트웨어에 적합하며 가볍고 내부질이 쉽습니다.
MySQL의 색인이란 무엇이며 성능을 어떻게 향상 시키는가?Apr 24, 2025 am 12:09 AMMySQL의 인덱스는 데이터 검색 속도를 높이는 데 사용되는 데이터베이스 테이블에서 하나 이상의 열의 주문 구조입니다. 1) 인덱스는 스캔 한 데이터의 양을 줄임으로써 쿼리 속도를 향상시킵니다. 2) B-Tree Index는 균형 잡힌 트리 구조를 사용하여 범위 쿼리 및 정렬에 적합합니다. 3) CreateIndex 문을 사용하여 CreateIndexIdx_customer_idonorders (customer_id)와 같은 인덱스를 작성하십시오. 4) Composite Indexes는 CreateIndexIdx_customer_orderOders (Customer_id, Order_Date)와 같은 다중 열 쿼리를 최적화 할 수 있습니다. 5) 설명을 사용하여 쿼리 계획을 분석하고 피하십시오
핫 AI 도구
Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!
인기 기사
어 ass 신 크리드 그림자 : 조개 수수께끼 솔루션
4 몇 주 전ByDDD
Windows 11 KB5054979의 새로운 기능 및 업데이트 문제를 해결하는 방법
3 몇 주 전ByDDD
Atomfall에서 크레인 제어 키 카드를 찾을 수 있습니다
4 몇 주 전ByDDD
<s> : 데드 레일 - 모든 도전을 완료하는 방법
1 몇 달 전ByDDD
KB5055523을 수정하는 방법 Windows 11에 설치되지 않습니까?
2 몇 주 전ByDDD
뜨거운 도구
MinGW - Windows용 미니멀리스트 GNU
이 프로젝트는 osdn.net/projects/mingw로 마이그레이션되는 중입니다. 계속해서 그곳에서 우리를 팔로우할 수 있습니다. MinGW: GCC(GNU Compiler Collection)의 기본 Windows 포트로, 기본 Windows 애플리케이션을 구축하기 위한 무료 배포 가능 가져오기 라이브러리 및 헤더 파일로 C99 기능을 지원하는 MSVC 런타임에 대한 확장이 포함되어 있습니다. 모든 MinGW 소프트웨어는 64비트 Windows 플랫폼에서 실행될 수 있습니다.
ZendStudio 13.5.1 맥
강력한 PHP 통합 개발 환경
VSCode Windows 64비트 다운로드
Microsoft에서 출시한 강력한 무료 IDE 편집기
Eclipse용 SAP NetWeaver 서버 어댑터
Eclipse를 SAP NetWeaver 애플리케이션 서버와 통합합니다.
PhpStorm 맥 버전
최신(2018.2.1) 전문 PHP 통합 개발 도구
