MySQL常用存储引擎之Innodb

1、MySQL5.5及之后版本默认存储引擎(5.5.8)

2、事务型的存储引擎

3、Innodb使用表空间进行数据存储

    innodb_file_per_table = ON：独立表空间：tablename.ibd

    innodb_file_per_table =OFF：系统表空间：ibdataX

    (1) 查看表的存储表空间的存储方式值

        show variables like 'innodb_file_per_table';

        

    (2) 创建存储引擎为innodb的表myinnodb

        create table myinnodb(id int, c1 varchar(10)) engine = 'innodb';

        

    (3) 查看表的存储表空间myinnodb

        ls -lh myinnodb*

        

    (4) 修改存储表空间的存储方式值为OFF

        set global innodb_file_per_table=off;

        

    (5) 重新创建存储引擎为innodb的表myinnodb_g

        create table myinnodb_g(id int, c1 varchar(10)) engine = 'innodb';

        

    (6) 查看myinnodb_g的存储表空间

        ls -lh myinnodb*

        

        可以看出myinnodb_g只有一个.frm文件，数据存储在了共享表空间ibdata1中：

            

4、系统表空间和独立表空间要如何选择

    (1) 比较：

        . 系统表空间无法简单的收缩文件大小

            收缩系统表空间的方法：

                将整个数据库中所有的innodb表导出，删除innodb相关的表空间文件，重启MySQL服务器进行表空间的重建，重新导入数据。(工序复杂，耗时)

        . 独立表空间可以通过optimize table命令收缩系统文件

        . 系统表空间会产生IO瓶颈

            只有一个文件，如果同时对多个表进行数据刷新时是顺序进行的，所以会产生IO瓶颈。

        . 独立表空间可以同时向多个文件刷新数据

    (2) 建议：

        . 对Innodb使用独立表空间

5、把原来存在于系统表空间中的表转移到独立表空间中的方法：

    步骤：

        (1) 使用mysqlIdump导出所有数据库表数据

        (2) 停止MySQL服务，修改my.cnf参数文件，加入innodb_file_per_table=ON参数，并删除Innodb相关文件

        (3) 重启MySQL服务，重建Innodb系统表空间

        (4) 重新导入数据

        注意：如果是主从服务的话，建议先操作从数据库。

6、Innodb存储引擎的特性

    (1) Innodb是一种事物性存储引擎

    (2) 完全支持事务的ACID特性

    (3) Redo Log 和 Undo Log

        a. Redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

            重做日志缓冲(redo log buffer)：

                show variables like 'innodb_log_buffer_size';

                

            重做日志文件(redo_log_file)：

                show variables like 'innodb_log_files_in_group';

                

                

        b.  Undo Log有两个作用：提供回滚和多个行版本控制(MVCC)。

            在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

            当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

    (4) Innodb支持行级锁

        a. 行级锁可以最大程度的支持并发

        b. 行级锁是由存储引擎实现的

        什么是锁？

            . 锁的主要作用是管理共享资源的并发访问

                例子：一个邮箱中所有的邮件都是串行连接在一起的，彼此首尾相连，这种结构对于读取和投递邮件都有很大的好处，当有新的邮件进来时，插入到文件末尾就行了。当有两个两个用户同时对同一邮箱进行邮件投递，很有可能邮箱数据会被破坏，两封信内容交叉在一起，这时锁保证了在一个用户进行投递时，另一个用户会阻塞，而无法向邮件末尾插入邮件。

            . 锁用于实现事务的隔离性

        锁的类型：

            

            . 共享锁(也称读锁)

            . 独占锁(也称写锁)

                desc myinnodb; # 查看myinnodb表结构

                insert into myinnodb values(2, 'bb'),(3, 'cc'); # 往表中插入两条数据

                begin; # 开启事务

                update myinnodb set c1 = 'bbbb' where id = 2; # 修改表中id=2的数据，不提交事务

                select * from myinnodb where id = 2; # 打开一个新的连接，查询表中id=2的数据，查询出的数据为存储在 Undo Log中的数据版本。

                

                

        锁的粒度:

            概念：指被加锁的最小单位。如在行上加锁，那么锁的最小单位就是行，这个锁就是行级锁；如在数据页上加锁，那么锁的最小单位就是页，这个锁就是页级锁；如在表上加锁，那么锁的最小单位就是表，这个锁就是表级锁。

            . 表级锁

                lock table myinnodb write; # 对表myinnodb进行加锁

                

                select * from myinnodb; # 打开一个新的连接，查询myinnodb表中的数据

                

                unlock table; # 对表进行解锁

                

                select * from myinnodb; # 在新的连接，查询myinnodb表中的数据

                

            . 行级锁

                行级锁可以最大程度的支持并发处理，同时锁的开销也比表级锁大，行级锁只在存储引擎中进行实现。

        阻塞和死锁：

            什么是阻塞？

                阻塞是因为不同锁之间兼容性的关系，在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放它锁占用的资源，这样就形成了阻塞。

            什么是死锁？

                死锁是指两个或两个以上的事务在执行过程中，相互占用了对方等待的资源而产生的一种异常。

    (5) Innodb状态检查

        show engine innodb status;

7、适用场景

    (1) Innodb适合于大多数OLTP应用