5.8.1.11 Filesort 探针

只要将文件排序操作应用于 table，就会触发文件排序探针。有关文件排序及其发生条件的更多信息，请参见第 8.2.1.14 节“按优化排序”。

filesort-start(database, table)
filesort-done(status, rows)

• filesort-start：在 table 上开始文件排序操作时触发。探针的两个参数database和table将标识要排序的 table。

• filesort-done：文件排序操作完成时触发。提供了两个参数，status(0table 示成功，1table 示失败)，以及在文件排序过程中排序的行数。

下面的脚本就是一个例子，该脚本除了主要查询的持续时间外，还跟踪文件排序过程的持续时间：

#!/usr/sbin/dtrace -s

#pragma D option quiet

dtrace:::BEGIN
{
   printf("%-2s %-10s %-10s %9s %18s %-s \n",
          "St", "Who", "DB", "ConnID", "Dur microsec", "Query");
}

mysql*:::query-start
{
   self->query = copyinstr(arg0);
   self->who   = strjoin(copyinstr(arg3),strjoin("@",copyinstr(arg4)));
   self->db    = copyinstr(arg2);
   self->connid = arg1;
   self->querystart = timestamp;
   self->filesort = 0;
   self->fsdb = "";
   self->fstable = "";
}

mysql*:::filesort-start
{
  self->filesort = timestamp;
  self->fsdb = copyinstr(arg0);
  self->fstable = copyinstr(arg1);
}

mysql*:::filesort-done
{
   this->elapsed = (timestamp - self->filesort) /1000;
   printf("%2d %-10s %-10s %9d %18d Filesort on %s\n",
          arg0, self->who, self->fsdb,
          self->connid, this->elapsed, self->fstable);
}

mysql*:::query-done
{
   this->elapsed = (timestamp - self->querystart) /1000;
   printf("%2d %-10s %-10s %9d %18d %s\n",
          arg0, self->who, self->db,
          self->connid, this->elapsed, self->query);
}

使用触发文件排序的ORDER BY子句在大型 table 上执行查询，然后在 table 上创建索引，然后重复相同的查询，您可以看到执行速度的差异：

St Who        DB            ConnID       Dur microsec Query
 0 @localhost test              14           11335469 Filesort on t1
 0 @localhost test              14           11335787 select * from t1 order by i limit 100
 0 @localhost test              14          466734378 create index t1a on t1 (i)
 0 @localhost test              14              26472 select * from t1 order by i limit 100