读书笔记-MySQL 是怎样运行的：从根儿上理解 MySQL

mysqld_safe

mysqld_safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启
它。另外，使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我
们找出发生错误的原因

mysql.server

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了，就像这样：
mysql.server start

系统变量的作用范围

GLOBAL：全局变量，影响服务器的整体操作。
SESSION：会话变量，影响某个客户端连接的操作。
eg: 在本次会话有效 SET SESSION default_storage_engine = MyISAM;
SHOW VARIABLES 默认显示SESSION的值,在每个会话初始化的时候都会取GLOBAL的值为默认初始化值

如果某个客户端改变了某个系统变量在 GLOBAL 作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为 SESSION 的值，只会影响后续连入的客户端在作用范围为 SESSION 的值

字符集

utf8mb3：阉割过的utf8字符集，只使用 1 ～ 3 个字节表示字符。
utf8mb4：正宗的utf8字符集，使用 1 ～ 4 个字节表示字符。
SHOW CHARSET; 查看字符集
SHOW COLLATION LIKE 'utf8\_%'; 查看比较规则
utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

MySQL有 4 个级别的字符集和比较规则，分别是：

  服务器级别
  数据库级别
  表级别
  列级别

character_set_server 服务器级别的字符集
collation_server 服务器级别的比较规则

character_set_database 当前数据库的字符集
collation_database 当前数据库的比较规则
表级别的字符集
mysql> CREATE TABLE t(
-> col VARCHAR(10)
-> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.03 sec)
如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则
列级别的字符集
ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
    只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

InnoDB记录结构

InnoDB页

InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位， InnoDB 中页的大小一般为 16 KB 。
也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB 内容刷新到磁盘中

InnoDB行

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式 。
设计InnoDB存储引擎的大叔们到现在为止设计了 4 种不同类型的 行格式 ，分别是Compact、Redundant、Dynamic和Compressed行格式，
随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的
指定行格式

