务器环境

MySQL是通过文件系统对数据和索引进行存储的。

MySQL从物理结构上可以分为日志文件和数据与索引文件。

MySQL在Linux中的数据索引文件和日志文件通常放在/var/lib/mysql。

slow_query_log_fileslow_query_log.log

1.3

ibdata文件使用共享表空间存储表数据和索引信息所有表共同使用一个或者多个ibdata文件。

.frm文件主要存放与表相关的数据信息主要包括表结构的定义信息

.ibd使用独享表空间存储表数据和索引信息一张表对应一个ibd文件。

.frm文件主要存放与表相关的数据信息主要包括表结构的定义信息

二、数据库基础概念

是传统关系型数据库的主要应用场景用来执行一些基本的、日常的事务处理

比如数据库记录的增、删、改、查等等。

不适合海量数据的存取与处理联机分析处理

OLAP

是分布式数据库的主要应用场景它对实时性要求不高但处理的数据量大通常应用于复杂的动态报表系统上。

2.2

行式存储法(Row-based)数据一行行存储传统的关系型数据库如

Oracle、DB2、MySQL、SQL

列式存储(Column-based)数据一列列存储列式是相对于行式存储来说的新兴的

Hbase、HP

数据是按照列为基础逻辑存储单元进行存储的一列中的数据在存储介质中以连续存储形式存在。

ElasticSearch、MongoDB、Solr、Splunk…

Redis(K/V)

psMongoDB属于文档存储存储一个JSON(BSON)文档、或者一个文本二进制文档。

类似的有elasticserachsolross……

2.3

行式存储实操中我们会发现行式数据库在读取数据时存在一个固有的“缺陷”。

这个缺陷是什么

列式存储

低延迟查询过程中可针对各列的运算并发执行最后在内存中聚合完整记录集最大可能降低查询响应时间;

查找数据高效无需维护索引查询过程中能够尽量减少无关IO避免全表扫描

节省空间因为各列独立存储且数据类型已知可以针对该列的数据类型、数据量大小等因素动态选择压缩算法以提高物理存储利用率如果某一行的某一列没有数据那在列存储时就可以不存储该列的值这将比行式存储更节省空间。

当然跟行数据库一样列式存储也有不太适用的场景这个场景是什么

数据需要频繁更新的交易场景表中列属性较少的小量数据库场景不适合做含有删除和更新的实时操作

2.4

先取行再取列从连续的空间取出10w行从10w行结果集合中取出name和status列

1行平均10KB10W行10W

Connectors连接器负责跟客户端建立连接、获取权限、维持和管理连接。

Management

查询优化器SQL语句在查询之前会使用查询优化器对查询进行优化。

explain语句查看的SQL语句执行计划就是由查询优化器生成的。

Cache和Buffer

查询缓存在MySQL5.7中包含缓存组件。

在MySQL8中移除了

Pluggable

Engines存储引擎存储引擎就是存取数据、建立与更新索引、查询数据等技术的实现方法。

3.2

所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实

存储引擎层

连接完成后如果你没有后续的动作这个连接就处于空闲状态。

客户端如果太长时间没动静连接器就会自动将它断开。

这个时间是由参数wait_timeout

mysql

列显示为“Sleep”的这一行就表示现在系统里面有一个空闲连接。

第二步查缓存

拿到一个查询请求后会先到查询缓存看看之前是不是执行过这条语句。

之前执行过的语句及其结果可能会以

key-value

如果语句不在查询缓存中就会继续后面的执行阶段。

执行完成后执行结果会被存入查询缓存中。

如果查询命中缓存MySQL

不需要执行后面的复杂操作就可以直接返回结果效率会很高但是不建议使用MySQL的内置缓存功能

第三步分析SQL语句

如果查询缓存没有命中接下来就需要进入正式的查询阶段了。

客户端程序发送过来的请求实际上只是一个字符串而已所以MySQL服务器程序首先需要对这个字符串做分析判断请求的语法是否正确然后从字符串中将要查询的表、列和各种查询条件都提取出来本质上是对一个SQL语句编译的过程涉及词法解析、语法分析、语义分析等阶段。

