MySQL系列(二)--MySQL存储引擎

影响数据库性能的因素:

1、硬件环境:CPU、内存、存盘IO、网卡流量等

2、存储引擎的选择

3、数据库参数配置(影响最大)

4、数据库结构设计和SQL语句

MySQL采用插件式存储引擎,可以自行选择,但同时如何选择也成了问题,本文会介绍存储引擎的特点,以及如何选择

MySQL体系结构:

MySQL系列(二)--MySQL存储引擎

Server用来实现所有与存储引擎无关的事:比如,查询语句等

MySQL所支持的存储引擎如下:通过show engines查询

MySQL系列(二)--MySQL存储引擎

MyISAM:MySQL5.5版本之前默认的存储引擎

MyISAM的表由FRM(存储表定义)、MYD(数据文件)和MYI(索引文件)组成,后面两个为MyISAM独有的

特性:

1、并发性和锁级别:

  使用表级锁,对数据进行读取和修改的时候,都会对表进行加锁,读写互斥,对读写混合的并发不好,对只读的并发还可以

2、表损坏修复:

  支持对意外关闭导致损坏的表进行检查和修复(不是事务恢复,因为MyISAM不是事务型引擎,没有事务恢复的相关日志),通过

check table tablename和repair table tablename进行检查和修复,也可以通过MyISAMCHK进行修复,但是一定要先将mysql服务停止

3、支持全文索引:

  是5.7版本之前唯一原生就支持全文索引的引擎

4、对只读表可以进行数据压缩:

  通过MyISAMPack对文件进行压缩,压缩过后就变成只读表了,不能修改数据

5、独有的系统表和临时表

临时表是指在排序、分组等操作中,当数据超过一定大小时,由查询优化器创建的临时表,而不是CREATE TEMPORARY TABLE创建这种临时表

限制:

  1、5.0版本之前默认表大小为4G,想要存储更大的表要修改MAX_rows和AVG_ROW_LENGTH,相乘就是表大小

  2、5.0版本之后默认256T

使用场景:

  1、不支持事务的应用

  2、只读类应用,可以对文件的压缩

  3、5.0之前,只有MyISAM支持空间类应用,可以存储例如GPS型数据

  4、大数据select场景

InnoDB:5.5版本之后默认存储引擎,代替了MyISAM

特性:

  1、支持事务,支持ACID特性,通过Redo Log(已提交的事务)和Undo Log(未提交的事务)

  2、支持行级锁,在存储引擎层实现,可以支持更大的并发

  3、使用表空间进行数据存储:通过show variables like 'innodb_file_per_table';进行查看

  值为ON,为每个表创建一个表空间:tablename.ibd

  值为OFF,使用系统表空间ibdataX(X为数字,1,2,3,4,5)

如何选择表空间存储方式:

  1、5.5之前默认使用系统表空间,表空间大小不会变,即使磁盘空间不足,主动删除一些信息,表空间不会改变

  2、独立表空间可以通过命令:optimize table来收缩系统文件

  3、系统表空间可能会存在IO瓶颈

  4、独立表空间可以同时向多个文件刷新数据,IO问题要小得多

V5.6之后,独立表空间变成默认,也是建议使用的

MyISAM与InnoDB区别:

  1).InnoDB支持事务,MyISAM不支持

  2).MyISAM适合查询以及插入为主的应用,InnoDB适合频繁修改以及涉及到安全性较高的应用

  3).InnoDB支持外键,MyISAM不支持

  4).V5.7版本之后,InnoDB支持了全文索引和空间函数,所以MyISAM使用场景越来越少,一般都是使用InnoDB

  5).InnoDB中不保存表的行数,如select count(*) from table时,InnoDB需要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读

出保存好的行数即可。注意的是,当count(*)语句包含where条件时MyISAM也需要扫描整个表

  6).MYISAM的性能更优,占用的存储空间少.MYISAM索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明

显优于INNODB

  7).对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引

  8).清空整个表时,InnoDB是一行一行的删除,效率非常慢。MyISAM则会重建表

  9).MyISAM支持表级锁,InnoDB支持行锁(某些情况下还是锁整表,如 update table set a=1 where user like '%lee%',或者where条件

没有使用主键)

CSV:

  1、CSV可以将CSV文件当做MySQL表进行处理,CSV数据是以CSV文件的格式进行存储

  2、所有列必须都是非空的

  3、不支持索引

  4、可以对数据文件直接进行编辑,其他存储引擎的数据都是以二进制的形式进行存储

使用场景:

  适合作为数据交换的中间表,例如可以将Excel的数据存储为CSV文件,直接复制到MySQL目录,就可以直接打开使用,反过来也是一样的

Archive:

  1、以zlib对表数据进行压缩,比MyISAM占用的磁盘IO更少

  2、数据存储在.ARZ文件中,.frm存储表结构信息

  3、只支持insert和select操作,支持高并发

  4、只支持在自增ID上加索引

使用场景:

  日志和数据采集类的应用

Memory:

  也被称为heap存储引擎,数据都是保存在内存当中,所以只存在.frm文件

  1、MySQL重启之后,Memory存储引擎对应的表数据都会消失,但是表结构存在,因为保存在.frm文件中

  2、支持hash索引和BTree索引,默认hash索引(使用与=查询),如果更多的是范围查询使用BTree索引

  3、所有字段的长度都是固定的,例如

  4、不能使用BLOG和TEXT这类大字段

  5、使用的是表级锁

  6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

  7、存储数据最大大小由max_heap_table_size参数决定,可以自行修改

使用场景:

  1、适用于等值查找,可用于查找和映射表,邮编和地区的映射

  2、保存数据分析的中间表

  3、缓存周期性聚合数据的结果表

总体就是:因为Memory的数据容易丢失,要求数据可再生

PS:主从复制不能保证Memory数据,master重启会重建表,slave也会重建的

如何选择存储引擎:

1、应用需要事务支持,就选用InnoDB

2、应用是否需要数据备份,一般生产环境都需要热备,也是InnoDB

3、崩溃恢复,InnoDB也比MyISAM更稳定

4、上面都不考虑的情况下,再考虑存储引擎的特性,特性再上面都已经基本总结到了

PS:尽量不要混合使用存储引擎,不然可能发生很奇怪的问题

上一篇:简单的学习心得:网易云课堂Android开发第三章自定义控件


下一篇:java struts2入门学习--基于xml文件的声明式验证