当前位置： 首页 > news >正文

mysql高级,mysql体系结构,mysql引擎,存储过程，索引，锁

news 2025/3/29 22:42:49

1.mysql体系结构

1） 连接层

主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2） 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3） 引擎层 [存储引擎]

数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。 在之前[MyISAM]: MySQL5.5之后，MySQL默认的存储引擎就是InnoDB,InnoDB默认使用的索引结构就是B+树,上面的服务层就是通过API接口与存储引擎层进行交互的

4）存储层

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2.mysql引擎

MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

InniDB支持事务，行级锁，外键

mylSAM  不支持事务和外键，支持表级锁

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

3.存储过程

MySQL 中的存储过程是一种预编译的 SQL 脚本，它可以存储在数据库服务器上，并通过一个名称来调用。存储过程可以在数据库级别上封装一组复杂的操作，这样可以简化客户端应用程序与数据库之间的交互，并且可以提高性能和安全性。

1.创建存储过程

CREATE PROCEDURE te01()
BEGIN
  SELECT 'hello';
END;

调用
call te01();

2.删除

DROP PROCEDURE te01;

3.定义变量

create PROCEDURE te02()
BEGIN
  declare num int default 5;
  SELECT num + 20;
END;
call te02();


4.直接赋值使用 SET

CREATE PROCEDURE te03()
BEGIN 
 DECLARE num int DEFAULT 1;
 set num=10;
 SELECT num;
 END;
 CALL te03();

5.通过select ... into 方式进行赋值操作

CREATE PROCEDURE te04()
BEGIN
  DECLARE num VARCHAR(20);
  SELECT city_name into num from city_innodb WHERE city_id=1;
  SELECT num;
  END;
  call te04();

6.if条件判断

CREATE PROCEDURE ce03(in m int,out result VARCHAR(200))
BEGIN
  if m in (1,3,5,7,8,10,12) then set result='31天';
  ELSEIF m=2 then set result='28天';
  ELSE set result='30天';
  end if;
END;
call ce03(2,@r);
SELECT @r;

7.case

CREATE PROCEDURE te08(in n int)
BEGIN
 CASE 
    WHEN n in (1,3,5,7) THEN
        SELECT '奇数';
   WHEN n in (2,4,6,7) THEN
        SELECT 'o数';
    ELSE
        SELECT '输入错误';
END CASE;
END;
call te08(5);

8.while

CREATE PROCEDURE te09(in n int,out sum INT)
BEGIN 
  set sum=0;
  WHILE n>=1 DO
    set sum=sum+n;
  set n=n-1;
END WHILE;
END;
call te09(100,@sum);
SELECT @sum;

9.repeat 类似do{}while

CREATE PROCEDURE te10(in n int,out sum int)
BEGIN
  set sum=0;
  REPEAT
    set sum=sum+n;
  set n=n-1;
UNTIL n=0 END REPEAT;
END;
CALL te10(100,@sum);
SELECT @sum;
 

4.索引

索引它就是一种数据结构，可以帮mysql快速查找到相应的数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的手段。

4.1索引优势劣势

4.2索引结构

不同的数据库引擎使用的索引结构不同，一般多用B+树

  • B+TREE 索引 ： 最常见的索引类型，大部分引擎都支持 B 树索引。

  • HASH 索引：只有Memory引擎支持 ， 使用场景简单 。

  • Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

4.3二叉树，红黑树，B+树

二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低，在大量数据的情况下层级较深，检索速度慢

红黑树：大量数据的情况下，层级较深，检索速度慢

B树，采用多路平衡，每个节点既存放索引又存放数据

B+树，根节点只存放索引，所有数据都存放在中叶子节，并且叶子节点会形成一个双链表，方便范围内查找，且查找效率比较稳定

4.4索引分类

按索引字段的特性分类 

普通索引   

  • 即一个索引值包含单个列。一个表可以有多个单例索引

唯一索引

  • 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引    它是一种特殊的唯一索引，不允许有空值

