超详细的MySQL工作原理体系结构( 二 ) _MySQL工作原理

文章插图

创建新表时如果不指定存储引擎，那么系统就会使用默认存储引擎，MySQL5.5 之前的默认存储引擎是 MyISAM，5.5 之后改为了 InnoDB 。MySQL 中同一个数据库，不同的表格可以选择不同的存储引擎。

文章插图

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用。每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表定义。数据文件的扩展名为.MYD (MYData) 。索引文件的扩展名是.MYI (MYIndex)
InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM 的存储引擎，InnoDB 写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。InnoDB：所有的表都保存在同一个数据文件中，InnoDB 表的大小只受限于操作系统文件的大小限制。Myisam 只缓存索引，不缓存真实数据；Innodb 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
MEMORY 存储引擎使用存在于内存中的内容来创建表。MEMORY 类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失。主要用于那些内容变化不频繁的代码表或者作为统计操作的中间结果表。三、MySQL内存结构MySQL中内存大致分为：全局内存（Global buffer）、线程内存（Thread buffer）两大部分。

文章插图

全局内存：缓冲池里面有数据缓存、索引缓存、锁信息、插入缓存等等。此外还有重做日志缓存、额外的内存池。线程内存：Master Thread、IO Thread、Purage Thread、Page Cleaner Thread 。
CheckPoint技术：缓冲池的设计目的为了协调CPU速度与磁盘速度的鸿沟。因此跟新或者删除的时候直接操作的是内存的数据，先写入重做日志，然后再修改内存池里面的数据，最后定时刷新到磁盘上。
四、MySQL文件结构

参数文件：启动MySQL实例的时候，指定一些初始化参数，比如：缓冲池大小、数据库文件路径、用户名密码等。my.cnf读取优先级是从左自右的顺序,但是当默认读取路径都有配置文件时,最后读取的参数的值，会覆盖前面读取的参数的值。/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf~/.my.cnf
日志文件：比如：错误日志、二进制日志、慢查询日志、查询日志等等。例如：通过show variables like "error_log"来查看错误日志存放内容。
socket文件：当用UNIX域套接字方式进行连接的时候需要的文件。
pid文件：MySQL实例的进程ID文件。
表结构文件：用来存放MySQL表结构定义文件。.frm后缀命名的文件都是表结构文件，和存储引擎类型无关。所有的表都会生成一个.frm文件；
存储引擎文件：存储引擎正在存储了记录和索引等数据。

表空间可以在逻辑上管理多个数据文件，而这些数据文件又可以分布在不同磁盘中这就使得一个表的数据、索引等信息可以被记录在多个磁盘中。
（1）共享表空间：共享表空间文件以.ibdata*来命名；共享表空间下，innodb所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。共享表空间主要存放double write、undo log（undo log没有独立的表空间，需要存放在共享表空间）
（2）独立表空间：每个表拥有自己独立的表空间用来存储数据和索引。
（3）查看数据库是否启用独立表空间：show variables like ‘innodb_file_per_table’;查看，innodb_file_per_table=ON,表示启用了独立表空间；
（4）使用独立表空间的优点：a.如果使用软链接将大表分配到不同的分区上，易于管理数据文件b.易于监控解决IO资源使用的问题；c.易于修复和恢复损坏的数据；d.相互独立的，不会影响其他innodb表；e.导出导入只针对单个表，而不是整个共享表空间；f.解决单个文件大小的限制；g.对于大量的delete操作，更易于回收磁盘空间；h.碎片较少，易于整理optimize table；i.易于安全审计；j.易于备份如果在innodb表已创建后设置innodb_file_per_table，那么数据将不会迁移到单独的表空间上，而是续集使用之前的共享表空间。只有新创建的表才会分离到自己的表空间文件。