1.MySQL体系结构和存储引擎

1.1.定义数据库和实例

数据库和实例是不一样的.数据库即指磁盘上的文件,而实例是指的启动的一个数据库程序.

MySQL是一个单进程多线程的数据库

mysql --help |grep my.cnf可以查看到MySQL默认的配置文件加载顺序

1.2.MySQL体系结构

连接池组件

管理服务和工具组件

SQL接口组件

查询分析器组件

优化器组件

缓冲组件

插件式存储引擎

物理文件

1.3.MySQL存储引擎

存储引擎是MySQL区别于其他数据库的一个重要特性

1.3.1.InnoDB存储引擎

支持事务,主要面向在线事务处理.

行锁设计,支持外键,并支持类似Oracle的非锁定读.

将数据放在一个逻辑的表空间中.MySQL4.1之后放在一个ibd文件中.支持用裸设备(row disk)来建立表空间.

通过MVCC来获得高并发性,并实现了标准的四种隔离级别:读未提交,读已提交,可重复读,序列化.

使用next-key locking来避免幻读的产生.除此之外还提供了插入缓冲,二次写,自适应哈希索引,预读等高并发

和高可用的功能.

数据存储采用聚集的方式,即按照主键顺序进行存放.如果没有显示指定主键,InnoDB会为每一行产生一个6个

字节的ROWID,并以此作为主键.

1.3.2.MyISAM存储引擎

不支持事务,采用表锁设计,支持全文索引.在MySQL5.5.8之前是默认存储引擎.

数据库系统和文件系统最大的不同就是事务.但是有些应用就不需要事务,例如纯查询应用.

缓冲池只缓冲索引文件而不缓冲数据文件,这跟大多数存储引擎都不一样

数据文件由MYD和MYI组成,一个存储数据,一个存储索引

MySQL5.0版本之前最大数仅支持4G.

1.3.3.NDB存储引擎

集群存储引擎,类似Oracle的RAC集群.

数据全部放在内存中

1.3.4.Memory存储引擎

数据存储在内存中.使用哈希索引.

只支持表锁,所以并发性能差,不支持TEXT和BLOB列类型,存储变长字段采用char会浪费内存空间.

MySQL使用Memory存储引擎存放查询的中间结果集.

1.3.5.Archive存储引擎

只支持insert和select操作,使用zlib算法压缩数据行后存储.压缩比可达1:10,适合存储归档数据.

1.3.6.Federated存储引擎

并不存放数据,而是指向网络上的一台远程MySQL服务器上的表.

1.3.7.Maria存储引擎

新开发的存储以前,设计目标为取代原有的MyISAM存储引擎

1.3.8.其他存储引擎

1.4.各存储引擎之间比较

查看MySQL支持的存储引擎:

show engines;

1.5.连接MySQL

1.5.1.TCP/IP

mysql -uroot -proot -h127.0.0.1

在连接到MySQL之前,会检查一张权限试图.试图在mysql数据库中,表名为user

1.5.2.命名管道和共享内存

在配置文件中增加--enable-named-pipe选项

在MySQL4.1之后还提供了共享内存的方式,在配置文件添加--shared-menory

1.5.3.UNIX域套接字

show variables like "%socket%";

mysql -uroot -S /tmp/mysql.socket

2.InnoDB存储引擎

事务安全的MySQL存储引擎

2.1.InnoDB存储引擎概述

从MySQL5.5开始是默认的存储引擎.

第一个完整支持ACID事务的MySQL存储引擎,特点是行锁设计,支持MVCC,支持外键,提供一致性非锁定读

2.2.InnoDB存储引擎版本

早期版本随着MySQL的版本更新而更新.MySQL5.1开始允许存储引擎开发商以动态方式加载引擎.

MySQL5.1中支持两个版本的InnoDB,一个是静态编译的InnoDB,一个是以动态方式加载的InnoDB

称之为InnoDB Plugin

2.3.InnoDB体系结构

从图中可以看出InnoDB存储引擎有多个内存块,可以认为这些内存块组成了内存池,负责如下工作:

1.维护所有进程/线程需要访问的多个内部数据结构

2.维护磁盘上的数据,方便快速读取,同时在对磁盘文件修改之前在这里缓存

