mysiam和innodb索引结构的差异

一、myisam的索引和记录是分开存储，数据记录是聚集存储在一块区域，索引以b+树单独存储，叶子节点存储索引以及指向数据记录的指针，myisam的主键索引和普通索引结构类似，数据记录和索引都是分开存储。
二、innodb的表必定会有一个聚簇索引，有主键，则主键是聚簇索引，没有主键则第一个非null，unique列是聚簇索引，否则，rowid为聚簇索引。聚簇索引为b+树结构，叶子节点，存储聚簇索引以及实际的数据记录。普通索引也为b+树结构，叶子节点存储普通索引以及聚簇索引。通过聚簇索引找数据会非常快，因为，聚簇索引的叶子节点直接就存储了记录，通过普通索引找数据，则会先通过普通索引找到聚簇索引，再通过聚簇索引找到数据记录。

故：1、聚簇索引不宜太大，因为，每个普通索引都会存储一份聚簇索引。
2、适合用递增的数据作为聚簇索引，否则，插入数据的时候，会导致b+树数据分裂，聚簇索引和数据记录发生移动。

锁差异

myisam不支持行锁，只支持表锁lock table，在读写操作的时候，都会对数据添加表锁，select的时候，自动获取表级读锁。读和读可以并行，读和写不可以并行，故select没有释放读锁，update、insert、delete这种写操作需要获取写锁，需要等待读锁释放。写锁和写锁互斥，写锁和读锁互斥，所以，update、insert、delete没有释放写锁的时候，select操作需要等待。和java的ReentrantReadWriteLock读写锁类似，但是myisam的写锁优先级高于读锁，就是说，写锁和读锁都在等待获取锁，即使读锁先进入等待队列，写锁还是会先获取锁，因为，myisam认为写操作少，读操作多，写比读重要，故myisam不适合写多读少的场景。

故：myisam高并发下，效率比较低，不建议使用。

举个实际的例子：
test表是myisam引擎。
一个会话：insert into test values (14, 'abcd', '3' and sleep(90));
另一个会话：insert into test values (15, 'abcd', '3');会堵塞，直到第一个会话释放表级写锁。
而如果是innodb就不会。

innodb支持多种锁，锁的是索引，而不是实际的数据记录，故如果锁不到实际的索引，则会锁住整个表，比如 where id != 10。
详情看之前的记录：innodb的7种锁 https://www.jianshu.com/p/8c0c9d3b0306

关于count()
myisam会直接存储行数，故select count()可以直接读取这个数据，很快，但是如果加上where 条件，则和innodb一样需要遍历所有行数，依然消耗很大。
innodb则需要遍历所有行数，故消耗很大。

关于全文索引
myisam支持全文索引，innodb5.6版本之后InnoDB存储引擎开始支持全文索引，5.7版本之后通过使用ngram插件开始支持中文。
比如like查询：like %abc%,不走索引，效率很低，然而使用全文索引，仅效率层面上会有所提高。
但是，因为全文索引占用数据库资源比较多，故，对于模糊搜索类似的请求，还是用外置索引比较好，比如es、solr类似的搜索引擎。

关于事务
myisam不支持事务，由于其不支持事务，在系统异常崩溃的时候，有可能会导致文件损坏。
innodb支持事务，但是事务比较消耗性能。

关于外键
myisam不支持外键。innodb支持外键。但是实际使用中，不建议在数据库中使用外键维护数据完整性，占用资源，影响性能，会让数据关系变得极其复杂，不利于数据的处理。而是通过程序来弱控制数据关系。