【MySQL】InnoDB和MyISAM区别

一、索引不同

1 InnoDB聚簇索引，MyISAM非聚簇索引

MyISAM 这棵树的叶子结点存储数据是物理地址，InnoDB的叶子结点直接存储数据记录，这也是簇索引与非簇索引的区别。

1 InnoDB聚簇索引

• 表数据文件本身就是按B+Tree组织的一个索引结构文件，数据文件是和（主键）索引绑在一起的。
• 聚集索引 - 叶子节点包含了完整的数据记录
• 必须要有主键，通过主键索引效率高。如果建表时未创建主键，MySQL会从表中找到一列全部不相等的数据，作为主键，维护索引树，如果找不到，则维护一个隐藏列，用于维护索引树。这个工作尽量让我们自己完成，不必再消耗MySQL的性能。
• 辅助索引叶子节点存储的数据为主键id；
• 使用辅助索引查询时，需要查询两次，先查询到主键，然后再通过主键查询到数据。

innodb主键索引：查找数据流程  -- 查询主键 = 30的数据；
1 将根节点，加载到内存中，在内存中通过二分查找算法，快速找出30的位置，找到对应的页；
2 再将这一页数据，加载到内存中，快速找出30的位置，找到对应的页；
3 最后定位到叶子节点，对应位置，从叶子节点中取出数据data。

innodb辅助索引 ： 查找数据流程   - -  辅助索引 = Eric
1 将辅助索引的根结点，加载到内存中，在内存中通过二分查找，找出对应页；
2 以此类推，直到定位到叶子节点，对应位置，从叶子节点中取出，当条数据的主键id；
3 使用主键id，在主键索引树中，回表查询，查处对应数据。

2 MyISAM非聚簇索引

• MyISAM是非聚簇索引，索引和数据分开存储，也是使用B+Tree作为索引结构，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
• 叶子节点只包含索引地址，不包含数据，数据在另外一个文件MYD文件。

MyISAM 查找数据流程：查找col = 30的数据
1 从MYI文件中，查找索引树，定位数据所在的叶子节点，通过叶子节点中包含的数据地址；
2 在图中右下加，MYD文件中，找出具体的数据。

2 InnoDB必须要有主键，MyISAM允许没有主键

• MyISAM 允许没有任何索引和主键的表存在，索引都是保存行的地址。
• InnoDB 如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

3 InnoDB支持外键

• InnoDB支持外键，而MyISAM 不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

4 InnoDB不支持全文索引

• InnoDB不支持fulltext全文索引，MyISAM支持；InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好。

5 索引保存位置不同

• MyISAM 的索引以表名+.MYI文件分别保存。
• InnoDB的索引和数据一起保存在表空间里。

二、对事物的支持

• InnoDB支持事务，MyISAM不支持。
• InnoDB支持事务，支持外键、行锁、事务是他的最大特点，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。
• MyISAM 强调的是性能，每次查询具有原子性。其执行数度比InnoDB类型更快，但是不提供事务支持)。MyISAM 默认表类型，它是基于传统的ISAM类型，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键。

三、存储结构不同

• MyISAM 数据和索引是分别存储的，数据文件的扩展名为(.MYD)。索引文件的扩展名是(.MYI)。
• InnoDB 数据和索引是集中存储的(.ibd)，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

四、存储空间不同

• MyISAM 可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
• InnoDB 需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

五、支持锁粒度不同

• MyISAM 只支持表级锁，用户在操作MyISAM 表时,select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
• InnoDB支持事务和行级锁，行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

六、count()函数不同

• MyISAM 保存有表的总行数，如果select count(*) from table;会直接取出出该值。

• InnoDB 没有保存表的总行数，如果使用select count(*) from table;就会遍历整个表，消耗相当大，但是在加了wehre条件后，MyISAM 和InnoDB处理的方式都一样。

七、常见问题

1 InnoDB 为什么一定要有主键？

1 bd文件在存储的时候，这个文件必须用B+树的结构来组织，B+树来源：
2 有主键，直接用主键；
3 没有主键不建索引，MySQL会从表中找到一列全部不相等的数据，作为主键，维护索引树，
4 如果找不到，则维护一个隐藏列，用于维护索引树。
5 这个工作尽量让我们自己完成，不必再消耗MySQL的性能。

2 InnoDB 为什么推荐使用整型的自增主键做索引?

整型：
1 查找元素，都从根节点开始查找，经历了很多次比较大小的操作，
使用整型比较大小更快，字符串比较大小逐位比较，从左到右，整型效率更快。 
2  一个索引占用的磁盘空间越小，整个索引占用的空间也就越小。节约磁盘（很贵）空间，整型占用的页大小，更小 。
自增：
1 B+树，叶子节点是双向指针，排好序的，对范围查找友好；
2 B+树，插入非自增的索引，索引树可能需要分裂、树需要再次平衡；
插入自增主键，可以减少索引树分裂和再次平衡的问题

3 为什么InnoDB主键索引结构叶子节点存储的是主键值？

1   一致性和节省存储空间
2   innodb只有一个聚簇索引，如果建了主键，就会直接用主键做聚簇索引。
3   二级索引的叶子节点放的值，是聚集索引的索引值。为了保证一致性、节省存储空间，减少复杂度。二级索引，是非聚集索引，稀疏索引，需要回表查询。

4 聚簇索引，和非聚簇索引哪个查询效率更快？

1   聚簇索引更快，定位到叶子节点时，就已经拿到了整行数据；
2   非聚簇索引，需要跨文件去查，在MYI文件中找到叶子节点中数据地址，
在通过数据地址在MYD文件中，去查找整行数据；

5 联合索引的底层结构长什么样？

1    图中是联合主键索引，是聚集索引，按照联合主键字段从左到右的顺序，左小右大的顺序进行排序。
叶子节点数据页，保存的是具体的数据，不是聚集索引的值，不用做回表操作。
2   树的高度要提升，即需要把当前页的第一个数据的索引，提升上去

6 最左前缀原则

1   索引是排好序的数据结构；
2   不按照最左前缀原则走，使用的索引不符合排好序的原则，索引就会失效。

如有缺漏或不对的地方还请各位指正。

欢迎关注我的个人公众号