# MySQL学习日记

qq_65348015

已于 2023-07-20 10:11:29 修改

阅读量54

点赞数 1

文章标签： mysql 学习 android

于 2023-07-20 10:02:30 首次发布

本文链接：https://blog.csdn.net/qq_65348015/article/details/131783582

版权

根据表查询创建表的方式

show create table  t_user;

### 查询结果展示
CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `power` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SQL性能分析—查询执行频次

可以查看哪种sql执行的频率最多，然后针对它做性能优化

show global status like 'com_______';

在这里插入图片描述

慢查询日志

慢查询日志记录了所有SQL执行时间超过了指定参数(long_query_time,单位：秒，默认10s)的所有SQL语句的日志。
查看MYSQL是否开启慢查询日志

SHOW VARIABLES LIKE  '%slow_query_log%';

在这里插入图片描述
ON就是已经开启，OFF就是没有开启

查看设置执行时间超过多少秒为慢查询SQL

SHOW VARIABLES LIKE 'long_query_time%';
或者
show global variables like 'long_query_time';

在这里插入图片描述

开启mysql的慢查询日志
一、永久开启（数据库服务重启后不失效）
修改配置文件my.cnf，[mysqld]下增加或修改参数如下：

[mysqld] 
slow_query_log=1 #开启慢查询
slow_query_log_file=/var/lib/mysql/localhost-slow.log #指定慢查询日志
long_query_time=3 #设定SQL执行时间超过3s为慢查询
log_output=FILE

slow_query_log_file参数用于指定慢查询日志文件的存放路径，如果没有指定参数slow_query_log_file。系统默认会给一个缺省的文件(host_name)-slow.log

重启mysql服务，设置生效
二、临时开启（数据库服务重启后失效）
开启慢查询

set global slow_query_log=1

在这里插入图片描述
设定慢查询阈值

set global long_query_time=3;

在这里插入图片描述

性能优化之profile

假如我们设定了慢查询阈值为3s,有的sql执行时间可能为2.999s，它的性能很差但是不会记录在慢查询日志当中，所以profile登场了，show profile可以帮助我们了解sql执行在哪里耗费了时间，帮助我们做性能优化。

查看profile是否开启

 select @@profiling;

在这里插入图片描述
0就是没有开启，1就是已经开启，默认没有开启

开启profile

set profiling =1;

在这里插入图片描述
查看所有SQL的执行时间

show profiles;

在这里插入图片描述
查看某一条SQL的执行时间情况

show profile for query 9;

在这里插入图片描述

存储过程

概述
如果在实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现的时候，那么我们就可以将这组复杂的SQL语句集提前编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。

存储过程：（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是很有好处的。

就是数据库 SQL 语言层面的代码封装与重用。

在这里插入图片描述
存储过程就类似于Java中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。

IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

MySQL存储过程的定义

创建

CREATE PROCEDURE 存储过程名(参数)
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END;
### 例如：	
create procedure p1()
BEGIN
 select count(*) from emp;
end;

调用

call p1();

查看
在这里插入图片描述
删除

drop procedure 存储过程名字
##例如：
drop PROCEDURE p1;

系统变量：由MYSQL提供，属于服务层，主要分为全局变量(global)与会话变量(session)

会话变量：只在当前会话有效，其他会话无效—会话：指的是控制台Console
系统变量：在所有会话都有效

查看系统变量

show [session/global] variables;
show [session/global] variables like '____';
select @@[session/global].变量名;
### 例如
show session variables;
show session variables like 'auth%';
select  @@session.autocommit ;

设置系统变量

set @@[session/global].变量名=值
###例如
set  @@session.autocommit=0;

如果不指定系统变量是session还是global,默认是session,同时系统变量的设定是临时的，如果需要永久设定，需要在配置文件中修改

用户变量

定义
用户自己定义的变量，只在当前会话有效
赋值

set @myname:='张三';
select count(*) into @mycount from emp; #从表中查出数据注入到@mycount中

使用

select @myname;
select @mycount;

局部变量

在begin…end之间定义的变量，只在当前存储过程中有效

create procedure p2()
begin
        declare mycount int default 0; ##局部变量的定义，变量的类型：int varchar char....
		select count(*) into mycount from emp;## 局部变量的赋值
		select mycount; ##查询局部变量
		
end;

call p2(); ## 存储过程的调用

if…else的使用

create procedure p4()
		begin
				declare score int default 58;
				declare result varchar(10);
				IF score>=60 THEN
				   set result:='及格';
			  elseif score >=70 then
				   set result:='良好';
				else
				   set result:='优秀';
			  END IF;
			select result;
		end;
call p4();	## 函数调用

参数

参数分为IN、OUT、INOUT类型三种类型。

IN类型的参数表示接受调用者传入的数据；
OUT类型的参数表示向调用者返回数据；
INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

定义格式：参数类型变量名变量类型

