mysql - 在 mysql 中查询巨大的数据库表需要太多时间

Question

我在一个 mysql 数据库表上运行 sql 查询，该表一整天都有 1.1 亿条以上的唯一记录。

问题:每当我使用“where”子句运行任何查询时，它至少需要 30-40 分钟。因为我想在第二天生成大部分数据，所以我需要访问整个数据库表。

能否请您指导我优化/重构部署模型？

网站描述:

mysql  Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.04 GB RAM, Dual Core dual CPU 3GHzRHEL 3

my.cnf contents :

[mysqld]datadir=/data/mysql/data/socket=/tmp/mysql.socksort_buffer_size = 2000000table_cache = 1024key_buffer = 128Mmyisam_sort_buffer_size = 64M# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1[mysql.server]user=mysqlbasedir=/data/mysql/data/[mysqld_safe]err-log=/data/mysql/data/mysqld.logpid-file=/data/mysql/data/mysqld.pid[root@reports root]#

DB table details:

CREATE TABLE `RAW_LOG_20100504` (
  `DT` date default NULL,
  `GATEWAY` varchar(15) default NULL,
  `USER` bigint(12) default NULL,
  `CACHE` varchar(12) default NULL,
  `TIMESTAMP` varchar(30) default NULL,
  `URL` varchar(60) default NULL,
  `VERSION` varchar(6) default NULL,
  `PROTOCOL` varchar(6) default NULL,
  `WEB_STATUS` int(5) default NULL,
  `BYTES_RETURNED` int(10) default NULL,
  `RTT` int(5) default NULL,
  `UA` varchar(100) default NULL,
  `REQ_SIZE` int(6) default NULL,
  `CONTENT_TYPE` varchar(50) default NULL,
  `CUST_TYPE` int(1) default NULL,
  `DEL_STATUS_DEVICE` int(1) default NULL,
  `IP` varchar(16) default NULL,
  `CP_FLAG` int(1) default NULL,
  `USER_LOCATE` bigint(15) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=200000000;

提前致谢!问候，

Answer 1

我鼓励您学习如何使用 EXPLAIN分析数据库的查询优化计划。另请参阅 Baron Schwartz 的演讲 EXPLAIN Demystified (他的幻灯片的 PDF 链接在那个页面上)。

了解如何创建索引——这与主键或自动递增伪键不同。查看演示 More Mastering the Art of Indexing作者:松信义典。

您的表可以在 CP_FLAG 和 WEB_STATUS 上使用索引。

CREATE INDEX CW ON RAW_LAW_20100503 (CP_FLAG, WEB_STATUS);

这有助于根据您的 cp_flag 条件查找行的子集。

然后您仍然会遇到 MySQL 的 GROUP BY 查询效率低下的不幸问题。它将临时结果集复制到磁盘上的一个临时文件中，并在那里对其进行排序。磁盘 I/O 往往会降低性能。

您可以提高您的sort_buffer_size 配置参数，直到它足够大，MySQL 可以在内存而不是磁盘上对结果集进行排序。但这可能行不通。

您可能不得不求助于预先计算所需的 COUNT()，并定期更新此统计信息。

---

@Marcus 的评论给了我另一个想法。您按网络状态分组，网络状态的不同值集是一个相当短的列表，它们不会改变。因此，您可以为每个不同的值运行单独的查询并生成所需的结果，这比使用创建临时表进行排序的 GROUP BY 查询要快得多。或者您可以为每个状态值运行一个子查询，并将它们UNION 在一起:

(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 200)
UNION
(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 404)
UNION
(SELECT COUNT(*), WEB_STATUS FROM RAW_LOG_20100504 WHERE CP_FLAG > 0 AND WEB_STATUS = 304)
UNION
...etc...
ORDER BY 1 DESC;

因为您的覆盖索引 包括CP_FLAG 和WEB_STATUS，所以这些查询永远不需要读取表中的实际行。它们只读取索引中的条目，它们可以更快地访问这些条目，因为 (a) 它们位于排序树中，并且 (b) 如果您为 key_buffer_size 分配足够的空间，它们可能会缓存在内存中.

我尝试的EXPLAIN 报告(包含 100 万行测试数据)表明这很好地使用了索引，并且没有创建临时表:

+------+--------------+------------------+------+--------------------------+
| id   | select_type  | table            | key  | Extra                    |
+------+--------------+------------------+------+--------------------------+
|  1   | PRIMARY      | RAW_LOG_20100504 | CW   | Using where; Using index |
|  2   | UNION        | RAW_LOG_20100504 | CW   | Using where; Using index |
|  3   | UNION        | RAW_LOG_20100504 | CW   | Using where; Using index |
| NULL | UNION RESULT | <union1,2,3>     | NULL | Using filesort           |
+------+--------------+------------------+------+--------------------------+

最后一行的 Using filesort 只是意味着它必须在没有索引的情况下进行排序。但是对子查询产生的三行进行排序是微不足道的，MySQL 在内存中完成。

---

在设计最佳数据库解决方案时，很少有简单的答案。很大程度上取决于您如何使用数据以及哪种查询具有更高的优先级以加快速度。如果有一个适用于所有情况的单一、简单的答案，软件将默认启用该设计，您无需执行任何操作。

您确实需要阅读大量手册、书籍和博客才能了解如何充分利用您可用的所有功能。

---

是的，我仍然会推荐使用索引。很明显，以前当您查询 1 亿行时没有索引的好处，它是行不通的。

您必须明白，您必须设计有利于您要运行的特定查询的索引。我无法知道您刚刚在评论中描述的索引是否合适，因为您没有显示您正在尝试加速的其他查询。

索引是一个复杂的话题。如果您在错误的列上定义了索引，或者如果您以错误的顺序获取列，则给定查询可能无法使用它。自 1994 年以来，我一直在支持 SQL 开发人员，但我从未找到一条简明的规则来解释如何设计索引。

您似乎需要一位导师，因为您正处于需要回答很多问题的阶段。在您工作的地方是否有人可以寻求帮助？