innodb 引擎

一.概述

InnoDB 是一个用的比较广泛的存储引擎,因为它支持事物和外键,还有不错的效率;我们先看看官方教程怎么说;

mysql innodb 引擎

　　我们先读一下, 对于上面的文档, 对一个InnoDB的表首先它在磁盘上的呈现形式是一个 .frm 文件和一个表空间文件, 对于InnoDB的表它的数据和索引是存放在表空间里面的.InnoDB的表空间是一个逻辑的单存储区域,这个区域由一个或多个文件组成,它的大小决定于文件系统的限制,我们可以把它放在不同的分区上,他还可以存放在裸分区上,突破文件系统的限制,提高性能.

　　它有共享和单独的表空间, 它的表空间存储跟机器的无关的,因此我们可以吧一个InnoDB的表空间复制到另外的一个地方来实现表的备份.

　　他提供完整的事物和外界支持支持, 他还提供基于日志的数据修复功能,

　　他对于多线程提供多版本控制,不同的线程看到的是各自的版本, 它采用的是基于索引的行级锁, 因此可能会发生一定死锁

二. InnoDB 引擎详解

InnoDB 引擎体系结构图

1. 表空间

　　InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中：(如:ibdata1)，但是这样不怎么好，增删数据库的时候，ibdata1文件不会自动收缩，单个数据库的备份也将成为问题。通常只能将数据使用mysqldump 导出，然后再导入解决这个问题。

　　在MySQL的配置文件[mysqld]部分，增加innodb_file_per_table参数。可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间。

独立表空间和共享表空间拥的优缺点如下

a) 共享表空间：

优点：

可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。

缺点：

所有的数据和索引存放到一个文件中以为着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。
我们知道共享表空间管理会出现表空间分配后不能回缩的问题，当出现临时建索引或是创建一个临时表的操作表空间扩大后，就是删除相关的表也没办法回缩那部分空间了。我们存在磁盘监控时，也许就报警不断了，但实际上MySQL还可以运行良好。另外，当磁盘上占用较多时性能也不是太好。这种情况处理只能是是建一个新的Slave从主库上Dump出来，然后在Dump到从库中，动作较大。
对于InnoDB Hot Backup备份的操作（或是直接冷备），每次需要CP的文件比较大。如果现在有180G的表空间，但实际数据只有50多G，那么我们将面对每次需要拷180G的数据。这种方式也许mysqldump是一个好的处理方式了。

b) 独立表空间：

在配置文件（my.cnf）中设置： innodb_file_per_table

配置表空间;

 innodb_data_file_path            ibdata1:10M:autoextend 

如要要配置绝对路径,需要把Innodb_data_home_dir 置空

Innodb_data_home_dir = 

 innodb_data_file_path = /tmp/ibdata1:10M:autoextend[:max20M]; /tmp/ibdata1:10M:autoextend[:max20M]

对于以前的表示不受影响的, 只对新表有效;

tablespace 可以自动增长,大小受到文件系统的限制,还可以放到裸设备("裸设备的extent")里面, 大小不再不受文件系统限制,

优点：

每个表都有自已独立的表空间。
每个表的数据和索引都会存在自已的表空间中。
可以实现单表在不同的数据库中移动。
空间可以回收（除drop table操作处，表空不能自已回收）
1. Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
2. 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
3. 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加过大，如超过100个G。

　　对于单表增长过大的问题，如果使用共享表空间可以把文件分开，但有同样有一个问题，如果访问的范围过大同样会访问多个文件，一样会比较慢。对于独立表空间也有一个解决办法是：使用分区表，也可以把那个大的表空间移动到别的空间上然后做一个连接。其实从性能上出发，当一个表超过100个G有可能响应也是较慢了，对于独立表空间还容易发现问题早做处理。

a ) 后台进程

首先我们通过下面几条命令来查看 InnoDB的状态(我的是mysql 5.6.17-win-64版);

1.select version();

mysql> select version();

+------------+

| version()  |

+------------+

| 5.6.17-log |

+------------+

1 row in set (0.03 sec)

2.show variables like '%_io_threads';

mysql> show variables like '%_io_threads';

+-------------------------+-------+

| Variable_name           | Value |

+-------------------------+-------+

| innodb_read_io_threads  | 4     |

| innodb_write_io_threads | 4     |

+-------------------------+-------+

2 rows in set (0.00 sec)

3.show engine innodb status\G;

