MySQL进阶之视图、触发器、存储过程、索引、事务、分支结构以及优化等

视图

sql语句的执行结果是一张虚拟表，我们可以基于该表做其他操作

如果这张虚拟表需要频繁使用，那么为了方便可以将虚拟表保存起来，保存起来之后就称之为视图(本质就是一张虚拟表)

语法结构

-- 创建视图
create view techer_course_view as
select * from teacher inner join course on course.teacher_id =teacher.tid;

-- 查看视图
select * from teacher_course_view; -- 和查看普通表的语法一样

-- 删除视图
drop view teacher_course_view;

注意：

  1. 在硬盘中，视图只有表结构文件，没有表数据文件
  2. 视图通常是用于查询，尽量不要修改视图中的数据

视图能尽量少用就尽量少用

触发器

针对表数据的增、删、改自动触发的功能(增前、增后、改前、改后、删前、删后)

语法结构

create trigger 触发器名 before/after insert/update/delete on 表名 for each row
begin
	sql语句
end

注意触发器内部的sql语句需要用到分号，但是分号又是sql语句默认的结束符，所以为了能够完整的写出触发器的代码，需要临时修改sql语句默认的结束符

delimiter $$
	sql语句
delimiter ;

案例

'准备表'
CREATE TABLE cmd (
    id INT PRIMARY KEY auto_increment,
    USER CHAR (32),
    priv CHAR (10),
    cmd CHAR (64),
    sub_time datetime, -- 提交时间
    success enum ('yes', 'no') -- 0代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);
'创建触发器'
delimiter $$  -- 将mysql默认的结束符由；改成$$ 
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if new.success ='no' then  -- 新纪录都会被mysql封装成new对象,if 条件决定了触发器的执行结果
insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);
end if;
end $$
delimiter $$  -- 记得要将结束符修改回来

'往表中插入数据，测试触发器'
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('kevin','0755','ls -l /etc',NOW(),'yes'),
    ('kevin','0755','cat /etc/passwd',NOW(),'no'),
    ('kevin','0755','useradd xxx',NOW(),'no'),
    ('kevin','0755','ps aux',NOW(),'yes');

'查询errlog表记录'
select * from errlog;

'删除触发器'
drop trigger tri_after_insert_cmd;

我们可以用触发器来监控系统或软件的状态

事务

事务的四大特征(ACID)

名称 英文 描述
原子性 Atomicity 事务是不可分割的最小单元，要么全部成功，要么全部失败
一致性 Consistency 事务完成时，必须所有的数据都保持一致的状态
隔离性 Isolation 数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性 Durability 数据一旦提交或回滚，它对数据库的影响是永久的，不可逆的

查看/设置事务提交方式

select @@autocommit;  -- 查看事务的提交方式--->1为自动 0 为手动

set @@autocommit=0;  -- 设置事务的提交方式为手动

提交/回滚事务

commit;  -- 提交事务
rollback; --回滚事务

案例

start transaction;  -- 开启事务

-- 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

commit； -- 只要没有执行commit操作，数据其实都没有真正的存入硬盘

rollback； -- 回滚到更新前状态

一旦commit就无法rollback，一旦rollback就无法commit

开启事务应该检测操作是否完整，不完整主动回滚rollback到上一个状态，如果完整就应该执行commit操作

# 当我们用python操作数据库的时候，可以用异常捕获来检查事务操作的完整性
try:
  update user1 set balance=900 where name='jason'; #买支付100元
  update user1 set balance=1010 where name='kevin'; #中介拿走10元
  update user1 set balance=1090 where name='tank'; #卖家拿到90元
except 异常：
	rollback；
else:
  commit;

扩展知识点(重要)

mysql提供了两种事务型存储引擎InnoDB和NDB cluster以及第三方xtraDB、PBXT

事务处理中有几个关键词汇会反复出现

  • 事务(transcation)
  • 回退(rollback)
  • 提交(commit)
  • 保留点(savepoint)

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)

-- 创建占位符可以使用savepoint
savepoint sq01;

-- 回退到占位符地址
rollback to sp01;

-- 保留点在执行roolback或者commit之后自动释放

在sql标准中定义了四种隔离级别，每种都规定了一个事物中所做的修改

