江苏龙网

  1. 主页 > 生活百科 > >

怎么能避免写出慢SQL？

慢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 关键字，然后执行这个查询语句就可以了 。
比如有一个用户表，包含用户 ID、姓名、部门编号和状态这几个字段：
怎么能避免写出慢SQL？

文章插图
 
【怎么能避免写出慢SQL？】我们希望查询某个二级部门下的所有人，查询条件就是，部门代号以 00028 开头的所有人 。下面这两个 SQL，他们的查询结果是一样的，都满足要求，但是，哪个查询性能更好呢？
SELECT * FROM user WHERE left(department_code, 5) = '00028';SELECT * FROM user WHERE department_code LIKE '00028%';我们分别查看一下这两个 SQL 的执行计划：
怎么能避免写出慢SQL？

文章插图
 
row 列:
  • MySQL 预估执行这个 SQL 可能会遍历的数据行数 。第一个 SQL 遍历了四千多行，这就是整个 User 表的数据条数；第二个 SQL 只有 8 行，这 8 行其实就是符合条件的 8 条记录 。显然第二个 SQL 查询性能要远远好于第一个 SQL 。
type 列:

上一篇：超屌的多线程锁分类，你确定不看看吗？

下一篇：白茶的功效与作用,茉莉毛尖属于什么茶茉莉花茶毛尖的功效与作用