视图触发器事务 MVCC 存储过程 MySQL函数 MySQL流程控制索引的数据结构索引失效慢查询优化explain 数据库设计三范式

2023年5月10日 83点热度 0人点赞 0条评论

视图 create view ... as
触发器
事务
多版本并发控制 MVCC
存储过程 procedure
函数
- 常用函数 soundex
流程控制
- 分支结构 IF THEN
- 循环结构 WHILE DO
索引
索引的数据结构
索引失效
慢查询优化
- explain命令使用方法
- 查询数据的方式
  - 全表扫描
  - 索引扫描
数据库设计三范式

视图 create view ... as

ps:SQL文件在上一篇博客末尾

视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用

create view teacher_course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

1.视图的表只能用来查询不能做其他增删改操作 视图的数据来源于原表
2.视图尽量少用 会跟真正的表产生混淆 从而干扰操作者 终端里视图和原表放在一起，Navicat会把视图单独存放在views。

将课程表与教师表拼接产生视图：

视图表无法删除：

触发器

简介

达到某个条件之后自动触发执行
在MySQL中更加详细的说明是触发器:针对表继续增、删、改操作能够自动触发
主要有六种情况:增前、增后、删前、删后、改前、改后

创建触发器的语法 create trigger

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

触发器命名有一定的规律

触发器的命名不是硬性规范，想自定义名字也可以。
tri_before_insert_t1
tri_after_delete_t2
tri_after_update_t2

临时修改SQL语句的结束符 delimiter

因为有些操作中需要使用分号 使用关键字更换SQL语句的结束符
delimiter $$  /* 临时将结束符更改为$$ */
delimiter ；  /* 用完记得改回来 */

触发器的实际运用

/* 创建cmd表 */
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
);


/* 创建触发器
1.tri_after_insert_cmd 表示cmd表增加数据后触发 名字可以修改 这样写比较规范
2.新增的数据的success字段如果为no，则会向errlog表插入一条数据，这条数据包含cmd表新增数据的信息。
3.NEW指代的当前cmd表的每一条数据对象，相当于每次往cmd表插入的数据。
*/
delimiter $$  # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin  # 表示SQL语句开始
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;  # 表示if语句结束，加上就行了。
end $$ # SQL语句结束 再加上结束符$$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

无注释版：
delimiter $$
create trigger helloworld after insert on cmd for each row
begin
    if NEW.success = 'no' then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
#NOW()函数可以获取当前的时间
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;

给cmd表插入数据之后，查看errlog表：

触发器补充方法 show triggers\drop trigger

查看所有的触发器: show triggers;

trigger --> 触发器的名字
event:INSERT --> 在发生插入的时候触发
table --> 发动触发器的表
statement --> sql语句
timming --> 触发的时机（这里是在插入之后）
删除触发器: drop trigger tri_after_insert_cmd;
新增多个触发器时报错：

这个版本的MySQL还不支持“同一个表中具有相同操作时间和事件的多个触发器”。

事务

事务的四大特性 ACID

事务的四大特性(ACID)
	A:原子性
		事务中的各项操作是不可分割的整体 要么同时成功要么同时失败
		比如银行转账 你的账户增加钱 别人账户扣钱 要么同时成功要么同时失败
	C:一致性
    	使数据库从一个一致性状态变到另一个一致性状态
 	I:隔离性
    	多个事务之间彼此不干扰
		他给你转钱，你给他转钱，互不干扰（操作数据时互不干扰）
  	D:持久性
     	也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的
		转账成功之后，账户的钱就永久性的减少了，不可能回退到转账之前的状态。

事务实际运用 start transaction

/* 创建用户表 */
create table user(
id int primary key auto_increment,
name char(32),
balance int
);

/* 添加基础数据 */
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

/* 修改数据之前先开启事务操作 */
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元

/* 回滚到上一个状态 */
rollback;

/* 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘*/
commit;

/*
回滚怎么实现？
没确认时，改的是内存中的数据，不影响硬盘中的真实数据。
当进行确认，才会将内存的数据 保存到硬盘 永久修改

执行rollback commit意味着事务结束。
*/

rollback:

commit确认之后无法rollback:

事务相关关键字 savepoint

"""
事务相关关键字
	start transaction;
	rollback
	commit
	savepoint 保留点
	部分事务 和 完整事务
"""
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，
这样如果需要回退可以回退到某个占位符（保留点）
# ps:但这也打破了事务的原子性，所以使用的少

创建占位符：savepoint sp01;
回退到占位符地址： rollback to sp01;

事务的隔离级别（重要）

事务需要对数据进行操作，所以事务与事务之间需要隔离操作，防止产生错误。
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别

1.read uncommitted(未提交读)

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

2.read committed(提交读)

大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"

3.repeatable read(可重复读)

可重复读是MySQL默认隔离级别。
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
幻读的例子
某事务读取数据库发现有3条数据结果另一个事物删了一条数据此时先前的事务读取的信息就错了，故成为幻读。
如何解决幻读？
在数据上做特殊标记，数据被修改，这个唯一标识会变。事务修改时检查标识，一旦发现标识和之前读取的时候不同，就重新读数据库。

4.serializable(可串行读)

强制事务串行执行，很少使用该级别
一次性只有一个事务操作数据，慢的离谱。