3.重做日志(redo log)缓冲

后台线程的主要作用是刷新内存池中的数据,保证缓冲池中的数据是最新的.

2.3.1.后台线程

InnoDB是多线程的存储模型.

1.Master Thread

非常核心的一个线程,负责将缓冲池中的数据异步刷新到磁盘,保证数据一致性,包括脏页的刷新,合并

插入缓冲,UNDO页的回收等.

2.IO Thread

InnoDB中大量使用了AIO来处理写IO请求.而IO Thread线程主要负责这些IO请求的回调处理.

InnoDB1.0版本之前有四个IO Thread,分别为write,read,insert buffer,log IO Thread.

Linux平台不能调整,但是Windows可以通过innodb_file_io_threads来增大IO Thread.

从1.0版本开始,write和read线程增加到了四个,并且去掉了innodb_file_io_threads参数,而使用

innodb_read_io_threads和innodb_write_io_threads参数进行设置.

show variables like "innodb_version";

show variables like "innodb%threads";

show engine innodb status;

3.Purge Thread

事务被提交之后,undolog可能已经不需要了,需要回收已经分配使用的undo页.

可以通过在配置文件增加innodb_purge_threads=x来启用独立的purge thread

4.Page Cleaner Thread

是InnoDB1.2.x版本引入的,刷新脏页的操作.

2.3.2.内存

1.缓冲池

页从缓冲池刷新回磁盘是通过checkpoint机制来实现的

大小可以通过innodb_buffer_pool_size来设置

show variables like "innodb_buffer_pool_size";

缓冲池缓存的数据类型:索引页,数据页,undo页,插入缓冲,自适应哈希索引,InnoDB存储的锁信息,数据字 典信息等.

从InnoDB1.0开始允许多个缓冲池实例.可以通过innodb_buffer_pool_instances设置.

2.LRU List,Free List和Flush List

数据库中的缓冲池是通过LRU算法来进行的.缓冲池中的页默认大小为16kb.跟其他的LRU算法相比,

InnoDB做了一些优化,加入了midpoint位置,新读取的页,虽然是最新访问的页,但是不会直接放入LRU

的首部,而是放入midpoint位置.这个算法在InnoDB中称为midpoint insertion strategy(中点插入策略)

默认在5/8处,大概37%.这个位置可以通过innodb_old_blocks_pct控制.

show variables like "innodb_old_blocks_pct";

midpoint之后的称为old列表,之前的称为new列表,那为什么不直接用朴素的LRU算法呢?

如果直接放入首部,那么可能某些sql操作会使得缓冲池中的数据被刷出.常见的这类操作为索引或数据

的扫描操作.这些操作需要使用内存中的所有页,会使得数据被从缓冲池中刷出.热点数据等到需要访问时

需要再次读取.为了解决这个问题引入了另外一个参数innodb_old_blocks_time,即读到mid位置之后多

久才会被放入LRU列表的热端.

LRU列表用来管理已经读取的页,但是在启动时是空的,需要从Free List中查询是否用空闲的页,如果有就 从Free List中取,取出后页从Free List中删除,如果没有没有则淘汰LRU列表中的页,当页从old列表进入

new列表时的操作称为page made young,从old列表进入new列表失败时称为page not made young.

通过show engine innodb status;可以查看LRU List和Free List的使用情况和运行情况.

这里有一个非常重要的观察变量:buffer pool hit rate,表示缓冲池的命中率,这个值不应该小于95%,如果

小于应该考虑是全表扫描引起的LRU列表被污染的情况.

InnoDB从1.0.x版本开始支持压缩页的功能,即原来的16KB压缩为1KB,2KB,4KB,8KB.对于非16KB的页

是通过unzip_LRU列表进行管理的.

在页上的数据被修改之后,该页被称为脏页,Flush列表中的页都是脏页.需要注意的是LRU List和Flush

List中都存在脏页.Flush列表用来管理将页刷会磁盘,二者互不影响.

3.重做日志缓冲

先将重做日志放入缓冲中,然后再刷新回redo log日志中.不需要设置的太大.

通过innodb_log_buffer_size来控制,默认大小为8MB.

三种情况下会刷新重做日志缓冲:

