Mysql各数据库引擎优缺点,以及常用表锁，行锁，页面锁(个人总结)

功能	MYISAM	MEMORY	INNODB	ARCHIVE
事务	不支持	不支持	支持	不支持
哈希索引	不支持	支持	不支持	不支持
BTREE索引	支持	支持	支持	支持
锁机制	表锁	表锁	行锁	行锁

行级锁：

        优点：

                1，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

        缺点：

                1，开销大，加锁慢；会出现死锁；

        应用场景：行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有 并发查询的应用

        注意：MySQL的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然访问不同行的纪录，但是如果使用相同索引的键，是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外不论是使用主键索引，唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁

页面锁：

        优点：

                1，页级锁页级锁是Mysql中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但是冲突多，行级冲突少，但是速度慢。页级锁折中，以此锁定相邻的一组纪录

        缺点：

                1，开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

        应用场景：  

表锁：

        优点：

                1，开销小，加锁快；不会出现死锁；

        缺点：

                1，锁定粒度大，发出锁冲突的概率最高，并发性最低

        应用场景：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用

MYISAM：

        优点：

                1，访问速度快。

        缺点：

                1，MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

        应用场景：

                1，对事务完整性没有要求；表的数据都会只读的。

        注意：行级锁和死锁，MyISAM是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成死锁的可能

MEMORY：

        优点：

               1，访问速度较快。

               2，MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。

        缺点：

               1，MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。

        应用场景：

                1，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用

               

INNODB：

        优点：

                1，支持事务和崩溃修复能力；引入了行级锁外键约束。

        缺点：

                1，占用的数据空间相对较大。

        应用场景：

                       1，需要事务支持，并且有较高的并发读写频率。

        注意：

                1，不建议使用较长的列做主键，例如char(64)，因为所有的普通索引都会存储主键，会导致普通索引过于庞大；

                2，建议使用趋势递增的key做主键，由于数据行与索引一体，这样不至于插入记录时，有大量索引分裂，行记录移动；

        总结：

                MyISAM和InnoDB都使用B+树来实现索引：MyISAM的索引与数据分开存储

                MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别

                InnoDB的聚集索引和数据行统一存储

                InnoDB的聚集索引存储数据行本身，普通索引存储主键

                InnoDB一定有且只有一个聚集索引

                InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK

ARCHIVE：

        优点：

                1，插入快，查询快，占用空间小

                2，支持行级锁和专用的缓存区，可实现高并发

        缺点：

                1，仅支持select 和 insert 

                2，只允许在自增ID列上加索引

                3，支持分区，不支持事务处理

                4，不支持对数据的修改

        应用场景：

                1,ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。      

        BTREE索引：

                优点：

                        1，很适合磁盘存储，能够充分利用局部性原理，磁盘预读；

                        2，很低的树高度，能够存储大量数据；

                        3，索引本身占用的内存很小；

                        4，能够很好的支持单点查询，范围查询，有序性查询；

                         5. ⾮叶⼦节点上可以存储更多的键值，相应的树的阶数（节点的⼦节点树）就会更⼤，树也就会变得更矮更胖。这样⼀来我们查找数
据进⾏磁盘I/O的次数就会⼤⼤减少，数据查询的效率也会更快。

                        
                        6. 所有数据记录都有序存储在叶⼦节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

                        
                        7. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的⽀持就可以⽅便的在数据查询后进⾏升序或者降序操作。

                        8. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的⽀持就可以⽅便的在数据查询后进⾏升序或者降序操作。
 

                缺点：相对而言B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

                应用场景：

                        1，主流索引

        HASH索引：

                优点：

                        1,hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引

                缺点：

                        1,哈希索引数据不是按照索引值顺序存储，无法用于排序。

                        2,不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。

                        3,只支持等值比较，不支持范围查询。(Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引)

                        4,当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。

                        5,存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询

                        6, 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的⽀持就可以⽅便的在数据查询后进⾏升序或者降序操作。

                应用场景：

                        1，一般特定的数据库支持hash索引