怎么能避免写出慢SQL？

所谓慢 SQL，就是执行特别慢的 SQL 语句。什么样的 SQL 语句是慢 SQL？多慢才算是慢 SQL？并没有一个非常明确的标准或者说是界限。但并不是说，我们就很难区分正常的 SQL 和慢 SQL，在大多数实际的系统中，慢 SQL 消耗掉的数据库资源，往往是正常 SQL 的几倍、几十倍甚至几百倍，所以还是非常容易区分的。

但问题是，我们不能等着系统上线，慢 SQL 吃光数据库资源之后，再找出慢 SQL 来改进，那样就晚了。那么，怎样才能在开发阶段尽量避免写出慢 SQL 呢？

定量认识 MySQL

慢 SQL 对数据库的影响，是一个量变到质变的过程，对“量”的把握，就很重要。作为一个合格的程序员，你需要对数据库的能力，有一个定量的认识。

影响 MySQL 处理能力的因素很多，比如：服务器的配置、数据库中的数据量大小、MySQL 的一些参数配置、数据库的繁忙程度等等。但是，通常情况下，这些因素对于 MySQL 性能和处理能力影响范围，大概在几倍的性能差距。所以，我们不需要精确的性能数据，只要掌握一个大致的量级，就足够指导我们的开发工作了。

一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单 SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。

我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了。

另外一个重要的定量指标是，到底多慢的 SQL 才算慢 SQL。这里面这个“慢”，衡量的单位本来是执行时长，但是时长这个东西，我们在编写 SQL 的时候并不好去衡量。那我们可以用执行 SQL 查询时，需要遍历的数据行数替代时间作为衡量标准，因为查询的执行时长基本上是和遍历的数据行数正相关的。

你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据。如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统，离线分析类系统另说。

遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL 中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过千万级别了。所以，每个表的数据量最好小于千万级别。

如果数据库中的数据量就是很多，而且查询业务逻辑就需要遍历大量数据怎么办？

使用索引避免全表扫描

使用索引可以有效地减少执行查询时遍历数据的行数，提高查询性能。

数据库索引的原理也很简单，我举个例子你就明白了。比如说，有一个无序的数组，数组的每个元素都是一个用户对象。如果说我们要把所有姓李的用户找出来。比较笨的办法是，用一个循环把数组遍历一遍。

有没有更好的办法？很多办法是吧？比如说，我们用一个 Map(在有些编程语言中是 Dictionary) 来给数组做一个索引，Key 保存姓氏，值是所有这个姓氏的用户对象在数组中序号的集合。这样再查找的时候，就不用去遍历数组，先在 Map 中查找，然后再直接用序号去数组中拿用户数据，这样查找速度就快多了。

这个例子对应到数据库中，存放用户数据的数组就是表，我们构建的 Map 就是索引。实际上数据库的索引，和编程语言中的 Map 或者 Dictionary，它们的数据结构都是差不多的，基本上就是各种 B 树和 HASH 表。

绝大多数情况下，我们编写的查询语句，都应该使用索引，避免去遍历整张表，也就是通常说的，避免全表扫描。你在每次开发新功能，需要给数据库增加一个新的查询时，都要评估一下，是不是有索引可以支撑新的查询语句，如果有必要的话，需要新建索引来支持新增的查询。

但是，增加索引付出的代价是，会降低数据插入、删除和更新的性能。这个也很好理解，增加了索引，在数据变化的时候，不仅要变更数据表里的数据，还要去变更每个索引。所以，对于更新频繁并且对更新性能要求较高的表，可以尽量少建索引。而对于查询较多更新较少的表，可以根据查询的业务逻辑，适当多建一些索引。

怎么写 SQL 能更好地使用索引，查询效率更高，这是一门手艺，需要丰富的经验，不是通过一节课的学习能练成的。但是，我们是有方法，可以评估写出来的 SQL 的查询性能怎么样，是不是一个潜在的“慢 SQL”。

逻辑不是很复杂的单表查询，我们可能还可以分析出来，查询会使用哪个索引。但如果是比较复杂的多表联合查询，我们单看 SQL 语句本身，就很难分析出查询到底会命中哪些索引，会遍历多少行数据。MySQL 和大部分数据库，都提供一个帮助我们分析查询功能：执行计划。

分析 SQL 执行计划

在 MySQL 中使用执行计划也非常简单，只要在你的 SQL 语句前面加上 EXPLAIN 关键字，然后执行这个查询语句就可以了。
举个例子说明，比如有一个用户表，包含用户 ID、姓名、部门编号和状态这几个字段：

我们希望查询某个二级部门下的所有人，查询条件就是，部门代号以 00028 开头的所有人。下面这两个 SQL，他们的查询结果是一样的，都满足要求，但是，哪个查询性能更好呢？

SELECT * FROM user WHERE left(department_code, 5) = '00028';
SELECT * FROM user WHERE department_code LIKE '00028%';
1
2

我们分别查看一下这两个 SQL 的执行计划：

我带你一起来分析一下这两个 SQL 的执行计划。首先来看 rows 这一列，rows 的含义就是，MySQL 预估执行这个 SQL 可能会遍历的数据行数。第一个 SQL 遍历了四千多行，这就是整个 User 表的数据条数；第二个 SQL 只有 8 行，这 8 行其实就是符合条件的 8 条记录。显然第二个 SQL 查询性能要远远好于第一个 SQL。

为什么第一个 SQL 需要全表扫描，第二个 SQL 只遍历了很少的行数呢？注意看 type 这一列，这一列表示这个查询的访问类型。ALL 代表全表扫描，这是最差的情况。range 代表使用了索引，在索引中进行范围查找，因为 SQL 语句的 WHERE 中有一个 LIKE 的查询条件。如果直接命中索引，type 这一列显示的是 index。如果使用了索引，可以在 key 这一列中看到，实际上使用了哪个索引。
通过对比这两个 SQL 的执行计划，就可以看出来，第二个 SQL 虽然使用了普遍认为低效的 LIKE 查询条件，但是仍然可以用到索引的范围查找，遍历数据的行数远远少于第一个 SQL，查询性能更好。