如何写出高性能的MySQL查询

时间:2022-10-04 23:54:49

作者:会写代码的猪 原文链接

 

想写这样一篇文章很久了,但始终没有下手。最近帮同事看了几个查询,而且自己也在考虑一个索引系统的问题,所以今天就把这个写了。介绍一下 MySQL的索引机制,还有一些MySQL查询的优化策略。鄙人才疏学浅,很可能说的不对,请路过的各位大侠批评指正,献丑了。

首先,说说 MySQL的索引存储方式。MySQL的索引一般是B-Tree的结构存储的,内存表也有Hash索引,但是内存表的出镜率似乎已经低到了用“可怜”来形 容的程度,所以我们只考虑B-Tree索引。
然后说说MySQL的联合索引。联合索引对于一个DBMS总是非常重要的,因为每一条SQL语句的条 件子句是单条件的可能性很小,大多数情况下为组合条件,因此对于组合索引的依赖也就很强。MySQL对于联合索引的创建规则通过一个例子说明:
对 于一个在列:col_a, col_b和col_c上的联合索引,MySQL会建立

index(col_a), index(col_a, col_b) 和index(col_a, col_b, col_c)

这样三个索引。

介绍完一些基本原理,我们来看MySQL对于索引的 选取规则和索引的建立原则(这些规则都是个人总结的,多来源于互联网,也有自己的经验)
对 于单个索引,一般来说MySQL的查询优化器总能在若干查询条件中选取效率较高的一个使用,所以不必投入太多精力,一般来说查询容易出现的问题容易出现在 联合索引。这里以一个两列的索引为例,说明一些问题。
例如:

idx_a_b (col_a, col_b)

建 立做和索引的列进行or组合不可使用索引
例如:有条件

col_a = val_a or col_b = val_b

这 个条件,是不可以使用idx_a_b索引的。然而同样的查询对于却可以使用这样的索引idx_a(col_a)或者idx_b(col_b)的,因此在建 立索引的时候就要考虑到出镜率最高的条件是什么,建立怎样的索引。而如果同时存在idx_a和idx_b两个索引的话,MySQL也只会选择一个使用,尽 可能使用索引把结果集缩小,再在这个结果集中遍历,使用其他条件筛选。
联合索引对非前缀列不生效
例 如:条件col_b = val_b这个条件是不会使用这个索引的,因为索引idx_a_b的前缀列是col_a。因此在建索引的时候,就要注意到,是否有很多使用这种条件的查 询,需要为col_b单独建立索引。
对于组合索引,遇到范围查询则放弃使用剩余部分
例如:条件

col_a = val_a and col_b = val_b

是 可以使用整个索引,而对于

col_a between val_a_left and val_a_right and col_b = val_b

这 个条件,只会使用索引的col_a这一部分,不会使用整个索引。对于这样的查询,我们有一个优化策略,若col_a是一个离散变量,则建议使用in代替 between,例如,

col_a between 1 and 5 and col_b = val_b

建议写成

col_a in (1, 2, 3, 4, 5) and col_b = val_b

这 样是可以使用整个idx_a_b的。

现在能想到的对于组合索引的使用就这些,还远远不够全面,不过了解了这些原理,一般的查询都是可以分析 的。接下来介绍几个策略性的SQL优化。
尽量少的选择列数
选择你需要的列,不用图省事就直接写个 select *,一来是为了减少通信的开销,再有就是如果你所选的列,都建立索引,那么这次查询就不会对表数据进行任何操作,只查索引,就返回。
减 少count(*), group by, distinct这样的操作
这三种操作将进行大量的计算,对数据库服务器造成很大 压力,而且很慢。这样的查询能避免就避免,能缓存就缓存。
对于limit offset,若offset值较大,则采用分割结果集策略
limit offset操作一般用于翻页,当offset值较小的时候直接使用limit offset效率搞,但当offset值增大到一定程度,这个查询效率就会骤然降低。建议在大offset的情况下,采取这个策略:缓存上一次结果的尾数 据,在新的查询中不使用offset,直接根据缓存结果进行查询。
例如:

select * from tb limit 100 offset 500000;

offset 值很大,建议这样做:缓存上一次结果的主键值id=id_val,sql改写为:

select * from tb where id>id_val order by id limit 100

这 条sql的效率将比上一条高很多倍。

策略性的优化也姑且先想到了这么几条,很不全面。综合上面的这些查询优化策略,我们还有几个提高性能的 系统配置和管理策略。例如:
定期重建索引
一张表用的时间久了,数据频繁更新,索引碎片会很多,降低查 询效率,重建索引可以整理这些碎片,大大提高查询和写入的效率。
配置恰当的query_buffer
如 果你的机器有足够大的内存,那多分给MySQL一点吧,在一台8G内存的机器上,我们一般会分给MySQL 4到6个G,query的缓存会给你带来惊喜的。
选择恰当的引擎
常用的MySQL引擎有Innodb 和MyISAM,前者更稳定且支持行级锁,后者处理一般查询效率更高。二者各有特点,一般我们会使用主从策略,主Innodb,从MyISAM的做法。
在 恰当的时候分表或分库
MySQL很强大,但对于200到300万以上的数据进行处理,性能就开始有明显的下降了,因此一般到这 个数量级,就建议拆分数据了。
别让查询链接阻塞
MySQL可以配置连接的超时时间,这个时间如果做得 太长,甚至到了10min,那么很可能发生这种情况,3000个链接都被占满而且sleep在哪,新链接进不来,导致无法正常服务。因此这个配置尽量配置 一个符合逻辑的值,60s或者120s等等。

当下能想到的也就是这些~~略显肤浅,不过就写到这里吧,希望可以抛砖引玉,给大家一个优化 MySQL的建议。