mysql> show engine innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name:

Status:

=====================================

2014-11-01 19:43:08 1024 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 52 seconds

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

BACKGROUND THREAD

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

srv_master_thread loops: 74 srv_active, 0 srv_shutdown, 35735 srv_idle

srv_master_thread log flush and writes: 35804

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 6

OS WAIT ARRAY INFO: signal count 6

Mutex spin waits 89, rounds 69, OS waits 2

RW-shared spins 4, rounds 120, OS waits 4

RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 0.78 mutex, 30.00 RW-shared, 0.00 RW-excl

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

TRANSACTIONS

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

Trx id counter 2116456

Purge done for trx's n:o < 2115865 undo n:o < 0 state: running but idle

History list length 1246

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 588, OS thread handle 0x1024, query id 924 localhost ::1 root init

show engine innodb status

---TRANSACTION 2116454, not started

MySQL thread id 71, OS thread handle 0x1ed0, query id 908 localhost 127.0.0.1 root cleaning up

--------

FILE I/O

--------

I/O thread 0 state: wait Windows aio (insert buffer thread)

I/O thread 1 state: wait Windows aio (log thread)

I/O thread 2 state: wait Windows aio (read thread)

I/O thread 3 state: wait Windows aio (read thread)

I/O thread 4 state: wait Windows aio (read thread)

I/O thread 5 state: wait Windows aio (read thread)

I/O thread 6 state: wait Windows aio (write thread)

I/O thread 7 state: wait Windows aio (write thread)

I/O thread 8 state: wait Windows aio (write thread)

I/O thread 9 state: wait Windows aio (write thread)

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,

 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

809 OS file reads, 182 OS file writes, 16 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

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

INSERT BUFFER AND ADAPTIVE HASH INDEX

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

Ibuf: size 1, free list len 0, seg size 2, 0 merges

merged operations:

 insert 0, delete mark 0, delete 0

discarded operations:

 insert 0, delete mark 0, delete 0

Hash table size 276707, node heap has 2 buffer(s)

0.00 hash searches/s, 0.00 non-hash searches/s

---

LOG

---

Log sequence number 245998372

Log flushed up to   245998372

Pages flushed up to 245998372

Last checkpoint at  245998372

0 pending log writes, 0 pending chkp writes

12 log i/o's done, 0.00 log i/o's/second

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

BUFFER POOL AND MEMORY

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

Total memory allocated 137428992; in additional pool allocated 0

Dictionary memory allocated 206673

Buffer pool size   8192

Free buffers       7730

Database pages     460

Old database pages 0

Modified db pages  0

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 0, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 460, created 0, written 170

0.00 reads/s, 0.00 creates/s, 0.00 writes/s

No buffer pool page gets since the last printout

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 460, unzip_LRU len: 0

I/O sum[0]:cur[0], unzip sum[0]:cur[0]

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

ROW OPERATIONS

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

0 queries inside InnoDB, 0 queries in queue

0 read views open inside InnoDB

Main thread id 2176, state: sleeping

Number of rows inserted 1, updated 0, deleted 0, read 712

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

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

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

ERROR:

No query specified

　　上面说明innodb_file_io_threads参数值为8，系统默认值为4，实际表明在linux下修改innodb_file_io_threads参数值无效。在innodb plugin中，不在使用innodb_file_io_threads参数，而使用innodb_read_io_threads and innodb_write_io_threads 两个值代替。

　1.master thread线程

　　在mysql5.1中如果没有系统编译的innodb plugin插件引擎。系统默认的innodb引擎，主要的工作都有由一个master thread线程来完成。在innodb plugin引擎中，有线程池来完成，但是在mysql5.5社区版，是没有该功能，在mysql官方文档说线程池的使用在商业版可以用。

每秒都会操作的内容：

日志缓冲刷新到磁盘，即使这个事务还没有提交，这种设计导致很大的事务提交(commit)时也会很快。

合并插入缓冲,在判断I/O次数少于5次时，可以执行插入缓冲操作。

INNODB存储引擎最多每次只会刷新100个脏页到磁盘，每秒是否刷新取决于脏页的比例，如果超过innodb_max_dirty_pages_pct设置的值，就会将100个脏页刷入文件。

　　
mysql> show variables like 'innodb_max_dirty_pages_pct';

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_max_dirty_pages_pct | 75    |

+----------------------------+-------+

