可信网站认证必须做吧,陕西做网站的,discuz网站开发,网站开发推广方案策划书MySQL之架构设计与历史
MyISAM存储引擎
在MySQL5.1及之前的版本#xff0c;MyISAM是默认的存储引擎。MyISAM是默认的存储引擎。MyISAM提供了大量的特性#xff0c;包括全文索引、压缩、空间函数(GIS)等#xff0c;但MyISAM不支持事务和行级锁#xff0c;而且有一个毫无疑…MySQL之架构设计与历史
MyISAM存储引擎
在MySQL5.1及之前的版本MyISAM是默认的存储引擎。MyISAM是默认的存储引擎。MyISAM提供了大量的特性包括全文索引、压缩、空间函数(GIS)等但MyISAM不支持事务和行级锁而且有一个毫无疑问的缺陷就是崩溃后无法安全回复。正是由于MyISAM引擎的缘故即使MySQL支持事务已经很长时间了在很多人的概念中MySQL还是非事务型的数据库。尽管MyISAM引擎不支持事务、不支持崩溃后的安全回复但它绝不是一无是处的。对于只读的数据或者表比较小、可以忍受修复(repair)操作则依然可以继续使用MySQL(但请不要默认使用MyISAM而是应当默认使用InnoDB)
存储
MyISAM会将表存储在两个文件中:数据文件和索引文件分别以.MYD和MYI为扩展名.MyISAM表可以包含动态或者静态(长度固定)行。MySQL会根据表的定义来决定采用何种行格式。MyISAM表可以存储的行记录数一般受限于可用的磁盘空间或者操作系统中单个文件的最大尺寸。 在MySQL5.0中MyISAM表如果是变长行则默认配置只能处理256TB的数据因为指向数据记录的指针长度是6个字节。而在更早的版本中指针长度默认是4字节所以只能处理4GB的数据而所有的MySQL版本都支持8字节的指针。要改变MyISAM表指针的长度(调高或者调低)可以通过修改表的MAX_ROWS和AVG_ROW_LENGTH选项的值来实现两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有所i你这可能需要很长的时间才能完成。
MyISAM特性
作为MySQL最早的存储引擎之一MyISAM有一些已经开发出来很多年的特性可以满足用户的实际需求。
1.加锁与并发. MyISAM对整张表加锁而不是针对行。读取时需要读到的所有表加共享锁写入时则对表加排他锁。但是在表有读取查询的同时也可以往表中插入新的记录(这被称为并发插入,CONCURRENT INSERT)2.修复. 对于MyISAM表MySQL可以手工或者自动执行检查和修复操作但这里说的修复和事务回复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失而且修复操作是非常慢的。可以通过CHECK TABLE mytable检查表的错误如果有错误可以通过执行REPAIR TABLE mytable进行修复。另外如果MySQL服务器已经关闭也可以通过myisamchk命令行工具进行检查和修复操作3.索引特性. 对于MyISAM表即使BLOB和TEXT等长字段也可以基于前500个字符创建索引。MyISAM也支持全文索引这是一种基于粉刺创建的索引可以支持复杂的查询4.延迟更新索引键(Delayed Key Write)。 创建MyISAM表的时候如果指定了DELAY_KEY_WRITE选项在每次修改执行完成时不会立刻将修改的索引数据写入磁盘而是会写到内存中的键缓冲区(in-memory key buffer)只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能但是在数据库或者主机崩溃时会造成索引损坏需要执行修复操作。延迟更新索引键的特性可以在全局设置也可以为单个表设置。
MyISAM压缩表
如果表在创建并导入数据以后不会再进行修改操作那么这样的表或许适合采用MyISAM压缩表。 可以使用myisampack对MyISAM表进行压缩(也叫打包pack).压缩表是不能进行修改的(除非先将表解除压缩修改数据然后再次压缩)。压缩表可以极大地减少磁盘空间占用因此也可以减少磁盘I/O从而提升查询性能。压缩表也支持索引但索引也是只读的。以现在的硬件能力对大多数应用场景读取压缩表数据时的解压带来的开销影响并不大而减少I/O带来的好处则要大得多。压缩时表中的记录时独立压缩的所以读取单行的时候不需要去解压整个表(甚至也不解压行所在的整个页面)
MyISAM性能
MyISAM引擎设计简单数据以紧密格式存储所以在某些场景下的性能很好。MyISAM有一些服务器级别的性能扩展限制比如对索引键缓冲区(key cache)的Mutex锁MariaDB基于段(segment)的索引键缓冲区机制来避免该问题。但MySIAM最典型的性能问题还是表锁的问题如果你发现所有的查询都长期处于Locked状态那么毫无疑问表锁就是罪魁祸首
MySQL内键的其他存储引擎
MySQL还有一些有特殊用途的存储引擎。在新版本中有些可能因为一些原因已经不再支持另外还有些会继续支持但是需要明确地启用后才能使用。
1.Archive引擎 Archive存储引擎只支持INSERT和SELECT操作在MySQL5.1之前也不支持索引。Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩所以比MySQL表的磁盘I/O更少。但是每次SELECT查询都需要执行全表扫描。所以Archive表适合日志和数据采集类应用这类应用做数据分析时往往需要全表扫描。或者在一些需要更快速的INSERT操作的场合下也可以使用. Archive引擎支持行级锁和专用的缓冲区所以可以实现高并发的插入。在一个查询开始直到返回表中存在的所有行数之前Archive引擎会阻止其他的SELECT执行以实现一致性读。另外也实现了批量插入在完成之后对读操作是不可见的。这种机制模仿了事务和MVCC的一些特性但Archive引擎不是一个事务型的引擎而是一个针对告诉插入和压缩做了优化的简单引擎2.Blackhole引擎 Blackhole没有实现任何的存储机制,它会丢弃所有插入的数据不做任何保存。但是服务器会记录Blackhole表的日志所以可以用于复制数据到悲苦或者只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发徽作用。但这种应用方式会碰到很多问题因此并不推荐3.CSV引擎. CSV引擎可以将普通的CSV文件(逗号分割值得文件)作为MySQL的表来处理但这种表不支持索引。CSV引擎可以在数据库运行时拷入或者拷出文件。可以将Excel等电子表格软件中的数据存储为CSV文件然后复制到MySQL数据目录下就能在MySQL中打开使用。同样的如果将数据写入到一个CSV引擎表其他的外部程序也能立即从表的数据文件中读取CSV格式的数据因此CSV引擎可以作为一种数据交换的机制非常有用4.Federated引擎。 Federated引擎是访问其他MySQL服务器的一个代理它会创建一个到远程MySQL服务器的客户端连接并将查询传输到远程服务器执行然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如Microsoft SQL Server和Oracle的类似特性竞争的可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性但也经常带来问题因此默认是禁止的。MariaDB使用了它的一个后续改进版本叫作FederatedX.5.Memory引擎 如果需要快速地访问数据并且这些数据不会被修改重启以后丢失也没关系那么使用Memory表(以前也叫做HEAP表)是非常有用的。Memory表至少比MyISAM表要快一个数量级因为所有的数据都存在内存中不需要进行磁盘I/OMemory表的结构在重启以后还会保留但数据会丢失 Memory表在很多场景可以发挥好的作用: 5.1 用于查找(lookup)或者映射(mapping)表例如将邮编和州名映射的表 5.2 用于缓存周期性聚合数据(periodically aggregated data)的结果 5.3 用于保存数据分析中产生的中间数据 Memory表支持Hash索引因此查询操作非常快。虽然Memory表的速度非常快但还是无法取代传统的基于磁盘的表。Memory表是表级锁因此并发写入的性能较低。它不支持BLOCK或TEXT类型的列并且每行的长度是固定的所以即使指定了VARCHAR列实际存储时也会转换成CHAR;这可能导致部分内存的浪费(其中一些限制在Percona版本已经解决) 如果MySQL在执行查询的过程中需要使用临时表来保存中间结果内部使用的临时表就是Memory表。如果中间结果太大超出了Memory表的限制或者含有BLOB或TEXT字段则临时表会转换成MyISAM表。 人们经常混淆Memory表和临时表临时表是指使用CREATE TEMPORARY TABLE语句创建的表它可以使用任何存储引擎因此和Memory 表不是一回事。临时表只在单个连接中可见当连接断开时临时表也将不复存在。6.Merge引擎 Merge引擎是MyISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用该引擎可以发挥作用但是引入分区功能后该引擎已经被放弃7.NDB集群引擎 2003年当时的MySQL AB公司从索尼爱立信公司收购了NDB数据库然后开发了NDB集群存储引擎作为SQL和NDB原生协议之间的解耦。MySQL服务器、NDB集群存储引擎以及分布式、share-noting的、容灾的、高可用的NDB数据库的组合被称为MySQL集群(MySQL Cluster)
MySQL引擎相关命令
MySQL查看存储引擎的命令
mysql SHOW ENGINES;
-----------------------------------------------------------------------------------------------------------------------------
| Engine | Support | Comment | Transactions | XA | Savepoints |
-----------------------------------------------------------------------------------------------------------------------------
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| Sequence | YES | Sequence Storage Engine Helper | NO | NO | NO |
-----------------------------------------------------------------------------------------------------------------------------
10 rows in set (0.11 sec)查看建表使用的存储引擎
mysql SHOW CREATE TABLE tjc_project.app_config;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| app_config | CREATE TABLE app_config (id int(11) NOT NULL AUTO_INCREMENT COMMENT 自增主键id,environment_type varchar(63) DEFAULT NULL COMMENT 环境类型,config_key varchar(127) DEFAULT NULL COMMENT 配置类型,config_value varchar(255) DEFAULT NULL COMMENT 配置的值,status tinyint(1) DEFAULT NULL COMMENT 开启状态 0:关闭 1:开启,create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间,update_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 更新时间,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.13 sec)查看默认的存储引擎配置
mysql SHOW VARIABLES LIKE %storage_engine%;
---------------------------------------------------------
| Variable_name | Value |
---------------------------------------------------------
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | myisam,memory,archive |
| internal_tmp_disk_storage_engine | InnoDB |
---------------------------------------------------------
4 rows in set (0.12 sec)
