最新公告
  • 欢迎您光临吆喝资源网,本站提供站长亲测的网站源码下载,打造专业的源码网!立即加入我们
  • 介绍MYSQL进阶的第二篇文章

    免费学习推荐:mysql视频教程

    5 MySQL体系结构

    在这里插入图片描述
    下面我们选一些比较常用的存储引擎进行简单的说明,mysql所使用的存储引擎会对数据库的性能产生直接的影响,还希望各位能仔细的了解存储引擎的一些特点,完了之后才使用存储引擎。

    5.1 MyISAM

    MyISAM在MySQL5.5之前版本是默认的存储引擎。由于这个原因,还有很多服务器在使用MyISAM这个存储引擎。同时,MyISAM目前是很多系统表,临时表所使用的存储引擎,这里说的临时表不是我们通过create table创建出来的表,是指在排序、分组等操作中,当数量超过一定的大小之后,有查询优化器建立的临时表。
    MyISAM存储引擎是由MYD和MYI组成,MYD是数据文件的扩展名,MYI是索引文件的扩展名,这个存储引擎是将表存储在以这两个为扩展名的数据文件和索引文件中。

    特性:

    • 并发性与锁级别
      MyISAM使用的是表级锁,并不是行级锁,这也就意味着对表中的数据进行修改时,需要对整个表进行加锁,而在对表的读取时也对所有的表加共享锁,从这里我们可以看到,使用MyISAM做引擎的表读取和写入两种操作是互斥的。由此可以看到MyISAM对于读写的并发操作并不会很好。如果只对于只读取操作的话,就并发性而言,性能也还不错,因为共享锁不会阻塞共享锁。
    • 表损坏修复
      MyISAM支持由于任意意外关闭而损坏的MyISAM表,进行检查和修复操作,但是这里说的修复并不是数据的恢复,因为MyISAM并不是一种事务性的存储引擎,所以它不能进行事务恢复所需要的相关日志,所以要注意MyISAM表的恢复可能会造成数据的丢失。
      我们可以通过check table tablename对表进行检查,通过repair table tablename对表进行恢复。
    • MyISAM表支持的索引类型
      MyISAM支持全文索引,并且在mysql5.7版本之前唯一原生就支持全文索引的官方存储引擎。
    • MyISAM表支持数据压缩
      如果MyISAM表示一张很大的只读表,也就是在表创建完导入数据后,就不会对表进行任何修改操作了,那我们就可以对表进行压缩了,以减少磁盘I/O。 我们可以使用myisampack命令来对表进行压缩。压缩是对表独立进行压缩的,因此读取一行数据的时候,不必对整个表进行解压。

    限制:

    • 版本 < mysql5.0时默认表大小为4G
    • 如存储大表则要修改MAX_Rows 和 AVG_ROW_LENGTH
    • 版本 > mysql5.0时默认支持位256TB

    适用场景:

    • 非事务性应用
    • 只读类应用(报表之类)
    • 空间类应用

    5.2 Innodb

    Innodb是MySQL5.5及之后版本默认的存储引擎,Innodb是事务存储的存储引擎,也就是说支持事务的处理。
    Innodb有自己的表空间的概念,然后数据是存储在表空间之中的,是由innodb_file_per_table这个参数来决定的,如果这个参数为ON,则会对每个Innodb表建立一个扩展名为ibd的系统文件,如果这个参数为OFF时,则会把数据存储到系统的共享表空间,也就是ibdataXX代表的时一个数字,默认从1开始。
    查看这个参数的命令为:
    show variables like 'innodb_file_per_table';
    修改这个参数的命令为:
    set global innodb_file_per_table=off;

    5.2.1 系统表空间和独立表空间要如何选择

    比较:

    系统表空间 独立表空间
    无法简单的收缩文件大小 可以通过optimize table命令收缩系统文件
    会产生IO瓶颈 可以同时向多个文件刷新数据

    建议:

    • 对Innodb使用独立表空间

    把原来存在于系统表空间中的表转移到独立表空间中的方法。
    步骤:

    1. 使用mysqldump导出所有数据库表数据
    2. 停止MySQL服务,修改参数,并删除Innodb相关文件
    3. 重启MySQL服务,重建Innodb系统表空间
    4. 重新导入数据

    5.2.2 Innodb存储引擎的特性

    • Innodb是一种事务性存储引擎
    • 完全支持事务的ACID特性(之前介绍过的原子性,一致性等)
    • Redo Log 和 Undo Log
      Redo Log实现了事务的持久性,有两部分组成,其中一个是内存中的工作日志持久缓冲区,是由innodb_log_buffer_size决定它的大小,另一个是重构日志文件,也就是我们在文件系统中看到的ib_logflie的相关文件。Undo Log实现了事务的原子性,在事务失败时进行回滚操作。Redo Log是顺序读写的,Undo Log是随机读写的,如果可以的话可以将数据存储在固态硬盘中,以提高性能。
    • Innodb支持行级锁
      行级锁和表级锁是不一样的,行级锁的特点就是可以最大程度的支持并发,行级锁是由存储引擎层实现的。

    5.2.3 Innodb状态检查

    可以使用以下命令对Innodb状态进行检查:
    show engine innodb status

    5.2.4 适用场景

    Innodb适合于大多数OLTP应用,因为在mysql5.7版本之后,Innodb已经支持了全文索引和空间函数。

    5.2.4 (拓展) 什么是锁

    5.2.3.1 什么是锁?

    • 锁最主要作用是管理共享资源的并发访问
    • 锁用于实现事务的隔离性

    5.2.3.2 锁的类型:

    • 共享锁(也称读锁)
    • 独占锁(也称写锁)

    5.2.3.3 写锁和读锁的兼容关系(对一行的兼容性情况)

    写锁 读锁
    写锁 不兼容 不兼容
    读锁 不兼容 兼容

    在实际情况中,可能与上表的结果会有所不同,主要是因为Innodb中的锁机制是很复杂的一样东西,还有很多锁的存在影响最终的结果。

    5.2.3.4 锁的粒度:

    • 表级锁
    • 行级锁

    5.2.3.5 阻塞和死锁

    阻塞:阻塞是因为不同锁之间的兼容性的关系,在有些时刻一个事务中的锁需要等待另一事务的锁释放,它所占用的资源形成了阻塞。
    死锁:死锁是指两个或两个以上的事务执行过程中,相互占用了对方等待的资源而产生的一种异常。从定义中可以看到,处在阻塞中的多个事务占用了被阻塞的事务等待的资源,而死锁是多个阻塞的事务互相占用了对方等待的资源。

    5.3 CSV存储引擎

    CSV存储引擎可以将csv文件作为mysql的表文件来处理,这种存储引擎的存储格式就是普通的csv文件,在csv存储引擎的数据存储方式非常的由特点,如果我们把表存储在MyISAM或者Innodb中,其数据文件我们是不能直接查看的,因为这两种文件的存储是以二进制的格式来存储的,而CSV存储引擎则不同,CSV的数据是以文本的方式存储在文件中的,也就是我们可以通过查看文件的命令来查看,如more,或者使用vi命令来查看编辑csv存储引擎中的表,只要符合CSV文件的格式和要求,我们就不用担心损坏数据。
    当我们在mysql中建立了CSV存储引擎表时,我们应该可以看到3个文件系统中的文件。这3个都是以表名为文件名,但是会分别以csv,csm,frm为后缀,其中csv文件就是CSV存储引擎中的数据文件。csm文件存储表的元数据和表状态和数据量。frm文件存储表结构信息。

    5.3.1 CSV存储引擎的特点

    • 最大的特点是以CSV格式进行数据存储
      CSV中的每一列都是以,来分隔的,并且文本的内容是以双引号来引起来的,如下图所示:
      在这里插入图片描述
    • 所有列必须都是不能为NULL的
      在建表的时候所有的列都必须是非空的,不能存储为NULL的值
    • 不支持索引
      不适合大表,不适合在线处理
    • 可以对数据文件直接编辑
      保存文本文件内容

    5.3.2 CSV存储引擎的适用场景

    CSV存储引擎适合作为数据交换的中间表
    在这里插入图片描述
    在这里插入图片描述

    5.4 Archive存储引擎

    5.4.1 文件系统存储特点

    Archive存储引擎会缓存所有的写,并且利用zlib对插入的行进行压缩,因此Archive存储引擎相对于MyISAM存储引擎的表更加节省磁盘I/O,对于同样数量级的数据,Archive存储引擎相对于MyISAM和Innodb更加节省存储空间。一个几T的Innodb的表存储在Archive存储引擎当中,可能只需要几百兆的存储空间。
    Archive存储引擎的表的数据是以ARZ为后缀的一个文件,和其他引擎一样,也存在一个以frm为后缀的系统文件,用于存储表的结构信息。

    5.4.2 Archive存储引擎的特点

    • 只支持insertselect操作
    • 只允许在自增ID列上加索引

    5.4.3 Archive存储引擎使用场景

    场景1:日志和数据采集类数据
    因为Archive不支持修改和删除,而我们ORDB一定会对数据进行修改的,但是对于一些仓库类型的应用,或者一些特殊的表,还是有用的,比如说,记录日志的表或者是数据采集类的表,因为它需要采集大量数据,所以比较适合使用Archive存储引擎。因为Archive存储引擎在所有引擎中来说,它的存储空间是最小的,但是还是要注意,即使在数据采集或日志的应用中,Archive存储引擎是无法对这些数据进行更新的,所以在记录日志或者在数据采集类应用中对数据进行修改的话,可能也无法使用Archive存储引擎。

    5.5 Memory存储引擎

    5.5.1 文件系统存储特点

    Memory存储引擎也称之为HEAP存储引擎,所以数据都保存在内存中, 这就意味着这中数据的表是一次性的,一旦MySQL服务重启,所有Memory存储引擎的数据都会消失,但是表结构会保留下来,因为在Memory存储引擎下创建表,只会生成一个frm系统文件,该文件是用于保存表结构的。这就是为什么重启MySQL服务器数据会丢失,表结构不会的原因。
    从它的文件存储特点我们可以知道,Memory存储引擎的I/O效率会比MyISAM高很多,因为MyISAM只有索引会保存在内存中,而数据则由操作系统来缓存的,而Memory存储引擎所有数据和索引都保存在内存中,下面我们看一下Memory存储引擎的功能特点。

    5.5.2 Memory的功能特点

    功能特点:

    • 支持HASH索引(默认)和BTree索引
      如果是HASH索引在做等值查询的时候会非常的快,如果是做范围查询的话就无法使用HASH索引了,所以在表创建的时候我们需要注意,如果表需要大量的等值查询就用HASH索引,范围查询就使用BTree索引。不同索引类型会对性能产生很大的影响。
    • 所有字段都为固定长度 varchar(10) = char(10)
      这就要求我们在定义表结构时,一定要符合要求最小的字段长度,否则浪费大量的内存。
    • 不支持BLOG和TEXT等大字段
    • Memory存储引擎使用表级锁
    • 最大大小由max_heap_table_size参数决定
      这个参数的默认值只有16兆,如果我们要在Memory存储引擎表中存储大量数据,就要修改这个参数,而这个参数修改是对已经存在的Memory存储引擎的表是不生效的,如果需要对存在表生效的话就需要通过对已经存在的表进行重建。

    5.5.3 Memory中容易混淆的概念

    Memory存储引擎表:
    对所有的系统都可以使用,它并不是一种临时表。
    临时表:
    临时表分为两种,一种是查询优化器在优化查询时所使用的系统使用临时表,也就是内部临时表,系统使用临时表在超过限制(使用BLOB或TEXT大字段)时使用MyISAM临时表,未超限制使用Memory表。
    另一种是通过命令create temporary table建立的临时表,建立的表可以使用任何存储引擎。
    无论是哪种临时表,只对内部可见。

    5.5.4 Memory的使用场景

    • 用于查找或者映射表,例如邮编和地区的对应表
    • 用于保存数据分析中产生的中间表
    • 用于缓存周期聚合数据的结果表

    Memory数据易丢失,所以要求数据可再生。

    5.6 Federated存储引擎

    5.6.1 Federated的特点

    • 提供了访问远程MySQL服务器上表的方法
      由于Federated存储引擎只是在本地建立了到远程服务器的一个连接,所以可以说我们所要访问的表全部还是放在远程服务器上,在本地并不存储数据。每次访问Federated存储引擎表的时候,查询都会被发送到远程服务器上运行,并从远程的MySQL服务器上获取相关的数据。
    • 本地不存储数据,数据全部放到远程服务器上
    • 本地需要保存表结构和远程服务器的连接信息
      因此也会在系统中存在一个frm文件,用于存于远程信息以及如何连接远程表的相关信息。

    5.6.2 Federated如何使用

    Federated存储引擎可以实现SQL Server连接服务器的功能,但是由于本身的性能并不太好,通常可以通过复制等实现相同的目的,所以在当前的MySQL版本中,Federated存储引擎默认是禁止的。如果需要使用Federated存储引擎,则需要在/usr/local/mysql/my.cnf中加入federated=1,接着重启MySQL服务器,我们可以通过show engine来确认当前MySQL服务器是否支持Federated存储引擎。
    而在create table语句中使用下面的连接字符串,
    mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
    远程服务器绑定连接:
    grant select,update,insert,delete on remote.remote_fet to [email protected]'127.0.0.1' identified by '123456'
    就可以决定查询的远程服务器的相关信息以及相关的数据库表的一些信息。

    5.6.3 Federated的适用场景

    • 偶尔的统计分析及手工查询
      由于Federated的性能较慢,只适用于偶尔的统计分析及手工查询。

    6 如何选择正确的存储引擎

    参考条件:

    • 事务
    • 备份
    • 崩溃恢复
    • 存储引擎的特有特性
      尽量避免混合使用存储引擎。

    以上就是介绍MYSQL进阶的第二篇文章的详细内容,更多请关注吆喝资源网其它相关文章!

    本文转自PHP中文网,吆喝网(http://yaoohe.com)搜集整理,如有疑问请联系本站客服!

    1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
    2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
    3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
    4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
    5. 如有链接无法下载、失效或广告,请联系管理员处理!
    6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
    7. 如遇到加密压缩包,默认解压密码为"yaoohe.com",如遇到无法解压的请联系管理员!
    8. 特别声明:本站源码除标明“已测试”外,其它都未测试,有BUG概本站概不负责。
    吆喝资源网 » 介绍MYSQL进阶的第二篇文章

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    吆喝资源网
    一个高级程序员模板开发平台

    发表评论

    • 6765会员总数(位)
    • 19292资源总数(个)
    • 60本周发布(个)
    • 0 今日发布(个)
    • 1823稳定运行(天)

    专业的网站源码网,提供精品php网站源码下载,小程序源码下载等!!

    小程序源码 了解详情
    升级SVIP尊享更多特权立即升级