sql优化

SQL优化

• 优化join查询

  • 常见的优化点

    • 关键字段建立索引（被驱动表建立索引即可）

  • 以小表驱动大表（mysql一般会自动选择）

    • 合理设置join_buffer_size

  • 优化过程

    • 将驱动表所有数据加载到join_buffer内存中，如果join_buffer_size大小足够装得下驱动表数据的话

    • 从驱动表中读入一行数据，取出关联字段到被驱动表遍历查找，如果存在索引，树级搜索符合条件的数据作为数据集的数据

    • 重复第二步，知道完成驱动表的遍历

  • 通过上述这种方式可以看出

    • 增大join_buffer_size，可以将驱动表数据都装进内存，不使用磁盘临时表，提升性能。

    • 关联字段建立索引，可以让被驱动表以树级复杂度进行搜索，提升遍历性能

    • 整体执行时间接近Nlog(M)，以小表N驱动大表可以提升性能

• 优化子查询

  • IN型子查询采用覆盖索引

  • 对于join子查询

    • 关联字段加索引

    • 不需要的字段不要返回

    • 使用覆盖索引

  • from型子查询

    • 减少返回的数量，利用group by使得子查询走内存临时表使得性能不要太差

• 优化order by

  • 主要优化using filesort，使其利用索引排序。主要手段是建立排序可以用的联合索引

    值得说的是：一般是联合索引中索引字段的顺序，跟 sql 中 where 条件及 order by 不一致会导致失效，只要顺序调整一致就不会出现这个问题。

  • 合理设施order by相关参数

    • 增大max_length_for_sort_data，线程级别

    • 增大sort_buffer_size ，线程级

  • 使用驱动表字段代替非驱动表字段，前提是意思相同

• 优化group by

  • 和order by类似

  • 补充一个：如果orber by结果不需要排序的话，可以order by = null，取消文件排序，不然会用默认字段排序

  • sql_big_resul会提示优化器结果集很大，直接使用磁盘临时表排序，如果结果集很大就不要走内存了，直接走磁盘就好。

  • sql_small_sult_提示优化器结果集很小，直接走内存临时表

    EXPLAIN SELECT SQL_SMALL_RESULT cus.* from customer cus GROUP BY cus.address_id

• 优化limit

  • 数量量过大

    • 缩小返回行数

  • 偏移量过大

    • limit机制是先扫描a条数据，再扫描offset数据，最后再截取，所以业务最好能控制深度，不过不允许，考虑延迟加载或者辅助定位

    • 延迟加载：是指构建一个可使用覆盖索引的子查询，通过覆盖索引先查出列的数据，再与自身关联返回需要的数据

      • 分页的深度越深，延迟的关联相比正常分页的性能就能更明显。但是仅仅使用单表查询

    • 辅助定位：将limit m，n查询转换为已知的位置的查询，每次查询的时候，从上一次分页查询中取出id最大值x，构建id>x

      SELECT cus.* from customer limit 100000,10;
      SELECT cus.* from customer where customer_id >'last_customer_id' limit 10

• 优化union合并

  • mysql执行union子查询总是创建并填充临时表，导致很多优化策略没法使用，经常需要把where limit 等放到子查询中，所以，尽可能使用union all代替union，需要过滤就在代码中去实现。

• 优化where

  • 关键在于建立合适的索引对数据进行过滤

  • 关联查询的where子句只能使用驱动表的索引

  • 根据区分度建立联合索引：对于区分度不高的，把高频使用的放前面；对于区分度高的，把区分度最高的放最前面

  • 结合order by列/group by列建立联合索引，例如where t1.a order by b可以考虑idxa_b；

  • 范围查询（in，like）的列放在联合索引的最后

  • 根据枚举拆分查询语句，并发查询提高性能