MySQL高级部分( 二: MySQL架构、引擎、索引)

已于 2022-07-31 10:57:46 修改
阅读量2.4k 收藏 1
点赞数
分类专栏： 数据库(mysql) 文章标签： mysql 架构 数据库
于 2022-01-24 22:20:46 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lanleihhh/article/details/122602146
版权

MySQL高级 二: MySQL架构、引擎、索引、事务

MySQL架构

MySQL 的完整架构图

在这里插入图片描述

各层介绍

连接层

一些客户端和连接服务,包含本地socket编程和大多数基于client/server工具实现的类似与 TCP/IP的通信。

负责与客户端建立连接、认证、授权 及相关安全方案。

连接池
频繁地 连接/断开 需要消耗大量资源，连接池 将这些连接缓存下来，接收到程序发送连接请求的时候直接使用连接池中建立好的连接，提升服务器的性能。

Authentication：认证
Thread Reuse：线程重用
Connection Limits：连接线程数量限制
Check Memory：检查内存，是指与连接线程相关的内存
Caches：必要时的线程缓存

服务层

接收SQL，负责调用函数，存储过程，触发器；对SQL的执行顺序进行排序优化
如果是查询操作，先从缓存中查，缓存中没有再去磁盘中查，提高了性能

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化,如确定查询表的顺序，是否利用素引等，最后生成相应的执行操作

  1. SQL接口
    是MySQL的外壳
    一个完整的SQL命令解释器
    检查SQL语句的语法

  2. 解析器
    SQL命令传递到解析器的时候会被解析器验证和解析
    不检查语法问题
    做语句的执行分析，查询翻译（转为本地可执行的操作）

  3. 查询优化器
    分析哪种的开销、代价最小
    一旦Optimizer发现有比当前选择的路径更优的实现目的方式，将会改写用户的SQL语句，优化完之后将会交与存储引擎执行

  4. 缓存
    查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
    数据库的不断被访问，IO是计算机中最慢的一环，尤其磁盘IO,通常载入到内存中管理.

存储引擎层 Pluggable Storage Engine

真正的执行者，MySQL提供了多种执行引擎，不同引擎特点不同，根据需要来选择。

存储引擎真正的负责了 MysQL 中数据的存储和提取，服务器 通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

物理文件存储层

在硬盘上存储系统文件，表数据，各种日志文件。

主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

下图是存储在本机的文件.

在这里插入图片描述

MySQL引擎

概述

MySQL中的数据用各种不用的技术存储在文件中,每种技术都使用了不同的 存储机制、索引技巧、锁定水平,最终提供不同的功能。

这些技术与配套的功能称为存储引擎（也叫表类型）。引擎用于存储、处理、保护数据的核心服务。 MySQL默认是InnoDB引擎。
​
查看支持的引擎

SHOW ENGINES;

在这里插入图片描述
查看指定表的引擎

SHOW TABLE STATUS LIKE '表名'

在这里插入图片描述

修改引擎

方式1
将 mysql.ini 中 default-storage-engine=InnoDB，重启服务
方式2
建表时指定 CREATE TABLE 表名(...)ENGINE=MYISAM;
方式3
建表后修改 ALTER TABLE 表名 ENGINE = INNODB;

MySQL中主要的存储引擎（MySQL8）

功能MYISAMMemoryInnoDBArchive
存储限制256TBRAM64TBNone
支持事务NoNoYesNo
支持全文索引YesNoYesNo
支持数字索引YesYesYesNo
支持哈希索引NoYesNoNo
支持数据缓存NoN/AYesNo
支持外键NoNoYesNo

MyISAM

  • 不支持事务
  • 不支持外键
  • 不支持行级锁
    当insert 或 update时，也就是“写操作”时需要锁定整个表，效率会低一些
  • 支持全文检索（索引）;存储表的总行数

InnoDB

是MySQL中的默认存储引擎
InnoDB是支持事务的。
支持行级锁和外键约束
支持全文索引（检索）

InnoDB的设计目标是处理大容量数据库系统，MySQL运行时InnoDB会在内存中建立缓冲池，用于缓冲数据和索引，支持主键自增，不存储表的总行数

对比项MyISAMInnoDB
主外键不支持支持
事务不支持支持
行表锁表锁,即时操作一条记录也会锁住整个表,不适合高并发的操作行锁,操作时只锁某一行,不对其他行有影响,适合高并发的操作
缓存只缓存索引,不缓存真实数据不仅缓存索引还要缓存真实数据,对内存的要求较高,而且内存大小对性能有很大的影响
表空间小大
关注点性能事务
默认安装YesYes

