mysql学习笔记

最新推荐文章于 2024-04-24 16:25:27 发布

勤奋的凯尔森同学

最新推荐文章于 2024-04-24 16:25:27 发布

阅读量249

点赞数 1

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/JingLisen/article/details/88045654

版权

MySQL 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

个人博客：小景哥哥

`MySQL`高级进阶

一、基本概念

数据库：物理操作系统文件或其他形式文件类型的集合。
数据库实例：MySQL数据库由后台进程以及一个共享内存区组成。

数据库是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合；数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

MySQL被设计为一个单进程多线程架构的数据库，MySQL数据库实例在系统上的表现就是一个进程。MySQL数据库是按照/etc/my.cnf → /etc/mysql/my.cnf → /usr/local/mysql/etc/my.cnf → ~/.my.cnf的顺序读取配置文件的。在Linux环境下，配置文件一般放在/etc./my.cnf下。配置文件中有一个配置参数datadir，该参数指定了数据库所在的路径。在Linux操作系统下默认datadir为/usr/local/mysql/data。

连接MySQL操作是一个连接进程和MySQL数据库实例进行通信。常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式，也是网络中使用得最多的一种方式。

二、`MySQL`组成

连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲组件
插件式存储引擎
物理文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FnI2VggP-1572188915287)(/Users/jason/Library/Application Support/typora-user-images/image-20191027143934043.png)]

三、`MySQL`存储引擎

存储引擎是基于表的，而不是数据库。

`InnoDB`存储引擎

InnoDB存储引擎支持事务，其设计目标主要是面向在线事务处理(OLTP, Online Transaction Processing)的应用。其特点是行锁设计、支持外键，并支持类似于Oracle的非锁定读，即默认读取操作不会产生锁。

InnoDB通过使用多版本并发控制(MVCC, Multi-Version Concurrency Control)来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为Repeatable级别。同时使用一种被称为next-key locking的策略来避免幻读(phantom)现象的产生。InnoDB存储引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead)等高性能和高可用的功能。

对于表中数据的存储，InnoDB存储引擎采用了聚集(clustered)的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显示地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。

实践证明，InnoDB存储引擎具备高可用性、高性能以及高可扩展性。

`MyISAM`存储引擎

MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP(On-line Analytical Processing)数据库应用。MyISAM的缓冲池只缓存索引文件，而不缓存数据文件，这和绝大多数数据库都非常的不同。MyISAM存储引擎由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

`Memory`存储引擎

Memory存储引擎(之前称HEAP存储引擎)将表中的数据存放在内存中，如果数据库重启或发生故障崩溃，表中的数据将会消失。它非常适合用于存储临时数据的临时表，以及数据仓库中的维度表。Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引。

Memory存储引擎非常快，但它只支持表锁，并发性能较差，并且不支持TEXT和BLOB。最重要的是，存储变长字段(varchar)时是按照定长字段(char)的方式进行的，因此会浪费内存，不过eBay工程师已解决此问题。

MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集(intermediate result)。如果中间结果集大于Memory存储引擎表的容量设置，又或者中间结果含有TEXT或BLOB列类型字段，则MySQL数据库会把其转换到MyISAM存储引擎表而存放到磁盘中。

`Archive`存储引擎

Archive存储引擎只支持insert和select操作，从MySQL 5.1开始支持索引。Archive存储引擎使用zlib算法将数据行(row)进行压缩后存储，压缩比一般可以达1:10。Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能。

`Federated`存储引擎

Federated存储引擎并不存放数据，它只是指向一台远程MySQL数据库服务器上的表。目前只支持MySQL数据库表，不支持异构数据库表。

`Maria`存储引擎

Maria存储引擎是由MySQL创始人Michael Widenius新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认存储引擎。Maria支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的选项，以及更好的BLOB字符类型的处理性能。

四、`InnoDB`存储引擎详解

InnoDB是事务安全的MySQL存储引擎，设计上采用了类似于Oracle数据库的架构。通常来说，InnoDB存储引擎是OLTP应用中核心表的首选存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地使用内存和CPU。

后台线程

InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。Master Thread是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(insert buffer)、UNDO页的回收等。在InnoDB存储引擎中大量使用了AIO(Async IO)来处理写IO请求，这样可以极大提高数据库的性能，而IO Thread的工作主要是负责这些IO请求的回调(call back)处理。IO Thread分别有write、read、insert buffer和log IO Thread。

