在sending data上耗时过久

gilper 2015-02-02 07:16:29
表结构:
CREATE TABLE `table` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`time` time NOT NULL,
`key` varchar(255) NOT NULL,
`channel` varchar(255) NOT NULL,
`index` varchar(255) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `key` (`key`),
KEY `key-channel` (`key`,`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

数据格式(500w条):
id time key channel index value
1 00:00:00 a a login 100
2 00:00:00 a a login 100
3 00:00:00 b b online 100
4 00:00:00 c a login 100
5 00:00:00 d c new 100
6 00:00:00 a a login 100

查询语句:
SELECT * FROM table WHERE `key` = 'a' AND `channel` = 'b';
2016 rows in set (1.11 sec)

EXPLAIN:
+----+-------------+---------+------+-----------------+-------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------+-------------+---------+-------------+------+-------------+
| 1 | SIMPLE | table | ref | key,key-channel | key-channel | 1534 | const,const | 2015 | Using where |
+----+-------------+---------+------+-----------------+-------------+---------+-------------+------+-------------+

PROFILE:
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000020 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.024401 |
| checking permissions | 0.000018 |
| Opening tables | 0.000024 |
| System lock | 0.000010 |
| Waiting for query cache lock | 0.000103 |
| init | 0.000034 |
| optimizing | 0.000098 |
| statistics | 0.011283 |
| preparing | 0.000091 |
| executing | 0.000004 |
| Sending data | 0.467803 |
| Waiting for query cache lock | 0.000011 |
| Sending data | 0.340983 |
| Waiting for query cache lock | 0.000007 |
| Sending data | 0.260710 |
| Waiting for query cache lock | 0.000011 |
| Sending data | 0.003772 |
| Waiting for query cache lock | 0.000009 |
| Sending data | 0.003093 |
| end | 0.000009 |
| query end | 0.000006 |
| closing tables | 0.000011 |
| freeing items | 0.000010 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000033 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000003 |
| logging slow query | 0.000002 |
| cleaning up | 0.000004 |
+--------------------------------+----------+

麻烦大家帮忙看看这个除了varchar(255)长度可以减少外,怎么样优化比较好,在Sending data上耗时比较久。
...全文
298 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
gilper 2015-02-05
  • 打赏
  • 举报
回复

56,677

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