第一章 MySQL体系结构和存储引擎

1.2 MySQL体系架构

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

1.3MySQL存储引擎

1.3.1InnoDB存储引擎

InnoDB存储引擎支持事物，其特特是行锁设计、支持外键。InnoDB存储引擎通过使用多版本并发控制（MVCC）来获得并发性。

同时，使用一种被称为next-key locking 的策略来避免幻读（phantom）现象的产生。

1.3.2MyISAM存储引擎

MyISAM存储引擎不支持事物、表锁设计、支持全文索引。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

1.3.3NDB存储引擎

1.3.4 Memory存储引擎

1.3.5 Archive存储引擎

1.3.6 Federated存储引擎

1.3.7 Maria存储引擎

1.3.8 其他存储引擎

第2章 InnoDB存储

2.1 InnoDB存储概述

该存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU。

2.3 InnoDB体系架构

2.3.2 内存

1 缓冲池

具体来看，缓冲池中缓存的数据页类型有:索引页、数据页、undo 页、插入缓冲(insert buffer)、自适应哈希索引(adaptive hash index)、InnoDB 存储的锁信息(lockinfo)、数据字典信息(data dictionary)等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的部分而已。图2-2 很好地显示了InnoDB 存储引擎中内存的结构情况。

2 LRU算法

带有midpoint的LRU算法。和传统的LRU算法做了一些优化。在InnoDB存储引擎中，LRU算法列表中还加入了midpoint位置。新读取到的页，并不是直接放入到LRU列表的首部，而是放在midpoint位置。默认配置下载LRU列表长度的5/8处。

为什么不用普通的LRU算法呢？因为某些SQL会全表扫描，把LRU列表中的热点数据移除，而新放入的页可能用一次就不用了，所以对LRU算法进行了优化。

LRU列表midpoint之前为new部分，之后为old部分，因为innodb_old_bolcks_time的设置而导致old部分移动到new部分的操作成为page made young。

伙伴算法了解一下。

3 重做日志缓冲

通常情况下，8MB的重做日志缓冲池足以满足绝大部分的应用，因为重做日志会在下列三种情况下回将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中。

MasterThread每-秒将重做日志缓冲刷新到重做日志文件;

每个事务提交时会将重做日志缓冲刷新到重做日志文件;

当重做日志缓冲池剩余空间小于1/2 时，重做日志缓冲刷新到重做日志文件。

2.4 Checkpoint技术

Checkpoint技术的目的是解决一下几个问题：

缩短数据库的恢复时间

缓冲池不够用时，将脏页刷新到磁盘

重做日志不可用时，刷新脏页。

Checkponit所做的事情无外乎是将缓冲池中的脏页刷回到磁盘。

2.6 InnoDB关键特性

插入缓冲（Insert Buffer）

两次写（Double Write）

自适应哈希索引（Adaptive Hash Index）

异步IO(Async IO)

刷新邻接表（Flush Neighbor Page）

2.6.1 插入缓冲

1 Insert Buffer

InnoDB缓冲池中有Insert Buffer信息固然不错，但是Insert Buffer和数据页一样，也是物理页的一个组成部分。

插入缓冲是对辅助索引来说的，而不是对主键索引来的说的,即非唯一辅助索引的插入操作。

比如此时有一张表student（id,name），id为主键索引， name为辅助索引。现在我插入一条记录，插入记录的同时伴随着主键索引和辅助索引的插入。主键索引其插入是有序的，所以速度比较快，但是辅助索引是离散型读取和插入的，所以速度很慢，此时把插入辅助索引的操作放在插入缓冲中，等到合适的时候批量插入。

Insert Buffer的使用需要同时满足一下两个条件：

●索引为辅助索引(secondary index)

●索引不是唯一（unique）的

3 Insert Buffer的内部实现

Insert Buffer的数据结构是一颗B+树。

当一个辅助索引要插入到页（space,offset）时，如果这个页不在缓存池红，那么InnoDB存储引擎首先根据上述规则构造一个search key，然后插入到Insert Buffer B+树的叶子节点中。

4 Merger Insert Buffer

概括地说，Merge Insert Buffer的操作可能发生在以下几种情况下:

●辅助索引页被读取到缓冲池时;

●InsertBufferBitmap页追踪到该辅助索引页已无可用空间时;

●Master Thread。

2.6.2 两次写

貌似很厉害的样子，看书吧。

第3章文件

3.1 参数文件

mysql配置参数文件

3.1.1 什么是参数

查询所有的参数

SHOW VARIABLES;

模糊查询某个参数

SHOW VARIABLES LIKE "version%";

3.2 日志文件

错误日志（error.log）

二进制日志(binlog)

慢查询日志(slow query log)

查询日志(log)

