MySQL存储引擎5(笔记)

浏览数：24 / 时间：2015年06月12日

存储引擎对于MYSQL来说是表的概念,所以通常存储引擎也车更为表类型,之前我们的图展示了MySQL的三层结构,存储引擎位于最后一层,所以存储引擎可以看作了MySQL核心功能和磁盘的一个接口,他决定了如何在磁盘上存储数据表.

查看存储引擎的命令:

  1: mysql> SHOW ENGINES; #查看MySQL支持的所有存储引擎
  2: mysql> SHOW TABLES STATUS [LIKE clause] [WHERE clause] #查看某表的存储引擎
  4: SHOW TABLE STATUS [{FROM | IN} db_name]
  5: [LIKE ‘pattern‘ | WHERE expr]    
  6: #查看指定数据库中的[表]的存储引擎

存储引擎中每个字段的意义

  1: 
  2: mysql> SHOW TABLE STATUS IN hellodb WHERE Name=‘classes‘\G
  3: *************************** 1. row ***************************
  4: Name: classes                      表名
  5: Engine: InnoDB                     存储引擎
  6: Version: 10                        版本
  7: Row_format: Compact                行格式
  8:     #这是上面的行的格式的种类  {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  9: Rows: 8                            表中的行数
 10: Avg_row_length: 2048               平均每行所包含的字节数
 11: Data_length: 16384                 表中数据总体大小，单位是字节
 12: Max_data_length: 0                 表能够占用的最大空间，单位为字节
 13: Index_length: 0                    索引的大小，单位为字节
 14: Data_free: 9437184                 对于MyISAM表，表示已经分配但尚未使用的空间，其中包含此前删除行之后腾出来的空间
 15: Auto_increment: 9                  下一个AUTO_INCREMENT的值
 16: Create_time: 2014-04-08 11:14:52   表的创建时间
 17: Update_time: NULL                  表数据的最近一次的修改时间
 18: Check_time: NULL                   使用CHECK TABLE或myisamchk最近一次检测表的时间
 19: Collation: utf8_general_ci         排序规则
 20: Checksum: NULL                     如果启用，则为表的checksum
 21: Create_options:                    创建表时指定使用的其它选项
 22: Comment:                           表的注释信息
 23: 1 row in set (0.01 sec)

各存储引擎的特性

修改默认存储引擎：通过default_storage_engine服务变量实现

InoDB

     两种格式
            1、innodb_file_per_table=OFF，即使用共享表空间
                每张表一个独有的格式定义文件: tb_name.frm
                还一个默认位于数据目录下共享的表空间文件：ibdata#

            2、innodb_file_per_table=ON，即使用独立表空间
                每个表在数据库目录下存储两个文件：
                    tb_name.frm
                    tb_name.ibd

表空间:类似一个黑盒子,除了InnoDB,其他人都不能管理里面的数据,;里面通常存储了数据和索引,因此他是聚集索引

InnoDB支持事务,有事务日志,事务日志在数据目录下,通常至少是两个大小相同的文件,并且,为了提高响应速度,两个事务日志文件都是磁盘上的一段连续的磁盘空间(顺序I/O),

支持外键约束

支持MVCC:多版本并发控制

支持聚集索引:将表和索引存储在一起,以提高响应速度,只能有一个,通常用主键来实现

聚簇索引之外的其它索引，通常称为辅助索引,辅助索引通常不是指向数据的,而是指向聚集索引的

支持行级锁:是根据间隙锁实现的

支持辅助索引

支持自适应的hash索引

支持热备:依赖与使用于独立的表空间

建议使用InnoDB的引擎,并且设置读提交的隔离等级

MyISAM:

每个表都会在数据库目录下存储三个文件:

            tb_name.frm
            tb_name.MYD
            tb_name.MYI

支持全文索引

支持压缩存放:用于实现数据仓库

支持空间索引

支持表级锁:

支持延迟更新索引

不支持事务,外键和行级锁,数据库崩溃后,由于没有事务,无法安全恢复数据

适用场景:只读数据,较小的表,能够容忍崩溃后的修改操作和数据丢失

ARCHIVE：
仅支持INSERT和SELECT，支持很好压缩功能；
适用于存储日志信息，或其它按时间序列实现的数据采集类的应用；

不支持事务，不能很好的支持索引；

CSV：
将数据存储为CSV(文本)格式；不支持索引；仅适用于数据交换场景；

浮点数存储为文本会丢失精度 ,数据之间使用逗号隔开

BLACKHOLE：
没有存储机制，任何发往此引擎的数据都会丢弃,类似 /dev/null；其会记录二进制日志，因此，常用于多级复制架构中作中转服务器；

MEMORY：
保存数据在内存中，内存表, 系统重启后数据就没有了；常用于保存中间数据，如周期性的聚合数据等；也用于实现临时表
仅支持hash索引，使用表级锁，不支持BLOB和TEXT数据类型

MRG_MYISAM：是MYISAM的一个变种，能够将多个MyISAM表合并成一个虚表；

NDB：是MySQL CLUSTER中专用的存储引擎,几乎没人用

第三方的存储引擎：

           OLTP类：
               XtraDB: 增强的InnoDB，由Percona提供；
                   编译安装时，下载XtraDB的源码替换MySQL存储引擎中的InnoDB的源码

               PBXT: MariaDB自带此存储引擎
                   支持引擎级别的复制、外键约束，对SSD磁盘提供适当支持；
                   支持事务、MVCC

TokuDB: 使用Fractal Trees索引，适用存储大数据，拥有很压缩比；已经被引入MariaDB；

           列式存储引擎：
               Infobright: 目前较有名的列式引擎，适用于海量数据存储场景，如PB级别，专为数据分析和数据仓库设计；
               InfiniDB
               MonetDB
               LucidDB

           开源社区存储引擎：
               Aria：前身为Maria，可理解为增强版的MyISAM(支持崩溃后安全恢复，支持数据缓存)
               Groona：全文索引引擎，Mroonga是基于Groona的二次开发版
               OQGraph: 由Open Query研发，支持图结构的存储引擎
               SphinxSE: 为Sphinx全文搜索服务器提供了SQL接口
               Spider: 能数据切分成不同分片，比较高效透明地实现了分片(shared)，并支持在分片上支持并行查询；

扩展阅读:索引类型: