mysql select 慢查询优化 2018-07-02 18:24

  • 场景:最近sql报警中比较多,在对慢查询优化,目前总结从以下几个方面进行优化

1、子查询都变成用join的方式, join方式的表的字段为同样类型, 且加上索引,查询时会快

2、 where中没有使用该索引字段, order by (或group by) 中不会使用索引。
3、避免使用select * 来返回全部内容,可以返回需要的字段。
4、在left join两个表时,on的字段上添加索引会加快访问速度,但是要求,left join两遍的索引字段类型一致。
5、如果mysql自带执行计划使用索引不合理,可以使用mysql hint ,mysql force index,来指定使用哪个索引。
6、安装mysql的时候查询是默认会有缓存的,我们在explain某个select 查询的时候,往往是第一次执行的会很慢,而第二次三次 后面的会执行很快,是因为mysql有自己的缓存机制,所以在慢查询优化的时候,最好查询时不使用缓存,可以在select 前面加上SQL_NO_CACHE 或者通过清空缓存的方式.
例如:

-- 不使用缓存
select SQL_NO_CACHE count(*) from fp_data;
-- 使用缓存
select SQL_CACHE count(*) from fp_data;
-- 清空缓存
reset query cache;

7、查询关联的表最多不超过三个,当关联表过多时,可以分页查询或者把某些字段合并到一个表中。
8、复合字段索引查询,最好要符合最左匹配原则。(A B C 生效的之后 A、AB、ABC)

9、表中的字段类型,符合业务的要求同时也在数据库这加以控制,比如int 默认长度为11,是代表字段的长度,如果是主键自增可以设置为无符号类型。 例如status字段只能是0,1,2,tinyint类型,当长度为1的时候可以代表true或者false,长度为tinyint(2)的时候代表具体的值。

10、表中最好添加created_time(默认:CURRENT_TIMESTAMP)、updated_time(默认CURRENT_TIMESTAMP且根据当前时间戳更新), 这样对表的操作有记录,知道最后更改表的时间。

11、查询缓慢有时候可能是表的碎片空间占用太大,所以必要时可以清除表的碎片空间。

12、如果查询in中的数字是连续的,可以用between and来代替。

13、最好是单表查询, 大sql拆成小sql, 可以用java在内存中操作,可以用java来实现, 减轻mysql IO次数。

14、对数据量过大表且查询频繁,改动不频繁的话,可以加上第三方缓存机制,这样在内存中读取要比在硬盘中读取快的多。

15、尽量扩展索引,而不是建立索引,例如表中已经索引a,现在要加(a,b),只需要修改原来索引即可。

16、可以放到从库中查询的, 可以主从分离,主库主要用来写, 从库用来查询, 所以尽量把大的查询放到从库中。

17、一些规则并不是万能的,还是要看在线上执行的时间,有的时候是去掉索引反倒执行的快,所以手动执行变快了才是真正的快,也不一定 百分之百的看扫描行数,有的时候扫描行数虽然多,但是执行的时间并不慢。相反的,有的时候可能执行计划中rows列中扫描行数并很少但是执行 时间可能高,所以还是要根据实际执行情况来进行优化。

  • 优化示例

由于是线上的数据,以及表结构, 就先不列举了,再优化的时候一般是会先考虑这些点。 以后再慢慢补充。