MySQL存储引擎介绍课件

MySQL存储引擎介绍mengxl2011-12-21MySQL存储引擎介绍mengxlMySQL体系结构MySQL存储引擎InnoDB存储引擎主要内容MySQL体系结构主要内容一个由多个子系统构成的层次化系统一、MySQL体系结构一个由多个子系统构成的层次化系统一、MySQL体系结构Connectors:用来与客户端应用程序建立连接的数据库接口ManagementServices&Utilities:系统管理和服务控制相关的辅助工具ConnectionPool：负责处理与用户访问有关的各种用户登录、线程处理、内存和进程缓存需求SqlInterface：提供从用户接受命令并把结果返回给用户的机制Parser:对SQL语句进行语法分析和解析，构造一个月来执行查询的数据结构Optimizer:优化查询语句，以保证数据检索动作的效率达到或者非常接近最最优。使用一种“选取-投影-联结”策略来处理查询，即先根据有关的限制条件进行选取(Select操作)以减少将要处理的元组个数，再进行投影以减少被选取元组力的属性字段的个数，最后根据连接条件生产最终的查询结果一、MySQL体系结构Connectors:用来与客户端应用程序建立连接的数据库接Caches&Buffers：保证使用频率最高的数据或结构能够以最有效率的方式被访问，缓存的类型有：表缓存、记录缓存、键缓存、权限缓存、主机名缓存等。一、MySQL体系结构Caches&Buffers：保证使用频率最高的数据或结最与众不同的特点是插件式存储引擎插件式表存储引擎是底层物理结构的实现，负责为数据库执行实际的数据I/O操作，它是基于表而不是数据库的。可以根据实际应用需求为每个表设定不同的选择。插件式存储引擎的核心是文件访问层的一个抽象接口，任何人都可以利用这个API接口去建立新的文件访问机制一、MySQL体系结构最与众不同的特点是插件式存储引擎一、MySQL体系结构技术关键点InnoDBMyISAMNDBMemoryArchiveMaria其他综合比较二、MySQL存储引擎介绍技术关键点二、MySQL存储引擎介绍并发性：锁粒度事务支持：ACID引用完整性：外键支持物理存储：表与索引等文件格式索引支持：索引策略与方法内存高速缓冲：缓存和缓冲区策略其他目标特性：安全限制等二、MySQL存储引擎介绍——关键技术并发性：锁粒度二、MySQL存储引擎介绍——关键技术支持事务行锁设计，提供并发性，同时支持MVCCMVCC——多版本并发控制支持外键，以确保数据完整性独立的ibd文件存储表结构和数据其他特性：插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用功能适用场合：强调可靠性和需要支持事务处理二、MySQL存储引擎介绍——InnoDB支持事务二、MySQL存储引擎介绍——InnoDB名词解释插入缓冲二次写自适应哈希索引MVCC——多版本并发控制一致性非锁定读二、MySQL存储引擎介绍——InnoDB名词解释二、MySQL存储引擎介绍——InnoDB不支持事务表锁设计全文索引存储表由MYD和MYI组成，分别存放数据和索引使用数据压缩和索引优化特点：可靠性高、适用范围广、检索速度快适用场合：强调数据检索速度二、MySQL存储引擎介绍——MyISAM不支持事务二、MySQL存储引擎介绍——MyISAMNetworkdatabase，一种集群存储引擎采用sharenothing集群架构每个节点都拥有自己的内存和磁盘特点：数据全部放在内存中，主键查询速度快增加NDB存储节点，可线性提高性能Join连接的网络开销大对RAM需求大适用场合：冗余、高可用性、负载均衡二、MySQL存储引擎介绍——NDBNetworkdatabase，一种集群存储引擎二、MySMySQLNDBcluster架构图

