极客时间 MySQL实战学习笔记 9-15讲

No.9 普通索引和唯一索引，应该怎么选择？

查询过程

其实查询过程两者的时间差距是微乎其微的，普通索引要比唯一索引多一次判断下一条记录是否符合，但InnoDB 的数据是按数据页为单位来读写的，所以就算多读一次也占用不了多少时间

更新过程

普通索引可以使用change buffer，可以将一系列的更新写到change buffer中，后期再一次性写入到磁盘中，极大的提高了更新的效率，而唯一索引没有办法使用change buffer 。所以尽量选择普通索引

 

change buffer 和 redo log

插入语句：

insert into t(id,k) values(id1,k1),(id2,k2);

假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存(InnoDB buffer pool) 中，k2 所在的数据页不在内存中。下图所示是带 change buffer 的更新状态图。

操作顺序：

1. Page 1 在内存中，直接更新内存
2. Page 2 没有在内存中，就在内存的change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）

 

查询操作

select * from t where k in (k1, k2);

假设内存中的数据都还在，此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关。

1. 读 Page 1 的时候，直接从内存返回。不需要等内存中的数据更新后返回。
2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志（可能有多个），依次merge一个正确的版本。然后写redo log，redo log中包含数据变更和change buffer 变更。此时内存中数据页为脏页，刷脏是后台线程的流程。如果某个数据页刷脏完成，当redo log中对应的该条刷盘时会识别出来并且跳过。

最后到底怎么选索引

1. 业务正确性优先，业务可以保证不重复，普通索引提升效率。业务不能保证重复，就需要唯一索引保证。
2. 历史数据归档库没有唯一索引冲突，可以选择普通索引。

 

No.10 MySQL为什么有时候会选错索引？

优化器的逻辑

Mysql是根据扫描行数来判断选择哪个索引，扫描行数越少，则越容易被选择，查看扫描行数可以通过explain关键字来查看。

explain select * from user where sex = 1

真正执行语句之前，mysql不知道具体有多少条，只能根据统计信息估算。这个统计信息就是索引的“区分度”。索引上不同值越多，区分度越好。而一个索引上不同值的个数称为“基数”。使用show index可以查看。下图中，每行三个字段值都是一样的，但在统计信息中，基数值都不准确。 

mysql怎么得到索引的基数？

mysql采用采样统计，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。
参数 innodb_stats_persistent有两种不同的模式

    设置为 on 的时候，表示统计信息会持久化存储。默认 N 是 20，M 是 10。
    设置为 off 的时候，表示统计信息只存储在内存中。默认 N 是 8，M 是 16。

如果统计信息不对，可以使用analyze table t 命令重新统计。

索引选择异常和处理

1. force index 强行选择一个索引 

   select * from user force index(sex) where sex = 1

2. 修改语句，引导 MySQL 使用我们期望的索引
3. 新建索引，或者删除误用的索引

 

No.11 怎么给字符串字段加索引？

1. 利用前缀索引

如果字符串过长，而前面几个字段可以确定一个唯一值，比如邮箱，前面都是几位数字+@qq.com，我们不用给全部字段加上索引，而只需要索引前面几个数字即可，这样就极大的节省索引占的空间了。

alter table user add index index_email(email,9)

2. 反转字符串

有的时候字段前面都是一样，而后面是不一样的，比如身份证号，这时就不好利用前缀索引了，不过我们可以将身份证的倒序存储，这样就巧妙的再次利用前缀索引的优势了。

select * from t where id_card = reverse('input_id_card');

3. 使用Hash 

这种就是将字符串计算出一个hash值，然后给表新增一个字段将hash存储进去，下次查找时先将字符串换算为hash再去表中查找hash列，不过这种只适合等值查询，不能进行范围查询。

 

No.12 为什么我的MySQL会“抖”一下？

当Mysql执行过程中会突然慢下来，过一会又好了，而且不是随机的，持续时间很短，看起来就好像Mysql“抖”了一下。这个过程其实是Mysql在刷"脏页"的过程。

什么时候会触发刷脏？

1.innodb的redo log写满了，这时候系统会停止所有更新,把checkpoint 往前推进。

2.buffer pool内存不足，此时需要淘汰一些数据页，有可能会淘汰脏页，就要先把脏页刷到磁盘。

刷脏页一定会写盘，就保证了每个数据页有两种状态：
a. 内存里的一定是正确数据。
b. 内存里没有，磁盘上的一定是正确数据。

3.mysql认为系统空闲时，会刷盘。当然系统繁忙时，也会见缝插针刷盘。

4.mysql正常关闭。

 

No.13 为什么表数据删掉一半，表文件大小不变？

