MYSQL "Sending data"每次显示不同的值

Question

我是一名学生，正在尝试为一个项目测试不同数据库的性能。我试图做的是生成 0-99999(通过多次自加入 0-9 数字表)并测量时序。我对我的结果感到惊讶，想知道是否有人可以帮助解释它们(它们如下所示)。

这是我的测试程序:

BEGIN

DECLARE vduration DECIMAL(8,4) DEFAULT 0;
DECLARE vid INT DEFAULT 0; 
DECLARE vcount INT DEFAULT 0;
DECLARE vprofilingid INT DEFAULT 0;
DECLARE a INT DEFAULT 0 ;

simple_loop: LOOP
     SET a = a+1;
     FLUSH QUERY CACHE;  

     SET profiling=1;
     SELECT n1.n + n2.n*10 + n3.n*100 + n4.n*1000 + n5.n*10000
     FROM
         baseline.num n1
       , baseline.num n2
       , baseline.num n3
       , baseline.num n4
       , baseline.num n5
     LIMIT 100000; 
     SET profiling=0;

     SELECT COUNT(*) INTO vcount FROM baseline.result;
     IF vcount=0 THEN
       SET vid=1;
     ELSE 
       SELECT MAX(Rid)+1 INTO vid FROM baseline.result;
     END IF; 

     SELECT MAX(DISTINCT(query_id)) INTO vprofilingid
     FROM information_schema.profiling;

     SELECT SUM(duration) INTO vduration
     FROM information_schema.profiling WHERE query_id=vprofilingid; 

     INSERT INTO baseline.result VALUES (vid, vduration); 

     IF a=5 THEN
       LEAVE simple_loop;
     END IF;
END LOOP simple_loop;             

END

结果:

RID     DURATION
8       0.0406
9       1.8610
10      1.8401
11      1.8558
12      1.8638

运行 SHOW PROFILES 显示:

8   0.04059275  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
9   1.86098975  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
10  1.84006350  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
11  1.85582025  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
12  1.86381750  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000

为什么第一次更快？这与我的假设相反。

我注意到 SENDINT DATA 有区别，

SELECT * FROM information_schema.profiling
WHERE query_id > 7 and state = 'Sending data';

显示:

8     10    Sending data    0.040310
9     10    Sending data    1.860891
10    10    Sending data    1.839958
11    10    Sending data    1.855719
12    10    Sending data    1.863717

另外，如果我将循环设置为只运行一次，则所有结果都在 0.04 秒左右。我在这里做错了什么吗？我真的很困惑。

Answer 1

这是 MySQL 拥有的所有线程状态的链接。

http://dev.mysql.com/doc/refman/5.5/en/general-thread-states.html

注意关于发送数据的部分:

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

因为发送数据包括磁盘读取时间，所以值可能会有很大差异，具体取决于数据是否在 HDD 缓存、RAM 缓存、MySQL 缓存等中。

您的第一个查询不会从磁盘读取任何数据，因此读取数据+发送数据这一步要快得多。