存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量。同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。
存储过程的优点:
1.减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别,可是如果存储过程包含上百行SQL语句,那么其性能绝对比一条一条的调用SQL语句要高的多。
2.执行速度更快。存储过程创建的时候,数据库已经对其进行了一次解析和优化。其次,存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接读取。
3.更强的安全性。存储过程是通过向用户授予权限,它们可以提供对特定数据的访问,提高代码安全,比如防止SQL注入。
4.业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高。
缺点:
1.可移植性方面:从一种数据库迁移到另外一种数据库时,不少的存储过程的编写要进行部分修改。
2.存储过程需要花费一定的时间学习,比如学习其语法等。
CREATE PROC[EDURE] 存储过程名
@参数1 [数据类型]=[默认值] [OUTPUT]
@参数2 [数据类型]=[默认值] [OUTPUT]
AS
SQL语句
EXEC 过程名[参数]
存储过程与自定义函数的区别
1.一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。
2.存储过程可以返回参数,如记录集,而函数只能返回值或者表对象。函数智能返回一个变量。
3.存储过程一般独立的来执行,而函数可以作为其他SQL语句的组成部分来出现。
存储引擎
MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。
每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
MySQL支持的存储引擎:
---MyISAM ---InnoDB ---Memory ---CSV ---Archive
并发控制:
当多个连接对记录进行修改时保证数据的一致性和完整性。
锁:
共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。
排它锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒:
表锁:是一种开销最小的锁策略。
行锁:是一种开销最大的锁策略。
InnoDB行锁实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
事务:
四大特性(ACID)
1.原子性:
2.一致性:事务开始和结束,数据库的完整性约束没有被破坏。
3.隔离性:不同事务相互隔离,互不影响
4.持久性:事务结束后,结果永久保存,不会出现事务回滚等问题。
事务的分类:
1.扁平事务:
2.带有保存点的扁平事务:
数据库索引:主键索引、非主键索引、聚簇索引、非聚簇索引
分区:水平分区、垂直分区
例题: 执行数据库查询时,如果要查询的数据有很多,假设有1000万条,用什么办法可提高查询效率(速度)?在数据库方面或java代码方面有什么优化的办法?
答案:
1.在数据库设计方面:
a.建立索引:InnoDB使用聚簇索引、MyISM使用非聚簇索引
b.分区:水平分区、垂直分区
c.尽量使用固定长度的字段
d.限制字段长度
2.在数据库I/O方面
a.增加缓冲区
b.如果涉及表的级联,不同的表存储在不同的磁盘上,以增加I/O速度。
3.在SQL语句方面
a.优化SQL语句,减少比较次数。
b.限制返回的条目数(MySQL中用limit)
4.在java方面
如果是反复查询,使用PreparedStatement减少查询次数。