mysql 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,如果是一个专用DB服务器,那么他可以占到内存的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的内部目录
这个值不用分配太大,系统可以自动调。不用设置太高。
通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在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文件相加大小在2G以内为佳。
具体情况还需要看你的事务大小,数据大小为依据。
说明:这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。
设置方法:
innodb_log_file_size=256M

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

参数:innodb_log_buffer_size:
作用:事务在内存中的缓冲。
分配原则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。一般最大指定为3M比较合适。
参考:Innodb_os_log_written(show global status 可以拿到,如果这个值增长过快,可以适当的增加innodb_log_buffer_size
另外如果需要处理大量的text,或是blob字段,可以考虑增加这个参数的值。
设置方法:innodb_log_buffer_size=3M
参数:innodb_flush_logs_at_trx_commit
作用:控制事务的提交方式
分配原则:这个参数只有3个值,0,1,2请确认一下自已能接受的级别。默认为1,主库请不要更改了。
性能更高的可以设置为0或是2,但会丢失一秒钟的事务。
说明:
这个参数的设置对innodb的性能有很大的影响,所以在这里给多说明一下。
当这个值为1时:innodb 的事务LOG在每次提交后写入日值文件,并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。
当这个值为2时:在每个提交,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。
但需要注意的是,由于进程调用方面的问题,并不能保证每秒100%的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。
当这个值为0时:日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何操作。
mysqld进程的崩溃会删除崩溃前最后一秒的事务。

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

上一篇:BZOJ2001 HNOI2010城市建设(线段树分治+LCT)


下一篇:Django中模板使用