MyISAM、innodb等存储引擎的数据类型比较:
MyIASM:
文件: .frm .MYD .MYI
MyIASM:并发性和锁级别
表的修复:
check table tablename:对数据表进行检查
repair table tablename:用于修复被破坏的表
myisamchk(命令行修复表 注意要停止mysql):
特性:
MyIASM表支持数据压缩:
命令行:myisampack 压缩完只能
限制:
版本<5.0默认表大小4g
适用场景:
非事务型应用
只读类应用
空间类应用
Innodb:
文件结构:.frm ,.idb
适用表空间进行数据存储
innodb_file_per_table
ON:独立表空间:tablename.idb
OFF:系统表空间:idbataX
系统表空间和独立表空间要如何选择
共享表空间:
优点:可以将表空间分成多个文件存放到各个磁盘上(表空间文件大小不受表大小的限制,如一个表可以分布在不同的文件上)。数据和文件放在一起方便管理。
缺点:
所有的数据和索引存放到一个文件中,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。
独立表空间:
优点:
1.每个表都有自已独立的表空间。
2.每个表的数据和索引都会存在自已的表空间中。
3.可以实现单表在不同的数据库中移动。
4.空间可以回收(除drop table操作处,表空不能自已回收)
a.Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b.对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c.对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
缺点:
1.单表增加过大,如超过100个G。
2.相比较之下,使用独占表空间的效率以及性能会更高一点。
推荐使用独立表空间
比较:
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table命令收缩系统文件
系统表空间会产生io瓶颈
独立表空间可以同时向多个文件刷新文件
建议
对innodb使用独立表空间
把系统表空间中的表转移到独立表空间方法:
1.使用mysqldump导出所有数据库表数据
2.停止Mysql服务,修改参数,并删除Innodb相关文件
3.重启mySQL服务,重建innodb系统表空间
4.重新导入数据
Innodb:
Innodb是一种事务型存储引擎;
完全支持事务的ACID特性
Redo Log和Undo log
支持行级锁
行级锁可以最大程度的支持并发
行级锁是由存储引擎层实现的
锁:
定义:
锁对主要作用是管理共享资源的并发访问。
实现事务的隔离性。
共享锁(读锁)
独占锁(写锁)
锁的粒度:
表级锁 lock table tablename
行级锁
阻塞和死锁:
阻塞
死锁
5.7之后全文索引和空间函数
使用场景:
适用于大多数OLTP(联机事务处理过程)应用
Innodb状态检查:show engine innodb stauts
Mysql服务器参数:
Mysql获取配置信息路径
Mysql获取配置信息路径
命令行参数
mysqld_safe --datadir=/data/sql_data
配置文件
获取配置文件路径:mysql --help --verbose|grep -A 1 'Default options'
Mysql配置参数的作用域
全局参数
set global 参数名=参数值;
set @@global.参数名:=参数值;
会话参数
set[session]参数名=参数值;
set @@session.参数名 :=参数值;
CSV:
文件系统存储特点:
数据以文本方式存储在文件中
.csv文件存储表内容
.csm文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
特点:
以csv格式践行数据存储
所有列必须都不是不能为null的
不支持索引--不支持大表,不适合在线处理
可以对数据文件直接编辑--保存文本内容
场景:
适合做为数据交换的中间表
Archive:
文件系统存储特点:
以zlib对表数据进行压缩,磁盘i/o更少
数据存储在ARZ为后缀的文件中
引擎特点:
只支持insert和select操作
只支持自增id增加索引
适用场景
日志类和数据采集类应用
Memory:
文件系统存储特点:
也称HEAP存储引擎,所以数据保存在内存中
功能特点:
支持HASH索引和BTree索引
HASH索引:等值索引
BTree索引:范围索引
所有字段固定长度varcher(10)=char(10)
不支持BLOG和TEXT等大字段
使用表级锁
最大大小由max_heap_table_size参数决定的
容易混淆的概念:
memory存储引擎表vs临时表
临时表:
系统临时表-》超过限制使用Myisam临时表、未超过限制使用Memory表
create temporary teble 建立的临时表
使用场景:
用于查找或者是映射表,例如邮编和地区的对应表
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
Federated:
特点
提供了访问远程Mysql服务器上表的方法
本地不存储数据,数据全部放在远程服务器上
本地需要保存表的结构和远程服务器的连接信息
如何使用:
mysql://user_name[:password]@host_name[:port_num]/db_name/tbl_name
适用场景
偶尔的统计分析和手动查询
存储引擎如何选择:
条件:
事务
备份
崩溃恢复
存储引擎的特有性质
混合bug,事务回滚,在线热备份