Mysql基础知识总结

news/2024/11/15 0:35:45/

​⭐️⭐️SQL执行顺序

Sql语句在数据库中的执行流程 📦

image-20240723172656072

  1. 系统（客户端）访问 MySQL 服务器前，做 的第一件事就是建立 TCP 连接。

  2. Caches & Buffers： 查询缓存组件

  3. SQL Interface: SQL接口 接收用户的SQL命 令，并且返回用户需要查询的结果。比如 SELECT ... FROM就是调用SQL Interface MySQL支持DML（数据操作语言）、DDL （数据定义语言）、存储过程、视图、触发器、 自定 义函数等多种SQL语言接口

  4. Parser: 解析器:在解析器中对 SQL 语句进行 语法分析、语义分析。

  5. Optimizer: 查询优化器

  6. 存储引擎

  7. 文件系统

  8. 日志系统

Sql查询语句的执行顺序📄

image-20240723173221066

如何查看MYSQL是否运行

 -- 查看mysql进程状态(不联网)ps -aux | grep mysql​-- 查看3306端口的运行状态(联网)netstat -antp | grep mysql​-- 启动mysql--  mysqld_safe 是一个启动脚本 --  --user=mysql表示使用mysql用户进行登录--  & 表示后台运行./mysqld_safe --user=mysql &

视图 关键字：view

⭐️⭐️⭐️⭐️概念：视图是从一个或者几个基本表或(视图)导出的表，它与基本表不同，是一个虚表。

🎈视图的创建

create view 视图名[view_xxx / v_xxx] as 查询语句

 create view v_stu_man asselect * from student where ssex = '男'

🎈视图的使用

⭐️⭐️⭐️

  1. 视图是一个虚拟表，只能用来查询，不能对其增改

  2. 更改表中字段内容，查询出的视图也会改变

 -- 视图使用select * from v_stu_man;​-- 视图进行多表连接查询-- 给视图起别名vsm,后期须用别名调用,否则会报错select * from v_stu_man vsmleft join class on vsm.classid = class.classid​-- 将多表连接查询结果创建新的视图create view v_vstuman_class asselect v_stu_man.* ,classname from v_stu_manleft join class on v_stu_man.classid = class.classid​select * from v_vstuman_class

🎈视图的查询

select * from information_schema.VIEWS WHERE table_schema = ‘库名';

 -- 查看库中所有的视图select * frominformation_schema.VIEWS WHERE table_schema = 'myschool';

🎈视图的删除

DROP VIEW view_name;

⭐️⭐️⭐️视图的作用(4个)

  1. 简化查询

  2. 重写格式化数据

  3. 频繁访问数据库

  4. 过滤数据

存储过程 关键字: procedure

🎗存储过程的创建

⭐️⭐️⭐️⭐️概念:存储过程是在大型数据库系统中，一组为了完成特定功能的sql语句集，存储在数据库中，经过第一次编译后，再次调用不需要重新编译，用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

🥖不带参数的存储过程

create PROCEDURE 存储过程名 [proc_xxx] (形参列表) begin 一组sql语句集 end ⭐️⭐️⭐️

  1. delimiter $$ 设置定界符(遇见$$符号才表示语句结束)

  2. 存储过程本质上还是一个函数

 delimiter $$create PROCEDURE proc_test()begin select * from student;end $$delimiter ;
🥖带参数的存储过程

in 只入参 (值传递)

out 只出参（无）

inout 出入参（引用传递）

环境变量 @ 局部环境变量 @@ 全局环境变量

 delimiter $$create procedure proc_test2(in a int,   -- in 只入参（值传递）out b int,  -- out 只出参 （无）inout c int -- inout 出入参 （引用传递）)begin set a = a+1;set b = b+100;set c = c+1000;end $$delimiter ;​-- 环境变量  @ 局部环境变量  @@ 全局环境变量set @x = 10;  set @y = 20;     set @z = 30;     ​select @x, @y, @z;  -- 输出结果: @x=10, @y=20, @z=30​call proc_test2(@x,@y,@z)​select @x, @y, @z; -- 输出结果:@x=10, @y=null, @z=1030