/doc/refman/5.0/en/mysql-cluster-overview.html二、MySQL存储引擎介绍——NDBMySQLNDBcluster架构图二、MySQL存储将表中数据存放在内存中默认使用哈希索引由一个.frm的扩展名来指明它存储的表定义特点：服务器重启时，Memory表里的数据全部丢失只支持表锁，并发性能差不支持TEXT和BLOB类型按照定常方式存储变长字段(varchar)数据高速度，低延迟，不适合频繁写适用场合：存储临时数据、数据仓库的纬度表二、MySQL存储引擎介绍——Memory将表中数据存放在内存中二、MySQL存储引擎介绍——Memo行锁机制更好的支持多线程多用户并发更好的支持读写混合语句以及扩展可选择磁盘存储介质永久保存数据Shared-nothing和分布式架构保证无单点故障，99.999%可用性数据自动分布在各个节点，应用开发者无需考虑分区或分片解决方案支持MEMORY中不支持的变长数据类型（包括BLOB和TEXT）ClusterVSMemory行锁机制更好的支持多线程多用户并发ClusterVSMe目标：提供高速的插入和压缩功能特点：只支持Insert和Select操作支持行锁来实现高并发的插入操作不支持事务采用zlib算法压缩存储，压缩比例达1:10适用场合：存储归档数据，如日志信息二、MySQL存储引擎介绍——Archive目标：提供高速的插入和压缩功能二、MySQL存储引擎介绍——目标：取代原有的MyISAM存储引擎特点：缓存数据和索引文件行锁设计提供MVCC功能支持事务和非事务安全二、MySQL存储引擎介绍——Maria目标：取代原有的MyISAM存储引擎二、MySQL存储引擎介FederatedMergeCSVSphinxInfobright……二、MySQL存储引擎介绍——其他Federated二、MySQL存储引擎介绍——其他二、MySQL存储引擎介绍——综合比较二、MySQL存储引擎介绍——综合比较概述及体系架构masterthread关键特性三、InnoDB存储引擎概述及体系架构三、InnoDB存储引擎由InnobaseOy公司开发（2006被Oracle收购）第一个完整支持ACID事务的MySQL存储引擎行锁设计支持MVCC支持外键一致性非锁定读应用广泛：Yahoo、Facebook、Youtube、Flickr、Wow、SecondLife…三、InnoDB存储引擎——概述由InnobaseOy公司开发（2006被Oracle收购三、InnoDB存储引擎——体系架构后台线程IO线程、master线程、锁监控线程、错误监控线程内存池缓冲池、重做日志缓冲池、额外内存池三、InnoDB存储引擎——体系架构三、InnoDB存储引擎——体系架构内存结构缓冲池：存放各种数据缓存，将数据库文件按页读取，LRU算法维护缓存，定时flush脏页到文件日志缓冲：将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件额外内存池：对一些数据结构本身信息的内存分配三、InnoDB存储引擎——体系架构内存结构masterthread的线程优先级别最高。组成：主循环（loop）、后台循环（backgroundloop）、刷新循环（flushloop）、暂停循环（suspendloop）。loop循环伪代码：void

master_thread(){

loop：

for(int

i

=

0;

i

<

10;

i++){

do

thing

once

per

second

//每1秒操作

sleep

1

second

if

necessary

}

do

things

once

per

ten

seconds

//每10秒操作background

loop:

do

something

goto

loop;}三、InnoDB存储引擎——masterthreadmasterthread的线程优先级别最高。三、InnoD每1秒操作：日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。合并插入缓冲（可能）。至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）。如果当前没有用户活动，切换到backgroundloop（可能）。伪代码：

do

log

buffer

flush

to

disk

if

(last_one_second_ios

<

5

)

do

merge

at

most

5

insert

buffer

if

(

buf_get_modified_ratio_pct

>

innodb_max_dirty_pages_pct

)

do

buffer

pool

flush

100

dirty

page

if

(

no

user

activity

)

goto

backgroud

loop

三、InnoDB存储引擎——masterthread每1秒操作：三、InnoDB存储引擎——masterthr每10秒操作：刷新100个脏页到磁盘（可能）。合并至多5个插入缓冲（总是）。将日志缓冲刷新到磁盘（总是）。删除无用的Undo页（总是）。刷新100个或者10个脏页到磁盘（总是）。产生一个检查点（总是）。伪代码：if

(

last_ten_second_ios

<

200

)

do

buffer

pool

flush

100

dirty

page

do

merge

at

most

5

insert

buffer

do

log

buffer

flush

to

disk

do

full

purge

if

(

buf_get_modified_ratio_pct

>

70%

)

do

buffer

pool

flush

100

dirty

