您的位置:金沙游乐场85155 > 大数据库 > 探索SQL Server元数据(三):索引元数据金沙851

探索SQL Server元数据(三):索引元数据金沙851

发布时间:2019-10-02 09:54编辑:大数据库浏览(96)

    总结

     在数据库中有很多有价值的信息都在索引上。一旦表的数量变大,很容易让表出现一些问题,比如无意中没有聚集索引或主键,或者有重复的索引或不必要的统计信息等。我们通过掌握如何查询这些索引的动态视图后能够快速查询定位使用表的信息,方便我们预防和解决这类问题,这些基础方法已经在DBA和数据库开发的工作中变得越来越重要了,

    SQL-Server Helper

    1. 下面给出 SQL-Server 数据库命令执行的几种方式样例

    金沙85155登入 1金沙85155登入 2

     1 public static bool ExecuteSqlNoResult(string sql)
     2 {
     3     using(SqlConnection conn = new SqlConnection())
     4     {
     5         try
     6         {
     7             conn.ConnectionString = RVCConnectingString;
     8             conn.Open();
     9             SqlCommand command = new SqlCommand(sql, conn);
    10             command.ExecuteNonQuery();
    11             return true;
    12         }
    13         catch(Exception ex)
    14         {
    15             // 
    16             return false;
    17         }
    18     }
    19 }
    

    [1]. 执行SQL,无返回值

    其中,SqlCommand表示要对SQL Server数据库执行的一个Transact-SQL语句或存储过程。无法继承此类。

    金沙85155登入 3金沙85155登入 4

     1 public static bool ExecuteSqlWithResult(string sql, out DataTable dtResult)
     2 {
     3     using(SqlConnection conn = new SqlConnection())
     4     {    
     5         dtResult = new DataTable(); 
     6         try
     7         {
     8             conn.ConnectionString = DatabaseConnectingString;
     9             conn.Open();
    10             SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
    11             sda.Fill(dtResult);
    12             return true;
    13         }
    14         catch(Exception ex)
    15         {
    16             // 
    17             return false;
    18         }
    19     }
    20 }
    

    [2]. 执行SQL,返回结果

    其中,SqlDataAdapter表示用于填充System.Data.DataSet和更新SQL Server数据库的一组数据命令和一个数据库连接。无法继承此类。

    金沙85155登入 5金沙85155登入 6

     1 public static bool ExecuteSqlTrans(List<string> sqlList)
     2 {
     3     using(SqlConnection conn = new SqlConnection())
     4     {
     5         SqlTransaction sqlTrans = null;
     6         try
     7         {
     8             conn.ConnectionString = DatabaseConnectingString;
     9             conn.Open();
    10             
    11             sqlTrans = conn.BeginTransaction();
    12             SqlCommand command = new SqlCommand();    
    13             command.Transaction = sqlTrans;
    14             command.Connection = conn;
    15             
    16             string sql = null;
    17             foreach(string sqlTmp in sqlList)
    18             {
    19                 sql = sqlTmp;
    20                 command.CommandText = sql;
    21                 command.ExecuteNonQuery();
    22             }
    23             
    24             // 提交事务(前面执行无误的情况下)
    25             sqlTrans.Commit();
    26             return true;
    27         }
    28         catch(Exception ex)
    29         {
    30             if(sqlTrans != null)
    31             {
    32                 // 执行出错,事务回滚
    33                 sqlTrans.RollBack();
    34             }
    35             retrun false;
    36         }
    37     }
    38 }
    

    [3]. 批量执行SQL,以事务方式

    其中,SqlTransaction表示要在 SQL Server 数据库中处理的 Transact-SQL 事务。无法继承此类。

    2. 判断表、存储过程等的存在性

    // 判断普通表
    IF NOT EXISTS( SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TableName') AND OBJECTPROPERTY(ID, 'IsTable')=1 )
    BEGIN
    CREATE TABLE TableName(
        ... ...
    )
    END
    
    // 判断存储过程
    IF exists(select 1 from sysobjects where id=object_id('ProcName') and xtype='P')
        DROP PROC ProcName
    GO
    
    // 判断临时表
    IF object_id('tempdb..#TAB_Tmp_Name') is not null 
    BEGIN
        DROP table #TAB_Tmp_Name
    END;
    CREATE table #TAB_Tmp_Name(
      ... ...  
    );
    

    3

     

    查找没有聚集索引的表

    关于索引,您可以找到很多有趣的东西。例如,这里有一种快速查找表的方法,无需使用聚集索引(堆)

    -- 展示所有没有聚集索引的表名称
    SELECT  object_schema_name(sys.tables.object_id)+'.'
        +object_name(sys.tables.object_id) AS 'Heaps'
          FROM sys.indexes /* see whether the table is a heap */
          INNER JOIN sys.tables ON sys.tables.object_ID=sys.indexes.object_ID
          WHERE sys.indexes.type = 0;
    

     f. 主键 PK ~ 外键 FK 

     主键:保证全局唯一性;
     外键:建立和加强两个表数据之间链接的一列或多列,强制引用完整性,可以有效防止误删;
      主键约束 ~ 外键约束 ~ 唯一约束
      - 主键约束
      主键约束用于实现实体完整性,每个表的主键有且只能有一个,主键列不能包含null值。声明联合主键采用第2、3种方法。创建PK约束,具体参见大话数据库或 三种方法创建主键约束
      系统默认生成的主键约束名为:PK_表名_序列串号
      - 外键约束
      外键约束用于实现参照完整性,一个表A:foreign key指向另一个表B:primary key,表B是主表,表A是从表。外键约束创建三种方法,参见大话数据库或者 三种方法创建外键约束
      系统默认生成的外键约束名为:FK_表名_字段名_序列串号
     示例主/外键的三种创建方法:
      1. 创建table时,直接在字段后面声明为 primary key 或者 foreign key

    1  create table orders(
    2         orderID varchar(10) not null primary key,
    3         orderProduct varchar(30) not null,
    4         personID varchar(20) foreign key references persons(personID)
    5   );
    

      2. 创建table时,全部字段声明之后,添加主键和外键的约束语句

    1  create table orders(
    2         orderID varchar(10) not null,
    3         orderProduct varchar(30) not null,
    4         personID varchar(20) not null,
    5         constraint PK_orders primary key(orderID),
    6         constraint FK_orders_personID foreign key(personID) references persons(personID)
    7  );
    

      3. 在table已创建后,为表添加主外键约束

    1  alter table orders
    2         add constraint PK_orders primary key(orderID),
    3              constraint FK_orders_personID foreign key(personID) references persons(personID) 
    

      - not null 约束
      强制列不接受null值,具体使用参考上述代码。
      - default 约束
      用于向列中插入默认值,default只能用于insert语句且不能与identity同时用,具体使用参考如下示例代码:
      1. 创建table时,直接在字段后面声明为 default

    1  create table Certifications(
    2      certID int not null primary key identity(1001,1),
    3      certName varchar(20) not null,
    4      certPassword varchar(20) default('12345678'),
    5      certTime varchar(30) default(getdate())
    6  );
    

      2. 注意,default约束不存在此种方法;
      3. 在table已创建后,为表添加默认约束

    1  alter table Certifications
    2      add constraint DF_Certifications_certPassword default '123456' for certPassword,
    3           constraint DF_Certifications_certTime default getdate() for certTime
    

      - check 约束
      用于限制列中的数据的范围,为多个列定义check约束采用第2、3种方法,具体方法如下:
      1. 创建table时,直接在字段后面添加:check(条件表达式)
      2. 创建table时,全部字段声明之后添加:constraint CHK_表名_字段名 check(条件表达式)
      3. 在table已创建后,为表添加check约束

    1   alter table 表名
    2       add constraint CHK_表名_字段名 check(条件表达式)
    

      - unique 唯一约束
     用于唯一标识表中的每条记录,通过唯一性性索引强制实体完整性,unique算是对primary key的补充,但是每个表可有多个unique约束且允许null值,创建unique约束的3种方法可参考上述方法:

    1  [1].unique
    2  [2].constraint UNQ_表名_字段名 unique(字段名)
    3  [3].alter table 表名
    4        add constraint UNQ_表名_字段名 unique(字段名)
    

      总结
      · 获取table的约束信息:exec sp_helpconstraint 表名
      · 撤销上述各种约束:alter table 表名 drop constraint 主/外键约束名 
      · 关闭/开启约束检测:nocheck/check constraint 约束名/all
      · 若表中已存在数据,在添加约束之前先使用with nocheck可以禁止对已有数据的检测。
      · 级联更新/删除:on update/delete cascade

    4. 某些高级检索技术

    where ... union ... group by ... having ... order by ... (limit) ... 

      a. 分组技术
      SQL Server 之几种分组技术介绍
      · group by
      在select中作为分组条件的列名一定要是在group by子句中使用的列列表中。

           select 作为分组条件的列名 聚合统计函数(被统计字段列) from 表名 group by 用于分组的列列表(A,B,C) 

      优先级:C > B > A
      · having
      having 与 where 语句类似,where 是在分类之前过滤,having 是在分类之后过滤,且having条件中经常包含聚合函数。
       group by … having … order by …  
      · rollup ~ cube
      rollup显示所选列的值得某一层次结构的聚合,cube显示所选列的值得所有组合的聚合,且更加细化;两者均需要和group by一起用。
      具体区别详解见:rollup ~ cuberollup ~ cube - 2
      b. 联合查询
      · union
      并集,用于整合2个以上的结果集,默认去重,union all不去重。但是有列类型和列数量是否对应一致的限制。 
      c. 连接查询
       连接是关系型数据库模型的主要特点,通过连接运算符来实现多个表的联表查询,灵活,语句格式:

       select 表名.列名[列列表...]
          from table_A 连接运算符 table_B [on 联表查询的匹配条件]
    

      注意,在连接表查询中学会使用别名。以下可参考 连接查询简例连接关系示意图
      · 内连接
      inner join,也即普通连接,包括等值连接、自然连接、不等连接。返回的查询结果集合仅仅是select的列列表以及符合查询条件和连接条件的行。其中,自然连接会去掉重复的属性列。  
      · 外连接
      outer join,包括左外连接、右外连接和完全连接。返回的查询结果集合不仅包含select的列列表以及符合查询条件和连接条件的行,还包括左表(左连接)、右表(右连接)或两个连接表(完全连接)中的所有数据行。

          A left join B == B right join A;   
      · 交叉连接
      cross join,连接表中所有数据的笛卡尔积,结果集的数据行数 = 第一个表中符合查询条件的数据行数 * 第二个表中符合查询条件的数据行数。cross join后加条件只能用where,不能用on。  
      · 自连接
      连接关键字的两边都是同一个表,将自身表的一个镜像当作另一个表来对待。自连接可以将需要两次查询的语句综合成一条语句一次执行成功。参考示例:自连接查询,也可参见大话数据库中关于自连接的例子。
      d. 子查询
     即内部查询(inner query),子查询就是位于select、update或delete语句中内部的查询。子查询在主查询执行之前执行一次,主查询使用子查询的结果。参考示例:子查询各种查询总结. 

      select select_list from table1
          where expression operator(select select_list from table2);
    

      · 单行子查询
      返回零行或一行。单行比较运算符:= ,>, >= ,< , <= ,<>。
      · 多行子查询 
      返回一行或多行。多行比较运算符:IN/NOT IN,ANY/ALL,EXISTS。
      ANY:匹配子查询得到的结果集中的任意一条数据;
      ALL:匹配子查询得到的结果集中的全部数据;
      EXISTS:返回bool值,只检查行的存在性,而IN检查实际值的存在性(一般情况EXISTS性能高于IN)。
      f. 索引
      此处将索引拿出来作为单独的一章进行总结学习,如下。

    5. 索引

    索引是对数据库表中一列或多列的值进行排序的一种结构,快速有效查找与键值关联的行,加快对表中记录的查找过滤或排序。索引采用 B树 结构。
    优点:
     (1)快速检索读取数据;
     (2)保证数据记录的唯一性;
     (3)实现表与表之间的参照完整性,加速表和表之间的连接;
     (4)在使用order by、group by子句进行数据检索时,利用索引可以减少排序分组时间;
     (5)通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能;
      缺点:
     (1)增加了数据库的存储空间;
     (2)创建索引和维护索引要耗费时间;
     (3)插入和修改数据时要花费较多时间更新索引;
     (4)修改性能和检索性能是相互矛盾的;
    分类:根据索引的顺序与数据表的物理顺序是否相同
     · 聚集索引
      索引的顺序与数据表的物理顺序相同,提高多行检索速度。一个表只能包含一个聚集索引。聚集索引的叶级是数据页,数据值的顺序总是按照升序排列。在创建任何非聚集索引之前先创建聚集索引。聚集索引的平均大小约为数据表的5%。
     · 非聚集索引
      索引的顺序与数据表的物理顺序不同,单行检索快。一个表最多249个非聚集索引。非聚集索引的叶级是索引页。索引页中的行标识符(或聚集键)指向数据页中的记录(或表的聚集索引,再通过聚集索引检索数据),行标识符由文件ID、页号和行ID组成,并且是唯一的。数据堆通过使用索引分配图(IAM)页来维护。
    特征:
     · 唯一性索引
     保证索引列中的全部数据是唯一的。只能在可以保证实体完整性的列上创建唯一性索引。
     · 复合索引
      一个索引创建在2个或多个列上。不能跨表建立复合列。注意列的排列顺序。复合索引可以提高查询性能,减少在一个表中所创建的索引数量。复合索引键中最多可以组合16列。
    创建索引:
     · 直接创建:索引创建向导或create index
     基本方法,灵活易扩展、优化索引。语法:

     create [unique][clustered|nonclustered] index 索引名  
       on {表|视图}(列 [asc|desc][,...n])
    

     · 间接创建:利用约束间接创建
     主键约束 - -> 唯一性聚集索引,唯一性约束 - ->唯一性非聚集索引。
     利用约束创建索引的优先级高于create index语句创建的索引。
    维护索引:
     · 查看索引
      [1]. exec sp_helpindex 表名
      [2]. select * from sysindexes [where name = "索引名"]
     · 修改索引
      [1]. 修改索引名:exec sp_rename ‘表名.索引名’, ‘新索引名’
      [2]. 重新生成索引:alter index 索引名/all on 表名
               rebuild;
         重新生成索引会先删除再重建索引。可以不用rebuild,直接用set设置索引选项。
     · 删除索引
       drop index 索引名 on 表名
       最好在删除之前,利用exists判断索引名的存在性;
     · 统计信息
     统计信息是存储在Sql Server中列数据的样本,Sql Server维护某一索引关键值的分布统计信息。
      [1]. exec sp_updatestats
      [2]. update statistics 表名 [索引名]
     ·dbcc showcontig:显示表的数据和索引的碎片信息。
     ·dbcc dbreindex(表名, 索引名):重建表的一个或多个索引。
     ·showplan_all 和 statistics io:分析索引,查询性能,更好的调整查询和索引。
       set showplan_all on/off
       set statistics io on/off 
    参考:
    [1]. 数据库索引的实现原理索引由浅入深
    [2]. 表和索引数据结构体系结构SQL索引学习-索引结构

    6. 视图

    视图是一种逻辑对象,是由基本表导出的虚拟表,不占用任何数据空间、不存储数据,仅封装预定义的查询语句,其内容由查询定义。视图是查看数据库表数据的一种方式,提供了存储预定义的查询语句作为数据库中的对象以备后用的作用,但视图不能索引。被查询的表称为基表,对视图的数据操作(增、删、改),系统根据视图的定义去操作与视图相关联的基本表。
    优点:
     (1)保证数据的逻辑独立性,数据保密;
     (2)隐藏复杂的SQL,SQL语句复用,数据简化操作逻辑,易于检索数据;
     (3)重新格式化检索出来的数据;
    创建视图: 
      create view 视图名 [with schemabinding/encryption] as 查询语句  
     (1)对于视图和基表必须紧密结合的情况,利用with schemabinding将视图定义为索引视图;
     (2)对创建视图的SQL语句加密,利用with encryption;
    维护视图:
     · 查看视图
      [1]. exec sp_helptext 视图名
      [2]. select definition from sys.sql_modules
          where object_id=object_id(‘视图名’)
     · 修改视图
        alter view 视图名 as 查询语句  
       重命名视图: exec sp_rename 旧视图名, 新视图名  
     · 删除视图
        drop view 视图名1 [, 视图名2, …]   

    7. 游标

    游标是一种只和一组数据中某一个记录进行交互的方法,是对(select)结果集的一种扩展。将面向集合的数据库管理系统和面向行的程序设计结合,主要用于交互式应用。
    Transact-SQL 游标
    存储过程、触发器和 T-SQL脚本,服务器端(后台)游标,仅支持单行数据提取,分为;

    • 静态游标:快照游标,在 tempdb 中创建游标;需要临时表保存结果集;
    • 动态游标:打开速度快、不需生成临时内部工作表,但连接速度慢,不支持绝对提取;
    • 只进游标:默认值,顺序提取、不支持滚动,最节省资源;
    • 键集驱动游标:键集唯一标识行,键集是打开游标时在 tempdb 中生成并内置在表 keyset 中;需要临时表保存键集;

    注:客户端(前台)游标,仅支持静态游标,默认在客户机上缓存整个结果集、需维护游标位置信息。服务器(后台)游标性能更佳、更精确的定位更新,允许多个基于游标的活动语句。
    使用游标的典型过程,分为:

    • 声明游标:declare 游标名称 + SQL检索语句

      declare 游标名称 cursor

       [local|global] [forward_only|scroll] [static|dynamic] ..
      

      for SQL(select)检索语句

    • 打开游标: open [golbal] 游标名称 | 游标变量  ,游标打开的同时检索数据并存储。

    • 提取数据

      fetch [next|prior|first|last | absolute|relative]

          from [global] 游标名称 | 游标变量
          into 结果变量[..]
      

        定位修改和删除数据:前提是用  for update of 列列表; 设置可编辑的列。

      update 表名 set 列名=新值[..] where current of 游标名
      delete from 表名 where current of 游标名
    
    • 关闭游标: close [golbal] 游标名称 | 游标变量  
    • 删除游标: deallocate [golbal] 游标名称 | 游标变量  

    注:游标变量指引用了游标的变量。其他操作:

      select @@CURSOR_ROWS;    // 游标中的数据行数
      select @@FETCH_STATUS;   // fetch执行状态(-2,-1,0)  
    

    8. 存储过程

    存储过程(Stored Procedure),数据库架构作用域内的重要对象,是存储在大型数据库系统中一段为了完成特定功能的可复用的代码块,是SQL语句和可选控制流语句的 预编译 集合,经过第一次编译后再次调用不必重新编译。存储过程主要用于返回数据。
    .vs 函数

    • 函数不能修改数据库表数据,存储过程可以;
    • 存储过程必须 execute 执行,函数调用更灵活;

    优点:简单、安全、高性能

    • 允许标准组件式编程,可移植性、可复用;
    • 简单易用,预编译、执行速度快、效率高;
    • 改善安全机制、保证数据的安全;
    • 节约网络流量、降低网络负载;

    分类

    • 系统存储过程:存储在 master 数据库中,以 "sp_"为前缀,用于从系统表中获取信息。
    • 用户自定义存储过程:T-SQL存储过程、CLR存储过程、临时存储过程。不能将CLR存储过程创建为临时存储过程。

    创建

    1  create proc|procedure 存储过程名
    2        (@parameter 参数数据类型 [,...])
    3  as
    4  begin
    5    < SQL语句代码块 
    6    return >
    7  end
    

    返回值

    • 利用 return 返回一个值;
    • 利用 output 定义返回参数来返回多个值; 

    维护
    · 查看:
      [1]. exec sp_helptext 存储过程名;
      [2]. sys.sql_modules目录视图;
      [3]. object_definition元数据函数; 
    · 加密:with encryption
    · 修改:直接将 create 替换为 alter;
    · 删除:drop proc 存储过程名;
    执行

    • 语法分析阶段
    • 解析阶段
    • 编译阶段:分析存储过程、生成存储过程执行计划。执行计划存储在过程高速缓存区(专门用于存储已经编译过的查询规划的缓冲区)。
      • 重新编译:[1].sp_recompile;[2]. 执行时在 exec 语句中选项 with recompile;
    • 执行阶段

    9. 触发器

    Trigger,触发器是特殊的存储过程,由 事件 自动触发,不能显式调用,主要用于维护和加强数据的(一致/引用)完整性约束和业务规则([1]. 约束;[2]. 触发器)。触发器可以级联嵌套。常用的 inserted 和 deleted 表是针对当前触发器的局部表,在高速缓存中存储新插入或删除的行数据的副本。可以理解为委托事件。通常触发器只与单个表关联。 
    约束 vs 触发器 vs 存储过程
    约束主要被用于强制数据的完整性,能提供比触发器更好的性能;触发器常用于验证业务规则或是复杂的数据验证。触发器可以实现约束的一切功能,但优先通过约束实现。

    • 错误信息管理:约束只能使用标准化的系统错误信息,触发器可以自定义错误信息;
    • 性能差异;
    • 管理维护的工作量; 

    参考:约束与数据库对象规则、默认值+数据库设计中约束、触发器和存储过程
    事件 - -> 触发器 - -> 存储过程
    ·  DML 触发器:响应数据操作语言事件,将触发器和触发它的语句作为可在触发器内回滚的单个事务;常用、性能开销小,可以实现相关表数据的级联更改、评估数据修改前后表的状态。
    ζ  AFTER 触发器:在 IUD 操作、INSTEAD OF 触发器和约束处理之后被激发;推荐且只能在表上指定; 
    ζ  INSTEAD OF 触发器:在约束处理之前被激发(执行预处理补充约束操作),指定执行DML触发器以代替通常的触发动作,优先级高于触发语句的操作;
    注:每个表或试图针对每个 DML 触发操作 IUD,有且只能有一个相应的 INSTEAD OF 触发器,可以有多个相应的 AFTER 触发器。
    ζ  CLR 触发器:执行在托管代码中的方法;
    ·  DDL 触发器:响应数据定义语言事件,用于在数据库中执行管理任务;
    ·  登录触发器:响应 logon 事件,用于审核和控制服务器会话;
    优点

    • 预编译、已优化,执行效率高;
    • 已封装,安全、易维护;
    • 可重复使用;

    缺点

    • 占用服务器资源多;
    • 后置触发(事后诸葛亮);

    创建与维护
    ·  DDL

      create/alter trigger 触发器名称
            on 作用域(DDL:数据库名database/服务器名all server)
            FOR create|alter|drop|grant 等DDL触发器
       as SQL处理语句
    

      删除: drop trigger 触发器名;  修改: create - -> alter  
    ·  DML

      create trigger 触发器名称
           on 作用域(DML:表名/视图名)
           [FOR|AFTER|INSTEAD OF] {[insert [,] update [,] delete]}
       as SQL处理语句
    

      嵌套:级联触发,递归触发
       ·  直接递归:更新T,触发Trig,Trig更新T,再次触发Trig;
       ·  间接递归:更新T1,触发Trig1,Trig1更新T2,T2触发Trig2,Trig2更新T1;
      参考:如何控制触发器递归

    10. 事务 - 锁

     具体参考 事务和锁 - sqh

    11. 全文索引

    全文索引是一种特殊类型的基于标记的功能性索引,用于提高在大数据文本中检索指定关键字的速度,由 全文索引引擎服务 (SQL Server FullText Search)创建和维护。全文索引创建和维护的过程称为填充:完全填充、基于时间戳的增量式填充、基于更改追踪的填充。全文索引只能在数据表上创建。
    全文索引 .vs. 普通索引

    • 普通索引采用B-Tree结构,全文索引基于标记生成倒排、堆积且压缩的索引;
    • 普通索引适于字符/字段/短文本查询,全文索引是基于关键字查询的索引,针对语言词语/长文本搜索;
    • 每个表允许有若干个普通索引,全文索引只能有一个;
    • 普通索引自动更新、实时性强,全文索引需要定期维护;

    全文目录 - 全文索引
    存储全文索引,是创建全文索引的前提。全文目录是虚拟对象,是表示全文索引的逻辑概念。全文目录和全文索引都是为全文搜索查询服务。

    • rebuild:重新生成全文目录;
    • reorganize:优化全文目录;

      create fulltext catalog 全文目录名 create fulltext index on 全文索引基于的表名[索引包含的列列表]

    原理:两步走
    对文本进行分词,并为每一个出现的单词记录一个索引项以保存出现过该单词的所有记录的信息。全文索引引擎对加入到全文索引的列中的内容按字/词建立索引条目,即先定义一个词库,然后在文章中查找每个词条(term)出现的频率和位置,把这些频率位置信息按词库顺序归纳,完成对文件建立一个以词库为目录的索引。
    · 创建基于关键字查询的索引
        - 如何对文本进行分词:二元分词法、最大匹配法和统计方法
        - 建立索引的数据结构:采用倒排索引的结构
    · 在索引中搜索定位
       全文谓词:在 select 的 where/having 子句中指定
        - contains:精确。简单词、派生词、加权词、前缀词、邻近词;
        - freetext:模糊。文本拆分,分别搜索;
       行集函数:在 from 子句中指定
        - containstable:
        - freetexttable:

    参考:全文索引原理介绍全文索引原理及范例

    那些是对象,那些不是?

    你可能注意到了一些奇怪的事情。尽管表的一些属性(如主键)本身就是对象,但列、统计或索引并非对象。让我们弄清楚这一点,因为它不是完全直观的体现在sys.objects,您可以找到关于所有公共数据库组件的基本标准信息,如表、视图、同义词、外键、检查约束、键约束、默认约束、服务队列、触发器和过程。我列出的所有这些组件都有其他属性,这些属性必须通过继承相关基本属性的视图可见,但也包括与对象相关的数据列。最好使用这些特殊的视图,因为它们有您需要的所有信息,系统只过滤您感兴趣的对象类型,比如表。各种对象(如约束和触发器)在sys.objects中都有parent_ID,非零的对象表,显示它们是子对象。

    下面的查询向您展示了一种查看这些子对象并将其与父母关联的简单方法。

    --查询索引父对象(表名)和索引名称
    SELECT parent.name AS Parents_name, 
           child.name AS Childs_Name, 
           replace(lower(parent.type_desc),'_',' ') AS Parents_type, 
           replace(lower(child.type_desc),'_',' ') AS Childs_type
    FROM sys.objects child
      INNER JOIN sys.objects parent
        ON parent.object_ID=child.parent_object_id
    WHERE child.parent_object_id<>0
    ORDER BY parents_name;
    

     

    金沙85155登入 7.

    你会发现索引不是对象。在第一个查询中,返回的object_ID是定义索引的表的ID。

    这里的问题是关系是复杂的。约束可以包含几个列,也可以由索引强制。索引可以包含几个列,但是顺序很重要。统计数据还可以包含几个列,也可以与索引相关联。这意sys.indexes, sys.stats and sys.columns不从sys.objects继承。参数和类型也是如此。

    在互联网笔试中,常遇到数据库的问题,遂来简单总结,注意,以 Sql Server 数据库为例。

    如何查询XML索引?

    XML索引被视为索引的扩展。我发现查看其细节的最好方法是为它们构建一个CREATE语句。

    SELECT 'CREATE' + case when secondary_type is null then ' PRIMARY' else '' end
     + ' XML INDEX '+coalesce(xi.name,'')+ '  
        ON ' --what table and column is this XML index on?
     + object_schema_name(ic.Object_ID)+'.'+object_name(ic.Object_ID)
     +' ('+col_name(Ic.Object_Id, Ic.Column_Id)+' )  
        '+ coalesce('USING XML INDEX [' + Using.Name + '] FOR ' + Secondary_Type_DeSc
         COLLATE database_default,'')    
        +'  
    '+      replace('WITH ( ' + 
       stuff(
      CASE WHEN xi.Is_Padded <> 0 THEN ', PAD_INDEX  = ON ' ELSE '' END 
      + CASE 
         WHEN xi.Fill_Factor NOT IN (0, 100) 
            THEN ', FILLFACTOR  =' + convert(VARCHAR(3), xi.Fill_Factor) + ''
            ELSE '' END 
      + CASE WHEN xi.Ignore_dUp_Key <> 0 THEN ', IGNORE_DUP_KEY = ON' ELSE '' END 
      + CASE WHEN xi.Allow_Row_Locks = 0 THEN ', ALLOW_ROW_LOCKS  = OFF' ELSE '' END 
      + CASE WHEN xi.Allow_Page_Locks = 0 THEN ', ALLOW_PAGE_LOCKS  = OFF' ELSE ' ' END
       , 1, 1, '')
     + ')', 'WITH ( )', '') --create the list of xml index options
    +  coalesce('/* '+convert(varchar(8000),Value)+ '*/','')--and any comment
        AS BuildScript
    FROM sys.xml_Indexes xi
          inner join sys.index_columns ic 
       ON ic.Index_Id = xi.Index_Id
       AND ic.Object_Id = xi.Object_Id   
      LEFT OUTER JOIN sys.Indexes [USING]
       ON [USING].Index_Id = xi.UsIng_xml_Index_Id
       AND [USING].Object_Id = xi.Object_Id
      LEFT OUTER JOIN sys.Extended_Properties ep
       ON ic.Object_Id = ep.Major_Id AND ic.Index_Id = Minor_Id AND Class = 7
    WHERE object_schema_name(ic.Object_ID) <>'sys' AND ic.index_id>0;
    

    上面的查询结果将显示所有基本的XML索引细节作为构建脚本。

    金沙85155登入 8

    数据库

    数据库系统,Database System,由数据库和数据库管理系统组成。
    数据库,DataBase ,是计算机应用系统中的一种专门管理数据资源的系统,按照数据结构来组织、存储和管理数据的仓库。数据表是最基本的数据库对象,是存储数据的逻辑单元。

    数据库管理系统,DataBase Management System,DBMS,管理数据库,负责数据的存储、安全、一致性、并发、恢复和访问。

    数据模型,通常由数据结构、数据操作和完整性约束三部分组成。

    每个表中有多少个索引,并展示他们的名字

    前面的表并不特别有用,因为无法一眼看到每个表有多少索引,以及它们是什么。下面这个语句可以实现:

    SELECT  convert(CHAR(20),object_schema_name(t.object_ID)+'.'
        +object_name(t.object_ID)) AS 'The_Table',
    sum(CASE WHEN i.object_ID IS NULL THEN 0 ELSE 1 END) AS The_Count,
    coalesce(stuff(( 
         SELECT ', '+i2.name
           FROM sys.indexes i2
           WHERE t.object_ID = i2.object_ID
           ORDER BY i2.name
         FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,''),'') AS Index_List
      FROM sys.tables AS t
      LEFT OUTER JOIN sys.indexes i
        ON t.object_id=i.object_id
          AND is_hypothetical = 0 AND i.index_id > 0 
    GROUP BY t.Object_ID;
    

     

    我在老的测试数据库上执行这个测试,对象名称比较短。

    The_Table            The_Count   Index_List
    -------------------- ----------- --------------------------------------------------
    dbo.publishers       1           UPKCL_pubind
    dbo.titles           2           titleind, UPKCL_titleidind
    dbo.titleauthor      3           auidind, titleidind, UPKCL_taind
    dbo.stores           1           UPK_storeid
    dbo.sales            2           titleidind, UPKCL_sales
    dbo.roysched         1           titleidind
    dbo.discounts        0           
    dbo.jobs             1           PK__jobs__6E32B6A51A14E395
    dbo.pub_info         1           UPKCL_pubinfo
    dbo.employee         2           employee_ind, PK_emp_id
    dbo.authors          2           aunmind, UPKCL_auidind
    
    (11 row(s) affected)
    

    SQL语言

    结构化查询语言,Structured Query Language,SQL是一种数据库查询和程序设计语言,用于存储数据以及查询、更新、管理关系数据库系统,高级的非过程化编程语言。Transact-SQL是微软对SQL的扩展,具有SQL的主要特点,同时增加了变量、运算符、函数、流程控制和注释等语言元素。
    SQL语言分四类:数据定义语言[DDL]、数据查询语言[DQL]、数据操纵语言[DML]、数据控制语言[DCL]。

    – [1].DDL(Data Defination Language)
          style="color: blue;">创建和管理数据库中的对象,定义SQL模式以及数据库、表、视图和索引的创建和撤销。不需COMMIT。
         创建CREAT,  修改ALTER,  删除DROP,  删除TRUNCATE
         TRUNCATE,  RENAME

    – [2].DQL(Data Query Language)
         基本结构: SELECT子句、FROM 子句、WHERE子句组成查询块。
         SELECT<字段名表>,  FROM<表或视图名>,   WHERE<查询条件>

    – [3].DML(Data Manipulation Language)
          style="color: blue;">直接操作数据表中的数据,根据需要检索、插入、删除数据以及更新数据库.
         操作的单位是记录。DML需要COMMIT显式提交。
         插入INSERT,   删除DELETE,  更新UPDATE

    – [4].DCL(Data Control Language)
         用于授予或取消对用户对数据库对象的访问权限,保证数据安全性。
         授权GRANT,  取消授权REVOKE,  显式限制权限集合DENY

    事务控制语言 - TCL (Transaction Control Language)
    提交COMMIT,回滚ROLLBACK,设置保存点SAVEPOINT
    SQL>COMMIT:显式提交
    SQL>ROLLBACK:回滚命令使数据库状态回到上次最后提交的状态
    SQL>SET AUTOCOMMIT ON:自动提交
    利用SQL命令间接完成:隐式提交。

    1. 数据类型

     1. 字符数据类型
      a. 字符串:char、varchar、text;
      b. Unicode字符串:nchar、nvarchar、ntext,用N标识,unicode是统一字符编码标准, 双字节对字符(英文,汉字)编码;
       使用Unicode数据类型,可以最大限度地消除字符转换的问题。
     2. 数字数据类型
      a. 整数型:tinyint(1)、smallint(2)、int(4)、bigint(8);
      b. Decimal和numeric:固定精度和小数位数,decimal(p,s)或numeric(p,s),0≤s≤p;
      c. 货币类型:smallmoney(4)、money(8);
      d. 近似数字:float、real(4);
      e. bit类型:0/1序列;
     3. 日期和时间数据类型
      time、date、smalldatetime、datetime、datetime2、datetimeoffset;
     4. 二进制数据类型
      binary、varbinary;
     5. 其他数据类型
      uniqueidentifier:16字节的十六进制数字组成,全局唯一,
      sql_variant:支持各种数据类型;
      还有xml、table等,此外还可以自定义数据类型。

    2.1 函数

    内置函数详细介绍参考:行集函数、聚合函数、排名函数、标量函数 或者数据库书籍。
    函数信息查询
       a. 工具栏“帮助”- -> “动态帮助”;
       b. 开始“文档教程”- -> “SQL server 教程”
    系统函数 ~ 自定义函数
    a. 系统函数
       允许用户在不直接访问系统表的情况下获取SQL系统表的信息。
    b. 自定义函数:User Defined Function
    优点

    • 模块化设计;
    • 执行速度快,缓存计划降低编译开销、无需重复解析和优化;
    • 减少网络流量;

    分类

    • 标量型函数:Scalar Function,只能返回标量值;
    • 内联表值型函数:Inline table-valued Function,参数化的视图,只能返回 TABLE 类型;
    • 多声明表值型函数:Multi-Statement Table-Valued Function,标量型函数和内联表值型函数的结合;

    创建

      create function 函数名(@参数名 参数类型, [..])
          returns 返回值类型
       as
       begin
          SQL语句;
          return 返回的对象;
       end
    

    注:begin…end 块中的语句不能有任何副作用。
    查询
    函数的定义、架构等。
    修改/删除
    alter/drop function 函数名

    2.2 关键字

      a. set ~ select
      select支持在一个操作内同时为多个变量赋值,但是为变量赋值和数据检索不能同时进行,参考 两者的区别
      b. cast() ~ convert(): 类型转换函数
      · cast(源值 as 目标类型);
      · convert(目标数据类型,源数据[, 格式化代号]),可以格式化日期和数值;
      c. delete ~ drop ~ truncate
      · delete:DML,删除数据表中的行(一行或所有行)/记录,自动隐式commit,无法回滚;
            delete from 表名 where 条件
      · drop:DDL,显式手动commit,可以回滚,删除数据库、数据表或删除数据表的字段;
            drop table 表名
      · Truncate:快速、无日志记录,删除数据表中的数据、不删除表,不可恢复;
            truncate table 表名
      从删除速度来说,drop> truncate > delete,其他区别详细参考 delete ~ drop ~ Truncate
      d. insert
      注意区别下面2个insert语句的区别,第一种Product格式,values中必须给出相应的值,其中日期系统默认1900-01-01;第二种格式,values中采用default约束。

       insert into Product(productName,productPrice,productStorage,productDate,productClass) 
                     values('电冰箱', null, 0, '', 1)
       insert into Product(productName,productClass) values('电冰箱',1)
    

      批量插入数据
      [1]. insert into 目标表表名或列视图 select 检索语句 from 源表名
      [2]. select 列列表 into 目标表表名 from 源表表名     
      e. waitfor
       定时、延时或阻止执行批处理、存储过程或事务。  

    3. 数据库表设计问题

      常用表操作格式 
      [a]. 创建数据库与表 
       create database/table 数据库名/表名 
      [b]. 查看表信息 
       exec sp_help 表名   
      [c]. 添加新列、修改列名与类型 
       alter table 表名 
        add 列名 列类型 
       exec sp_rename ‘表名.列名’, ‘新列名’ (注意必须加引号) 
       alter table 表名 
        alter column 列名 新的列数据类型     
    

      a. E-R模型图
     实体-联系(Entities-Relationships)模型,描述概念数据模型的方法之一,软件生命周期的设计阶段,提供实体、属性、联系的面向用户的表达方法,实体之间存在一对一、一对多、多对多的联系。
      b. 关系规范化 - 数据库完整性
      三大范式:
      · 第一范式 1NF:所有属性(值)是不可分割的原子值;
      · 第二范式 2NF:所有属性数据必须依赖主键;
      · 第三范式 3NF:数据库表中不能包含已在其他表中包含的非主键信息;
     关系型数据库三大完整性:
      · 实体完整性:主键约束 primary key,唯一且非空;
      · 参照完整性:引用完整性,外键约束 foreign key 等关联约束;
      · 用户自定义完整性:域完整性,字段类型等;
      c. 分区表
     按照数据水平方式分区,将数据分布于数据库的多个不同的文件组中:
      - 改善大型表以及具有各种访问模式的表的可伸缩性和可管理性;
      - 对于多CPU系统,支持并行方式对表操作;
     分区函数~分区方案:

      create partition function 函数名(分区条件) 
       as range left/right for values() 
      create partition scheme 方案名 
       as partition 函数名
    

     一个分区方案只能用一个分区函数,一个分区函数可以被多个分区方案共用。
      d. 文件组 
     在数据库中对文件进行分组的一种管理机制,一个文件不能是多个文件组的成员。文件组只能包含数据文件,事务日志文件不能是文件组的一部分。使用文件组可以隔离用户对文件的依赖,通过文件组间接管理文件,可以使得同一文件组内的文件分布在不同的硬盘中,能提高IO性能。
     具体地可参考 文件和文件组
      e. 标识符
     每一行数据必须都有一个唯一的可区分的属性作为标识符。
      · identity:本地(表内)唯一,使用方法 identity(初始种子值,增量);
         select @@identity:查看新插入行数据的标识符(的序号)  
         select $identity from 表名:引用(显示)表的唯一标识符列  
      · uniqueidentifier:全局唯一,应用rowguidcol属性作为标识符指示新列为guid列,默认定义使用newid或newsequentialid()函数生成全局唯一值;同理,使用$rowguid引用唯一标识符列。
         ID uniqueidentifier default newsequentialid() rowguidcol   

    索引中有哪些列,顺序又是什么 ?

    也可以汇总上面语句,每个索引汇总成一行,展示所有索引,具体代码如下:

    SELECT object_schema_name(t.object_ID)+'.'+t.name AS The_Table, --the name of the table
       coalesce(stuff (--get a list of indexes
         (SELECT ', '+i.name
         +' ( '
             +stuff (--get a list of columns
             (SELECT ', ' + col_name(Ic.Object_Id, Ic.Column_Id) 
             FROM  sys.Index_columns  ic
             WHERE ic.Object_ID=i.Object_ID
             AND ic.index_ID=i.index_ID
             ORDER BY index_column_ID ASC
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') +' )'
         FROM sys.indexes i 
         WHERE i.object_ID=t.object_ID
         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,''),'') AS Indexes
     FROM sys.tables t;
    

    效果如下:

    金沙85155登入 9

    背景

    在第一篇中我介绍了如何访问元数据,元数据为什么在数据库里面,以及如何使用元数据。介绍了如何查出各种数据库对象的在数据库里面的名字。第二篇,我选择了触发器的主题,因为它是一个能提供很好例子的数据库对象,并且在这个对象中能够提出问题和解决问题。

    本篇我将会介绍元数据中的索引,不仅仅是因为它们本身很重要,更重要的是它们是很好的元数据类型,比如列或者分布统计,这些不是元数据中的对象。

    索引对于任何关系数据库表都是必不可少的。然而,就像吐司上的黄油一样,过度使用它们可能会在数据库中产生问题。有时,可以对表进行过度索引或缺失索引,或者构建重复索引。有时问题是选择一个坏的填充因子,错误地设置ignore_dup_key选项,创建一个永远不会被使用(但必须被维护)的索引,丢失外键上的索引,或者将GUID作为主键的一部分。简而言之,任何频繁使用的数据库系统中的索引都需要定期维护和验证,而目录视图是完成这些工作的最直接的方式之一。

    检查重复的统计信息

    通过比较与每个统计信息相关联的列号列表,您可以快速查看同一列或一组列是否有多个统计信息。

    SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID) as tableName,
           count(*) as Similar, ColumnList as TheColumn, 
           max(name)+', '+min(name) as duplicates
    FROM 
       (SELECT Object_ID, name,   
         stuff (--get a list of columns
             (SELECT ', ' + col_name(sc.Object_Id, sc.Column_Id)
             FROM  sys.stats_columns  sc
             WHERE sc.Object_ID=s.Object_ID
             AND sc.stats_ID=s.stats_ID
             ORDER BY stats_column_ID ASC
             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS ColumnList
       FROM sys.stats s)f
    GROUP BY Object_ID,ColumnList 
    HAVING count(*) >1;
    

    结果如下:

    金沙85155登入 10

    展示了包含重复的统计对象,在本例中是sales.customer表在AccountNumber列上有两个类似的统计对象。

    计算表总的索引空间

    让我们看看每个表的总索引空间,以及表中的行数。

    SELECT 
      object_schema_name(i.Object_ID) + '.'+ object_name(i.Object_ID) AS Thetable,
      convert(DECIMAL(9,2),(sum(a.total_pages) * 8.00) / 1024.00)    AS 'Index_MB',
      max(row_count) AS 'Rows',
      count(*) AS Index_count
    FROM sys.indexes i
    INNER JOIN
      (SELECT object_ID,Index_ID, sum(rows) AS Row_count 
         FROM sys.partitions GROUP BY object_ID,Index_ID)f
      ON f.object_ID=i.object_ID AND f.index_ID=i.index_ID
    INNER JOIN sys.partitions p 
      ON i.object_id = p.object_id
        AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units a 
      ON p.partition_id = a.container_id
      WHERE objectproperty(i.object_id, 'IsUserTable') = 1
    GROUP BY i.object_id;
    

    金沙85155登入 11

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:探索SQL Server元数据(三):索引元数据金沙851

    关键词:

上一篇:没有了

下一篇:T-SQL:SQL语句处理顺序的坑(四)