MySQL的高阶部分
1. MySQL的事务
(1)存储引擎的介绍
介绍:当客户端发送一条SQL语句给服务器时,服务器端通过缓存、语法检查、校验通过之后,然后会通过调用底层的一些软件组织,去从数据库中查询数据,然后将查询到的结果集返回给客户端,而这些底层的软件组织就是存储引擎。
MySQL的存储引擎:
- MySQL的核心就是存储引擎,MySQL可以设置多种不同的存储引擎,不同的存储引擎在索引、存储、以及锁的策略上是不同的。
- Mysql5.5之前,使用的是myisam存储引擎,支持全文搜索,不支持事务。
- Mysql5.5以后,使用的是innodb存储引擎,支持事务以及行级锁
(2)MySQL事务的介绍
介绍:事务是一个操作序列,这些操作要么都做,要么都不做,是一个不能分割的工作单位。在两条或两条以上的SQL语句才能完成的业务时,才需要用事务,因为事务时同步原则,效率比较低。
事务的ACID特性:
- 原子性:放在同一事务的一组操作时不可分割的
- 一致性:在事务的执行前后,整体的状态是不变的
- 隔离性:事务之间是独立存在的,两个不同事务之间互不影响
- 持久性:事务执行之后,将会永久的影响到数据库。
#例:一个事务操作
BEGIN;
update t_account set money=money+100 where id =1;
update t_account set money=money-100 where id =2;
COMMIT;
#一个回滚操作
BEGIN;
update t_account set money=money+100 where id =1;
update t_account set money=money-100 where id =2;
COMMIT;
注意:MySQL数据库,dml操作采用的是自动提交
#查看自动提交
show variables like 'autocommit';
#修改自动提交
set autocommit=0;
(3)MySQL事务并发时产生的问题
脏读:在一个事务的执行范围内,读到了另一事务未提交的数据。
解决:读已提交,一个数据库只能读到另一个事务提交后的数据。(Oracle默认的事务隔离级别)
不可重复读:一个事务,在只读范围内,被另一事务修改并提交事务,导致多次读取的数据不一致的问题。
解决:可重复读(MySQL默认的事务隔离级别)
虚读:一个事务的只读范围内,被另一个事务删除或者添加数据,导致多次读取的数据不一致的问题。
解决:串行化:解决所有问题,但是速度十分缓慢,不能使用并发事务。
注意:查看事务的隔离级别:select @@tx_isolation;
2. MySQL的存储程序
(1)MySQL的存储程序的介绍
描述:运行与服务器端的程序。
优点:简化开发,执行效率比较高(在服务器端以通过校验,可直接使用)
缺点:服务器端保存这些存储程序需要占用磁盘空间;数据迁移时,需要将这些存储程序进行迁移;调试和编写程序在服务器端都不方便
存储程序的分类:存储过程、存储函数、触发器
注意:存储程序不能使用事务
(2)存储过程
介绍:存储过程是在服务器端的一段可执行的代码块。
例:
#修改结束符标志
delimiter //
#创建存储过程
create procedure pro_book()
begin
#sql
select * from book;
select * from book where bid=3;
end //
#运行
call pro_book()
#参数的传入
delimiter //
create procedure pro_book02(num int)
begin
select * from book where bid=num;
end ; //
--调用
call pro_book02(3)
#传出参数
delimiter //
create procedure pro_book03(num int,out v_name varchar(10))
begin
select bname into v_name from book where bid=num;
end ; //
--调用,这里的@v_name是一个用户变量
call pro_book03(1,@v_name);
select @v_name;
#传入传出参数
delimiter //
create procedure pro_book04(num int)
begin
select bid into num from book where bid=num;
end ; //
--调用
set @v_id=3; --给用户变量赋值
call pro_book04(@v_id);
select @v_id;
控制流程语句
#if语句
delimiter //
create procedure if_test(score int)
begin
-- 定义局部变量
declare myLevel varchar(20);
if score>80 then
set myLevel='A';
elseif score >60 then
set myLevel='B';
else
set myLevel='C';
end if;
select myLevel;
end; //
-- 调用
call if_test(70);
#while循环
delimiter //
create procedure while_test()
begin
declare i int ;
declare sum int ;
set i=1;
set sum =0;
while i<=100 do
set sum=sum+i;
set i=i+1;
end while ;
select sum;
end ;//
call while_test()
#loop循环
delimiter //
create procedure loop_test()
begin
declare i int ;
declare sum int ;
set i=1;
set sum =0;
-- 起别名
lip:loop
if i>100 then
-- 离开loop循环
leave lip ;
end if ;
set sum=sum+i;
set i=i+1;
end loop ;
select sum;
end ;//
call loop_test()
#repeat循环
delimiter //
create procedure repeat_test()
begin
declare i int ;
declare sum int ;
set i=1;
set sum =0;
repeat
set sum=sum+i;
set i=i+1;
-- 不要加分号
until i>100
end repeat ;
select sum;
end ;//
call loop_test()
(3).存储函数
存储在服务器端,有返回值,函数可以作为SQL的一部分进行调用。
例:
delimiter //
create function func_01(num int)
-- 返回值类型
returns varchar(20)
deterministic
begin
declare v_name varchar(20);
select bname into v_name from book where bid =num ;
return v_name;
end ; //
set @v_name=func_01(3);
select @v_name;
-- 作为SQL的一部分调用
select * from book where bname=func_01(3);
函数和存储过程的区别:
- 存储过程有三种参数模式(in、out、inout)实现数据的输入输出,而函数是通过返回值进行数据传递。
- 关键字不同
- 存储过程可以作为独立个体执行,函数只能作为SQL的一部分执行。
(4).触发器
触发器,存储在服务器端,由事件调用,不能传参。
事件类型:增、删、改
语法:
create trigger 触发器名
触发时机(after|before) event(update|delete|insert)
on 需要设置触发器的表名 for each row (设置为行级触发器)
begin
一组sql
end;
例:
delimiter //;
-- 创建一个触发器
create trigger tri_test
after delete
-- 设置为行级别的触发器
on book for each row
begin
insert into book values(old.id,'悲惨数据','zzy');
end;//
注意:在触发器中有两个对象:old、new,old表示删除数据时那条原数据记录,
new表示修改和插入数据时,那条新数据记录。
3. MySQL的表的设计
(1)数据库的三大范式:
- 1NF:所有字段都是原子性的,不可分割的。
- 2NF:非主键字段必须与主键相关(每一张表只描述一类事物),而不能与主键部分相关(在联合主键时有效)
- 3NF:非主键字段必须与主键相关(每一张表只描述一类事物),而不能与主键部分相关(在联合主键时有效)
(2)表的关系:
一一对应
#以人和×××为例
#人表:
CREATE TABLE `t_people` (
`id` int(11) NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)
#×××表:
create table t_idcard(
card_number varchar(18) primary key,
create_date date,
p_id int unique,
foreign key (p_id) REFERENCES t_people(id)
)
注意:设计方法:想办法让外键字段同时拥有唯一约束,外键字段在任意的表中都可以
一对多:
#以部门和员工表为例:
create table t_emp(
eid int PRIMARY KEY,
ename varchar(50) not null,
job varchar(50),
deptno int ,
foreign key (deptno) REFERENCES t_dept(deptno)
)
#部门表:
create table t_dept(
deptno int primary key,
deptname varchar(50)
)
注意:设计方法:只需要在多的那个表中增加一个外键约束
多对多:
设计方法:需要找一张中间表,转化成两个一对多的关系
4.数据库的优化:
1、尽量避免全表扫描,首先应考虑在where及order by设计的列上建立索引。
2、尽量避免在where字句中对字段进行null判断,否则将导致引擎放弃使用索引而进行全表扫描。
3、应尽量避免在where字句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
4、应尽量避免在where字句中使用or来链接条件。
5、使用in和not in也要慎用。
6、尽量不要使用select *来查询。
7、避免频繁的创建和删除临时表,以减少系统表的资源消耗
5.mysql有关权限的表
MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。
这些权限表分别user,db,table_priv,columns_priv和host。下面分别介绍一下这些表的结构和内容:
user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
db权限表:记录各个帐号在各个数据库上的操作权限。
table_priv权限表:记录数据表级的操作权限。
columns_priv权限表:记录数据列级的操作权限。
host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
6.数据表损坏的修复方式
使用 myisamchk 来修复,具体步骤:
1)修复前将mysql服务停止。
2)打开命令行方式,然后进入到mysql的/bin目录。
3)执行myisamchk –recover 数据库所在路径/*.MYI
使用repair table 或者 OPTIMIZE table命令来修复,REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表
REPAIR TABLE 用于修复被破坏的表。 OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘
空间并没有立即被回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,
而非数据库)
7.数据库架构
数据库架构要考虑的问题:
- 数据可靠和一致性;
- 数据容错;
- 当数据量和访问压力变大时,方便扩充;
- 高度可用,出问题时能及时恢复,无单点故障;
- 不应因为某一台机器出现问题,导致整网性能的急剧下降;
- 方便维护
8.数据库的读写分离
1. 将读操作和写操作分离到不同的数据库上,避免主服务器出现性能瓶颈;
2. 主服务器进行写操作时,不影响查询应用服务器的查询性能,降低阻塞,提高并发;
3. 数据拥有多个容灾副本,提高数据安全性,同时当主服务器故障时,可立即切换到其他服务器,提高系统可用性;
9.mysql 索引的建立原则
1、索引分四类:
index ---- 普通索引,数据可以重复
fulltext ---- 全文索引,用来对大表的文本域(char,varchar,text)进行索引
unique ---- 唯一索引,要求所有记录都唯一
primary key ---- 主键索引,也就是在唯一索引的基础上相应的列必须为主键
2、使用索引需注意:
(1). 只对 where 和order by 需要查询的字段设置索引,避免无意义的硬盘开销;
(2). 组合索引支持前缀索引;
(3). 更新表的时候,如增删记录,MySQL会自动更新索引,保持树的平衡;因此更多的索引意味着更多的维护成本
3、索引建立原则
(1). 尽量减少like,但不是绝对不可用,”xxxx%” 是可以用到索引的
(2). 表的主键、外键必须有索引
(3). 谁的区分度更高(同值的最少),谁建索引,区分度的公式是count(distinct(字段))/count(*)
(4). 单表数据太少,不适合建索引
(5). where,order by ,group by 等过滤时,后面的字段最好加上索引
(6). 如果既有单字段索引,又有这几个字段上的联合索引,一般可以删除联合索引;
(7). 联合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
(8). 联合索引: mysql 从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index(a,b,c). 可以支持 a|a,b|a,b,c 3种组合进行查找,但不支持 b,c 进行查找.当最左侧字段是常量引用时,索引就十分有效。
(9). 前缀索引: 有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于覆盖索引 Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。
(10). NULL会导致索引形同虚设
4、禁用索引
- like “ %xxx ”
- not in , !=
- 对列进行函数运算的情况(如 where md5(password) = “xxxx”)
- WHERE index=1 OR A=10
- 存了数值的字符串类型字段(如手机号),查询时记得不要丢掉值的引号,否则无法用到该字段相关索引,反之则没关系