决战圣地玛丽乔亚Day20---MyIsam & InnoDB

MyIsam和InnoDB的区别

1. InnoDB支持事务！MyIsam不支持事务。

 InnoDB对于语句的执行会默认封装成事务执行，最好把多条语句封装一起再提交，高频率提交事务也不是很好。

2.InnoDB支持外键，MyIsam不支持

3.数据结构不同，虽然索引的结构都是B+树，但是InnoDB采用聚集索引，MyIsam采用非聚集索引。这也是很关键的一个区别。

   InnoDB由于使用聚簇索引，一定会有主键，没有主键会自动生成一个唯一列作为主键。Myisam可以没有主键的存在。

理解这一点需要深刻的理解聚簇索引和非聚簇索引的以及B+树的关系。之前的内容已经讲的很清楚。

包括为什么InnoDB使用了聚簇索引不建议主键太散，（因为为了维护B+树的特性，叶子节点需要排列，如果散开需要不断排列）。

InnoDB的索引和数据都放在叶子结点，Myisam的索引和数据是分开的，具体的做法就是：

表中的数据单独存放在一个文件，索引单独存放在一个文件。Myisam会为主键创一个索引，叶子节点存（主键值+数据的地址）

MyIsam的主键索引和辅助索引结构没有任何差别，都需要先找到叶子结点然后去数据文件二次查找。区别就是辅助索引的key可能部唯一，但是主键的key唯一，这就导致即使是等值查询，也要用范围查找的方式来做。

myisam 通过 key_buffer 把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢的原因。

上面这个举个例子，我需要找某条数据，找到了叶子节点所在的索引，把他们都先缓存到内存中然后再进行比对，如果发现命中了缓存就会更快。

下面这个图就很形象：

 

 4.InnoDB不保存行号，MyIsam保存行号。所以在做Count全表行数的查找，MyIsam可以秒算。

InnoDB由于存在事务的原因，行数在不同时刻的准确性没法保证，而且如果索引不是覆盖innodb的缓冲区，count计算会慢，如果只需要知道大概的行数可以用  show table status.

5.   5.7之前InnoDB是不支持全文索引的， 5.7之后就支持了。

6. MyISAM表格可以被压缩后进行查询操作

7.MyIsam没有行锁。这里注意 InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

 

如何选择：
1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

 

innodb引擎的4大特性 ： 插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

________________________________________________________________________________________________________________________________________________

优化器：

优化器对于联合索引的优化：

联合索引的查询，一般是Where  XXX  AND  XXX

对于不按顺序的Where条件，优化器也会对其进行一个优化：、

1)        如果两个列都没有索引，那么使用全表扫描。

2)        否则，如果其中一个列拥有更好的存取类型（比如，一个具有索引，另外一个没有索引；再或者，一个是唯一索引，另外一个是非唯一索引），那么使用该列作为驱动表达式。

3)        否则，如果两个列都分别拥有索引，并且两个条件对应的存取类型是一致的，那么选择定义索引时的先定义的索引。

简单来讲就是优化器会优先按顺序选择索引来作为驱动表达式。通用，我们应该避免用不到的字段加入联合索引，没有意义且会拖慢时间。

 

优化器关于回表导致索引失效的问题：

使用索引什么情况下会回表？

通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。

简单来说，如果使用主键索引，不需要回表。 如果是辅助索引，需要回表。

如果是联合索引，如果联合索引是覆盖索引（即，索引的字段在覆盖查找字段）不需要回表。

 

mysql执行sql前会执行sql优化、索引选择等操作，mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价，

从中选择一个mysql认为代价最小的方式进行sql查询操作。而在回表数据量比较大时，经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况。
从Mysql5.6后，我们可以使用SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE来分析sql执行计划。

由于使用索引后，由于需要回表操作导致IO随机读所预估耗时比全表扫描聚合索引顺序IO的耗时长的情况下

通过分析sql执行过程，可以发现选择索引错误的是因为使用的索引回表记录太多导致预估查询成本大于全表扫描最终选择了错误的索引。

因此减少该sql的执行时间，下一步的优化方案是减少该sql的回表操作，即让该sql进行索引覆盖解决回表过多导致索引使用错误的问题。

具体参考文章：https://blog.csdn.net/sz85850597/article/details/91999015