InnoDB支持所有的隔离级别

set transaction isolation level 级别

  1. read uncommitted(未提交读)

    事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为脏读

  2. read committed(提交读)

    大多数数据库系统默认的隔离级别

    一个事物从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做不可重复读

  3. repeatable read(可重复读)

    能够解决脏读问题，但是无法解决幻读

    所谓幻读指的是某个事物在读取某个范围内的记录时另外一个事物又在该范围内插入了新的纪录，当之前的事物再次读取该范围的纪录会产生幻行，innodb和xtradb通过多版本并发控制(mvcc)及间隙锁策略解决该问题

  4. serializable(可串行读)

    强制事务串行执行，很少使用该级别

用事务日志提升事务的效率

存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改记录到持久在硬盘上的事物日志中，而不用每次都将修改的数据本身持久到磁盘

事务日志采用的是追加的方式因此写日志操作是磁盘上一小块区域内的顺序io而不像随机io需要查找多个地方移动磁头，所以采用事务日志的方式相对来说要快的多

事务日志持久之后内存中被修改的数据在后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为预写式日志，修改数据需要写两次磁盘

MVCC多版本并发控制

MVCC只能在read committed(提交读)、repeatable read(可重复读) 两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新， serializable :所有的行都加锁)

innodb的mvcc通过在每行记录后面保存两个隐藏的列来实现mvcc

  • 一个列保存了行的创建时间
  • 一个列保存了行的过期时间(或删除时间) -->本质是版本号

每开始一个新的事务，版本号读会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如

刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

锁

读锁(共享锁):多个用户同意时刻可以同时读取同一个资源互不干扰

写锁(排他锁):一个写锁会阻索其他的写锁和读锁

死锁：

  1. 多个事务试图以不同的顺序锁定资源时就可能会产生死锁
  2. 多个事务同时锁定同一个资源时也会产生死锁

存储过程

mysql的存储过程类似于python中的自定义函数

  • 介绍
    存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

  • 特点
    1.封装，复用
    2.可以接受参数，也可以返回数据
    3.减少网络交互，效率提升

存储过程思想上很简单，就是说数据库sql语言层面的代码封装与重用。

语法结构

delimiter 临时结束符
create procedure 存储过程名称([参数列表])
begin
			-- sql语句
end 临时结束符
delimiter ;

创建一个存储过程


delimiter $$
create procedure p1(
	in m int,  -- in表示这个参数只能是传入不能被返回出去
  in n int,  
  out res int  -- out 表示这个参数可以被返回出去，还有一个inout表示既可以传入又可以被返回
)
begin
select tname from teacher where tid>m and tid<n;
-- 用来标志校验存储过程是否被执行，再调用存储过程前定义变量res为某个值,当调用过p1()后就被改为0
set res =0;  
end $$
delimiter ;

用户自定义变量
set @变量名 = 数据值

-- 针对res我们需要提前定义
set @res =10;
-- 查看res的值
select @res=10；
-- 调用
call p1(1,4,@res)
-- 查看res
select @set  -- 说明存储过程运行完毕
image-20220819175703748 image-20220819175732975

查看、删除存储过程

-- 查看指定存储过程结构
show create procedure 存储过程名 

-- 删除存储过程
drop procedure 存储过程名；

内置函数

各种方法以及案例

'可以通过help 函数名  查看帮助信息'

-- 1.移除指定字符串
trim  ltrim  rtrim

-- 2.大小写转换
lower upper

-- 3.获取左右起始指定个数字符
left  right

-- 4. 返回读音相似值(对英文效果)
soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
		但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
		where Soundex(name)=Soundex('J.Lie')
