数据库 SQL优化和触发器

• 常见的SQL优化

1. SQL语句嵌套一般不超过三层，SQL语句不能写得过长，过于冗余且容易出错。
2. 临时表：使用临时表加快查询和索引的效率。
3. 巧妙使用OR或者AND：
   1. 数量相差过大的情况下则可以分开运行语句。
   2. 使用or查询时可以将其改为两个union的方式以加快查询速度。

      SELECT 
          id,`name`,phone_no 
      FROM
      	md5blacklists 
      WHERE
      	cert_id = "aaaaa" 
      UNION
      SELECT
      	id,`name`,phone_no
      FROM
      	md5blacklists 
      WHERE
      	phone_no = "ccccccc";

   3. 索引中无等同于or的情况，多列设置成一个索引的情况下相当于and；

4. 只在必要情况下使用begin tran：（因为事务会整体性提交，导致提交时耗时过长）
   1. begin tran 标记事务的开始
   2. commit tran提交事务  
   3. 优点是保证了数据的一致性，缺点是容易出错,如果出错则回滚到begin tran之前的部分
5. 部分查询上加上nolock，使用插删改的不能加nolock，查询的表属于频繁发生分裂的慎用，使用临时表一样可以保存“数据前影”

   //错误写法，NOLOCK这个时候不是关键字
    SELECT * FROM TEST NOLOCK
    
    //SQL Server2008以后不推荐写法
    SELECT * FROM TEST (NOLOCK);
    
    //正确的标准写法
   SELECT * FROM TEST WITH(NOLOCK);

6. 合理使用like模糊查询：like‘%exmaple%’
7. SQLserver表的三种连接方式：merge join，nested loop join，hash join 连接时用on，比如：on a.id = b.id
   1. 连接方式为mysql的引擎设置

• 触发器的常见写法：

//常见语法通用模板

create  trigger trigger_name

on {table_name| view_name}

for {after| instead of} {insert, update, delete}//在进行本行操作时触发触发器进行

    as

    {

        //sql语句部分

        declare @student_idchar(10)

        select @student_id=s.student_id from students

        if (conditional code)//begin end的触发条件

        begin

        //错误抛出条件，并进行回滚

        raiserror('错误',16,8)

        rollbacktran


        end

    }


//--简单例子

//--在student上创建<strong>INSERT触发器</strong>stu_insert，要求在student表中插入记录时(要求每次只能插入一条记录)，这个触发器都将更新class表中的class_nun列。并测试触发器stu_insert。

​

create trigger stu_insert

on student

for insert

as

update class set class_num=class_num + 1 

where class_id=(select class_id from inserted)

• 作用：

1、过多的触发器使得数据逻辑变得复杂

2、数据操作比较隐含，不易进行调整修改

3、触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OO思想。

• DDL 触发器使用场合：
  • 要防止对数据库架构进行某些更改。
  • 希望数据库中发生某种情况以响应数据库架构中的更改。
  • 要记录数据库架构中的更改或事件。
• DML触发器：
  •  After触发器
    • After触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。
      • insert触发器
      • update触发器
      • delete触发器
  • Instead of 触发器 （并不执行其操作，只执行触发器）
    •  Instead of 触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。