innodb存储引擎

innodb存储引擎

1.存储：数据目录。有配置参数为“ innodb_data_home_dir ” 、“ innodb_data_file_path ” 和

  “innodb_log_group_home_dir”这三个目录位置指定参数决定存储目录，以及参数“innodb_file_per_table”决定Innodb 的表空间存储方式

 存储文件：frm，ibd结尾的文件。frm存储表结构，ibd存储索引和数据

 存储日志：ib_logfilen文件

2.innodb存储引擎开启或关闭：

 关闭innodb_fast_shutdown=

 0 完成所有的full purge和merge insert buffer操作(如：做InnoDB plugin升级时)

 1 默认，不需要完成上述操作，但会刷新缓冲池中的脏页

 2 不完成上述两个操作，而是将日志写入日志文件，下次启动时，会执行恢复操作recovery

 没有正常地关闭数据库(如：kill命令)/innodb_fast_shutdown=2时，需要进行恢复操作。 

 恢复innodb_force_recovery=

 0 默认，但需要恢复时执行所有恢复操作

 1 忽略检查到的corrupt页

 2 阻止主线程的运行，如主线程需要执行full purge操作，会导致crash

 3 不执行事务回滚操作

 4 不执行插入缓冲的合并操作

 5 不查看撤销日志undo log，InnoDB存储引擎会将所有未提交的事务视为已提交

 6 不执行前滚的操作

3.表空间：

  表空间：单独表空间和共享表空间。表空间的最大限制为64TB

  查看当前表空间设置：

  mysql> show variables like "innodb_file_per_table";

  ON代表单独表空间管理，OFF代表共享表空间管理；（查看单表的表空间管理方式，需要查看每个表是否有单独的数据文件）

  修改数据库的表空间管理方式：

  修改innodb_file_per_table的参数值即可，但是修改不能影响之前已经使用过的共享表空间和单独单独表空间；

  innodb_file_per_table=1 为使用单独表空间

  innodb_file_per_table=0 为使用共享表空间

  mysql> show variables like ‘innodb_data%‘;

  配置参数：

  innodb_data_file_path=ibdata1:2G;ibdata2:3G:autoextend:max:4G //指定两个共享空间。大小分别是2G和3G。当两个都满了后ibdata2自动增长最大4G

                       也可以指定存储路径：innodb_data_file_path=/tmp/ibdata1:2G;ibdata2:3G:autoextend:max:4G

  需提前把innodb_data_home_dir=  设置为空。

  共享表空间的优点:

  表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制（很多文档描述有点问题）。

  数据和文件放在一起方便管理

  共享表空间的缺点:

  所有的数据和索引存放到一个文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，当数据量非常大的时候，

  表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，对于经常删除操作的这类应用最不适合用共享表空间。

  共享表空间分配后不能回缩：当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了（

  可以理解为mysql的表空间10G，但是才使用10M，但是操作系统显示mysql的表空间为10G），进行数据库的冷备很慢

  单独表空间的优点:

  每个表都有自已单独的表空间，每个表的数据和索引都会存在自已的表空间中，可以实现单表在不同的数据库中移动。

  空间可以回收（除drop table操作处，表空不能自已回收）

  Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

  对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

  对于使用单独表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

  单独表空间的缺点:

  单表增加过大，当单表占用空间过大时，存储空间不足，只能从操作系统层面思考解决方法；

4.事务：

  事务的四个特性：原子性，一致性，隔离性，持久性。

  事务的开启： set autocommit=1 //开启事务，0则是关闭事务

  事务的提交：

  start transaction //开启

  commit;//提交

  隐士提交：

  隐式提交的SQL语句

以下这些SQL语句会产生一个隐式的提交操作，即执行完这些语句后，会有一个隐式的COMMIT操作。

1、DDL语句：ALTER DATABASE...UPGRADE DATA DIRECTORY NAME、。。。。

    2、用来隐式的修改mysql架构的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD。

    3、管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMIZE TABLE 、REPAIR TABLE。

  事务的隔离级：

  set {global|session} transaction=

1、READ UNCOMMITED

2、READ COMMITED

3、REPEATABLE READ

4、SERIALIZABLE

  查看当前会话的事务隔离级别：

  select @@tx_isolation;

  插卡看全局事务隔离级别：

  select @@global.tx_isolation;

  在SERIALIZBLE的事务隔离级别，InnoDB存储引擎会对每个SELECT语句后自动加上LOCK IN SHARE MODE，即给每个读取操作加一个共享锁，

  因此在这个事务隔离级别下，读占用锁了，一致性的非锁定读不再予以支持，一般不再本地事务中使用SERIALIZBLE的隔离级别，

  SERIALIZABLE的事务隔离级别主要用于InnoDB存储引擎的分布式事务。

  在READ COMMITED的事务隔离级别下，除了唯一性的约束检查以及外键约束的检查需要Gap Lock，InnoDB存储引擎不会使用Gap Lock的锁算法。

  分布式事务:

  通过XA事务可以来支持分布式事务的实现，在使用分布式事务时，InnoDB存储引擎必须使用SERIALIZABLE的隔离级别，

  查看是否启用了XA事务支持（默认开启)

  show variables like ‘innodb_support_xa‘

