SQL优化

最近在做一些SQL优化的事情，在这里记录一些SQL优化的过程与想法，希望可以给其他遇到同样问题的人一些思路与灵感。

1. 表结构优化
   1. 表结构优化一般在建表初期，这样不需要处理历史数据。
   2. 有text类型或者特别长的varchar类型字段，并且需要查询的，如设备号、长链url等，可以加前缀索引来解决，不过还有更高效的方法：单独加一列用MD5或者crc32映射长串，推荐crc32，因为速度更快。
   3. crc32字段加索引，查询的时候先根据crc32来查询，为了防止误判，再比较一次原串。
   4. 如果db选择索引错误（可能查询条件众多，索引也很多），没有用区分度最高的索引来查询，可以选择用子查询/临时表(如with语句)的方式来解决。
2. 单纯加索引解决
   1. 关系表，有查询关系的SQL：select * from relationships where user_id = ? and relation_type = ? and updated_time < ? order by updated_time desc limit ?
   2. 未加索引，加索引解决。（user_id+relation_type+updated_time联合索引，在user_id和relation_type确定时，updated_time也是有序的，同时也减少了数据库排序操作）
   3. 很多时候我们加联合索引的顺序有多种选择，这时候可以按照区分度来排序，区分度越高的字段越靠前。如何判定区分度，可以用全表distinct该列的值x，然后除以表中总数y，x/y的比值越接近1区分度越大。
3. 将db的负担转移到应用
   1. 关系表，有批量查询关系的SQL：select * from relationships where user_id=? and relation_type = ANY(?) and updated_time>=? order by updated_time desc offset ? limit ?
   2. 之前是加了user_id+relation_type+updated_time的联合索引，但是此时relation_type是一个数组, 也可以使用这个索引，但是db需要进行快排，因为relation_type是数组的情况下，updated_time不是有序的。数据量比较大时，db快排花的时间和内存都比较多。
   3. 优化这个SQL，可以将SQL拆为单个user_id和relation_type的SQL（类似上一个SQL），这样就能使用索引，且不需要排序，然后在应用层排序（实际上合并就行了）。
   4. 拆为单个SQL时，可以串行查询，也可以并行查询，并行要控制并行数量。
   5. 拆分的好处是能减少db的压力，将压力转移到方便横向拓展的应用层。
   6. 同理其它的一些子查询、group by等SQL，也可以考虑这样优化。
4. 没有使用预想的索引
   1. token表，存储了用户所有的token，有查询用户最新token的SQL：select * from user_access_tokens where user_id=? and token_type = 'default' order by id desc limit 1
   2. 表中是有（user_id，access_token）联合索引的，理论上来说，db会选择这个索引。然而小概率情况下，在某个user_id的token特别多时，且有order by id desc、token_type的干扰，db选择可能会选择主键索引，导致全表扫描。
   3. 为了防止db选择id索引，我们可以强制使用某个索引，如MySQL可以使用force index，而我们使用的是postgresql，后来找到一个巧妙的方法: order by id+0 desc，避免了pg使用主键索引，主要原理是id+0是一个表达式，db不会在表达式上使用索引。
   4. 同时提醒我们，字段尽量不要使用表达式，可能导致db选择索引错误，如where id+1=3类似的，直接id=3-1即可。
5. IN优化，大拆小
   1. 在慢SQL日志中，经常发现大批量的in()查询语句，in中的数据成千上万个，db的负担很重。
   2. 一般in语句，都限制最大查询数量，如每次最多500个，如果要查3000的数据，可以分成6批，这6批可以依次串行查询，也可以分6个线程并行查询，然后合并。但是在并行查询时，要控制同时并行查询的线程数量，防止给db较多的连接压力。
6. 小合大，单条合成批量查询
   1. 上面说了大拆小，现在有小查询合并为一个大查询的优化。

   2. 业务中常见一些写法：

      var result []Result for _, item := range items {     curData := db.Query(item)     result = append(result, curData) }

      即在循环内执行单次查询，当循环次数很多时，会耗费很多db与应用之间的io时间，对io极不友好。建议改为一次性批量查询，逻辑同上方`大拆小`。

7. SQL难以优化，或使用索引依然很慢
   1. 从业务角度分析，是否能改SQL？
      1. 如某些业务一次性获取全量数据，是否可以改为分页获取，每次获取少量数据，通过offset来分页。
      2. 是否需要精确值？能否数据量少时展示精确值，数据量大就展示近似值？如count(*)语句，可以使用explain的近似值。
      3. 从业务角度分析某些难查的条件或者排序能否去掉？
   2. 加缓存，由于引入缓存，可能极小概率下有缓存和db不一致的问题，在缓存过期后变一致，业务上能接受就好。
      1. redis缓存
         1. 查询时先查缓存，查不到查db，然后写缓存，设置合理的过期时间。
         2. 有更新写入时，删缓存。
      2. 本地缓存
         1. 同redis缓存基本一致。
   3. 表结构修改
      1. 能否通过冗余字段，来避免一些join、避免一些计算。
      2. 将查询的压力分摊到每次更新处，更新的时候就计算好，查询时少量计算或直接使用。
   4. 架构优化
      1. 前面说的都是表结构以及索引的优化，如果说慢SQL导致db压力过大，影响了实际业务，可以从架构层面考虑解决
         1. 主从分离，读写分离。实际业务中应该大部分都是读，读写分离是必要的，且一个主库支持挂载多个从库。读有瓶颈时，从库需要支持拓展。
         2. 分库分表。单表数据量大或者负载压力过大时，可以考虑分库分表。
            1. 单表字段过多可以考虑纵向分表，拓展表。
            2. 数据量大时，可以根据某个字段hash分表，或者根据时间分表。
         3. 业务隔离。
            1. 不同业务之间最好不要使用同一个数据库实例，减少耦合，增加隔离，这样出问题时不会相互影响。
   5. 其它
      1. db查询建议设置超时时间，超时被cancel掉，可减少慢SQL积压