赞
踩
目录
2.InnoDB 引擎:具备外键支持功能的事务存储引擎(重点)
MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件,之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中,这个目录就称为 数据目录。
数据目录 对应着一个系统变量 datadir,可以通过下面的命令查看自己的数据目录
show variables like 'datadir';
安装目录 下非常重要的 bin 目录,它里边存储了许多关于控制客户端程序和服务器程序的命令 (许多可执行文件,比如mysql,mysqld,mysqld_safe等)。
使用 CREATE DATABASE 新建一个数据库时,MySOL会做两件事:
我们的数据其实都是以 记录的形式 插入到表中的,每个表的信息其实可以分为两种:
MySQL 是典型的 C/S 架构,即 Client/Server 架构,服务器端程序使用的 mysqld
客户端进程向服务器进程发送段文本 (SQL语句),服务器进程处理后再向客户端进程发送一段文本 (处理结果)
注:从MySQL 5.7.20版本开始,查询缓存已经被官方标注为被弃用的特性,并在8.0版本中被完全移除。现在一个流行的设计模式是在memcached或Redis中缓存数据。
MySQL Server 结构可分为如下三层:
系统(客户端)访问 MySQL 服务器前,做的第一件事就是建立 TCP 连接。 经过三次握手建立连接成功后, MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
用户名或密码不对,会收到一个Access denied for user错误,客户端程序结束执行
用户名密码认证通过,会从权限表查出账号拥有的权限与连接关联,之后的权限判断逻辑,都将依赖于此时读到的权限
多个系统都可以和MySOL服务器建立连接,每个系统建立的连接肯定不止一个。所以,为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySOL服务器里有专门的 TCP连接池限制连接数采用长连接模式复用TCP连接,来解决上述问题。
连接管理的职责是:负责认证、管理连接、获取权限信息
SELECT id,name FROM student WHERE gender = '女';
这个SELECT查询先根据WHERE语句进行 选取 ,而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性 投影 ,而不是将属性全部取出以后再进行过滤,将这两个查询条件 连接 起来生成最终查询结果。
从MySQL 5.7.20开始,不推荐使用查询缓存,并在 MySQL 8.0中删除 。 原因如下:
和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用,主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。同时开源的 MySQL 还允许开发人员设置自己的存储引擎
这种高效的模块化架构为那些希望专门针对特定应用程序需求(例如数据仓库、事务处理或高可用性情况)的人提供了巨大的好处,同时享受使用一组独立于任何接口和服务的优势存储引擎
插件式存储引擎层( Storage Engines),真正的负责了MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。
所有的数据、数据库、表的定义、表的每一行的内容、索引,都是存在文件系统上,以文件 的方式存在的,并完成与存储引擎的交互。当然有些存储引擎比如InnoDB,也支持不使用文件系统直接管理裸设备,但现代文件系统的实现使得这样做没有必要了。在文件系统之下,可以使用本地磁盘,可以使用 DAS、NAS、SAN等各种存储系统。
MySQL的查询流程:
1. 查询缓存:Server 如果在查询缓存中发现了这条 SQL 语句,就会直接将结果返回给客户端;如果没有,就进入到解析器阶段。因为查询缓存往往效率不高,所以在 MySQL8.0 之后就抛弃了这个功能。
2. 解析器:在解析器中对 SQL 语句进行语法分析、语义分析
select user_id,name from users group by user_id;
如果SQL语句正确,则会生成一个这样的语法树:
3. 优化器:在优化器中会确定 SQL 语句的执行路径,比如是根据 全表检索 ,还是根据 索引检索等
在查询优化器中,可以分为 逻辑查询优化阶段 和 物理查询优化阶段
4. 执行器: 截止到现在,还没有真正去读写真实的表,仅仅只是产出了一个执行计划。于是就进入了执行器阶段
在执行之前需要判断该用户是否 具备权限 。如果没有,就会返回权限错误。如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,调用存储引擎API对表进行读写。存储引擎 API 只是抽象接口,下面还有个存储引擎层,具体实现还是要看表选择的存储引擎。在 MySQL8.0 以下的版本,如果设置了查询缓存,会将查询结果进行缓存。
总结:SQL 语句在 MySQL 中的流程是:SQL语句→查询缓存(8.0之前)→解析器→优化器→执行器
存储引擎就是存储数据、建立索引、更新/查询数据 等技术的实现方式,他是mysql数据库的核心,负责 接收上层传下来的指令,然后 对表中的数据进行提取或写入操作。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。我们可以在创建表的时候,来指定选择的存储引擎,如果 没有指定将自动选择默认的存储引擎。
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5及之后,InnoDB是默认的 MySQL 存储引擎。
除了增加和查询外,还需要更新、删除操作,那么,应优先选择InnoDB存储引擎。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎
为了保存表结构, InnoDB 在 数据目录 下对应的数据库子目录下创建了一个专门用于 描述表结构的文件
表名.frm
比方说我在数据库qq下面创建一个名为test的表,数据库qq对应的子目录下就会创建一个名为 test.frm 的用于描述表结构的文件
注:MySQL8把frm文件合并到 ibd 文件中了。
为了更好的管理这些页,lnnoDB提出了一个表空间 的概念,这个表空间是一个抽象的概念,它可以对应文件系统上一个或多个真实文件(不同表空间对应的文件数量可能不同),负责存储表中的数据和索引。每一个表空间可以被划分为很多个页,我们的表数据就存放在某个表空间下的某些页里。这里表空间有几种不同的类型:
①系统表空间 (system tablespace)
默认情况下,InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件,这个文件就是对应的系统表空间 在文件系统上的表示。这个文件是 自扩展文件,当不够用的时候它会自己增加文件大小。
在一个MySQL服务器中,系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中,我们表中的数据都会被默认存储到这个系统表空间
②独立表空间(file-per-table tablespace)
在MySOL5.6.6以及之后的版本中,lnnoDB并不会默认的把各个表的数据存储到系统表空间中,而是 为每一个表建立一个独立表空间,也就是说我们创建了多少个表,就有多少个独立表空间。使用 独立表空间来存储表数据的话,会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件,文件名和表名相同,只不过添加了一个ibd的扩展名而已:
表名.ibd
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM 不支持事务、行级 锁、外键 ,有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。是MySQL5.5之前默认的存储引擎
①不支持事务,不支持外键
②支持表锁,不支持行锁
③访问速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用
MySQL8之前:
xxx.frm:存储表结构信息
MySQL8:
xxx.sdi:存储表结构信息
在MyISAM中的索引全部都是二级索引,该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件,同时表数据都存放在对应的数据库子目录下。
其中 .MYD 是表的数据文件,也就是我们插入的用户记录。采用独立表存储模式,每个表对应一个MYD文件; .MYI 是表的索引文件,我们为该表创建的索引都会放到这个文件中。
Memory采用的逻辑介质是 内存 , 响应速度很快 ,但是当mysqld守护进程崩溃的时候 数据会丢失 。另 外,要求存储的数据是数据长度不变的格式,比如,Blob和Text类型的数据不可用(长度不固定的)。
- Memory同时 支持哈希(HASH)索引 和 B+树索引 。
- Memory表至少比MyISAM表要 快一个数量级 。
- MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数,分别是 max_rows 和
- max_heap_table_size 。其中,max_rows可以在创建表时指定;max_heap_table_size的大小默认为16MB,可以按需要进行扩大。
- 数据文件与索引文件分开存储。
缺点:其数据易丢失,生命周期短。基于这个缺陷,选择MEMORY存储引擎时需要特别小心。
另外,InnoDB 本身不支持 Hash 索引,但是提供自适应 Hash 索引。如果某个数据经常被访问,当满足一定条件的时候,就会将这个数据页的地址存放到Hash 表中。这样下次查询的时候,就可以直接找到这个页面的所在位置,这样让 B+ 树也具备了 Hash 索引的优点。
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
InnoDB: 是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要 求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
MyISAM :如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。现在已经被mongodb替代
MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。现在已经被redis替代
官方介绍索引是帮助MySQL高效获取数据的数据结构,可以简单理解为"排好序的快速查找数据结构"。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。进行数据查找时,首先查看查询条件是否命中某条索引,符合则通过索引查找 相关数据,如果不符合则需要 全表扫描,即需要一条一条地查找记录,直到找到与条件符合的记录
索引 是在 存储引擎 中实现的,因此每种存储引擎的索引不一定完全相同,并且每种存储引擎不一定支持所有索引类型。同时,存储引擎可以定义每个表的 最大索引数 和 最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。
如上图所示,数据库没有索引的情况下,数据 分布在硬盘不同的位置上面,读取数据时,摆臂需要前后摆动查找数据,这样操作非常消耗时间。如果 数据顺序摆放,那么也需要从1到6行按顺序读取,这样就相当于进行了6次I/O操作,依旧非常耗时。
如果给字段Col2添加了索引,就相当于在硬盘上为Col2维护了一个索引的数据结构,即这个二叉搜索树。二叉搜索树的每个结点存储的是(K,V)结构,key是Col2,value 是该 key 所在行的文件指针(地址)。比如:该二又搜索树的根节点就是:(34,0x07)。现在对 Col2添加了索引,这时再去查找 Col2= 89 这条记录的时候会先去查找该二又搜索树(二又树的遍历查找)。读 34到内存,89>34;继续右侧数据,读 89 到内存,89 = 89;找到数据返回。找到之后就根据当前结点的value快速定位到要查找的记录对应的地址。我们可以发现,只需要 查找两次就可以定位到记录的地址,查询速度就提高了。
这就是我们为什么要建索引,目的就是为了 减少磁盘I/O的次数 ,加快查询速率。每个节点的读取可以视为一次I/O读取,因此树的高度与最多的I/O次数相关。
优势
劣势
注:索引可以提高查询的速度,但是可能会影响插入、修改、删除记录的速度。因为需要维护索引。
每一行存储记录的格式叫行格式,下面是简化后的行格式 Compact:
0
表示普通记录、1
表示目录项记录、2
表示最小记录、3
表示最大记录。index_demo
表中的三个列,分别是c1
、c2
和c3
。我们把一些记录放到页里,页内的每条记录都是逻辑连续的,但在物理存储上不是连续的,数据页之间也不是连续的,而是通过双向链表连接。
并且每个数据页还会维护一个 页目录(广泛来讲也叫数据页),里面的数据按照从小到大的顺序排序,这样可以通过 二分法 来快速定位页内的某些记录,比一条一条遍历的时间效率高很多
当我们往表里再插入一些数据后,很可能会出现下面这种情况
因为我们现在是按照主键的方式存储,我们需要保证记录按主键递增的方式存储(实际上这是建立主键索引的必要条件),所以我们需要进行一些记录移动,整个过程,我们称之为页分裂
当数据页越来越多的时候,我们可以给每一个数据页做一个对应的目录项,用于快速定位某些记录的所在页,目录项之间通过双向链表连接。这些目录项其实就叫做 索引
同样的,目录项也维护了一个自己的目录页(连续空间的数组结构)用于二分查找。
每个目录项包括下边两个部分:
当记录越来越多时,由于这些目录项是不连续的(通过链表连接), 我们需要能根据主键快速定位一个目录项。对此,我们可以为这些目录项再生成一个更高级的目录,如下图
我们可以用下边这个图来描述它:
这个数据结构,它的名称就是 B+树
不论是存放 用户记录 的数据页,还是存放 目录项记录 的数据页,我们都把它们存放到B+树这个数据结构中了,所以我们也称这些数据页为节点。从图中可以看出,我们的实际用户记录其实都存在了B+树的叶子节点上,其余用来存放 目录项 的节点称为 非叶子节点 或者内节点,其中B+树最上边的那个节点也称为 根节点。
假设所有存放用户记录的叶子节点代表的数据页可以存放 100条用户记录,所有存放目录项记录的内节点代表的数据页可以存放 1000条目录项记录,那么:
如果B+树只有1层,也就是只有1个用于存放用户记录的节点,最多能存放100 条记录
如果B+树有2层,最多能存放 1000 x 100=10 0000 条记录
如果B+树有3层,最多能存放 1000 x 1000 x 100=1 0000 0000 条记录
如果B+树有4层,最多能存放 1000 x 1000 x 1000 x100=1 0000 0000 条记录。相当多的记录!
一般情况下,我们用到的B+树都不会超过 3 层,当然实际情况中每个节点可能不能填充满,因此在数据库中,B+ Tree的高度一般在2~4层,又因为InnoDB存储引擎设计时是将根节点常驻内存的,那我们通过主键值去查找某条记录最多只需要 1~3次 I/O 操作
又因为在每个页面内有所谓的 Page Directory (页目录),所以在页面内也可以通过二分法实现快速定位记录。
索引按照物理实现方式,索引可以分为2种:聚簇(聚集)和非聚簇(非聚集)索引。我们也把非聚集索引称为二级索引或者辅助索引。
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式(所有的用户记录都存储在了叶子节点),也就是所谓的 索引即数据,数据即索引。
特点
1. 使用记录主键值的大小进行记录和页的排序,这包括三个方面的含义:
2. B+树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)。
优点
缺点
聚簇索引 只能在搜索条件是主键值时才能发挥作用,因为B+树中的数据都是按照主键进行排序的。如果我们想以别的列作为搜索条件,可以再建几棵B+树,不同的B+树中的数据采用不同的排序规则。比方说我们用c2 列的大小作为数据页、页中记录的排序规则,再建一棵B+树。
二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:
主键值的存在是为了保证新插入的记录能找到自己在哪个页里,比如我们想插入一行记录,其中c1、c2、c3的值分别:‘9’、‘1’、‘c’,页3中的两条目录项记录对应的c2列的值都是1,新插入的这条记录的c2列的值也是1,那就不知道该插入到哪个页中了。
但是当我们加上主键后,可以根据主键来确定该插入的页,‘9’>‘7’,应该放入页5内
回表
我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值,所以如果我们想根据c2列的值查找到完整的用户记录的话,仍然需要到 聚簇索引 中再查一遍,这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树!
非聚簇索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个非聚簇索引。
联合索引实际上也是一种二级索引(非聚簇索引)。我们可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照 c2和c3列 的大小进行排序,这个包含两层含义:
- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下,采用c3列进行排序
为c2和c3列建立的索引的示意图如下:
前边介绍B+树索引的时候,为了更容易理解,就先把存储用户记录的叶子节点都画出来,然后接着画存储目录项记录的内节点,自下而上。实际上B+树的形成过程是自上而下的:
这个过程特别注意的是:一个B+树索引的根节点自诞生之日起,便不会再移动。这样只要我们对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,然后凡是 InnoDB 存储引擎需要用到这个索引的时候,都会从那个固定的地方取出根节点的页号,从而来访问这个索引。
再强调一点:索引 是在 存储引擎 中实现的,因此每种存储引擎的索引不一定完全相同,并且每种存储引擎不一定支持所有索引类型。
即使多个存储引擎支持同一种类型的索引,但是他们的实现原理也是不同的。Innodb 和 MylSAM默认的索引是B+ tree索引;而Memory默认的索引是Hash索引。
MyISAM引擎使用B+Tree 作为索引结构,叶子节点的data域存放的是数据记录的地址
我们知道 InnoDB中索引即数据,也就是聚簇索引的那棵B+树的叶子节点中包含了所有完整的用户记录,而 MyISAM 的索引方案虽然也使用树形结构,但是却 将索引和数据分开存储,所以在MyISAM存储引擎中,并没有数据页的概念:
这里设表一共有三列,假设我们以Col1为主键,上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主键索引和二级索引(Secondary key) 在结构上没有任何区别,只是主键索引要求key是唯一的,而二级索引的key可以重复。如果我们在Col2上建立一个二级索引,如下图:
索引虽然能加速查询,但不能乱建,它在空间和时间上都会有消耗:
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。