一、普通索引和唯一索引
查询过程
对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
InnoDB 的数据是按数据页为单位来读写的。在InnoDB 中，每个数据页的大小默认是 16KB。对于整型字段，一个数据页可以放近千个 key。
更新过程
当需要更新一个数据页时，如果数据页在内存中就直接更新， 而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 changebuffer 中， 这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
唯一索引的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。所以唯一索引的更新不能使用 change buffer，只有普通索引可以使用。
change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
如果插入一个新记录 (4,400) 的话：
- 这个记录要更新的目标页在内存中
对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。 - 这个记录要更新的目标页不在内存中
对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。
change buffer 的使用场景
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
索引选择和实践
普通索引和唯一索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。
如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。
change buffer 和 redo log
insert into t(id,k) values(id1,k1),(id2,k2)
k1 所在的数据页在内存 (InnoDBbuffer pool) 中，k2 所在的数据页不在内存中。下图是是带 change buffer 的更新状态图。
分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）
(系统表空间就是用来放系统信息的，比如数据字典什么的，对应的磁盘文件是ibdata1, 数据表空间就是一个个的表数据文件，对应的磁盘文件就是 表名.ibd)
这条更新语句做了如下的操作（按照图中的数字顺序）：
- Page 1 在内存中，直接更新内存；
- Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
- 将上述两个动作记入 redo log 中（图中 3 和 4）。做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。
redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗
如果某次写入使用了 change buffer 机制，之后主机异常重启，是否会丢失 change buffer 和数据?
不会丢失，虽然是只更新内存，但是在事务提交的时候，我们把 change buffer 的操作也记录到 redo log 里了，所以崩溃恢复的时候，change buffer 也能找回来。
merge 的过程是否会把数据直接写回磁盘?
merge 的执行流程是这样的：
- 从磁盘读入数据页到内存（老版本的数据页）；
- 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
- 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。
到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据。
二、MySQL为什么有时候会选错索引？
优化器的逻辑
化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
扫描行数是怎么判断的?
MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。 而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。
我们可以使用 show index 方法，看到一个索引的基数。虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。
MySQL 是怎样得到索引的基数的呢？
MySQL 采样统计，如果把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。接下来，我们再一起看看优化器预估的，这两个语句的扫描行数是多少。
优化器为什么放着扫描 37000 行的执行计划不用，却选择了扫描行数是 100000 的执行计划呢？这是因为，如果使用索引 a，每次从索引 a 上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。
MySQL 选错索引，是没能准确地判断出扫描行数。既然是统计信息不对，那就修正。analyze table t 命令，可以用来重新统计索引信息。我们来看一下执行效果。
索引选择异常和处理
一种方法是，采用 force index 强行选择一个索引。
第二种方法就是，可以考虑修改语句(语义的逻辑是相同的)，引导 MySQL 使用我们期望的索引。
第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
三、怎么给字符串字段加索引？
使用前缀索引后，可能会导致查询语句读数据的次数变多。
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。 因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。
首先，使用下面这个语句，算出这个列上有多少个不同的值:
select count(distinct email) as L from SUser;
然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句:
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;
前缀索引对覆盖索引的影响
使用前缀索引就用不上覆盖索引对查询性能的优化了
区分度不好的字符串怎么建索引？
如身份证号码
第一种方式是使用倒序存储。存储身份证号的时候把它倒过来存。
第二种方式是使用 hash 字段。可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
它们的相同点是，都不支持范围查询。
四、为什么MySQL会“抖”一下？
一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。
InnoDB 在处理更新语句的时候，只做了写日志( redo log)这一个磁盘操作。然后把内存里的数据写入磁盘的过程，术语就是flush。在这个 flush 操作执行之前，内存和硬盘数据是不一致的。
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。
什么情况会引发数据库的 flush 过程呢？
- InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作。 如果你从监控上看，这时候更新数会跌为 0。
- 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
InnoDB 用缓冲池（buffer pool）管理内存， 缓冲池中的内存页有三种状态：还没有使用的；使用了并且是干净页；使用了并且是脏页。。
而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。
一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长。 - MySQL 认为系统“空闲”的时候。
- MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
InnoDB 刷脏页的控制策略
首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试。
想要尽量避免“一下”这种情况，**就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%。**其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的。
补充：mysql在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉。
在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。
找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。而如果使用的是 SSD 这类 IOPS 比较高的设备的话，因为这时候 IOPS 往往不是瓶颈，所以可以设置为0。
