索引优化与查询优化

哪些维度可以进行数据库调优？

  • 索引失效，没有充分利用到索引--索引建立

  • 关联查询太多JOIN(设计缺陷或不得已的需求)--SQL优化

  • 服务器调优及各个参数设置（缓冲、线程数等）--调整my.cnf

  • 数据过多--分库分表

  • 虽然SQL查询优化的技术有很多，但是大方向上完全可以分成物理查询优化和逻辑查询优化两大块

    • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用
    • 逻辑查询优化是通过SQL 等价变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高

索引失败案例

  1. 全值匹配（联合索引覆盖where字段）
  2. 最佳左前缀法则（B+树特性，左边值未定，无法使用此索引）
  3. 主键插入顺序（频繁随机插入主键，容易导致页分裂）
  4. 计算、函数、类型转换（自动或手动）导致索引失效
  5. 范围条件右边的列索引失效（创建联合索引时，中间的索引字段是范围匹配，导致一部分索引未使用上）
  6. 不等于(!=或者<>)索引失效
  7. is null 可以使用索引，is not null 无法使用索引
  8. like以通配符%开头索引失效
  9. OR前后存在非索引的列，索引失效（前后索引字段可以使用index_merge）
  10. 数据库和表的字符集统一使用同一字符集
  • 一般性建议：
    • 对于单列索引，尽量选择针对当前query过滤性更好的索引
    • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
    • 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引
    • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
  • 总之：书写SQL语句时，尽量避免造成索引失效的情况

关联查询优化

join语句原理

  • Simple Nested-Loop Join （简单嵌套循环连接）
    • 算法相当简单，驱动表A中的每一条记录与被驱动表B的记录进行判断
  • Index Nested-Loop Join
    • 这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，我们称之为Index Nested-Loop Join ，简称INLJ
  • Block Nested-Loop Join
    • 不再是逐条获取驱动表的数据，而是一块一块的获取，引入了 join buffer缓冲区，将驱动表的 join 相关的部分数据列（大小受 join buffer 的限制）缓存到 join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率
    • 参数设置
      • block_nested_loop
        • 通过 show variables like '%optimitzer_switch%';
      • join_buffer_size
        • 驱动表能不能一次加载完，要看join_buffer能不能存储所有的数据，默认情况下 join_buffer_size = 256k
  • Hash Join
    • MySQL8.0开始废弃BNLJ，默认使用hash join
    • Nested Loop: 对于被连接的数据子集较小的情况，Nested Loop 是个较好的选择
    • Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行

Join小结

  1. 整体效率比较：INLJ > BNLJ > SNLJ
  2. 永远用小结果集驱动大结果集（其本质是减少外层循环的数据数量）
  3. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
  4. 增大join buffer size 的大小（一次缓存的数据越多，那么内层包的扫描次数就越少）
  5. 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）

子查询优化

  • MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，既一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作
  • 在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快

排序优化

  • SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是WHERE子句中避免全表扫描，在 ORDER BY 子句 避免使用 FileSort 排序。
  • 尽量使用Index完成ORDER BY 排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引
  • 无法使用index时，需要对FileSort方式进行调优。

FileSort算法：双路排序和单路排序

双路排序（慢）

  • MySQL 4.1 之气是双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中供读取对应的数据输出

单路排序（快）

  • 从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出。

优化策略

  • 尝试提高 sort_buffer_size
  • 尝试提高 max_length_for_sort_data
  • Order by 时 select * 是一个大忌，最好只Query需要的字段

GROUP BY优化

  • group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引
  • group by先排序再分组，遵照索引建的最佳左前缀法则
  • 当无法使用索引列，增大max_length_for_sort_data 和 sort_buffer_size 参数的设置
  • where效率高于having，能写在where限定的条件就不要写在having中了
  • 减少使用order by

优化分页查询

  • 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
  • 该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询

优先考虑覆盖索引

  • 一个索引列包含了满足查询结果的数据就叫做覆盖索引
  • 索引列+主键包含SELECT 到 FROM 之间查询的列

覆盖索引的利弊

好处

  • 避免Innodb表进行索引的二次查询（回表）
  • 可以把随机IO变成顺序IO加快查询效率

弊端

  • 索引字段的维护总是有代价的。

前缀索引

  • MySQL是支持前缀索引的。默认地，如果你创建索引地语句不指定前缀长度，那么索引就会包含整个字符串
  • 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

索引下推

  • 使用前后的扫描过程

    • 在不使用ICP索引扫描的过程

      • storage层：只将满足index key 条件的索引记录对应的整行记录取出，返回给server层
      • server层：对返回的数据，使用后面的where条件过滤，直至返回最后一行

    • 使用ICP扫描的过程

      • storage层：首先将index key条件满足的索引记录区间确定，然后再索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层
      • server层：对返回的数据，使用table filter条件做最后的过滤

    • 使用前后的成本差别

      • 使用前，存储层多返回了需要被index filter过滤掉的整行记录
      • 使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。IPC地加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

  • ICP的使用条件

    • 只能用于二级索引
    • explain显示的执行计划中type值（join类型）为range、ref、eq_ref或者ref_or_null
    • 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取争标的记录到server端做where过滤
    • ICP可以用于MyISAM和InnoDB存储引擎
    • MySQL5.7版本开始支持
    • 当SQL使用覆盖索引时，不支持ICP优化方法

  • ICP的开启/关闭

    • 默认情况下启用索引条件下推。可以通过设置系统变量 optimizer_switch 控制：index_condition_pushdown
    #开索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

#关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=on';
    • 当使用索引条件下推时，EXPLAIN语句输出结果中Extra列内容显示为 Using index condition

普通索引vs唯一索引

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

change buffer：

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

change buffer使用场景

  1. 普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议你尽量选择普通索引。
  2. 在实际使用中会发现， 普通索引和change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。
  3. 如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。而在其他情况下，change buffer都能提升更新性能。
  4. 由于唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？
    • 首先，业务正确性优先。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一个排查思路。
    • 然后，在一些“ 归档库”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

其他查询优化策略

EXISTS 和 IN 的区分

  • 小表驱动大表，EXIST后面跟大表，IN后面跟小表
    • 区别：IN是后面的表作为驱动表，EXISTS是前面的表作为驱动表

COUNT(*) 与 COUNT(具体字段)与COUNT(1)效率

  • 对于InnoDB存储引擎，COUNT(具体字段)会经量采用二级索引。而对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计

SELECT(*)

  • 会通过查询数据字典将 * 按序号转换成所有列名，这会大大耗费资源和时间
  • 无法使用覆盖索引

LIMIT1对优化的影响

  • 针对的是会扫描全表的SQL语句，如果确定结果集只有一条，那么加上LIMIT1的时候，当找到一条结果的时候就不会继续扫描了，这样就加快查询速度

多使用COMMIT

  • COMMIT所释放的资源：
    • 回滚段上用于恢复数据的信息
    • 被程序语句获得的锁
    • redo/undo log buffer 中的空间
    • 管理上述3种资源中的内部花费