sql优化，建表用触发器更新表

淡淡的烟草味﹌ 2022-01-05 02:17 177阅读 0赞

> 项目中用到一张表,然后sql是一条需要关联两张表的查询的，所以比较慢

`SELECT c.name juwei,count(1) as value FROM j_sq_rjbxx a,j_dict_xzqh_china b,j_dict_xzqh c WHERE concat( substr( a.HJDXZQHDM, 1, 2 ),'0000' ) = b.dm AND a.syrklbdm = '02' and b.mc='江苏省' and c.code=a.iszzrs group by a.iszzrs order by value desc`![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70]  
然后查了一下数据为12w,并且**主表不会经常改变**，所以就想到额外使用一张表来存储需要查询的数据，  
![主表][20190622190428130.png]  
于是乎，就新建一张表来存储juwei和vlaue，建表很简单，主要的是要定义两个触发器，用来出发主表的更新与删除操作，用来跟新副表的value值，

## 触发器 ##

--触发器，更新 身份居委表的数据
    create trigger trigger_j_sq_rjbxx after insert
    on j_sq_rjbxx for each row
    begin
    	set @juweicode = new.iszzrs;
    	set @shenfencode = new.HJDXZQHDM;
    	if(new.HJDXZQHDM is not null and new.syrklbdm='02' and new.iszzrs is not null)
    		then
    			set @juwei = (select name from j_dict_xzqh where code=@juweicode);
    			set @shenfen = (select mc from j_dict_xzqh_china where dm = concat(substr( @shenfencode, 1, 2 ),'0000'));
    			update j_dict_committee_city set j_dict_committee_city.value = j_dict_committee_city.value + 1 where juwei=@juwei and mc=@shenfen;
    	end if;
    end;
    --还有一个 删除 触发器
    create trigger trigger_j_sq_rjbxx_delete after DELETE
    on j_sq_rjbxx for each row
    begin
    	set @juweicode = old.iszzrs;
    	set @shenfencode = old.HJDXZQHDM;
    	if(@shenfencode is not null and old.syrklbdm='02' and @juweicode is not null)
    		then
    			set @juwei = (select name from j_dict_xzqh where code=@juweicode);
    			set @shenfen = (select mc from j_dict_xzqh_china where dm = concat(substr( @shenfencode, 1, 2 ),'0000'));
    			update j_dict_committee_city set j_dict_committee_city.value = j_dict_committee_city.value - 1 where juwei=@juwei and mc=@shenfen;
    	end if;
    end;

解释一下sql，创建一个触发器，insert的时候触发，new是insert的时候的插入的新行，这边我是需要juwei，和shenfencode两个字段，所以我定义出来，然后这两个字段根据两个关联表查到实际所对应的实际含义（cdoe）字段，然后判断再哪个行数据上进行自增，同样的删除触发器就是实现自减。

## 检验 ##

这是表新建的查询表原先的数值，243个，  
![原本数量][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70 1]

insert into j_sq_rjbxx (rid,syrklbdm,iszzrs,hjdxzqhdm) values ('121212','02','04014003','320000')

我们向主表插入一个字段，可以看到数值加了1，说明触发器是没问题的。  
![插入后的数值][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70 2]

## 最后 ##

执行一下我们所需要的查询，显然速度已经很可以了，  
![在这里插入图片描述][20190624140335679.jpg]  
这只是12w的数据量，如果这是几百万的话，显然是非常有必要的，并且再建立了查询表之后还需要给查询表加索引之后就能更快的拿到我们需要的数据了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70]: /images/20211230/683ce605a9574d04a60a8876bf37c90f.png
[20190622190428130.png]: /images/20211230/0da3205c116d4bcd8dadae640041a9a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70 1]: /images/20211230/5f89df938ed048ef90effff7a60ec7a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ppYW5qdW5fZmVp_size_16_color_FFFFFF_t_70 2]: /images/20211230/17023c8cf0dc42868c1c792997f797eb.png
[20190624140335679.jpg]: /images/20211230/63d4fde854ce4755977f053f6b0c7c33.png