3.2.1 错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。该文件不仅记录了所有的错误信息，也记录了一些警告信息或者正确的信息。

文件的位置：

show variables like "log_error";

3.2.2 慢查询日志

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

SHOW VARIABLES LIKE "long_query_time";

在默认情况下，MySQL数据库并不启动慢查询日志，用户需要手工将这个参数设为ON。

查询慢查询日志开关状态和日志位置

SHOW VARIABLES LIKE "slow_query_log%";

开启慢查询日志（修改/etc/my.cnf文件）

# 开启慢查询日志
slow_query_log=on

重新启动MySQL (重启、重启、重启)

测试慢查询，如下图所示，查询时间为27秒

则在慢查询日志中记录了当前SQL,如下图所示

慢查询日志信息保存的mysql.slow_log表里

//查询慢查询保存位置（文件还是表）
show variables like "log_output";
//设置慢查询保存在表里
SET GLOBAL log_output="table";
//执行SQL 。。。。。
//查询慢SQL表
use mysql;
select * from slow_log;

3.2.3 查询日志

查询日志记录了所有对MySQL数据库的请求信息，无论这些请求是否得到了正确的执行。默认文件名为：主机名.log

show variables like '%general_log%';

此处和开始慢查询日志类似，不在演示

3.2.4 二进制日志（很重要的问题）

二进制日志（binary log）记录了对MySQL数据库执行更新的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身没有修改。然而，若操作本身并没有导致数据库发生变化，那么改操作可能也会写入二进制日志。

开启二进制日志

修改/etc/my.cnf文件

#开启二进制日志
log_bin=mysql-bin

重新启动MySQL (重启、重启、重启)

此时在/usr/local/mysql/data里多了两个文件

其中mysql-bin.00001即为二进制日志文件，mysql-bin.index为二进制的索引文件，不建议修改。

查询mysql二进制文件（不能用cat）

mysqlbinlog mysql-bin.000001

二进制日志主要有以下几种作用：恢复、复制、审计

其实很多MySQL用的主从复制就是基于bin.log日志文件的。

3.3套接字文件

略

3.4 pid文件

略

3.5 表结构定义文件

我的mysql数据文件的目录为/usr/local/mysql/data

当你创建了一个名称为abc的数据库时，在/usr/local/mysql/data下就会有一个abc的目录，用来表示abc数据库；

当你在abc数据库里建了张bcd表，那么在/usr/local/mysql/data/abc下就会有bcd.frm和bcd.ibd

其中bcd.frm为表结构，bcd.ibd为表数据。

3.6 InnoDB存储引擎文件

很重要。看书吧

第4章表

4.1 索引组织表

在InnoDB存储引擎表中，每张表都有主键（Primary Key）,如果在创建表时没有显示地定义主键，则InnoDB存储引擎会按照如下方式选择或者创建主键：

首先判断表中是否有非空的唯一索引（Unique NOT NULL），如果有，则该列即为主键。

如果不符合上述条件，InnoDB存储引擎自动创建一个6字节大小的指针。

当一个表中有多个非空唯一索引，InnoDB存储引擎将选择建表时的第一个定义的非空唯一索引为主键。主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

4.2 InnoDB逻辑存储结构

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

4.2.1 表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。在默认的情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有的数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放在一个表空间内。

如果启用了innodb_file_per_table的参数，需要注意的是每张表的表空间内存放的值是数据、索引和插入缓冲Bitmap页，其它类型的数据，如回滚（undo）信息、插入缓冲索引页、系统事物信息、二次写缓冲（Double write buffer）等还是存放在原来的共享表空间内。这同时也说明了一个问题：即使启用了innodb_file_per_table ，共享表空间还是会不断地增加其大小。

4.2.2 段

表空间是由各个段组成，常见的段有数据端、索引段、回滚段等。

在InnoDB存储引擎中，对段的管理都是由引擎自身完成，DBA不能也没必要对其进行控制。

4.2.3 区

区是由连续的页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。在默认情况下，InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页。

4.2.4 页

同大多数数据库一样，InnoDB 有页(Page) 的概念(也可以称为块)，页是InnoDB磁盘管理的最小单位。在InnoDB存储引擎中，默认每个页的大小为16KB。而从InnoDB 1.2.x 版本开始，可以通过参数innodb_ page_ size 将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_ page_ size, 不可以对其再次进行修改。除非通过mysqldump导人和导出操作来产生新的库。

在InnoDB存储引擎中，常见的页类型有:

●数据页(B-tree Node)

●undo页(undo Log Page)

●系统页(System Page)

●事务数据页(Transaction system Page)

●插人缓冲位图页(Insert Buffer Bitmap)

●插人缓冲空闲列表页(Insert Buffer Free List)