5.外键

  外键的使用需要满足下列的条件：

  1. 两张表必须都是InnoDB表，并且它们没有临时表。

  2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

  3. 建立外键关系的对应列必须建立了索引。

  4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

  如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，

  最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。

  InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:

  1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

  2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。

     ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

  3. NO ACTION: InnoDB拒绝删除或者更新父表。

  4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

  5. SET DEFAULT: InnoDB目前不支持。

  外键约束使用最多的两种情况无外乎：

  1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；在外键定义中，使用ON UPDATE CASCADE ON DELETE RESTRICT；

  2）父表更新时子表也更新，父表删除时子表匹配的项也删除。在外键定义中，可以使用ON UPDATE CASCADE ON DELETE CASCADE；

  InnoDB允许你使用ALTER TABLE在一个已经存在的表上增加一个新的外键:

   ALTER TABLE tbl_name

     ADD [CONSTRAINT [symbol]] FOREIGN KEY

     [index_name] (index_col_name, ...)

     REFERENCES tbl_name (index_col_name,...)

     [ON DELETE reference_option]

     [ON UPDATE reference_option]

  InnoDB也支持使用ALTER TABLE来删除外键：

    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

6.内存利用：

   参数：innodb_buffer_pool_size

    这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。

　　该参数分配内存的原则：这个参数默认分配只有8M，如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，

    会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋10％左右做为这个参数的值。

例如：数据大小为50MB,那么给这个值分配innodb_buffer_pool_size＝64MB

    查看参数配置：

show global variables like ‘%inndb_buffer%‘

设置方法：

     set global innodb_buffer_pool_size=4G

    这个参数分配值的使用情况可以根据show innodb status"G;中的

    ----------------------

    BUFFER POOL AND MEMORY

    ----------------------

    Total memory allocated 4668764894; 

    去确认使用情况。

   参数：innodb_additional_mem_pool

    作用：用来存放Innodb的内部目录

    这个值不用分配太大，系统可以自动调。不用设置太高。

通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。

查看参数配置：

     show global variables like ‘%innodb_add%‘

   分配原则：

    用show innodb status"G;去查看运行中的DB是什么状态（参考BUFFER POOL AND MEMORY段中），然后可以调整到适当的值。

    ----------------------

    BUFFER POOL AND MEMORY

    ----------------------

    Total memory allocated 4668764894; in additional pool allocated 16777216

    参考：in additional pool allocated 16777216

    根据你的参数情况，可以适当的调整。

   设置方法：

    innodb_additional_mem_pool=16M

  参数：innodb_max_dirty_pages_pct

    作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.

          这个参数的另一个用处：当Innodb的内存分配过大，致使swap占用严重时，可以适当的减小调整这个值，使达到swap空间释放出来。

   建义：这个值最大在90％，最小在15％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。

     查看擦数设置大小： show global variables like ‘%pct%‘

  设置方法：

  innodb_max_dirty_pages_pct＝90

   动态更改需要有super权限：

  set global innodb_max_dirty_pages_pct=50;

7.关于日志：

  参数：innodb_log_file_size

   作用：指定日志的大小

   分配原则：几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般控制在几个LOG文件相加大小在２Ｇ以内为佳。

   具体情况还需要看你的事务大小，数据大小为依据。

   说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。

   设置方法：

   innodb_log_file_size=256M

  参数：innodb_log_files_in_group

作用：指定你有几个日值组。

分配原则：　一般我们可以用２－３个日值组。默认为两个。

设置方法：

innodb_log_files_in_group=3

  参数：innodb_log_buffer_size：

作用：事务在内存中的缓冲。

分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。一般最大指定为３Ｍ比较合适。

    参考：Innodb_os_log_written(show global status 可以拿到，如果这个值增长过快，可以适当的增加innodb_log_buffer_size

    另外如果需要处理大量的text，或是blob字段，可以考虑增加这个参数的值。

    设置方法：innodb_log_buffer_size=3M

  参数：innodb_flush_logs_at_trx_commit

    作用：控制事务的提交方式

    分配原则：这个参数只有３个值，0，１，２请确认一下自已能接受的级别。默认为１，主库请不要更改了。

    性能更高的可以设置为０或是２，但会丢失一秒钟的事务。

   说明：

   这个参数的设置对innodb的性能有很大的影响，所以在这里给多说明一下。

   当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。

   当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。

      但需要注意的是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

   当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

      mysqld进程的崩溃会删除崩溃前最后一秒的事务。

   从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。

  设置方法：

   innodb_flush_logs_at_trx_commit=1

本文出自 “用户和存储过程” 博客，请务必保留此出处http://9548010.blog.51cto.com/9538010/1624678