mysql8 索引、视图、事务、存储过程、触发器

一、视图

1、什么是视图

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

2、创建视图

create view class2student as \
SELECT  *  FROM class INNER JOIN student on cid = class_id;

Query OK, 0 rows affected

3、删除视图

drop view xxx;

视图的数据不要去修改，它来自于查询的表的结果，原表数据改动，视图自动跟着改动

二、触发器

1、在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

2、为啥使用触发器

触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码

3、创建触发器的语法

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

需要注意在书写sql代码的时候结束符是;

而整个触发器的结束也需要分号; 这就会出现语法冲突需要我们临时修改结束符号

delimiter $$

delimiter ;

该语法只在当前窗口有效

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
    ('egon','0755','ls -l /etc',NOW(),'yes'),
    ('egon','0755','cat /etc/passwd',NOW(),'no'),
    ('egon','0755','useradd xxx',NOW(),'no'),
    ('egon','0755','ps aux',NOW(),'yes');

# 查询errlog表记录
select * from errlog;

# 删除触发器
drop trigger tri_after_insert_cmd;

三、事务（重要）

1、什么是事务

mysql 事务是一组数据库操作的集合，这些操作要么全部执行成功，要么全部回滚，以确保数据库的一致性和完整性。

2、事务的作用

保证了对数据操作的'数据安全性'

3、事务具有以下四个特性， ACID 特性：

原子性（Atomicity）：原子不可分割，要么全部执行成功，要么全部回滚。
一致性（Consistency）：事务在执行前后，数据库必须保持一致的状态。
隔离性（Isolation）：并发执行的多个事务之间应该相互隔离，互不影响
持久性（Durability）：事务一旦完成，数据永久修改

在MySQL中，使用以下语句来定义和管理事务：

BEGIN 或 START TRANSACTION：标识一个事务的起点。
COMMIT：提交事务，将事务中的操作永久保存到数据库。
ROLLBACK：回滚事务，撤销事务中的所有操作，将数据库恢复到事务开始之前的状态。

在默认情况下，MySQL的自动提交模式是开启的，这意味着每个SQL语句都被视为一个单独的事务并立即执行和提交。如果需要使用事务，可以显式地启动事务并使用COMMIT或ROLLBACK来控制事务的提交或回滚。

4、案例

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

# 往表中插入数据 
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

# 回滚到上一个状态
# rollback;

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

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

使用python代码来实现代码逻辑

被监测的代码一旦发生异常，代码走except代码体，执行回滚，否则就提交，刷新数据到硬盘中。

try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='egon'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

5、事务的隔离级别设置：

读未提交（Read Uncommitted）：最低的隔离级别。事务可以看到其他事务未提交的数据变更，可能导致脏读（Dirty Read），即读取到未提交的数据。
读已提交（Read Committed）：在一个事务中，只能看到已经提交的数据变更。解决了脏读的问题，但可能会导致不可重复读（Non-repeatable Read），即在同一个事务中，多次读取同一行数据可能会得到不同的结果。
可重复读（Repeatable Read）：在一个事务中多次读取同一行数据时，保证返回的结果一致，不会受到其他事务的影响。但是，可能会出现幻读（Phantom Read），即在同一个事务中多次查询同一范围的数据时，结果集的行数可能会发生变化。
串行化（Serializable）：最高的隔离级别，确保并发事务之间的数据完全隔离，避免了脏读、不可重复读和幻读的问题。它通过对事务加锁来实现隔离，从而使得每个事务依次执行。

默认情况下，MySQL使用的事务隔离级别是可重复读（Repeatable Read）。可以使用以下语句来设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL <隔离级别>;

# 例如，设置事务隔离级别为读已提交（Read Committed）：
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

需要注意的是，更高的隔离级别通常会导致更多的锁和资源消耗，降低系统的并发性能。因此，在选择隔离级别时，需要权衡数据的一致性要求和系统性能之间的关系。

四、存储过程 procedure（类似py的自定义函数）

1、什么是存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数

2、基本使用

delimiter $$
create procedure p1()  # procedure 是存储过程关键字
begin 
	select * from user;
end $$
delimiter ;

# 调用
call p1()；

3、创建存储过程

存储过程类似py函数，也有定义阶段、形参、实参数

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

调用存储过程

set @res=10;  定义  res=10

call p2(1,5,@res)  调用
+------------+
| tname      |
+------------+
| 刘海燕老师 |
| 朱云海老师 |
+------------+

#select @res  查看
+------+
| @res |
+------+
| 0    |
+------+

根据上述结果可以看出存储过程的sql语句被执行，筛选出符合条件的老师，@res也被重置

4、py使用存储过程

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

cursor.callproc() 方法来调用该存储过程

cursor.callproc() 方法的语法如下：

cursor.callproc(procname, args=())

在python程序中调用

pymysql链接mysql
....

# 调用存储过程
cursor.callproc('p1',(2,4,10))  # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')

存储过程与事务使用举例(了解)

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;


  DECLARE exit handler for sqlwarning
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION;
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT;

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功


END //
delimiter ;

五、函数

注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！

而存储过程是包含sql语句，可以使用pymysql调用

1、date_format() 函数的使用