●未压缩的二进制大对象页(Uncompressed BLOB Page)

●压缩的二进制大对象页(compressed BLOB Page)

4.2.5 行

InnoDB存储引擎是面向行的(row-oriented), 也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放16KB / 2 -200行的记录，即7992行记录。

第5章索引与算法

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到-个合适的平衡点，这对应用程序的性能至关重要。

5.1 InnoDB存储引擎索引概述

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

B+树索引

全文索引

哈希索引

5.2 数据结构与算法

略。。。

5.3 B+树

特别重要，看书吧

5.4 B+树索引

B+树索引的本质就是B+树在数据库中的实现。但是B+树在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在2~4层，这也就是说查询某一键值的行记录时最多只需要2到4次IO。

数据库中的B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index) ，但是不管是聚集还是辅助的索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

5.4.1 聚集索引

聚集索引(clustered index)就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

换句话说：聚集索引的叶子节点存放了整行的数据。非叶子节点存放的是键值以及指向数据页的偏移量。

每张表只能拥有一个聚集索引。即主键索引。

5.4.2 辅助索引

辅助索引(secondary index)又称非聚集索引。叶子节点中除了包含键值以为，还有聚集索引键。

5.4.3 B+树索引的分裂

没看懂，太菜。。。

5.5 Cardinality值

5.5.1 什么事Carinality值

并不是所有的查询条件中出现的列都需要添加索引。

如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，适合建立索引。反例，性别列加索引，不是很可取

Carinality值获取如下图所示：

Carinality/n_rows_in_table应尽可能地接近1.如果非常小，那么用户需要考虑是否还有必要创建这个索引。

即通过Carinality值可以看某个索引是否有必要存在。

5.5.2 InnoDB存储引擎的Cardinality统计

在InnoDB存储引擎中，Cardinality 值是通过对8个叶子节点预估而得的，不是一个实际精确的值。再者，每次对Cardinality值的统计，都是通过随机取8个叶子节点得到的，这同时又暗示了另一个Cardinality现象，即每次得到的Cardinality值可能是不同的。

5.6 B+树索引的实用

5.6.1 不同应用中B+树索引的实用

无论是在OLAP还是OLTP应用中，添加索引都是有意义的，只是添加索引的列不一样，关注点不一样。

5.6.2 联合索引

即复合索引。

比如我创建了一个表student(id,name,age)

我创建了一个索引index_name_age(name,age),查询name的时候可以实用到复合索引index_name_age，查询age的时候不能用到index_name_age索引。

5.6.3 覆盖索引

覆盖索引又称索引覆盖。覆盖索引不是一种新的索引。（其实我感觉叫索引覆盖其实更形象）

比如我创建了一个表student(id,name,age)，创建了index_name(name)，我查询select name from student where name="zhangsan"; 该处就实用了覆盖索引，即我查询的数据在索引中就可以拿到。

如果select age,name from student where name="zhangsan"这么查询，那么请问index_name中会存age的值吗，不会的，那就不叫覆盖索引（索引覆盖）了。

5.6.6 Multi-Range Read优化

MRR优化有以下几个好处:

●0 MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。

●口减少缓冲池中页被替换的次数。

●0批量处理对键值的查询操作。

举例：

select * from student where name like "关系户" or score >90

此时可能查到的ID为9,19,1,2,8

因为记录是在页中，假设9和8的记录在一个页中，那么会有一种情况为9记录所在的页进入缓冲区又被移除，后来8记录所在的页又被加入缓冲区，这样相当于8和9所在页进了2次。

优化后，把ID排序，然后按照顺序查，就能保证8和9记录所在的页只需要进入一次缓冲区。

5.6.7 Index Condition Pushdown(ICP)优化

看书吧

5.8 全文检索

略。。。用ES

6 锁

6.1 什么是锁

锁是数据库系统区别于文件系统的一个关键特性。

InnoDB存储引擎是行级锁。MyISAM是表级锁。

6.2 lock与latch

lock的对象为事务，latch的对象为线程

6.3 InnoDB存储引擎中的锁

6.3.1 锁的类型

●共享锁（S Lock）:允许事务读一行数据。

●排他锁（X Lock）:允许事务删除或者更新一行数据。

●意向共享锁（IS Lock）:事务想要获得一张表中某几行的共享锁。

●意向排他锁（IX Lock）:事务想要获得一张表中某几行的排他锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如表6-4所示。

6.3.2 一致性非锁定读

很重要，建议看书

一致性非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制( multi versioning)的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。如图6-4所示。

之所以称其为锁定读，因为不需要等待访问行上X锁的释放，快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。

在事务隔离级别READ COMMITTED和REPEATABLE READ (InnoDB存储引擎的默认事务隔离级别)下，InnoDB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。这就是为什么READ COMMITTED时候能读到修改后的数据，而REPEATABLE READ不能读到修改后的数据，以为你他们读的版本不一样。

