我有一个mysql表,结构如下:
mysql> show create table logs G;
Create Table: CREATE TABLE `logs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`request` text,
`response` longtext,
`msisdn` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`shortcode` varchar(255) DEFAULT NULL,
`response_code` varchar(255) DEFAULT NULL,
`response_description` text,
`transaction_name` varchar(250) DEFAULT NULL,
`system_owner` varchar(250) DEFAULT NULL,
`request_date_time` datetime DEFAULT NULL,
`response_date_time` datetime DEFAULT NULL,
`comments` text,
`user_type` varchar(255) DEFAULT NULL,
`channel` varchar(20) DEFAULT 'WEB',
/**
other columns here....
other 18 columns here, with Type varchar and Text
**/
PRIMARY KEY (`id`),
KEY `transaction_name` (`transaction_name`) USING BTREE,
KEY `msisdn` (`msisdn`) USING BTREE,
KEY `username` (`username`) USING BTREE,
KEY `request_date_time` (`request_date_time`) USING BTREE,
KEY `system_owner` (`system_owner`) USING BTREE,
KEY `shortcode` (`shortcode`) USING BTREE,
KEY `response_code` (`response_code`) USING BTREE,
KEY `channel` (`channel`) USING BTREE,
KEY `request_date_time_2` (`request_date_time`),
KEY `response_date_time` (`response_date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=59582405 DEFAULT CHARSET=utf8
,里面有超过30000000条记录。
mysql> select count(*) from logs;
+----------+
| count(*) |
+----------+
| 38962312 |
+----------+
1 row in set (1 min 17.77 sec)
现在的问题是非常慢,select的结果需要很长时间才能从表中取出记录。
我下面的子查询几乎需要30分钟来获取一天的记录:
SELECT
COUNT(sub.id) AS count,
DATE(sub.REQUEST_DATE_TIME) AS transaction_date,
sub.SYSTEM_OWNER,
sub.transaction_name,
sub.response,
MIN(sub.response_time),
MAX(sub.response_time),
AVG(sub.response_time),
sub.channel
FROM
(SELECT
id,
REQUEST_DATE_TIME,
RESPONSE_DATE_TIME,
TIMESTAMPDIFF(SECOND, REQUEST_DATE_TIME, RESPONSE_DATE_TIME) AS response_time,
SYSTEM_OWNER,
transaction_name,
(CASE
WHEN response_code IN ('0' , '00', 'EIL000') THEN 'Success'
ELSE 'Failure'
END) AS response,
channel
FROM
logs
WHERE
response_code != ''
AND DATE(REQUEST_DATE_TIME) BETWEEN '2016-10-26 00:00:00' AND '2016-10-27 00:00:00'
AND SYSTEM_OWNER != '') sub
GROUP BY DATE(sub.REQUEST_DATE_TIME) , sub.channel , sub.SYSTEM_OWNER , sub.transaction_name , sub.response
ORDER BY DATE(sub.REQUEST_DATE_TIME) DESC , sub.SYSTEM_OWNER , sub.transaction_name , sub.response DESC;
我也给我的表添加了索引,但是仍然很慢。
有什么帮助吗?
编辑:使用EXPLAIN
运行上述查询+----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16053297 | Using temporary; Using filesort |
| 2 | DERIVED | logs | ALL | system_owner,response_code | NULL | NULL | NULL | 32106592 | Using where |
+----+-------------+------------+------+----------------------------+------+---------+------+----------+---------------------------------+
按照目前的情况,查询必须扫描整个表。
但首先,让我们公布一个可能的错误:
AND DATE(REQUEST_DATE_TIME) BETWEEN '2016-10-26 00:00:00'
AND '2016-10-27 00:00:00'
给出两天的日志——26日全部和27日全部。或者这是你真正想要的?(BETWEEN
含)
但是性能问题是索引不会被使用,因为request_date_time
隐藏在函数(DATE
)中。
跳到更好的表达方式:
AND REQUEST_DATE_TIME >= '2016-10-26'
AND REQUEST_DATE_TIME < '2016-10-26' + INTERVAL 1 DAY
- A
DATETIME
可以与日期进行比较。 - 包括26日上午午夜,不包括27日午夜。
- 您可以轻松地将
1
更改为您希望的任意天数,而无需处理闰日等。 - 这个公式允许在
request_date_time
上使用索引,从而大大减少了要扫描的数据量。
至于其他诱人的领域:
-
!=
没有很好地优化,因此没有"复合"指数可能是有益的。 - 因为我们不能真正超越
WHERE
,没有索引是有用的GROUP BY
或ORDER BY
。 - 我对
WHERE
中DATE()
的评论不适用于GROUP BY
;
为什么要有子查询?我认为单层就可以完成。这将消除一个相当大的临时表。(是的,这意味着3次使用TIMESTAMPDIFF()
,但这可能比临时表便宜得多。)
多少RAM?innodb_buffer_pool_size
的值是多少?
如果我的评论还不够,如果你经常运行这样的查询(超过一天或超过一个日期范围),那么我们可以讨论构建和维护一个汇总表,这可能会给你10倍的加速