索引

为什么使用索引？

真实的业务场景，数据量会非常大，比如有100万条，这100万条数据存储在磁盘中，一页数据大小为16K,存储这么多数据需要很多数据页，若查询id='200001’的数据，需要全表扫描，从第一个数据页向后逐页查询。对于大量的数据，逐页查询，性能会急剧下降。因此采用索引。

什么是索引？

在Java中有数组索引，字符串索引。都是为了方方便访问里面的数据。

索引是帮助MySQL 高效获取数据的数据结构。
排好序的快速查找的数据结构。

数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

在这里插入图片描述
未来加快Col2的查找，可以维护一个二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到响应数据，从而快速地减速出符合条件的记录。

索引的原理

索引类似树的目录，在一本书前加上目录，查找内容时不必逐页翻找，可以快速地定位到所需要的内容页码，借助索引，执行查询时不必扫描整个表救能够快速地找到需要的数据。

通过不断地缩小想要获取的数据的范围来筛选出最终想要的结果。同时将随机的事件变成顺序的事件，即我们可以总是用同一种查找方式来锁定数据。

索引的优势

  1. 提高了查询效率(使用B+树存储，有序，将索引数据加载到内存中，降低数据库的 IO 成本)

  2. 通过索引列对数据进行排序(),降低数据排序的成本,降低了CPU的消耗

索引的劣势

索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用磁盘空间的。

虽然索引提高了查询速度，同时却会降低更新表的速度，例如对表进行insert,update,delete时，MySQL不仅要保存数据，还要保存索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引的分类

主键索引

设定为主键后数据库会自动建立主键索引

建表后建立主键索引
ALTER TABLE 表名 add PRIMARY KEY 表名(列名); 
删除建主键索引： 
ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引

  • 一个索引只包含单个列，一个表可以有多个单列索引
-- 创建单值索引 
-- CREATE INDEX 索引名 ON 表名(列名); 
删除索引： 
DROP INDEX 索引名 ON 表名;

在这里插入图片描述

唯一索引

索引列的值必须唯一，允许为 null

创建唯一索引 
CREATE UNIQUE INDEX 索引名 ON 表名(列名); 
删除索引 
DROP INDEX 索引名 ON 表名;

在这里插入图片描述

组合（复合）索引

即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小（对于相同的多个列建立索引），当表的行数远大于索引列的数目时可以使用复合索引。

创建复合索引
CREATE INDEX 索引名 ON 表名(列 1,列 2...); 
删除索引： 
DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则
表中有 a,b,c 3 列,为 a,b 两列创建组合索引,那么在使用时需要满足最左侧索引原则.在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则组合索引不生效

索引生效

SELECT * FROM test WHERE a='' AND b='';
SELECT * FROM test WHERE b='' AND a=''; 
SELECT * FROM test WHERE a='' AND c='';

在这里插入图片描述

索引失效

SELECT * FROM test WHERE b='' AND c='';

在这里插入图片描述

全文索引

like会使索引失效
需要模糊查询时，一般索引无效，这时候就可以使用全文索引了

  • 创建索引
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
  • 使用索引
