9.唯一索引和普通索引，以及changebuffer

两类索引在查询能力上没差别， 主要考虑的是对更新性能的影响：
唯一索引在更新时（如插入），需要找到位置，判断冲突，然后进行更新操作。比普通索引多了一步判断冲突，虽然仅耗费了微小的cpu时间，但是如果要更新的记录的目标页不在内存中：

• 对于唯一索引来说：需要将数据页读入内存， 判断到没有冲突， 插入这个值， 语句执行结束；
• 对于普通索引来说， 则是将更新记录在change buffer， 语句执行就结束了

对于唯一索引来说， 所有的更新操作都要先判断这个操作是否违反唯一性约束，因此， 唯一索引的更新就不能使用change buffer 。

由于唯一索引用不上change buffer的优化机制， 因此如果业务可以接受， 从性能角度出发我建
议你优先考虑非唯一索引，如果业务不能保证， 或者业务就是要求数据库来做约束， 那么没得选，必须创建唯一索引。
此外，在一些“归档库”的场景， 你是可以考虑使用唯一索引的。 比如， 线上数据只需要保留半年， 然后历史数据保存在归档库。 这时候， 归档数据已经是确保没有唯一键冲突了。 要提高归档效率， 可以考虑把表里面的唯一索引改成普通索引

changebuffer 原理

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

将change buffer中的操作应用到原数据页， 得到最新结果的过程称为merge，merge是真正进行数据更新的时刻， 而change buffer的主要目的就是将记录的变更动作缓存下来， 所以在一个数据页做merge之前， change buffer记录的变更越多， 收益就越大。

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页） ；
2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个） ， 依次应用， 得到新
   版数据页；
3. 写redo log。 这个redo log包含了数据的变更和change buffer的变更。
   到这里merge过程就结束了。 这时候， 数据页和内存中change buffer对应的磁盘位置都还没有修
   改， 属于脏页， 之后各自刷回自己的物理数据， 就是另外一个过程了

因此， 对于写多读少的业务来说， 页面在写完以后马上被访问到的概率比较小， 此时change
buffer的使用效果最好。 这种业务模型常见的就是账单类、 日志类的系统。

反过来， 假设一个业务的更新模式是写入之后马上会做查询， 那么即使满足了条件， 将更新先记录在change buffer， 但之后由于马上要访问这个数据页， 会立即触发merge过程。 这样随机访问
IO的次数不会减少， 反而增加了change buffer的维护代价。 所以， 对于这种业务模式来
说， change buffer反而起到了副作用。

Q1 如果某次写入使用了change buffer机制， 之后主机异常重启， 是否会丢失change buffer和数据？
不会丢失。在事务提交的时候， 我们把change buffer的操作也记录到redo log里了， 所以崩溃恢复的时候， change
buffer也能找回来

changebuffer 和redo log

change buffer 和WAL 的redolog提升性能的核心机制， 都是尽量减少随机读写。

• change buffer 主要节省的时随机读磁盘的IO消耗（如果在内存中则直接返回，直到真正需要读的时候才将数据页读入内存，根据change buffer 修正后得到一个正确的结果）
• redo log主要节省的时随机写磁盘的IO，WAL思想先写日志再写数据页，日志为顺序写，并且可以合并多条一块写

10.MySQL为什么有时候会选错索引？如何处理？

优化器选择索引的目的， 是找到一个最优的执行方案， 并用最小的代价去执行语句。 在数据库里面， 扫描行数是影响执行代价的因素之一，优化器还会结合是否使用临时表、 是否排序等因素进行综合判断。