CREATE TABLE record_format_demo (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;

COMPACT 行格式


记录的额外信息
这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为 3 类，分别是 变长字段长度列表 、NULL 值列表 和 记录头信息 ，我们分别看一下。
变长字段长度列表 变长字段长度列表
我们知道MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，我们也可以把拥有这些数据类型的列称为 变长字段 ，变长字段中存储多少字节的数据是不固定
的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于把MySQL服务器搞懵，所以这些变长字段占用的存储空间分为两部分：

  1. 真正的数据内容
  2. 占用的字节数
    在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放，我们再次强调一遍，
    是逆序存放！

NULL值

  1. 首先统计表中允许存储NULL的列有哪些。
  2. 如果表中没有允许存储 NULL 的列，则 NULL 值列表 也不存在了

表结构:

记录头信息


VARCHAR(M) 最多能存储的数据

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字
节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型
的列，其实需要占用 3 部分存储空间：
真实数据
真实数据占用字节的长度
NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间

  • 如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多
    需要2个字节，那在该字符集下，M的最大取值就是32766（也就是： 65532/2 ），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就
    是21844，就是说最多能存储21844（也就是： 65532/3 ）个字符
    上述所言在列的值允许为 NULL 的情况下， gbk 字符集下 M 的最大取值就是 32766 ， utf8 字符集下 M 的最大取值就是 21844 ，这都是在表中只有一个字段的情况下说的，一定要记住一个行
    中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节
    在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在 记录的真实数据 处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据 处用 20 个字节存储指向这些
    页的地址（当然这 20 个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页，如图所示：

InnoDB 数据页结构



InnoDB数据页的 7 个组成部分

各个数据页可以组成一个 双向链表 ，而每个数据页中的记录会按照主键值从小到大的顺序组成一个 单向链表 ，每个数据
页都会为存储在它里边儿的记录生成一个 页目录 ，在通过主键查找某条记录的时候可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速
找到指定的记录

B+树索引


最终结果如下:

B+树索引的使用

  1. 每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有 用户记录 都存储在B+树的叶子节点，所有 目录项记录 都存储在内节点。
  2. InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立 聚簇索引 ，聚簇索引的叶子节点包含完整的用户记录。
  3. 我们可以为自己感兴趣的列建立 二级索引 ，二级索引 的叶子节点包含的用户记录由 索引列 + 主键 组成，所以如果想通过 二级索引 来查找完整的用户记录的话，需要通
    过 回表 操作，也就是在通过 二级索引 找到主键值之后再到 聚簇索引 中查找完整的用户记录。
  4. B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到
    大的顺序而形成了一个单链表。如果是 联合索引 的话，则页面和记录先按照 联合索引 前边的列排序，如果该列值相同，再按照 联合索引 后边的列排序。
  5. 通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快。

索引的代价

  1. 空间上的代价
    这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由
    许多数据页组成，那可是很大的一片存储空间呢。
  2. 时间上的代价
    每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链
    表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而
    增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排
    序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这还能不给性能拖后腿么

那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？

这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计
算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索
引 + 回表 的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用 二级
索引 + 回表 的方式进行查询，因为回表的记录越少，性能提升就越高，

为了避免主键突然大突然小,推荐让主键具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入

因为如果主键突然大突然小的话,会导致页经常分裂,影响性能

mysql数据目录

  • mysql分为安装目录和数据目录,查找mysql数据目录: SHOW VARIABLES LIKE 'datadir';

  • CREATE DATABASE:每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹
    1.在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹）。
    2.在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥。

表空间

1.系统表空间
这个所谓的 系统表空间 可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在 数据目录 下创建一个名
为ibdata1（在你的数据目录下找找看有木有）、大小为12M的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。怎
么才12M？这么点儿还没插多少数据就用完了，哈哈，那是因为这个文件是所谓的 自扩展文件 ，也就是当不够用的时候它会自己增加文件大小～

  • 独立表空间 (file-per-table tablespace)
    在 MySQL5.6.6 以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立
    表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对
    应的子目录下创建一个表示该 独立表空间 的文件，文件名和表名相同，只不过添加了一个.ibd的扩展名而已，所以完整的文件名称长这样：表名 .ibd
    eg: demo库下所有表的表空间,一个表对应一个表空间

MyISAM如何存储表结构

在MyISAM中的索引全部都是 二级索引 ，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储
数据文件和索引文件。而且和InnoDB不同的是，MyISAM并没有什么所谓的 表空间 一说，表数据都存放到对应的数据库子目录
下。假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的xiaohaizi目录下会为test表创建这三个文件：
test.frm
test.MYD
test.MYI

视图:视图只会存结构,不会存数据

  • 文件名: MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+ 编码值 的形式作为文件名
    eg: a? 对应 a@003f

InnoDB 的表空间

  • 表空间: 对于系统表空间来说，对应着文件系统中一个或多个实际文件；对于每个独立表空间来说，对应着文件系统中一个名为 表名 .ibd的实际文件
    可以把表空间想象成被切分为许许多多个 页 的池子，当我们想为某个表插入一条记录的时候，就从池子中捞出一个对应的页来把数据写进去。

  • 区: 64个页就组成一个区,256个区就组成一组,每个组的前3个页会记录特定的信息

  • 段: 一个区域连续的64个页组成一个段,段是为了分配连续的空间用的,这样可以避免过多的随机IO

  • 一个区就是在物理位置上连续的 64 个页。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区 为单位分配,可以避免过多的随机Io

  • B+树根据叶子节点和非叶子节点分为:一个叶子节点段，一个非叶子节点段

  • 碎片区的概念，也就是在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，
    比如有些页用于段 A ，有些页用于段 B ，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：
    1.在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
    2.当某个段已经占用了 32 个碎片区页面之后，就会以完整的区为单位来分配存储空间。

  • 区的分类
    空闲的区：现在还没有用到这个区中的任何页面。
    有剩余空间的碎片区：表示碎片区中还有可用的页面。
    没有剩余空间的碎片区：表示碎片区中的所有页面都被使用，没有空闲页面。
    附属于某个段的区。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外 InnoDB 还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位

  • 系统表空间: 系统表空间与独立表空间的一个非常明显的不同之处就是在表空间开头有许多记录整个系统属性的页面

InnoDB 数据字典


其中SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS这四个表尤其重要，称之为基本系统表,这些内部表不能直接访问

  • information_schema 系统数据库
    在information_schema数据库中的这些以INNODB_SYS开头的表并不是真正的内部系统表（内部系统表就是我们上边唠叨的以SYS开头的那些表），
    而是在存储引擎启动时读取这些以SYS开头的系统表，然后填充到这些以INNODB_SYS开头的表中。

单表访问方法

  • MySQL Server有一个称为 查询优化器 的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，
    优化的结果就是生成一个所谓的 执行计划 ，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的，
    最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
  • 访问类型
样例表
CREATE TABLE single_table (
	id INT NOT NULL AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	KEY idx_key1 (key1),
	UNIQUE KEY idx_key2 (key2),
	KEY idx_key3 (key3),
	KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
  1. const ** 常数级别,消耗忽略不计,eg: SELECT * FROM single_table WHERE id = 1105017;


    只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效,走的是const
    2.     ref** 比常数差一些 eg: SELECT * FROM single_table WHERE key1 = 'abc';

  2. ref_or_null 如果还想把null的值找出来
  3. range
    SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

  4. index
    SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
    因为查询的三个值和where条件都在idx_key_part索引中,所以遍历的范围就可以缩小到从这个索引里面找值,由于二级索引记录比聚簇索记录小的多,而且查询的值又在索引内,不用回表
    所以直接遍历该索引,这种直接遍历二级索引的执行方式叫做index
  5. all
  • 有的搜索条件无法使用索引的情况
    SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';
    包含了索引字段和非索引字段,该sql能利用的索引只有key2,所以使用二级索引定位阶段用不到common_field字段,该字段在回表的时候才会被用到
    优化器将用不到索引的搜索条件替换为 TRUE
    SELECT * FROM single_table WHERE key2 > 100 AND TRUE;
    简化后就是:SELECT * FROM single_table WHERE key2 > 100; 获取的范围就是(100, +∞)
    如果是or的情况:
    SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';
    SELECT * FROM single_table WHERE key2 > 100 OR TRUE;
    接着化简：
    SELECT * FROM single_table WHERE TRUE;
    所以用or后会直接走全表扫描
  • 复杂搜索条件下找出范围匹配的区间

有的查询的搜索条件可能特别复杂，光是找出范围匹配的各个区间就挺烦的，比方说下边这个：
SELECT * FROM single_table WHERE
(key1 > 'xyz' AND key2 = 748 ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;
我滴个神，这个搜索条件真是绝了，不过大家不要被复杂的表象迷住了双眼，按着下边这个套路分析一下：
首先查看WHERE子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引。
这个查询的搜索条件涉及到了key1、key2、common_field这 3 个列，然后key1列有普通的二级索引idx_key1，key2列有唯一二级索引idx_key2。
对于那些可能用到的索引，分析它们的范围区间。
假设我们使用idx_key1执行查询
我们需要把那些用不到该索引的搜索条件暂时移除掉，移除方法也简单，直接把它们替换为TRUE就好了。上边的查询中除了
有关key2和common_field列不能使用到idx_key1索引外，key1 LIKE '%suf'也使用不到索引，所以把这些搜索条件替换
为TRUE之后的样子就是这样：
(key1 > 'xyz' AND TRUE ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))
化简一下上边的搜索条件就是下边这样：
(key1 > 'xyz') OR
(key1 < 'abc' AND key1 > 'lmn') OR
(key1 > 'zzz')
替换掉永远为TRUE或FALSE的条件
因为符合key1 < 'abc' AND key1 > 'lmn'永远为FALSE，所以上边的搜索条件可以被写成这样：
(key1 > 'xyz') OR (key1 > 'zzz')
继续化简区间
key1 > 'xyz'和key1 > 'zzz'之间使用OR操作符连接起来的，意味着要取并集，所以最终的结果化简的到的区间就是：key1

xyz。也就是说：上边那个有一坨搜索条件的查询语句如果使用 idx_key1 索引执行查询的话，需要把满足key1 > xyz的二级
索引记录都取出来，然后拿着这些记录的 id 再进行回表，得到完整的用户记录之后再使用其他的搜索条件进行过滤。
假设我们使用idx_key2执行查询
我们需要把那些用不到该索引的搜索条件暂时使用TRUE条件替换掉，其中有关key1和common_field的搜索条件都需要被替换
掉，替换结果就是：
(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))
哎呀呀，key2 < 8000 OR TRUE的结果肯定是TRUE呀，也就是说化简之后的搜索条件成这样了：
key2 = 748 OR TRUE
这个化简之后的结果就更简单了：
TRUE
这个结果也就意味着如果我们要使用idx_key2索引执行查询语句的话，需要扫描idx_key2二级索引的所有记录，然后再回
表，这不是得不偿失么，所以这种情况下不会使用idx_key2索引的。

连接的原理

  • 在内连接中 内连接中的 WHERE 子句和 ON 子句是等价的
  • 在外连接中,on条件不符合的驱动表数据也会提取出来
  • 内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集
以下都是内连接的写法
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
  • 对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果,左外连接和右外连接的驱动表和被驱动表不能轻易互换
  • 驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数

扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足。现实生活中的表可不像t1、t2这种只有 3 条记录，成千上万条记录都
是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘上，
等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉。我们前边又说过，采用 嵌套循环连接 算法的两表连接过程中，被驱动表可是要被访问好
多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：
尽量减少访问被驱动表的次数

当被驱动表中的数据非常多时，每次访问被驱动表，被驱动表的记录会被加载到内存中，在内存中的每一条记录只会和驱动表结果集的一条记录做匹配，之后就会被从
内存中清除掉。然后再从驱动表结果集中拿出另一条记录，再一次把被驱动表的记录加载到内存中一遍，周而复始，驱动表结果集中有多少条记录，就得把被驱动表从
磁盘上加载到内存中多少次。所以我们可不可以在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载
被驱动表的代价了。所以设计MySQL的大叔提出了一个join buffer的概念，join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的
记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成
的，所以这样可以显著减少被驱动表的I/O代价。

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。设计MySQL的大叔把这种加入了join
buffer的嵌套循环连接算法称之为 基于块的嵌套连接 （ Block Nested-Loop Join ）算法。
这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144 字节 （也就是256KB），最小可以设置为128 字节 。当然，对于
优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连
接查询进行优化。
另外需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最
好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录呢哈。

  • 右（外）连接和左（外）连接其实只在驱动表的选取方式上是不同的，其余方面都是一样的，所以优化器会首先把右（外）连接查询转换成左（外）连接查询。

基于规则的优化

  • 凡是不符合 WHERE 子句中条件的记录都不会参与连接。
    只要我们在搜索条件中指定关于被驱动表相关列的值不为NULL，那么外连接中在被驱动表中找不到符合ON子句条件的驱动表记录也就被排除出最后的结果集了，
    也就是说：在这种情况下：外连接和内连接也就没有什么区别了
    eg:SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2 WHERE t2.n2 IS NOT NULL; 这种情况和内连接没有什么区别
  • 子查询:
    1.不相关子查询: 子查询可以单独运行出结果，而不依赖于外层查询的值
    2.相关子查询: 子查询的执行需要依赖于外层查询的值

Explain详解

id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

  • table: EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的table 列代表着该表的表名e
    eg: EXPLAIN SELECT * FROM s1 INNER JOIN s2;
  • id: 在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表
    查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。
    eg:EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
    这里本应该会出现两个id不同的sql语句,但是结果是两个id相同的,说明是优化器优化成用连接查询

    EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
    临时表对结果进行去重
  • select_type
  1. SIMPLE: 查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型 eg: EXPLAIN SELECT * FROM s1; 连接查询: EXPLAIN SELECT * FROM s1 INNER JOIN s2;
  2. PRIMARY: 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY
    eg: EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
  3. UNION: 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION
  4. UNION RESULT: MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT
  5. SUBQUERY: 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键
    字代表的那个查询的select_type就是SUBQUERY
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

    由于 select_type 为 SUBQUERY 的子查询由于会被物化，所以只需要执行一遍。
  6. DEPENDENT SUBQUERY: 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

    select_type 为 DEPENDENT SUBQUERY 的查询可能会被执行多次
  7. DEPENDENT UNION: 在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
  8. DERIVED: 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED
    EXPLAIN SELECT * FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

  • partitions 一般为NULL

  • possible_keys 和 key: possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些
    possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引

  • key_len
    EXPLAIN SELECT * FROM single_table WHERE key1 = 'a';

    由于key1列的类型是VARCHAR(100)，所以该列实际最多占用的存储空间就是300字节，又因为该列允许存储NULL值，所以key_len需要加1，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是303

  • ref
    当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东东是个
    啥，比如只是一个常数或者是某个列。大家看下边这个查询：
    EXPLAIN SELECT * FROM single_table WHERE key1 = 'a';

    可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点：
    EXPLAIN SELECT * FROM single_table s1 INNER JOIN single_table s2 ON s1.id = s2.id;

    有的时候与索引列进行等值匹配的对象是一个函数，比方说下边这个查询：
    EXPLAIN SELECT * FROM single_table s1 INNER JOIN single_table s2 ON s2.key1 = UPPER(s1.key1);

  • rows
    如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。
    EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

  • filtered

    1. 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
    2. 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
      该参数一般用在多表连接时作性能参考
      EXPLAIN SELECT * FROM single_table s1 INNER JOIN single_table s2 ON s1.key1 = s2.key1 WHERE s1.common_field = '9999';

    查询优化器打算把s1当作驱动表，s2当作被驱动表。s1的执行计划为91396条 filtered列为10.00
    这意味着驱动表s1的扇出值就是91396 ×10.00% = 9139.6 这说明还要对被驱动表执行大约9139次查询。

  • Extra
    Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句

    1. No tables used 没有表

    2. Impossible WHERE where条件永远为false

    3. No matching min/max row 当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时

    4. Using index 当我们的查询列表以及搜索条件中只包含属于某个索引的列，不需要回表操作

    5. Using index condition where条件部分使用了索引eg: EXPLAIN SELECT * FROM single_table WHERE key1 > 'z' AND key1 LIKE '%a';

    6. Using where: 当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

    7. Using join buffer 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法 ，比如下
      边这个查询语句:
      EXPLAIN SELECT * FROM single_table s1 INNER JOIN single_table s2 ON s1.common_field = s2.common_field;

    8. Using filesort 很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序
      如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用 文件排序 的执行方式改为使用索引进行排序。

    9. Using temporary
      在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来
      完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示
      eg: EXPLAIN SELECT DISTINCT common_field FROM single_table s1;

      执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以我们最好能使用索引来替代掉使用临时表，

optimizer trace 表查看优化器生成执行计划的整个过程

  • SHOW VARIABLES LIKE 'optimizer_trace';
  • 打开开关查看详细优化器执行计划 SET optimizer_trace="enabled=on";
    然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程
  • 使用完后关闭

InnoDB 的 Buffer Pool

  • 即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就
    可以进行读写访问了，在进行完读写访问之后并不着急把该页对应的内存空间释放掉，而是将其 缓存 起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了
  • 查看缓冲池大小 SHOW VARIABLES LIKE '%innodb_buffer_pool_size%'
  • free 链表,数据改动了,就将该页移动到flush 链表
  • LRU 链表:
    1. 如果该页不在Buffer Pool中，在把该页从磁盘加载到Buffer Pool中的缓存页时，就把该缓存页对应的 控制块 作为节点塞到链表的头部。
    2. 如果该页已经缓存在Buffer Pool中，则直接把该页对应的 控制块 移动到LRU 链表 的头部
      **#### **Redo日志
  • redo日志本质上只是记录了一下事务对数据库做了哪些修改

对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来

事务

  • 事务隔离级别: 脏写 > 脏读 > 不可重复读 > 幻读

  • 能不能在两个事务中交叉更新同一条记录呢？哈哈，这不就是一个事务修改了另一个未提交事务修改过的数据，沦为了脏写了么？ InnoDB 使用锁来
    保证不会有脏写情况的发生，也就是在第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了，把锁释
    放之后才可以继续更新。

posted @ 2020-12-17 14:48  余***龙  阅读(943)  评论(0)  编辑  收藏  举报