所谓慢SQL，就是执行极慢的SQL语句。什么样的SQL语句是慢SQL？慢SQL到底有多慢？对于这类问题，并没有非常明确的标准，也没有界限。但这并不意味着很难区分正常SQL和慢速SQL。在大多数实际系统中，慢SQL消耗的数据库资源往往是普通SQL的几倍、几十倍，甚至上百倍。SQL还是很容易区分的。问题是我们不能等到系统上线，慢SQL耗尽了数据库的资源，再找出慢SQL来改进，这样会造成很多不良影响。那么，我们如何在开发阶段尽量避免编写慢SQL呢？01定量认识MySQL由此可见慢SQL对数据库的影响是一个从量变到质变的过程。“量”的把握对于开发者来说至关重要。重要的是要说。一个合格的程序员需要对数据库的能力有一个定量的了解。影响MySQL处理能力的因素有很多，比如服务器的配置、数据库中数据量的大小、MySQL的一些参数配置、数据库的繁忙程度等等。但是，一般情况下，这些因素对MySQL的性能和处理能力的影响，大概在一个数量级之内，也就是性能差距是几倍高几倍低。因此，我们不需要知道精确的性能数据，只要有一个大概的数量级就足以应付实际的开发工作了。目前，普通的MySQL数据库服务器的处理能力限制在每秒10000条左右的简单SQL。这里的“简单SQL”指的是不需要遍历很多条记录的SQL语句，类似于主键查询。根据配置级别的不同，服务器的处理能力也会有所不同。可能低配置的服务器只能达到几千/秒，高配置的服务器可以达到几万/秒，所以这里给出的10,000/秒Bars是中位经验值。考虑到普通系统不可能只有简单的SQL，实际处理能力会大大降低。我个人的经验是，一个MySQL数据库服务器平均每秒执行几百条SQL，一般已经很忙了。即使看起来CPU利用率和磁盘繁忙度不高，我们也需要考虑“卸载”数据库。另一个重要的量化指标是慢SQL有多慢才算是慢SQL？这里的“慢”本来是用执行时间来衡量的，但是对于时间这个指标，我们写SQL的时候不好衡量。因此，在执行SQL查询时，可以用需要遍历的数据行数代替时间作为衡量标准，因为查询的执行时间与遍历的数据行数基本呈正相关关系。我们在写查询语句的时候，可以根据要查询的数据表中的数据总量来估算这个查询需要遍历多少行数据。如果遍历的行数在百万以内，只要查询不是每秒执行几十、几百次，就可以认为查询是安全的。当遍历数据行数达到百万量级时，查询最快需要几秒。这时候就要慎重考虑是否有优化的方法。如果遍历的行数达到千万以上，系统中不应该出现这种SQL。当然，我们这里讨论的都是线上交易系统，线下分析系统另当别论。遍历千万行的SQL是MySQL查询的一道坎。在MySQL中，单表的数据量尽量保持在1000万以下，最多不要超过20到3000万。原因很简单。对一张千万级规模的表进行查询，加几个WHERE条件进行过滤，是可以接受的。符合条件的数据最多可能是数十万或数百万规模。但是如果和其他表联合查询的话，遍历的数据量很可能会超过千万级。因此，最好将每个表的数据量控制在千万级以内。如果数据库本身数据量很大，查询业务逻辑确实需要遍历大量数据，怎么办？02使用索引避免全表扫描使用索引可以有效减少执行查询时遍历的行数，从而提高查询性能。表现。数据库索引的原理比较简单，举个例子就能解释清楚。比如有一个无序数组，数组中的每一个元素都是一个用户对象。如果我们想找出所有姓李的用户，那么比较笨的办法就是循环遍历数组。有没有更好的办法？答案是肯定的。例如，我们可以用一个Map（某些编程语言中的Dictionary）来为数组做一个索引，Key用来保存姓氏，value就是这个姓氏的所有用户对象的序号集合阵列中，如图1所示。这样查找的时候就不需要遍历数组了。只需要先在Map中搜索，然后直接根据序号去数组中获取用户数据，这样搜索速度就快很多了。图1 基于Map构建的内存索引接下来，我们将这个例子映射到数据库中。存放用户数据的数组就是表，我们建的Map就是索引。实际上，数据库索引的数据结构类似于编程语言中的Map或Dictionary，基本上就是各种B树和哈希表。大多数情况下，我们写的查询语句应该使用索引来避免遍历整个表，也就是通常所说的避免全表扫描。在开发新功能时，每当我们需要向数据库中添加新的查询语句时，我们都必须事先评估新的查询语句是否可以被索引支持。如果有必要，我们需要创建一个新的索引来支持新的查询。但是增加索引要付出的代价是会降低数据插入、删除和更新的性能。这也很好理解。添加索引后，当数据发生变化时，不仅要更改数据表中的数据，还必须更改每个索引。因此，对于更新频繁、更新性能要求高的表，可以尽量少建索引。对于查询多、更新少的表，可以根据查询的业务逻辑适当多建索引。那么，如何编写SQL才能更好地利用索引，让查询更有效率呢？这是一项需要丰富经验的技能，学习本文内容后是无法实践的（推荐阅读?）。但是，对于SQL的查询性能，我们还是有办法评估它是否是一个潜在的“慢SQL”。对于逻辑不是很复杂的单表查询，我们或许也可以分析出该查询会使用哪个索引。但是，如果是复杂的多表联合查询，我们就很难通过查看SQL语句本身来分析查询会用到哪些索引，会遍历多少行数据。MySQL和大多数数据库都提供了一个可以用来分析查询的功能，即执行计划。03分析SQL执行计划在MySQL中使用执行计划非常简单，只需要在SQL语句前面加上EXPLAIN关键字，然后执行查询语句即可。下面举例说明，比如有这样一张用户表，包括用户ID、姓名、部门编号、状态等字段，如图2所示。图2 用户表示例我们要查询某二级部门下的所有人员，查询条件为部门代码以00028开头的所有人员。下面两条SQL语句的查询结果相同，均满足要求。那么，哪种查询语句的性能更好呢？1SELECT*FROMuserWHEREleft(department_code,5)='00028';23SELECT*FROMuserWHEREdepartment_codeLIKE'00028%';下面我们分别查看这两条SQL语句的执行计划，如图3所示。图3 两条SQL语句的执行计划下面我们分析一下这两条SQL语句的执行计划。首先查看行列。rows列的意思是MySQL估计执行这条SQL可能遍历的数据行数。第一条SQL遍历了4534行，也就是整个User表的数据条数；第二条SQL只有8行，这8行其实就是8条满足条件的记录。显然，第二条SQL的查询性能要比第一条SQL高很多。为什么第一个SQL需要全表扫描，而第二个SQL只需要遍历少量行？注意类型列，表示本次查询的访问类型。ALL代表全表扫描，这是性能最差的情况。range表示使用索引，也就是说在索引中只进行范围搜索，因为在SQL语句的WHERE条件中有LIKE查询限制。如果直接使用索引，则type列显示索引，可以看到key列实际使用了哪个索引。通过比较这两条SQL的执行计划可以看出，虽然第二条SQL使用了公认效率低下的LIKE查询条件，但是遍历数据的行数远少于第一条SQL，查询性能是更好的。04小结在开发阶段，衡量一条SQL语句查询性能的手段是估算执行该SQL时需要遍历的数据行数。如果遍历行数在百万以内，可以认为是安全SQL；如果在百万到千万之间，需要仔细评估和优化；如果超过千万，那就很危险了。为了减少写慢SQL的可能性，最好将每张数据表的行数控制在千万以内。索引可以显着减少查询遍历的数据量，所以提高SQL查询性能最有效的方法就是让查询尽可能多地使用索引。然而，指数也是一把双刃剑。在提高查询性能的同时，也会降低数据更新的性能。对于复杂的查询，最好使用SQL执行计划，提前分析查询。从SQL执行计划的结果中，我们可以看到查询遍历的估计行数以及它将使用哪些索引。执行计划还可以帮助您优化查询语句。作者简介：李跃，美团基础技术部高级技术专家，极客时间《后端存储实战课》《消息队列高手课》等专栏作家。曾就职于当当网、京东零售等公司。多年从事互联网电商行业基础设施领域的架构设计与研发，多次参与双十一、618电商大促。专注于分布式存储、云原生架构下的服务治理、分布式消息和实时计算等技术领域，致力于推动基础设施技术的创新和开源。本文节选自《电商存储系统实战：架构设计与海量数据处理》，经发布者授权发布。(ISBN:9787111697411)转载请保留文章出处。