SQL

语法分析根据词法分析的结果语法分析器会根据语法规则做语法检查判断你输入的这个

MySQL

预处理器预处理器则会进一步去检查解析树是否合法比如表名是否存在语句中表的列是否存在等等在这一步MySQL会检验用户是否有表的操作权限。

1词法分析

selectc_id,first_name,last_namefromcustomerwherec_id14

MySQL

把select这个关键字识别出来这是一个查询语句把“customer”识别成“表名

customer”

syntax”错误提醒就是在这个位置出现的。

如果语法正确就会根据

MySQL语法规则与SQL

预处理器则会进一步去检查解析树是否合法比如表名是否存在语句中表的列是否存在等等在这一步MySQL会检验用户是否有表的操作权限。

预处理之后会得到一个新的解析树。

第四步优化SQL语句

优化器顾名思义就是对查询进行优化。

作用是根据解析树生成不同的执行计划然后选择最优的执行计划。

MySQL

里面使用的是基于成本模型的优化器哪种执行计划执行时成本最小就用哪种。

而且它是io_cost和cpu_cost的开销总和它通常也是我们评价一个查询的执行效率的一个常用指标。

#查看上次查询成本开销

当有多个索引可用的时候决定使用哪个索引在一个语句有多表关联join的时候决定各个表的连接顺序以哪个表为基准表。

举个栗子

1比如hello数据库中表customer上执行下面的语句这个语句用到了两个索引last_name和address_id。

#hello数据库中表customer

既可以使用last_name索引查询然后过滤列address_id

也可以使用address_id索引查询然后过滤列last_name。

两种执行计划的结果是一样的但是执行效率会有所不同而优化器的作用就是决定选择使用哪一个方案。

注意优化器最多就是辅助作用很有限我们的SQL语句不能依赖于MySQL的优化器去调优如果SQL语句垃圾则没有优化的空间。

优化SQL的根本在于掌握MySQL分析与调优技能。

第五步执行SQL语句

开始执行的时候要先判断一下你对这个表customer有没有执行查询的权限如果没有就会返回没有权限的错误。

2调用存储引擎接口查询

如果有权限就使用指定的存储引擎打开表开始查询。

执行器会根据表的引擎定义去使用这个引擎提供的查询接口提取数据。

InnoDB

调用引擎接口取“下一行”重复相同的判断逻辑直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端。

举个栗子

比如我们新建一个用户lesson1_test只有表actor的查询权限没有表customer的查询权限。

CREATE

mysql_userlocalhost;使用这个用户lesson1_test连接mysql执行下面的查询语句就会返回没有权限的错误。

mysql

在/etc/my.cnf文件中修改“query_cache_type”参数

修改配置文件

成本高查询缓存的失效非常频繁只要有对一个表的更新这个表上所有的查询缓存都会被清空。

因此很可能你费劲地把结果存起来还没使用呢就被一个更新全清空了。

命中率不高对于更新压力大的数据库来说查询缓存的命中率会非常低。

除非你的业务就是有一张静态表很长时间才会更新一次。

比如一个系统配置表那这张表上的查询才适合使用查询缓存。

功能并不如业的缓存工具更好redis、memcache、ehcache…

MySQL

也提供了这种“按需使用”的方式。

你可以将参数query_cache_type

设置成

只有InnoDB引擎支持事务、行锁、外键。

在选择引擎时尽可能使用InnoDB引擎。

使用其他引擎在mysql中默认使用InnoDB引擎一个数据库中不同的表可以使用不同的引擎。

create

存储引擎说明MyISAM高速引擎拥有较高的插入查询速度但不支持事务InnoDB5.5版本后MySQL的默认数据库支持事务和行级锁定比MyISAM处理速度稍慢ISAMMyISAM的前身MySQL5.0以后不再默认安装MRG_MyISAM将多个表联合成一个表使用在超大规模数据存储时很有用Memory内存存储引擎拥有极高的插入更新和查询效率。

