慢sql的优化思路

前言

之前写过mysql杂谈,原理偏多。今天主要是想聊聊慢sql的优化思路。第一步先用explain sql,查看一下执行计划,一般慢查询都会有type=all,extra=Using filesort,using temporary等,优化目标就是把这些干掉。

---

1.没有索引,先建索引,基本可以解决90%的慢sql问题

2.有索引,但没生效,需要排除失效原因

• 条件字段函数操作,如select a from t where month(date) = 10
• 隐式类型转换,如varchar字段a, select a from t where a = 1,这里的a会转成数值类型
• 隐式字符编码转换,如在join t on a=t.b的时候,a是utf8mb4字符集,b是utf8字符集,会发生将b转成utf8mb4的函数转换,所以b的索引会失效
• 不符合最左前缀原则,例如联合索引(a,b)中,没有a在where条件中,例如like查询 ‘%xx’
• 索引的区分度太低,例如在性别列上的索引,优化器会认为扫描行数太多从而选择全表扫描,事实上区分度低的索引可以删除掉的

3.索引生效了,但还不够快

• 禁用select * 这种写法,越少字段,显然网络传输越快,内存上,也可以占用更少join_buffer和sort_buffer等,从而提升查询效率
• 如果只是查询几个字段,可以利用覆盖索引的特性,将这几个字段建立联合索引,这样会避免回表,从而提升查询速度,如果用explian sql会在extra列看到Using index
• 如果是类似这样的语句where a='123' and b>13的,如果对(a,b)建立联合索引,这样可以利用索引下推的特性,直接在inodb层就完成判断从而加快查询,如果用explian sql会在extra列看到Using index condition

4.其它情况

order by

如果在order by语句出现了extra=Using filesort,说明mysql使用sort_buffer或者多个磁盘文件保存查询数据,再对数据进行快速排序,最后将排序后的结果返回客户端。这种情况我们可以利用索引的有序性,对排序字段建立索引,从而避免排序。
如果是联合索引(a,b,c),要注意索引字段是依次有序的,要符合最左前缀原则才行。

• 例如select a,b,c where a='' and b='' order by c,a和b查询条件不可少
• 例如按创建时间排序order by create_time desc,可以用order by id desc,这样利用上了主键索引
• 例如select a,b,c where a='' or b='' order by c 可以改成 select a,b,c where a='' order by c 和 select a,b,c where b='' order by c利用索引有序性,然后再对两个结果集处理

group by

如果在group by语句出现了extra=Using temporary; Using filesort,说明mysql使用了内存临时表存储中间数据,如果数据大小超过tmp_table_size,那么会转成磁盘临时表,最后对结果排序。所以我们可以做几点优化措施

• 对group by的字段建立索引,从而避免临时表和排序
• 没有排序要求,可以后面加 order by null,取消排序
• 如果数据实在太大,可以加SQL_BIG_RESULT,让优化器直接排序

join

如果jion语句中出现Using where; Using join buffer (Block Nested Loop),说明mysql开辟join_buffer大小的内存空间,使用了BNL算法,这种算法代价高。我们需要对被关联表的字段建立索引,从而使mysql使用NLJ算法提高效率。另外,无论是BNL还是NLJ算法,都是小表作为驱动表会得到更高的查询效率。

---

小结

其实sql优化中并没有什么魔法，多用用explian和show WARNINGS看看执行计划,无论是建立索引和调整参数(sort_buffer,join_buffer,tmp_table_size等),说到底也只是空间换时间。现实中更多的时候是需要我们深入业务系统,根据实际情况修改逻辑和改写sql从而提高查询效率。如果数据量实在太多，我们可以通过拆分历史表的方式减低查询数据量或者引入Hadoop大数据体系来处理数据。