视图、触发器、存储过程、事务(掌握)、内置函数、流程控制、循环结构、索引与慢查询优化

最新推荐文章于 2024-07-30 19:26:47 发布

nana_cx

最新推荐文章于 2024-07-30 19:26:47 发布

阅读量197

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/m0_56371364/article/details/126444888

版权

视图

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

语法结构

create view 视图名 as SQL语句

mysql> show tables;
+--------------+
| Tables_in_d7 |
+--------------+
| class        |
| course       |
| score        |
| student      |
| studentinfo  |
| teacher      |
+--------------+

mysql> create view studentinfo as select * from student inner join class on student.class_id = class.cid;

mysql> create view studentinfo as select * from student inner join class on student.class_id = class.cid;

特点

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

mysql> delete from studentinfo;
ERROR 1395 (HY000): Can not delete from join view 'd7.studentinfo'

ps:视图在平常能少用就少用

触发器

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

语法结构

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

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

案例

可以用SQL语句：show triggers; 来查看该库中的触发器有哪些

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;

事务

事务(transaction)就是开启事务之后，执行sql语句的时候，只会出现两种情况，要么全部执行完毕你，要么在执行的时候出错就回退所有操作，什么都不做了

MySQL提供两种事物型存储引擎InnoDB 和 NDB cluster及第三方XtraDB、PBXT

事务的特性(ACID)

原子性(A)：事物是一个不可分割的工作单位，要么同时成功，要么同时失败

一致性©：操作的结果是一致的。eg:转账：一方数量减少，另一方数量增加相应的

隔离性(I)：多个事物之间相互隔离的，互补干扰

持久性(D)：一旦事务提交，它对数据库的改变就是永久的

语法结构

start transaction;

案例

rollback是回滚到上一个状态；

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

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

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;

站在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;

关键词汇

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

事物(transaction)、回退(rollback)、提交(commit)、保留点(savepoint)

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

创建占位符使用savepoint:savepoint sp01
回退到占位符地址：rollback to sp01

ps:保留点在执行rollback或者commit之后自动释放

隔离级别

在SQL标椎中定义了四种隔离级别，每种级别都规定了一个事务中说做的修改InnoDB支持所有隔离级别。隔离的级别有read uncommitted(未提交读)、read committed(提交读)、repeatable read(可重复读)、serializable(可串行读)

语法格式

set transaction isolation level 级别

1.read uncommitted(未提交读)：事务中的修改及时没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也叫做‘脏读’

2.read committed(提交读)：这是大多数数据库系统默认的隔离级别，一个事务从开始知道提交之前所做的任何修改对其他事务都是不可见的，这种级别也叫‘不可重复读’

3.repeatable read(可重复读)：这是MySQL默认隔离级别，能够解决‘脏读’的问题，但是无法解决‘幻读’，幻读就是当某个事务在读取某个范围的记录是另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题

4.serializable(可串行读)：强制事务串行执行，很少使用该级别

隔离级别	脏读	不可复读	幻读
未提交读	可能	可能	可能
提交读	不可能	可能	可能
可重复读	不可能	不可能	对InnoDB不可能
可串行读	不可能	不可能	不可能

事务日志

事务日志可以帮助提高事务的效率，存储引擎在修改表的数据时，只需要修改其内存，拷贝之后，再把该修改记录到持久在硬盘上的事务日志中，而不是每次都将修改的数据本身持久到磁盘

1.事务的一致性和持久性由事务的 redo 日志和undo 日志来保证。
	redo log 是重做日志，提供再写入操作，实现事务的持久性；
	undo log 是回滚日志，提供回滚操作，保证事务的一致性。

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

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

MVCC多版本并发控制

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

1.InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC，一个例保存了行的创建时间，一个列保存了行的过期时间(或删除时间)，本质是系统版本号
ps:每开始一个新的事务，版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来与查询到的每行记录版本号进行比较

eg:
1.1刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason				  1			
可以看见，我们在username列插入了jason这条数据，在create_version这列存储了1,1是这次插入操作的事务id
1.2然后我们将jason修改为jason01，实际存储是这样的
	username		create_version		delete_version
    jason				1					2
    jason01				2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
