先分析查询速度慢的原因
- 记录忙查询日志
先开启慢查询日志
slow-query-log=on //开启查询日志
slow-query-log-file=/path/to/somefile //保存的文件
long-query-time=time //执行超过多久被标记为慢查询
log-queries-not-using-indexes= //记录那些没有使用索引的查询
对于生成的慢查询日志可以使用mysqldumpslow或者pt-query-digest工具进行分析 - 使用mysql 命令show profile 实时观察sql语句执行的时间
- 使用explain 生成执行计划,分析单条sql语句是否有使用索引
优化查询过程中的数据访问
- 访问数据太多导致查询性能下降
例如有一张大订单表,现在需要查询一月份的所有订单记录,我们可以进行分批查询,分4次进行查询,每次查询一个星期的订单。 - 查询返回了过多对需要无意义的数据
有时候查询到的数据只有前几行数据是需要使用的,可以使用limit只返回前几行的数据。避免使用select * 返回字段,按需指定需要使用的字段。 - 使用覆盖索引
索引的字段覆盖了sql语句中需要使用的字段,能够直接重索引中返回需要的信息,无需再回源表中进行查询,避免了二次查询。 - 避免重复查询
对于一些对实时性要求不高的sql查询,可以将第一次查询的结果保存到缓存中,下次进行查询的时候能够直接重缓存中命中。
优化长难的查询语句
- 尽可能少查询,必要时将大查询分解成多个小查询
例如某条sql需要更新10W条数据,我可以拆分成10次来进行,每次更新1W条数据,这样能够减少服务器的负担、行锁的竞争。 - 分解关联查询
可以将一条关联擦好像分解成多条sql来执行,让缓存的效率更高,执行单个查询可以减少锁的竞争,在应用层做关联可以更容易对数据库进行拆分
优化特定类型的查询
- 优化count()
count(*)会忽略所有列,直接统计所有列数,不要使用count(列名)。
增加一张表来专门做审计工作。 - 优化关联查询
确保join 子句中 on 条件上有使用索引。
确保where , group by ,ordrer by 的字段都来自主表,因为mysql在进行联表查询时只会用到第一张表的索引。 - 优化子查询,改写成关联查询,mysql读嵌套子查询的优化差
- limit偏移量大的时候,查询效率低,可以记录上次查询的最大ID,下次查询时
- 优化union查询,使用union all 代替 union,如果需要去重,可以在应用层去重
- order by,不需要排序时使用 null,避免mysql服务器使用文件排序