绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
简单总结innodb存储引擎及运行机制
2019-05-03 13:58:53

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

001-常见存储引擎

一、MySQL中myisam与innodb的区别

InnoDB支持事务,Myisam不支持事务;

InnoDB支持外键,Myisam不支持外键;

InnoDB支持行锁和表锁;Myisam不支持行锁。

Innodb 是索引组织表, myisam 是堆表

InnoDB索引与表在一起,MyISAM索引与表独立。

Innodb主键索引叶节点存数据,Myisam索引叶节点存的地址

InnoDB在内存存储索引和数据,MyISAM 将索引存储到内存中

innodb表索引与表共用文件,myisam索引单独存放

innodb表按主键顺序存放,myisam随机存放

InnoDB适合oltp,myisam适合olap


二、memory存储引擎

以前称作head存储引擎


三、补充:NDB分布式集群存储引擎

1、类似于Oracle的RAC,但它是Share Nothing的架构。

2、数据全部放在内存中,因此通过主键查找非常快。

3、复杂的join操作需要巨大的网络开销,查询速度会很慢。


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

002-innodb后台线程


| thread/sql/main                        |

| thread/innodb/io_handler_thread        |四读四写(四类change buffer合并io+redo log刷redo+write thread+read thread)

| thread/innodb/srv_master_thread        |一个master线程(主线程)

| thread/innodb/srv_purge_thread         |Purge thread(删除记录和删除undo)

| thread/innodb/srv_monitor_thread       |monitor_thread

| thread/innodb/srv_error_monitor_thread |一个错误监控线程

| thread/innodb/srv_lock_timeout_thread  |一个lock监控线程

| thread/innodb/page_cleaner_thread      |page_cleaner_thread(刷buffer)

| thread/sql/signal_handler              |单独的signal处理线程

| thread/sql/slave_sql                   |slave_sql

| thread/sql/slave_io                    |slave_io 


一、一个master线程

1、innodb几乎在这个线程上实现有所有功能

每秒1次的操作

每10秒一次的操作

数据库空闲时或数据库关闭时的操作

2、Master thread 内部几个循环(loop)组成:

主循环(loop),

后台循环(background loop),

刷新循环(flush loop),

暂停循环(suspend loop)。

3、主循环有1s循环和10s循环. 1s循环即循环执行一次就sleep 1s后又执行一次又sleep 1 s.

4、各种循环执行的次数,据此判断系统负载高低

5、在循环内可以调用其他线程进行相关的操作。


二、一个lock监控线程


三、一个错误监控线程

四、IO线程

1、innodb_write_io_threads

默认四个,在高并发下可设为8

2、innodb_read_io_threads

默认四个,在高并发下可设为8

3、page clean线程刷新的方式分为三种



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


003-innodb引擎的4大特性


一、Change buffer

将对二级辅助索引页的操作缓存下来,以此减少辅助索引的随机IO,并达到刷盘操作合并的效果。


二、二次写(防卡写失败造成页断裂)

1、先放入doublewrite buffer

2、接着二次写

(1)写入磁盘共享表空间中(连续存储,顺序写,性能很高),每次写1MB;

(2)再写入实际的各个表空间文件(离散写);

3、如果刷盘的时os崩溃,在恢复过程中

(1)从共享表空间的doublewrite中找到该页的一个最近的副本,将其复制到表空间文件;

(2)再应用redo log,就完成了恢复过程;


三、自适应哈希索引(ahi)

1、自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

2、启用AHI后

(1)读取和写入速度可以提高2倍

(2)辅助索引的连接操作性能可提高5倍。


四、预读(read ahead)

1、mysql-5.7中的innodb_buffer_pool_prefetching

(1)一个Extend的64个页面大多页被访问了,Extend中的其它页被访问概率也非常大。

(2)于是将Extend中的其它页面与一并调入内存了,样做可以把多次IO请求合并成一次。

2、read-ahead也可能带来性能问题



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

004-InnoDB引擎的七种类型的锁

1、共享锁和排他锁(Shared and Exclusive Locks)

不是真正意义上的锁,对其它锁起修饰作用。

2、记录锁(Record Locks)