1 row in set (0.00 sec)
innodb存储引擎的逻辑存储结构，默认情况下存放砸ibdata1空间中称之为表空间；当定义innodb_file_per_table时，存放在“表名”.idb中，包括数据，索引和插入缓冲；undo文件，系统事物信息和二次写缓冲任然保存在ibdata1中。
表空间由段(segment),区(extent),页(page)组成.
segment由数据段，索引段，回滚段组成。
extent由64个连续的页组成，每页大小为16KB，即大小为1MB.
page(页)有数据页(b-tree page),undo页(undo page), 系统页(system page),事物数据页(transaction system page),插入缓冲位图页(insert buffer bitmap),插入缓冲空闲列表页(insert buffer free list),未压缩的二进制大对象页(uncompressed blob page),压缩的二进制大对象页(compressed blob page)

b) 内存

　　INNODB内存由三部分组成：缓冲池(buffer pool),重做日志缓冲池(redo log buffer)和额外的内存池(additional memory pool)

mysql innodb 引擎

1.缓冲池(buffer pool)

　a) 描述

mysql> show variables like 'innodb_buffer_pool_size';

+-------------------------+-----------+

| Variable_name           | Value     |

+-------------------------+-----------+

| innodb_buffer_pool_size | 134217728 |

+-------------------------+-----------+

1 row in set (0.00 sec)

　　缓冲池中的数据库类型有：索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应hash(adaptive hashindex)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)。

　　InnoDB工作方式：将数据文件按页(每页16K)读入InnoDBbuffer pool，然后按最近最少使用算法(LRU)保留缓存数据，最后通过一定频率将脏页刷新到文件。

2.重做日志缓冲池(redo log buffer)

mysql> show variables like 'innodb_log_buffer_size';

+------------------------+---------+

| Variable_name          | Value   |

+------------------------+---------+

| innodb_log_buffer_size | 8388608 |

+------------------------+---------+

1 row in set (0.00 sec)

3.额外的内存池(additional memory pool)

mysql> show variables like 'innodb_additional_mem_pool_size';

+---------------------------------+---------+

| Variable_name                   | Value   |

+---------------------------------+---------+

| innodb_additional_mem_pool_size | 8388608 |

+---------------------------------+---------+

1 row in set (0.00 sec)

三 : 优化

参数调优内容：

　　内存利用方面
　　日值控制方面
　　文件IO分配，空间占用方面
　　其它相关参数

内存利用方面

　1. innodb_buffer_pool_size

这个参数和MyISAM的key_buffer_size有相似之处，但也是有差别的。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。
该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋１０％左右做为这个参数的值。例如：数据大小为５０Ｍ,那么给这个值分配innodb_buffer_pool_size＝64M
设置方法：
innodb_buffer_pool_size=4G
这个参数分配值的使用情况可以根据show innodb status\G;中的
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4668764894;
去确认使用情况

　2. innodb_additional_mem_pool：

作用：用来存放Innodb的内部目录
这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。
分配原则：
用show innodb status\G;去查看运行中的ＤＢ是什么状态（参考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

　　

关于日值方面：

　1.innodb_log_file_size

作用：指定日值的大小
分配原则：几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。
说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。
设置方法：
innodb_log_file_size=256M

　2.innodb_log_files_in_group

作用：指定你有几个日值组。
分配原则：　一般我们可以用2-3个日值组。默认为两个。
设置方法：
innodb_log_files_in_group=3

　3.innodb_log_buffer_size：

作用：事务在内存中的缓冲。
分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Ｏｒａｃｌｅ等数据库了解这个，一般最大指定为３Ｍ比较合适。
参考：Innodb_os_log_written(show global status 可以拿到)
如果这个值增长过快，可以适当的增加innodb_log_buffer_size
另外如果你需要处理大理的ＴＥＸＴ，或是ＢＬＯＢ字段，可以考虑增加这个参数的值。
设置方法：
innodb_log_buffer_size=3M

　4.innodb_flush_logs_at_trx_commit

作用：控制事务的提交方式
分配原则：这个参数只有３个值，０，１，２请确认一下自已能接受的级别。默认为１，
性能更高的可以设置为０或是２，但会丢失一秒钟的事务

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

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

当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是，由于进程调用方面的问题，并不能保证每秒100%的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。

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

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

　　设置方法：
　　innodb_flush_logs_at_trx_commit=1

文件IO分配，空间占用方面

　1. innodb_file_per_table

