查询优化与事务处理

Posted on 2020-06-16 13:15  留不住的时间  阅读(212)  评论(0)  编辑  收藏  举报

一：索引

1、 什么是索引

a) 数据库中的索引是某个表中一列或多列值的集合，以及物理标识这些值的数据页的逻辑指针清单

b) 索引页是数据库中存储缩印的数据页。

c) 索引页存放检索数据行的关键字页及该行的地址指针，类似词典的目录

2、 索引分类

a) 唯一索引

  1. 不允许两行具有相同的索引值

b) 主键索引

  1. 是一种特殊的唯一索引
  2. 要求主键中的每个值是唯一的

c) 聚集索引

  1. 表中各行的物理顺序与键值的逻辑顺序相同
  2. 一个表只能包含一个聚集索引

d) 非聚集索引

  1. 建立在索引页上，在查询数据时可以从索引中找到记录存放的位置
  2. 表中各行数据存放的物理顺序与键值的逻辑顺序不相同
  3. 聚集索引比非聚集索引访问速度更快
  4. 一个表可以创建多个非聚集索引

e) 复合索引

  1. 将多个列组合而成的索引

f) 全文索引

  1. 一种特殊类型的基于标记的功能性索引，
  2. 由sql server 中全文引擎服务创建和维护
  3. 主要用于在大量文本中搜索字符串

3、 创建和使用索引

a) 打开xuayuan表——右键列名——索引/键——添加——类型：索引——选择列——设置索引名为IX_name——关闭

b) select * from xueyuan

with (index=IX_name)

where 学员姓名 like '孙%'

c) 使用索引可以加快数据检索速度

d) 建立索引的列的标准：

  1. 频繁搜索的列
  2. 经常用于查询选择的列
  3. 经常排序、分组的列
  4. 经常用于连接的列（主键/外键）

e) 不要用下面的列创建索引

  1. 仅包含几个不同值的列
  2. 表中仅包含几行

4、 提高查询速度技巧：

a) 查询时减少使用“*”返回全部列，不要返回不需要的列

b) 索引应该尽量小，在字节数小的列上建立索引

c) Where子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前

d) 避免在order by 子句中使用表达式

e) 根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理

二：视图

视图是保存在数据库中的select查询

使用视图原因：

处于安全考虑，用户不必看到整个数据库结构，而隐藏部分数据

符合用户日常业务逻辑，使其对数据更容易理解

1、 什么是视图

a) 视图是一种虚拟表，他的行和列来自查询中引用的表

b) 视图的作用

  1. 筛选表中的行
  2. 防止未经许可的用户访问敏感数据
  3. 将多个物理数据表抽象为一个逻辑数据表

c) 好处

  1. 对最终用户的好处
    1. 结果更容易理解
    2. 获得数据更容易
  2. 对开发人员的好处
    1. 限制数据检索更容易
    2. 维护应用程序更方便

2、 如何创建和使用视图

a) 右键视图——新建视图——选择表——添加

b) 使用视图的注意事项

  1. 每个视图中可以使用多个表
  2. 与查询相似，一个视图可以嵌套另一个视图，最好不超过三层
  3. 视图定义中的select语句不能包括下列内容
    1. Order by子句，除非在select语句的选择列表中也有一个top子句
    2. Into关键字
    3. 引用临时表或表变量

三：存储过程

1、 为什么需要存储过程

a) 使用存储过程来避免远程发送并执行sql代码带来的安全隐患什么是存储过程

b) 存储过程把对数据库操作的sql代码预先编译好并保存在服务端，

c) 减少了网络传输量，保证应用程序的运行性能，防止了未经授权的攻击者想截取sql代码的行为

2、 什么是存储过程

a) 存储过程是sql语句和控制语句的预编译集合，

b) 保存在数据库中，可由应用程序调用执行

c) 优点：

  1. 模块化程序设计
  2. 执行速度块，效率高
  3. 减少网络流量
  4. 具有良好的安全性

d) 存储过程分类：

  1. 系统存储过程
  2. 用户自定义的存储过程

3、 系统存储过程

a) 一组预编译的T-SQL语句

b) 系统存储过程：提供了管理数据库和更新表的机制，并充当从系统表中检索信息的快捷方式

  1. 系统存储过程：以“sp_”开头，并存放在resource数据库中
  2. 调用执行存储过程的语法：exec  存储过程名 [参数值]

c) 扩展存储过程：是sql server提供的各类系统存储过程中的一类

  1. 允许使用其他编程语言创建外部存储过程，为数据库用户提供实例到外部程序的接口
  2. 以“xp_”开头，以DLL形式单独存在
  3. 语法：exec  xp_cmdshell  dos命令  [no_output]

4、 用户自定义存储过程

a) 一个完整的存储过程包括以下三部分

  1. 输入参数和输出参数
  2. 在存储过程中执行的T-SQL语句
  3. 存储过程的返回值

四：触发器

1、 什么是触发器

a) 触发器是在对表进行插入、更新或删除操作时自动执行的存储过程

  1. 触发器是通过事件进行触而被执行的，存储过程可以通过存储过程名称而被直接调用
  2. 用于强制业务规则，可以定义比用check约束更为负责的约束

b) 触发器分类：

  1. Insert触发器：当向表中插入数据时触发
  2. Update触发器：当更新表中某列、多列时触发
  3. Delete触发器：当删除表中记录时触发

c) 触发器有两个特殊的表：Deleted表和insered表，存储在内存中，不允许用户直接修改

  1. 两个表是只读的，查看：select  *  from  deleted
  2. Deleted表：临时保存被删除或更新前的记录行
  3. Inserted表：临时保存被插入或被更新后的记录行
  4. 更新语句：类似于在删除之后执行插入：首先旧行被复制到deleted表中，然后新行被复制到触发器表和inserted表中

d) 触发器作用：实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性

  1. 强化约束
  2. 跟踪变化
  3. 级联运行

2、 如何创建触发器

五：事务

1、 为什么使用事务

a) 通过支持事务机制保证数据的一致性

2、 什么是事务

3、 如何执行事务

六：锁

1、 认识锁

2、 认识死锁