page

else

dobuffer

pool

flush

10

dirty

page

do

fuzzy

checkpoint三、InnoDB存储引擎——masterthread每10秒操作：三、InnoDB存储引擎——masterthbackgroudloop：删除无用的Undo页（总是）。合并20个插入缓冲（总是）。跳回到主循环（可能）。跳转到flushloop（可能）。伪代码：do

full

purge

do

merge

20

insert

buffer

if

not

idle:

goto

loopelse:

goto

flush

loop

三、InnoDB存储引擎——masterthreadbackgroudloop：三、InnoDB存储引擎——mflushloop&suspend_loop：flushloop不断刷新100个页，直到符合条件。如果flushloop无事可做，将会切换到suspend_loop，suspend_loop将masterthread挂起，等待事件的发生伪代码：flush

loop:

do

buffer

pool

flush

100

dirty

page

if

(

buf_get_modified_ratio_pct>

innodb_max_dirty_pages_pct

)

goto

flush

loop

goto

suspend

loop

suspend

loop:

suspend_thread()

waiting

event

goto

loop;三、InnoDB存储引擎——masterthreadflushloop&suspend_loop：三、In插入缓冲两次写自适应哈希索引一致性非锁定读MVCC（多版本并发控制）三、InnoDB存储引擎——关键特性插入缓冲三、InnoDB存储引擎——关键特性插入缓冲聚集索引主键、第一个非空的唯一索引、ROWID插入聚集索引是按顺序的，不需随机读取磁盘辅助索引索引不唯一数据页按序插入，插入索引需离散访问索引页，效率低插入缓冲为了提高辅助索引的插入和修改性能先判断是否在缓冲池，如果在，则直接插入索引页，否则将先插入缓冲池，然后定期合并插入索引页三、InnoDB存储引擎——关键特性插入缓冲三、InnoDB存储引擎——关键特性两次写Doublewrite：内存doublewriteBuffer(2M)和物理磁盘上共享表空间(连续页)三、InnoDB存储引擎——关键特性两次写三、InnoDB存储引擎——关键特性MVCC(多版本并发控制)基本原理：在事务中保存数据的快照，这意味着在一个事务里能够看到数据一致的视图，而不用担心这个事务运行多长时间，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。基本特征：每行数据都存在一个版本，每次数据更新时都更新该版本，修改时Copy出当前版本随意修改，各事务之间无干扰，保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）三、InnoDB存储引擎——关键特性MVCC(多版本并发控制)三、InnoDB存储引擎——关键特一致性非锁定读指innodb通过行多版本控制技术的方式来读取数据库中的数据，如果读取的数据正在进行删除或者更新，这时读取操作不会因此等待行上的锁释放，而会去读取行的一个快照（快照指的是该行之前的版本的数据），快照没有额外的开销且读取快照不需要加锁大大提高了数据读取的并发性，在innodb中，只是默认的方式，即读取不会占用和等待表上的锁在事务不同的隔离级别下，读取方式还是有所不同的，并不是每一个隔离级别下读取都是一致性的读三、InnoDB存储引擎——关键特性一致性非锁定读三、InnoDB存储引擎——关键特性

Q&A

ThanksEmail:mengxl@Popo:frankie_meng@Msn:mengxiangl@

Q&A

