数据库入门之学习《MySQL实战45讲》第11讲-第15讲笔记

《MySQL实战45讲》第11讲

使用前缀索引的优势

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

使用前缀索引的问题

• 使用前缀索引可能会增加扫描行数，这会影响到性能。
• 使用前缀索引就用不上覆盖索引对查询性能的优化了。

字符串字段创建索引的方法

• 直接创建完整索引，这样可能比较占用空间；
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

《MySQL实战45讲》第12讲

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

什么情况会引发数据库的 刷脏页（flush） 过程呢

• InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log留出空间可以继续写。

• 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

• MySQL 认为系统“空闲”的时候。

• MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
• 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

《MySQL实战45讲》第13讲

数据删除流程

若我们要删掉 一条记录，InnoDB 引擎只会把这个记录标记为删除，以后可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

InnoDB 的数据是按页存储的， 那么如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。

实际上，delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

重建表

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

重建表的流程

简单描述一下引入了 Online DDL 之后，重建表的流程

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
5. 用临时文件替换表 A 的数据文件。

由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。

《MySQL实战45讲》第14讲

count(*) 的实现方式

在不同的 MySQL 引擎中，count(*) 有不同的实现方式

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高
• 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数

count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差别

下面的讨论还是基于 InnoDB 引擎的。

首先你要弄清楚 count() 的语义。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

• 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• 对于 count(字段) 来说： 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是null 才累加。
• 但是 count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值，肯定不是 null，按行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*) 。

所以我建议你，尽量使用 count(*)。

《MySQL实战45讲》第15讲

与 redo log 相比，binlog 有着无法替代的功能

1. 归档。redo log 是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log 也就起不到归档的作用。
2. MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是 binlog 复制。
3. 还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费 MySQL 的 binlog 来更新自己的数据。关掉 binlog的话，这些下游系统就没法输入了。

redo log buffer 是什么？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没 commit 的时候就直接写到 redo log 文件里。

redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个 insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。

但是，真正把日志写到 redo log 文件，是在执行 commit 语句的时候做的。