《高性能MySQL》&《MySQL技术内幕 InnoDB存储引擎》笔记

最新推荐文章于 2024-09-20 17:53:41 发布

zhuguang10

最新推荐文章于 2024-09-20 17:53:41 发布

阅读量3.7k

点赞数 6

分类专栏：程序员 Java. 架构文章标签： Java Java程序员程序员架构后端

本文链接：https://blog.csdn.net/zhuguang10/article/details/97652295

版权

第一章 MySQL架构与历史

MySQL的架构

从上图可以看出，MySQL数据库区别于其他数据库的最重要的一个特点就是其插件式的表存储引擎。需要注意的是，存储引擎是基于表的，而不是数据库的（即同一个数据库中的不同表可以有不同的存储引擎）。

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

连接MySQL

连接MySQL是一个连接进程和MySQL数据库实例进行通信。从程序设计的角度来说，本质上是进程通信。

连接MySQL的方式有：TCP/IP套接字、命名管道和共享内存、UNIX域套接字。

InnoDB与MyISAM存储引擎对比

InnoDB支持事务、外键、行锁；支持非锁定读，即默认读取操作不会产生锁。
InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。
提供了插入缓冲，二次写，自适应哈希索引，预读等高性能和高可用的功能。
对于表中数据的存储，InnoDB存储引擎采用了聚集的方式，因此每张表数据的存储都是按照主键的顺序进行存放（这种表称为“索引组织表”）。

MyISAM（发音：my-z[ei]m）不支持事务、表锁设计，支持全文索引（InnoDB已经支持）。

MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择InnoDB表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择MyISAM表。

第二章 InnoDB存储引擎概述

内存

缓冲池

在数据库系统中，由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

（注：上图中左上角的日志缓冲应该为重做日志缓冲）
需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。

重做日志缓冲

重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。默认为8MB.

系统在以下三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：

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

Checkpoint技术

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中D（Durability 持久性）的要求。

Checkpoint技术是用来解决以下几个问题：

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

当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。

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

重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这部分重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时这部分重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。

对于InnoDB存储引擎而言，其是通过LSN（Log Sequence Number）来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。

第三章文件

日志文件

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA 在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录了一些警告信息或正确的信息。

慢查询日志

可以在MySQL启动时设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该值默认为10秒。

查询日志

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。

二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作。

二进制日志文件默认未开启。手动开启后会使系统性能下降大概1%.

但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。

重做日志文件

在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。这两个文件就是重做日志文件，或者事务日志。

重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。

重做日志与二进制日志的区别：

二进制日志会记录所有与mysql数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志，而InnoDB存储引擎的重做日志只记录有关其本身的事务日志，
记录的内容不同，不管你将二进制日志文件记录的格式设为哪一种，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志；而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况；
写入的时间也不同，二进制日志文件是在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大；而在事务进行的过程中，不断有重做日志条目被写入重做日志文件中。

第四章之一表

索引组织表

在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table）。在InnoDB存储引擎表中，每张表都有个主键，如果在创建表时没有显式定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：

首先判断表中是否存在非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键；
如果不符合上述条件，InnoDB存储引擎会自动创建一个6字节大小的指针；

对于其他的一些数据库，如Microsoft SQL Server数据库，其中一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。堆表的特性决定了堆表上的索引都是非聚集的。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

从InnoDB存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为（block），InnoDB存储引擎的逻辑存储结构大致如图：

VARCHAR

MySQL数据库的VARCHAR类型可以存放65535字节数据（除去别的开销，实际最大可以存放65532字节）；
VARCHAR(N)中的N是指字符数；
此外，此处65535长度是指所有VARCHAR列的长度总和，如果列的长度总和超出这个长度，依然无法创建，如：

CREATE TABLE test (
    a VARCHAR(22000),
    b VARCHAR(22000),
    c VARCHAR(22000)
) CHARSET = latin1

分区表

分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，都可独自处理，也可以作为一个更大对象的一部分进行处理。

当前MySQL数据库支持以下几种类型的分区：

RANGE分区：行数据基于属于一个给定连续区间的列值放入分区；
LIST分区：和RANGE类似，只是LIST分区里面是离散的值；
HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数；
KEY分区：根据MySQL数据库提供的（即内置的）哈希函数进行分区。

分区和性能

数据库应用分为两类：一类是OLTP（在线事务处理），如Blog，电子商务，网络游戏等；另一类是OLAP（在线分析处理），如数据仓库，数据集市。在一个实际的应用环境中，可能既有OLTP的应用，也有OLAP的应用。如网络游戏中，玩家的操作的游戏数据库应用就是OLTP的，但是游戏厂商可能需要对游戏产生的日志进行分析，通过分析得到的结果来更好地服务于游戏，预测玩家的行为等，而这却是OLAP的应用。

对于OLAP的应用，分区的确可以很好地提高查询的性能，因为OLAP应用大多数查询需要频繁地扫描一张很大的表。假设有一张1亿行的表，其中有一个时间戳属性列。用户的查询需要从这张表中获取一年的数据。如果按时间戳进行分区，则只需要扫描相应的分区即可。

然而对于OLTP的应用，分区应该非常小心。在这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可。而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO。因此B+树可以很好地完成操作，不需要分区的帮助，并且设计不好的分区会带来严重的性能问题。

例如：很多开发团队会认为含有1000W行的表是一张非常大的表，所以他们往往会采用分区，如对主键做10个HASH的分区，这样每个分区就只有100W的数据了，因此查询应该变快了，如SELECT * FROM TABLE WHERE PK=@pk。但是有没有考虑过这样一种情况：100W和1000W行的数据本身构成的B+树的层次都是一样的可能都是2层。那么上述走主键分区的索引并不会带来性能的提高。如果1000W的B+树高度是3,100W的B+树的高度是2，那么上述按主键分区的索引可以避免1次IO，从而提高查询效率。这没问题，但是这张表只有主键索引，没有任何其他的列需要查询的，如果还有类似如下的SQL语句：SELECT * FROM TABLE WHERE KEY = @key,这时对于KEY的查询需要扫描所有的10个分区，即使每个分区的查询开销为2次IO，则一共需要20次IO。而对于原来单表的设计，对于KEY的查询只需要2~3次IO。

这里，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。

——没有全局的索引，所以才需要遍历每个分区的索引。

第四章之二 Schema与数据类型优化

选择优化的数据类型

更小的通常更好；更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少；
简单就好；例如，整形比字符串操作代价更低；实用内建类型而不是字符串来存储日期和时间；用整形存储IP地址等；
尽量避免NULL；如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL 的列使得索引、索引统计和值比较都更复杂。尽管把可为NULL的列改为NOT NULL带来的性能提升比较小，但如果计划在列上创建索引，就应该尽量避免设计成可为NULL的列；

字符串类型

VARCHAR 和 CHAR

VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。

下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？࿰