1.3 当我们删除数据的时候，实际存储是这样的
	username		create_version		delete_version
    jason				1					2
    jason01				2                     3

2.但我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
	2.1 当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了
	2.3 当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。

存储过程

类似于python中自定义函数

语法结构

delimiter 临时结束符
create procedure 名字(参数,参数)
begin
	sql语句;
end 临时结束符
delimiter ;

call 名字()  # 调用

案例

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  查看

补充知识

1.查看存储过程具体信息:show create procedure pro1;
2.查看所有存储过程:show procedure status;
3.删除存储过程：drop procedure pro1;

内置函数

我们可以借助help 函数名来查看内置函数的用法

移除指定字符-- Trim 、LTrim、RTrim

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT LTRIM('  barbar');
        -> 'barbar'
mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'

大小写转换–Lower、Upper

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'
mysql> SELECT UPPER('Hej');
        -> 'HEJ'

获取左右起始指定个数字符–Left、Right

mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'

返回读音相似值(对英文) --Soundex

mysql> insert into t2 values(2,'jason'),(1,'json');
mysql> insert into t2 values(2,'jason'),(1,'json');
mysql> select * from t2 where soundex(name)=soundex('json');
+------+-------+
| id   | name  |
+------+-------+
|    2 | jason |
|    1 | json  |
+------+-------+

日期格式

date_format

在MySQL中表示时间格式尽量采用2022-11-11形式

mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'

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');

Date

mysql> select name, date(sub_time) from blog where date(sub_time) = '2015-03-01';
+---------+----------------+
| name    | date(sub_time) |
+---------+----------------+
| 第1篇   | 2015-03-01     |
+---------+----------------+

year

mysql> select name, sub_time from blog  where Year(sub_time)=2016 AND Month(sub_time)=07;
+---------+---------------------+
| name    | sub_time            |
+---------+---------------------+
| 第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 |
+---------+---------------------+

adddate

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
        -> '2008-02-02'

addtime

ADDTIME(expr1,expr2)

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'

datediff

DATEDIFF(expr1,expr2)

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31

流程控制

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 ;

索引

索引在MySQL中也叫做‘键’，是存储引擎用于快速找到记录的一种数据结构。它就好比一本书的目录，他能够更快的找到我们想要的内容，让获取的数据更有目的性，从而提高数据库检索数据的性能

1.primary key、unique key、index key三中间在数据查询的时候使用都可以加快查询的速度
2.primary key、unique key 除了可以加快数据的查询，还有一些额外的限制(唯一、非空)
3.index key只能加快数据的查询，本身没有任何的限制
4.真正理解索引加快数据查询的含义：索引的存在可以加快数据的查询，但是会减慢数据的增删

索引相关概念

1.聚集索引：其实就是值得是主键(主目录)
2.辅助索引:就是用unique key、index key 建立索引(副目录)
		通过辅助索引，找到找到主键值
4.覆盖索引：在辅助索引中就找到了我们想要的数据
		eg:select name from user where name ='jason';
4.非覆盖索引：通过辅助索引查找其他字段信息
		eg :select age from user where name = 'jason'

索引底层原理

索引的底层原理其实就是数据结构，主要用于优化数据的查询操作

1.二叉树：两个分支
2.B树(B-树):除了叶子节点可以有很多分支，其他节点最多只能两个分支，所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
3.B+树:只有叶子存放真正的数据，其他节点只存之间值(辅助索引值)
4.B*树:在数节点添加了通往其他节点的通道，减少查询的次数

3慢查询优化

explain命令的应用是查询数据的方式

常见的索引扫描的类型

all:全扫描
index:Full Index Scan，index与ALL区别为index类型只遍历索引树。
range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
	eg:explain select * from city where population>30000000;
ref:使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
eq_ref:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
const:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
system:当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
null:MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

explain SQL语句

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

测试索引

数据准备

#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';  # 速度变快