MySQL - 访问表索引或表数据慢

Question

我有包含大量行的 MyISAM 表 - 从 10M 到 500M。这些表很少用于存储时间序列数据，我想针对 SELECT 对其进行优化，我正在通过 2 个索引进行优化:纪元和分类器列(包含几千个类别)。

我的问题是，我为特定类别所做的第一个 SELECT 相当长(10 到 50 秒)，而后续的非常快，即使使用 SQL_NO_CACHE。这样的查询通常会返回 100,000 到 100 万个元素。

分析表明，MySQL 花费大量时间“发送数据”。这表明花费了大量时间来完成磁盘 I/O。但是我不太明白瓶颈在哪里:

1. 瓶颈在 BTREE 读取中吗？该树只有几千个节点，然后在所选节点中的点不到 1M。我不敢相信在现代机器上完成这项工作需要 30 秒，即使是使用老式硬盘驱动器也是如此。
2. 是在读取表中的行吗？这同样少于一百万行，平均长度约为 40 字节。
3. 还有什么我没有考虑到的吗？

查询结果如下:

mysql> SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63;
+---------------+
| COUNT(`Time`) |
+---------------+
|        450619 |
+---------------+
1 row in set (28.67 sec)

mysql> SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63;
+---------------+
| COUNT(`Time`) |
+---------------+
|        450619 |
+---------------+
1 row in set (2.20 sec)

mysql> SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63;
+---------------+
| COUNT(`Time`) |
+---------------+
|        450619 |
+---------------+
1 row in set (0.88 sec)

mysql> SHOW PROFILES;
+----------+-------------+-----------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                             |
+----------+-------------+-----------------------------------------------------------------------------------+
|        1 | 28.66720725 | SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63 |
|        2 |  2.19872350 | SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63 |
|        3 |  0.87811475 | SELECT SQL_NO_CACHE COUNT(`Time`) FROM archive_1 WHERE Channel=63 |
+----------+-------------+-----------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000113 |
| checking permissions |  0.000010 |
| Opening tables       |  0.000027 |
| System lock          |  0.000017 |
| init                 |  0.000030 |
| optimizing           |  0.000018 |
| statistics           |  0.055731 |
| preparing            |  0.000024 |
| executing            |  0.000008 |
| Sending data         | 28.611161 |
| end                  |  0.000019 |
| query end            |  0.000005 |
| closing tables       |  0.000014 |
| freeing items        |  0.000021 |
| logging slow query   |  0.000003 |
| logging slow query   |  0.000004 |
| cleaning up          |  0.000005 |
+----------------------+-----------+
17 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000105 |
| checking permissions | 0.000011 |
| Opening tables       | 0.000036 |
| System lock          | 0.000015 |
| init                 | 0.000028 |
| optimizing           | 0.000019 |
| statistics           | 0.032255 |
| preparing            | 0.000024 |
| executing            | 0.000007 |
| Sending data         | 2.166140 |
| end                  | 0.000020 |
| query end            | 0.000004 |
| closing tables       | 0.000014 |
| freeing items        | 0.000025 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000018 |
+----------------------+----------+
16 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000018 |
| System lock          | 0.000012 |
| init                 | 0.000021 |
| optimizing           | 0.000014 |
| statistics           | 0.000059 |
| preparing            | 0.000020 |
| executing            | 0.000007 |
| Sending data         | 0.877795 |
| end                  | 0.000021 |
| query end            | 0.000004 |
| closing tables       | 0.000015 |
| freeing items        | 0.000029 |
| logging slow query   | 0.000015 |
| cleaning up          | 0.000006 |
+----------------------+----------+
16 rows in set (0.00 sec)

我正在查询的特定表包含 107,407,213 行，数据长度为 4,237,427,600 字节，索引长度为 4,255,541,248 字节。我昨天优化了它，从那以后那里没有添加数据。

如果查询受 I/O 限制，我总是可以切换到 SSD，而且我还可以选择将时间索引存储为整数而不是 double 。但到目前为止我还不明白我的瓶颈在哪里，我想在了解更多之前避免重大变化。

Answer 1

SQL_NO_CACHE 表示mysql不应该使用查询缓存。

仍然使用磁盘/缓冲区缓存，这就是为什么第一次查询花费时间较长，而后续查询更快的原因。