DATE_FORMAT(date, format)

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

要统计出某一年的某月写了几篇博客

+----+-------+---------------------+
| id | NAME  | sub_time            |
+----+-------+---------------------+
| 1  | 第1篇 | 2015-03-01 11:31:21 |
| 2  | 第2篇 | 2015-03-11 16:31:21 |
| 3  | 第3篇 | 2016-07-01 10:21:31 |
| 4  | 第4篇 | 2016-07-22 09:23:21 |
| 5  | 第5篇 | 2016-07-23 10:11:11 |
| 6  | 第6篇 | 2016-07-25 11:21:31 |
| 7  | 第7篇 | 2017-03-01 15:33:21 |
| 8  | 第8篇 | 2017-03-01 17:32:21 |
| 9  | 第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_format(sub_time,'%Y-%m') | count(id) |
+-------------------------------+-----------+
| 2015-03                       | 2         |
| 2016-07                       | 4         |
| 2017-03                       | 3         |
+-------------------------------+-----------+

六、流程控制

1、先定义一个存储过程，即sql中可以调用的函数

# 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 ;

2、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、数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作

索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

primary key
unique key
index key

注意：上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询

2、索引的本质

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，

也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

3、索引的影响

在表中有大量数据的前提下，创建索引速度会很慢

以后实际添加索引的时候，尽量在空表的时候添加，在创建表的时候就添加索引，此时添加索引是最快的
如果表中数据已经有了，还需要添加索引，也可以，只不过创建索引的速度会很慢，不建议这样做

在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低

4、如何添加索引？到底给哪些字段加索引呢?

没有固定答案，具体给哪个字段加索引，要看你实际的查询条件

select * from user where name='' and password='';

索引的使用其实是需要大量的工作经验，才能正确的判断出

'''不要一创建表就加索引，在一张表中，最多最多不要超过15个索引，索引越多，性能就会下降'''

如果数据量比较小，不需要加索引，100w一下一般不用加，mysql针对于1000w一下的数据，性能不会下降太多.

以后加索引的时候，尽量给字段中存的是数字的列加,我们使用主键查询速度很快

select * from user where name = ''
select * from user where id = '' # 主键查询的更快一些

5、b+树索引

在MySQL 8中，创建索引时默认使用的是B+树索引。B+树是MySQL最常用的索引类型，它提供了快速的查询、范围查询和排序能力。

当你使用CREATE INDEX语句创建索引时，如果没有显式指定索引类型，MySQL会默认使用B+树索引。B+树索引适用于大多数场景，并且在大多数情况下能够提供高效的查询性能。

然而，MySQL 8也提供了其他类型的索引，例如哈希索引和全文索引。你可以根据具体的需求和场景选择不同的索引类型来优化查询性能。

如果你想要创建特定类型的索引，可以使用CREATE INDEX语句的USING子句来指定索引类型。例如，创建哈希索引可以使用以下语法：

CREATE INDEX index_name
USING HASH
ON table_name (column_name);

B+树索引具有以下特点：

平衡性：B+树是一种平衡树，即树的所有叶子节点位于同一层级，这使得在树上进行搜索和遍历更加高效。
有序性：B+树的所有叶子节点按照键值的大小顺序排序，这使得范围查询和排序操作更加高效。
多叉性：B+树的每个节点可以存储多个键值和对应的数据地址，这样可以减少磁盘访问次数，提高查询效率。
磁盘友好性：B+树的内部节点仅存储键值和子节点的引用，而不存储数据本身，这使得每个节点的大小更小，可以在磁盘上存储更多的节点，减少磁盘IO操作。
支持范围查询：由于B+树的有序性，可以很方便地执行范围查询操作，例如查找某个范围内的所有值。

只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低次数越少

一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项。

6、给一张表里面的什么字段字段建立索引能够降低树的层级高度

>>> 主键id字段

聚集索引(primary key)

聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。

myisam在建表的时候对应到硬盘有几个文件(三个)？

innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引

特点：

叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

select name from user where name='jack';

上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select age from user where name='jack';

上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

7、测试索引

准备数据，创建存储过程，实现批量插入100万条记录

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

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare  i int default  1;
    while(i<1000000)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=1000; # 0.048s
# 避免打印带来的时间损耗
select count(id) from s2 where id = 1000000; # 0.048s
select count(id) from s2 where id = 1;  # 0.050s

# 给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 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字段一剑封喉

posted @ 2023-07-14 15:20 凡人半睁眼阅读(31) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

海棠未雨，梨花先雪，一半春休

想看山海，早也去，晚也去，一个人也去

mysql8 索引、视图、事务、存储过程、触发器

一、视图

二、触发器

三、事务（重要）

四、存储过程 procedure（类似py的自定义函数）

五、函数

六、流程控制

七、索引（重要）

公告

海棠未雨，梨花先雪，一半春休

想看山海，早也去，晚也去 ，一个人也去

mysql8 索引、视图、事务、存储过程、触发器

一、视图

二、触发器

三、事务（重要）

四、存储过程 procedure（类似py的自定义函数）

五、函数

六、流程控制

七、索引（重要）

公告

想看山海，早也去，晚也去，一个人也去