(第三回合)回龙观大叔狂磕mysql

嘿? 同学, 我们又见面了.

这次狂磕mysql系列进入了第三回合, 回龙观大叔清明节约了我去体育公园打网球, 休息之余问了下他最近的情况, 说这两天对人生有了更多的思考, 听完后感觉很惊悚, 之后我再抽机会分享下他那悲观的心态.

今天还是分享下回龙观大叔的学习心得吧

大叔: 小子你不讲武德, 有些知识我还没学到呢...

数据库目录

还记得上次面试官问我 MyISAM 和 InnoDB 区别时, 我得意答到:有个很大区别可能大家没怎么注意, 就是他们的目录结构不一样

面试官肯定的点了点头, 我继续说: MyISAM表会有三个文件 结构、数据、索引, 而InnoDB只有一个目录.

面试官让我出门右转乘电梯.

搞清楚 MyISAM 目录结构

首先查看存储地址

SHOW VARIABLES LIKE 'datadir';

+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/mysql/ |
+---------------+-----------------------+

每次创建数据库都会创建对应的库目录, 此目录下存放着表的相关文件

物理存储结构:

• 表名.frm 描述表结构的文件
• 表名.MYD 具体的插入数据
• 表名.MYI 索引文件

搞清楚 InnoDB 目录结构

我们通过上一回合知道了 InnoDB是通过 页 为基本单位来管理存储空间的, 一个 页 大概才16KB, 我们一个数据表动辄都十几G二十几G, 而我们怎么管理这些零碎的16KB页呢?

为了更好的管理这些页，InnoDB有一个表空间或者文件空间（英文名：table space或者file space）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里

空间也分为 系统表空间、独立表空间和其他类型的表空间, 例如undo表空间、临时表空间等, 我们目前只关注具体表数据存储, 所以这里先只关注独立表空间.

创建表后会在对应数据库目录下存在 表名.frm 和 表名.ibd 两个文件.

物理存储结构:

• 表名.frm
• 表名.ibd

我们可以看到两种存储引擎的设计思想的差异, MyISAM 是面向具体表实现的, 他区分了 索引、数据、结构, 划分为三个基本文件, 而InnoDB 是面向页存储的, 他使用页的存储模型将数据与所有索引放在一起.

对于InnoDB的独立表空间来说，每个表的数据都会被存储到一个与表名同名的.ibd文件中；对于MyISAM存储引擎来说，数据和索引会分别存放到与表同名的.MYD和.MYI文件中。这些文件会随着表中记录的增加而增大，它们的大小受限于文件系统支持的最大文件大小

哎～看到这里, 终于知道面试官为什么对我不满意了...

InnoDB 存储逻辑结构

上面说到了目录结构, 我们一杆子桶到底, 看看 InnoDB 逻辑上是怎么管理的呢?

区(extent)

我们知道一个页大概有 16KB, 连续的64个页就是一个区, 也就是说一个区默认占用1MB空间大小.

而每256个区又被划分成一组.

image.png

段(segment)

存放叶子节点的区的集合就算是一个段（segment），存放非叶子节点的区的集合也算是一个段.

默认情况下一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么？

碎片(fragment)

开始我们数据记录小, 段是从某个碎片区以单个页面为单位来分配存储空间的, 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间.

关系图如下:

image.png

InnoDB 索引计划

索引

image.png

查询计划 EXPLAIN

对于我们的sql运行效果怎么样, EXPLAIN 是我们必须学会的一个命令.

EXPLAIN 各个列作用如下:

下面我们挑几个比较重要的字段:

1. type

type 类型有 system, const, eq_ref,ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL, 基本跟我们上图索引查询类型是一样的, 只是更加细化了一些和增加了子查询类型.

2. possible_keys

possible_keys 表示查询优化器认为可能用到索引列, 这是一个权重计算过程, key 表示在查询实际使用的索引, 如果我们 possible_keys 过多而使用到的 key 少, 证明查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引.

3. key_len

key_len 表示最后选择的索引 key 的长度, key的长度越长占用内存空间越大, 对于我们扫面页来说会加慢查询速度, 所以 mysql 默认情况下单个列的索引不能超过767个字符(utf-8)