⭐️⭐️⭐️分页查询

 -- 删除存储过程drop procedure proc_stuPage;​delimiter $$create procedure proc_stuPage(in curpage int,  -- 页数(接收当前页码)in sizepage int, -- 步长(一页内的数据条数)，页面大小out stucount int, -- 学生个数out pagecount int -- 页个数)begin declare cp int; -- 定义cp变量-- 分页 (当前页数-1)*步长--(cp即跳过多少条记录)set cp = (curpage-1)*sizepage; -- 查学生个数放进stucount变量里select count(*) from student into stucount; -- 页个数(学生数/页面大小)并向上取整set pagecount = ceiling(stucount / sizepage);-- 分页,limit不接受表达式，所以定义变量cpselect * from student limit cp,sizepage; end $$delimiter ;​set @a = 0;set @b = 0;-- 查询第2页3条数据call proc_stuPage(2,3,@a,@b);​select @a,@b

🎗存储过程的调用

call 存储过程名

 call proc_test();

🎗存储过程的查看

SELECT * FROM information_schema.ROUTINES WHERE routine_schema=‘库名

🎗存储过程的删除

DROP PROCEDURE 存储过程名;

⭐️⭐️⭐️存储过程和函数的区别

  • 语法 ：关键字不同，存储过程是procedure， 函数是function；

  • 执行 ：存储过程可以独立执行，函数必须依 赖表达式的调用；

  • 返回值 ：存储过程可以定义多个返回结果， 函数只有一个返回值

  • 功能 ：函数不易做复杂的业务逻辑，但是存储过程可以。

触发器(隐式执行) 关键字: trigger

⭐️⭐️⭐️⭐️概念：触发器是数据库中针对数据库表操作触发的特殊的存储过程。

🎈触发器的创建

old 已经存在的数据

new 还不存在的数据(比如:新增)

delimiter $$ create trigger 触发器名【trig_xxx】 before/after insert/ update / delete on 表名 for each row begin 触发后执行的一组sql语句 end $$ delimiter ;

🎈触发器的查看

SELECT * FROM information_schema.TRIGGERS WHERE trigger_schema = '触发器所在的库名'

🎈触发器的删除

drop trigger 触发器名

例子: 删除学生

 -- 删除学生 sid 为 1  在此之前把学生成绩删除-- 创建触发器delimiter $$create trigger trig_delstu_delscbefore delete on student for each row begin -- old 已经存在的数据 、 new  还不存在的数据delete from sc where sid = old.sid;end $$delimiter ;​delete from student where sid = 1;​select * from student;​-- 查看myschool中的触发器SELECT * FROM information_schema.`TRIGGERS`WHERE trigger_schema = 'myschool'​-- 删除触发器drop trigger trig_delstu_delsc​

⭐️⭐️⭐️不推荐使用触发器

⭐️⭐️⭐️存储过程与触发器的区别

  • 关键字不同 存储过程用到的时procedure 触发器用的是trigger

  • 存储过程需要调用才执行，触发器自动执行

  • 存储过程可以定义返回值，但触发器没有返回值

  • 存储过程时一组特定功能的sql语句，触发器则是sql语句前后执行，本身不影响原功能。

事务

⭐️⭐️⭐️概念

事务(Transaction)将一组sql语句放在同一批次内去执行，如果一个sql语句出错，则该批次的所有sql都将被取消执行。

⭐️⭐️⭐️事务四大特征\ACID原则

  • 原子性:数据库中的事务执行作为原子粒度，即不可再分，整个语句要么执行，要么不执行。

  • 一致性:即在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。

  • 隔离性:不同事务的执行是互不干扰的，采用锁的机制保证事务之间的隔离性。

  • 持久性:事务完成之后，该事务所对数据库所作的更改便持久的保存在数据库中，并不会被回滚。