作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。
分配原则：只有使用不使用。但ＤＢ还需要有一个公共的表空间。
设置方法：
innodb_file_per_table=1

　2. innodb_file_io_threads

作用：文件读写ＩＯ数，这个参数只在Windows上起作用。在LINUX上只会等于4
设置方法：

innodb_file_io_threads=4

3. innodb_open_files

作用：限制Innodb能打开的表的数据。
分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是３００。
设置方法：
innodb_open_files=800
请适当的增加table_cache

其它相关参数

这里说明一个比较重要的参数：

　1. innodb_flush_method

作用：InnoDB和系统打交道的一个ＩＯ模型分配原则：Windows不用设置。Unix可以设置：fsync() or O_SYNC/O_DSYNC如果系统可以禁止系统的Cache那就把他禁了。Linux可以选择：O_DIRECT 直接写入磁盘，禁止系统Cache了
设置方法：
innodb_flush_method=O_DIRECT

　2.innodb_max_dirty_pages_pct

作用：控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.
这个参数的另一个用处：当InnoDB的内存分配过大，致使Swap占用严重时，可以适当的减小调整这个值，使达到Swap空间释放出来。建义：这个值最大在90%，最小在15%。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。
设置方法：
innodb_max_dirty_pages_pct＝90
动态更改需要有Super权限：
set global innodb_max_dirty_pages_pct=50;

四: 其他

查看 InnoDB buffer 各项属性

mysql> show variables like '%innodb_buffer%';

+-------------------------------------+----------------+

| Variable_name                       | Value          |

+-------------------------------------+----------------+

| innodb_buffer_pool_dump_at_shutdown | OFF            |

| innodb_buffer_pool_dump_now         | OFF            |

| innodb_buffer_pool_filename         | ib_buffer_pool |

| innodb_buffer_pool_instances        | 8              |

| innodb_buffer_pool_load_abort       | OFF            |

| innodb_buffer_pool_load_at_startup  | OFF            |

| innodb_buffer_pool_load_now         | OFF            |

| innodb_buffer_pool_size             | 134217728      |

+-------------------------------------+----------------+

8 rows in set (0.00 sec)

查看 mysql 里面的存储引擎

mysql> show variables like '%have%';

+----------------------+----------+

| Variable_name        | Value    |

+----------------------+----------+

| have_compress        | YES      |

| have_crypt           | NO       |

| have_csv             | YES      |

| have_dynamic_loading | YES      |

| have_geometry        | YES      |

| have_innodb          | YES      |

| have_ndbcluster      | NO       |

| have_openssl         | DISABLED |

| have_partitioning    | YES      |

| have_profiling       | YES      |

| have_query_cache     | YES      |

| have_rtree_keys      | YES      |

| have_ssl             | DISABLED |

| have_symlink         | YES      |

+----------------------+----------+

14 rows in set (0.43 sec)

参考资料:　　http://imysql.cn/node/609

参考资料:　　http://blog.chinaunix.net/uid-20682026-id-3167453.html

参考资料:　　http://jingyan.baidu.com/article/fedf07377c493f35ac89770c.html

参考资料:　　http://www.cnblogs.com/zhangjun516/archive/2013/03/19/2968997.html

参考资料:　　http://blog.csdn.net/tsuliuchao/article/details/5373041

参考资料:　　http://baike.baidu.com/view/1238935.htm?fr=aladdin

参考资料:　　http://dev.mysql.com/doc/refman/5.6/en/index.html

秒客网

mysql innodb 引擎

innodb 引擎

一.概述

二. InnoDB 引擎详解

1. 表空间

a) 共享表空间：

b) 独立表空间：

优点：

缺点：

a ) 后台进程

1.master thread线程

b) 内存

1.缓冲池(buffer pool)

a) 描述

三 : 优化

参数调优内容：

内存利用方面

日值控制方面

文件IO分配，空间占用方面

其它相关参数

内存利用方面

1. innodb_buffer_pool_size

2. innodb_additional_mem_pool：

关于日值方面：

1.innodb_log_file_size

2.innodb_log_files_in_group

3.innodb_log_buffer_size：

4.innodb_flush_logs_at_trx_commit

文件IO分配，空间占用方面

1. innodb_file_per_table

2. innodb_file_io_threads

3. innodb_open_files

其它相关参数

1. innodb_flush_method

2.innodb_max_dirty_pages_pct

四: 其他

相关文章