介绍MYSQL进阶的第二篇文章-tutorial mysql-php.cn

介绍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时，则会把数据存储到系统的共享表空间，也就是ibdataX，X代表的时一个数字，默认从1开始。
查看这个参数的命令为：
show variables like 'innodb_file_per_table';
修改这个参数的命令为：
set global innodb_file_per_table=off;

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

比较：

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

建议：

对Innodb使用独立表空间

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

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

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存储引擎的特点

只支持insert和select操作
只允许在自增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 fred_link@'127.0.0.1' identified by '123456'
就可以决定查询的远程服务器的相关信息以及相关的数据库表的一些信息。

5.6.3 Federated的适用场景