MySQL数据库性能优化之SQL优化

  【IT168 技术】有人反馈之前几篇文章过于理论缺少实际操作细节,这篇文章就多一些可操作性的内容吧。

  注:这篇文章是以 MySQL 为背景,很多内容同时适用于其他关系型数据库,需要有一些索引知识为基础。

  IO永远是数据库最容易瓶颈的地方,这是由数据库的职责所决定的,大部分数据库操作中超过90%的时间都是 IO 操作所占用的,减少 IO 次数是 SQL 优化中需要第一优先考虑,当然,也是收效最明显的优化手段。

  除了 IO 瓶颈之外,SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU 计算也就成为了我们 SQL 优化的重要目标

  明确了优化目标之后,我们需要确定达到我们目标的方法。对于 SQL 语句来说,达到上述2个目标的方法其实只有一个,那就是改变 SQL 的执行计划,让他尽量“少走弯路”,尽量通过各种“捷径”来找到我们需要的数据,以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标

  很多人为了统计记录条数,就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他们认为这样性能更好,其实这是一个误区。对于有些场景,这样做可能性能会更差,应为数据库对 count(*) 计数操作做了一些特别的优化。

  这个误区甚至在很多的资深工程师或者是 DBA 中都普遍存在,很多人都会认为这是理所当然的。实际上,count(column) 和 count(*) 是一个完全不一样的操作,所代表的意义也完全不一样。

  这个误区主要存在于大量的开发人员中,主要原因是对数据库的存储原理不是太了解。

  实际上,大多数关系型数据库都是按照行(row)的方式存储,而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位,一般为4KB,8KB 大多数时候,每个IO单元中存储了多行,每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

  所以,我们是取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。

  当然,也有例外情况,那就是我们的这个查询在索引中就可以完成,也就是说当只取 a,b两个字段的时候,不需要回表,而c这个字段不在使用的索引中,需要回表取得其数据。在这样的情况下,二者的IO量会有较大差异。

  我们知道索引数据实际上是有序的,如果我们的需要的数据和某个索引的顺序一致,而且我们的查询又通过这个索引来执行,那么数据库一般会省略排序操作,而直接将数据返回,因为数据库知道数据已经满足我们的排序需求了。

  有这个误区其实并不能怪我们,而是因为 MySQL 开发者在用词方面的问题。filesort 是我们在使用 explain 命令查看一条 SQL 的执行计划的时候可能会看到在 “Extra” 一列显示的信息。

  实际上,只要一条 SQL 语句需要进行排序操作,都会显示“Using filesort”,这并不表示就会有文件排序操作。

  延伸阅读:理解 MySQL Explain 命令输出中的filesort,我在这里有更为详细的介绍。

相关阅读