问题就是： 扫描行数是怎么判断的？
MySQL在真正开始执行语句之前， 并不能精确地知道满足这个条件的记录有多少条， 而只能根
据统计信息来估算记录数。这个统计信息就是索引的“区分度”。 显然， 一个索引上不同的值越多， 这个索引的区分度就越好。 而一个索引上不同的值的个数， 我们称之为“基数”（cardinality） 。 也就是说， 这个基数越大， 索引的区分度越好。
MySQL通过采样统计来计算基数，采样统计的时候， InnoDB默认会选择N个数据页， 统计这些页面上的不同值， 得到一个平均
值， 然后乘以这个索引的页面数， 就得到了这个索引的基数。而数据表是会持续更新的， 索引统计信息也不会固定不变。 所以， 当变更的数据行数超过1/M的时候， 会自动触发重新做一次索引统计。
通过设置参数innodb_stats_persistent的值来选择：

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

MySQL选错索引

• 情况1. 优化器使用普通索引会把回表的代价算进去
• 情况2. order by时候会选择避免排序的索引降低代价
• 情况。。。

解决与排查方法：
show index 查看索引基数排查是否统计信息不准，explain查看索引使用情况，预估扫描行数rows是否正常

对于由于索引统计信息不准确导致的问题， 你可以用analyze table来解决。
而对于其他优化器误判的情况， 你可以在应用端用force index来强行指定索引， 也可以通过修改
语句来引导优化器， 还可以通过增加或者删除索引来绕过这个问题。

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

1. 直接创建完整索引， 这样可能比较占用空间；
2. 创建前缀索引（靠前的部分连续字节做索引）， 节省空间， 但会增加查询扫描次数， 并且不能使用覆盖索引；
3. 倒序存储， 再创建前缀索引， 用于绕过字符串本身前缀的区分度不够的问题；
4. 创建hash字段索引， 查询性能稳定， 有额外的存储和计算消耗， 跟第三种方式一样， 都不支持范围扫描。
   在实际应用中， 你要根据业务字段的特点选择使用哪种方式。

另外，前缀索引 email(18)， 这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下， 因为系统并不确定前缀索引的定义是否截断了完整信
息。

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

InnoDB在处理更新语句的时候， 只做了写日志这一个磁盘操作。当内存数据页跟磁盘数据页内容不一致的时候， 我们称这个内存页为“脏页”。 内存数据写入到磁盘后， 内存和磁盘上的数据页的内容就一致了， 称为“干净页”。不论是脏页还是干净页， 都在内存中。
平时执行很快的更新操作， 其实就是在写内存和日志， 而MySQL偶尔“抖”一下的那个瞬间， 可能就是在刷脏页（flush）：

• InnoDB的redo log写满了。 这时候系统会停止所有更新操作， 把checkpoint往前推进， redo log留出空间可以继续写。 这种情况是InnoDB要尽量避免的。 因为这时， 整个系统不能再接受更新了， 所有的更新都必须堵住。

• 系统内存不足。 当需要新的内存页， 而内存不够用的时候， 就要淘汰一些数据页， 空出内存给别的数据页使用。 如果淘汰的是“脏页”， 就要先将脏页写到磁盘。
  如果一个查询要淘汰的脏页个数太多， 会导致查询的响应时间明显变长；

• MySQL认为系统“空闲”的时候。只要有机会就刷一点“脏页”

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

InnoDB刷脏页的控制策略

刷脏页速度两个影响因素：
一个是脏页比例， 一个是redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限， 默认值是75%，
全力刷脏页的速度与磁盘IOPS有关：innodb_io_capacity参数。
当前的脏页比例（假设为M） ，当前写入的日志序号跟checkpoint对应的序号之间的差值（假设为N），系统会通过一系列复杂的计算得到F1(M)和F2(N)两个值， 取其中较大的值记为R， 之后引擎就可以按照：
innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度

innodb_flush_neighbors 参数与连坐机制：
一旦一个查询请求需要在执行过程中先flush掉一个脏页时， 这个查询就可能要比平时慢了。 而MySQL中的一个机制， 可能让查询更慢： 在准备刷一个脏页的时候， 如果这个数据页旁边的数据页刚好是脏页， 就会把这个“邻居”也带着一起刷掉； 而且这个逻辑还可以继续蔓延， 也就是对于每个邻居数据页， 如果跟它相邻的数据页也还是脏页的话， 也会被放到一起刷。
innodb_flush_neighbors 参数 值为1的时候会有上述的“连坐”机制， 值为0时表示不找邻居， 自己刷自己的。

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