但是会占用和数据量成正比的内存空间。

只在内存上保存数据意味着数据可能会丢失Falcon一种新的存储引擎支持事物处理传言可能是InnoDB的替代者Archive将数据压缩后进行存储非常适合存储大量的独立的作为历史记录的数据但是只能进行插入和查询操作CSVCSV

CSV

引擎怎么选择大部分情况下InnoDB都是正确的选择。

对于如何选择MySQL引擎这件事上可以归纳为一句话除非需要用到某些InnoDB不具备的特性并且没有其他办法可以替代否则都应该选择InnoDB引擎。

InnoDB和MyISAM存储引擎区别

.myi索引文件锁表锁、行锁表锁事务支持不支持CRDU读、写读多索引结构B

TreeB

上图详细显示了InnoDB存储引擎的体系架构从图中可见InnoDB存储引擎由内存池后台线程和磁盘文件三大部分组成。

4.4

用于加速数据的访问和修改通过将热点数据缓存在内存的方法最大限度地减少磁盘

Buffer

中数据以页为存储单位其实现数据结构是以页为单位的单链表。

默认大小为128M

Pool

用于加速非热点数据中二级索引的写入操作。

由于二级索引数据的不连续性导致修改二级索引时需要进行频繁的磁盘

消耗大量性能Change

25%最大50%在引擎启动时便初始化完成。

其物理结构为一棵名为

ibuf

聚簇索引是也叫聚集索引有的也叫索引组织表指的是一种数据存储方式指数据与索引的数据结构存储在一起。

如

InnoDB

的主键索引中所有叶子节点都存储了对应行的数据。

因为数据肯定只是存储在一个地方所以一个表只能有一个聚集索引。

周期性合并二级索引页

IndexAHI用于实现对于热数据页的一次查询。

是建立在索引之上的索引使用聚簇索引进行数据页定位的时候需要根据索引树的高度从根节点走到叶子节点通常需要3

根据对索引使用情况的分析和索引字段的分析通过自调优Self-tuning的方式为索引页建立或者删除哈希索引。

AHI

所作用的目标是频繁查询的数据页和索引页而由于数据页是聚簇索引的一部分因此

AHI

获取二级索引页的记录指针再根据主键沿着聚簇索引查找数据若聚簇索引查询同样命中

AHI则直接返回目标数据页的记录指针此时就可以根据记录指针直接定位数据页。

#查看自适应哈希状态信息

来缓冲日志文件的写入操作。

内存写入加上日志文件顺序写的特点使得InnoDB

对于任何修改操作都将录入诸如

这样的日志文件中因此日志文件的写入操作非常频繁却又十分零散。

这些文件都存储在磁盘中因此日志记录将引发大量的磁盘

Buffer将分散的写入操作放在内存中通过定期批量写入磁盘的方式提高日志写入效率和减少磁盘

注意这种将分散操作缓冲

事务提交的时候必须将操作写入日志中此时日志文件若未落盘而系统崩溃则相关操作将丢失而无法恢复。

而使用

write

指令将缓冲区数据刷入文件中若操作系统在未刷入前崩溃则同样将导致数据丢失不可恢复。

InnoDB

将所有数据都逻辑地存放在一个空间中称为表空间Tablespace。

表空间由段Segment、区extent、页Page组成。

如果开启了独立表空间innodb_file_per_table1每张表的数据都会存储到一个独立的表空间即一个单独的.ibd文件。

如果设置了参innodb_file_per_table0关闭了独占表空间则所有基于InnoDB存储引擎的表数据都会记录到系统表空间。

InnoDB

数据字典、双写缓冲、修改缓冲和回滚日志的存储位置如果关闭独立表空间它也将存储所有表数据和索引。

10MB、名为

的文件系统表空间所对应的文件由innodb_data_file_path

设置默认为8MB且不可缩减即使删除系统表空间中存储的表和索引此过程释放的空间仅仅是在表空间文件中标记为已释放而已并不会缩减其在磁盘中的大小。

数据字典Data

数据字典是由各种表对象的元数据信息表结构索引列信息等组成的内部表。

