作者：微信小助手
发布时间：2020-11-25T08:54:40
码农架构的读者应该注意到上个周末有分享一篇文章：一个几乎每个系统必踩的坑儿：访问数据库超时，最后对于怎么避免写出慢SQL没有过多赘述，但实际上这个问题我们经常遇到。我们不能等着系统上线，慢 SQL 吃光数据库资源之后，再找出慢 SQL 来改进，那样就晚了。那么，怎样才能在开发阶段尽量避免写出慢 SQL 呢？
定量认识MySQL
一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单 SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。
我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了。
另外一个重要的定量指标是，到底多慢的 SQL 才算慢 SQL。这里面这个“慢”，衡量的单位本来是执行时长，但是时长这个东西，我们在编写 SQL 的时候并不好去衡量。那我们可以用执行 SQL 查询时，需要遍历的数据行数替代时间作为衡量标准，因为查询的执行时长基本上是和遍历的数据行数正相关的。
你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据:
-
如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。 -
遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。 -
遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统，离线分析类系统另说。
遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL 中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过千万级别了。所以，每个表的数据量最好小于千万级别。
使用索引避免全表扫描
增加索引付出的代价是，会降低数据插入、删除和更新的性能。这个也很好理解，增加了索引，在数据变化的时候，不仅要变更数据表里的数据，还要去变更每个索引。所以，对于更新频繁并且对更新性能要求较高的表，可以尽量少建索引。而对于查询较多更新较少的表，可以根据查询的业务逻辑，适当多建一些索引。
分析SQL执行计划
在 MySQL 中使用执行计划也非常简单，只要在你的 SQL 语句前面加上 EXPLAIN 关键字，然后执行这个查询语句就可以了。
MySQL 预估执行这个 SQL 可能会遍历的数据行数。第一个 SQL 遍历了四千多行，这就是整个 User 表的数据条数；第二个 SQL 只有 8 行，这 8 行其实就是符合条件的 8 条记录。显然第二个 SQL 查询性能要远远好于第一个 SQL。
type 列:
表示这个查询的访问类型。ALL 代表全表扫描，这是最差的情况。range 代表使用了索引，在索引中进行范围查找，因为第二个 SQL 语句的 WHERE 中有一个 LIKE 的查询条件。如果直接命中索引，type 这一列显示的是 index。如果使用了索引，可以在 key 这一列中看到，实际上使用了哪个索引。
总结
在开发阶段，衡量一个 SQL 查询语句查询性能的手段是，估计执行 SQL 时需要遍历的数据行数。遍历行数在百万以内，可以认为是安全的 SQL，百万到千万这个量级则需要仔细评估和优化，千万级别以上则是非常危险的。为了减少慢 SQL 的可能性，每个数据表的行数最好控制在千万以内。索引可以显著减少查询遍历数据的数量，所以提升 SQL 查询性能最有效的方式就是，让查询尽可能多的命中索引，但索引也是一把双刃剑，它在提升查询性能的同时，也会降低数据更新的性能。对于复杂的查询，最好使用 SQL 执行计划，事先对查询做一个分析。在 SQL 执行计划的结果中，可以看到查询预估的遍历行数，命中了哪些索引。执行计划也可以很好地帮助你优化你的查询语句。
往期推荐