如果要收缩一个表， 只是delete掉表里面不用的数据的话， 表文件的大小是
不会变的， 你还要通过alter table命令重建表， 才能达到表文件变小的目的。
原因：delete命令其实只是把记录的位置， 或者数据页标记为了“可复用”，但是并没有回收空间， 但磁盘文件的大小是不会变的。
不止是删除数据会造成空洞， 插入数据也会，如果数据是随机插入的， 就可能造
成索引的数据页分裂，页分裂完成后， 页末尾就会留下空洞。
也就是说， 经过大量增删改的表， 都是可能是存在空洞的

如何正确收缩空间：
使用alter table A engine=InnoDB命令来重建表， MySQL会自
动完成转存数据、 交换表名、 删除旧表的操作。但在整个DDL过程中， 表A中不能有更新。 也就是说， 这个DDL不是Online的。
而在MySQL 5.6版本开始引入的Online DDL：
重建表的流程：

image.png

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

alter语句在启动的时候需要获取MDL写锁， 但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢？ 为了实现Online， MDL读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢？ 为了保护自己， 禁止其他线程对这个表同时做DDL。
所以， 相对于整个DDL过程来说， 锁的时间非常短。 对业务来说，就可以认为是Online的。

重建表的这个语句alter table t engine=InnoDB， 其实隐含的意思是：
alter table t engine=innodb,ALGORITHM=inplace;
对于server层来说， 没有把数据挪动到临时表， 是一个“原地”操作， 这就是“inplace”名称的来源（server层没有临时表，引擎层有临时文件，因此磁盘空间不够的时候也无法重建）
跟inplace对应的就是拷贝表的方式了， 用法是：
alter table t engine=innodb,ALGORITHM=copy;
当你使用ALGORITHM=copy的时候， 表示的是强制拷贝表（有临时表也有临时文件）

补充：使用optimizetable、 analyze table和alter table这三种方式重建表的区别：

• 从MySQL 5.6版本开始， alter table t engine = InnoDB（也就是recreate） 默认为上图流程；
• analyze table t 其实不是重建表， 只是对表的索引信息做重新统计， 没有修改数据， 这个过程中加了MDL读锁；
• optimize table t 等于recreate+analyze。

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

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

• MyISAM引擎把一个表的总行数存在了磁盘上， 因此执行count(*)的时候会直接返回这个数，
  效率很高；
• 而InnoDB引擎就麻烦了， 它执行count( *)的时候， 需要把数据一行一行地从引擎里面读出来， 然后累积计数。
  需要注意的是，这里讨论的是没有过滤条件的count( *)， 如果加了where 条件的话， MyISAM表也是不能返回得这么快的。

为什么InnoDB不跟MyISAM一样， 也把数字存起来呢？

因为即使是在同一个时刻的多个查询， 由于多版本并发控制（MVCC） 的原因， InnoDB表“应该返回多少行”也是不确定的。（事务开启时的一致性视图、事务有更新操作导致当前读等原因，查到的行数并不一致）

普通索引树比主键索引树小很多。 对于count(*)这样的操作， 遍历哪个索引树得
到的结果逻辑上都是一样的。 因此， MySQL优化器会找到 最小的那棵树来遍历。 在保证逻辑正确的前提下， 尽量减少扫描的数据量， 是数据库系统设计的通用法则之一。

show table status 命令的输出结果里面也有一个TABLE_ROWS用于显示这个表当前有多少行,索引统计的值是通过采样来估算,因此不能代替count(*)

MyISAM表虽然count()很快， 但是不支持事务；
show table status命令虽然返回很快， 但是不准确；
InnoDB表直接count()会遍历全表， 虽然结果准确， 但会导致性能问题.