当innodb_file_per_table的参数为OFF时，表的数据会放到共享内存中，也就是和数据字典放一块。而为ON时，表的数据存储在以.ibd为后缀的文件中，当我们使用drop table删除表时，会直接删除这个文件达到回收的目的，而如果数据是放到了共享内存中，那么即使表删除了，空间也是不会回收的。所以我们一般都将此参数设置为ON，MySQL5.5.6版本之后默认就是ON了。
 

内容基于innodb_file_per_table on展开

假设要删除R4，innodb只会标记R4删除。如果之后插入一个ID在300和600之间的记录时，可能会复用该位置。如果删掉整页，整个数据页可以被复用,所以磁盘文件大小不会缩小。

当我们删除某一行记录时，其实MySQL只是把此行记录标记为了“可复用”，但磁盘大小是不会变的，所以通过delete表中记录是达不到回收表空间的。这些被标记为“可复用”而没有使用的空间看起来就像是“空洞”，其实不止删除会造成空洞，插入一样可以，如果我们不是按顺序插入，而是随机插入，那么就可能造成页分裂，而之前那一页末尾可能还有未使用的空间。

重建表

新建一个表，将旧表中的数据一行一行读出来插入到新表中，然后以新表替换旧表。

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

1.recreate重建表

alter table 表名 engine = InnoDB

2.重新统计索引信息

analyze table 表名

3.recreate + 重新统计索引信息

optimize table 表名

三种方式重建表的区别 

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

optimize table t 等于 recreate+analyze

 

No.14 count(*)这么慢，我该怎么办？

count(*) 的实现方式

• MyISAM 引擎保存总行数，所以它的count(*)效率很高。但如果加了where不能很快返回。
• Innodb需要一行一行读出来累积计数。(多个事务count的行数不同，所以不能保存总行数)

 

执行速度比较

count(其他字段) < count(主键) < count(1) ≈ count(*)

 

不同的 count 用法(基于 InnoDB 引擎)

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

No.15 日志和索引相关问题

如果redo处理perpare阶段，写binlog之前崩溃（crash），恢复时事务回滚。
如果binlog写完了，redo未commit前崩溃（crash）：

    如果redo log事务完整，有了commit标识，直接提交；
    如果redo log里事务只有完整的perpare，则判断对应事务binlog是否完整：
    a. 如果是，则提交事务；
    b. 否则回滚。

追问 1：MySQL 怎么知道 binlog 是完整的?
回答：一个事务的binlog是有完整格式的：

    statement 格式的 binlog，最后会有 COMMIT；
    row 格式的 binlog，最后会有一个 XID event。

mysql 5.6.2版本以后，引入binlog-checksum验证binlog内容是否正确。

追问 2：redo log 和 binlog 是怎么关联起来的？
回答：它们有个共同的数据字段：XID。

追问 3：处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，MySQL 为什么要这么设计?
回答：因为写入binlog后，会被从库使用，为了保证主备一致性。

追问 4：如果这样的话，为什么还要两阶段提交呢？干脆先 redo log 写完，再写 binlog。崩溃恢复的时候，必须得两个日志都完整才可以。是不是一样的逻辑？
回答：两阶段提交是经典分布式系统问题，并不是mysql独有的。
innodb，如果redo log提交完成，事务就不能回滚（如果还允许回滚，可能覆盖掉别的事务的更新）。但如果redo log直接提交，binlog写失败时，innodb回滚不了 ，数据和binlog日志会不一致。两阶段提交就是为了每个“人”都ok，在一起提交。

追问 5：不引入两个日志，也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复，又能支持归档，不就可以了？
回答：不可以，历史原因，innodb不是mysql原生引擎，binlog不支持崩溃恢复，所以innodb实现了redo log。

追问 6：那能不能反过来，只用 redo log，不要 binlog
回答：如果从崩溃恢复角度来讲是可以的。但redo log是循环写，历史日志没法保留，而binlog有归档功能。binlog还有可以实现复制主从同步。

追问 7：redo log 一般设置多大？
回答：redo log太小会导致很快写满，然后就会强行刷redo log。如果几个TB硬盘，直接将redo log设置为4个文件，每个文件1G。

追问 8：正常运行中的实例，数据写入后的最终落盘，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？
回答：这个问题就是“redo log 里面到底是什么”的问题。
redo log没有记录数据页完整数据，所以它没有能力自己去更新磁盘数据页。

    1.如果再次运行的实例，数据页被修改，跟磁盘数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这过程和redo log毫无关系。
    2.在崩溃恢复场景，Innodb如果判断一个数据页可能在崩溃恢复时丢失更新，就会将它读到内存，然后让redo log更新内存内容。更新完成内存也变成脏页，就回到第一种情况。

 

本篇转载自

https://blog.csdn.net/zxcc1314/article/details/84842650

https://blog.csdn.net/junmoxi/article/details/85758284