与InnoDB多版本控制的一点乐趣

Consider the following commands, executed in the MySQL CLI on a new connection with no special preparation (and pay special attention to the execution time):

考虑以下命令，在没有特殊准备的情况下，在MySQL CLI上的新连接中执行（特别注意执行时间）：

mysql> show create table t \G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `a` int(10) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table t \G *************************** 1. 行 *************************** 表格：t 创建表格：CREATE TABLE `t` ( `a` int(10) unsigned NOT NULL, `b` int(10) unsigned NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 行在集合中 (0.00 秒)

mysql> select * from t; Empty set (5.20 sec)

mysql> select * from t; Empty set (5.20 秒)

mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (5.22 sec)

mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 行在集合中 (5.22 秒)

mysql> select * from t where a = 10; Empty set (0.00 sec)

mysql> select * from t where a = 10; Empty set (0.00 sec)

mysql> select * from t where a < 10; Empty set (5.35 sec)

mysql> select * from t where a < 10; Empty set (5.35 sec)

mysql> select * from t where a > 10; Empty set (5.41 sec)

mysql> select * from t where a > 10; Empty set (5.41 sec)

mysql> select * from t where a in (10, 20, 30); Empty set (0.00 sec)

mysql> select * from t where a in (10, 20, 30); Empty set (0.00 sec)

mysql> select * from t where a > 1000000; Empty set (0.00 sec)

mysql> select * from t where a > 1000000; 空集 (0.00 秒)

mysql> select * from t where a > 500000; Empty set (2.60 sec)

mysql> select * from t where a > 500000; 空集 (2.60 秒)

What is happening? Why is it so slow? Why are some things slow and others not?

发生了什么？为什么这么慢？为什么有些事情慢而其他事情不慢？

Looking for the culprit

寻找罪魁祸首

The SHOW PROCESSLIST doesn’t show anything unusual:

SHOW PROCESSLIST 没有显示任何异常：

mysql> show processlist; +----+------+-----------------+...