create procedure p5(in score int,out result varchar(10))
		begin
				IF score>=60 THEN
				   set result:='及格';
			  elseif score >=70 then
				   set result:='良好';
				else
				   set result:='优秀';
			  END IF;
		end;
		
## 函数调用
call p5(85,@result);	#@result是用户变量
select @result;

定义一个条件控制语句 CASE

case是另一个条件判断的语句，类似于编程语言中的 choose、when语法。MySQL 中的 case语句有两种语法格式。
第一种写法

create procedure p1(in num int)
begin 
    case #条件开始
	    when num<0 then 
		    select '负数';
		  when num>=0 then
		    select '正数';
		  else
		    select '不是负数也不是正数';
		end case; #条件结束
end;
## 函数调用
call p1(10);

第二种写法

create procedure p2(in num int)
begin 
    case num #条件开始
	    when 0 then 
		    select 'false';
		  when 1 then
		    select 'true';
		  else
		    select '不是0也不是1';
		end case; #条件结束
end;
## 函数调用
call p2(1);

定义一个循环语句 while

drop PROCEDURE if EXISTS p2;
create procedure p2(in num int,out total int)
begin
    set total:=0;
   while num<10 do
	   set num:=num+1;
	   set total:=total+num;
		end while;
end;
## 函数调用
call p2(0,@total);
select @total;

定义一个循环语句 LOOP

循环语句，用来重复执行某些语句。

执行过程中可使用== LEAVE语句或者ITEREATE==来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于Java中的break，用来终止循环；
ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

drop PROCEDURE if EXISTS p2;
create procedure p2(in num int,out total int)
begin
      set total:=0;
			demo_total:loop
			  if num>=10 then
			     LEAVE demo_total;
			  elseif num%2=1 then		
	         set num:=num+1;			
				   ITERATE demo_total;
			  end if;
	      	set total:=total+num;	
					set num:=num+1;
			 end loop demo_total;	   
end;
## 函数调用
call p2(0,@total);
select @total;

定义一个repeat

在这里插入图片描述

drop PROCEDURE if EXISTS p2;
create procedure p2(in num int,out total int)
begin
      set total:=0;
			REPEAT
				set total:=total+num;
				set num:=num-1;
			UNTIL num=0
			END REPEAT;
  
end;
## 函数调用
call p2(10,@total);
select @total;

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器的类型
在这里插入图片描述

注意：
不能创建具有相同名字的触发器。
1. 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器（比如afterinsert插入之后有一个触发器那么就不能再有触发器是after的了，但是before insert是可以的）。
2. 因此，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。

语法

创建

create trigger 触发器名 [before|after]  insert/update/delete on 表名 for each row --行级触发器
begin
    ........
end;

查看

show triggers;

删除

drop trigger 触发器名

student表与class表的初始状态
在这里插入图片描述

①创建一个insert事件触发器

创建触发器

drop trigger if EXISTS student_class;
create trigger  student_class after insert on student for each row
begin
  update class set count=count+1 where class.id=new.class_id;
end;

执行insert操作

insert into student values(1,'小米',101,'N');
insert into student values(2,'小华',101,'F');
insert into student values(3,'小红',102,'F');
insert into student values(4,'小白',102,'N');

class表中的字段也跟着更新
在这里插入图片描述

②创建一个delete 事件触发器

create trigger D_S_C after delete on student for each row
begin 
  
	update class set count=count-1 where class.id=old.class_id;
end;

删除一条记录

delete from student where name='小华';

class表也跟着更新
在这里插入图片描述

InnoDB存储引擎详细介绍

逻辑存储结构
架构
事务原理
MVCC

逻辑存储结构

在这里插入图片描述

MVCC的基本介绍

当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select…lock in share mode(共享锁)，select …for update、update、insert、delete(排他锁)都是一种当前读。

快照读
简单的select(不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。.

Read Committed**:每次select，都生成一个快照读。
Repeatable Read**:开启事务后第一个select语句才是快照读的地方。
Serializable**:快照读会退化为当前读。

MVCC
全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView.

MVCC的实现原理

隐藏字段

当我们创建表时，InnoDB会为我们的数据库表添加2~3个隐藏字段
在这里插入图片描述

undo log

undo log回滚日志，在insert.update、delete的时候产生的便于数据回滚的日志。

当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

1.先复制一份就数据到undo log中
2.修改记录中的数据，同时更新DB_TRX_ID和DB_ROLL_PIR

ReadView

核心字段
在这里插入图片描述
匹配规则

案例分析

qq_65348015

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
# MySQL学习日记

创建CREATE PROCEDURE 存储过程名(参数) BEGIN [ DECLARE 变量名类型 [ DEFAULT 值 ];] 存储过程的语句块;END;### 例如： create procedure p1() BEGIN select count(*) from emp;end;调用call p1();查看删除drop procedure 存储过程名字 ##例如： drop PROCEDURE p1;case是另一个条件判断的语句，类似于编程语言中的 choose、when语法。
复制链接

扫一扫