⭐️⭐️⭐️事务的操作(5步骤)

  1. 关闭Mysql自动提交

    SET AUTOCOMMIT = 0 ;

  2. 开启事务

    START TRANSACTION ;

  3. 一组sql语句

  4. 结束事务(判断)

    • 提交

    COMMIT ;

    • 回滚

    ROLLBACK ;

  5. 开启自动提交

SET AUTOCOMMIT = 1 ;

事物的原子性、一致性、持久性

事务的日志(2个)

事物的原子性、一致性、持久性由事务的redo日志 和 undo日志来保证

redo日志(重做日志) 提供再写入操作，恢复提交事务修改的页操作，用来保证事物的持久性

undo日志(回滚日志)回滚行记录到某个特定版本，用来保证事务的原子性、一致性。

事务的隔离性

为了让不同的事务之间相互不存在干扰，就需要对事务的操作进行隔离， 事务的隔离性也就是将操作同一个数据的事务相互分离，让操作之间分开有序的执行

通常数据库里都是采用锁的机制，保证事务之间的隔离性

⭐️⭐️⭐️事物的隔离级别

隔离级别越高，并发事务问题也越多，同时意味着加的锁也就越多，所以性能也越差。

READ_UNCOMMITTED 读未提交

  1. 事务读取：不加锁

  2. 事务写入：加写锁

  3. 解决问题：脏写

  4. 存在问题：脏读、不可重复读、幻读

READ_COMMITTED 读提交(不可重复读)

  1. 事务读取：加读锁(每次select完成都会释放读锁)

  2. 事务写入：加写锁

  3. 解决问题：脏写、脏读

  4. 存在问题：不可重复读、幻读

REPEATABLE_READ 可重复读(mysql默认)

  1. 事务读取：加读锁(每次select完不会完全释放锁，而是等事务结束后才释放)(如果是mysql的innodb还会加间隙锁)

  2. 事务写入：加写锁

  3. 解决问题：脏写、脏读、不可重复读、幻读(如果是mysql的innodb则已解决)

  4. 存在问题：幻读 (如果是mysql的innodb则不会出现幻读问题)

SERIALIZABLE 串行化

  1. 不管读取还是修改所有的事务串行化执行，一个事物的执行必须等其他事务的结束

事务日志

  • 事务的隔离性由 锁机制 实现。

  • ⽽事务的原⼦性、⼀致性和持久性由事务的 redo ⽇志和undo ⽇志来保证。 REDO LOG 称为 重做⽇志 ，提供再写⼊操作，恢复提交事务修改的⻚操作，⽤来保证事务的 持久性。 UNDO LOG 称为 回滚⽇志 ，回滚⾏记录到某个特定版本，⽤来保证事务的原⼦性、⼀致性。

  • redo 的流程

  • undo ⽇志

⭐️⭐️⭐️事务并发问题

在事务并发执行的时候，如果不进行事务隔离，那么就会出现脏写、脏读、不可重复读、幻读(数据新增时出现的现象)的问题。INNODB不会出现幻读问题(因为使用了MVCC机制)

Mysql中的锁分类

  • 基于锁的属性分类：共享锁(读锁、S锁)、排他锁(写锁、X锁)

  • 基于锁的粒度分类：表锁、行锁(记录锁、间隙锁、临键锁)

  • 基于锁的状态分类：意向共享锁、意向排它锁

MYSQL常见的锁类型

