公司网站的具体的建设方案,网络整合营销策划书,编程培训机构需要哪些证件,wordpress 用户 关注引言
在文件系统中#xff0c;MySQL将每个数据库#xff08;也可以称之为schema#xff09;保存为数据目录下的一个子目录。创建表时#xff0c;MySQL会在数据库子目录下创建一个与表同名的.frm文件保存表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义…引言
在文件系统中MySQL将每个数据库也可以称之为schema保存为数据目录下的一个子目录。创建表时MySQL会在数据库子目录下创建一个与表同名的.frm文件保存表的定义。因为MySQL使用文件系统的目录和文件来保存数据库和表的定义在Windows中大小写不是敏感的而在Linux系统中则大小写敏感。不同的存储引擎保存数据和索引的方式是不同的但表的定义则是在MySQL服务器层统一处理的。
InnoDB作为事务型数据的首选存储引擎是中高级程序员必须掌握的知识与之经常一同提起的MyISAM也是在应用场景中频繁会接触的典型存储引擎。
在《高性能MySQL》第五章中有关于这两种引擎的索引描述本篇博客将结合书中内容进行总结和概括帮助更好地理解其内部的存储方式。
一、查看数据库存储引擎的SQL语句
SHOW ENGINES; SHOW VARIABLES LIKE %storage_engine%; 另外还可以通过SHOW TABLE STATUS来查看表的状态信息里面会包含与表相关的动态信息展示
SHOW TABLE STATUS LIKE teacher;
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
------- ------ ------- ---------- ------ -------------- ----------- --------------- ------------ --------- -------------- ------------------- ----------- ---------- --------------- -------- -------------- ---------
teacher InnoDB 10 Dynamic 18 910 16384 0 81920 0 19 2020-05-24 16:13:32 (NULL) (NULL) utf8_general_ci (NULL) Row_format 可选三个值Dynamic、Fixed或者Compressed。Dynamic表示行长度是可变的一般包含可变长度的字段如VARCHAR或 BLOB。Fixed表示行长度固定只包含固定长度的列如CHAR和INTEGER。Compressed只在压缩表中存在。 Rows表中行数MyISAM是一个精确值InnoDB是估计值。 Avg_row_length平均每行包含的字节数。 Data_length表数据的大小。 Max_data_length表数据的最大容量该值与存储引擎有关。 Index_length索引的字节数。 Data_free对于MyISAM表表示已分配但目前没有使用的空间。这部分空间包括了之前删除的行以及后续可以被INSERT 利用到的空间。 Auto_increment下一个AUTO_INCREMENT的值。 二、InnoDB 和 MyISAM 存储引擎的比较
关于InnoDB和MyISAM的常规比较下表是重点 2.1 InnoDB
InnoDB是MySQL默认的事务型引擎也是最重要、使用最广泛的存储引擎被设计成用来处理大量短期事务短期事务大部分情况是正常提交的很少会被回滚。
InnoDB采用MVCC多版本并发控制来支持高并发并且实现了四个标准的隔离级别。其默认级别是可重复读并且通过间隙锁next-key locking策略防止幻读的出现。
间隙锁使得InnoDB不仅仅锁定查询涉及的行还会对索引中的间隙进行锁定以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的。InnoDB的索引结构和MySQL的其他存储引擎有很大不同聚簇索引对主键查询有很高的性能。不过它的二级索引secondary index非主键索引中必须包含主键列所以如果主键列很大的话其他的所有索引都会很大。因此如果表上的索引较多的话主键应当尽可能的小。
InnoDB的存储格式是平台无关的因此可以将数据和索引文件在不同的平台上复制迁移。
InnoDB内部做了很多优化包括从磁盘读取数据时采用的可预测性预读能够自动在内存中创建hash索引以加速读操作的自适应哈希索引adaptive hash index以及能够加速插入操作的插入缓冲区insert buffer等。
2.2 MyISAM
在MySQL5.1 及之前的版本MyISAM是默认的存储引擎。
MyISAM提供了大量的特性全文索引、压缩、空间函数等。但MyISAM不支持事务和行级锁而且崩溃后无法安全恢复。但对于只读的数据或表比较小、可以忍受修复操作依然可以选择MyISAM。
MyISAM会将表存储在两个文件中数据文件和索引文件。分别以.MYD和.MYI为扩展名。
MyISAM表可以包含动态或静态固定长度行。MyISAM表可以存储的行记录数一般受限于可用的磁盘空间或者操作系统单个文件的最大尺寸。
作为MySQL最早的存储引擎之一MyISAM有一些已经开发出来很多年的特性 ①加锁和并发MyISAM对整张表加锁而不是针对行。读取时会对需要读到的所有表加共享锁写入时则对表加排他锁。但在表有读取查询的同时也可以往表里插入新的记录这被称为并发插入——CONCURRENT INSERT。 ②修复MyISAM可以手工或自动执行检查和修复操作但这里的修复并不是事务恢复或崩溃恢复。执行表的修复可能导致一些数据丢失而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误如果有错误可以通过执行REPAIR TABLE mytable进行修复。 ③索引特性对于MyISAM即使BLOB和TEXT等长字段也可以基于前500个字符创建索引。MyISAM也支持全文索引这是一种基于分词创建的索引可以支持复杂的查询。 ④延迟更新索引键Delayed Key Write创建MyISAM表的时候如果指定了DELAY_KEY_WRITE选项在每次修改执行完成时不会立刻将修改的索引数据写入磁盘而是会写到内存中的缓冲区。只有在清理缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式极大的提升了写入性能但在数据库或主机崩溃时会造成索引损坏需要执行修复操作。延迟更新索引的特性可以在全局设置也可以在单个表设置。 如果表在创建并导入数据之后不会再进行修改操作那么这样的表或许适合采用MyISAM压缩表。
可以使用myisampack对MyISAM表进行压缩也叫打包pack。压缩表中的数据是不可以直接修改的但可以先解压缩、修改数据、再压缩。
压缩表可以极大的减少磁盘空间占用因此也可以减少磁盘IO从而提升查询性能。压缩表也支持索引但索引也是只读的。
以目前的硬件能力大多数场景下读取压缩表数据时的解压开销影响并不大而减少IO带来的好处是非常明显的。压缩表中的记录是独立记录的所以读取单行的时候不需要解压整张表甚至不需要解压行所在的页面。
MyISAM引擎设计简单数据以紧密格式存储所以在某些场景下的性能很好。但MyISAM最典型的性能问题是表锁的问题如果你发现所有的查询都长期处于“Locked”状态那么毫无疑问表锁是罪魁祸首。
三、InnoDB 和 MyISAM 的数据分布
在《高性能MySQL》第五章作者围绕着数据在两种截然不同的存储引擎中是如何存储的进行了细致的分析。
先来说说MyISAM存储引擎。它对表中数据有单独的存储文件所谓“单独的” 指的是数据和主键是分开存储的。这一点与InnoDB有着本质的区别。这在数据库领域叫做——非聚簇索引。
我思考了一下如果让我去设计一个存储引擎根据我的知识水平多半就是会设计成MyISAM这样的数据存储结构。我们先来看一下它是如何来存储数据和主键的 首先不论在InnoDB还是在MyISAM中索引都是以B树的形式来存储的这没什么好说的参考《MySQL 高级 —— 索引实现的思考》然后我们看到主键索引树中的叶子节点都会指向具体的数据行。
也就是说MyISAM分开存储了主键列和数据行然后通过在主键索引的叶子节点中同时保存列值主键值和指向数据行的指针从而实现关联。这在计算机领域是一种非常典型的键值关联的方式。这也是为什么我说如果要我来设计存储引擎可能多半也是这样做的原因可以说MyISAM的数据存储方式是非常简单的。
MyISAM的二级索引的叶子节点同样保存了指向数据行的指针。因此本质上MyISAM的主键索引和普通的二级索引或者叫辅助索引没有太大的区别。从上图中也可以看出。 什么是二级索引 二级索引也叫辅助索引是除主键索引以外的其他类型的索引。 InnoDB存储引擎相对于MyISAM就要复杂许多。
首先它以聚簇索引的形式来组织数据其次作为聚簇索引的主键索引与二级索引也是有许多不同点 InnoDB的聚簇索引就是主键索引其叶子节点包含主键的列值、事务ID、回滚指针、以及所有数据列。
可以说InnoDB整个表的逻辑结构就是通过主键的聚簇索引方式来存储的在InnoDB中聚簇索引就是表。
所谓“聚簇”意思就是数据与主键存储在一起。 另外如果InnoDB的主键是一个列前缀索引InnoDB还是会包含完整的主键列和剩下的其他列。这里的列前缀我的理解是主键列并不是完整的作为索引列而是“前缀”作为索引列。比如主键列值是123456那么这里的列前缀可以是123即仅取主键列的前缀作为索引。 InnoDB的二级索引与MyISAM的二级索引有所不同它不是类似于MyISAM那样在叶子节点中保存“行指针”而是保存主键值以此来作为“指针”。这是因为当出现行移动或数据页分裂时可以避免对二级索引的维护操作。但这样的代价可能是会让二级索引占用更多的空间。 对于非叶子节点它包含了索引列和一个指向下级节点的指针这对所有的 B树索引都适用。 四、InnoDB为什么更推荐顺序递增id
InnoDB更推荐使用自增id作为聚簇索引的主键。
我们知道B树索引是按照索引列递增的顺序进行存储的InnoDB的主键索引也不例外。
在向InnoDB插入数据时自增的 id 可以更快速地直接在数据末尾追加。MySQL数据的存储以页为单位当页被插满达到页的最大填充因子默认15/16下一条记录就会写入新的页。
而如果使用随机值如UUID作为主键因为新行的UUID不一定比之前插入的记录大所以InnoDB无法简单的把新行插入到索引的最后而是需要为新行寻找合适的位置。通常是已有数据的中间位置。那么之前已经写满的并且已经刷到磁盘上的页可能会被重新读取。这会增加很多额外工作并会导致数据分布不够优化。 随机主键的缺点如下 1、写入的目标页可能已经刷到磁盘上并且从缓存中移除或者还没有被加载到缓存中就必须要先从磁盘中读取目标页导致大量的随机IO。 2、因为写入是随机的InnoDB不得不频繁的做页分裂操作以便为新的行分配空间。页分裂会移动大量的数据一次插入最少需要修改三个页面而不是一个。 3、由于频繁的页分裂页会变得稀疏并被不规则地填充所以最终数据会有碎片。因此可能还需要做一次OPTIMIZE TABLE 来重建表并优化页的填充。 什么是 OPTIMIZE TABLE 语法OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name 简单的说由于大量修改数据如删除、移动等造成的存储空间利用不均导致的数据碎片。那么就可以使用OPTIMIZE TABLE 来优化数据表从而更好的利用未使用的空间整理数据文件的碎片。 一般情况下根本不需要运行 OPTIMIZE TABLE即使对可变长度的行进行了大量的更新也不需要频繁运行每周一次或每月一次即可。只对MyISAM、BDB、InnoDB表有效。OPTIMIZE TABLE时MySQL会锁表。 另外顺序主键也不一定是完全无害的在高并发场景顺序插入可能会造成明显的争用主键的上界会成为热点这可能会使并发插入导致间隙锁竞争。
还有另一个热点可能是AUTO_INCREMENT锁机制。这些问题可能需要重新设计表或应用或者更改 innodb_autoinc_lock_mode设置。
五、选择合适的存储引擎
MySQL其实还有很多其他存储引擎这里建议如何选择存储引擎的原则是“除非需要用到某些InnoDB不具备的特性并且没有其他办法可以替代否则都应该优先选择InnoDB”。
例如如果要用到全文索引建议优先考虑InnoDB加上Sphinx的组合而不是使用支持全文索引的MyISAM。
如果应用需要不同的存储引擎请先考虑一下几个因素
①事务如果需要事务则使用InnoDB是最好的选择如果不需要事务并且主要是SELECT和INSERT操作那么MyISAM是不错的选择。一般日志型应用比较符合这一特性。
②备份如果可以定期关闭服务器来执行备份那么备份的因素可以忽略。如果需要在线热备份那么最好选择InnoDB。
③崩溃恢复很多人即使不需要事务支持也会选择InnoDB的原因就是它可以在系统崩溃后快速地恢复数据。