6.3.3 一致性锁定读

在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，innoDB存储引擎的SELECT操作使用的是一致性非锁定读。

但在某些情况下，用户需要显式地对数据库读取操作进行加锁以保障数据逻辑的一致性。而这要求数据库支持加锁语句。

SELECT 。。。FOR UPDATE:对读取的行加了一个X锁，其他事务不能对已锁定的行加任何锁。

SELECT。。。LOCK IN SHARE MODE:对读取的行加了一个S锁。

6.3.4 自增长与锁

在Innodb存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列。如果不是第一个列，则MySQL数据库会抛出异常。

6.4 锁的算法（贼重要）

6.4.1 行锁的3种算法

●Record Lock:单个行记录上的锁

●Gap Lock:间隙锁，锁定一个范围，但不包含记录本身。

●Next-key Lock:Gap Lock+Gap Lock ,锁定一个范围，并且锁定记录本身。

对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。

案例：

表如下

CREATE TABLE `z` (
  `a` int(11) NOT NULL DEFAULT '0',
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

微信截图_20230224162737.png

步骤3的时候，此时给主键索引5加了X锁，给辅助索引（1,3）（3,6）加了X锁；所以步骤4的时候是阻塞的。

步骤3加了两个锁：在主键索引a上加了Record Lock，在辅助索引b上加了Gap Lock

6.4.2 解决Phantom Problem(幻读问题)

Phantom Problem是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

在默认的隔离级别下，即可重复读，InnoDB存储引擎采用Next-Key Locking机制避免幻读问题。

6.5 锁问题

很重要。。。看书吧

6.6 阻塞

在默认的情况下InnoDB存储引擎不会回滚超时引发的错误异常。

6.8 锁升级

锁升级是指将当前锁的粒度降低。

假设一张表有3 000 000个数据页，每个页大约有100条记录，那么总共有300 000 000条记录。若有一个事务执行全表更新的SQL语句，则需要对所有记录加X锁。若根据每行记录产生锁对象进行加锁，并且每个锁占用10字节，则仅对锁管理就需要差不多需要3GB的内存。而InnoDB存储引擎根据页进行加锁，并采用位图方式，假设每个页存储的锁信息占用30个字节，则锁对象仅需90MB的内存。由此可见两者对于锁资源开销的差距之大。

第7章事务

7.1 认识事务

7.1.1 概述

原子性（automicity）

一致性（consistency）

隔离性（isolation）

持久性（durability）

7.1.2 分类

扁平事务（Flat Transactions）

我们平时最常用的事务，就是扁平事务。

带有保存点的扁平事务（Flat Transactions with Savepoints）

BEGIN;//开启事务
INSERT INTO student VALUES(5,"5",5);
SAVEPOINT x;//设置保存点为X
INSERT INTO student VALUES(6,"6",6);
INSERT INTO student VALUES(7,"7",7);
ROLLBACK TO x;//回滚到保存点X
INSERT INTO student VALUES(8,"8",8);
COMMIT;//只插入5和8

链事务（Chained Transactions）

嵌套事务（Nested Transactions）

分布式事务（Distributed Transactions）

7.2 事务的实现（很重要，建议看书）

redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。

redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定的版本。因此两者记录的内容不同。

redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。

7.2.1 redo

重做日志用来实现事务的持久性，其又两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file）,其实持久的。

参考：https://www.cnblogs.com/klvchen/p/10861850.html

bin log和redo log的不同：

7.2.2 undo

undo是逻辑日志。

当事务提交时，InnoDB存储引擎会做一下两件事情：

1.将nudo log 放入列表中，以供之后的purge操作

2.判断undo log所在的页是否可以重用，弱可以分配给下个事务使用。

7.2.3 purge

很重要，略。。。

用来清理undo log的。

总结

1）版本不同，可能参数名称不同

2）动手做一做，比一直看要好

3）了解了一下 explan的实用，本文没讲，但是用到了

4）搭建数据库的时候开启二进制日志，有利无害

SQL测试数据

建表语句

CREATE TABLE `bigdata` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=48344397 DEFAULT CHARSET=utf8;

生成数据的函数

CREATE DEFINER=`root`@`%` PROCEDURE `createDataInBigdata`()
BEGIN
  #Routine body goes here...
  declare i int;
  set i = 0;
  while i<5000000000 do
   INSERT into bigdata (username,password,age,create_time) VALUES(
substring(md5(rand()),1,10),
substring(md5(rand()),1,10),
FLOOR((RAND() * 100)),now());
   set i=i+1;
  end while;
END

《MySQL技术内幕：InnoDB存储引擎》笔记