在MySQL中，特别是针对InnoDB存储引擎，存在多种类型的锁，⽤于控制事务之间的并发访问， 确保数据的⼀致性和隔离性

  1. 共享锁（S）：也称为读锁。当⼀个事务对某个数据项加了共享锁，其他事务可以同时对该数 据项加共享锁，但不能加排他锁。共享锁主要⽤于⽀持读取操作。

  2. 排他锁（X）：也称为写锁。当⼀个事务对某个数据项加了排他锁，其他事务不能对该数据项 加任何类型的锁。排他锁主要⽤于⽀持写⼊操作，如更新、插⼊或删除数据。

  3. 意向共享锁（IS）：事务想要获取⼀个表上的多个⾏的共享锁时，可以在表级别加意向共享 锁。这样可以减少锁的粒度，提⾼并发性能。

  4. 意向排他锁（IX）：事务想要获取⼀个表上的多个⾏的排他锁时，可以在表级别加意向排他 锁。这种锁表明事务可能需要对这些⾏加排他锁。

  5. 表锁：这种锁是在表级别上加的，通常⽤于MyISAM存储引擎。表锁包括表共享读锁和表排他 写锁，它们分别对应于共享锁和排他锁。

  6. ⾏级锁：这种锁是在⾏级别上加的，主要⽤于InnoDB存储引擎。⾏级锁包括记录锁（Record Locks）、间隙锁（Gap Locks）和临键锁（Next-Key Locks）。这些锁主要⽤于实现 MVCC（多版本并发控制）和避免幻读现象。

  7. 乐观锁：这种锁机制假设并发冲突较少，事务在提交时才检查是否有冲突。通常通过版本号或 时间戳来实现。

  8. 悲观锁：这种锁机制假设并发冲突较多，事务在开始时就加锁，以防⽌其他事务的修改。这种 ⽅式可能导致锁等待和性能下降。

在实际应⽤中，MySQL会根据事务的需要和配置参数⾃动选择合适的锁类型，以平衡并发性能和 数据⼀致性。了解这些锁类型有助于优化数据库性能和解决并发访问问题。

间隙锁

在MySQL中，间隙锁（Gap Lock）是InnoDB存储引擎⽤来解决幻读问题的⼀种锁机制。间隙锁 锁定的是索引记录之间的间隙，或者是第⼀个索引记录之前或最后⼀个索引记录之后的空间，⽽不 是索引记录本身。

间隙锁的作用

间隙锁的主要作⽤是阻⽌其他事务在锁定的间隙内插⼊新的索引记录。这样，当⼀个事务在执⾏范 围查询时，即使其他事务尝试插⼊新的记录到这个范围内，也不会影响到当前事务的查询结果，从 ⽽避免了幻读现象。

间隙锁的工作原理

  1. 锁定间隙：当⼀个事务执⾏⼀个带有范围条件的SELECT语句，或者执⾏⼀个带有范围条件的 UPDATE或DELETE语句时，InnoDB会锁定满⾜条件的索引记录之间的间隙。

  2. 共享和排他间隙锁：间隙锁可以是共享的（S）或排他的（X）。共享间隙锁不会阻⽌其他事 务获得共享间隙锁，但会阻⽌排他间隙锁。排他间隙锁则阻⽌其他事务获得任何类型的间隙 锁。

  3. 间隙锁与索引：间隙锁只在事务隔离级别为可重复读（Repeatable Read）或序列化 （Serializable）时使⽤。⽽且，间隙锁只在与某些类型的索引⼀起使⽤时才会⽣效，例如聚 簇索引或辅助索引。

间隙锁的示例

   假设我们有⼀个表 orders ，其中有⼀个索引 order_date ： ​```CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, INDEX (order_date) );如果⼀个事务执⾏了以下查询：START TRANSACTION;SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'FOR UPDATE;```

InnoDB会在 order_date 索引上锁定从'2023-01-01'到'2023-01-31'之间的所有间隙，以及这个 范围内的所有索引记录。这意味着其他事务不能在这个范围内插⼊新的订单记录，但可以读取或更 新现有的记录。

间隙锁的注意事项

  1. 性能影响：间隙锁可能会增加锁竞争，从⽽影响性能。因此，设计数据库时应该考虑索引的选 择和事务隔离级别的设置。

  2. 死锁⻛险：由于间隙锁的存在，事务之间可能会发⽣死锁。例如，两个事务可能都试图锁定同 ⼀个间隙，导致互相等待。

  3. 锁的释放：间隙锁只在事务提交或回滚时释放，⽽不是在语句执⾏完毕时释放。 通过使⽤间隙锁，MySQL的InnoDB存储引擎能够提供更⾼的事务隔离级别，同时尽可能地减少锁 竞争，提⾼并发性能。然⽽，间隙锁的使⽤也需要谨慎，因为它可能会引⼊死锁的⻛险，并对性能 产⽣⼀定的影响。

