mysql - 从表中选择速度很慢

Question

我的问题是:简单的选择查询需要很长时间(3分钟)。

结构:

mysql> show create table seventhcont_exceptionreport;

seventhcont_exceptionreport | CREATE TABLE `seventhcont_exceptionreport` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `body_html` longtext NOT NULL,
  `datetime_created` datetime NOT NULL,
  `subject` varchar(256) NOT NULL,
  `host` varchar(128) NOT NULL,
  `exc_value` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=74607 DEFAULT CHARSET=utf8 |

行数:

mysql> select count(*) from seventhcont_exceptionreport;

+----------+
| count(*) |
+----------+
|     7064 |
+----------+
1 row in set (0.00 sec)

查询1(正常):

mysql> select id, datetime_created from seventhcont_exceptionreport order by id LIMIT 100 OFFSET 6000;

...
100 rows in set (0.30 sec)

查询 2(非常慢):

mysql> select id, datetime_created from seventhcont_exceptionreport order by id LIMIT 100 OFFSET 7000;

...

63 rows in set (3 min 40.56 sec)

!!! 3分40秒。

为什么？

更新

解释查询 1:

mysql> EXPLAIN select id, datetime_created from seventhcont_exceptionreport order by id LIMIT 100 OFFSET 6000;
+----+-------------+-----------------------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table                       | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-----------------------------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | seventhcont_exceptionreport | index | NULL          | PRIMARY | 4       | NULL | 6100 |       |
+----+-------------+-----------------------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

解释查询 2:

mysql> EXPLAIN select id, datetime_created from seventhcont_exceptionreport order by id LIMIT 100 OFFSET 7000;
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table                       | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | seventhcont_exceptionreport | ALL  | NULL          | NULL | NULL    | NULL | 7067 | Using filesort |
+----+-------------+-----------------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

更新

分析表:

mysql> ANALYZE TABLE seventhcont_exceptionreport;
+--------------------------------+---------+----------+----------+
| Table                          | Op      | Msg_type | Msg_text |
+--------------------------------+---------+----------+----------+
| 7k.seventhcont_exceptionreport | analyze | status   | OK       |
+--------------------------------+---------+----------+----------+
1 row in set (2.51 sec)

Answer 1

我不是 MySQL 专家，但我也许可以为您指明正确的方向。

在第一个查询中，我们可以在解释计划中看到使用了索引访问。相反，对于第二个查询，我们可以看到执行了非索引访问(类型 index 与 ALL)。另外，我们可以看到 MySQL 正在使用使用文件排序。

这意味着 MySQL 无法对索引执行排序操作，因此对数据本身执行排序操作。这可能是因为排序缓冲区太小(另请参阅 https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/ )。

因此，尝试增加排序缓冲区的大小 (soft_buffer_size)。