系统优化怎么做-SQL优化

拼命工作好好玩 2019-06-28

前言

数据库很重要!很重要!很重要! 重要的事情说三遍。所以单独用一篇来讲述SQL怎么优化。这里提前说到一点,不建议在业务代码里写很多复杂业务SQL,基本尽可能的减少 join,子查询 等,也就说尽量在应用层来解决问题,降低产生低效SQL的概率,数据库只是完成数据存储及最简单查询的组件。

SQL优化

主要4个方向,以下4个方向尽可能达到了,SQL的执行效率就提高了。

  1. 避免全表扫描
  2. SQL中尽可能不使用临时表
  3. 减小查询中间结果集大小
  4. 尽可能命中索引

发现慢SQL

DBA开启MySQL的慢查询日志,对每日数据库慢查询进行监控。慢查询后每日汇总提供开发进行处理。DBA给出指导意见。

分析执行计划

主要看对SQL的执行过程中

explain [extended] select … from … where …

得到结果是

+—-+————-+——-+——-+————-+——-+——-+——————-+———+———+——-+-+——-+——-+—-+———-+—————+———-+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+—-+————-+——-+——-+————-+——-+——-+——————-+———+———+——-+-+——-+——-+—-+———-+—————+———-+———+———+——-+——+——-+

其中 table 表示是哪个表的数据。

  1. type比较重要。表示链接的类型。链接类型由好到坏的,依次是 system > const > eq_ref > ref >
    fulltext > ref_or_null > index_merge > unique_subquery >
    index_subquery > range > index > ALL 一般情况,至少要达到 range 级别,最好是 ref
    级别。否则可能会有性能问题。
  2. possible_keys 是指可以应用到该表的索引,如果为NULL则没有。
  3. key 是指用到的索引。
  4. key_len 是索引的长度,在不影响查询精度的情况下,值越小越好。
  5. ref 是指索引的那一列被使用了。一般会是个常数。
  6. rows 是指有多少行。
  7. extra 是指额外的信息。也是比较重要的。
如果值为 distinct ,说明mysql 找到了域行联合匹配的行,就不再查找了。
如果值为 not exits : mysql优化了 left join ,一旦找到了 left join 匹配的行,便不再进行搜索了。
如果值为 rang checked for each : 没有找到理想的索引。
如果为 using filesort ,则需要改进sql了。这说明 mysql执行 需要 文件排序。这是比较影响效率的。
如果为 using temporary , 这是使用了 临时表。 这种情况也比较影响效率,sql需要改进。或者从应用层进行改进。
如果为 where used 说明使用了where语句。如果 type为 all 或者 index ,一般会出现这样的结果。这样的问题,一般是查询需要改进。

SQL优化实例

分页查询

第一种分页写法

select * 
  from t
 where thread_id = 771025
   and deleted = 0
 order by gmt_create asc limit 0, 15;

原理:

一次性根据过滤条件取出所有字段进行排序返回。

数据访问开销=索引IO + 索引全部记录结果对应的表数据IO

缺点:

该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。

第二种分页写法:

select t.* from (
     select id from t
     where 
     thread_id = 771025 
     and deleted = 0 order by gmt_create asc limit 0, 15) a, t 
 where a.id = t.id;

前提:

假设t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)

原理:

先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。

数据访问开销=索引IO+索引分页后结果对应的表数据IO

优点:

每次翻页消耗的资源和时间都基本相同,就像翻第一页一样

适用场景:

当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用

批量SQL

减少和数据库交互次数
INSERT ... ON DUPLICATE KEY UPDATE
 REPLACE INTO
 INSERT IGNORE
 INSERT INTO VALUES()
  • 对同一个表的多次alter操作必须合并为一次操作。
  • mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引,
    通常的做法分为两步:
alter table t add column b varchar(10);

然后增加索引:

alter table t add index idx_aa(aa);

正确的做法是:

alter table t add column b varchar(10),add index idx_aa(aa);

总结

数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。

思考题

  1. 万一经过SQL优化后,还是达不到要求,还有什么手段能进行优化呢?
  2. 在一个既有系统前期业务快速迭代,导致系统很多业务已经写了很多低效的SQL,导致系统运行缓慢,领导需要快速解决运行缓慢的问题,有哪些手段可以用呢?

相关推荐