MySQL补充，视图，触发器，事务，MVCC多版本并发控制，内置函数，流程控制，索引，慢查询优化，测试索引，联合索引

一：视图

1.视图的由来

当SQL语句的执行结果是一张表的时候，我们要基于这张表进行频繁操作的时候，需要将这张表保存起来，保存起来的表就是"视图"

2.创建视图的语法

create view 视图名  as SQL语句
1

eg:
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
1
2
3

3.视图的特点：

• 1.在硬盘中，视图只有表结构文件，没有表数据文件
• 2.视图通常是用来查询的，并且视图中的数据是无法被修改的

4.总结：

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

二：触发器

1.什么是触发器？

• 针对数据的增删改自动触发的功能
• 增前，增后，删前，删后，改前，改后

2.语法

1.语法

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

2.书写注意事项

• 触发器内部的SQL语句会用到分号 但是分号又是SQL语句默认的结束符，所以需要临时修改SQL语句的结束符

delimiter 新的结束符
触发器相关语句
delimiter ;
1
2
3

3.案例

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

三：事务(重点)

1.定义：

事务时一组原子性的SQL查询，或者说是一个独立的工作单元。事务内的语句要么全部执行成功，要么全部执行失败

2.事务的ACID4大特征

• 1.原子性：事务最小的工作单位，要么全成功，要么全失败
• 2.一致性：事务开始和结束之后，数据库的完整性不被破坏
• 3.隔离性：不同事务之间互不影响
  • 四种隔离级别：RU(未提交读)，RC(已提交读)，RR(可重复读), SERIALIZABLE(串行化)
• 4.持久性：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失

3.四种隔离级别

1.未提交读**（READ UNCOMMITTED/RU）**：

脏读：一个事务读取到另一个事务未提交的数据

2.已提交读**（READ COMMITTED/RC）**：

不可重复读：一个事务因读取到另一个事务已提交的update，导致对同一条记录读取两次以上的结果不一致。

3.可重复读**（REPEATABLE READ/RR）**：

幻读：一个事务因读取到另一个事务已提交的insert数据，导致对同一张表读取两次以上的结果不一致。

4.串行化**（SERIALIZABLE）**：

将并发操作编程串行

4.事务相关的命令

• 1.开启事务：start transaction;

• 2.回滚到上一个状态：rollback;

• 3.确认操作：commit; 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘

• 4.保留点：savepoint；

• 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
  创建占位符可以使用savepoint
  
  savepoint sp01;
  回退到占位符地址
  rollback to sp01;
  
  # 保留点在执行rollback或者commit之后自动释放
  1
  2
  3
  4
  5
  6
  7
  8

5.事务存储引擎

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

InnoDB支持所有隔离级别 set transaction isolation level 级别

6.事务日志

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

7.案例

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操作"""

# 站在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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

四：MVCC多版本并发控制

1.适用场景

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.结论：

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

五：存储过程

类似于python中的自定义函数

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


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

"""
查看存储过程具体信息
	show create procedure pro1;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro1;
"""
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

六：内置函数

"ps:可以通过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	计算两个日期差值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

七：流程控制

# 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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

八：索引

1.定义：

MySql官方对索引的定义为：索引是帮助MySql高效获取数据的数据结构。

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

2.索引的优势劣势

优势

• 类似于书籍的目录索引,提高数据检索的效率,降低数据库的IO成本。
• 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。

劣势

• 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。
• 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行 INSERT、 UPDATE、 DELETE。因为更新表时,MSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

3.键

1.分类

• primary key
• unique key
• index key

2.各自的特点

• primary key、unique key除了可以加快数据查询还有额外的限制
• index key只能加快数据查询 本身没有任何的额外限制

九：索引的底层结构

1.树：

是一种数据结构 主要用于优化数据查询的操作

2.二叉树：

1.二叉数的特点：

• 一个节点只能有两个子节点，也就是一个节点度不能超过2

• 左子节点 小于 本节点，右子节点大于等于 本节点

2.分类：B树(B-树)、B+树、B*树

B树：除了叶子节点可以有多个分支 其他节点最多只能两个分支 所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)

B-Tree特点:

1. 叶节点具有相同的深度。
2. 节点中的元素从左向右递增排序
3. 所有的元素不重复

B+树：只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)

B+Tree特点：

1. 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
2. 叶子节点包含所有索引字段
3. 叶子节点用双向指针相连，提高区间访问性

B*树：在树节点添加了通往其他节点的通道 减少查询次数

B*树的特点：

    1. B树定义了非叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3代替B+树的1/2）；
    2.  B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点中增加新结点的指针；     B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它不需要指向兄弟的指针；
1
2

十：慢查询优化

1.explain语法详情

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

2.常见的索引扫描类型

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

从上到下，性能从最差到最好，我们认为至少要达到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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 表没有任何索引的情况下
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字段一剑封喉 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

十二：联合索引

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';  # 速度变快
1
2
3
4
5
6
7
8
9
10
11
12