1.16 MySQL

1.16 MySQL

1.16.1 MyISAM与InnoDB的区别

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

1.16.2 MySQL四种索引

1）唯一索引

主键索引是唯一的，通常以表的ID设置为主键索引，一个表只能有一个主键索引，这是他跟唯一索引的区别。

2）聚簇索引

聚簇索引的叶子节点都包含主键值、事务 ID、用于事务 MVCC 的回滚指针以及所有的剩余列。

3）辅助索引（非聚簇索引|二级索引）

辅助索引也叫非聚簇索引，二级索引等，其叶子节点存储的不是行指针而是主键值，得到主键值再要查询具体行数据的话，要去聚簇索引中再查找一次，也叫回表。这样的策略优势是减少了当出现行移动或者数据页分裂时二级索引的维护工作。

4）联合索引

两个或两个以上字段联合组成一个索引。使用时需要注意满足最左匹配原则！

1.16.3 MySQL的事务

（1）事务的基本要素（ACID）

（2）事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致

幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

1.16.4 MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

1.16.5 MyISAM与InnoDB对比

（1）InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的：

①InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起,MySQL5.7表结构放在.frm中）

②MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构，MySQL5.7是.frm）、*.MYD（数据），*.MYI（索引）

（2）InnoDB中主键索引是聚簇索引，叶子节点中存储完整的数据记录；其他索引是非聚簇索引，存储相应记录主键的值 。

（3）InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。

（4）MyISAM中无论是主键索引还是非主键索引都是非聚簇的，叶子节点记录的是数据的地址。

（5）MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

1.16.6 B树和B+树对比

1）B+ 树和 B 树的差异

（1）B+树中非叶子节点的关键字也会同时存在子节点中，并且是在子节点中所有关键字的最大值（或最小）。

（2）B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。

（3）B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

2）B+树为什么IO的次数会更少

真实环境中一个页存放的记录数量是非常大的（默认16KB），假设指针与键值忽略不计（或看做10个字节），数据占 1 kb 的空间：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放16条记录。

如果B+树有2层，最多能存放1600×16 = 25600条记录。

如果B+树有3层，最多能存放1600×1600×16 = 40960000条记录。

如果存储千万级别的数据，只需要三层就够了。

B+树的非叶子节点不存储用户记录，只存储目录记录，相对B树每个节点可以存储更多的记录，树的高度会更矮胖，IO次数也会更少。