MySQL执行原理、存储引擎、索引模型简介

时间：2024-03-17 17:29:34

1.sql的执行原理

  • Connectors

    连接、支持多种协议，各种语言

  • Management service

    系统管理和控制工具，例如：备份、集群副本管理等

  • pool

    连接池

  • sql interfaces

    sql接口-接收命令返回结果

  • parser

    分析解析器：验证

  • optimizer

    优化器：优化sql执行效率

  • cache and buffer

    查询缓存

  • storage engines

    存储引擎：可插拔

  • file system

    文件系统

1.1mysql的基本架构和执行原理

两个部分：server层与存储引擎层

  • 连接器

    建立客户端和服务器连接、权限获取、维持管理连接

    #mysql -u$user -p$password
mysql -uroot -p

    mysql本质为客户端连接工具,tcp握手成功，需要进行身份认证

    查询连接状态：

    短链接：执行少数几次查询就会断开，浪费大量资源

    长连接：连接成功后，长时间保持连接,wait_timeout默认连接时间

    查看默认连接时长

    show variables like 'wait_timeout';

    查当前连接时长

    show processlist；

    长连接驻留内存解决方法

    1、固定时间自动重新连接

    2、mysql_reset_connection 重置连接

  • 查询缓存

    query_cache_type:查询缓存类型

    show variables like 'query_cache_type';

    如果需要使用，需要设置为DEMAND(按需)

    缓存失效

    1、增删改操作导致缓存失效

    mysql8.0已将缓存移除

  • 分析器

    词法分析：检测每个单词的含义

    语法分析：对sql语法规则校验，是否满足mysql语法规范

  • 优化器

    选择最优解

    索引选择、执行顺序可能影响执行效率，优化器进行最优选择

    select * from t1 inner join t2 using(ID) where t1.c = 10 and t2.d = 20;

    如下两个执行顺序：

    1、先找到t1.c=10的所有记录和整个t2表关联，最后筛选t2.d=20的记录

    2、先找到t2.d=20的所有记录和整个t1表关联，最后筛选t1.c=10的记录

  • 执行器

    select * from emp where no = 10;

    1、表操作权限验证

    2、innodb存储引擎查询第一行，查看no=10则写道结果集，如果不等于10则跳过整张表查询完毕

    3、将结果返回给客户端

2.存储引擎

2.1存储引擎简介

数据库存储引擎是数据库底层软件组织，数据库管理管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。MySQL的核心就是存储引擎。

mysql底层设计采用可插拔式的存储引擎，用户根据需求，选择或自定义存储引擎

mysql5.5之后默认存储引擎为innodb

1、查看mysql可支持的存储引擎

show engines;

2、各种存储引擎对比

  • innoDB

    mysql5.5默认的存储引擎，事务型数据库。

    1.数据底层的存储：数据表文件-->>.frm(表结构)文件和.ibd(数据和索引)文件

    2.事务：支持热备份，对数据完整性要求较高，mysql是较好选择

    3.锁的粒度：采用MVVC（多版本并发）支持高并发操作，支持四种事务隔离级别，行锁

    4.存储特点：采用聚簇索引

    5.适用场景：更新和查询比较频繁，并发操作、要求事务，支持外键约束

    #查看mysql数据存储位置
show variables like '%data%';

  • MyISAM

    1.存储形式：数据表文件-->>.frm(表结构)和.MYD和.MYI(数据和索引分离)

    2.事务：不支持

    3.存储特点：非聚簇

    4.其他：全文检索，压缩，延迟更新索引等

    5.适用场景：count计算、查询

  • Memory

    1.数据保存在内存中，增删改查效率高，但是不能持久化

    2.不支持事务、表级锁

2.2如何设置存储引擎

#默认mysql的配置文件路径
修改存储引擎：
default_storage_engine=INNODB

#创建表时修改存储引擎:
create table tname(col) engine = INNODB;

#查看某张表的基本信息
#命令行模式
show tables status from dbname where name = tname \G;

3.索引

3.1索引简介

索引：为了提升查询效率创建数据机构

3.2常见的索引模型

  • 哈希表

    键-值方式存储数据结构。使用key进行hash计算获取到一个值（位置），去该位置寻找数据（值）
数组，hash函数 

适用场景：
等值查询
不适用场景：
范围查询，存储是无序的

哈希碰撞（哈希冲突）
解决方法：
1、链表（拉链法）
    即多个不同的key值经过哈希函数的计算后，会出现同一个值的情况。处理方法是拉出一个链表。

  • 有序数组

整个数组中排列的是有序的，查找非常方便，可以使用二分法
适用场景：等值查询、范围查询，主要是用与存储静态的数据或者不需要经常变更的数据

不适用场景：
进行数据的插入与删除，因为每次插入或删除一个数值时都需要移动后面的数据消耗会比较大

  • 二叉搜索树

        二叉查找树（Binary Search Tree）（又称二叉搜索树，二叉排序树）它或者是一棵空树，或者是具有下列性质的二叉树：若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；它的左、右子树也分别为二叉排序树。

二叉树的特点：

1、有链表的快速插入与删除操作的特点，

2、有数组快速查找的优势

3、树最影响效率的是树的深度

二叉树存在的问题：

1、在操作数据时如何平衡二叉树，因为在插入随机的情况下有可能其中不同分支的树深度不同导致查询消耗增加

  • B树

B树的定义：

B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树

每个节点都存有索引和数据，也就是对应的key和value。索引是指向子节点的指针，数据是关键字；
每个节点最多有m-1个关键字（可以存有的键值对），即每个节点至多含有m棵子树；
若根节点不是终端节点，则至少有两棵子树，即最少可以只有1个关键字；
除根节点外的所有非叶节点至少有上限值（m/2）棵子树，即上限值（m/2）-1个关键字；
每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。

B树优点：

1、B树在每一个节点上都存储数据，因此在访问离根节点近的数据时速度会更快

2、继承二叉树优点

  • B+树

B+树特点

B+树结构只在叶子节点才存储真正的数据，其他子节点存储的是数据索引，假设每个子节点大小16KB，则B+树相比B树能存储更多的关键字，每次读入内存的关键字也会更多，这样B+树从磁盘读取数据损耗更低低，因为子节点不存储真正的数据所以B+树的树高也会比B树低很多不需要几层就能存储较大数据,会大大磁盘IO次数。

B+树叶子节点数据是顺序排放的，所以B+树结构对范围查询有天然优势,方便遍历。

B+树结构树的层高稳定，B+树查询效率稳定性更好， 在B+树结构中，分支节点并不存储数据，分支节点只是叶子节点的索引，对于任意关键字的查找都必须从根节点走到分支节点，所有关键字查询路径长度相同，每个数据查询效率相当。B树结构其每一个分支节点上也都保存数据，因此对于每一个数据的查询所走的路径长度是不一样的，效率也不一样，B树稳定性不如B+树好

相关文章