双写缓冲Doublewrite

Buffer双写缓冲用于保证写入磁盘时页数据的完整性防止发生部分写失效问题Doublewrite

Buffer

中写入磁盘时可能会出现页未写完全但系统崩溃的问题。

InnoDB

在数据从

后写入磁盘的双写缓冲中再写入表数据文件因此数据文件的写入总是按照

16K

是记录数据修改前状态的逻辑日志保存所有被更新的数据行逻辑状态的历史版本。

Undo

log

操作时对数据的恢复和根据数据的历史版本实现多版本并发控制MVCC功能。

2独立表空间

会为每个数据库单独创建子文件夹数据库文件夹内为每个数据表单独建立一个表空间文件

table.ibd

其他类型的信息如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等仍存放于系统表空间

3其他

实例有权限的地方。

该表空间内可以容纳多张数据表同时在创建时可以指定该表空间所使用的默认引擎。

通用表空间存在的目的是为了在系统表空间与独立表空间之间作出平衡。

系统表空间与独立表空间中的表可以向通用表空间移动反之亦可但系统表空间中的表无法直接与独立表空间中的表相互转化。

共享表空间

共享表空间Shared

Tablespace用于独立保存临时表数据及其回滚信息。

该表空间文件路径由

4.5

表空间由各个段Segment组成创建的段类型分为数据段、索引段、回滚段等。

由于

InnoDB

segment。

一个段会包含多个区至少会有一个区段扩展的最小单位是区。

数据段称为

索引树上一个节点就是一个页MySQL规定一个页上最少存储2个数据项。

如果向一个页插入数据时这个页已将满了就会从区中分配一个新页。

如果向索引树叶子节点中间的一个页中插入数据如果这个页是满的就会发生页分裂。

操作系统管理磁盘的最小单位也是页是操作系统读写磁盘最小单位Linux中页一般是4K可以通过命令查看。

#默认

所以InnoDB从磁盘中读取一个数据页时操作系统会分4次从磁盘文件中读取数据到内存。

写入也是一样的需要分4次从内存写入到磁盘中。

4行【Row】

InnoDB的数据是以行为单位存储的1个页中包含多个行。

在MySQL5.7中InnoDB提供了4种行格式Compact、Redundant、Dynamic和Compressed行格式Dynamic为MySQL5.7默认的行格式。

InnoDB行格式官网https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html创建表时可以指定行格式

CREATE

innodb_default_row_formatDYNAMIC;

#查看表行格式

在数据库中进行读取操作将从磁盘中读到的页放在缓冲池中下次再读相同的页时首先判断该页是否在缓冲池中。

若在缓冲池中称该页在缓冲池中被命中直接读取该页。

否则读取磁盘上的页。

对于数据库中页的修改操作则首先修改在缓冲池中的页然后再以一定的频率刷新到磁盘上。

页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时都触发而是通过一种称为CheckPoint的机制刷新回磁盘。

内存数据落盘要考虑的核心问题高性能写入数据同时要保证数据的绝对安全性

数据库灵魂三问

分散写入操作放在内存中通过定期批量写入磁盘的方式提高写入效率减少磁盘

②如何持久化也就是修改后的数据如何到磁盘中去。

内存里缓冲池中的数据页要完成持久化通过两个流程来完成

脏页落盘

对于数据库中页的修改操作则首先修改在缓冲池中的页缓冲池中的页与磁盘中的页数据不一致所以称缓冲池中的页为脏页。

然后再以一定的频率将脏页刷新到磁盘上。

页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发而是通过一种称为CheckPoint的机制刷新回磁盘。

如果每次一个页发生变化就进行落盘每次落盘一个页必然伴随着4次IO操作那么性能开销会非常大。

而且这个开销是随着写入操作的增加指数级增长的如果数据长期在内存中保存那么数据就存在安全性风险

Ahead

Commit机制要求当一个事务提交时所有产生的日志都必须刷新到磁盘上。

如果日志刷新成功后缓冲池中的数据刷新到磁盘前数据库发生了宕机那么重启时数据库可以从日志中恢复数据。

