一、Mysql数据库的体系结构
图中需要注意的是存储引擎是基于表的,不是基于数据库的。
二、存储引擎
下面只介绍几种常见的存储类型
1、InnoDB
InnoDB存储引擎支持事务,其设计目标是面向在线事务处理(OLTP)的应用,起特点是行锁设计、支持外键、并支持类似于Oracle的非所定读,即默认读取操作不会产生锁。从MySQL数据库5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。
InnoDB通过使用多版本并发控制(MVVM)来获得高并发、并且实现了标准的4种隔离级别,默认为REPEATABLE级别,同时,使用一种被称为next-key-locking的策略来避免幻读的现象。除此之外,InnoDB存储引擎还提供了插入缓冲,二次写,自适应哈希索引,预读等高性能和高可用功能。
InnoDB存储引擎4大特性:插入缓冲,二次写,自适应哈希索引,预读
对于表中数据的存储,InnoDB存储引擎采用了聚集的方式,因此每张表的存储都是按主键的顺序进行存放。如果没有显示的声明主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键。
InnoDB为什么推荐使用自增ID作为主键?
自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树频繁的合并和分裂(对比UUID来说)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值。
2、MyISAM
MyISAM存储引擎不支持事务,表锁设计,支持全文索引,主要面向一些在线分析处理(OLAP)的数据库应用。在MySQL5.5.8之前MyISAM存储引擎是默认的存储引擎。MyISAM存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件,不缓存数据文件。MyISAM存储引擎由MYD和MYI组成,MYD用来存放数据文件,MYI用来存放索引文件。
在MySQL5.0之前,默认的MyISAM支持的表大小为4G,从5.0开始,MyISAM默认支持256TB的单表数据。
MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
3、Memory
Memory存储引擎(之前叫做HEAP存储引擎)将表中的数据存放在内存中,如果数据库重启或者发生崩溃,表中的数据将会丢失,它非常适合于存储临时数据的临时表以及数据仓库中的维度表。Memory存储引擎默认使用哈希索引,而不是我们熟悉的B+数索引。
虽然Memory存储引擎速度非常快,但在使用上还是有一定的限制,比如:只支持表锁,并发性能差,并且不支持TEXT和BLOB列类型。最重要的是,存储变长类型如varchar时是按照定长字段char类型进行的,因此会浪费内存。
三、InnoDB和MyISAM的对比
InnoDB | MyISAM | |
---|---|---|
事务 | 支持 | 不支持 |
外键 | 支持 | 不支持 |
索引 | 聚集索引 | 非聚集索引 |
全文索引 | InnoDB1.2.x之后支持,1.2.x之前不支持,mysql5.5.8-5.7之间都不支持全文索引,mysql5.7开始支持全文索引 | 支持 |
锁 | 表、行(默认行锁) | 表锁 |
主键 | 必须要有(没定义的话就用ROWID) | 可以没有 |
InnoDB存储文件有frm、ibd,而MyISAM是frm、MYD、MYI
InnoDB: frm是表定义文件,ibd是数据文件
MyISAM: frm是表定义文件,MYD是数据文件,MYI是索引文件
InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快(注意不能加where条件)
那为什么InnoDB没有这个变量呢?
因为InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的,因此count统计会计算对于当前事务而言可以统计到的行数,而不是将总行数存储起来方便快速查询。
InnoDB的行锁实现在索引上的,而不是锁在物理记录上。如果访问没有走索引,也就无法使用行锁,将会退化成表锁。
t_user(uid,uname,age,sex) innodb; uid PK 无其它索引
update t_user set age = 10 where uid = 1; 命中索引,行锁
update t_user set age = 10 where uid != 1; 未命中索引,表锁
update t_user set age = 10 where uname = "zhangsan"; 无索引,表锁
四、如何选择用哪种存储引擎
1、看是否支持事务,如果需要支持事务就用InnoDB,不需要的话可以考虑使用MyISAM;
2、如果表中大多数都只是读操作,可以考虑使用MyISAM,如果写和读差不多或者写多请使用InnoDB
3、系统崩溃后,MyISAM恢复起来更加困难,能接受或者保证系统不崩溃可以考虑使用MyISAM
综上:可以看出不知道使用哪种存储引擎请使用InnoDB,因为使用InnoDB存储引擎总不会错,而且MySQL5.5.8就默认使用InnoDB存储引擎了。