带有大表的 Mysql : how to optmize this query?

Question

我有一个使用 InnoDB 的表，用于存储我的系统发送的所有消息。目前该表有 4000 万行，并且每月增长 3/4 百万。

我的查询基本上是选择从用户发送并在数据范围内的消息。这是一个简单的创建表:

CREATE TABLE `log` (  `id` int(10) NOT NULL DEFAULT '0',  `type` varchar(10) NOT NULL DEFAULT '',  `timeLogged` int(11) NOT NULL DEFAULT '0',  `orig` varchar(128) NOT NULL DEFAULT '',  `rcpt` varchar(128) NOT NULL DEFAULT '',  `user` int(10) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `timeLogged` (`timeLogged`),  KEY `user` (`user`),  KEY `user_timeLogged` (`user`,`timeLogged`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

注意:由于其他查询，我也有单独的索引。

查询看起来像这样:

SELECT COUNT(*) FROM log WHERE timeLogged BETWEEN 1282878000 AND 1382878000 AND user = 20

问题是此查询需要 2 分钟到 10 分钟，具体取决于用户和服务器负载，等待页面加载的时间太长。我启用了 mysql 缓存并在应用程序中缓存，但问题是当用户搜索新范围时，它不会命中缓存。

我的问题是:

• 更改 user_timeLogged 索引会有什么不同吗？
• 这是 MySQL 和大型数据库的问题吗？我的意思是，Oracle 或其他数据库是否也遇到此问题？

据我所知，我的索引已正确创建，此查询不应花费这么长时间。

感谢所有提供帮助的人!

Answer 1

你正在使用 innodb 但没有充分利用你的 innodb 聚簇索引(主键)，因为它看起来像你的典型查询的形式:

select <fields> from <table> where user_id = x and <datefield> between y and z

不是

select <fields> from <table> where id = x 

以下文章应该可以帮助您优化查询的表设计。

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

如果您正确理解这篇文章，您应该会发现类似以下内容:

drop table if exists user_log;
create table user_log
(
user_id int unsigned not null,
created_date datetime not null, 
log_type_id tinyint unsigned not null default 0, -- 1 byte vs varchar(10)
...
...
primary key (user_id, created_date, log_type_id)
)
engine=innodb;

下面是上述设计的一些查询性能统计数据:

计数

select count(*) as counter from user_log

counter
=======
37770394

select count(*) as counter from user_log where 
 created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'

counter
=======
35547897

基于用户和日期的查询(所有查询都使用冷缓冲区运行)

select count(*) as counter from user_log where user_id = 4755

counter
=======
7624

runtime = 0.215 secs


select count(*) as counter from user_log where 
 user_id = 4755 and created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'

counter
=======
7404

runtime = 0.015 secs

select 
 user_id,
 created_date,
 count(*) as counter
from 
 user_log 
where 
 user_id = 4755 and created_date between '2010-09-01 00:00:00' and '2010-11-30 00:00:00'
group by
 user_id, created_date
order by
 counter desc
limit 10;

runtime = 0.031 secs

希望这有帮助:)