SELECT 结构 FROM 表名 WHERE MATCH(列名) AGAINST(搜索词')

在这里插入图片描述

索引创建原则

需要创建索引的情况

  • 主键自动建立唯一索引 主键索引
  • 频繁作为查询条件的字段(where age=20; )
  • 外键关系建立索引
  • 排序的字段（排序字段若通过索引去访问将大大提高排序速度）
  • 分组的字段

不要创建索引的情况

  • 表记录太少 (eg:后台管理的一些系统参数表)
  • 频繁insert,delete,update的表，更新表时，MySQL需要保存数据和索引文件
  • 不作为查询条件的字段
  • 数据大量重复的字段（性别，年龄）
    应该只为最经常查询和最经常排序的数据列建立索引，某个列包含许多重复的内容，建立索引没有什么太大效果。

索引的数据结构

mysql索引的数据结构是树，常用的存储引擎InnoDB采用的是B+树(B+Tree)。

B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树 （B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构。

二叉查找树(BST)

  • 一颗二叉树
  • 结点的值大于左子树所有节点的值
  • 结点的值小于右子树所有节点的值

二叉查找树：https://blog.csdn.net/lanleihhh/article/details/121302715

平衡二叉树（AVL-Tree）

平衡二叉树是二叉排序树的基础上，对树的深度进行了限制，从而减少了查找比较的次数

  • 是一棵二叉查找树
  • 左子树与右子树的深度差在-1、0、1内，否则对子树进行旋转调整。

平衡多路二叉树（B+Tree）

相对于平衡二叉树，对父结点的直接子结点个数，不再仅限于2，

可以指定m(自定义)，这样可以在树的深度不大量增加的前提下，保存更多的结点。

  • 排好序的,一个节点可以存储多个数据 （横向扩展 使得树的高度降低）
  • 非叶子节点不存储数据,只存储索引,可以放更多的索引
  • 数据记录都存放在叶子节点中. 找到了索引,也就找了数据
  • 所有叶子节点之间都有一个链指针。（适合区间查询 age>20 age<50）
    注意：所有结点均带有值
    在这里插入图片描述
    使用B+树来存储索引，B+树会自动排序
  • B+树的每个节点可以存储多个数据(横向扩展)，树的高度降低了
  • 非叶子节点存储索引，叶子节点存储数据
  • 每个叶子节点之间有指针指向，便于区间范围查询

聚簇索引和非聚簇索引

聚簇索引

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以 主键 就是聚簇索引。

键可以直接找到数据

学号是索引，根据学号只查询学号，这种场景是聚簇索引。

非聚簇索引

索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫辅助索引

学号是索引，根据学号查询姓名，虽然学号加了索引，但是还是要查询姓名。根据学号找到主键，通过主键回表查询姓名，这种场景是非聚簇的。

举例说明两种索引的场景

CREATE TABLE student (
	id BIGINT, 
	NO VARCHAR (20), 
	NAME VARCHAR (20), 
	PRIMARY KEY (id), 
	UNIQUE KEY idx_no (NO ) 
)

在这里插入图片描述

第一种
直接根据主键查询获取所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了 id=1 的所有字段的值

SELECT * FROM student WHERE id = 1

第二种
根据 编号 查询编号和名称，编号本身是一个唯一索引，但查询的列包含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主键 ID，需要根据主键 ID 重新查询一次，所以这种查询下 NO 是非聚簇索引

SELECT NO,NAME FROM student WHERE NO = 123

第三种
我们根据编号查询编号(有人会问知道编号了还要查询？要，你可能需要验证该编号在数据库中是否存在)，这种查询命中编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询，这种场景下 NO 是聚簇索引。

总结:

  • MySQL 中 InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇 式设计.
    在这里插入图片描述
  • 而 MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中
    在这里插入图片描述
    在这里插入图片描述
  • test.MYD，存储数据；
  • test.MYI，存储索引；
lanleihhh
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL进阶篇之存储引擎
moliang1999的博客
02-21 110
：MySql学习之路一：mysql深入高级篇之存储引擎介绍
mysql 存储引擎的选择
weixin_33781606的博客
03-20 316
存储引擎的选择 在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。但建议选择使用InnoDB InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，...
MySQL 存储引擎
qq_42944130的博客
05-25 198
MySQL 存储引擎 1. 介绍 plugin storage engine 存储引擎相当于Linux 文件系统。 插件的模式存在。作用在表的一种属性。 2. MySQL中的存储引擎类型 Oracle MySQL ： mysql> show engines; MRG_MYISAM CSV MyISAM BLACKHOLE PERFORMANCE_SCHEMA InnoDB
MySQL5.7新特性
DATA
08-15 1万+
MySQL5.7新特性 http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html Added Features1.安全提升 mysql.user中plugin列非空，如果是空值将禁用帐户。 密码默认360天过期。可以修改：通过参数全部禁用： [mysqld] default_password_lifetime=0 可以动态修改： def
MySQL大表优化方案
福禄网络研发团队的博客
11-02 1万+
背景 阿里云RDS FOR MySQL（MySQL5.7版本）数据库业务表每月新增数据量超过千万,随着数据量持续增加,我们业务出现大表慢查询,在业务高峰期主业务表的慢查询需要几十秒严重影响业务 方案概述 一、数据库设计及索引优化 MySQL数据库本身高度灵活，造成性能不足，严重依赖开发人员的表设计能力以及索引优化能力,在这里给几点优化建议 时间类型转化为时间戳格式,用int类型储存,建索引增加查询效率 建议字段定义not null，null值很难查询优化且占用额外的索引空间 使用TINYINT类型代替枚
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 172万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql的高级架构.xmind
04-05
mysql高级架构，主要分为5大模块;1.mysql架构介绍、2.索引优化分析、3.查询截取分析、4.mysql锁分析、5.主从复制； 本资源可帮助朋友们提升自我，让自己变得更加优秀！
MySQL高级报包含MySQL架构内容
03-25
包含MySQL的环境搭建过程和步骤,主从复制的原理,及搭建过程,MyCat的使用方法和原理,以及索引的使用规范和优化注意事项
Mysql架构、索引介绍及原理分析.zip
04-23
Mysql架构、索引介绍及原理分析.zipMysql架构、索引介绍及原理分析.zipMysql架构、索引介绍及原理分析.zipMysql架构、索引介绍及原理分析.zipMysql架构、索引介绍及原理分析.zipMysql架构、索引介绍及原理分析....
mysql高级特性 mysql 架构、索引调优、事务、日志备份文档
09-05
mysql 架构、索引调优、事务、日志备份文档
MYSql高级教程-存储引擎和SQL优化
06-28
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部...
python学习-使用pandas库分析excel表，并导出所需的表
SixSix的自留地
05-16 495
使用pandas库分析excel表中多个子表的数据
【MySQL精通之路】管理和实用程序
Anakki的博客
05-19 217
8 mysql_migrate_keyring——keyring密钥迁移实用程序。2 innocchecksum——离线InnoDB文件校验和实用程序。7 mysql_config_editor——mysql配置工具。9 mysqlbinlog——用于处理二进制日志文件的实用程序。6 myisampack——生成压缩的只读MyISAM表。4 myisamchk——MyISAM表格维护实用程序。5 myisamlog——显示MyISAM日志文件内容。3 myisam_ftdump——显示全文索引信息。
MySQL--增、删、改、查，
weixin_65476290的博客
05-15 1154
数据库的概述、发展、现状、历史、分类MySQL关系型数据库、架构（C/S）window系统安装MySQL数据库Linux系统【选学】数据库对象——数据库（database）show、create、drop命令数据库对象——表（table）数据类型数据库表的约束表结构的调整（alter）数据库授权和权限管理（grant）数据库的编码情况【编码一致，建议统一采用utf8mb4】
mysql-8.0.21-winx64 安装
最新发布
hhw199112的博客
05-21 316
但最好能从一个已安装的mysql里把它的my.ini复制一份到自己的安装目录中。该命令执行后，会让输入密码，但是初始化的时候创建的是不带密码的账号root,直接回车即可。将mysql库中的user表，将root所在记录的host改为"%"即可。打开安装目录中的bin文件夹，在当前目录一下打开cmd命令，执行如下命令。该命令用户初始化数据库，创建一个不带密码的账号root。执行该命令，设置root的登录密码是Root_123。还是在bin文件夹中，打开cmd命令，执行如下命令。
MySQL 将json转为行，JSON_TABLE函数详解
常备不懈
05-17 661
`JSON_TABLE` 是 MySQL 8.0 引入的一个强大函数，它用于将 JSON 数据转换为关系表的格式。通过 `JSON_TABLE` 函数，可以将 JSON 文档解析为关系表的列和行，使得在 SQL 查询中处理 JSON 数据更加方便。
快速理解MySQL null的10大坑
weixin_42100387的博客
05-19 228
在 MySQL 中，如果使用了外键约束，并且外键列允许 NULL 值，那么当引用的列中出现 NULL 值时，可能会导致一些意想不到的行为，如无法正确匹配行或者导致级联更新或删除的问题。若一列的所有值都是null，那么sum函数的结果不是0，而是null，所以可能会因为值的类型兼容问题，出现意料之外的情况。对于动态若类型的语言，null，0，false，‘’，都是false，可能会影响一些逻辑。有的说null值就不走索引，经过实测，就算全是null值，也显示的Using index。
mysql 技术内幕:innodb 存储引擎pdf
09-03
在这本书中，作者首先详细介绍了InnoDB存储引擎的设计思想和架构，包括InnoDB存储引擎的物理存储、索引结构和事务处理机制等。读者可以通过这些内容了解到InnoDB存储引擎是如何将数据存储在磁盘上，如何管理和维护...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值