先说说什么叫存储引擎吧,乍一看觉得好像很高端的样子,如果去了解MySQL体系结构,你会发现,在存储引擎下面一层是文件系统,而存储引擎,说的明白也就是与文件系统打交道的子系统,是一种文件访问机制访问数据的。
我们可以通过show engines\G;查看当前支持的存储引擎,其中support:yes代表支持
我们在建表时候可以指定使用哪一种存储引擎
也可以使用alter语句将已经存在的表修改为其他存储引擎
下面是各种存储引擎之间区别:
特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
存储限制 | 有 | 64TB | 有 | 没有 | 又 |
事务安全 | 支持 | ||||
锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
哈希索引 | 支持 | 支持 | |||
全文索引 | 支持 | ||||
集群索引 | 支持 | ||||
数据缓存 | 支持 | 支持 | 支持 | ||
索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
数据可压缩 | 支持 | ||||
空间使用 | 低 | 高 | N/A | 低 | 低 |
内存使用 | 低 | 高 | 中等 | 低 | 高 |
批量插入速度 | 高 | 低 | 高 | 高 | 高 |
支持外键 | 支持 |
由于目前使用比较多的是MyISAM和InnoDB引擎,所以这里主要介绍这两种存储引擎得不同^-^
MyISAM存储引擎
MyISAM不支持事务,也不支持外键,但是他的访问速度快;锁级别是表级锁;支持表损坏修复,支持数据压缩
每个MyISAM在磁盘上存储为3个文件,文件名和表名相同,扩展名也是有硬性规定,例如我创建了test3数据表
.frm文件(存储表定义)
.MYD(存储数据)
.MYI(存储索引)
数据文件和索引文件放置在不同文件,平均分布IO,可以获得更快速度
为了演示数据压缩,我使用存储过程往数据表插入了200000条记录,我们使用myisampack命令进行压缩,注意在压缩之前必须关闭数据库,否则会出现错误,下面是压缩之前的:
这样就压缩完成,压缩完之后最好按照提示用myisamchk -rq 数据表位置检查一下
这是压缩之后的大小,我们可以看到虽然上面压缩的是test3.MYI,这只是myisampack程序要求文件名参数必须是索引文件,但是实际上压缩数据文件。
然后我们试着往压缩之后的数据表插入新纪录:
这是因为压缩之后的表是只读表,要想重新插入数据,必须解压
使用myisamchk --unpack 路径/tbname.MYI即可以解压
接下来演示数据表修复过程^-^
数据表损坏原因很多,包括但不限于
服务器突然断电导致数据文件损坏
强制关机,没有关闭mysql服务
磁盘故障
服务器死机
mysqld进程在写表时候被杀掉
如图,我把MYD文件修改一下出现如下图错误:
执行repair table tbname
回复正常^-^
如上只是简单介绍了如何压缩表和修复表,但是方法绝对不限于此,更深入大家可以参考别的文章^-^
回到正题-MyISAM的表支持三种不同的存储格式:
- 静态(固定长度)表
- 动态表
- 压缩表
- 静态表中的字段都是非变长字段,这样每个记录都是固定长度的,这种存储方式优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间比动态表多。静态表的数据在存储时候会按照列的宽度定义补足空格,但是在应用访问时候已经去掉。
这里需要注意一点,如果我们保存的数据后面本来就有空格,那么在返回时候也会被去掉
如上面例子,插入记录后面的空格都被去掉了,前面的空格保留。动态表包含变长字段,存储优点是占用空间相对较少,但是频繁更新删除会产生碎片,并且出现故障时候修复相对较难。
压缩表是上述用myisampack工具创建。
InnoDB存储引擎
InnoDB和MyISAM最大区别在于前者支持事务,并且对于InnoDB每一条SQL语句都默认封装为事务处理,并且自动提交,所以我们在有些时候set commit=0就是为了不让他自动提交;
对于InnoDB而言,他还支持外键,而MyISAM是不支持的,注意在创建外键时候,要求父表必须有对应的索引,子表在创建外键时候也会自动创建对应索引。
当然innodb不支持全文索引,而MyISAM支持(其实MyISAM的全文索引不支持中文分词好像对我们没什么太大用处)
存储方式:
InnoDB存储表和索引有下面两种方式:
1、使用共享表空间存储,这种方式创建表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件;emmm^-^这个共享表空间文件就是在mysql/data下面的ibdata1文件了,我们通过show variables like 'innodb_data%';查看内容如下
实际上,这个文件是可以修改的,当只有一个数据文件ibdata1并且表空间快满了,我们可以添加额外的表空间扩展容量
修改/usr/local/mysql/my.cnf文件(^-^就是配置文件位置)更改innodb_file_per_table为off就是使用共享表空间,然后新增两个数据文件ibdata2和ibdata3
innodb_data_file_path = ibdata1:12M;ibdata2:17M;ibdata3:20M:autoextend
而如果将innodb_file_per_table设置为on,则系统将为每一个表生成独立的tablename.ibd文件,这叫做独占表空间文件
emmm以上是我的个人学习总结,当然两者的区别不仅局限于此,如有不同观点欢迎指出学习^-^