视图
概念 :就是有一张或者多张表组织成的结果形成的一张虚拟表,仅仅是为了更便捷的查看数据而产生的,无法使用sql语句做增删改的操作!(不推荐使用)
语句结构:
create view 视图名 as SQL语句;
触发器
概念 : 对表数据的增删改自动触发的功能(增前,增后,改前,改后,删前,删后)
1.关键字:trigger
创建触发器 : create trigger 触发器的名字 before/after 增/删/改 on for each row
begin
sql语句;
end
'''
触发器内部sql语句的结尾需要用分号,但是分号在sql语句是默认结束的意思
为了完整的写完触发器,需要临时修改sql语句的结束符
关键字:delimiter &&
sql语句
delimiter;
'''
# 触发器结束以后 需要在end后面把结束符修改回来 delimiter ;
删除触发器 drop tirgger 触发器的名字
事务
概念 :多个操作同时进行,要么都成功,要么都失败,这也称之为事务的原子性
关键字 :transaction
# 事务的四大特性(ACID) :
A.原子性 :要么都成功,要么都失败。
C.一致性 :事务执行接收之后,数据库的完整性不被破坏
I.隔离性 :多个事务之间相互隔离,互不干扰,事务提交前对其他事务不可见
D.持久性 :事务一旦提交,对数据库的改变就是永久的
'''
事务的相关代码:
1.start transaction 开启事务
2.commit 事务的提交 (没有执行commit的数据是没有刷新到硬盘的)
3.rollback 事务的回滚(一旦事务提交失败,我们需要将数据回滚到提交前的状态)
4.savepoint 保留点(为了支持回退部分事务处理,必须在事务处理块中放置合适的占位符,这样就可以回退到保留点) 创建占位符:savepoint sp01;
回退到占位符:rollback to sp01;
保留点会在rollback或者commit提交后自动释放
'''
事务的四大并发:
1.脏读(读未提交)
2.不可重复读(读已提交)
3.可重复读(幻读)
4.串行化
隔离等级:
1.读未提交 read uncommitted
其他事务可以读取事务已经修改的但未提交的数据,这种也称之为脏读(不正确的数据)
# 看了还未提交的数据 脏读
2.读已提交 read committed (大多数数据库默认的隔离级别)
事务a在读取一个数据,事务b也在读取这个数据并修改提交(其他事务不可见),那么a读取到的数据可能是不一致的,这种又称之为不可重复读
# 数据已经修改了 但是看到的是未修改前的数据 数据不一致
3.可重复读 repeatable read
可以解决脏读问题,但是出现幻读问题
一个事务在读取数据的时候,另一个事务在这段数据插入了新的数据并提交,当再次查看这段数据的时候,会产生幻行 # 查看数据,被插入新数据,再看数据出现了幻读现象(例如,刚看的时候是3条数据,刷新一下变5条了)
'''INNODB通过多版本并发控制(MVCC)和间隙锁策略解决此问题'''
4.串行化 serializable
强制事务串行,很少使用该等级
ps: 设置隔离等级:set transaction isolation level 级别
事务日志 :1.先将原始数据从磁盘中读入内存中来,修改数据的内存拷贝
2.提供再写入操作,恢复提交事务修改的页操作,用来保证事务的持久性
回滚行记录到某个特定版本,用来保证事务的原子性、一致性
3.先写日志,再写磁盘,只有日志写入成功,才算事务提交成功
4.在执行事务的过程中,使用顺序 l/O,效率比随机 I/O 快
5.定期将内存中修改的数据刷新到磁盘中
MVCC多版本并发控制
# 是MySQL中基于乐观锁理论实现隔离级别的方式,用于实现已提交读和可重复读隔离级别的实现,也称为多版本数据库
MVCC多版本并发控制中,读操作可以分为两类:
1.快照读(非锁定读) :读的是记录的可见版本,不用加锁,例如select查询就是快照读
2.当前读:读取的是记录的最新版本,并且返回当前读的记录 ,例如insert,delete 等读的都是最新数据
在已提交读隔离级别,每次select查询时都会生成新的快照读
在可重复读隔离级别,事务第一次select查询时会生成一个当前事务的全局快照读,并且只生成一次
# 多版本并发控制的思想就是保存数据的历史版本,通过对数据行的多个版本管理来实现数据库的并发控制。这样就可以通过比较版本号决定数据是否显示出来,读取数据的时候不需要加锁也可以保证事务的隔离效果。
InnoDB 会根据以下两个条件检查每行记录:
1.只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务 读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。
2.行的删除版本要么未定义,要么大于当前事务版本号。可以确保事务读取到的行,在事务开始之前未被删除。
只有符合上述两个条件的记录,才能返回作为查询结果。
存储过程
1.创建存储过程的语法结构:
create procedure 名字(参数1,参数2)
delimiter 临时结束符
begin
sql语句;
end 临时结束符
delimiter ; 修改回来
2.调用创建过程:
关键字 call 创建过程的名字()
3.参数:
in 只可以传参 不可以返回
out 可以返回
inout 可以传入 也可以被返回
eg:
create porcedure t1(
in id int,
out name str,
out res str # res 需要提前定义 set @res=10
)
delimiter &&
begin
select * from 表名 ;
set res=0; # 用来记录传入的参数是否执行
end &&
delimiter ;
调用:call t1(@res)
查看res: select @res
内置函数
只可以在MySQL里面使用!!
1.用 help 函数名 查看帮助信息
2.常见内置函数: 移除指定字符 Trim LTrim RTrim
大小写转换 Lower Upper
获取左右起始指定个数字符 Left Right
返回读音相似值(英文有效) Soundex
日期格式 date_format
其他日期相关函数 adddate 增加日期
addtime 增加时间
datediff 计算两个日期时间差
date_format相关操作:
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') # Aug 31 2022 11:45 PM
DATE_FORMAT(NOW(),'%m-%d-%Y') # 8-20-2022
DATE_FORMAT(NOW(),'%d %b %y') # 31 Aug 22
DATE_FORMAT(NOW(),'%d %b %Y %T:%f') # 31 Aug 2022 15:25:46.635
流程控制
if语法结构:
delimiter &&
create procedure 函数名()
begin
declare i int default 0;
if i=1 then
select 1;
elseif =2 then
select 2;
else
select 3
end if ;
end &&
delimiter ;
while语法结构:
delimiter &&
create procedure 函数名()
begin
declare num int;
set num =10;
while num<10 do
select num
set num =num+1
end while;
end &&
delimiter ;
索引
1.索引是为了加快数据的查找,在mysql中索引也称为’键‘,是存储引擎为了快速找到数据的一种数据结构
可以加快查询速度的键:primary key ,unique key ,index key(只能加快查询速度,没有其他功能)
# 索引的优缺点:
'''
优点:加快数据的查询,降低数据库的IO成本
通过索引列对数据进行排序,降低CPU的消耗
缺点:索引是一张表里面存放了主键和索引字段,也要占用内存空间
降低表的更新速度 ,更新表时,mysql不仅要保存数据,还要保存索引文件
'''
聚集索引:就是指表的主键 primary key
辅助索引: 就是除了主键外的其他索引,unique key 和index key,查找数据的时候都要经过主键才能查找数据
覆盖索引:在辅助索引中就找到了需要查询的数据
# 索引建立在表的列(字段)上 在where的字段后面加上索引才会加快查询速度
# 索引底层原理 (用的树)
二叉树概念 :就是有两个分支的树,树是一种数据结构(主要为了优化数据查询,用最短的时间查到想要查询的数据)
基于二叉树优化之后的树:
B树:又称B-树/多叉树,除了叶子节点外,其他节点只能有两个分叉,所有节点都可以存放完整的数据(每个数据有固定大小)
缺点:数据块是有固定大写,存储的数据不够多,存储的数据越多树的高度会越来越高,查找的时间会越来越长
B+树:只在叶子节点存放数据,其他节点存放主键或索引键,层级更少,在B树基础上为叶子节点增加链表指针,非叶子节点 作为叶子节点的索引
B*树:在b+树基础上为其他节点也增加通道,减少查询时间
慢查询优化
# 什么是慢查询 就是在日志中记录运行比较慢的SQL语句,是指mysql记录所有执行超过long_query_time参数设定的时间阈 值的SQL语句查询 可以找出执行较慢的sql语句进行优化,提高运行速度
sql语句执行慢的原因:偶尔慢 :db在刷新脏页,慢查询日志记录满了,内存不够,mysql关闭或者空闲的时候
一直慢:索引没有设计好,SQL语句没写好,mysql选错了索引
1.慢查询语法结构:explain sql语句
基本步骤:1.where 条件单表查询 先从数据最小的表开始查询 (配置慢查询日志)
2.explain 查看执行计划,是否与预期一致
3.order by limit排序一下,让排序的表优先查
4.加索引时参照建索引的原则
5.观察结果,超出预期从1开始继续分析
# sql不同的语句书写方式对于效率往往有本质的差别
常见索引扫描类型:
1.index :index和all的区别,index只遍历索引树
2.range :(一般到达这个级别即可) 索引扫描范围,只扫描带有between或者where子句中<,>查询
3.ref :使用非唯一索引扫描或者唯一索引前缀扫描 #(根据字段的前n个字符创建的索引)
4.eq_ref :类似于ref,区别就在于扫描的我唯一索引
5.const :当mysql对某部分进行优化,并转换为常量时,使用这些语句
6.system :当mysql对某部分进行优化,并转换为常量时,使用这些语句
7.null :MySQL在优化过程中分解语句,执行时甚至不用访问表或索引
常见的慢查询优化:
1.索引没有起到作用的情况(没有索引)
—使用like关键字查询,如果匹配的字符串第一个关键字符号为‘%’,那么索引不会起作用
—使用多列查询语句,一个索引包含了16个字段,只有索引使用了这些字段中的第一个字段,才会起作用
2.优化数据库的表结构
—将字段很多的表,拆解成多个表
—对于需要联合查询的表,可以建立中间表以提高查询效率
标签:语句,事务,查询,索引,提交,MySQL,数据
From: https://www.cnblogs.com/Hsummer/p/16608875.html