【MySQL】第6回 MySQL各种功能

目录

1. 视图(view)

1.1 视图概念

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据

1.2 原由

SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作,如果这张虚拟表需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后就称之为"视图"(本质就是一张虚拟表)

1.3 语法结构与操作

# 创建视图基本语法（SQL语句为多表查询语句）
create view 视图名 as SQL语句;
# 修改视图
alter view 视图名 as select 语句
# 显示视图创建情况
show create view 视图名;
# 查看视图
show tables；
# 删除视图
drop view 视图名[,视图名…];
# 重命名视图
Rename table 视图名 to 新视图名;

1.4 总结

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

2. 触发器(trigger)

2.1 触发器概念

触发器是与表有关的数据库对象，针对表数据的增、改、删自动触发的功能(增前、增后、改前、改后、删前、删后)

2.2 语法结构与操作

create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
   sql语句
end;
# before/after：指定是在之前还是之后触发
# insert/update/delete：指定当表发生什么类型(insert/update/delete)的操作时会触发
# 查看触发器
show triggers;
# 删除触发器
drop triggers 触发器的名字

2.3 delimiter 修改SQL语句默认的结束符

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

delimiter $$   # 第一次为将mysql默认的结束符由;换成$$
编写需要用到分号的各种语句
delimiter ;    # 第二次为把修改为$$的结束符修改成原来的分号

2.4 代码案例

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对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
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;

2.5 注意事项

  1. 触发器中不能对本表进行insert，update，delete操作，以免递归循环触发
  2. 触发器是针对每一行的数据，对增删改非常频繁的表上切记不要使用触发器，因为非常消耗资源。
  3. 尽量少使用 触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert 的效率就非常低了。

3. 事务(transaction)

3.1 事务概念

是将⼀组操作封装成⼀个执行单元（封装到⼀起），这⼀个执⾏单元要么⼀起执行成功，要么⼀起失败，不会出现执行“⼀半”的情况。

3.2 事务(ACID)四大性质

  1. 原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  2. 一致性(Consistency)：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  3. 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  4. 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.3 代码案例

1.mysql操作

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;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

2.python代码角度，应该实现的伪代码逻辑

try:
    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元
except 异常:
    rollback;
else:
    commit;

3.4 事务关键词

  1. MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
  2. 事务(transaction)、回退(rollback)、提交(commit)、保留点(savepoint)
  3. 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
  4. 创建占位符可以使用savepoint
    savepoint sp01;
  5. 回退到占位符地址
    rollback to sp01;
  6. 保留点在执行rollback或者commit之后自动释放

3.5 事务4种隔离级别

  1. 设置当前事务的隔离级别
    set transaction isolation level 级别
  2. read uncommitted(未提交读)：事务中的修改即使没有，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
  3. reed committed(提交读)：大多数数据库系统默认的隔离级别，一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做”不可重复读“
  4. repeatable read(可重复读)：能够解决"脏读"问题，但是无法解决"幻读"，所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(mvcc)及间隙锁策略解决该问题
  5. serializable(可串行读)：强制事务串行执行，很少使用该级别
  6. 图列

3.6 事务日志可以帮助提高事务的效率

  1. 存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  2. 事务日志采用的是追加方式因此写日志操作时磁盘上一小块区域内的顺序IO而不像随机IO需要操作多个地方磁头所以采用事务日志的方式相对来说要快的多
  3. 事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

3.7 MVCC多版本并发控制

  1. MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新，serializable:所有的行都加锁)
  2. InnoDB的MVCC通过在每个记录后面保存两个隐藏的列来实现MVCC
    1. 一个列保存了行的创建时间
    2. 一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
  3. 每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询的每行记录版本号进行比较
  4. 由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来。
    1. 当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
    2. 当前事务id要小于delete_version值，这表示事务开始之后这行记录才被删除

4. 存储过程(procedure)

4.1 存储过程概念

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。储存过程思想上很简单，就是数据库SQL语言层面的代码封装与重用

4.2 语法结构与操作

create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin 
     SQL语句；
end;
# in:该类型参数作为输入，也就是需要调用时传入值
# out:该类型参数作为输出，也就是该参数可以作为返回值
# inout:即可以作为输入参数，也可以作为输出参数 

# 查看存储过程
show create procedure 存储过程名字;
# 查看所有存储过程
show procedure status;
# 删除储存过程
drop procedure 存储过程名字;