a.Master Thread每秒刷新一次

b.每个事务提交时会刷新

c.重做日志缓冲池空闲空间小于1/2时

4.额外的内存池

在InnoDB存储引擎中,对内存的管理是通过一种称为内存堆的方式进行的.

2.4.Checkpoint技术

缓冲池的目的就是为了协调内存和磁盘的速度相差太大的问题,因此页的操作都是在缓冲池中完成的.如果一条

DML语句,如果update和delete改变了缓冲池中页的内容,则这个页就是脏页.但是如果每次一个页都改变了值

就提交,那么性能开销会很大.但是如果在写磁盘时发生了宕机,则数据会丢失,此时就需要应用重做日志.但是如

果重做日志太大则会非常困难.

当通过重做日志来恢复数据时会有两个问题:

1.缓冲池无法缓存数据库中的所有数据

2.重做日志无法做到无限大

checkpoint作用:

1.缩短数据库的恢复时间

2.缓冲池不够用时,将脏页刷新到磁盘

3.重做日志不可用时,刷新脏页

当发生故障时不需要应用所有的重做日志,因为checkpoint之前的已经刷新回磁盘了,只需要刷新checkpoint之

后的重做日志.

此外当重做日志不可用时,LRU算法会溢出最近最少使用的页,如果这个页是脏页,那么需要强制执行checkpoint

将脏页刷新回磁盘.

对于InnoDB来说,通过LSN(Log sequence number)来标记版本的,而LSN是8字节的数字,单位为字节.每个页有

LSN,重做日志有LSN,checkpoint也有LSN.可以通过show engine innodb status;查看checkpoint所做的事情

就是将脏页刷新回磁盘.

有两种checkpoint:

1.Sharp Checkpoint

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

2.Fuzzy Checkpoint

但是如果运行时每次都把脏页刷新到磁盘,那么性能开销非常的大,所以会使用Fuzzy Checkpoint

来处理,而不是刷新所有的脏页

2.5.Master Thread工作方式

1.InnoDB1.0.x之前的Master Thread

2.InnoDB1.2.x之前的Master Thread

3.InnoDB1.2.x之后的Master Thread

2.6.InnoDB特性

1.插入缓冲

insert buffer是一种特殊的数据结构（B+ tree）并不是缓存的一部分，而是物理页，当受影响的索引页不 在buffer pool时缓存 secondary index pages的变化，当buffer page读入buffer pool时，进行合并操作， 这些操作可以是 INSERT, UPDATE, or DELETE operations (DML)

insert buffer只能用在非唯一索引上.

原理:

对于为非唯一索引，辅助索引的修改操作并非实时更新索引的叶子页，而是把若干对同一页面的更新缓 存起来做，合并为一次性更新操 作，减少IO，转随机IO为顺序IO,这样可以避免随机IO带来性能损耗， 提高数据库的写性能 .

先判断要插入非聚集索引页是否在缓冲池中,如果在,则直接插入,如果没有则先存入insert buffer,好似欺骗.

再以一定的频率插入.

需要满足的条件:

1.索引是辅助索引

2.索引不是唯一的

2.两次写

3.自适应哈希索引

哈希是一种非常快的查询方式.复杂度为o(1).而B+树的查找次数,取决于B+树的高度,生产环境一般为3-4层

InnoDB会监控表上的数据,当认为建立哈希索引可以带来性能提升时会自动建立哈希索引(AHI).对于(a,b)

这样的索引会有两种情况:where a = xxx,where a = xxx and b = xxx

4.异步IO

为了提高磁盘操作性能,当前的数据库采用异步IO进行操作.

innodb_use_native_aio

5.刷新邻接页

当刷新一个脏页时,会自动检测邻近的页,如果是脏页就进行刷新

3.文件

3.1.参数文件

动态参数

静态参数

3.2.日志文件

错误日志

二进制日志

慢查询日志

查询日志

3.3.套接字文件

3.4.pid文件

3.5.表结构定义文件

3.6.InnodDB存储引擎文件

表空间文件

重做日志文件

4.表

4.1.索引组织表

InnoDB存储引擎中,表都是根据主键顺序存放的,这种存储方式成为索引组织表.InnoDB存储引擎表中,每张

