前言
数据库很重要！很重要！很重要！ 重要的事情说三遍。所以单独用一篇来讲述SQL怎么优化。不过这里说到一点，不建议在业务代码里写很多复杂业务SQL，基本尽可能的减少 join,子查询 等，也就说尽量在应用层来解决问题，降低产生低效SQL的概率，数据库只是完成数据存储及最简单查询的组件。
SQL优化
主要4个方向，以下4个方向尽可能达到了，SQL的执行效率就提高了。
避免全表扫描
SQL中尽可能不使用临时表
减小查询中间结果集大小
尽可能命中索引
发现慢SQL
DBA开启MySQL的慢查询日志，对每日数据库慢查询进行监控。慢查询后每日汇总提供开发进行处理。DBA给出指导意见。
分析执行计划
主要看对SQL的执行过程中
explain [extended] select … from … where …
得到结果是
+—-+————-+——-+——-+——————-+———+———+——-+——+——-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——-+——————-+———+———+——-+——+——-+
其中 table 表示是哪个表的数据。
type比较重要。表示链接的类型。链接类型由好到坏的，依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般情况，至少要达到 range 级别，最好是 ref 级别。否则可能会有性能问题。
possible_keys 是指可以应用到该表的索引，如果为NULL则没有。
key 是指用到的索引。
key_len 是索引的长度，在不影响查询精度的情况下，值越小越好。
ref 是指索引的那一列被使用了。一般会是个常数。
rows 是指有多少行。
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);
总结
数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。
思考题
万一经过SQL优化后，还是达不到要求，还有什么手段能进行优化呢？
在一个既有系统前期业务快速迭代，导致系统很多业务已经写了很多低效的SQL，导致系统运行缓慢，领导需要快速解决运行缓慢的问题，有哪些手段可以用呢？
共同学习，写下你的评论
评论加载中...
作者其他优质文章