联合索引（多列索引，组合索引，复合索引）

  • 一个索引包含多个列，专门用于组合搜索，其效率大于索引合并

全文索引

按索引的存储形式

聚集索引        将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据
必须有，而且只有一个
二级索引        将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
可以存在多个

索引的语法

create index 索引名[name_index] on 表名(name)

type索引类型 

system ：根据主键索引

const:：根据唯一索引

eq_ref和ref：根据普通索引

range：索引范围查询

index和all：没有索引查询

平常的到达了range即可，尽量到ref

如何分析sql

explain  一般查看哪些指标：type索引类型key:实际使用的索引key_len索引的长度row:影响得到行数

索引失效的场景：

1.使用联合索引时必须满足最左前缀法则

从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

2.使用联合索引时，某个字段进行了范围查询，它的右侧索引会失效（创建索引时的顺序）

3.索引运算，索引列上有运算操作的话会失效

4.字符串索引类型的字段查询时没有使用 ' '

5.模糊查询，尾部模糊索引不会失效，头部模糊会失效

6.or  连接了一个索引字段，or后面的字段没有索引则导致索引字段也会失效

7.覆盖索引

查找的条件和需要的数据都能在索引中找到，不需要再回表

5.锁

5.1锁的分类

从对数据操作的粒度分 ：

1） 表锁：操作时，会锁定整个表。MyISAM 5.6 后InnoDB支持

2） 行锁：操作时，会锁定当前操作行。Innodb

从对数据操作的类型分：

1） 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2） 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写操作和读操作。

MySQL这2种锁的特性可大致归纳如下 ：

锁类型特点
表级锁偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

5.2mylsam表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。 但是我们为了演示出效果人为加锁。

显示加表锁语法：

加读锁 ： lock table table_name read; 

加写锁 ： lock table table_name write；--- insert delete updpate

释放锁:   unlock tables; -- 执行完毕

5.3InnoDB 行锁

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。

InnoDB 实现了以下两种类型的行锁。

  • 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

  • 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

    当执行修改操作，默认innodb就会给数据库表中对应的行加排他锁

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

行级锁:

分成: 共享锁: 执行select时会自动添加该锁。 不影响其他的操作。

        排他锁: 执行更新操作时自动添加该锁。影响其他事务的更新操作

5.3.2悲观锁和乐观锁

悲观锁:

乐观锁: 增加一个version字段，---一个事务进行操作时，该事务会先获取version的值。当这个事务操作完成后，查看数据库中的version字段值是否和当前自己家拥有的version字段的值相同。如果相同则修改成功并对version+1，如果不同则不修改。


http://www.mrgr.cn/news/95977.html

相关文章：

  • （UI自动化测试web端）第二篇：元素定位的方法_css定位之ID选择器
  • uniapp动态循环表单校验失败：初始值校验
  • 解决Spring Cloud OpenFeign端点未暴露问题
  • Modbus RTU ---＞ Modbus TCP透传技术实现（Modbus透传、RS485透传、RTU透传）分站代码实现、协议转换器
  • 反序列化漏洞
  • Docker+Ollama+Xinference+RAGFlow+Dify部署及踩坑问题
  • msyql--基本操作之运维篇
  • 【JavaSE】抽象类和接口
  • uniapp页面列表，详情返回不刷新，新增或编辑后返回刷新
  • mysql中show命令的使用
  • NodeJs之fs模块
  • 【408--复习笔记】计算机组成原理
  • 【模型压缩+推理加速】知识蒸馏综述解读
  • 嵌入式硬件工程师从小白到入门-原理图（三）
  • ofd转pdf报错：org.ofdrw.reader.ZipUtil.unZipFileByApacheCommonCompress【已解决】
  • 语言模型理论基础-持续更新-思路清晰
  • Vue 2 探秘：visible 和 append-to-body 是谁的小秘密？
  • Brainstorm绘制功能连接图（matlab）
  • vue - [Vue warn]: Duplicate keys detected: ‘0‘. This may cause an update error.
  • 第六篇：Setup：组件渲染前的初始化过程是怎样的？