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 凡人半睁眼阅读(40) 评论(0) 编辑收藏举报

刷新页面返回顶部

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

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

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

一、视图

二、触发器

三、事务（重要）

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

五、函数

六、流程控制

七、索引（重要）

公告

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

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

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

一、视图

二、触发器

三、事务（重要）

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

五、函数

六、流程控制

七、索引（重要）

公告

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