事务被提交后，其所使用的undolog可能不再需要，因此需要Purge Thread来回收已经使用并分配的undo页。在InnoDB 1.1版本之前，purge操作仅在InnoDB存储引擎的Master Thread中完成。而从InnoDB 1.1版本开始，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能。从InnoDB 1.2开始，InnoDB支持多个Purge Thread，这样做的目的是为了进一步加快undo页的回收。同时由于Purge Thread需要离散地读取undo页，这样也能更进一步利用磁盘的随机读取性能。

Purge Cleaner Thread是在InnoDB 1.2.x版本引入的。其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。而其目的是为了减轻Master Thread的工作及对于用户查询线程的阻塞，进一步提高InnoDB存储引擎的性能。

内存

InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。为了协调CPU速度和磁盘速度的鸿沟，基于磁盘的的数据库系统通常使用缓冲池技术来提高数据库的性能。

对于数据库中页的修改操作，首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为checkpoint的机制刷新回磁盘。

缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB存储的锁信息(lock info)、数据字典信息(data dictionary)等，不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

LRU List、Free List、Flush List

数据库的缓冲池是通过LRU(Latest Recent Used, 最近最少使用)算法来进行管理的。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放LRU列表中尾端的页。在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，同样使用LRU算法对缓冲池进行管理。并且InnoDB存储引擎对LRU算法进行了一些改进，LRU列表中还加入了midpoint位置。新读取到的页，虽然是最新访问的页，但并不是直接插入到LRU列表的首部，而是放入到LRU列表的midpoint位置。这个算法在InnoDB存储引擎下称为midpoint insertion strategy。默认配置下，该位置在LRU列表长度的 5/8处。把midpoint之后的列表称为old列表，之前的表称为new列表。可以简单地理解为new列表中的页都是最为活跃的热点数据。

改进之后的LRU算法的优点：若直接读取到的页放入到LRU的首部，那么某些SQL操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放入LRU列表的首部，那么非常可能将所需要的热点数据页从LRU列表中移除，而在下一次需要读取该页时，InnoDB存储引擎需要再次访问磁盘。为了解决这个问题，InnoDB存储引擎引入了另一个参数来进一步管理LRU列表，这个参数是innodb_old_blocks_time，用于表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则根据LRU算法，淘汰LRU列表末端的页，将该内存空间分配给新的页。当页从LRU列表的old部分加入到new部分时，称此时发生的操作为page made young，而因为innodb_old_blocks_time的设置而导致页没有从old部分移动到new部分的操作称为page not made young。

InnoDB从1.0.x开始支持压缩页的功能，即将原本16KB的页压缩为1KB、2BK、4KB、8KB。对于非16KB的页，是通过unzip_LRU列表进行管理的，通过伙伴算法进行内存的分配。例如从缓冲池中申请大小为4KB的页：

检查4KB的unzip_LRU列表，检查是否有可用的空闲页；
若有，则直接使用；
否则，检查8KB的unzip_LRU列表；
若能够得到空闲页，将页分为2个4KB页，存放到4KB的unzip_LRU列表；
若不能得到空闲页，从LRU列表中申请一个16KB的页，将页分为1个8KB的页、2个4KB的页，分别存放到对应的unzip_LRU列表中。

在LRU列表中的页被修改后，称该页为脏页(dirty page)，即缓冲池中的页和磁盘上的页的数据产生不一致。数据库通过checkpoint机制将脏页刷新回磁盘。Flush列表中的页即为脏页列表。脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

重做日志缓冲(redo log buffer)存放着InnoDB存储引擎的重做日志信息，它按照一定的频率将重做日志刷新到重做日志文件。默认8MB的重做日志缓冲池足以满足绝大多数的应用。重做日志在以下三种情况下会将重做日志缓冲区中的内容刷新到外部磁盘的重做日志文件中。

Master Thread每一秒将重做日志缓冲刷新到重做日志文件；
每个事务提交时会将重做日志缓冲刷新到重做日志文件；
当重做日志缓冲池剩余空间小于1\2时，重做日志缓冲刷新到重做日志文件。

Checkpoint技术

为了避免发生数据丢失的问题，当前事务数据库系统普遍采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。

Checkpoint(检查点)可以缩短数据库的恢复时间；缓冲池不够用时，可将脏页刷新到磁盘；重做日志不可用时，刷新脏页。

对于InnoDB存储引擎而言，其是通过LSN(Log Sequence Number)来标记版本的。LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。InnoDB内部有两种Checkpoint，Sharp Checkpoint和Fuzzy Checkpoint。Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，也是默认的工作方式，即innodb_fast_shutdown=1。若数据库在运行时也使用Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在InnoDB存储引擎内部使用Fuzzy Checkpoint进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。