mysql innodb 引擎

时间:2022-04-24 18:15:49

innodb 引擎

一.概述


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

mysql innodb 引擎

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

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

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

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

二. InnoDB 引擎详解


InnoDB 引擎体系结构图

mysql innodb 引擎

1. 表空间

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

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

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

a) 共享表空间:

优点:

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

缺点:

  1. 所有的数据和索引存放到一个文件中以为着将有一个很常大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。
  2. 我们知道共享表空间管理会出现表空间分配后不能回缩的问题,当出现临时建索引或是创建一个临时表的操作表空间扩大后,就是删除相关的表也没办法回缩 那部分空间了。我们存在磁盘监控时,也许就报警不断了,但实际上MySQL还可以运行良好。另外,当磁盘上占用较多时性能也不是太好。这种情况处理只能是是建一个新的Slave从主库上Dump出来,然后在Dump到从库中,动作较大。
  3. 对于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")里面, 大小不再不受文件系统限制,

优点:

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

缺点:

  1. 单表增加过大,如超过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官方文档说线程池的使用在商业版可以用。

每秒都会操作的内容:

  1. 日志缓冲刷新到磁盘,即使这个事务还没有提交,这种设计导致很大的事务提交(commit)时也会很快。
  2. 合并插入缓冲,在判断I/O次数少于5次时,可以执行插入缓冲操作。
  3. 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)

三 : 优化

参数调优内容:

  1.   内存利用方面

  2.   日值控制方面

  3.   文件IO分配,空间占用方面

  4.   其它相关参数

  • 内存利用方面

 1. innodb_buffer_pool_size

这个参数和MyISAM的key_buffer_size有相似之处,但也是有差别的。这个参数主要缓存innodb表的索引,数据,插入数据时的缓冲。为Innodb加速优化首要参数。
该参数分配内存的原则:这个参数默认分配只有8M,可以说是非常小的一个值。如果是一个专用DB服务器,那么他可以占到内存的70%-80%。这个参数不能动态更改,所以分配需多考虑。分配过大,会使Swap占用过多,致使Mysql的查询特慢。如果你的数据比较小,那么可分配是你的数据大小+10%左右做为这个参数的值。例如:数据大小为50M,那么给这个值分配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的内部目录
这个值不用分配太大,系统可以自动调。不用设置太高。通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在error log有中显示的。
分配原则:
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

  

  • 关于日值方面:

 1.innodb_log_file_size

 
作用:指定日值的大小
分配原则:几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般控制在几个LOG文件相加大小在2G以内为佳。具体情况还需要看你的事务大小,数据大小为依据。
说明:这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。
设置方法:
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.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Oracle等数据库了解这个,一般最大指定为3M比较合适。
参考:Innodb_os_log_written(show global status 可以拿到)
如果这个值增长过快,可以适当的增加innodb_log_buffer_size
另外如果你需要处理大理的TEXT,或是BLOB字段,可以考虑增加这个参数的值。
设置方法:
innodb_log_buffer_size=3M 

 4.innodb_flush_logs_at_trx_commit

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

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

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

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

  设置方法:
  innodb_flush_logs_at_trx_commit=1

  • 文件IO分配,空间占用方面

 1. innodb_file_per_table

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

 2. innodb_file_io_threads

作用:文件读写IO数,这个参数只在Windows上起作用。在LINUX上只会等于4
设置方法:

innodb_file_io_threads=4

3. innodb_open_files

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

  • 其它相关参数

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

 1. innodb_flush_method

作用:InnoDB和系统打交道的一个IO模型分配原则: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