普通索引和唯一索引

普通索引和唯一索引的区别是？

InnoDB 的数据是按数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。必须要将数据页读入内存才能判断，如果数据页已经读到内存了，那直接更新内存会更快，就没必要使用 change buffer了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change **buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数为50的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据可里面成本最高的操作之一。 change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多，收益就越大。因此，对于写多读少的业务来说，数据页在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类，日志类的系统。

索引选择和实践

普通索引和唯一索引，在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer 。而在其他情况下，change buffer 都能提升更新性能。
在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。特别地，在使用机械硬盘时， change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

redo log 主要是节省的随机写磁盘的IO消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的IO消耗。
_

小结

change buffer 机制

MySQL 为什么 有时候会 选错索引

在 MySQL 中一张表其实是可以支持多个索引的，但是写SQL语句的时候，并没有主动指定使用哪个索引，使用哪个索引是由MySQL来确定的。

选择索引是 优化器 的工作，优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。

小结

  • 索引统计的更新机制
  • 索引统计信息不准确，analyze table
  • 优化器误判，force index 强行指定索引，修改语句引导优化器，增加删除索引绕过问题

怎么给字符串字段加索引

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

使用前缀索引就用不上覆盖索引对查询性能的优化了。

身份证号码这种字段的索引优化：

  1. 倒序存储
  2. hash字段索引

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫做 redo log（重做日志）。在更新内存写完 redo log 后，就返回给客户端，本次更新成功。

flush

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

哪些场景会出发 flush:

  1. redo log 写满
  2. 内存不足
  3. MySQL判定系统空闲时
  4. MySQL正常关闭时

redo log 写满了，整个系统不能再接受更新了。

内存不够用了，要先将脏页写到磁盘。InnoDB 用缓冲池 _buffer pool_ 管理内存。

  • 未使用
  • 使用中，干净页
  • 使用中，脏页

innodb_io_capacity 磁盘能力参数 - 建议设置成磁盘的 IOPS
_

  1.  fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

— 忘记 subLinux OS 密码了，尴尬😅 paradise1994
fio 安装的稍微有点慢

脏页比例， redo log 写盘速度

innodb_flush_neighbors 参数：控制 flush 附近数据页处理逻辑

表空间，表数据

参数inndodb_file_per_table

  • 参数 OFF 表的数据存放在系统共享表空间，也就是跟数据字典放在一起
  • 参数 ON 每个 InnoDB 表数据存储在一个以 .idb为后缀的文件中

数据的删除流程

表空间的释放，标记为可复用，重建表，online DDL

count(*)

技术问题，没法优化，可以建一张表存