mysql触发器实现oracle物化视图示例代码

oracle数据库支持物化视图--不是基于基表的虚表，而是根据表实际存在的实表，即物化视图的数据存储在非易失的存储设备上。

下面实验创建ON

COMMIT

的FAST刷新模式，在mysql中用触发器实现insert

,

update

,

delete

刷新 *** 作

1、基础表创建，Orders

表为基表，Order_mv为物化视图表

复制代码

代码如下:

mysql>

create

table

Orders(

->

order_id

int

not

null

auto_increment,

->

product_name

varchar(30)not

null,

->

price

decimal(10,0)

not

null

,

->

amount

smallint

not

null

,

->

primary

key

(order_id))

Query

OK,

0

rows

affected

mysql>

create

table

Order_mv(

->

product_name

varchar(30)

not

null,

->

price_sum

decimal(8.2)

not

null,

->

amount_sum

int

not

null,

->

price_avg

float

not

null,

->

order_cnt

int

not

null,

->

unique

index(product_name))

Query

OK,

0

rows

affected

2、insert触发器

复制代码

代码如下:

delimiter

$$

create

trigger

tgr_Orders_insert

after

insert

on

Orders

for

each

row

begin

set

@old_price_sum=0

set

@old_amount_sum=0

set

@old_price_avg=0

set

@old_orders_cnt=0

select

ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)

from

Order_mv

where

product_name=new.product_name

into

@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt

set

@new_price_sum=@old_price_sum+new.price

set

@new_amount_sum=@old_amount_sum+new.amount

set

@new_orders_cnt=@old_orders_cnt+1

set

@new_price_avg=@new_price_sum/@new_orders_cnt

replace

into

Order_mv

values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt)

end

$$

delimiter

3、update触发器

复制代码

代码如下:

delimiter

$$

create

trigger

tgr_Orders_update

before

update

on

Orders

for

each

row

begin

set

@old_price_sum=0

set

@old_amount_sum=0

set

@old_price_avg=0

set

@old_orders_cnt=0

set

@cur_price=0

set

@cur_amount=0

select

price,amount

from

Orders

where

order_id=new.order_id

into

@cur_price,@cur_amount

select

ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)

from

Order_mv

where

product_name=new.product_name

into

@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt

set

@new_price_sum=@old_price_sum-@cur_price+new.price

set

@new_amount_sum=@old_amount_sum-@cur_amount+new.amount

set

@new_orders_cnt=@old_orders_cnt

set

@new_price_avg=@new_price_sum/@new_orders_cnt

replace

into

Order_mv

values(new.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt)

end

$$

delimiter

4、delete触发器

复制代码

代码如下:

delimiter

$$

create

trigger

tgr_Orders_delete

after

delete

on

Orders

for

each

row

begin

set

@old_price_sum=0

set

@old_amount_sum=0

set

@old_price_avg=0

set

@old_orders_cnt=0

set

@cur_price=0

set

@cur_amount=0

select

price,amount

from

Orders

where

order_id=old.order_id

into

@cur_price,@cur_amount

select

ifnull(price_sum,0),ifnull(amount_sum,0),ifnull(price_avg,0),ifnull(order_cnt,0)

from

Order_mv

where

product_name=old.product_name

into

@old_price_sum,@old_amount_sum,@old_price_avg,@old_orders_cnt

set

@new_price_sum=@old_price_sum

-

old.price

set

@new_amount_sum=@old_amount_sum

-

old.amount

set

@new_orders_cnt=@old_orders_cnt

-

1

if

@new_orders_cnt>0

then

set

@new_price_avg=@new_price_sum/@new_orders_cnt

replace

into

Order_mv

values(old.product_name,@new_price_sum,@new_amount_sum,@new_price_avg,@new_orders_cnt)

else

delete

from

Order_mv

where

product_name=@old.name

end

if

end

$$

delimiter

5、这里delete触发器有一个bug，就是在一种产品的最后一个订单被删除的时候，Order_mv表的更新不能实现，不知道这算不算是mysql的一个bug。当然，如果这个也可以直接用sql语句生成数据，而导致的直接后果就是执行效率低。

复制代码

代码如下:

->

insert

into

Order_mv

->

select

product_name

,sum(price),sum(amount),avg(price),count(*)

from

Orders

->

group

by

product_name

子查询优化策略

对于不同类型的子查询，优化器会选择不同的策略。

1. 对于 IN、=ANY 子查询，优化器有如下策略选择：

semijoin

Materialization

exists

2. 对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：

Materialization

exists

3. 对于 derived 派生表，优化器有如下策略选择：

derived_merge，将派生表合并到外部查询中（5.7 引入 ）；

将派生表物化为内部临时表，再用于外部查询。

注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

in子查询、exists子查询、连接，效率的探讨

以下是SQL的帮助 （高级查询优化概念）

Microsoft® SQL Server™ 2000 使用内存中的排序和哈希联接技术执行排序、交集、联合、差分等 *** 作。SQL Server 利用这种类型的查询计划支持垂直表分区，有时称其为分列存储。

SQL Server 使用三种类型的联接 *** 作：

嵌套循环联接

合并联接

哈希联接

如果一个联接输入很小（比如不到 10 行），而另一个联接输入很大而且已在其联接列上创建索引，则索引嵌套循环是最快的联接 *** 作，因为它们需要最少的 I/O 和最少的比较。有关嵌套循环的更多信息，请参见了解嵌套循环联接。

如果两个联接输入并不小但已在二者联接列上排序（例如，如果它们是通过扫描已排序的索引获得的），则合并联接是最快的联接 *** 作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相似。然而，如果两个输入的大小相差很大，则哈希联接 *** 作通常快得多。有关更多信息，请参见了解合并联接。

哈希联接可以有效处理很大的、未排序的非索引输入。它们对复杂查询的中间结果很有用，因为：

中间结果未经索引（除非已经显式保存到磁盘上然后创建索引），而且生成时通常不为查询计划中的下一个 *** 作进行适当的排序。

查询优化器只估计中间结果的大小。由于估计的值在复杂查询中可能有很大的误差，因此如果中间结果比预期的大得多，则处理中间结果的算法不仅必须有效而且必须适度弱化。

哈希联接使得对非规范化的使用减少。非规范化一般通过减少联接 *** 作获得更好的性能，尽管这样做有冗余之险（如不一致的更新）。哈希联接则减少使用非规范化的需要。哈希联接使垂直分区（用单独的文件或索引代表单个表中的几组列）得以成为物理数据库设计的可行选项。有关更多信息，请参见了解哈希联接。

---