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

Question

我正在一个具有110Mn +独特记录整天的mysql数据库表上运行sql查询。

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

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

网站描述：

 
mysql Ver 14.12 Distrib 5.0.24, for pc-linux-gnu (i686) using readline 5.0 
4 GB RAM, 
Dual Core dual CPU 3GHz 
RHEL 3 

my.cnf中的内容：

 
[mysqld] 
datadir=/data/mysql/data/ 
socket=/tmp/mysql.sock 

sort_buffer_size = 2000000 
table_cache = 1024 
key_buffer = 128M 
myisam_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=mysql 
basedir=/data/mysql/data/ 

[mysqld_safe] 
err-log=/data/mysql/data/mysqld.log 
pid-file=/data/mysql/data/mysqld.pid 
[[email protected] root]# 

数据库表的详细信息：

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; 

提前感谢！ Regards，

Answer 1

将索引添加到where子句中的任何字段。主键必须是唯一的;唯一索引需要是唯一的，但唯一性不是索引的先决条件。

严重定义或不存在的索引的首要原因表现差之一，并固定这些常可导致惊人的改进

快速信息：

• http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
• http://www.tizag.com/mysqlTutorial/mysql-index.php

Answer 2

我鼓励您学习如何使用EXPLAIN来分析数据库的查询优化计划。另请参阅Baron Schwartz的演示文稿EXPLAIN Demystified（他的幻灯片的PDF链接在该页面上）。

了解如何创建索引 - 这与主键或自动增量伪码不同。见Yoshinori Matsunobu的介绍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报告我想（与试验数据的1M行）表明，该使用索引很好，不会创建一个临时表：

+------+--------------+------------------+------+--------------------------+ 
| 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开发人员提供支持，而我从来没有找到简单明了的规则来解释如何设计索引。

你好像你需要一个导师，因为你处于一个需要很多问题回答的阶段。有工作的人可以请求帮助你吗？