常见SQL优化的方法。
- 优化数据访问: limit 子句缩减数据行数、避免select *
- 拆分查询:分而治之的思想,将一个大查询拆分多个小查询,每个小查询只返回一部分查询结果。
- 覆盖索引:当索引中的列包含所有查询中需要使用的列的时候,可以避免回表
- 避免索引失效:检查SQL是否因为写的不合理,导致索引失效。
- 分解联表查询:让业务层分多个查询来聚合,或者增加冗余字段减少联表查询
- 排序优化: 对于有排序场景,如果extra显示filesort,这时候就需要考虑对排序的字段建立索引,避免文件排序
回答
- 优化数据访问:要先确认这条查询语句是否查询了不必要的数据行,可以通过limit子句来缩减查询返回的数据行数,如果查询语句用了select*,需要改进SQL语句,只返回需要查询的列。
- 切分查询:针对一个大查询可以拆分多个小查询,每个小查询只返回一部分查询数据。比如删除一千万行数据,可以改进成分批删除,每一-次只删除一批数据,然后睡眠-下,再删除下一-批,这样可以将一次性的压力分散到一个很长的时间段中,不仅可以降低对服务器的性能影响,还可以大大减少删除时锁的持续时间。
- 覆盖索引:如果没有索引字段的话,就需要考虑建立索引,或者建立联合索引,通过覆盖索引的查询,这样就避免回表查询,可以提高查询性能。
- 避免索引失效:检查SQL语句有没有问题,比如对索引进行了计算和函数操作、联合索引没有遵循最左匹配原则等,这些场景都会导致索引失效,这时候就需要修改SQL避免索引失效的发生。
- 分解联表查询:针对联表查询的SQL语句,可以将联表查询分解成多个单表查询的语句,然后在业务层来聚合数据,或者增加冗余字段减少联表查询。
- 排序优化:针对order by排序操作,如果执行计划的extra 显示了文件排序,这时候我们可以对排序字段和其他字段建立联合索引,因为索引数据是天然有序的,这样对索引字段进行排序操作的时候,就不需要文件排序了,提高了查询性能。
如果SQL和索引都没问题,查询还是很慢怎么办?
需要发散下思维,往架构方向思考。
- 分批查询:针对一个大查询可以拆分多个小查询,每个小查询只返回-部分查询数据。
- 增加缓存: 针对频繁读取的热点数据,我们可以放到Redis缓存,避免每次都要请求MySQL。
- 分表:如果表的数据量很大,比如表数据千万级别了,这时候可以考虑分表了,通过减少每次查询数据总量来解决数据查询缓慢的问题。
- 主从复制: 针对读多写少的场景,我们可以搭建MySQL主从模式来分摊读请求的流量。
- 分库:针对写多读少的场景,单库的性能无法抗住高并发流量,就需要进行分库,把并发请求分散到多个实例中去。