临键锁

在MySQL中，临键锁（Next-Key Lock）是InnoDB存储引擎⽤来解决幻读问题的⼀种锁机制。临 键锁是记录锁和间隙锁的组合，它不仅能锁定记录本身，还能锁定记录下⼀个值的间隙，从⽽防⽌ 其他事务在该间隙内插⼊新的记录。

临键锁的作用

  1. 防⽌幻读：通过锁定记录下⼀个值的间隙，临键锁可以防⽌其他事务在该间隙内插⼊新的记 录，从⽽避免了幻读现象。

  2. 提⾼并发性能：临键锁的使⽤可以减少锁的竞争，提⾼系统的并发性能。

临键锁的工作原理

临键锁是在记录锁的基础上扩展⽽来的。当⼀个事务执⾏⼀个带有范围条件的SELECT语句，或者 执⾏⼀个带有范围条件的UPDATE或DELETE语句时，InnoDB不仅会锁定满⾜条件的索引记录， 还会锁定这些记录下⼀个值的间隙。

临键锁的注意事项

  1. 性能影响：临键锁可能会增加锁竞争，从⽽影响性能。因此，设计数据库时应该考虑索引的选 择和事务隔离级别的设置。

  2. 性能影响：临键锁可能会增加锁竞争，从⽽影响性能。因此，设计数据库时应该考虑索引的选 择和事务隔离级别的设置。

  3. 锁的释放：临键锁只在事务提交或回滚时释放，⽽不是在语句执⾏完毕时释放。

通过使⽤临键锁，MySQL的InnoDB存储引擎能够提供更⾼的事务隔离级别，同时尽可能地减少锁 竞争，提⾼并发性能。然⽽，临键锁的使⽤也需要谨慎，因为它可能会引⼊死锁的⻛险，并对性能 产⽣⼀定的影响。

临钥锁与MVCC的关系是什么

在MySQL的InnoDB存储引擎中，临钥锁（Next-Key Locking）和多版本并发控制（MVCC）是 两种不同的机制，但它们共同⼯作以实现事务的隔离性和并发控制。

临钥锁与MVCC的关系

  1. 并发控制：MVCC通过维护数据的多个版本来实现事务的并发控制，允许读写操作在不加锁的 情况下进⾏。临钥锁则通过锁定记录和记录之间的间隙来防⽌幻读，从⽽实现更⾼级别的事务 隔离。

  2. 锁的粒度：临钥锁是记录锁和间隙锁的组合，它的粒度⽐单纯的记录锁更细。通过使⽤临钥 锁，InnoDB可以在不需要锁定整个表或整个索引的情况下实现事务的隔离性。

  3. 锁的兼容性：MVCC通过版本号来区分不同事务看到的数据版本，⽽临钥锁通过锁定记录和间 隙来防⽌其他事务插⼊新的记录。这两种机制在事务隔离级别为可重复读（Repeatable Read）或序列化（Serializable）时共同⼯作，以确保数据的⼀致性和隔离性。

临钥锁和MVCC是MySQL InnoDB存储引擎中两种重要的并发控制机制。它们共同⼯作，以实现事 务的隔离性和并发性能，同时尽可能地减少锁竞争和性能影响。

MYSQL中的死锁

在MySQL中，死锁是指两个或多个事务在等待对⽅释放锁的情况下，导致所有事务都⽆法继续执 ⾏的现象。死锁通常发⽣在事务并发操作且相互锁定资源时

死锁检测

  1. 使⽤INFORMATION_SCHEMA:

    可以通过查询 INFORMATION_SCHEMA.INNODB_TRX 和 INFORMAT ION_SCHEMA.INNODB_LOCKS 这两个视图来检测事务和锁的状态

     SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  

这些查询可以帮助你了解当前活跃的事务及其持有的锁信息。

  1. SHOW ENGINE INNODB STATUS: 这个命令可以显示InnoDB存储引擎的当前状态，包括锁 信息、事务状态等，有助于分析死锁情况。

      SHOW ENGINE INNODB STATUS;

