最近在做一些SQL优化的事情,在这里记录一些SQL优化的过程与想法,希望可以给其他遇到同样问题的人一些思路与灵感。
- 表结构优化
- 表结构优化一般在建表初期,这样不需要处理历史数据。
- 有text类型或者特别长的varchar类型字段,并且需要查询的,如设备号、长链url等,可以加前缀索引来解决,不过还有更高效的方法:单独加一列用MD5或者crc32映射长串,推荐crc32,因为速度更快。
- crc32字段加索引,查询的时候先根据crc32来查询,为了防止误判,再比较一次原串。
- 如果db选择索引错误(可能查询条件众多,索引也很多),没有用区分度最高的索引来查询,可以选择用子查询/临时表(如with语句)的方式来解决。
- 单纯加索引解决
- 关系表,有查询关系的SQL:select * from relationships where user_id = ? and relation_type = ? and updated_time < ? order by updated_time desc limit ?
- 未加索引,加索引解决。(user_id+relation_type+updated_time联合索引,在user_id和relation_type确定时,updated_time也是有序的,同时也减少了数据库排序操作)
- 很多时候我们加联合索引的顺序有多种选择,这时候可以按照区分度来排序,区分度越高的字段越靠前。如何判定区分度,可以用全表distinct该列的值x,然后除以表中总数y,x/y的比值越接近1区分度越大。
- 将db的负担转移到应用
- 关系表,有批量查询关系的SQL:select * from relationships where user_id=? and relation_type = ANY(?) and updated_time>=? order by updated_time desc offset ? limit ?
- 之前是加了user_id+relation_type+updated_time的联合索引,但是此时relation_type是一个数组, 也可以使用这个索引,但是db需要进行快排,因为relation_type是数组的情况下,updated_time不是有序的。数据量比较大时,db快排花的时间和内存都比较多。
- 优化这个SQL,可以将SQL拆为单个user_id和relation_type的SQL(类似上一个SQL),这样就能使用索引,且不需要排序,然后在应用层排序(实际上合并就行了)。
- 拆为单个SQL时,可以串行查询,也可以并行查询,并行要控制并行数量。
- 拆分的好处是能减少db的压力,将压力转移到方便横向拓展的应用层。
- 同理其它的一些子查询、group by等SQL,也可以考虑这样优化。
- 没有使用预想的索引
- token表,存储了用户所有的token,有查询用户最新token的SQL:select * from user_access_tokens where user_id=? and token_type = 'default' order by id desc limit 1
- 表中是有(user_id,access_token)联合索引的,理论上来说,db会选择这个索引。然而小概率情况下,在某个user_id的token特别多时,且有order by id desc、token_type的干扰,db选择可能会选择主键索引,导致全表扫描。
- 为了防止db选择id索引,我们可以强制使用某个索引,如MySQL可以使用force index,而我们使用的是postgresql,后来找到一个巧妙的方法: order by id+0 desc,避免了pg使用主键索引,主要原理是id+0是一个表达式,db不会在表达式上使用索引。
- 同时提醒我们,字段尽量不要使用表达式,可能导致db选择索引错误,如where id+1=3类似的,直接id=3-1即可。
- IN优化,大拆小
- 在慢SQL日志中,经常发现大批量的in()查询语句,in中的数据成千上万个,db的负担很重。
- 一般in语句,都限制最大查询数量,如每次最多500个,如果要查3000的数据,可以分成6批,这6批可以依次串行查询,也可以分6个线程并行查询,然后合并。但是在并行查询时,要控制同时并行查询的线程数量,防止给db较多的连接压力。
- 小合大,单条合成批量查询
- 上面说了大拆小,现在有小查询合并为一个大查询的优化。
-
业务中常见一些写法:
var result []Result
for _, item := range items {
curData := db.Query(item)
result = append(result, curData)
}
即在循环内执行单次查询,当循环次数很多时,会耗费很多db与应用之间的io时间,对io极不友好。建议改为一次性批量查询,逻辑同上方`大拆小`。
- SQL难以优化,或使用索引依然很慢
- 从业务角度分析,是否能改SQL?
- 如某些业务一次性获取全量数据,是否可以改为分页获取,每次获取少量数据,通过offset来分页。
- 是否需要精确值?能否数据量少时展示精确值,数据量大就展示近似值?如count(*)语句,可以使用explain的近似值。
- 从业务角度分析某些难查的条件或者排序能否去掉?
- 加缓存,由于引入缓存,可能极小概率下有缓存和db不一致的问题,在缓存过期后变一致,业务上能接受就好。
- redis缓存
- 查询时先查缓存,查不到查db,然后写缓存,设置合理的过期时间。
- 有更新写入时,删缓存。
- 本地缓存
- 同redis缓存基本一致。
- redis缓存
- 表结构修改
- 能否通过冗余字段,来避免一些join、避免一些计算。
- 将查询的压力分摊到每次更新处,更新的时候就计算好,查询时少量计算或直接使用。
- 架构优化
- 前面说的都是表结构以及索引的优化,如果说慢SQL导致db压力过大,影响了实际业务,可以从架构层面考虑解决
- 主从分离,读写分离。实际业务中应该大部分都是读,读写分离是必要的,且一个主库支持挂载多个从库。读有瓶颈时,从库需要支持拓展。
- 分库分表。单表数据量大或者负载压力过大时,可以考虑分库分表。
- 单表字段过多可以考虑纵向分表,拓展表。
- 数据量大时,可以根据某个字段hash分表,或者根据时间分表。
- 业务隔离。
- 不同业务之间最好不要使用同一个数据库实例,减少耦合,增加隔离,这样出问题时不会相互影响。
- 前面说的都是表结构以及索引的优化,如果说慢SQL导致db压力过大,影响了实际业务,可以从架构层面考虑解决
- 其它
- db查询建议设置超时时间,超时被cancel掉,可减少慢SQL积压
- 从业务角度分析,是否能改SQL?