ThanksEmail:mengxlMySQL存储引擎介绍mengxl2011-12-21MySQL存储引擎介绍mengxlMySQL体系结构MySQL存储引擎InnoDB存储引擎主要内容MySQL体系结构主要内容一个由多个子系统构成的层次化系统一、MySQL体系结构一个由多个子系统构成的层次化系统一、MySQL体系结构Connectors:用来与客户端应用程序建立连接的数据库接口ManagementServices&Utilities:系统管理和服务控制相关的辅助工具ConnectionPool：负责处理与用户访问有关的各种用户登录、线程处理、内存和进程缓存需求SqlInterface：提供从用户接受命令并把结果返回给用户的机制Parser:对SQL语句进行语法分析和解析，构造一个月来执行查询的数据结构Optimizer:优化查询语句，以保证数据检索动作的效率达到或者非常接近最最优。使用一种“选取-投影-联结”策略来处理查询，即先根据有关的限制条件进行选取(Select操作)以减少将要处理的元组个数，再进行投影以减少被选取元组力的属性字段的个数，最后根据连接条件生产最终的查询结果一、MySQL体系结构Connectors:用来与客户端应用程序建立连接的数据库接Caches&Buffers：保证使用频率最高的数据或结构能够以最有效率的方式被访问，缓存的类型有：表缓存、记录缓存、键缓存、权限缓存、主机名缓存等。一、MySQL体系结构Caches&Buffers：保证使用频率最高的数据或结最与众不同的特点是插件式存储引擎插件式表存储引擎是底层物理结构的实现，负责为数据库执行实际的数据I/O操作，它是基于表而不是数据库的。可以根据实际应用需求为每个表设定不同的选择。插件式存储引擎的核心是文件访问层的一个抽象接口，任何人都可以利用这个API接口去建立新的文件访问机制一、MySQL体系结构最与众不同的特点是插件式存储引擎一、MySQL体系结构技术关键点InnoDBMyISAMNDBMemoryArchiveMaria其他综合比较二、MySQL存储引擎介绍技术关键点二、MySQL存储引擎介绍并发性：锁粒度事务支持：ACID引用完整性：外键支持物理存储：表与索引等文件格式索引支持：索引策略与方法内存高速缓冲：缓存和缓冲区策略其他目标特性：安全限制等二、MySQL存储引擎介绍——关键技术并发性：锁粒度二、MySQL存储引擎介绍——关键技术支持事务行锁设计，提供并发性，同时支持MVCCMVCC——多版本并发控制支持外键，以确保数据完整性独立的ibd文件存储表结构和数据其他特性：插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用功能适用场合：强调可靠性和需要支持事务处理二、MySQL存储引擎介绍——InnoDB支持事务二、MySQL存储引擎介绍——InnoDB名词解释插入缓冲二次写自适应哈希索引MVCC——多版本并发控制一致性非锁定读二、MySQL存储引擎介绍——InnoDB名词解释二、MySQL存储引擎介绍——InnoDB不支持事务表锁设计全文索引存储表由MYD和MYI组成，分别存放数据和索引使用数据压缩和索引优化特点：可靠性高、适用范围广、检索速度快适用场合：强调数据检索速度二、MySQL存储引擎介绍——MyISAM不支持事务二、MySQL存储引擎介绍——MyISAMNetworkdatabase，一种集群存储引擎采用sharenothing集群架构每个节点都拥有自己的内存和磁盘特点：数据全部放在内存中，主键查询速度快增加NDB存储节点，可线性提高性能Join连接的网络开销大对RAM需求大适用场合：冗余、高可用性、负载均衡二、MySQL存储引擎介绍——NDBNetworkdatabase，一种集群存储引擎二、MySMySQLNDBcluster架构图