死锁解决办法

  1. 设置超时时间: 通过设置 innodb_lock_wait_timeout 参数，可以指定事务等待锁的超时时 间。超过这个时间后，事务将⾃动回滚。

 SET GLOBAL innodb_lock_wait_timeout = 60;

  1. ⼿动⼲预: 通过查询 INFORMATION_SCHEMA.INNODB_TRX 找到死锁的事务，然后⼿动杀死其中⼀ 个事务，以解除死锁。

 SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_state = 'LOCK WAIT';KILL trx_mysql_thread_id;        

  1. 调整事务的执⾏顺序: 改变事务中SQL语句的执⾏顺序，或者调整事务的⼤⼩和范围，可以避 免死锁的发⽣。

  2. 使⽤死锁检测⼯具: MySQL本身没有内置的死锁检测⼯具，但可以通过第三⽅⼯具或⾃定义脚 本来检测和处理死锁。

  3. 优化锁的使⽤: 尽量减少锁的持有时间，避免在事务中进⾏⻓时间的计算或等待操作。尽量使 ⽤低隔离级别的事务，以减少锁的使⽤

  4. 使⽤悲观锁或乐观锁: 根据实际情况选择适合的锁策略。悲观锁适⽤于并发冲突较多的场景， 乐观锁适⽤于并发冲突较少的场景。

    通过以上⽅法，可以有效地检测和解决MySQL中的死锁问题，提⾼系统的稳定性和性能。

Mysql的MVCC

通过MVCC机制：MySQL的多版本并发控制（MVCC）是⼀种⽤于实现事务隔离的技术，它允许多个事务在同⼀时 刻对同⼀数据进⾏操作⽽不互相阻塞。MVCC通过维护数据的多个版本来实现这⼀点，每个事务看 到的数据版本是基于该事务开始时的数据版本

MVCC的基本原理

  1. 版本号：每个数据⾏都有⼀个隐藏的版本号，⽤于标识该⾏数据的版本。当⼀⾏数据被更新 时，其版本号也会被更新。

  2. 读取操作：读取操作会读取符合其事务开始时版本号的数据版本，⽽不是最新的数据版本。这 样可以保证事务在读取过程中看到的数据是⼀致的。

  3. 写⼊操作：写⼊操作会创建⼀个新的数据版本，并更新该⾏数据的版本号。旧的数据版本仍然 保留，直到垃圾回收器清理掉不再需要的版本。

MySQL中的MVCC实现

MySQL的InnoDB存储引擎使⽤了⼀种称为“乐观锁定”的技术来实现MVCC。具体来说，InnoDB 使⽤了以下⼏种机制：

  1. ⾏版本链：每⾏数据都有⼀个版本链，包含该⾏数据的所有版本。每个版本都有关联的时间 戳，表示该版本的创建时间。

  2. 事务快照：每个事务在开始时会创建⼀个快照，记录当前所有的活动事务。事务在读取数据 时，会使⽤这个快照来确定哪些数据版本是可⻅的。

  3. 间隙锁：为了防⽌幻读，InnoDB使⽤了间隙锁（Gap Locks），它可以锁定查询结果集之间 的间隙，防⽌其他事务插⼊新的记录。

MVCC的优点

  1. 提⾼并发性能：MVCC通过减少锁的使⽤，提⾼了系统的并发性能。

  2. 减少锁争⽤：由于每个事务看到的是⾃⼰快照中的数据版本，减少了锁争⽤的情况。

  3. 简化事务管理：MVCC使得事务的管理和隔离更加简单和⾼效。

MVCC的缺点

  1. 额外的存储开销：维护多个数据版本会占⽤更多的存储空间。

  2. 垃圾回收开销：需要定期清理不再需要的数据版本，否则会导致存储空间浪费。

