目录
1 函数和存储过程
存储过程和函数是事先经过编译并存储在数据库中的一段sql语句集合,调用存储过程和函数可以简化应用开发工作,提高数据处理的效率。
1.1 函数创建
delimiter 自定义符号
create function 函数名(形参列表) returns 返回类型 -- 注意是retruns
begin
函数体 -- 若干sql语句,但是不要直接写查询
return val;
end 自定义符号
delimiter ;
释义:
delimiter 自定义符号 是为了在函数内些语句方便,制定除了;之外的符号作为函数书写结束标志,一般用$$或者//
形参列表 : 形参名 类型 类型为mysql支持类型
返回类型: 函数返回的数据类型,mysql支持类型即可
函数体: 若干sql语句组成
return: 返回指定类型返回值
e.g. 无参数的函数调用
delimiter $$
create function st() returns int
begin
return (select score from class order by score desc limit 1);
end $$
delimiter ;
select st();
e.g. 含有参数的函数调用
delimiter $$
create function queryNameById(uid int)
returns varchar(20)
begin
return (select name from class where id=uid);
end $$
delimiter ;
select queryNameById(1);
- 设置变量
- 定义用户变量 : set @[变量名] = 值;使用时用@[变量名]。
- 定义局部变量 : 在函数内部设置 declare [变量名] [变量类型]; 局部变量可以使用set赋值或者使用into关键字。
1.2 存储过程创建
创建存储过程语法与创建函数基本相同,但是没有返回值。
delimiter 自定义符号
create procedure 存储过程名(形参列表)
begin
存储过程 -- sql语句构成存储过程语句集
end 自定义符号
delimiter ;
释义:
delimiter 自定义符号 是为了在函数内些语句方便,制定除了;之外的符号作为函数书写结束标志
形参列表 :[ IN | OUT | INOUT ] 形参名 类型
in 输入,out 输出,inout 可以输入也可以输出
存储过程: 若干sql语句组成,如果只有一条语句也可以不写delimiter和begin,end
e.g. 存储过程创建和调用
delimiter $$
create procedure st()
begin
select name,age from class;
select name,score from class order by score desc;
end $$
delimiter ;
call st();
-
存储过程三个参数的区别
-
IN 类型参数可以接收变量也可以接收常量,传入的参数在存储过程内部使用即可,但是在存储过程内部的修改无法传递到外部。
-
OUT 类型参数只能接收一个变量,接收的变量不能够在存储过程内部使用(内部为NULL),但是可以在存储过程内对这个变量进行修改。因为定义的变量是全局的,所以外部可以获取这个修改后的值。
-
INOUT类型参数同样只能接收一个变量,但是这个变量可以在存储过程内部使用。在存储过程内部的修改也会传递到外部。
-
e.g. : 分别将参数类型改为IN OUT INOUT 看一下结果区别
delimiter $$
create procedure p_out ( OUT num int )
begin
select num;
set num=100;
select num;
end $$
delimiter ;
set @num=10;
call p_out(@num)
1.3 存储过程和存储函数操作
- 调用存储过程
语法:
call 存储过程名字([存储过程的参数[,……]])
- 调用存储函数
语法:
select 存储函数名字([函数的参数[,……]])
- 使用show create语句查看存储过程和函数的定义
语法:
show create {procedure|function} 存储过程或存储函数的名称
- 查看所有函数或者存储过程
select name,type from mysql.proc where db='stu';
- 删除存储过程或存储函数
语法:
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
1.4 函数和存储过程区别
- 函数有且只有一个返回值,而存储过程不能有返回值。
- 函数只能有普通参数,而存储过程可以有in,out,inout多个类型参数。
- 存储过程中的语句功能更丰富,实现更复杂的业务逻辑,可以理解为一个按照预定步骤调用的执行过程,而函数中不能展示查询结果集语句,只是完成查询的工作后返回一个结果,功能针对性比较强。
- 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
2 数据库优化
2.1 数据库设计范式
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式。
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
各种范式呈递次规范,越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细,一个数据库中需要的表也就越多,此时多个表联接在一起的花费是巨大的,尤其是当需要连接的两张或者多张表数据非常庞大的时候,表连接操作几乎是一个噩梦,这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式,以避免数据操作异常,又不至于表关系过于复杂。
范式简介:
-
第一范式: 数据库表的每一列都是不可分割的原子数据项,而不能是集合,数组,记录等组合的数据项。简单来说要求数据库中的表示二维表,每个数据元素不可再分。
例如: 在国内的话通常理解都是姓名是一个不可再拆分的单位,这时候就符合第一范式;但是在国外的话还要分为FIRST NAME和LAST NAME,这时候姓名这个字段就是还可以拆分为更小的单位的字段,就不符合第一范式了。
-
第二范式: 第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分,所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组,作为实体的唯一标识,每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。
-
第三范式: 在第二范式的基础上属性不传递依赖,即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键,使不同的表中不要有重复的字段就好了。
2.2 MySQL存储引擎
-
定义: mysql数据库管理系统中用来处理表的处理器
-
基本操作
1、查看所有存储引擎
mysql> show engines;
2、查看已有表的存储引擎
mysql> show create table 表名;
3、创建表指定
create table 表名(...)engine=MyISAM;
4、已有表指定
alter table 表名 engine=InnoDB;
-
常用存储引擎特点
InnoDB
1. 支持行级锁,仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进 行操作。 2. 支持外键、事务、事务回滚 3. 表字段和索引同存储在一个文件中 1. 表名.frm :表结构 2. 表名.ibd : 表记录及索引文件
MyISAM
1. 支持表级锁,在锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则 读也不允许 2. 表字段和索引分开存储 1. 表名.frm :表结构 2. 表名.MYI : 索引文件(my index) 3. 表名.MYD : 表记录(my data)
-
如何选择存储引擎
1. 执行查操作多的表用 MyISAM(使用InnoDB浪费资源) 2. 执行写操作多的表用 InnoDB CREATE TABLE tb_stu( id int(11) NOT NULL AUTO_INCREMENT, name varchar(30) DEFAULT NULL, sex varchar(2) DEFAULT NULL, PRIMARY KEY (id) )ENGINE=MyISAM;
2.3 字段数据类型和键的选择
- 数据类型优先程度 数字类型 --> 时间日期类型 --> 字符串类型
- 同一级别 占用空间小的 --> 占用空间大的
字符串在查询比较排序时数据处理慢
占用空间少,数据库占磁盘页少,读写处理就更快
- Innodb如果不设置主键也会自己设置隐含的主键,所以最好自己设置
- 尽量设置占用空间小的字段为主键
- 建立外键会自动建立索引,在表关联查询时建议使用外键子段作为关联条件
- 外键虽然可以保持数据完整性,但是会降低数据导入和操作效率,增加维护成本
2.4 explain语句
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
explain select * from class where id <5;
EXPLAIN主要字段解析:
-
table:显示这一行的数据是关于哪张表的
-
type:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL,一般来说,得保证查询至少达到range级别,最好能达到ref。
type中包含的值:
- system、const: 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref: 访问索引,返回某单一行的数据.(通常在联接时出现,查询使用的索引为主键或唯一键)
- ref: 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生
- range: 这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西,并且该字段上建有索引时发生的情况
- index: 以索引的顺序进行全表扫描,优点是不用排序,缺点是还要全表扫描
- ALL: 全表扫描,应该尽量避免
-
possible_keys:显示可能应用在这张表中的索引。如果为空,表示没有可能应用的索引。
-
key:实际使用的索引。如果为NULL,则没有使用索引。
-
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
-
rows:MySQL认为必须检索的用来返回请求数据的行数
2.5 SQL优化
-
尽量选择数据类型占空间少,在where ,group by,order by中出现的频率高的字段建立索引
-
尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段
-
尽量控制使用自定义函数
-
查询最后添加 LIMIT 会停止全表扫描
-
尽量避免 NULL 值判断,否则会进行全表扫描,默认值为空时可以用默认0代替
优化前:select number from t1 where number is null;
优化后:select number from t1 where number=0;
-
尽量避免 or 连接条件,否则会放弃索引进行全表扫描,可以用union代替
优化前:select id from t1 where id=10 or id=20;
优化后: select id from t1 where id=10 union all select id from t1 where id=20;
-
尽量避免使用 in 和 not in,否则会全表扫描
优化前:select id from t1 where id in (1,2,3,4);
优化后:select id from t1 where id between 1 and 4;
2.6 表的拆分
垂直拆分 : 表中列太多,分为多个表,每个表是其中的几个列。将常查询的放到一起,blob或者text类型字段放到另一个表
水平拆分 : 减少每个表的数据量,通过关键字进行划分然后拆成多个表