这样可以保证数据的安全性

Write

LogWAL策略要求数据的变更写入到磁盘前首先必须将内存中的日志写入到磁盘InnoDB

WALWrite

说白了保证数据的持久性与安全性我们采用记录日志的方式那么也就是说日志安全了数据就安全了

为了确保每次日志都写入到redo日志文件在每次将redo日志缓冲写入redo日志后调用一次fsync操作将缓冲文件从文件系统缓存中真正写入磁盘。

这样做不就等同于数据直接写入磁盘吗

来控制redo日志刷新到磁盘的策略。

接下来我们看redo日志如何落盘。

3redo日志落盘

InnoDB的该属性可以控制每次事务提交时InnoDB的行为。

是InnoDB性能调优的一个基础参数涉及InnoDB的写入性能和数据安全性。

#查看写入时机参数配置

‘innodb_flush_log_at_trx_commit’;

配置详解

log日志文件的。

而是等待主线程每秒写入一次。

如果MySQL崩溃或者服务器宕机此时内存里的数据会全部丢失最多会丢失1秒的事务。

写入效率最高但是数据安全最低

为1时每次事务提交时会将数据将从log

buffer写入redo日志文件与文件系统缓存并同时fsync刷新到磁盘中。

系统默认配置为1MySQL崩溃已经提交的事务不会丢失要完全符合ACID必须使用默认设置1。

写入效率最低但是数据安全最高

为2时事务提交时也会将数据写入redo日志文件与文件系统缓存但是不会调用fsync而是让操作系统自己去判断何时将缓存写入磁盘。

事务提交都会将数据刷新到操作系统缓冲区可以认为是已经持久化到磁盘但没有真正意义上持久化到磁盘。

如果MySQL崩溃已经提交的事务不会丢失。

但是如果服务器宕机或者意外断电操作系统缓存内的数据会丢失所以最多丢失1秒的事务。

Tips.

用户程序写入数据到磁盘文件时需要调用操作系统的接口操作系统本身是有缓冲区的之后依赖操作系统机制不时的将缓存中刷新到磁盘文件中。

用户程序可以执行fsync操作将操作系统缓冲区的数据刷入到磁盘文件中。

只有设置为1是最安全但是性能消耗的方式可以真正地保证事务的持久性但是由于MySQL执行刷新操作

fsync()

的性能会明显地下降。

0的性能最好的模式但安全性却很差。

2是一个折中的选择。

在配置时需综合安全性和性能等因素。

4CheckPoint检查点机制

如果redo日志可以无限地增大同时缓冲池也足够大那么是不需要将缓冲池中页的新版本数据页刷新回磁盘。

当发生宕机时完全可以通过redo日志来恢复整个数据库系统中的数据到宕机发生的时刻。

显然这是不可能的因此Checkpoint检查点技术就诞生了目的是解决以下几个问题

缩短数据库的恢复时间当数据库发生宕机时数据库不需要重做所有的日志因为Checkpoint之前的页都已经刷新回磁盘。

数据库只需对Checkpoint后的redo日志进行恢复这样就大大缩短了恢复的时间。

缓冲池不够用时将脏页刷新到磁盘当缓冲池不够用时根据LRU算法会溢出最近最少使用的页若此页为脏页那么需要强制执行Checkpoint将脏页也就是页的新版本刷回磁盘。

redo日志不可用时刷新脏页当redo日志出现不可用时Checkpoint将缓冲池中的页至少刷新到当前redo日志的位置

1).

数据库系统对redo日志的设计都是循环使用的并不是让其无限增大的。

如果当数据库宕机恢复操作时不需要redo日志中的部分redo日志这部分就可以被覆盖重用。

2).

Number来标记日志刷新的版本。

LSN是8字节的数字每个页有LSNredo日志中也有LSNCheckpoint也有LSN。

SHOW

Checkpoint发生的时间、条件及脏页的选择等都非常复杂。

