3MySQL数据库- MySQL数据库的架构 - 引擎对比- 全栈式开发32

一、数据库的整体架构

• 架构：四层
  

（一）网络连接层

• 多种语言提链接方式

（二）服务层

• 第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存，以及跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。通过下图来观察服务器的内部结构
  

（三）存储引擎层

• 负责MySQL中数据的存储与提取。服务器中的查询执行引擎通过API与存储引擎进行通讯，通过接口屏蔽了不同存储引擎的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每一种存储引擎有不同的特点，我们可以根据不同的业务特点，选择最适合的存储引擎。如果对存储引擎的性能不满意，可以通过修改源码来得到自己想要发到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对innodb存储引擎进行了扩充
• 最常用的就是innodb和myisam
• 数据库存储引擎被设计为可插拔的架构：在不影响当前业务下，可随时切换当前存储引擎，已达到存储性能的要求

查看存储引擎

• show engines;

切换换引擎

• ALTER TABLE demo1.classes ENGINE = MyISAM ;

（四）系统文件层

• 数据保存的位置
• opt：这个文件不属于任何数据库的任何表。存了默认的字符集编码
  • 我们一般将表设置字符集编码就是utf8_general_ci，支持中文
  • 支持表情字符集编码：utfmb4
• frm：记录表结构，不是MyISAM特有的
• my.ini配置文件：
  • interactive_timeout=120，120秒的交互环境链接超时时间，可以自行增加

二、数据库的锁

什么是锁

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

锁定类型

• 共享锁（也称为读锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响
• 独占锁（也称写锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

锁的粒度

• 表级锁：锁的是整个表，即使操作一条记录也会锁住整个表，不适合高并发的操作
• 行级锁：锁的是某行数据，并发比表级锁好，作时只锁某一行，不会其他行有影响

三、数据库的引擎

如何选择存储引擎

• 人部分情况下，InnoDB都是正确的选择，可以简单地对那为一句话“除非需要用到某些InnoDB不具备的特性，并且没有其他方法可以替代，否则都应该优先选择InnoDB引擎”
• 同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

参考条件

• 事务

  • 如果应用需要事务支持，那么InnoDB（或者XtraDB）是目前最稳定并且经过验证的选择

• 备份

  • 如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。反之，如果需要在线备份，那么选择InnoDB就是基本的要求

• 崩溃修复

  • MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢

应用举例

• 日志型应用—MyISAM
• 只读或者大部分只读的表----MyISAM
• 订单处理----InnoDB

对比项	MyiSAM	innoDB
主外键	不支持	支持
事务	不支持	支持
锁	表锁	行锁、表锁。但是InnoDB的行锁只是在WHERE的主键是有限的，非主键的WHERE都会锁全表的
缓存	只缓存索引，不缓存真实的数据	不仅缓存索引还要缓存真实数据，对内存要求极高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y
存储结构	每张表存放在三个文件：.frm.MYD.MYI	所有的表数据保存在一个数据文件中（有可能是多个文件，或者是独立的表空间文件）InnoDB表的大小只首先与操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份和恢复	由于MyISAM的数据是以文件的形式存储，所以在快平台的数据转移会很方便，在备份和恢复时可以单独针对某个表进行操作	免费的方案可以使拷贝数据提交文件、备份binlog、或者用mysqldump，在数据量达到几十G的时候就相对困难了
AUTO_INCREMENT	MyISAM表可以和其他字段一起建立联合索引	InnoDB必须包含只有该字段的索引
SELECT	MyISAM更优
INSERT		InnoDB更优
UPDATE		InnoDB更优
DELETE		InnoDB更优 ,它不会重新建立表，而是一行一行的删除
COUNT without WHERE	MyISAM更优。因为MyISAM更优保存了表的具体行数
COUNT with WHERE	一样	一样，InnoDB也会锁表
FULLTEXT全文索引	支持	不支持，可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点

（一）MyISAM

• MySQL5.5之前的版本默认存储引擎，后面就是InnoDB
• MyISAM存储引擎表由MYD（表的数据文件）和MYI（表的索引）组成
  • 独有的两个文件
    

特性

• 1.并发性与锁级别：锁级别使用的是表级锁，加锁后会将整张表都上锁，谁都不能动，只有当前执行完毕才可以。并发性不好
• 2.表损坏修复：不常用
• 3.支持表的压缩：节约空间，查询更快。如果压缩后数据发生变化要重新压缩
• 4.插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

  # 检查表状态，可以看是否被损坏
  check table 表名
  # 修复表
  repair table 表名
  

限制

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

适用场景

• 1.非事务型应用
• 2.只读类应用

（二）InnoDB

• 5.5之后版本默认存储引擎支持事务的ACID特性
• InnoDB 使用表空间进行数据存储

表空间

• on 独立表空间
  • 默认on
  • tablename.ibd 默认，特有的
• off 系统表空间：
  • ibdataX X是一个数字，
  • ibdataX 没有保存在响应的数据库文件夹里面，而是在外部data文件夹里面
• 系统表空间和独立表空间如何选择
  • 系统表空间会产生IO瓶颈，刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
  • 独立表空间可以同时多个文件刷新数据

# 查看表空间
show variables like 'inoodb_file_per_table
# 如何切换到系统表空间
set global inoodb_file_per_table=off

特性

• 1.支持事务的ACID特性
• 2.Innodb支持行级锁，可以最大程度的支持并发
• 3.支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

（三）CSV

文件系统存储特点

• 数据以文本方式存储在文件中
• .CSV文空间存储表内容，自己编辑文件会有错
• .CSM文件存储表的元数据如表状态和数据量
• .frm文件存储表结构信息
• 建表的时候不能自增，不支持主键、索引。不予许字段有非空的

应用场景

• 适用于一些中间表

（四）memory

• 也称HEAP存储引擎所以数据保存在内存中，如果mysql服务器重启数据会丢失，但是表结果结构会保存下来，比较危险
• 存在内存上的，所以读取速度要快的多，这也是为什么我们也要学redis、mongoDB，存储在内存的数据库

特性

• 支持HASH等值索引和BTree范围索引，
  • 默认HASH等值索引，innoDB只支持BTree
  • 指定索引方式：create index idx_titile using btree on 表(字段)
• 所有字段都为固定长度varchar(10) = char(10)，两种都一样了
• 不支持BLOB和TEXT等大字段
• memory存储引擎使用表级锁
• 默认最大64M max_nreap_table_size = 64M
• 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。