MySQL进阶-优化查询

优化分页查询

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

优先考虑覆盖索引

什么是覆盖索引？

简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

理解方式一 ：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。 一个索引包含了满足查询结果的数据就叫做覆盖索引 。

理解方式二 ：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。

举例一：

当条件是不等于的时候，查询优化器不一定会使用索引。此时要是只查索引包含的两个字段就会走这个覆盖索引，查询优化器选择索引用不用都是基于查询成本的考虑。

举例二：

此时模糊查询百分号在前，索引失效

此时走了覆盖索引，经过查询优化器的考量，发现走覆盖索引成本较低。

上述都使用到了声明的索引，下面的情况则不然，查询列依然多了classId,结果是未使用到索引，因为此时多了这一列，需要进行回表操作，查询成本较高，查询优化器会认为不如直接全表扫描。

覆盖索引的利弊

首先就是 避免了回表操作 ，要查询的字段索引节点都有可以直接返回，不需要回表到叶子节点查询其它字段信息， 由于索引是按照键值顺序存储的 ，可以把随机读取的IO转化索引查找的顺序IO，加快查询效率。

如何给字符串添加索引

前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

这两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图。

如果使用的是index1 （即email整个字符串的索引结构），执行顺序是这样的：

1. 从index1索引树找到满足索引值是’ ’的这条记录，取得ID2的值；
2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；
3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=' ’的 条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

如果使用的是index2 （即email(6)索引结构），执行顺序是这样的：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
2. 到主键上查到主键值是ID1的行，判断出email的值不是’ ’，这行记录丢弃；
3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然 后判断，这次值对了，将这行记录加入结果集；
4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

结论：

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，但使用了前缀索引就不能使用覆盖索引了，因为前缀索引必须回表查询，才能精确比对。

索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。即先在索引层面筛选，再利用筛选完的索引

使用案例

ICP的使用条件

1. 如果表的访问类型为 range 、 ref 、 eq_ref 或者 ref_or_null 可以使用ICP。
2. ICP可以使用 InnDB 和 MyISAM 表，包括分区表 InnoDB 和 MyISAM 表
3. 对于 InnoDB 表，ICP仅用于 二级索引 。ICP的目标是减少全行读取次数，从而减少I/O操作。
4. 当SQL使用覆盖索引时， 不支持ICP优化方法。因为这种情况下使用ICP不会减少I/O 。
5. 相关子查询的条件不能使用ICP

普通索引 vs 唯一索引

查询过程

假设，执行查询的语句是根据索引字段进行等值匹配。

• 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一 个不满足k=5条件的记录。
• 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

更新过程

为了说明普通索引和唯一索引对更新语句性能的影响这个问题，介绍一下change buffer。

• 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话， 在不影响数据一致性的前提下， InooDB会将这些更新操作缓存在change buffer中 ，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
• 将change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge 。除了 访问这个数据页 会触 发merge外，系统有 后台线程会定期 merge。在 数据库正常关闭（shutdown） 的过程中，也会执行merge 操作。
• 如果能够将更新操作先记录在change buffer， 减少读磁盘 ，语句的执行速度会得到明显的提升。而且， 数据读入内存是需要占用 buffer pool 的，所以这种方式还能够 避免占用内存 ，提高内存利用率。

change buffer的使用场景

1. 普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是 对 更新性能 的影响。所以， 建议你 尽量选择普通索引 。

2. 在实际使用中会发现， 普通索引 和 change buffer 的配合使用，对于 数据量大 的表的更新优化 还是很明显的。

3. 如果所有的更新后面， 都马上伴随着对这个记录的查询 ，那么你应该关闭change buffer 。而在其他情况下，change buffer都能提升更新性能。

4. 由于唯一索引用不上change buffer的优化机制，因此如果 业务可以接受 ，从性能角度出发建议优 先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？

   • 首先， 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能 问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。 这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一 个排查思路。
   • 然后，在一些“ 归档库 ”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年， 然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率， 可以考虑把表里面的唯一索引改成普通索引。