精华内容
下载资源
问答
  • 一、三大范式什么是范式...范式是关系数据库理论的基础,是我们在设计数据库结构过程中所要遵循的规则和指导方法,以下就是对这三个范式的基本介绍:第一范式(1NF)1、数据表中的每一列(字段),必须是不可拆分的最...

    一、三大范式

    什么是范式:简言之就是,数据库设计对数据的存储性能,还有开发人员对数据的操作都有莫大的关系。所以建立科学的,规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导方法,以下就是对这三个范式的基本介绍:

    第一范式(1NF)

    1、数据表中的每一列(字段),必须是不可拆分的最小单元,也就是确保每一列的原子性。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。

    a50e83887227035575752e187633f377.png
    03accf93853964cea0db64903a22ff7c.png

    如果需求知道那个省那个市并按其分类,那么显然第一个表格是不容易满足需求的,也不符合第一范式。

    2、两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余数据。

    45cd9fb48237306cbf6a1cae72e36003.png
    ecfe78dc8922663936a38c5346c0acb1.png

    显然第一个表结构不但不能满足足够多物品的要求,还会在物品少时产生冗余。也是不符合第一范式的。

    第二范式(2NF)

    满足1NF后要求表中的所有列,每一行的数据只能与其中一列相关,即一行数据只做一件事。只要数据列中出现数据重复,就要把表拆分开来。

    7a5e2ec99382cbb76031ad43c174e02b.png

    一个人同时订几个房间,就会出来一个订单号多条数据,这样子联系人都是重复的,就会造成数据冗余。我们应该把他拆开来。

    f7368848002710b860ecd8ad7f10fe1d.png
    f6e39ad6ae2c1cc873564623b6fec836.png

    这样便实现啦一条数据做一件事,不掺杂复杂的关系逻辑。同时对表数据的更新维护也更易操作。

    第三范式(3NF)

    满足2NF后,要求:表中的每一列都要与主键直接相关,而不是间接相关(表中的每一列只能依赖于主键)。

    数据不能存在传递关系,即没个属性都跟主键有直接关系而不是间接关系。像:a-->b-->c 属性之间含有这样的关系,是不符合第三范式的。

    注意事项:

    1.第二范式与第三范式的本质区别:在于有没有分出两张表。

    第二范式是说一张表中包含了多种不同实体的属性,那么必须要分成多张表,第三范式是要求已经分好了多张表的话,一张表中只能有另一张标的ID,而不能有其他任何信息,(其他任何信息,一律用主键在另一张表中查询)。

    771d17e183daf316eea861a8ec63ed7a.png

    2.必须先满足第一范式才能满足第二范式,必须同时满足第一第二范式才能满足第三范式。

    三大范式只是一般设计数据库的基本理念,可以建立冗余较小、结构合理的数据库。如果有特殊情况,当然要特殊对待,数据库设计最重要的是看需求跟性能,需求>性能>表结构。所以不能一味的去追求范式建立数据库。

    二、五大约束

    数据库中的五大约束包括

    1.主键约束(Primay Key Coustraint) 唯一性,非空性;

    2.唯一约束 (Unique Counstraint)唯一性,可以空,但只能有一个;

    3.默认约束 (Default Counstraint) 该数据的默认值;

    4.外键约束 (Foreign Key Counstraint) 需要建立两表间的关系;

    5.非空约束(Not Null Counstraint):设置非空约束,该字段不能为空。

    五大约束的语法示例

    1. 添加主键约束

    Alter table 表名 add Constraint 主键名 primary key(字段)

    2. 添加唯一约束

    Alter table 表名 add Constraint 约束名 unique(字段)

    3. 添加默认约束

    Alter table 表名 add Constraint 约束名 default(默认内容) for 字段名

    4. 添加检查约束

    Alter table 表名 add Constraint 约束名 check (字段表达)

    5. 添加外键约束

    Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名(字段名)

    详细介绍:

    (1)[外键约束 (Foreign Key Counstraint) ]

    1.设置外键的注意事项:

    ①:只有INNODB的数据库引擎支持外键,修改my.ini文件设置default-storage-engine=INNODB;

    ②:外键与参照列的数据类型必须相同。(数值型要求长度和无符号都相同,字符串要求类型相同,长度可以不同);

    ③:设置外键的字段必须要有索引,如果没有索引,设置外键时会自动生成一个索引;

    2.设置外键的语法:

    [CONSTRAINT 外键名] FOREIGN KEY(外键字段) REFERENCES 参照表(参照字段) [ON DELETE SET NULL ON UPDATE CASCADE] -- 设置操作完整。

    3、外键约束的参照操作:

    当对参照表的参照字段进行删除或更新时,外键表中的外键如何应对。

    参照操作可选值:

    RESTRICT: 拒绝对参照字段的删除或修改(默认);

    NO ACTION:与RESTRICT相同,但这个指令只在MySql生效;

    CASCADE: 删除或更新参照表的参照字段时,外键表的记录同步删除或更新;

    SET NULL: 删除删除或更新参照表的参照字段时,外键表的外键设为NULL (此时外键不能设置为NOT NULL)。

    (2)[主键约束](Primay Key Coustraint)

    1.主键的注意事项:主键默认非空,默认唯一性约束,只有主键可以设置自动增长(主键不一定自增,自增一定是主键)。

    2.设置主键的方式:

    ①:在定义列时设置:id INT UNSIGNED PRIMARY KEY。

    ②:在列定义完成后设置:PRIMARY KEY(id)。

    参考原文:https://www.cnblogs.com/zhouguowei/p/9268788.html

    展开全文
  • 主键约束唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下: 主键不允许空值,唯一索引允许空值。主键列在创建时,已经默认为空值 + 唯一索引了。 主键...

    主键和唯一索引的区别

    主键约束比唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下:

    1. 主键不允许空值,唯一索引允许空值。主键列在创建时,已经默认为空值 + 唯一索引了。

    2. 主键只允许一个,唯一索引允许多个。一个表最多只能创建一个主键,但可以创建多个唯一索引。

    3. 主键产生唯一的聚集索引,唯一索引产生唯一的非聚集索引(注:聚集索引确定表中数据的物理顺序,所以是主键是唯一的(聚集就是整理数据的意思)聚集(clustered)索引,也叫聚簇索引。)

    4. 主键可以被其他表引用为外键,而唯一索引不能。

    5. 主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。在 RBO 模式下,主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度

    6. 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。总体来说:主键相当于一本书的页码,索引相当于书的目录。其实主键和索引都是键,不过主键是逻辑键,索引是物理键,意思就是主键不实际存在,而索引实际存在在数据库中,主键一般都要建,主要是用来避免一张表中有相同的记录,索引一般可以不建,但如果需要对该表进行查询操作,则最好建,这样可以加快检索的速度。

    7. 对于innodb,主键毫无疑问是一个聚集索引。但是当一个表没有主键,或者没有一个索引,innodb会如何处理呢。请看如下规则

      如果一个主键被定义了,那么这个主键就是作为聚集索引

      如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引

      如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,该列的值会随着数据的插入自增。

    展开全文
  • 索引

    千次阅读 2013-02-26 20:36:02
    一、什么是索引  常见的索引有B-TREE索引、位图索引、全文索引,位图索引... B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持B-TREE索引的内
    一、什么是索引
     常见的索引有B-TREE索引、位图索引、全文索引,位图索引一般用于数据仓库应用,全文索引由于使用较少,这里不深入介绍。B-TREE索引包括很多扩展类型,如组合索引、反向索引、函数索引等等,以下是B-TREE索引的简单介绍:

     B-TREE索引也称为平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用于提升查询性能和唯一约束支持B-TREE索引的内容包括根节点、分支节点、叶子节点。B-TREE索引如下图所示:


                                                           

    如果我们把一个表的内容认为是一本字典,那索引就相当于字典的目录,如下图所示:


                                                        

     图中是一个字典按部首+笔划数的目录,相当于给字典建了一个按部首+笔划的组合索引。
    一个表中可以建多个索引,就如一本字典可以建多个目录一样(按拼音、笔划、部首等等)。
    一个索引也可以由多个字段组成,称为组合索引,如上图就是一个按部首+笔划的组合目录。


    二、索引的作用:
    索引是什么我们先说到这里,以后我们会进一步的讲述,索引不单是对列排序那么简单,在排序的基础上,它还按数据结构中B树的方式存储,这个就是后话了。下面我们说一下,索引有什么用。索引的作用,只有两个字,就是“查找”。它是为了加快查找速度。对于无序的一组数据,想在其中查找某一个数,只能逐个进行对比。而在有序的数列中查找某一个数,则有许多种方法,比如折半查找等等,ORACLE采用的是B树法。通常在表中某一列搜索数据时,如果此列上建立的有索引,ORACLE会自动的在索引中利用搜索算法,快速的找到要查找的值,再取出对应行的行地址,根据此行地址一表中取出行数据。这个速度比没有索引时要快很多倍。
    下面,我们先建立一个索引,仍后试一下使用索引查找数据,比不用索引时速度到底有什么不同。


    三、索引的创建
    索引的创建语法:CREATE INDEX 索引名 ON 表名(列1,列2,……)
    索引可以按照一个表的多个列创建,这样的索引称为复合索引。对于复合索引,ORACLE先按列1排序,如果列1的值有重复的,再按列2排序,等等。
    下面我先创建一个比较大一点的表,行数多一点,这样我们对比通过索引查找和无索引时的查找,它们间的差别更明显。


    gyj@OCM> drop table t5;


    Table dropped.


    gyj@OCM> create table t5 as select * from dba_objects;


    Table created.


    我通过DBA_OJECTS表,创建T5表。DBA_OBJECTS表存储数据库中所有对象的相关信息,行比较多。但是这样行还是不够多


    gyj@OCM> insert into t5 select * from t5;


    72804 rows created.


    gyj@OCM> /


    145608 rows created.


    gyj@OCM> /


    291216 rows created.


    gyj@OCM> /


    582432 rows created.


    gyj@OCM> commit;


    Commit complete.


     我再如上将T5中的行读出来,插入到T5中,反复几次,让T5表的行数至少有一百万行左右。
    gyj@OCM>  select count(*) from t5;


      COUNT(*)
    ----------
       1164864


    最终,我的T5表中的100多万行。
    然后,我们打开ORACLE的计数器:


    gyj@OCM> set timing on   
    gyj@OCM> select * from t5 where object_name='T5';
    (结果略)
    Elapsed: 00:00:00.25


    用时0.25秒。因为ORACLE会在内存中缓存一些信息,因此,上面的命令,应该多执行几次,等待每次执行时间没有太大的变化,取这个执行时间稳定下来的结果,这样比较准确。我这里,没有建索引时,用时0.15秒。
    因为是在OBJECT_NAME列查找,我就在OBJECT_NAME列上,建立一个索引:


    gyj@OCM> create index idx_t5_object_name on t5(object_name);


    Index created.


    创建索引的命令,非常简单,我就不再说了。下面我已经以T5表的OBJECT_NANE列为准,创建了索引,通常说我为T5表的OBJECT_NAME列创建了索引,下面再次在这个列中进行查询,


    gyj@OCM> select * from t5 where object_name='T5';
    (结果略)
    Elapsed: 00:00:00.01


    当每次的执行时间稳定下来后,当有索引后,执行时间是0.01秒。
    执行时间有非常明显的下降,而且,随着表中行数的进一步增加,按索引搜索和直接在表中搜索所耗时间,差距会更大。使用索引进行查找的优势,会更明显。
    索引的使用是透明的,在这条“select * from t5 where object_name='T5'”语句中,我们完全看不到有索引的影子,只要“WHERE 条件”所涉及的列,创建有对应的索引,ORACLE会自动的选择使用索引。
    由于索引对于加快查找速度有显著效果,我们可以在经常需要查找的列上,都创建索引。但是索引的数量也不适合太多。因为每一个索引都会有相应的索引维护操作。你每在表中插入一行,ORACLE会将相应列的值,插入进索引。每删除一行,或修改索引对应列的值,ORACLE也都要对应的修改索引。在对表进行DML时,所引起的对索引的DML操作,就是索引维护。它加重了DML操作时的负担,因此,索引是不适合建太多的,否则,DML操作时的速度将更慢。


    四、基于函数的索引
    我们仍以刚才的例子为准,在OBJECT_NAME列上有索引。下面,我们试一下如下命令:
    gyj@OCM> select * from t5 where lower(object_name)='t5';
    (结果省略)
    Elapsed: 00:00:00.64


    它的稳定时间,是0.64秒左右。虽然我们的条件仍然涉及到了OBJECT_NAME列,但上面的命令显然没有使用索引。因为它的执行时间很慢。在使用索引查找时,速度只有0.01秒左右。现在是0.64秒。没有使用索引的原因是这语句的条件:lower(object_name)='t5' ,先把OBEJCT_NAME转为了小写,再和小写的t5比较。一旦像这样对列名进行了处理,索引将不会被使用。如果经常要对此列转换为小写再比较,为了使用索引,你可以专门创建“基于函数的索引”,命令如下:


    gyj@OCM> create index idx_f_t5_obname on t5(lower(object_name));


    Index created.


    和上面创建普通索引的命令基本相同,只是将列名换为了LOWER(列) ,好,下面再试查询的速度有没有提高:


    gyj@OCM> select * from t5 where lower(object_name)='t5';
    (结果省略)
    Elapsed: 00:00:00.01


    稳定耗时是00: 00: 00.01 。0.01秒,比没有基于函数索引前的0.64秒快了很多倍。
    这就是基于函数的索引。


    五、索引创建指南

    这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件,如下图所示:

                                                               

     1、字段的选择性很好(主键最佳)。
     2、字段出现在查询条件中,并且查询条件可以使用索引。
     3、语句执行频率高,一天会有几千次以上。
     4、通过字段条件可筛选的记录集很小。


    六、删除索引
    索引会带来额外的索引维护操作,因此,不经常使用的索引,我们应该及时的删除它,以减少DML操作时的负担。删除索引命令如下,把上面刚建的基于函数的索引删掉:


    gyj@OCM> drop index idx_f_t5_obname;                    


    Index dropped.





    **********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
    Name:    guoyJoe

    QQ:        252803295

    Email:    oracledba_cn@hotmail.com

    Blog:      http://blog.csdn.net/guoyJoe

    ITPUB:   http://www.itpub.net/space-uid-28460966.html

    OCM:     http://education.oracle.com/education/otn/YGuo.HTM
     _____________________________________________________________
    加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!

    答案在:http://blog.csdn.net/guoyjoe/article/details/8624392

    Oracle@Paradise  总群:127149411

    Oracle@Paradise No.1群:177089463(已满)

    Oracle@Paradise No.2群:121341761

    Oracle@Paradise No.3群:140856036


    展开全文
  • 之所以这个结构称为堆,是因为它不以任何人为指定的逻辑顺序进行排列。而是按照分区组队数据进行组织。就是说,是按照磁盘的物理顺序。只要需要读取的数据文件没有文件系统碎片(注意和下面提到的索引的碎片区分)...

    一、堆(Heap)

    之所以这个结构称为堆,是因为它不以任何人为指定的逻辑顺序进行排列。而是按照分区组队数据进行组织。也就是说,是按照磁盘的物理顺序。只要需要读取的数据文件没有文件系统碎片(注意和下面提到的索引的碎片区分),这个读取过程在磁盘中就可以连续的进行,没有多余的磁盘臂移动。而磁盘臂移动是I/O操作中开销最大的操作。

    堆使用一个bitmap结构来管理数据的分配。也就是它会告诉你两个结果,这个区是分配了,还是没有分配。每一个区中的物理顺序如下图。

    (此图倒数第二行的数字有错误,应该加1)

    对于新插入的数据,堆只管在最后一条数据的后面的一个空闲位置保存新插入的数据,不保持任何的逻辑顺序。比如拿order表举例,如果先插入orderid 4,5,6, 假设在位置1:176、 1:177、1:178这三个位置。这时再插入1,这时保存的数据就变味4,5,6,1,  1保存在 1:179的位置。

    PS:IAM链知识

    Sytem_internals_allocation_units存放第一个数据页和第一个IAM页的指针。IAM按照数据页的顺序存放数据页的指针。数据页之间并无直接链接。

    接下来查看IAM的信息,如下:

    Slot 0 = (1:79)                      Slot 1 = (1:89)                      Slot 2 = (1:90)

    Slot 3 = (1:93)                      Slot 4 = (1:94)                      Slot 5 = (1:109)

    Slot 6 = (1:110)                     Slot 7 = (1:114)  

    IAM: Extent Alloc Status Slot 1 @0x592EC0C2

     

    (1:0)        - (1:176)      = NOT ALLOCATED                              

    (1:184)      - (1:192)      =     ALLOCATED                              

    (1:200)      - (1:376)      = NOT ALLOCATED 

    加亮部分表明了IAM对应的分区信息,以及第一个数据页面指针指向79页。这与我们查询出的first_page值是一致的。一个IAM页面对应8个数据页,当超过8个数据页时,系统会从其对应的4GB空间(约512000个页面)中分配统一区的页面。当数据页超过可分配的页面数时,建立新的IAM页。

    这里只有8个slot,8个临时存放数据页,当分配数据页超过8个混合分区后,系统会为数据表分配统一分区。(这里的每一个分配区间都是从区的第一个开始算的,比如说192那个指的是192-199)

    每一个指针都指向一个数据页。当分配数据页超过8个混合分区后,系统会为数据表分配统一分区。这里,系统为数据表分配184~191, 192~199两个统一分区。每个分区包含8页,加上8个混合分区的页面,一共是25个页面。这25个页面中,使用了19个数据页,加上1个IAM页共使用了20个页面。因为统一分区是顺序分配的, 所以可以计算出从195~199的页面没有被使用。用DBCC可以验证这个推算。

    下面我们把插入的数据删除,然后再查看IAM的页面分配情况,发现页面分配不会因为数据删除而改变,数据页内仅仅是将数据清空而已。

    关于页头部需要注意:

    • 正如我们所期望的——页类型为10 ,。
    • 前页和后页指针都为NULL, 因为在IAM 链中没有其他的IAM 页。
    • slot 数目为2 :一个是IAM 头记录;另一个是位图。
    • 页几乎满了

    IAM 页头有下面的字段:

    • sequenceNumber
      • 这是IAM 页在IAM 链中的位置值。每一个IAM 页加入到IAM 链时该值增加1 。
    • status
      • 未用
    • objectId
    • indexId
      • 在SQL SERVER 2000 及以前版本,这是IAM 页所属的对象和索引的ID ;2005 和以后的版本这两个字段未用。
    • page_count
      • 字段未用——原来是用来跟踪单页分配数组中页数目。
    • start_pg
      • IAM 页映射了一个GAM 区间。这个字段存储了映射区间的首页ID.
    • 单页分配数组( Single Page Allocations array 
      • 这些是从混合分区中分配的页的数组。这个数组只存在IAM 链中的第一个页中(因为整个IAM 链中只需要跟踪8 个单独分配的页)。

    位图占用IAM 页剩下的空间,每一位表示GAM 区间中的每一个区。如果区被分配给该实体,那么对应位就置1 ,否则为0 。很明显,为不同实体映射同一GAM 区域的两个IAM 页不可能有相同的位被置上——DBCC CHECKDB 会检查这个。在上面的DBCC PAGE 输出中,你可以看出没有区分配给表。你会发现输出最多到272 页所在的区—— 这是因为数据文件就这么大。

    关于IAM 页还要注意两件事:

    • IAM 页是从混合区中分配而来的,且这些页不受监控。
    • 一个文件中分配的IAM 也可以用来跟踪另一个文件的区。

    IAM 

    如果我们一直增大文件并往表中插入数据,最终我们将需要另一个IAM 页来映射下一个GAM 区间。这就是IAM 链的由来。IAM 链表用来跟踪单个实体上的空间分配。这个链表是不排序的——IAM 页按添加的顺序加入链表中,每个IAM 页有一个数值,同样是以添加顺序增加的。

    “实体”的定义。到底是谁使用IAM 链?这个概念在SQL SERVER 2000 和2005 中区别很大。

    在SQL Server2000 中,下面每个实体都有一个IAM 链表:

    • 堆或聚集索引
      • 一个表只能选其一,不能两者皆有。它们的索引ID 分别为0 和 1 。
    • 非聚集索引
      • 它的索引ID 从2 到250 (就是说只有249 个索引)。
    • 表的完整LOB 存储
      • 对于堆或者聚集索引中的LOB 列,有时也被称为“ 文本索引” ,它拥有一个固定的索引ID 值255 。

    SQL SERVER 2000 及以前版本中每个兑现最多251 链表。我常总结为:在SQL SERVER 2000 中,每一个索引一个IAM 链(如果你还记得IAM 叫“ 索引分配映射” 的话,我觉得还是很贴切的)。

    分配单元(SQL SERVER 2005 及以后版本)

    现在在SQL SERVER 2005 及以后版本中,发生了一些变化。虽然IAM 链和IAM 页与以前是一模一样的,但是它们所对应的东西变了,而且现在一个表可以拥有750000 条IAM 链!现在IAM 链为三类东西映射分配空间:
    1 .堆和B 树(B 树是系统用来存储索引的内部结构)
    2 .LOB 数据
    3 .行溢出数据

    我们称这些分配空间的单元为分配单元(allocation units ),这三类分配单元的相应的内部名称为:
    1 .HOBT 分配单元(发音和指环王中的霍比特人一样)
    2 .LOB 分配单元
    3 .SLOB 分配单元(SMALL –LOB)
    对应的外部名称为:
    1 .IN_ROW_DATA 分配单元
    2 .LOB_DATA 分配单元
    3 .ROW_OVERFLOW_DATA 分配单元

    严格来说,它们不能再被称为IAM 链了,因为它们不再跟踪索引的分配空间了。只是它们还是IAM 页的链表,所以还被称为IAM 链,现在它跟踪的单元叫分配单元(allocation unit )。除了这些,和以前没有任何区别。关于IAM链的更多用途参考:http://blog.csdn.net/misterliwei/article/details/5943447

    SQL Server中的GAM页和SGAM页

    我们已经知道SQL Server IO最小的单位是页,连续的8个页是一个区。SQL Server需要一种方式来知道其所管辖的数据库中的空间使用情况,这就是GAM页和SGAM页。GAM(全局分配位图)是用于标识SQL Server空间使用的位图的页。位于数据库的第3个页,也就是页号是2的页。

    我们看到页内的数据通过16进制表示。也就是一个数字是4比特,两个是一字节。其中前4个字节0000381f是系统信息,slot1的后10个字节也是系统信息。其余的每位表示SQL Server的一个区的状态,0表示已分配,1表示未分配。下面我们就通过图1所示的GAM页来计算一下这个数据库所占的空间。

        我们可以看到,由于数据库刚刚创建,分配的空间在第4-8个字节就能表示,也就是0001c0ff。下面将0001c0ff由16进制化为2进制。结果是

        0000 0000 0000 0001 1100 0000 1111 1111

        通过计算,可以看出,上面的bit中有21个0,也就是目前数据库已经分配了21个区,我们知道每个区是8*8k=64K。因此算出这个数据库占用空间(21*64)/1024=1.3125MB≈1.31MB。

    那可能大家会有疑问了,那如果数据库增长超过一个GAM所能表示的区的范围那该怎么办?答案很简单,就是再创建一个GAM页,第二个GAM页的位置也可以通过图1中的信息进行计算。图1中slot1有7992个字节,其中前四个字节用于存储系统信息,后面7988字节用于表示区的情况,因此所能表示的区是7988*8=63904,横跨的页的范围是511232,所以第511232+1页应该是下一个GAM页,而页号就会是511232页。这个区间也就是所谓的GAM Interval,接近4GB。

    Shared Global Allocation Map Page

        通过GAM页可知,分配空间的最小单位是区。但假如一个非常小的索引或是表只占1KB,但要分给其64K的空间就显得过于奢侈了。所以当几个表或索引都很小时,可以让几个表或索引公用一个区,这类区就是混合区。而只能让一个表或索引使用的区就是统一区。SGAM位于数据库的第四页,也就是GAM的下一个页。页号为3。通过和GAM相同位置的bit组合,就能知道空间的状态。所能表示的几种状态如表1所示。

      GAM SGAM位
    未分配 1 0
    统一区或空间使用完的混合区 0 0
    含有可分配空间的混合区 0 1

    表1.SGAM和GAM

     

        通过SGAM和GAM的组合,SQL Server就能知道该从哪里分配空间。

        第二个SGAM页位于第二个GAM页之后,也就是页号为511233的页。依此类推。

    二、回归正题: 聚集索引(Clustered Index)

    聚集索引以B树的方式保存数据。由于在另一篇文章中已经详细的分析了B树,这里就不再详细说明。

    (PS:B-树

    是一种多路搜索树(并不是二叉的):

           1.定义任意非叶子结点最多只有M个儿子;且M>2;

           2.根结点的儿子数为[2, M];

           3.除根结点以外的非叶子结点的儿子数为[M/2, M];

           4.每个结点存放至少M/2-1(取上整)和至多M-1个关键字;(至少2个关键字)

           5.非叶子结点的关键字个数=指向儿子的指针个数-1;

           6.非叶子结点的关键字:K[1], K[2], …, K[M-1];且K[i] < K[i+1];

           7.非叶子结点的指针:P[1], P[2], …, P[M];其中P[1]指向关键字小于K[1]的子树,P[M]指向关键字大于K[M-1]的子树,其它P[i]指向关键字属于(K[i-1], K[i])的子树;

           8.所有叶子结点位于同一层;

           如:(M=3)

    B-树的搜索,从根结点开始,对结点内的关键字(有序)序列进行二分查找,如果命中则结束,否则进入查询关键字所属范围的儿子结点;重复,直到所对应的儿子指针为空,或已经是叶子结点;

    B-树的特性:

           1.关键字集合分布在整颗树中;

           2.任何一个关键字出现且只出现在一个结点中;

           3.搜索有可能在非叶子结点结束;

           4.其搜索性能等价于在关键字全集内做一次二分查找;

           5.自动层次控制;

           由于限制了除根结点以外的非叶子结点,至少含有M/2个儿子,确保了结点的至少利用率,其最底搜索性能为:

       其中,M为设定的非叶子结点最多子树个数,N为关键字总数;

           所以B-树的性能总是等价于二分查找(与M值无关),也就没有B树平衡的问题;

           由于M/2的限制,在插入结点时,如果结点已满,需要将结点分裂为两个各占M/2的结点;删除结点时,需将两个不足M/2的兄弟结点合并

    B+树

           B+树是B-树的变体,也是一种多路搜索树:

           1.其定义基本与B-树同,除了:

           2.非叶子结点的子树指针与关键字个数相同;

           3.非叶子结点的子树指针P[i],指向关键字值属于[K[i], K[i+1])的子树(B-树是开区间);

           5.为所有叶子结点增加一个链指针;

           6.所有关键字都在叶子结点出现;

           如:(M=3)

    B+的搜索与B-树也基本相同,区别是B+树只有达到叶子结点才命中(B-树可以在非叶子结点命中),其性能也等价于在关键字全集做一次二分查找;

           B+的特性:

           1.所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;

           2.不可能在非叶子结点命中;

           3.非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;

           4.更适合文件索引系统;

     

    小结

           B树:二叉树,每个结点只存储一个关键字,等于则命中,小于走左结点,大于走右结点;

           B-树:多路搜索树,每个结点存储M/2到M个关键字,非叶子结点存储指向关键字范围的子结点;

           所有关键字在整颗树中出现,且只出现一次,非叶子结点可以命中;

           B+树:在B-树基础上,为叶子结点增加链表指针,所有关键字都在叶子结点中出现,非叶子结点作为叶子结点的索引;B+树总是到叶子结点才命中;

    继续拿Order表举例,Order表中的全部数据都保存在B树中的叶层(leaf level)中,其他层只是起到一个索引的作用,并不包含任何数据。叶层是一个双向链表结构,并按照聚集索引的主键的逻辑顺序排列。因此逻辑顺序是用指针来维护。

    我们在图中页层所见到是逻辑顺序,和上图堆中所展示的物理顺序要区分开来。

    为什么我一再强调逻辑顺序和物理顺序?因为理解这很重要。

    如图所示,聚集索引中除了B树之外,仍然维护了一个IAM结构,而这个结构就能保证在需要的时候,我们能按照物理顺序而不是逻辑顺序去在叶层中读取数据。

    那么什么时候才需要呢?先看什么是索引碎片。(除了IAM之外,还有一个指向逻辑上第一块数据的指针,IAM保存物理顺序,页层保存逻辑顺序,若通过物理顺序访问,速度最快,可使用IAM,若使用逻辑顺序带排序,则使用指向头的指针。)

    2.1 索引碎片

    数据库中之所以会出现碎片,是因为B树的页拆分造成的。具体页拆分请参考数据结构,这里要说的是由于拆分所产生的新页不保证一定就会在被拆分的页的后面,而是可能出于文件的任何位置。这就是“无序页”。换句话说,也就是在列表中处于后面位置的元素,在物理文件中却排在前面。如果你明白指针的定义的话,这句话并不难理解。因为叶层的双向列表就是以指针来维护逻辑顺序。

    因此在按逻辑顺序读取的时候,由于无序页的存在,可能造成磁臂频繁的摆动。别忘记,磁盘摆动是I/O中开销最大的操作。而I/O往往是一个系统的瓶颈所在。

    如果按照物理顺序来读取,也就是unordered读取,就会避免上面所产生的问题。再次强调,unordered是指不按逻辑顺序读取,所以叫unordered。

    (PS:页拆分http://www.cnblogs.com/TeyGao/p/3649982.html

    很多同行会问起页拆分的相关的问题,自己对页拆分页迷迷糊糊,有点云里雾里的感觉,今天来测试测试。

    首先生成测试数据

    --=========================================
    --使用TestDB数据库来测试
    USE TestDB
    GO
    DROP TABLE TB01
    GO
    --=======================================
    --创建测试表TB01
    CREATE TABLE TB01
    (
        ID INT PRIMARY KEY,
        C1 NVARCHAR(MAX)
    )
    GO
    --=======================================
    --插入420条数据,所有数据存放在一个8KB的数据页中
    INSERT INTO TB01(ID,C1)
    SELECT T.RID,N'C' FROM (
    SELECT 
    ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
    FROM sys.all_columns
    ) AS T
    WHERE T.RID<422
    AND T.RID<>418
    --====================================

    现在表TB01上有一个数据页(接近填满),使用DBCC查看

    然后尝试插入数据导致页拆分:

    复制代码
    --====================================
    --插入一行数据
    INSERT INTO TB01(ID,C1)
    SELECT 418,REPLICATE(N'1',4000)
    --====================================
    --查看数据页
    DBCC IND('TestDB','TB01',1)
    复制代码

    我们可以很清楚地发现,在插入一行数据后,数据页由原来的一页变成了9页(一个非叶子节点页和8个叶子节点页),是不是很不科学呢? 新插入的数据只需要一个数据页来存放,加上原来的数据,只需要2个数据库便可以存放,为什么会造成这么多页面使用呢?

    通过上面的图,可以清楚看到数据有两层,非叶子节点(也是根节点)页是5170,使用该页来查看数据分布情况:

    --===================================
    --查看非叶子节点来查看数据和页的对应情况
    DBCC PAGE('TestDB',1,5170,3)

    观察上图的ID,我们可以发现以下规律

    211=1+420/2

    316=211+420/2/2

    368=316+420/2/2/2

    394=368+420/2/2/2/2

    407=394+420/2/2/2/2/2

    从上面的数据不难看出,每页数据逐渐一半一半地减少。再通过sys.fn_dblog(NULL,NULL)来查看事务,最后一次插入操作引发1次插入事务和8个页拆分事务。

    由此,我们推断出在上面的插入过程中,发生了以下操作:

    1. 新事务开始,一行新数据需要插入到数据页中,该数据行不是数据页最尾数据行

    2. 判断页中剩余空间,发现数据页不能存放新插入行,需要页拆分

    3. 开启一个新事务,将页中一半数据移动到一个新的页面,关闭事务

    4. 循环第2步和第3步,直到有一数据页能存放新插入的行

    5. 插入数据,提交事务

     

    到此,很多人就会疑问,拆分一半到底是数据行数的一半还是数据占用空间大小的一半呢?

    让我们再做一个实验

    复制代码
    --==========================================
    --清除表中数据
    TRUNCATE TABLE TB01
    --=======================================
    --插入198条数据,所有数据存放在一个8KB的数据页中
    --前99条数据和后99天数据的大小不相同
    INSERT INTO TB01(ID,C1)
    SELECT T.RID,N'C' FROM (
    SELECT 
    ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
    FROM sys.all_columns
    ) AS T
    WHERE T.RID<100
    
    INSERT INTO TB01(ID,C1)
    SELECT T.RID,N'CCCCCCCCCCCC' FROM (
    SELECT 
    ROW_NUMBER()OVER(ORDER BY object_id) AS RID 
    FROM sys.all_columns
    ) AS T
    WHERE T.RID>100
    AND T.RID<200
    --====================================
    --插入一行数据导致页拆分
    INSERT INTO TB01(ID,C1)
    SELECT 100,REPLICATE(N'1',2000)
    复制代码

    同样适用根节点来数据分布:

     

     由于后99行数据占用的空间大小较大,在页拆分时,没有将后99条全部拆分到新的数据页上,因此我们得出结论,页拆分时是按照数据占用空间大小来拆分的,与数据行数无关。

    --=====================================================================================================

    总结:

    1.发现在页拆分时,会按照页中数据占用空间的情况,将占用空间一半的数据移动到新的数据页上

    2.如果拆分后仍无法存放新数据,则继续页拆分,知道有数据页可以存放新数据为止,因此一次插入操作可能会引起多次页拆分。

    3.每次页拆分会被当成一个事务处理,页拆分的事务单独提交(在提交插入事务之前已提交),及时插入失败,页拆分的事务也不会回滚。

    4.更新导致的页拆分情况与插入导致的页拆分类似

     

    PS:

    1. 在测试中,未发现没有按照一半空间拆分的情况,但没有找到相关官方文档来证明。

    总之就是为了让逻辑顺序和聚集索引键顺序一致,有时候需要在非连续区域放置某一条记录,为了让这条记录满足顺序,必须把原连续记录拆分出来,改变新纪录所在页的前后指针来满足顺序。

    2.2 索引的层数

    索引的层数,也就是B树的高度,直接表明了一次查找操作在页面读取方面的开销。一些执行计划如Nested loop联接会多次调用查找操作。因此理解这个概念很重要。

    树的高度主要和以下几个因素相关

    1. 表的总行数。
    2. 平均一行保存数据的大小。
    3. 页的平均密度。因为不是每一页都应该填充满数据,这样可以减少页拆分的次数。
    4. 一页所能容纳的行数。

     

    具体公式也很简单,3级索引大概能容纳4百万行,4级索引大概能容纳4亿行数据。因此通常一张表的索引层数通常为3到4级。 

     

    3、非聚集索引(NonClustered Index)

    非聚集索引也是以B树组织的。和聚集索引的区别就在于它的叶层并不包含所有的数据。在默认情况下它只包含了键列的数据,并包含了一个行定位符(row locator)。这个行定位符的具体内容取决于它建立在以堆形式的表还是以B树组织的表,换句话说也就是这张表是否建立了聚集索引会影响到非聚集索引的行定位符。如果是建立了聚集索引,那么这个行定位符就是一个聚集键,我们通过这个聚集键再次查找聚集索引上的数据。

     聚集索引上的非聚集索引

    如果表是堆组织结构的,那么它就是一个直接指向数据所在行的物理指针。

    下图是建立在堆上的非聚集索引

     

    3.1 如果非聚集索引包含了我们需要查找的所有数据

    这种情况我们通常叫做索引覆盖。

    正因为非聚集索引有着和索引一样的结构,并且由于非聚集索引所包含的列少,因此数据量就小,使得叶层的一页能包含更多的行,因此进行一次I/O页读取的动作的时候,就能读取进更多的行。因此查找效率是最高的。

    举个不恰当的例子,美女征婚,应征人员的个人信息表有 “姓名、 德、 智、 体 、美、 劳、 高、 富、 帅”这几列,按姓名排序。美女只关注“高、 富、 帅”这三列的内容,为了更快的筛选,我们帮美女按照个人信息表的内容重新制作了一张表,这张表忽略了其他信息,只保留了高、富、帅和姓名,筛选效率当然就比原来关注更多内容时要高。

     

    3.2 如果非聚集索引不包含我们需要查找的所有数据

    通俗的说这时我们就需要从非聚集索引中所包含的线索去包含所有数据的表中去找。

    按照我们之前的定义换句话来说,就是通过非聚集索引中的行定位符去聚集索引或者堆中去查找所需的数据。

     

    二、通过实例来说明上述概念

     

    我们创建一张Order表,表上建立了几个索引

    1.为orderdate列创建了聚集索引

    2.为orderid列创建了非聚集索引

    1.1.1 只为获取整张表的数据,对数据顺序不关心

    SELECT [orderid]

          ,[custid]

          ,[empid]

          ,[shipperid]

          ,[orderdate]

          ,[filler]

      FROM [Performance].[dbo].[Orders]

     

     

     

    分析:由于我们需要获取整张表的数据,因此不需要任何筛选也不需要任何排序。因此我们按照磁盘物理顺序读取出所有数据无疑是最快的选择。 所以已排序为False. 再次说明这里的已排序的顺序是聚集键的逻辑顺序,和物理顺序不同。

     

    通过IAM在聚集索引的叶层扫描。在这种情况下无论表是以堆或者B树的形式组织情况都类似。

    不管是堆还是B树,都是用IAM来保存的,IAM里分区,区里有页,页里可以放数据,也可以放索引,在扫描时只用检查其类型,排除非数据即可按物理顺序读取,速度较快。

     

    (1000000 行受影响)

    表'Orders'。扫描计数1,逻辑读取25081 次,物理读取5 次,预读23545 次,lob 逻辑读取0 次,lob 物理读取0 次,lob 预读0 次。

     

    1.1.2 按聚集键顺序获取整张表的数据

    对于Orders表,以orderdate为聚集键,因此如果我们使用顺序查询,就可以直接获取所需要的数据。

    image

     

     

     

    这是我们就不再通过IAM来对叶层进行扫描,而是通过叶节点的指针来进行扫描。

     

    1.1.3 如果不按照聚集键,而是按照其他列的顺序来获取整张表

    我们并没有把orderid设置成聚集索引的键,而是把它设成了非聚集索引的键。因此在返回整张表的内容时:

    1.非聚集索引键列orderid对我们没有意义,因为我们期望返回的是整张表的内容,而非聚集索引只包含键列的内容。

    2.聚集键列orderdate的顺序在这里对我们是没有什么用的。

     

    由上面的推论可以知道,这时我们所创建的索引对我们都没有任何帮助。因此,与其按照逻辑顺序返回,不如按照最快速的无序返回,再把返回的结果集排序。而计划证明了我们的猜想。

    image

     

    1.1.4 如果我们要查询的内容,正好在非聚集索引里面就已经包含了

    和上面查询基本类似,区别在于我们在查询结果中把非聚集索引中不包含的列全部删除了,这时非聚集索引就形成了覆盖。我们就可以利用非聚集索引进行查询。

    image


    一些索引建议:
    1.对于长字符串,比如VARCHAR(80)这种类型的索引要比更为紧凑数据类型的索引大很多。同样地,你也不太可能对长字符串列进行全匹配查找。

     

    http://www.cnblogs.com/lwzz/archive/2012/08/05/2620824.html

     

    补充:在基于聚集索引的非聚集索引上,叶子节点存放的是聚集索引的键,所以在找到之后还要通过聚集索引键来查找,之所以不能用物理地址是因为,聚集索引的数据物理地址是不固定的,可能被页拆分而改变地址,所以用键来保存是比较稳妥的做法。(至于修改聚集索引键做的操作就需要再查查了)

    转载于:https://www.cnblogs.com/guangshan/p/4575523.html

    展开全文
  • Java---MySQL约束

    2020-04-02 14:58:32
    Java—MySQL约束 非空约束 唯一约束称为唯一索引 主键约束 主键约束-自动增长 外键约束 外键约束:级联操作
  • 索引类型介绍:主键索引primary key() 要求关键字不能重复,不能为null,同时增加主键约束 主键索引定义时,不能命名唯一索引unique index() 要求关键字不能重复,同时增加唯一约束普通索引index() 对关键字没有...
  • 索引类型介绍:主键索引primary key() 要求关键字不能重复,不能为null,同时增加主键约束 主键索引定义时,不能命名唯一索引unique index() 要求关键字不能重复,同时增加唯一约束普通索引index() 对关键字没有...
  • 【判断题】唯一索引也称为唯一约束。【判断题】用户可以通过如下命令使用索引 SELECT * FROM 索引名;【判断题】要提高数据库的查询速度,应该尽可能多的建立索引。【单选题】以下不是声音三要素是【简答题】石材分为...
  • MySQL学习 - 索引

    2020-04-07 00:21:17
    索引在MySQL中也称为“键 key”,是存储引擎用于快速找到记录的一种数据结构。通过构建合理的索引,可以优化查询性能,提高查询效率,同时通过添加约束,也保证了字段的唯一性和数据的完整性。通常我们在表中的某一...
  • 索引组织表

    2011-05-30 23:44:00
    索引组织表 · 前面五种索引的基表数据存储是随机的,这种表称为堆表; · 索引组织表的数据存储在二叉树索引中,所以,如果通过主键来存取数据,...· 索引组织表上不能建唯一约束不能将索引组织表建立在簇上
  • 索引组织表 · 前面五种索引的基表数据存储是随机的,这种表称为堆表; · 索引组织表的数据存储在二叉树...· 索引组织表上不能建唯一约束不能将索引组织表建立在簇上面; · 索引组织表的语法要点: Ø ORGANI
  • 数据库的约束详解(2) —— 外键

    千次阅读 2015-07-19 20:17:24
    数据库的约束详解(2) —— 外键  外键约束主要用于保证一个或两个数据表之间的参照完整性。... 建立外键约束时,MySQL会为该列建立索引。    1. 给表的某一列添加外键约束 alter tabl
  • 索引组织表 · 前面五种索引的基表数据存储是随机的,这种表称为堆表; · 索引组织表的数据存储在二叉树索引...· 索引组织表上不能建唯一约束不能将索引组织表建立在簇上面; · 索引组织表的语法要点: ORG
  • oracle 数据库应用随写

    2011-06-30 11:02:59
    1、oracle 强烈建议,任何应用程序的库表至少需要创建两个...2、外键也称为外部键约束,是保障完整性约束唯一方法,也是关系数据库的精髓所在。 3、约束,也称完整约束,oracle系统的完整约束包括主键、外键及CHE...
  • 这个唯一标识符可能是一列,可能是几列的组合,称为主键。就是说,表中的主键在所有行上必须取唯一值。强制实体完整性的方法有:索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性。 ​ 如:student表中sno...
  • 2、唯一索引:加速查找和唯一约束(可含null) 加入索引:create unique index 索引名 on 表名(列名) 删除:drop index 索引名 on 表名 3、主键索引 加入索引:alter table 表名 add primary key(列名) 删除索引:...
  • MySQL会给唯一约束的列上默认创建一个唯一索引; create table temp ( id int not null, name varchar(25), password varchar(16), --使用表级约束语法, constraint uk_name_pwd unique(name, password) ); 表示...
  • 6.3.映射

    2020-12-23 19:27:08
    映射则进一步突破了对索引的类型约束,将其定义为可用任意类型表示的键,而与之对应的被检索信息则被称为值,因此映射可被视作键值对的集合。 映射以散列表作为底层结构,用键的哈希作为值的索引,因此键无序且...
  • 1唯一约束unique和主键key的区别?1、什么是数据的存储引擎?存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的,所以存储...
  • 1唯一约束unique和主键key的区别? 1、什么是数据的存储引擎? 存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式...
  • 数据库资料

    2018-04-01 22:00:33
    回顾SQL Server的约束约束的目的:确保表中数据的完整型常用的约束类型:主键约束(Primary Key Constraint):要求主键列数据唯一,并且不允许为空唯一约束(Unique Constraint):要求该列唯一,允许为空,但只能...
  • 在创建表时,经常会创建该表的主键、外键、唯一约束、Check约束等  语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1,column2,…..column_n) ...
  • 2009达内SQL学习笔记

    2010-02-10 19:46:58
    直接用一句语句可以,如下 export ORACLE_HOME=/oracledata/.../bin: 一、注意事项: 大小写不敏感,即不区分大小写。提倡关键字大写,便于阅读和调式。 “!”在SQL环境下执行Unix命令。 SQL语句是由简单的...
  •  Oracle Database 11g的推出让关注Oracle的人欣喜万分,不过不免有些担心,因为此前还没有合适的书系统而深入地介绍这个新版本。要想学习和掌握它的诸多新特性,只能从Oracle手册入手,而数万页的11g手册不免让...
  • 9.6.3 使用唯一索引 416 9.7 高级的索引使用案例 416 9.7.1 外键索引 416 9.7.2 索引视图 419 9.8 最佳实践 422 9.9 总结 423 第10章 并发编程 425 10.1 什么是并发 426 10.2 查询优化的基础知识 427 10.3 ...
  • 9.6.3 使用唯一索引 416 9.7 高级的索引使用案例 416 9.7.1 外键索引 416 9.7.2 索引视图 419 9.8 最佳实践 422 9.9 总结 423 第10章 并发编程 425 10.1 什么是并发 426 10.2 查询优化的基础知识 427 10.3 ...
  • Firebird数据库中文版

    热门讨论 2011-11-29 08:48:43
    包括但不限于"FULL/LEFT/RIGHT [OUTER] JOIN , UNION, DISTINCT , 子查询 (IN, EXISTS),内部函数 (AVG, SUM, MIN, MAX, COALESCE, CASE, ..), 主键,外键,唯一索引以及所有通用的数据类型。 Firebird还实现了域,...
  • LINGO软件的学习

    2009-08-08 22:36:50
    稠密集包含了父集成员的所有组合(有时也称为父集的笛卡尔乘积)。稀疏集仅包含了父集的笛卡尔乘积的一个子集,可通过显式罗列和成员资格过滤器这两种方式来定义。显式罗列方法就是逐个罗列稀疏集的成员。成员资格...
  • 3.2.3 索引唯一扫描 71 3.2.4 索引范围扫描 72 3.2.5 索引全扫描 74 3.2.6 索引跳跃扫描 77 3.2.7 索引快速全扫描 79 3.3 联结方法 80 3.3.1 嵌套循环联结 81 3.3.2 排序-合并联结 83 3.3.3 散列联结 84 ...
  • 3.2.3 索引唯一扫描 71 3.2.4 索引范围扫描 72 3.2.5 索引全扫描 74 3.2.6 索引跳跃扫描 77 3.2.7 索引快速全扫描 79 3.3 联结方法 80 3.3.1 嵌套循环联结 81 3.3.2 排序-合并联结 83 3.3.3 散列联结 84 ...

空空如也

空空如也

1 2
收藏数 38
精华内容 15
关键字:

唯一索引也称为唯一约束