我们知道InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录(否则就退化链表了)。所以一个记录最多不能超过8k

4. ref

ref 表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行对应的大概范围。依次从最优到最差的分别为：system>const>eq_ref>ref>range>index>All, 对于一个百万级以上表来说, 我们需要保证基本的 index 查询.

5. rows

rows 表示查询优化器计算需要扫描的数据行数, 这个参考意义很大, 对于索引建立错误或数据分布不稀疏情况, 可通过此字段观察.

6. filtered

filtered 表示查询预测的分数, 表示返回结果的行数占需读取行数的百分比, filtered 的值越大越好.

7. Extra

Extra 额外建议信息, 下面摘抄了几段

No matching min/max row 当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息

Using index 查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息

Using index condition 虽然出现了索引列，但却不能使用到索引

optimizer trace

在mysql 5.6版本之前我们只能根据 EXPLAIN 查看sql执行查询计划, 5.6之后版本新推出 optimizer trace 功能, 我们看看怎么使用吧.

#查看是否开启, 默认关闭
SHOW VARIABLES LIKE 'optimizer_trace';

#开启
SET optimizer_trace="enabled=on";

开启后我们不需要做任何操作, 查询语句执行完成后，就可以到information_schema数据库下的OPTIMIZER_TRACE表中查看完整的优化过程

OPTIMIZER_TRACE有4个列，分别是： QUERY：表示我们的查询语句。 TRACE：表示优化过程的JSON格式文本。 MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。 INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1，我们暂时不关心这个字段的值

InnoDB Buffer Pool 缓存池

我们知道内存读写速度要比磁盘快得多, 利用中间内存替换磁盘读写速度也是我们业务开发常用的方式, 作为强大的数据库存储引擎, 自然也少不了内存的利用.

我们看看 InnoDB 是怎么做的呢?

InnoDB 存储引擎是基于磁盘存储的，对数据记录的管理是以页为单位的。由于CPU与磁盘之间在速度上的巨大差距，那么缓冲池就应运而生了，它的存在提高数据库的整体性能.

在MySQL服务器启动的时候就向操作系统申请了一片连续的内存, 学名叫做Buffer Pool.

它主要是干什么呢?

Buffer Pool存储的是页的数据, 在数据库中读取页的操作，首先将从磁盘读取的页存放在缓冲池中。下一次再读取数据页时，先判断该数据页是否在缓冲池中，如果在缓冲池中，会直接读取该数据页，否则读取磁盘上的页

对数据库中页的修改操作，会首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里有一点需要注意，页从缓冲池刷新到磁盘的操作并不是在每次页发生更新时触发的，而是通过 Checkpoint 机制刷新到磁盘

缓冲池的大小直接影响了数据库的整体性能。随着内存技术的成熟，内存成本也在不断下降，因此强烈建议在数据库专用服务器上，将尽可能多的物理内存分配给缓冲池

Buffer Pool默认只有128M, 我们可以根据机器内存情况设置 innodb_buffer_pool_size 参数, 一般情况推荐设置 innodb-buffer-pool-size 为服务器总可用内存的75%.

脏页

对于缓存的页, 修改后还未同步到磁盘中我们称为脏页. 但是我们的 Buffer Pool 那么大, Checkpoint 是怎么同步的呢?

所有修改的页会单独放到一个链表中, 当我们同步磁盘时只需要同步这个 flush 链表就可以了.

image.png

LRU链表分为young和old两个区域，可以通过innodb_old_blocks_pct来调节old区域所占的比例。首次从磁盘上加载到Buffer Pool的页会被放到old区域的头部，在innodb_old_blocks_time间隔时间内访问该页不会把它移动到young区域头部。在Buffer Pool没有可用的空闲缓存页时，会首先淘汰掉old区域的一些页

结束语

此文主要讲了 数据库目录结构、Innodb 存储的逻辑结构、索引和 InnoDB Buffer Pool 相关科普知识.

因为时间关系, 后面部分是我自己摘摘抄抄拼起来的.

期待大叔重新调整好心情, 带大家狂磕事务篇.