目录

ThasBlog

学无止境

X

SQL 优化的几种方式

高性能索引

建立三星索引:

一星:根据索引能够确定一段较小的范围,散列好的字段靠前

二星:查询结果排序与索引排序一致,联合索引从做到右有序。

三星:宽索引,查询结果尽可能在索引中找到(覆盖索引),避免回表

索引使用注意点:

  1. 不要在索引列上做运算
  2. 尽可能全值匹配 (唯一确定一条索引
  3. 最左匹配原则
  4. 范围条件放最后
  5. 覆盖索引
  6. 不等于要慎用
  7. Null 和 Not Null, 尽可能不要使用null, null有很歧义
  8. like查询只能匹配做前缀
  9. 字符串要加引号
  10. or的所有条件都必须要有索引,否则会全表扫描
  11. 使用索引排序来做排序,从左到右依次有序,中间索引单独排列是无序的
  12. 使用自增主键,顺序插入(MySQL B+树特性)

移除无用的索引

插入或修改数据都需要修改索引,无用索引会浪费插入时的性能。

分页优化

分页查询时,offset的那部分依然会被查询到,只是在返回客户端时被丢弃了 (也就是查询到了大量不必要的数据),先根据条件查询 ID(不回表),再根据ID查询其他字段(子查询)。 此种办法会多物化出一个子查询结果集。

通过手动指定 MaxId 限定查询的起始位置。

查询降级

当查询无可避免的一定会查询超时,牺牲一部分查询数据保证服务可用。

当MySQL单表数据达到百万级别时, count全表会变得非常慢,而分页查询不会全表会全索引检索,所以查询时间还好,此时可以牺牲掉分页总数来保证服务可用。

复杂联表或聚合查询语句缓存为单个表

从结果上讲,有点类似于“视图”,但是视图是不持久化结果的。从方法讲,类似与索引,查询运算成本转嫁到插入阶段。

在插入语句时,构建查询结果,插入缓存表,查询时直接查询缓存表。

联表查询时,如果不是eq_ref的索引 (O(N)复杂度),会形成巨大的笛卡尔积 O(N1 * N2 * N3);

聚合查询时,如果要对聚合结果进行排序, 则先要运算出聚合结果, 再进行 file_sorting。

缓存

很多数据都是长时间不会变化的,使用缓存避免无用的查询,降低数据库qps(为其他查询让路)


标题:SQL 优化的几种方式
作者:thas
地址:https://thas.cc/articles/2021/07/04/1625395418760.html