MYSQL之存储引擎

MYSQL结构

MySQL存储引擎分类

1. MyISAM引擎

MyISAM引擎特点：

        1.不支持事务
            事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
        2.表级锁定
            数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
        3.读写互相阻塞
            不仅会在写入的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
        4.只会缓存索引
            MyISAM可以通过key_buffer_size的值来提高缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。
        
        5.读取速度较快
            占用资源相对较少
        6.不支持外键约束，但只是全文索引
        7.MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。

MyISAM引擎适用的生产业务场景

        1.不需要事务支持的业务(例如转账就不行，充值也不行)
        2.一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。
        3.读写并发访问都相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)
        4.数据修改相对较少的业务(阻塞问题)
        5.以读为主的业务，例如：www.blog,图片信息数据库，用户数据库，商品库等业务
        6.对数据一致性要求不是很高的业务。
        7.中小型的网站部分业务会用。
        小结：单一对数据库的操作都可以示用MyISAM，所谓单一就是尽量纯读，或纯写(insert,update,delete)等。　

MyISAM引擎调优精要

        1.设置合适的索引(缓存机制)(where、join后面的列建立索引，重复值比较少的建索引等)
        2.调整读写优先级，根据实际需求确保重要操作更优先执行，读写的时候可以通过参数设置优先级。
        3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)。
        4.尽量顺序操作让insert数据都写入到尾部，较少阻塞。
        5.分解大的操作，降低单个操作的阻塞时间，就像操作系统控制cpu分片一样。
        6.降低并发数(减少对MySQL访问)，某些高并发场景通过应用进行排队队列机制Q队列。
        7.对于相对静态(更改不频繁)的数据库数据，充分利用Query Cache(可以通过配置文件配置)或memcached缓存服务可以极大的提高访问频率。
        8.MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
        9.可以把主从同步的主库使用innodb，从库使用MyISAM引擎。主库写，从库读可以(不推荐，有些麻烦的地方，市场上有人这么用)。

2. InnoDB引擎

InnoDB引擎特点：

        1.支持事务：支持4个事务隔离界别，支持多版本读。
        2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
        3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
        4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
        5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
        6.所有SecondaryIndex都会保存主键信息。(了解)
        7.支持分区，表空间，类似oracle数据库。
        8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
        9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
        
        小结：三个重要功能：Supports transactions，row-level locking，and foreign keys

InnoDB引擎适用的生产业务场景

        1.需要事务支持(具有较好的事务特性，例银行业务)
        2.行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
        3.数据更新较为频繁的场景，如：BBS(论坛)、SNS(社交平台)、微博等
    
        4.数据一致性要求较高的业务，例如：充值转账，银行卡转账。
        5.硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO，可以通过一些参数来设置，这个就不细讲啦~~~
        
        6.相比MyISAM引擎，Innodb引擎更消耗资源，速度没有MyISAM引擎快

InnoDB引擎调优精要

        1.主键尽可能小，避免给Secondery index带来过大的空间负担。
        2.避免全表扫描，因为会使用表锁。
        3.尽可能缓存所有的索引和数据，提高响应速度，较少磁盘IO消耗。
        4.在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交，有开关可以控制提交方式。
        5合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
        如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。
        6.避免主键更新，因为这会带来大量的数据移动。

InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选
择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高
         的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操 
         作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的 
         锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比 
         较高的系统，InnoDB是最合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发
         性要求不是很高，那么选择这个存储引擎是非常合适的。