《MySQL实战45讲》第11讲
使用前缀索引的优势
使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。
使用前缀索引的问题
- 使用前缀索引可能会增加扫描行数,这会影响到性能。
- 使用前缀索引就用不上覆盖索引对查询性能的优化了。
字符串字段创建索引的方法
- 直接创建完整索引,这样可能比较占用空间;
- 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
- 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
- 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。
《MySQL实战45讲》第12讲
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。
什么情况会引发数据库的 刷脏页(flush) 过程呢
- InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作,把 checkpoint 往前推进,redo log留出空间可以继续写。
![](https://i-blog.csdnimg.cn/blog_migrate/6a2d35d18570d9cef795dd3b203700de.png)
-
系统内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘。
-
MySQL 认为系统“空闲”的时候。
-
MySQL 正常关闭的情况。这时候,MySQL 会把内存的脏页都 flush 到磁盘上,这样下次 MySQL启动的时候,就可以直接从磁盘上读数据,启动速度会很快。
刷脏页虽然是常态,但是出现以下这两种情况,都是会明显影响性能的
- 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长;
- 日志写满,更新全部堵住,写性能跌为 0,这种情况对敏感业务来说,是不能接受的。
《MySQL实战45讲》第13讲
数据删除流程
若我们要删掉 一条记录,InnoDB 引擎只会把这个记录标记为删除,以后可能会复用这个位置。但是,磁盘文件的大小并不会缩小。
InnoDB 的数据是按页存储的, 那么如果我们删掉了一个数据页上的所有记录,会怎么样?答案是,整个数据页就可以被复用了。
实际上,delete 命令其实只是把记录的位置,或者数据页标记为了“可复用”,但磁盘文件的大小是不会变的。也就是说,通过 delete 命令是不能回收表空间的。这些可以复用,而没有被使用的空间,看起来就像是“空洞”。
重建表
经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。而重建表,就可以达到这样的目的。
重建表的流程
简单描述一下引入了 Online DDL 之后,重建表的流程
- 建立一个临时文件,扫描表 A 主键的所有数据页;
- 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
- 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中;
- 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件;
- 用临时文件替换表 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 有着无法替代的功能
- 归档。redo log 是循环写,写到末尾是要回到开头继续写的。这样历史日志没法保留,redo log 也就起不到归档的作用。
- MySQL 系统依赖于 binlog。binlog 作为 MySQL 一开始就有的功能,被用在了很多地方。其中,MySQL系统高可用的基础,就是 binlog 复制。
- 还有很多公司有异构系统(比如一些数据分析系统),这些系统就靠消费 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 语句的时候做的。