mysql性能优化总结(三)
mysql体系结构
插件式存储引擎,将数据的查询和存储相分离.每一款存储引擎都有各自的优缺点.可以灵活选用
架构: 客户端 -> mysql服务层 -> 存储引擎层
存储引擎是针对表,不是针对库,同一库中的不同的表,可以使用不同的存储引擎.(但是不建议这样做),存储引擎的不同会对性能产生直接的影响.
mysql常用的存储引擎之MyISAM
mysql5.5之前版本默认的存储引擎
MYISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成,另外还有一个frm文件,并非myisam特有,是所有存储引擎都有的文件,用于存储表结构
myisam特性
1.并发性与锁级别
使用的是表级锁,读取数据的时候也是对全表加共享锁.读取和写入是互斥的.
读写并发场景性能不佳
2.表损坏恢复
check table tableName;
repair table tableName;
3.myisam支持的索引类型
支持全文索引,并且是在5.7之前唯一支持全文索引的官方存储引擎
支持对text和blob字段简历前500字符的前缀索引
4.myisam支持对只读表的数据压缩,减少磁盘io(测试使用mariadb,发现压缩之后仍然可以插入数据,myisam.OLD还在一直变大)
myisampack -b -f tablename.MYI
myisam限制
不支持事务
5.0之前的版本默认表大小是4G
5.0之后的版本默认表大小是256TB
myisam适用场景
1.非事务型应用
2.只读类应用
3.空间类应用 myisam是5.7之前唯一支持空间函数的存储引擎,例如gps数据
mysql常用的存储引擎之InnoDB
5.5及之后默认的存储引擎
特性:
1.系统表空间和独立表空间两种存储方式
由参数innodb_file_per_table决定,5.6之后默认ON,使用独立表
比较
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table指定来收缩系统文件
使用系统表空间会产生io瓶颈,独立表空间可以同时向多个文件刷新数据
2,是一种事务存储引擎,完全支持ACID特性
Redo Log 和 Undo Log实现ACD,锁实现I
3.支持行级锁,最大程度的支持并发,行级锁是在存储引擎层实现的
锁的类型
共享锁(读锁)
独占锁(写锁)
对于用一条记录,读锁和写锁是互斥的,所以按道理来说如果对一条数据加上写锁,应该读取不到才对.但是实际情况是仍然可以读取到,这是为什么呢?因为此时读取的Undo Log 中的老的数据版本 \
锁的粒度
表级锁
行级锁
阻塞和死锁
如何处理死锁?
在多个事务中,按照相同的顺序访问多个资源,或者加上索引
innodb状态检查
show engine innodb status
使用场景 innodb适用于大多数OLTP应用
mysql常用的存储引擎之CSV
存储特点
1.数据以CSV格式进行存储
2.所有列必须都是非null
3.不支持索引
4.不适合大表,不适合在线处理
5.可以对数据文件直接编辑
使用场景:
适合作为数据交换的中间表
mysql常用的存储引擎之Archive
存储特点
1.以zlib对表数据进行压缩,磁盘I/O更少,一个几T的innodb表,如果里面的数据存储到archive存储引擎中,可能只需要几百M
2.数据存储在以ARZ为后缀的文件中
3.只支持insert和select操作
4.只支持在自增id上建立索引
使用场景
日志和数据采集类应用
mysql常用的存储引擎之memory
也称heap存储引擎,数据保存在内存中
存储特点
1.支持Hash索引和BTree索引,建立索引时默认hash索引(等值查询快,不支持范围查询),BTree所以支持范围查找
2.所有字段都为固定长度 varchar(10)=char(10)
3.不支持BLOB,TEXT等大字段类型
4.使用表级锁
5.最大大小由max_heap_table_size决定
使用场景
1.用于查找或者是映射表(等值查询快),例如邮编和地区的对应关系
2.用于保存数据分析中产生的中间表
3.用户缓存周期性聚合数据的结果表
mysql常用的存储引擎之federated
特性
提供了访问远程mysql服务器上表的方法(结盟表)
本地没有数据,数据全部在远程
本地需要保存表结构和连接信息
默认禁止
使用场景
偶尔的统计分析和手工查询