4.3 代码案例

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;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;

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

4.4 存储过程的优点

  1. 封装性
  2. 可增强SQL语句的功能和灵活性
  3. 可减少网络流量
  4. 高性能
  5. 提高数据库的安全性和数据的完整性

5. 内置函数

5.1 分类

  1. 从功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、回去MySQL信息函数、聚合函数等。
  2. 从入参个数角度来说，又分单行函数和多行函数

5.2 通过help 函数名 查看帮助信息

5.3 移除指定字符

Trim、LTrim、RTrim

5.4 大小写

Lower、Upper

5.5 获取左右起始指定个数字符

Left、Right

5.6 返回读音相似值(对英文效果)

Soundex

5.7 日期格式:date_format

current_date():当前日期
current_time():当前时间
current_timestamp():当前时间戳
date(datetime):返回datetime参数的日期部分
date_add(date, interval d_value_type):在date中添加日期或时间。interval后的数值单位可以是year/day/minute/second
date_sub(date, interval d_value_type): 在date中减去日期或时间。interval后的数值单位可以是year/day/minute/second
datediff(date1, date2): 计算两个日期的时间差，单位是天
now(): 当前日期时间

6. 流程控制

6.1 流程控制概念

实际开发中，解决复杂的问题是需要执行多个sql语句的，流程控制语句的作用就是用来控制存储过程中SQL语句的执行顺序。流程分为顺序结构，分支结构和循环结构，对应MySQL来说，流程控制语句主要有3类。

6.2 代码案例

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;


# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

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

END //
delimiter ;

7. 索引

7.1 索引概念

索引是帮助MySql高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySql的性能优化问题。

7.2 性能理解

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

7.3 索引在MySQL中也叫做“键”

  1. 索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
  2. 三种健
    1. primary key
    2. unique key
    3. index key
  3. 上述的三种键在数据查询的时候使用都可以加快查询的速度
    1. primary key, unique key 除了可以加快查询速度
    2. index key只能加快数据查询，本身没有任何的额外限制

7.3 索引优缺点

优点:大大加快数据查询速度
缺点:维护索引需要耗费数据库资源
索引需要占用磁盘空间
当队标的数据进行增删改的时候，因为要维护索引，速度会受到影响

7.3 索引类型介绍

  1. BTREE:B+树索引
  2. HASH：HASH索引
  3. FULLTEXT：全文索引
  4. RTREE：R树索引

7.4 索引语法

# 创建索引
alter table test add index index_name(name);	
#创建索引
create index index_name on test(name);	
#查看索引	
desc table;	
#查看索引	
show index from table;	
#删除索引	
alter table test drop key index_name;		
#添加唯一性索引	
alter table student add unique key uni_xxx(xxx);	
#查看表中数据行数
select count(*) from city;
#查看去重数据行数
select count(distinct name) from city;

7.5 树

  1. 树：是一种数据结构 主要用于优化数据查询的操作
  2. 二叉树:两个分支
    B树：除了叶子节点可以有多个分支 其他节点最多只能两个分支，所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
    B+树：只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
    B*树：在树节点添加了通往其他节点的通道 减少查询次数

7.6 二叉树

二叉树是每个结点最多有两个子树的树结构。即“左子树” 和“右子树”。本身是有序树

  1. 二叉树
  2. 满二叉树
  3. 完全二叉树
  4. 平衡二叉树
    解决二叉树退化成链表而引入了平衡二叉树，特点是尽量保证两边平衡，高度差<=1，平衡二叉树要么是一棵空树，要么保证左右子树的高度之差不大于 1，平衡二叉树要么是一棵空树，要么保证左右子树的高度之差不大于 1
    子树也必须是一颗平衡二叉树

7.7 慢查询优化

  1. explain命令使用方法
    explain SQL语句
  2. explain命令应用
    1. 全表扫描：在explain语句结果中type为ALL
    2. 什么时候出现全表扫描?
      1. 业务确实要获取所有数据
      2. 不走索引导致的全表扫描
        1. 没索引
        2. 索引创建有问题
        3. 语句有问题
    3. 索引扫描
      1. index
      2. range
      3. ref
      4. const
      5. eq_ref
      6. system
      7. null
  3. 生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

7.8 代码实例

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

# 联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快
posted @ 2022-08-19 14:56  |相得益张|  阅读(56)  评论(0)  编辑  收藏  举报