事务并发会出现的问题

  • 脏写：事务 Session A、Session B，事务Session A 修改了另一个未提交事务Session B 修改过的数据

  • 脏读：事务 Session A读取了已经被 Session B 更新但还没有被提交的字段，之后若 Session B 回滚 ，Session A 读取的内容就是临时且无效 的。

  • 不可重复读：事务Session A读取 了一个字段，然后 Session B 更新了该字段。之后 Session A 再次读取同一个字段， 值就不同了。

  • 幻读：事务Session A 从一个表中读取 了一个字段, 然后 Session B 在该表中插入了一些新的行。 之后, 如果 Session A 再次读取同一个表, 就会多出几行。把新插⼊的那些记录称之为 幻影记录。

解决幻读问题

幻读是指在⼀个事务中多次执⾏相同的查询，但是第⼆次查询返回的结果集中包含了第⼀次查询时 不存在的记录。这通常发⽣在其他事务插⼊了新的记录，⽽这些新记录在第⼀个查询之后、第⼆个 查询之前被插⼊到数据库中。

为了避免幻读问题，可以采取以下⼏种⽅法：

  1. 使用事务隔离级别:

  • 重复读（Repeatable Read）: 设置事务隔离级别为重复读可以避免幻读。在这个隔离级 别下，MySQL使⽤间隙锁（Gap Locks）来锁定查询结果集之间的间隙，防⽌其他事务 插⼊新的记录。

  • 串行化（Serializable）: 设置事务隔离级别为串⾏化可以完全避免并发问题，但可能会降 低性能，因为所有事务都会按顺序执⾏。

  1. 使用锁:

  • 行级锁: 在执⾏更新或删除操作时，可以显式加锁来防⽌其他事务插⼊新的记录。

  • 间隙锁: InnoDB存储引擎使⽤间隙锁来锁定查询结果集之间的间隙，防⽌其他事务插⼊新 的记录。

  1. 使用悲观锁:

  • 在事务开始时就对需要访问的数据加锁，直到事务结束。这种⽅法可以有效避免幻读，但 也可能导致锁等待和性能下降。

使⽤乐观锁: 假设并发冲突较少，事务在提交时才检查是否有冲突。通常通过版本号或时间戳来实现。 虽然这种⽅法不能完全避免幻读，但可以减少锁的使⽤，提⾼并发性能。

  1. 使用事务的SAVEPOINT和ROLLBACK

  • 在事务中使⽤保存点（SAVEPOINT）和回滚（ROLLBACK）功能，可以在发现幻读后回 滚到保存点，重新执⾏查询。

  1. 使用数据库约束

  • 使⽤唯⼀约束或主键约束来防⽌插⼊重复的记录，从⽽避免幻读。

  1. 查询优化

  • 尽量避免在事务中进⾏⼤范围的查询或扫描，尤其是那些可能导致其他事务插⼊新记录的 操作。 通过以上⽅法，可以有效地避免MySQL中的幻读问题，提⾼系统的稳定性和性能。根据具体的应 ⽤场景和需求，选择适合的⽅法来进⾏优化。

函数

select curdate() 当前日期

select curtime() 当前时间

select now() 当前时间点

select month(now()) 查询当月的月份

select DATE_FORMAT() 依照指定的fmt格式格式化 日期date值

select datediff（now(),‘2024-6-24’） 计算时间差（天数）

select TIMESTAMPDIFF(SECOND,'2002-05-09',NOW()) 活了已经（秒） ：计算两个日期的时间差

select now() + interval 5 year 查询距离时间的间隔时间

select concat(‘hello’,’world’)：拼接字符串

select concat_ws(‘hello’,'-',’world’)：拼接带字符的字符串

select floor(9.1) ：向上取整

select round(20.333,2)： 四舍五入

select TRUNCATE(3.999,2)： 截取数字

select Ssex,GROUP_CONCAT(sname) from student GROUP BY Ssex

常见的函数分类

数学函数，聚合函数，字符串函数，日期函数和自定义函数

⭐️⭐️⭐️面试题：查询当月过生日的学生？

 select * from student where month(birthday) = MONTH(now())

慢查询

数据库默认的最大连接数 （默认）200

定义：MySQL默认10秒内没有响应SQL结果,则为慢查询

image-20240724163342709

