sql优化

sql优化

索引

1、在插入修改删除较多的表上尽量避免建立索引
2、在where语句左边尽量少用通配符，如“WHERE A.NAME LIKE ‘%AAA%’”会使索引失效，而“WHERE A.NAME LIKE ‘AAA%’”则不会

create （unique 唯一索引字段） index 自定义索引名 on 表名（字段名）

存储过程

1、不需要人工干预
2、高性能，执行一次后执行规划驻留在高速缓冲储存器中
3、减少网络流量

- 创建存储过程
CREATE 存储过程的名称(参数)
BEGIN
...需要执行的SQL语句
END

- 调用
CALL 存储过程的名称(参数)

例：

CREATE PROCEDURE p_student_select(参数)
BEGIN 
SELECT * FROM student;
END

CALL p_student_select(参数)

sql语句来自于https://blog.csdn.net/ht_vIC/article/details/121372997

存储函数

1、存储函数只有一个返回参数而存储过程可以拥有多个返回参
2、可以直接调用，不需要call
3、必须含有return

CREATE FUNCTION 表名(参数)

RETURNS 所有mysql适用的数据类型

[NOT] DETERMINISTIC statements

例：

CREATE FUNCTION demo()

RETURNS CHAR(50)

RETURN ( SELECT s_name FROM suppliers WHERE s_call='48075');
DELIMITER ;

触发器

1、保持数据的一致性、完整性和正确性
2、只要增删改的语句可以执行，触发器也要执行

CREATE TRIGGER T2 -- 触发器名称
	ON Teacher   --触发器的表或视图
	FOR INSERT,UPDATE  --添加和修改时触发
	AS DECLARE @TAGE TINYINT    --定义年龄变量
	DECLARE @TSEX varchar(2)	--定义性别变量
	DECLARE @TPROF varchar(10)  --定义职称变量 
	SELECT @TAGE=Age FROM inserted  --插入表的查询
	SELECT @TPROF=Prof FROM inserted
	SELECT @TSEX=Sex FROM inserted
	if(@TSEX='男' and @TAGE>60) or
     (@TSEX='女' and @TAGE>60 and @TPROF='教授') or
     (@TSEX='女' and @TAGE>55 and @TPROF<>'教授')
     BEGIN
     PRINT '你输入的数据不满足要求,请核对后再进行输入!'
     ROLLBACK TRAN
     END
ELSE BEGIN
     PRINT '你输入的数据没有问题,数据已经插入数据库中!'
     COMMIT TRAN
END

事件

事件是基于时间周期执行某些任务，触发器则是基于某个表的事件所触发的

查看事件调度器是否开启（开启返回NO 关闭则返回OFF）
show variables like '%event_scheduler%'

开启调度器
set global event_scheduler=1

关闭调度器
set global event_scheduler=0

创建定时事件
creat event 事件名 on schdule every 时间 do update 字段名 set 参数
例：
creat event clear on schdule every 30second do update p.id set count=0

删除事件
drop event 事件名

视图

1、视图是一张虚拟表，不是真实的表
2、适用于多表联合、复杂的查询，在使用时直接查询该View即可
3、使用View可能会降低查询速度，可以提高硬件配置来缓解

CREATE VIEW 视图名 AS  select语句
select * from 视图名