表中都有个主键,如果没有则会有个默认主键.

4.2.InnoDB逻辑存储结构

所有数据都被逻辑的存放在一个空间中,称之为表空间.表空间又由段(segment),区(extent),页(page)组成.

页在一些文档中也称之为块(block)

4.2.1.表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层,所有的数据都放在表空间中.默认情况下innodb

存储引擎有一个默认的共享表空间,即所有的数据都会放到这个表空间里.

如果启用了innodb_file_per_table则每张表内的数据会单独放到一个表空间内,但是存放的是数据,索引

插入缓冲bitmap页.其他的数据如回滚信息,插入缓冲索引页,系统事务信息,二次写缓冲都还在原来的共享

表空间内,这说明了一个问题,开启了innodb_file_per_tabel之后,共享表空间还是会不断的增大.

4.2.2.段

表空间是由各个段组成,常见的段有数据段,索引段,回滚段等.InnoDB存储引擎是索引组织的,因此数据即索引

索引即数据.对段的管理都是由InnoDB存储引擎完成的.

4.2.3.区

区是由连续页组成的空间,任何情况下区的大小都为1MB.为了保证区中的页的连续性,一般InnoDB存储引擎 一次申请4-5个区.默认情况下一个页的大小为16KB,即一个区有64个页.

InnoDB1.2.x版本开始新增了innodb_page_size参数.

4.2.4.页

同大多数数据库一样,InnoDB有页的概念,每个页大小默认16KB,可以通过innodb_page_size设置页大小.

设置完成之后所有的页大小都为innodb_page_size大小,不能再次改变.除非通过mysqldump导入或者导出

操作产生新的表.

页类型:

数据业,undo页,系统页,事务数据页,插入缓冲位图页,插入缓冲空闲列表页,未压缩的二进制大对象页,压缩

的二进制大对象页.

4.3.InnoDB行记录格式

跟大多数数据库一样InnoDB也是以行来记录数据的.即页中存放着表中一行行的数据.在InnoDB1.0.x版本之 前提供了Compact和Redundant两种格式来存放行记录数据

4.3.1.Compact

4.3.2.Redundant

4.3.3.行溢出数据

4.3.4.Compressed和Dynamic记录格式

4.3.5.CHAR的行结构存储

4.4.InnoDB数据页结构

4.5.Named File Formats机制

4.6.约束

4.7.视图

4.8.分区表

5.索引与算法

5.1.InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引:

B+树索引

全文索引

哈希索引

InnoDB支持的哈希索引是自适应的.InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引.

不能认为干预是否在一张表中生成哈希索引.

6.锁

6.1.什么是锁

锁是数据库系统区别于文件系统的一个关键特性.用于管理对于共享资源的并发访问.InnoDB会在行级别

对表数据进行加锁.

6.2.lock与latch

在数据库中,lock和latch都可以称之为锁,但是两者有着截然不同的含义.

latch一般理解为闩锁,因为其要求锁定时间足够短,太长时间的锁定会造成性能会非常差.在InnoDB存储引擎

中,latch又分为mutex(互斥量)和rwlock(读写锁).

lock的对象是事务,用来锁定的是数据库中的对象,如表,页,行等.一般lock的对象仅在事务commit或者rollback

之后进行释放.

对于InnoDB中的latch可以通过show engine innodb mutex;查看.

6.3.InnoDB存储引擎中的锁

6.3.1.锁的类型

共享锁(S Lock):允许事务读一行数据

排它锁(X Lock):允许事务删除或更新一行数据

如果事务T1获取了行r的共享锁,那么事务T2可以立即获得行r的共享锁,因为读取并没有改变行r的数据,这种

情况称之为锁兼容.但是如果事务T1获得了行r的排它锁,那么事务T2,T3必须等到事务T1释放了行r之后才可

以获得行r的排它锁.

另外InnoDB支持多粒度锁定.这种特性允许事务在行级上的锁和表级上的锁同时存在.为了支持多粒度的锁

操作,InnoDB存储引擎支持一种额外的锁方式,称之为意向锁.

只是对自己的读书过程做些笔记,如果有不对的地方,烦请各位指正.谢谢!