单个行记录上的锁

3、间隙锁(Gap Locks)

锁定一个范围,但不包括记录本身,即使某些不存在的键值也会被无辜的锁定

4、Next-Key Locks(repeatable-read)

默认的锁算法,锁定一个范围,并且锁定记录本身

5、自增锁(AUTO-INC Locks)

针对AUTO_INCREMENT列的表级锁,一个事务正插入,其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。

6、共享意向锁(Intention Locks)

是特殊的表级别锁,仅仅表明意向

要获得某些行的S锁,须先获得表的IS锁(intention shared),预示未来有意向对表中某些行加S锁

7、插入意图锁(Insert Intention Locks)

是特殊的表级别锁,仅仅表明意向

要获得某些行的X锁,须先获得表的IX锁(intention exclusive),预示未来有意向对表中某些行加X锁

8、补充:Intention锁不互斥,它与S/X互斥:

     S       X

IS   兼容    互斥

IX   互斥    互斥



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

005-数据库中的几种事务隔离级别

1、未提交读(Read uncommitted)

2、已提交读(Read committed(RC))

  Oracle和SQL Server默认事务隔离级别,阿里rds上隔离级别为READ-COMMITTED

3、可重复读(Repeatable read(RR)

在同一个事务内的查询都是事务开始时刻一致的,多会话并发读取时,可能将未提交的记录查询出来,而出现幻读。

4、可串行化(Serializable)。


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

006-redolog与binlog的区别

1、记录范围:redo只记InnoDB日志,binlog记录所有引擎的日志

2、记录内容:binlog记录SQL语句event,redo记录页更改的物理情况。

3、写入时间:先写redo log,再写binlog,并以binlog写成功为事务提交成功的标志。

4、崩溃恢复:对两者比较,xid在binlog里存在则提交,不存在则回滚。



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

007-INNODB也采用LRU进行管理检查点

一、Sharp Checkpoint(全量检查点)

发生在数据库关闭时将所有的脏页都刷新回磁盘,这是默认的工作方式,即参数innodb_fast_shutdown=1。


二、FUZZY CHECKPOINT(增量检查点)

在运行时InnoDB存储引擎内部使用Fuzzy Checkpoint只刷新一部分脏页。


三、innodb_io_capacity控制缓冲区刷新脏页时,一次刷新脏页的数量,一般建议如下:

1、100=SATA

2、200=SAS(15000转)

3、600=Raid10(6块SAS)

4、5000=SSD 

5、10000-50000=PCI-E 



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

008-online DDL

一、之前版本做ddl,为了避免堵塞DML。

1、采用主从滚动的方式,操作复杂,

2、选择pt-osc工具(pt-online-schema-change)

(1)需要主键或唯一键否则不执行;

(2)触发器在一定情况下会导致死锁,对业务有一定的影响


二、mysql 5.6版本的online ddl则避免了上述限制。

1、首先ADD COLUMN和DROP COLUMN不再会堵塞DML操作,

2、同时建立索引 方面默认使用LOCK=NONE的模式而不会堵塞DML,


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

009-innodb运行机制补充

一、query cache

1、查询必须大小写、空格、库名、字符集严格一致

2、在mysql8.0中已作废


二、线程池避免高并发让MySQL瘫痪

Thread_Cache被划分为多个group(组),每个组又有对应的工作线程

1、MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,

2、将空闲的连接线程放在连接池中,而不是立即销毁.

3、这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接线程,

4、而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程。

5、在短连接的应用中Thread_Cache的功效非常明显,因为在应用中数据库的连接和创建是非常频繁的


三、应不应该开启自动提交?

1、set autocommit = 1;

MySQL在autocommit自动提交模式下,每个SQL都是一个独立的事务

阿里rds上自动提交打开


2、set autocommit = 0:

意味着只执行一个 select 语句也会启动对应的事务,而且不会自动提交。

这个事件会持续存在直到你主动执行 commit 或 rollback 语句,或者断开连接。

如果是长连接则会导致长事务,而长事件会占用回滚段和锁资源。



动力小刚于2019年4月  个人邮箱:zcs0237#163.com


分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~