优化视图逻辑太过复杂导致性能较低的问题

优化视图逻辑太过复杂导致性能较低的问题

1、对视图的基础表的部分条件字段进行添加索引，以提高视图查询的性能

2、使用触发器，将视图的数据物化到一个数据表，业务通过直接查询该表，速率提高很快，但是存在部分问题，会导致设计的表的新增、删除与更新的操作变慢
优点：实时性强，但是会导致其他操作的性能较差。

3、新增一个临时表，并且用存储过程的方式，将视图的数据物化到临时表。优点：性能提高，耗费资源少； 缺点：实时性差，并且会出现数据空洞的情况。

4、新增两个临时表，并且用存储过程的方式，将视图的数据物化到临时表，再将临时表的数据传到临时表2，业务通过查询临时表2进而获取数据，该方法基本解决了方法3的空洞期的问题。
但是实时差，若业务实时性要求大时，无法满足。

注：
1、存储过程的写法
CREATE DEFINER=root@localhost PROCEDURE device_temp_pro()
BEGIN
TRUNCATE TABLE temp;
INSERT INTO temp select * from devices_view;
TRUNCATE table temp_devices_view;
INSERT into temp_devices_view select * from temp;
END

2、存储过程可通过事件进而对存储过程进行定时出发。

测试用例：
删除触发器：

create TRIGGER temp_device_delete
AFTER delete on devices
for each row 
begin  
	delete from temp where not EXISTS (select * from devices_view where temp.`code`= devices_view.`code`);
end;

新增触发器：

create TRIGGER temp_device_insert
AFTER insert on devices
for each row 
begin  
	set @old_id = 0;
	set @old_pid = 0;
	set @old_code = "";
	set @old_mode = "";
	set @old_rotation = 0;
	set @old_pd = null;
	set @old_deleteflag = 0;
	set @old_memo ="";
	set @old_deviceImg ="";
	set @old_manual ="";
	set @old_operatingSystem ="";
	set @old_ParameterSetting ="";
	set @old_manufacturesName ="";
	set @old_address ="";
	set @old_tel ="";
	set @old_lastTime = null;
	set @old_longitude ="";
	set @old_latitude ="";
	set @old_groupName ="";
	set @old_kv ="";
	
	select * from devices_view where devices_view.`code` = new.`code`  INTO @old_id, @old_pid,@old_code,@old_mode,@old_rotation,@old_pd,@old_deleteflag,@old_memo,@old_deviceImg,@old_manual,@old_operatingSystem,@old_ParameterSetting,@old_manufacturesName,@old_address,@old_tel,@old_lastTime,@old_longitude,@old_latitude,@old_groupName,@old_kv;
	
		replace into temp VALUES(@old_id, @old_pid,@old_code,@old_mode,@old_rotation,@old_pd,@old_deleteflag,@old_memo,@old_deviceImg,@old_manual,@old_operatingSystem,@old_ParameterSetting,@old_manufacturesName,@old_address,@old_tel,@old_lastTime,@old_longitude,@old_latitude,@old_groupName,@old_kv);
		
	end;

更新触发器：

create TRIGGER temp_device_update
AFTER update on devices
for each row 
begin  
	set @old_id = 0;
	set @old_pid = 0;
	set @old_code = "";
	set @old_mode = "";
	set @old_rotation = 0;
	set @old_pd = null;
	set @old_deleteflag = 0;
	set @old_memo ="";
	set @old_deviceImg ="";
	set @old_manual ="";
	set @old_operatingSystem ="";
	set @old_ParameterSetting ="";
	set @old_manufacturesName ="";
	set @old_address ="";
	set @old_tel ="";
	set @old_lastTime = null;
	set @old_longitude ="";
	set @old_latitude ="";
	set @old_groupName ="";
	set @old_kv ="";
	
	select * from devices_view where devices_view.`code` = new.`code`  INTO @old_id, @old_pid,@old_code,@old_mode,@old_rotation,@old_pd,@old_deleteflag,@old_memo,@old_deviceImg,@old_manual,@old_operatingSystem,@old_ParameterSetting,@old_manufacturesName,@old_address,@old_tel,@old_lastTime,@old_longitude,@old_latitude,@old_groupName,@old_kv;
	
		update temp set id=@old_id, pid=@old_pid, `mode` =@old_mode, rotation=@old_rotation, pd=@old_pd, deleteflag=@old_deleteflag, memo=@old_memo, deviceImg= @old_deviceImg, manual=@old_manual, operatingSystem=@old_operatingSystem,ParameterSetting= @old_ParameterSetting, manufacturesName=@old_manufacturesName,address=@old_address,tel=@old_tel,lastTime=@old_lastTime,longitude=@old_longitude, latitude=@old_latitude, groupName=@old_groupName, kv=@old_kv where `code`= @old_code;
		
	end;