多版本并发控制 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值，这表示在事务开始之后这行记录才被删除。
"""

存储过程 procedure

有参函数和无参函数

/* 可以看成是python中的自定义函数 */

# 无参函数
delimiter $$
create procedure p1()
begin
	select * from cmd;
end $$
delimiter ;

# 调用
call p1()


# 有参函数
delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from cmd where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要先提前定义 
set @res=10;  定义
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

/* 
1. 注意 in out inout
2. 为什么要使用set定义？ 需要一个变量接受返回值
3. 为什么要@? @指向函数里的返回值res
*/

存储过程相关关键字 show procedure status

查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;

在mysql和python中使用

# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！

# 1、直接在mysql中调用
set @res=10  # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10);  # 报错
call p1(2,4,@res);  

# 查看结果
select @res;  # 执行成功，@res变量值发生了变化

# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

函数

可以看成是python中的内置函数
mysql的内置函数只能在SQL语句中使用

常用函数 soundex


/* 可以通过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'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate	增加一个日期 
	addtime	增加一个时间
	datediff计算两个日期差值

Left函数只要数据的指定个数的字符：

返回读音相似的值soundex（只对英文有效）：

date_format实现真实业务逻辑（将博客按照年月分组）：

blog表展示
按照年月分组

流程控制

分支结构 IF THEN

declare i int default 0;
IF i = 1 THEN
	SELECT 1;
ELSEIF i = 2 THEN
	SELECT 2;
ELSE
	SELECT 7;
END IF;

循环结构 WHILE DO

DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
	SELECT num ;
	SET num = num + 1 ;
END WHILE ;

索引

索引的概念

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容
2）让获取的数据更有目的性，从而提高数据库检索数据的性能

MySQL中的索引 index key

MySQL索引主要有两种结构：B+Tree索引和Hash索引。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构，mysql有以下几种键：

* primary key
* unique key
* index key
'''
1.上述的三个key都可以加快数据查询
2.primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
3.外键不属于索引键的范围 是用来建立关系的 与加快查询无关
'''

索引加快查询的本质

	id int primary key auto_increment,
 	name varchar(32) unique,
  	province varchar(32)
 	age int
 	phone bigint
 	
	select name from userinfo where phone=18818888888;  # 一页页的翻
	select name from userinfo where id=99999;  # 按照目录确定页数找

/*
1. 索引可以加快数据查询 但是会降低增删的速度  (发生增删改操作时，会重新建立索引)
2. 通常情况下我们频繁使用某些字段查询数据，为了提升查询的速度可以将该字段建立索引
*/

聚焦索引辅助索引覆盖索引非覆盖索引

聚集索引(primary key)
	主键、主键索引
辅助索引(unique,index)
	除主键意外的都是辅助索引
	辅助索引在查询数据的时候还是要借助于聚集索引
	/*查询顺序： 辅助索引树 --> 主键索引 --> 主键索引树 -->真实数据 */
	
覆盖索引
	select name from user where name='jason';
非覆盖索引
	select age from user where name='jason';

索引的数据结构

索引底层其实是树结构>>>:树是计算机底层的数据结构

'''注意：树结构每一个节点存放的数据大小是固定的'''

树有很多中类型
	二叉树、b树、b+树、B*树......

二叉树

#　二叉树
	二叉树里面还可以细分成很多领域 我们简单的了解即可 
  	二叉意味着每个节点最大只能分两个子节点
	二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。
	
	根节点：树的最顶端的节点。（根节点只有一个）
	枝节点：除根节点之外，并且本身下面还连接有节点的节点。
	叶结点：自己下面不再连接有节点的节点（即末端），称为叶子节点（又称为终端结点）。

B树

所有的节点都可以存放完整的数据

MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？

可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找树，主要的特点是：

1、叶子节点都在同一层，叶子节点没有指针连接
2、B树的节点中存储着多个元素，每个内节点有多个分叉
3、节点中的元素包含键值和数据，节点中的键值从大到小排列
4、所有的节点都可以存放完整的数据

下面模拟下查找key为27的data的过程：

存在的一些问题：

B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响查询效率。
不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

B+\B*树

只有叶子节点才会存放真正的数据 其他节点只存放索引数据
B+叶子节点增加了指向其他叶子节点的指针
B*叶子节点和枝节点都有指向其他节点的指针

对比B树和B+树，我们发现二者主要存在以下几点不同的地方：

数据都存放在叶子节点中
非叶子节点只存储键值信息，不再存储数据
所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表

B+树等值查询

下面模拟下查找key为9的data的过程：

B+树范围查询

下面模拟下查找key的范围为9到26这个范围的data的过程：

从上面的结果，我们可以知道B+树作为索引结构带来的好处：

磁盘IO次数更少
数据遍历更为方便
查询性能更稳定

由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。

索引失效

有时候就算采用索引字段查询数据也可能不会走索引!!!

1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

2.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

3.OR前后存在非索引的列，索引失效
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

4.普通索引的不等于不会走索引；如果是主键，则还是会走索引；如果是主键或索引是整数类型，则还是会走索引

5.计算导致索引失效

慢查询优化

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

查询数据的方式

全表扫描

1）在explain语句结果中type为ALL
2）什么时候出现全表扫描?
2.1 业务确实要获取所有数据
2.2 不走索引导致的全表扫描
2.2.1 没索引
2.2.2 索引创建有问题
2.2.3 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

索引扫描

2.1 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

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

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key 作为关联条件A join B on A.sid=B.sid
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

参考：
https://www.cnblogs.com/Dominic-Ji/articles/15429493.html
https://www.cnblogs.com/Dominic-Ji/articles/15426531.html

数据库设计三范式

第一范式：任何一张表都应该有自己的主键，并且每一个字段的原子性都是不可再分的。
第二范式：在第一范式的基础上，要求所有的非主键字段完全依赖主键，不能产生部分依赖。
第三范式：在第二范式的基础上，所有非主键只能依赖于主键，不能产生传递依赖。
'''
提醒：完美符合三范式的数据库也比较少，为了满足客户的实际需求，
常常会用数据冗余去换执行速度，就是鱼和熊掌的关系。
'''