MyISAM和InnoDB的区别

一、使用的锁

• MyISAM:
  myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

• InnoDB ：
  Innodb支持事务和行级锁，是innodb的最大特色。
  事务的ACID属性：atomicity,consistent,isolation,durable。
  并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。

  锁的机制可参考：MySQL锁机制

二、实现上的区别

  1、InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
  2、InnoDB支持外键，而MyISAM不支持。
  3、InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

myisam允许没有任何索引和主键的表存在
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)

  4、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。
  5、Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；5.7以后的InnoDB支持全文索引了。
  6、InnoDB支持表、行级锁(默认)，而MyISAM支持表级锁。
  7、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有。
  8、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI。

.frm 用于存储表的定义
.MYD 用于存放数据
.MYI 用于存放表索引

三、sql优化的注意点

  1、使用EXPLAIN关键词检查SQL。EXPLAIN可以帮你分析你的查询语句或是表结构的性能瓶颈，就得EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的，是否有全表扫描等；
  2、查询的条件尽量使用索引字段，如某一个表有多个条件，就尽量使用复合索引查询，复合索引使用要注意字段的先后顺序。
  3、多表关联尽量用join，减少子查询的使用。表的关联字段如果能用主键就用主键，也就是尽可能的使用索引字段。如果关联字段不是索引字段可以根据情况考虑添加索引。
  4、尽量使用limit进行分页批量查询，不要一次全部获取。
  5、绝对避免select *的使用，尽量select具体需要的字段，减少不必要字段的查询；
  6、尽量将or 转换为 union all。
  7、尽量避免使用is null或is not null。
  8、要注意like的使用，前模糊和全模糊不会走索引。
  9、Where后的查询字段尽量减少使用函数，因为函数会造成索引失效。
  10、避免使用不等于（!=），因为它不会使用索引。
  11、用exists代替in，not exists代替not in，效率会更好；
  12、避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。
  13、千万不要 ORDER BY RAND()

参考文献

1、https://mp.weixin.qq.com/s/KVY9StAhXGTy1jshHHkawQ
2、https://www.cnblogs.com/y-rong/p/8110596.html