/doc/refman/5.0/en/mysql-cluster-overview.html二、MySQL存储引擎介绍——NDBMySQLNDBcluster架构图二、MySQL存储将表中数据存放在内存中默认使用哈希索引由一个.frm的扩展名来指明它存储的表定义特点：服务器重启时，Memory表里的数据全部丢失只支持表锁，并发性能差不支持TEXT和BLOB类型按照定常方式存储变长字段(varchar)数据高速度，低延迟，不适合频繁写适用场合：存储临时数据、数据仓库的纬度表二、MySQL存储引擎介绍——Memory将表中数据存放在内存中二、MySQL存储引擎介绍——Memo行锁机制更好的支持多线程多用户并发更好的支持读写混合语句以及扩展可选择磁盘存储介质永久保存数据Shared-nothing和分布式架构保证无单点故障，99.999%可用性数据自动分布在各个节点，应用开发者无需考虑分区或分片解决方案支持MEMORY中不支持的变长数据类型（包括BLOB和TEXT）ClusterVSMemory行锁机制更好的支持多线程多用户并发ClusterVSMe目标：提供高速的插入和压缩功能特点：只支持Insert和Select操作支持行锁来实现高并发的插入操作不支持事务采用zlib算法压缩存储，压缩比例达1:10适用场合：存储归档数据，如日志信息二、MySQL存储引擎介绍——Archive目标：提供高速的插入和压缩功能二、MySQL存储引擎介绍——目标：取代原有的MyISAM存储引擎特点：缓存数据和索引文件行锁设计提供MVCC功能支持事务和非事务安全二、MySQL存储引擎介绍——Maria目标：取代原有的MyISAM存储引擎二、MySQL存储引擎介FederatedMergeCSVSphinxInfobright……二、MySQL存储引擎介绍——其他Federated二、MySQL存储引擎介绍——其他二、MySQL存储引擎介绍——综合比较二、MySQL存储引擎介绍——综合比较概述及体系架构masterthread关键特性三、InnoDB存储引擎概述及体系架构三、InnoDB存储引擎由InnobaseOy公司开发（2006被Oracle收购）第一个完整支持ACID事务的MySQL存储引擎行锁设计支持MVCC支持外键一致性非锁定读应用广泛：Yahoo、Facebook、Youtube、Flickr、Wow、SecondLife…三、InnoDB存储引擎——概述由InnobaseOy公司开发（2006被Oracle收购三、InnoDB存储引擎——体系架构后台线程IO线程、master线程、锁监控线程、错误监控线程内存池缓冲池、重做日志缓冲池、额外内存池三、InnoDB存储引擎——体系架构三、InnoDB存储引擎——体系架构内存结构缓冲池：存放各种数据缓存，将数据库文件按页读取，LRU算法维护缓存，定时flush脏页到文件日志缓冲：将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件额外内存池：对一些数据结构本身信息的内存分配三、InnoDB存储引擎——体系架构内存结构masterthread的线程优先级别最高。组成：主循环（loop）、后台循环（backgroundloop）、刷新循环（flushloop）、暂停循环（suspendloop）。loop循环伪代码：void

master_thread(){

loop：

for(int

i

=

0;

i

<

10;

i++){

do

thing

once

per

second

//每1秒操作

sleep

1

second

if

necessary

}

do

things

once

per

ten

seconds

//每10秒操作background

loop:

do

something

goto

loop;}三、InnoDB存储引擎——masterthreadmasterthread的线程优先级别最高。三、InnoD每1秒操作：日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）。合并插入缓冲（可能）。至多刷新100个InnoDB的缓冲池中的脏页到磁盘（可能）。如果当前没有用户活动，切换到backgroundloop（可能）。伪代码：

do

log

buffer

flush

to

disk

if

(last_one_second_ios

<

5

)

do

merge

at

most

5

insert

buffer

if

(

buf_get_modified_ratio_pct

>

innodb_max_dirty_pages_pct

)

do

buffer

pool

flush

100

dirty

page

if

(

no

user

activity

)

goto

backgroud

loop

三、InnoDB存储引擎——masterthread每1秒操作：三、InnoDB存储引擎——masterthr每10秒操作：刷新100个脏页到磁盘（可能）。合并至多5个插入缓冲（总是）。将日志缓冲刷新到磁盘（总是）。删除无用的Undo页（总是）。刷新100个或者10个脏页到磁盘（总是）。产生一个检查点（总是）。伪代码：if

(

last_ten_second_ios

<

200

)

do

buffer

pool

flush

100

dirty

page

do

merge

at

most

5

insert

buffer

do

log

buffer

flush

to

disk

do

full

purge

if

(

buf_get_modified_ratio_pct

>

70%

)

do

buffer

pool

flush

100

dirty

page

else

dobuffer

pool

flush

10

dirty

page

do

fuzzy

checkpoint三、InnoDB存储引擎——masterthread每10秒操作：三、InnoDB存储引擎——masterthbackgroudloop：删除无用的Undo页（总是）。合并20个插入缓冲（总是）。跳回到主循环（可能）。跳转到flushloop（可能）。伪代码：do

full

purge

do

merge

20

insert

buffer

if

not

idle:

goto

loopelse:

goto

flush

loop

三、InnoDB存储引擎——masterthreadbackgroudloop：三、InnoDB存储引擎——mflushloop&suspend_loop：flushloop不断刷新100个页，直到符合条件。如果flushloop无事可做，将会切换到suspend_loop，suspend_loop将masterthread挂起，等待事件的发生伪代码：flush

loop:

do

buffer

pool

flush

100

dirty

page

if

(

buf_get_modifi