using filesort mysql_关于MySQL SQL优化误区(Extra:Using filesort 认识误区)

以下为文章摘录：

常见误区

count(1)和count(primary_key) 优于 count(*)

很多人为了统计记录条数，就使用 count(1) 和

count(primary_key) 而不是 count(*)

，他们认为这样性能更好，其实这是一个误区。对于有些场景，这样做可能性能会更差，应为数据库对

count(*)

计数操作做了一些特别的优化。(注意INNODB引擎不同)

count(column) 和 count(*) 是一样的

这个误区甚至在很多的资深工程师或者是 DBA

中都普遍存在，很多人都会认为这是理所当然的。实际上，count(column)

和 count(*)

是一个完全不一样的操作，所代表的意义也完全不一样。

count(column)

是表示结果集中有多少个column字段不为空的记录

count(*) 是表示整个结果集有多少条记录

select a,b from … 比 select a,b,c from …

可以让数据库访问更少的数据量

(注意这里说的是“访问”，不是“返回”数据量)

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

实际上，大多数关系型数据库都是按照行(row)的方式存储，而数据存取操作都是以一个固定大小的IO单元(被称作

block 或者 page)为单位，一般为4KB，8KB…

大多数时候，每个IO单元中存储了多行，每行都是存储了该行的所有字段(lob等特殊类型字段除外)。

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

当然，也有例外情况，那就是我们的这个查询在索引中就可以完成，也就是说当只取

a,b两个字段的时候，不需要回表，而c这个字段不在使用的索引中，需要回表取得其数据。在这样的情况下，二者的IO量会有较大差异。

order by 一定需要排序操作

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

实际上，利用索引来优化有排序需求的

SQL，是一个非常重要的优化手段

延伸阅读：MySQL ORDER

BY 的实现分析 ，MySQL 中

GROUP BY 基本实现原理 以及 MySQL

DISTINCT 的基本实现原理

这3篇文章中有更为深入的分析，尤其是第一篇

执行计划中有 filesort

就会进行磁盘文件排序

有这个误区其实并不能怪我们，而是因为 MySQL

开发者在用词方面的问题。filesort 是我们在使用

explain 命令查看一条 SQL

的执行计划的时候可能会看到在 “Extra”

一列显示的信息。

实际上，只要一条 SQL

语句需要进行排序操作，都会显示“Using

filesort”，这并不表示就会有文件排序操作。

延伸阅读：理解 MySQL

Explain

命令输出中的filesort，我在这里有更为详细的介绍

Using filesort 认识误区

Explain 命令输出信息中的 filesort

到底是什么意思呢？其实很简单，就是告诉你 MySQL

需要进行实际的排序操作而不能通过索引获得已排序数据。

个人觉得上面的错误答案其实至少错了以下两点：

filesort(其实就是排序)

可不一定会产生临时表哦

filesort

与临时表数据写入磁盘是没有任何直接联系的

上面两点错误中第一点在 MySQL Performance Blog

的文章中也提到了。

实际上，在我之前的一篇文章 MySQL

ORDER BY 的实现分析 中已经很清楚的分析了 MySQL

在进行排序的时候，只有当返回的数据和排序条件不在同一个表中的时候(主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给客户端。在

MySQL 中 filesort

的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在

sort buffer

中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在

sort buffer

中进行排序。)，才需要使用到临时表。

学技术，真的是要非常严谨才行，如果我们仅仅通过字面意思来猜想其含义，而不仔细阅读文档并分析实验，很多时候得到的答案可能都是错误的。