优化mysql聚合_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)&＃61;char(10)

3.不支持BLOB,TEXT等大字段类型

4.使用表级锁

5.最大大小由max_heap_table_size决定

使用场景

1.用于查找或者是映射表(等值查询快),例如邮编和地区的对应关系

2.用于保存数据分析中产生的中间表

3.用户缓存周期性聚合数据的结果表

mysql常用的存储引擎之federated

特性

提供了访问远程mysql服务器上表的方法(结盟表)

本地没有数据,数据全部在远程

本地需要保存表结构和连接信息

默认禁止

使用场景

偶尔的统计分析和手工查询