关于mysql查询优化的再思考

关于mysql查询优化的再思考

之前写过一篇关于mysql的优化思考的文章,mysql 语句优化的十个经验,现在想来过了几年,自己平时接触过的优化问题也有好几个了,是时候继续写一篇新的文章.

关于索引命中

很多时候对于短查询(where语句只有几条,不存在表关联),只要命中一个索引,速度就会很快,同时对于小表就算不命中,基本上也无关紧要(数量集问题后面会提到)
但是对于绝大数使用的短查询知道基本上都会命中索引(除非表没有索引).因此真正容易出问题的往往是多表联查,很多情况下,复杂的on语句下索引命中往往是随缘的(也不能说随缘,而是对于非dba的开发,索引的优化机制是黑盒,只能尽量凭借经验去猜着写),很多你认为会命中,往往不会命中,这时候使用explain往往是优势.但是我也遇到过在测试环境命中,而在正式环境不命中的情况,猜测是不同的数量下,优化机制不同.

联合索引

联合索引的一旦命中,速度会提升很快,但是联合索引的命中规则是左向命中的,所以很多时候会限制写的人的写法(起码在我当前的5.7是这样,不知道之后的mysql是否会优化)

慢查询的使用

开启慢查询和配合各类日志能很快找出出现问题的查询语句.尤其是对于那些经常出现的慢查询.

不过开发中也要有成本概念.比如我们现在的很多慢查询都是凌晨生成查询日志的生成的.这些慢查询的出现其实是在我们预估的范围内,没必要投入开发成本去优化.比如我之前提到这一篇mysql关联的索引命中.md就是经常发生,有必要特地去优化的查询.虽然花了几个小时去查询和优化,但是是值得的.

数量级

代码开发中,数量级是一个常用的概念,比如对于后台,每一页都有20条,每一条我都用短查询,虽然有20*n的短查询,但是和某些不命中索引的关联查询比起来速度上并没有明显的区别,写起来反而会更快一点.出现问题也更容易查找.

再比如我之前我上文提到的优化里面,因为大小表驱动的问题,关联查询中,大小表驱动下,一旦没有命中索引,生成几百万条的全表查询,速度就会变得极其感人.这也是很多文章里面经常提到的大小表驱动问题的原因.

权衡开发时间,出现频率也可以算是数量级的一个概念,把一堆长查询放到缓存中,之后避免重新查询也算是一个常用的优化手段.

切分查询

有时候对于一个大的查询我们需要分而治之,每一个查询都完全一样,每次只返回一小部分.
比如我们需要删除某个月的消息,一次性delete 对于服务器而言压力很大,我们可以每次只删除10000条,循环处理,大大降低对于服务器的影响,同事降低删除时锁的持有时间.

同时避免一次性访问过多的数据,也可以有效避免数量级,我之前提到过尽量避免使用*,这一点对于多表查询下,没有limit时候尤其重要,大数量集下,只发送1/4的数据,也可以大大节约时间.

在编程语言内处理

我之前文章提到的,很多情况下,把数据提取出来放到编程语言里面处理,也是优势,之前帮实习生优化代码就有一个这个问题.实习生的逻辑是把数据处理和检查都放到了mysql里面,要花大量的时间精力去研究如何命中索引.我给的经验是,用一个短的查询,对一个表进行处理,用yii里面index语句,把之前的数据提取出来清洗掉无用的数据,然后把剩下的十几条有用的数据再去查询数据库,这样速度就提升到了20倍.
浪费了少量的cpu和内存资源.大大减少了数据库的压力和开发成本.