MySQL：怎么避免写出慢SQL

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

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

定量认识MySQL

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

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

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

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

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

遍历行数在千万左右，是MySQL查询的一个坎儿。MySQL中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。（每个表的数据量最好小于千万级别。）

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

使用索引避免全表扫描

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

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

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

可以通过【查询执行计划】评估写出来的SQL的查询性能怎么样，是不是一个潜在的“慢SQL”

分析SQL执行计划

在 MySQL 中使用执行计划也非常简单，只要在你的 SQL 语句前面加上 EXPLAIN 关键字，然后执行这个查询语句就可以了。

举个例子说明，比如有一个用户表，包含用户 ID、姓名、部门编号和状态这几个字段：

我们希望查询某个二级部门下的所有人，查询条件就是，部门代号以 00028 开头的所有人。下面这两个 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，查询性能更好。

小结

在开发阶段，衡量一个SQL查询语句查询性能的手段是，估计执行SQL时需要遍历的数据行数。边咯行数在百万以内，可以认为是安全的SQL，百万到千万这个量级则需要仔细评估和优化，千万级别以上则是非常危险的。为了减少慢SQL的可能性，每个数据表的行数最好控制在千万以内。

索引可以显著减少查询遍历数据的数量，所以提升SQL查询性能最有效的方式是，让程序尽可能多的命中索引，但索引也是一把双刃剑，它在提升查询性能的同时，也会降低数据的更新性能。

对于复杂的查询，最好使用SQL执行计划，事先对查询做一个分析。在SQL执行行计划的结果中，可以看到查询预估的遍历行数，命中了哪些索引。执行计划也可以很好地帮助你优化你的查询语句。