统计数量，不要使用非主键，为什么查找太慢，因为已经找到之后，不确定是否还有重复的数据

⭐️⭐️⭐️sql执行计划

在查询前能否预先估计查询究竟要涉及多少行、使用哪些索引、运行时间呢？

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为 MYSQL先读这个值然后把它当做常数来对待。

Extra 关于MYSQL如何解析查询的额外信息。


http://www.ppmy.cn/news/1505626.html

相关文章

PDF——分割pdf的10个工具

PDF——分割pdf的10个工具

PDF分割器是一种可用于将PDF文档分割成更小的文档甚至单个页面的工具。分割 PDF 文档的主要原因是为了更容易共享。 但该过程的成功取决于您用于拆分 PDF 的工具。较简单的工具仅提供几个选项，可能并不适合所有类型的文档。我们将在本文中列出的 10 个最佳 PDF 分割…
阅读更多...
部署Springboot + Vue 项目到远程服务器Windows10系统的详细配置

部署Springboot + Vue 项目到远程服务器Windows10系统的详细配置

远程服务器操作系统为Windows系统，Java程序环境，Maven环境都安装有，Mysql ,Redis等都有的前提下 1. mysql数据库导入，非常简单很好操作，这里省略。。比如用HeidiSql 或者Navicat 工具导入数据库 2. 后端javaSpringb…
阅读更多...
c#设置触控屏触控自动映射到扩展屏

c#设置触控屏触控自动映射到扩展屏

概要 在大多数系统中，触摸屏和触控笔的使用并不常见，相关的解决方案也较少。在Windows系统中，触摸屏和触控笔通常通过USB连接，并默认映射到主屏幕。如果没有进行额外设置，以下两种场景可能导致问题： 主屏幕为非触控屏幕，扩展屏幕为触控屏幕，导致在扩展屏上的操作实际影…
阅读更多...
在没有硬盘的情况下进行电脑数据迁移

在没有硬盘的情况下进行电脑数据迁移

电脑数据迁移方式 在更换电脑的时候需要进行文件的传输，但是没有硬盘可以选择使用网线直连或者无线文件共享。通用配置 1.将旧电脑的文件夹或者磁盘设置文件共享 找到指定的文件夹右键属》属性，点击共享》点击高级共享 选择共享文件夹以及修改共享用户…
阅读更多...
基于Docker的Redis安装

基于Docker的Redis安装

一、搜索镜像 通过docker 搜索 redis 镜像文件 docker search redis二、下载镜像 通过docker images 查看下载下来的redis镜像，默认下载最新的版本 docker pull redis三、配置挂载文件 创建本地与docker映射的目录，即本地存放的位置 创建本地存放re…
阅读更多...
Flink-DataWorks第二部分：数据集成（第58天）

Flink-DataWorks第二部分：数据集成（第58天）

系列文章目录 数据集成 2.1 概述 2.1.1 离线（批量）同步简介 2.1.2 实时同步简介 2.1.3 全增量同步任务简介 2.2 支持的数据源及同步方案 2.3 创建和管理数据源 文章目录 系列文章目录前言2. 数据集成2.1 概述2.1.1 离线（批量）同步…
阅读更多...
C++详解-＞函数模板+类模版

C++详解-＞函数模板+类模版

文章目录 前言1、反泛型编程2、函数模板(1)、函数模板概念(2)、函数模板定义格式(3)、函数模板实例化(4)函数模板参数匹配原则 3、类模版(1)类模板实例化(2)类模板实现Stack（压/出栈函数） 前言 此篇主要描述函数模板的概念、格式以及实例化等&#xff1b…
阅读更多...
PhPMyadmin漏洞复现

PhPMyadmin漏洞复现

首先我们打开环境，这个环境需要在小皮面板里安装，然后php版本需要在5.5以上的版本才可以运行 然后打开sql面板，将面板中的语句执行 保证日志保存状态开启 修改日志的存放位置 将语句执行 然后在工具上进行连接测试 写入一个webshell 然后打开…
阅读更多...
最新文章

Copyright @ 2022~2023