"""

-- 5.日期格式：date_format
'在mysql中表示时间格式尽量采用2022-11-11形式'

-- 数据准备
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
   
-- 查看相同月份下的数据--->分组加时间格式转换
select date_format(sub_time,'%Y-%m'),group_concat(name) from blog group by date_format(sub_time,'%Y-%m');
image-20220819182623703 
-- 日期类型还可以这样使用
where date(sub_time) ='2015-03-01'
where year(sub_time)=2016 and month(sub_time)=07;

更多日期处理相关函数

adddate	    增加一个日期
addtime     增加一个时间
datediff    计算两个日期差值

流程控制

if条件语句

代码演示

delimiter //
create procedure p_if()
begin
	declare a int default 0; 
	if a=1 then
		select 666;
	elseif a =10 then
		select 888;
	else
		select 16888;
	end if;
end //
delimiter ;

'终端输出'
mysql> call p_if();
+-------+
| 16888 |
+-------+
| 16888 |
+-------+
1 row in set (0.00 sec)

while循环

代码演示

delimiter //
create procedure p_while()
begin
	declare num int default 0;
	while num <3 do
		select num;
		set num = num +1;
	end while;
end //
delimiter ;

mysql> call p_while();
+------+
| num  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

索引

定义

  • 特点

    索引就好比一本书的目录，它能让你更快的找到自己想要的内容

    让获取的数据更有目的性，从而提高数据库检索数据的性能

  • 种类

    primary key

    unique key

    index key

上述的三种键在数据查询的时候使用都可以加快查询的速度，其中primary key、unique key除了可以加快数据查询还有额外的限制，index key只能加快数据查询，本身没有任何的额外限制

注意：索引的存在可以加快数据的查询，但是会减慢数据的增删

索引底层原理

索引的底层原理是通过树来实现，树是一种数据结构，主要用于优化数据查询的操作

种类 结构
二叉树 两个分支
B-树 除了叶子结点有多个分支，其他节点最多只能有两个分支，所有节点都可以存放完整数据(单每个数据块是有固定大小的，层级会很深)
B+树 只有叶子节点存放真正的数据，其他节点只存主键值(辅助索引值)
B*树 在树节点添加了通往其他节点的通道，减少查询次数

索引管理

索引是建立在表的列上(字段)的。

在where后面的列建立索引才会加快查询速度。

pages<---索引(属性)<---查数据。

索引的语法与在千万级别下索引性能的提升

基本语法

-- 创建索引
create index 索引名  on 表名(字段名);

-- 删除索引
drop index 索引名 on 表名;

-- 查看索引
show index from 表名;

下面，我们演示一下在千万条数据下创建索引对查询速度的优化

-- 数据准备
create table user(
	id int primary key auto_increment,
  username varchar(32),
  password varchar(32),
  phone varchar(20),
  email varchar(40)
);
利用pymysql的excutemany()函数导入千万条数据 -- 用存储过程好像太慢了

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    database='db08',
    password='123456',
    charset='utf8mb4',
    autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内参数将结果组织成字典形式
sql = f'insert into user(username,password,phone,email) values (%s,%s,%s,%s)'
list_all=[]
for i in range(10000000):
    list1=[f'jason{i}',f'qaz{i}',f'180{i}',f'jas{i}@126.com']
    list_all.append(list1)
cursor.executemany(sql,list_all)  # 第二个参数应该是列表或元组套列表或元组的形式

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.89 sec)

-- 数据准备好后，分别用通过id和phone查询出指定用户的name，看看执行时间的差距
select username from user where id =5000000;
select username from user where phone=180999999;

+--------------+
| username     |
+--------------+
| jason4999999 |
+--------------+
1 row in set (0.00 sec)

+-------------+
| username    |
+-------------+
| jason999999 |
+-------------+
1 row in set (2.75 sec)

create index idx_phone on user(phone);

-- 索引创建好后，执行查询
select username from user where phone ='180999999'；
+-------------+
| username    |
+-------------+
| jason999999 |
+-------------+
1 row in set (0.00 sec)

通过执行查询的时间可以看出索引的创建对查询的优化很大

explain详解

当我们用查询的sql后，想要查看sql的执行计划，可以用过explain来查看，查看出该sql的信息和执行过程，索引使用方式等，从而优化。

-- 可以通过explain查看 select username from user where phone ='180999999'这个sql的执行计划
image-20220819201044071

常见的索引扫描类型：

  • all
  • index
  • range
  • ref
  • eq_ref
  • const
  • system
  • null

从上到下，性能从最差到最好，我们认为至少要达到range级别

posted @ 2022-08-19 20:14  荀飞  阅读(43)  评论(0)  编辑  收藏  举报