mysql 概率查询,MySQL查询优化

认真写文章，用心做分享。

公众号：xy的技术圈

为什么查询会慢？

查询慢的原因可能很多，比如网络原因、锁争用等等。但最基本的原因可能是访问的数据太多。要么可能访问了太多的行，要么可能访问了太多的列，要么可能MySQL在查询的时候需要分析大量超过需要的行。

查询优化

一个查询根据不同的场景，一般可以通过下面的方式来进行优化：

使用COUNT *

在使用COUNT统计行数时，COUNT(*)是性能最高的。

使用LIMIT

有时候查询并不需要返回所有的结果集，这时候可以加LIMIT限制返回多少。这在分页的场景下很常见。

MySQL在进行UNION操作的时候，如果把LIMIT写在外查询里，会先将两个子查询的所有数据放在临时表，然后取前xx行。这样性能其实是比较低的，更推荐的做法是把LIMIT同时也到子查询里。

有时候可以使用LIMIT 1来代替MIN和MAX函数。因为这两个函数需要全表扫描。

不要查询所有列

有时候为了方便，会直接写SELECT *来查询所有列的数据。但可能使用的时候不一定要使用到所有列。这个时候可以只查询部分需要用到的列。

但有时候可能也有例外，因为这样做确实可以简化开发，而且有时候应用程序里面可能会有缓存机制，这样多个查询可能可以复用这个缓存。

经常查询的列建立索引

在前面的文章《MySQL索引原理》及《MySQL索引使用策略和优化》中，我们介绍了MySQL的索引。在经常查询的列上面建立索引，可以让MySQL尽可能地扫描少的行。

但这里也需要注意，维护索引是有代价的，所以需要自己去权衡时候使用索引。还有一点就是MySQL的索引是基于“最左匹配”原则的。详情可以参考之前的文章。

分解复杂查询

有时候一个复杂查询可能会“很大”，需要扫描许多行，关联很多表。这个时候可以把它分解成很多个小的查询。这样做有很多好处：

让缓存的效率更高

执行单个查询可以减少锁的竞争

更容易对数据库进行拆分

减少冗余记录的查询：有时候关联查询可能会重复查询同样的数据行。

当然，分解后也会带来弊端，就是发生的查询数量多了，MySQL的连接数就多了，可能会带来网络上的一些开销。但MySQL一般是放在内网的，网络一般会很快，所以不会有太大的影响。

反范式设计数据表

在设计数据表的时候，为了性能上的优化，可以反范式地设计表。这样可以减小表的关联，加快查询的速度。

使用IN

MySQL在查询的时候使用IN()时，会先将IN列表中的数据进行排序，然后通过二分查找的方式来确定列表中的值时候满足条件，这是一个O(log n)复杂度的操作，而如果是OR的话，复杂度是O(n)，所以如果对IN列表中有大量取值的时候，使用IN会更快一些。

但尽量不要使用IN加子查询。因为MySQL会将相关的外层表压到子查询中，效率很低。如果是需要用到IN加子查询的时候，建议使用EXISTS()等效查询来做。

不使用OFFSET

OFFSET在分页的时候很常见。一旦页码过大，OFFSET和导致MySQL扫描大量不需要的行然后丢掉。那如何解决这个问题呢？有两种方式。

第一种方式是从前端就限制了用户不能直接访问太大的页码。比如百度搜索结果下面的分页，限制了用户很难访问到很大的页码。

第二种方式是使用主键来辅助。比如上一页返回的id的1000，那下一页就可以直接WHERE查找id大于1000且小于1020。这样查的时候就可以不用OFFSET了，MySQL也只需要扫描很少的行。但这样有一个弊端，就是有些数据可能被删掉了，那每页的范围和数量就不太好控制。

总结

总的来说，主要是针对MySQL本身的查询流程和引擎实现来做优化。但实际我们开发程序的时候，在应用层可以做缓存来优化性能，这样可以大大减少MySQL的请求和查询次数。

另一方面，MySQL的索引非常重要，理解索引的原理，创建合适的索引可以极大地提升查询的性能。使用索引的主要好处就是可以在查询的时候扫描更少的行。

更多文章，关注公众号：xy的技术圈