Q1.现在有一个页面经常要显示交易系统的操作记录总数， 到底应该怎么办呢？
答案是: 只能自己计数
需要自己找一个地方， 把操作记录表的行数存起
来。
1.用缓存系统保存计数 :如redis，但是又存在系统不同步的问题，导致不精确，崩溃会丢失
2.在数据库保存计数：直接放到数据库里单独的一张计数表C中，由于innodb的事务特性，可以解决上述redis中的问题（逻辑实现上是启动一个事务， 执行两个语句：insert into 数据表；update 计数表， 计数值加1。）。

Q2 count(*)、 count(主键id)、 count(字段)和count(1)等不同用法的性能， 有哪些差别？
分析性能差别的时候， 你可以记住这么几个原则：

1. server层要什么就给什么；
2. InnoDB只给必要的值；
3. 现在的优化器只优化了count(* )的语义为“取行数”， 其他“显而易见”的优化并没有做。
   对于count(主键id)来说， InnoDB引擎会遍历整张表， 把每一行的id值都取出来， 返回给server层。 server层拿到id后， 判断是不可能为空的， 就按行累加。
   对于count(1)来说， InnoDB引擎遍历整张表， 但不取值。 server层对于返回的每一行， 放一个数字“1”进去， 判断是不可能为空的， 按行累加。
   单看这两个用法的差别的话， 你能对比出来， count(1)执行得要比count(主键id)快。 因为从引擎返回id会涉及到解析数据行， 以及拷贝字段值的操作。
   对于count(字段)来说：
4. 如果这个“字段”是定义为not null的话， 一行行地从记录里面读出这个字段， 判断不能为null， 按行累加；
5. 如果这个“字段”定义允许为null， 那么执行的时候， 判断到有可能是null， 还要把值取出来再判断一下， 不是null才累加。
   也就是前面的第一条原则， server层要什么字段， InnoDB就返回什么字段。
   但是count()是例外， 并不会把全部字段取出来， 而是专门做了优化， 不取值。 count()肯定不是null， 按行累加。
   所以结论是： 按照效率排序的话， count(字段)<count(主键id)<count(1)≈count( *)， 所以建议尽量使用count( *)

16. order by 是怎样工作的

全字段排序 VS rowid排序

1.MySQL会为每个线程分配一个内存（sort_buffer） 用于排序该内存大小为sort_buffer_size
1>如果排序的数据量小于sort_buffer_size， 排序将会在内存中完成
2>如果排序数据量很大， 内存中无法存下这么多数据， 则会使用磁盘临时文件来辅助排序， 也称外部排序
3>在使用外部排序时， MySQL会分成好几份单独的临时文件用来存放排序后的数据， 然后在将这些文件合并成一个大文件

2.mysql会通过遍历索引将满足条件的数据读取到sort_buffer， 并且按照排序字段进行快速排序
1>如果查询的字段不包含在辅助索引中， 需要按照辅助索引记录的主键返回聚集索引取出所需字段
2>该方式会造成随机IO， 在MySQL5.6提供了MRR的机制， 会将辅助索引匹配记录的主键取出来在内存中进行排序， 然后在回表
3>按照情况建立联合索引来避免排序所带来的性能损耗， 允许的情况下也可以建立覆盖索引来避免回表

全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端
缺点：
1.造成sort_buffer中存放不下很多数据， 因为除了排序字段还存放其他字段， 对sort_buffer的利用效率不高
2.当所需排序数据量很大时， 会有很多的临时文件， 排序性能也会很差
优点：
MySQL认为内存足够大时会优先选择全字段排序， 因为这种方式比rowid 排序避免了一次回表操作

rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中， 并按照排序字段进行排序
3.按照排序后的顺序， 取id进行回表取出想要获取的数据
4.将结果集返回给客户端
优点： 更好的利用内存的sort_buffer进行排序操作， 尽量减少对磁盘的访问
缺点： 回表的操作是随机IO， 会造成大量的随机读， 不一定就比全字段排序减少对磁盘的访问