而Checkpoint所做的事情无外乎是将缓冲池中的脏页刷回到磁盘不同之处在于每次刷新多少页到磁盘每次从哪里取脏页以及什么时间触发Checkpoint。

Checkpoint分类

在InnoDB存储引擎内部有两种Checkpoint分别为Sharp

Checkpoint、Fuzzy

checkpoint数据库正常运行时在不同的时机将部分脏页写入磁盘。

仅刷新部分脏页到磁盘也是为了避免一次刷新全部的脏页造成的性能问题。

Fuzzy

Checkpoint默认方式只刷新一部分脏页不是刷新所有脏页主要有以下几种情况

Master

Thread中会以每秒或者每10秒一次的频率将部分脏页从内存中刷新到磁盘这个过程是异步的。

正常的用户线程对数据的操作不会被阻塞。

2、FLUSH_LRU_LIST

Checkpoint缓冲池不够用时根据LRU算法会淘汰掉最近最少使用的页如果该页是脏页的话会强制执行CheckPoint将该脏页刷回磁盘由Page

Cleaner

Checkpoint重做日志不可用的情况需要强制从脏页列表中选取一些脏页刷新磁盘到缓存由Page

Cleaner

Thread完成。

由于磁盘是一种相对较慢的存储设备内存与磁盘的交互是一个相对较慢的过程。

innodb_log_file_size定义的是一个相对较大的值正常情况下由前面两种checkpoint刷新脏页到磁盘在前面两种checkpoint刷新脏页到磁盘之后脏页对应的redo

Flush

much即脏页数量太多导致强制进行Checkpoint。

由参数innodb_max_dirty_pages_pt

来控制默认75即75%。

当脏页数量占据75%缓冲池时刷新一部分脏页到磁盘。

由Page

Cleaner

我们知道脏页会在某些场景下进行刷盘将缓冲池内的脏页数据落地到磁盘。

因为存储引擎缓冲池内的数据页大小默认为16KB而文件系统一页大小为4KB所以在进行刷盘操作时就有可能发生如下场景

如图所示数据库准备刷新脏页时需要四次IO才能将16KB的数据页刷入磁盘。

但当执行完第二次IO时数据库发生意外宕机导致此时才刷了2个文件系统里的页这种情况被称为写失效partial

pagewrite。

此时重启后磁盘上就是不完整的数据页就算使用redo

注意redo

log无法恢复数据页损坏的问题恢复必须是数据页正常并且redo

log中记录的是对页的物理操作如偏移量600写’xxxx’记录。

如果这个页本身已经发生了损坏再对其进行重做是没有意义的

该怎么解决这个问题

在数据库进行脏页刷新时如果此时宕机有可能会导致磁盘数据页损坏丢失我们重要的数据。

此时就算重做日志也是无法进行恢复的因为重做日志记录的是对页的物理修改。

其实就是在重做日志前用户需要一个页的副本当写入失效发生时先通过页的副本来还原该页再进行重做这就是double

如图其实Double

buffer物理磁盘上共享表空间中连续的128个页即2个区extent大小同样为2MB可以看出有了Double

在对缓冲池的脏页进行刷新时并不直接写磁盘而是会通过memcpy函数将脏页先复制到内存中的Double

write

buffer再分两次每次1MB顺序地写入共享表空间的物理磁盘上然后马上调用fsync函数同步磁盘。

这样做可以避免缓冲写带来的问题Double

write崩溃恢复

如图如果操作系统在将页写入磁盘的过程中发生了崩溃在恢复过程中InnoDB存储引擎可以从共享表空间中的Double

write中找到该页的一个副本将其复制到表空间文件再应用重做日志。

090923

不会记录完整的一页数据因为这样日志太大它只会记录那次sequence如何操作了update,insert哪页(page)的哪行(row)因为数据库使用的页page默认16KB大小和操作系统对磁盘的操作页page默认4KB不一样当提交了一个页需要刷新到磁盘会有多次IO

此时刷了前面的8k时异常发生宕机。

在系统恢复正常后如果没有double

write

writer的数据的完整性如果不完整直接丢弃doublewrite

buffer内容重新执行那条redo