MySQL实战45讲（10--15）-笔记
11 | 怎么给字符串字段加索引？
维护一个支持邮箱登录的系统，用户表是这么定义的：
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
登录操作，有类似这样的语句
mysql> select f1, f2 from SUser where email='xxx';
如果 email 这个字段上没有索引，那么这个语句就只能做全表扫描。
同时，MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。
创建前缀索引
比如，这两个在 email 字段上创建索引的语句：
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));
前缀索引占用的空间会更小，这同时带来的损失是，可能会增加额外的记录扫描次数。
分析语句的执行流程：
select id,name,email from SUser where email='zhangssxyz@xxx.com';
如果使用的是 index1（即 email 整个字符串的索引结构），这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
如果使用的是 index2（即 email(6) 索引结构），可能要扫描多行。
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
更好的索引长度
语句：
mysql> select count(distinct email) as L from SUser;
可以一同使用 DISTINCT 和 COUNT 关键词，来计算非重复结果的数目。
mysql> select
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例。
前缀索引对覆盖索引的影响
语句：
select id,email from SUser where email='zhangssxyz@xxx.com';
select id,name,email from SUser where email='zhangssxyz@xxx.com';
如果使用 index1（即 email 整个字符串的索引结构）的话，可以利用覆盖索引，从index1 查到结果后直接就返回了，不需要回到 ID 索引再去查一次。
而如果使用 index2（即email(6) 索引结构）的话，就不得不回到 ID 索引再去判断 email 字段的值。
使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
其他方式
第一种方式是使用倒序存储。
如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：
mysql> select field_list from t where id_card = reverse('input_id_card_string');
第二种方式是使用 hash 字段。
你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
使用倒序存储和使用 hash 字段这两种方法的异同点：
- 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，
而 hash字段方法需要增加一个字段。
当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
- 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，
而hash 字段的方式需要额外调用一次 crc32() 函数。
如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
- 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。
因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。
而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。
小结
- 直接创建完整索引，这样可能比较占用空间；
- 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
- 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
- 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不
支持范围扫描。
12 | 为什么我的MySQL会“抖”一下？
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。
例如这几种场景：
- 对应的就是 InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把checkpoint 往前推进，redo log 留出空间可以继续写。
-
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
-
MySQL 认为系统“空闲”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。
-
MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
其中第三第四种情况不需要多考虑，
第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。
因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。
第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。
InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
- 还没有使用的；
- 使用了并且是干净页；
- 使用了并且是脏页。
InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。
所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：
- 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
- 日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。
InnoDB 刷脏页的控制策略
要知道 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页可以刷多快。
这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。
这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试。
InnoDB 的刷盘速度就是要参考这两个因素：
- 一个是脏页比例
- 一个是 redo log 写盘速度。
现在你知道了，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。
所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。
要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。
其中，脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total
得到的。
接下来，我们再看一个有趣的策略。
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而
MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁
边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻
辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会
被放到一起刷。
在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上
述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机
IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。
而如果使用的是 SSD 这类 IOPS 比较高的设备的话，我就建议你把 innodb_flush_neighbors
的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷
脏页操作，减少 SQL 语句响应时间。
在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。
13 | 为什么表数据删掉一半，表文件大小不变？
一个 InnoDB 表包含两部分，即：表结构定义和数据。
在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。
参数 innodb_file_per_table
表数据既可以存在共享表空间里，也可以是单独的文件。
这个行为是由参数 innodb_file_per_table 控制的：
-
这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
-
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。
我建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。
因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。
而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。
数据删除流程
在B+树的结构中就算是删除了，它还是没有释放这个空间，而是可能会复用这个位置，所以磁盘的文件大小不会缩小。
（就算是一页的数据给删除了，那也只是说明，这一页可以片被复用了）
但是，数据页的复用跟记录的复用是不同的。
delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。
也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。
实际上，不止是删除数据会造成空洞，插入数据也会。
重建表
alter table A engine=InnoDB 命令来重建表，重建之后更紧凑，数据页的利用率也更高。
在 MySQL 5.5 版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表 B 不需要你自己创建，
MySQL 会自动完成转存数据、交换表名、删除旧表的操作。
但是这个 DDL 不是 Online 的。
而在MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件
5. 用临时文件替换表 A 的数据文件。
简单来说就是：+ row log，把DDL过程中的操作弄进去了。
14 | count(*)这么慢，我该怎么办？
count(*) 的实现方式
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候会直接返回这个数，效率很高；
- 而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，
如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。
那为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？
这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，
InnoDB表“应该返回多少行”也是不确定的。
count(*) 操作的时候还是做了优化的：
在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
- MyISAM 表虽然 count() 很快，但是不支持事务；
- show table status 命令虽然返回很快，但是不准确；（官方文档说误差可能达到 40% 到 50%）
- InnoDB 表直接 count() 会遍历全表，虽然结果准确，但会导致性能问题。
用缓存系统保存计数
你可以用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1。
问题：可能会丢失更新。
还有：将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使 Redis 正常工作，这个值还是逻辑上不精确的。
在数据库保存计数
如果我们把这个计数直接放到数据库里单独的一张计数表 C 中，又会怎么样呢？
- 首先，这解决了崩溃丢失的问题，InnoDB 是支持崩溃恢复不丢数据的。
- 利用“事务”这个特性，解决计数不准确的问题。
不同的 count 用法
count() 的语义：
count() 是一个聚合函数，对于返回的结果集，一行行判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。
分析性能差别的时候，你可以记住这么几个原则：
1. server 层要什么就给什么；
2. InnoDB 只给必要的值；
3. 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。
-
对于 count(主键 id) 来说，
InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
-
对于 count(1) 来说，
InnoDB 引擎遍历整张表，但不取值server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
-
对于 count(字段) 来说：
如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
-
但是 count() 是例外，
并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。
小结
把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，
是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。
而把计数值也放在MySQL 中，就解决了一致性视图的问题。
InnoDB 引擎支持事务，我们利用好事务的原子性和隔离性，就可以简化在业务开发时的逻辑。这也是 InnoDB 引擎备受青睐的原因之一。
15 | 答疑文章（一）：日志和索引相关问题
这一章没有我记载，因为自己还有些不明白，抱歉。
