参考
Mysql官网解释 5.6版本,存储引擎的详细介绍
https://dev.mysql.com/doc/refman/5.6/en/myisam-storage-engine.html
概述
Mysql支持常用的4种存储引擎：Myisam、InnoDB(默认)、Memory、Merge
- Myisam：高效缓存 多读少写时读取更快，B+树索引
- InnoDB：支持事务、行级锁、外键，B+树索引
- Memory：基于Hash索引，内存存储
- Merge：分表时子表必须是Myisam
SHOW ENGINES;查看，Mysql支持多种存储引擎。
MyISAM
- 特点
- 在 heave-read的业务场景下，访问速度快
- 主索引和辅助索引一样，都是非聚簇索引，数据和索引分离，存储的value值是真实数据的物理地址
- 默认保存了表的总行数，select Count(*) 直接返回结果
- 表锁，没有行锁，所以无法支持并发的写，比如无法支持同时修改一张表中2个不同的数据
- Merge引擎分表时，子表必须是Myisam
-
使用场景：多读少写，不需要事务、外键，也不容易服务器异常的场景
-
Myisam缓存机制
每次通过索引读取数据的时候，Mysql都会缓存 这个 IndexPage 到内存当中，这个内存块的大小由 key_buffer_size（默认 8M）决定，记录了这个索引块里面的所有数据对应的物理地址信息，并且mysql有一个LRU队列去管理这个缓存块。
包括修改的时候，也是直接修改这个缓存块（同时会记录日志），并且标记这个page为dirty，当从LRU队列里面移除的时候，如果Dirty则需要写回到硬盘上。
因为缓存的只是索引和物理存储地址，而不缓存真正的数据，所以和Innodb相比，相同大小的缓存空间，Myisam可以缓存更多的索引。
When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size).
- 5.7版本之后 Myisam不支持分区功能，只有InnoDB和NDB支持分区
InnoDB
- 支持事务：通过内部MVCC机制实现
- 支持外键
- 支持行级锁：行锁是建立在索引的基础之上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引，否则用的还是表锁
- 支持并发读写，因为索引查询是行锁
- 自动增长列
- 辅助索引是以非聚簇索引实现的，辅助索引的value存的是主键，所以主键越小越好，减小辅助索引树的大小
- InnoDB is typically said to have better crash recovery,灾难恢复更稳定
为什么Myisam比Innodb读取更快？
结论：在多读少写的业务场景下，Myisam的read的速度快好几倍。但是在有读有写或者多读少写的业务场景下，Myisam因为是表锁会阻塞，读和写都慢。
-
非聚簇索引一次lookup:在非主键索引的Query的业务场景下，Myisam只需要一次B+树 key lookup，就可以读取到data的物理地址，再一次IO读取，就可以获取到磁盘的数据信息，但是 InnoDB却需要2次索引树查找，这个都是不在一个物理page的查找，所以Myisam理论上在这一点设计上差不多快一倍。
-
Myisam没有MVCC，在任意时间节点，一个数据就只有1个值，而Innodb就可能同时存在多个版本，加大了搜索筛选条件。
-
Myisam可以更好的利用缓存：因为索引和数据是分离的，只缓存索引（索引值里面有key值和dataAddress），然后再一次IO读取，就可以捞取到数据。
但是Innodb因为数据和索引是存在一起的，必须同时缓存相应的索引和数据，所以相同缓存空间下，InnoDB可以缓存的数据量更少，缓存的命中率更低，但是一旦命中，则无需IO操作，可以直接返回数据。
Myisam PK Innodb
- Innodb优势： 事务、外键、行级锁、并发读写、灾难恢复更靠谱
- Myisam优势： 文本索引、count(*)存储、多读少写时快速读取和高效缓存、表数据可以拷贝迁移
数据存储结构
数据存储位置： 在 %datadir%/databaseName
默认一个page的大小是16k
- Myisam
Myisam的存储结构: 索引和数据是分开存储的。
- student.frm ：存储表结构等相关信息
- student.MYI：存储表的索引数据
- student.MYD：存储表的具体数据
Myisam的存储结构比较独立，可以通过直接拷贝这3个文件，来实现表数据的 跨库迁移，甚至可以是跨操作系统的迁移
- InnoDB
InnoDB的存储结构：分为两种模式（共享模式和 默认的独占模式 innodb_file_per_table）
- student.frm ：存储表结构等相关信息
- student.ibd ：存储表全部的数据内容和索引内容
共享表空间以及独占表空间都是针对数据的从物理意义上来讲：
- 共享表空间: 会把表集中存储在一个系统表空间里。即每一个数据库的所有表的数据，索引文件全部放在一个文件中。该文件目录默认的是服务器的数据目录。 默认的文件名为:ibdata1 初始化为10M。
- 独占表空间: 每一个表分别创建一个表空间，这时。在对应的数据库目录里每一个表都有.ibd文件(这个文件包括了单独一个表的数据内容以及索引内容)
memory
- 内存存储，mysql重启数据消失
- 默认索引Hash算法，速度快，但是范围查找就慢，也可以通过指定 B+树为索引
- 不支持事务、一般被redis memcache取代
merge
用来做分表用的，多个结构相同的表，虚拟出一个merge表，可单独取操作子表来实现高性能，通知也支持操作merge表来实现，逻辑上统一的数据。
注意事项
- InnoDB只有在走索引查询加锁时才是行锁，否则都是表锁
例如User表 主键 id，但是name字段没有索引，事务A 通过非索引字段 name来查询 huangzs的用户，并且锁定该数据（可能有多个 huangzs用户），因为name字段没有索引，所以是表锁
在没有commit之前，开启事务B，尝试修改 name='huang'的操作是不能执行的，因为当前表被锁住了
TXA：
begin;
update user set memo ='hzs' where name='huangzs'
TXB:
begin;
update user set memo ='hzs' where name='huang'
行级锁的正确使用:查询条件是通过主键字段进行行级锁的，所以可以并发的修改数据
TXA：
begin;
update user set memo ='hzs' where id=500;
TXB:
begin;
update user set memo ='hzs' where id=501;
-
select的时候，只有 lock in share mode 或者 for update 才会锁定数据，没有限定词的话，默认是直接读取的，不存在竞争。也就是一行数据即使加锁了，直接select也可以查的到
-
InnoDB死锁了也不用怕，去倒杯水就好了：mysql 默认有innodb_lock_wait_timeout：50s的设置，超过50s会自动释放锁
网友评论