MYSQL 常用的引擎主要有一下几种,MRG_MYISAM 、CSV 、MyISAM、InnoDB、MEMORY ,NDB,其中MyISAM、InnoDB是mysql最常用的存储引擎,今天主要讨论 InnoDB引擎。
一、什么是InnoDB引擎
InnoDB引擎是MYSQL数据库的另一个重要的额存储引擎,正成为目前MYSQL AB所有发行新版的标准,被包含在所有二进制安装包里。
和其他的存储引擎相比,InnoDB引擎的优点支持兼容ACID的事物,以及参数完整性(即对外建的支持)。
MYSQL5.5.5以后数据库的默认存储引擎为InnoDB引擎
二、InnoDB引擎的特点
1、支持事物:支持4个事物隔离级别,支持多版本读(ACID :原子性、一致性、隔离性、持久性.)
2、行级锁定(更新时一般是锁定当前行,InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非 锁定读):通过索引实现,全表扫描仍然会是锁表。
3、读写阻塞与事物隔离级别有关
4、具有非常高效的缓存特性:能缓存索引,也能缓存数据(InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。 InnoDB存储它的表&索引在一个表空间中,表空间可以包含多个文件(或原始磁盘分区))
5、整个表和主键以Cluster方式存储,组成一颗平衡树
6、所有Secondar Index都会保存主键信息
7、支持分区,表空间,类似oracle数据库
8、支持外键约束,5.5版本以后支持全文索引
三、事务的四大特性(ACID)
1、原子性
事务是一个不可分割的单位,事务中的所有SQL灯操作要么都发生,要么都不发生.
2、一致性
事务发生之前和发生之后,数据的完整性必须保持一致.
3、隔离性
当并发访问数据库时,一个正在执行的事务在执行完毕前,对于其他的会话是不可见的,多个并发事务之间的数据相互隔离的。
4、持久性
一个事务一旦被提交,它对数据库中的数据改变就是永久性的,如果出了错误,事务也不允许撤销,只能通过“补偿性事务”.
四、InnoDB引擎架构
InnoDB的多个内存块组成了内存池,负责如下工作:
1、维护所有进程/线程需要访问的多个内部数据结构。
2、缓存磁盘上的数据,方便快速的读取,并且在对磁盘文件的数据进行修改之前在这里缓存。
3、重做日志缓存。
后台线程的主要作用是负责刷新内存池中的数据,保证缓冲池中的内存缓存是最近的数据,此外、将已经修改的数据文件刷 新到磁盘文件
后台线程
innodb存储引擎后台有7个线程,—–4个IO线程(insert buffer thread,log thread,read thread,write thread),1个master thread,一个lock监控线程,一个错误监控线程。
内存
innodb存储引擎内存由以下三个部分组成:缓冲池(buffer pool),重做日志缓存(redo log buffer),额外的内存池(additional
memory pool)。可以使用 show engine innodb status来查看innodb_buffer_pool的使用情况。
innodb_buffer_pool_size:具体看,缓冲池中的数据库类型有:索引页、数据库页、undo页、插入缓存页(insert buffer)、自适应
hash(adaptive hashindex)、innodb存储的锁信息(lock info)、数据字典信息(data dictionary)。
InnoDB工作方式:将数据文件按页(每页16K)读入InnoDBbuffer pool,然后按最近最少使用算法(LRU)保留缓存数据,最后通过一定频
率将脏页刷新到文件。
五、InnoDB引擎适用的生产业务场景
1、需要事务支持的业务(具有较好的食物特性)
2、行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成
3、数据更新较为频繁的场景
4、数据一致性要求较高的业务
5、硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,尽可能减少磁盘IO
六、INNODB表空间
默认表空间文件为ibdata1文件innodb_data_file_path存储数据,innodb_file_per_table可以按表分别产生一个表空间.db文件,但仅存该表的
数据索引和插入缓冲等信息,其他信息如undo信息,系统事务信息,double write buffer等还是存放在默认表空间(ibdata1或表空间组)里。
七、关键特性,为innodb提高性能的技术
1、插入缓存
当一个表有非聚集索引时,对于非聚集索引的叶子节点的插入不是顺序的,这时候需要离散的访问非聚集索引页,性能就在这里降
低了,这是由于b+树的原理导致的。插入缓存就是用来解决这个问题的。对于非聚集索引的插入和更新操作,不是每一次都直接插入
索引页,而是先判断插入的非聚集索引页是否在缓存中,如果在就直接插入,如果不在就放入到一个插入缓冲区中,好似欺骗数据库
这个非聚集索引已经插入到叶子节点了。然后再以一定的频率插入缓存和非聚集索引页字节点的合并操作。插入缓存的使用需要满足
以下两个条件(也就是非唯一的辅助索引):索引是辅助索引;索引不是唯一的。
2、两次写
两次写给innodb带来的是可靠性,主要用来解决部分写失败(partial page write)。在应用重做日之前,我们需要一个页的副本,当写入失效
发生时,先通过页的副本来还原该页,再进行重做,这就是doublewrite。
doublewrite有两部分组成,一部分是内存中的doublewrite buffer,大小为2M,另外一部分就是物理磁盘上的共享表空间中联系的128个页,
即两个区,大小同样为2M。当缓冲池的张也刷新时,并不直接写硬盘,而是回通过memcpy函数将脏页先拷贝到内存中的doublewrite
buffer,之后通过doublewrite buffer再分两次写,每次写入1M到共享表空间的物理磁盘上,然后马上调用fsync函数,同步磁盘。
3、自适应哈西索引
由于innodb不支持hash索引,但是在某些情况下hash索引的效率很高,于是出现了 adaptive hash index功能,innodb存储引擎会监控对表上
索引的查找,如果观察到建立hash索引可以提高性能的时候,则自动建立hash索引
4、启动、关闭、恢复
innodb_fast_shutdown影响InnoDB表关闭。该参数有0、1、2三个参数
0 MySQL关闭时 完成所有的full purge和merge insertbuffer操作
1 默认值 只将缓冲池内的一些脏页刷新至磁盘
2 将日志都写入日志文件不会有任何事务丢失但下次启动时会进行recovery
innodb_force_recovery影响整个innodb存储引擎的恢复状况,该值默认为0,表示当需要恢复时,需要执行所有的恢复操作,当不能进行有 效恢复时,如数据页发生了corruption,mysql数据库可能宕机,并把错误写入错误日志中。
八、InnoDB引擎调优精要
1、主键尽可能小,避免给Secondar Index带来过大的空间负担
2、避免全表扫描,因为会使用表级锁
3、尽可能缓存所有的索引和数据。,提高响应速度,减少磁盘IO消耗
4、在大批量插入的时候,尽量自己控制事物而不要使用autocommit自动提交有开关可以控制提交方式
【 取消自动提交事物
mysql> set global init_connect="set autocommit=0";
init_connect='SET autocommit=0' // 在mysqld里面加上这些内容
】
5、合理设置innodb_flush_log_at_trx_commit参数值,不要过度追求安全性、
(如果innodb_flush_log_at_trx_commit=0 log buffer 每秒就会被刷写日志文件到磁盘,提交事物的时候不做任何操作)
6避免主键更新,因为这会带来大量的数据移动
九、生产环境中如何批量更改mysql引擎
alter table table_name ENGINE = INNODB
十、InnoDB与MYISAM区别总结
1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
在执行数据库写入的操作(insert,update,delete)的时候,mysiam表会锁表,而innodb表会锁行。通俗点说,就是你执行了一个update语句,那么mysiam表会将整个表都锁住,其他的insert和delete、update都会被拒之门外,等到这个update语句执行完成后才会被依次执行
另外,InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
3.对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
4.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
十一、InnoDB与MYISAM如何选择
当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主,相比较而言更新和写入比较少,并且业务方面数据完整性要求不那么严格,就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。