浅谈MYSQL引擎之INNODB引擎

2322312

MYSQL 常用的引擎主要有一下几种，MRG_MYISAM 、CSV 、MyISAM、InnoDB、MEMORY ，NDB，其中MyISAM、InnoDB是mysql最常用的存储引擎，今天主要讨论 InnoDB引擎。


一、什么是InnoDB引擎
        InnoDB引擎是MYSQL数据库的另一个重要的额存储引擎，正成为目前MYSQL AB所有发行新版的标准，被包含在所有二进制安装包里。
和其他的存储引擎相比，InnoDB引擎的优点支持兼容ACID的事物，以及参数完整性(即对外建的支持)。
MYSQL5.5.5以后数据库的默认存储引擎为InnoDB引擎


二、InnoDB引擎的特点
1、支持事物：支持4个事物隔离级别，支持多版本读（ACID :原子性、一致性、隔离性、持久性.）
2、行级锁定(更新时一般是锁定当前行，InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非 锁定读)：通过索引实现，全表扫描仍然会是锁表。
3、读写阻塞与事物隔离级别有关
4、具有非常高效的缓存特性：能缓存索引，也能缓存数据（InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。 InnoDB存储它的表＆索引在一个表空间中，表空间可以包含多个文件（或原始磁盘分区））
5、整个表和主键以Cluster方式存储，组成一颗平衡树
6、所有Secondar Index都会保存主键信息
7、支持分区，表空间,类似oracle数据库
8、支持外键约束，5.5版本以后支持全文索引

三、事务的四大特性(ACID)
1、原子性
        事务是一个不可分割的单位,事务中的所有SQL灯操作要么都发生，要么都不发生.
2、一致性
        事务发生之前和发生之后，数据的完整性必须保持一致.
3、隔离性
        当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据相互隔离的。
4、持久性
        一个事务一旦被提交，它对数据库中的数据改变就是永久性的，如果出了错误，事务也不允许撤销，只能通过“补偿性事务”.

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

1、需要事务支持的业务(具有较好的食物特性)
2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
3、数据更新较为频繁的场景
4、数据一致性要求较高的业务
5、硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO

五、InnoDB引擎调优精要
1、主键尽可能小，避免给Secondar Index带来过大的空间负担
2、避免全表扫描，因为会使用表级锁
3、尽可能缓存所有的索引和数据。，提高响应速度，减少磁盘IO消耗
4、在大批量插入的时候，尽量自己控制事物而不要使用autocommit自动提交有开关可以控制提交方式
        【  取消自动提交事物
           mysql> set global init_connect="set autocommit=0";
           init_connect='SET autocommit=0' // 在mysqld里面加上这些内容  
        】
5、合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性、
  (如果innodb_flush_log_at_trx_commit=0 log buffer 每秒就会被刷写日志文件到磁盘，提交事物的时候不做任何操作)
6避免主键更新，因为这会带来大量的数据移动

六、生产环境中如何批量更改mysql引擎
alter table table_name ENGINE = INNODB

七、InnoDB与MYISAM区别总结
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM        只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
        在执行数据库写入的操作（insert,update,delete）的时候，mysiam表会锁表，而innodb表会锁行。通俗点说，就是你执行了一个update语        句，那么mysiam表会将整个表都锁住，其他的insert和delete、update都会被拒之门外，等到这个update语句执行完成后才会被依次执行
        另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update         table set num=1 where name like “%aaa%”

3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索        引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

八、InnoDB与MYISAM如何选择
    当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快。