Mysql查询运行速度更快

Question

表结构:

CREATE TABLE IF NOT EXISTS `logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user` bigint(20) unsigned NOT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  `date` int(11) unsigned NOT NULL,
  `plus` decimal(10,2) unsigned NOT NULL,
  `minus` decimal(10,2) unsigned NOT NULL,
  `tax` decimal(10,2) unsigned NOT NULL,
  `item` bigint(20) unsigned NOT NULL,
  `info` char(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `item` (`item`),
  KEY `user` (`user`),
  KEY `type` (`type`),
  KEY `date` (`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=FIXED;

查询:

SELECT logs.item, COUNT(logs.item) AS total FROM logs WHERE logs.type = 4 GROUP BY logs.item;

表包含 110k 条记录，其中 50k 条类型 4 记录。执行时间:0.13秒

我知道这很快，但我可以让它更快吗？

我预计有 100 万条记录，因此时间会增加很多。

Answer 1

使用 EXPLAIN 分析查询:

mysql> EXPLAIN SELECT logs.item, COUNT(logs.item) AS total FROM logs 
    WHERE logs.type = 4 GROUP BY logs.item\G

           id: 1
  select_type: SIMPLE
        table: logs
         type: ref
possible_keys: type
          key: type
      key_len: 1
          ref: const
         rows: 1
        Extra: Using where; Using temporary; Using filesort

“Using temporary; Using filesort”表示一些代价高昂的操作。因为优化器知道它不能依赖将 item 的每个值存储在一起的行，所以它需要扫描整个表并收集临时表中每个不同项目的计数。然后对生成的临时表进行排序以生成结果。

您需要按该顺序在列(类型、项目)上的日志表上建立索引。然后优化器知道它可以利用索引树在移动到下一个值之前完全扫描 logs.item 的每个值。这样做可以跳过临时表收集值，跳过结果的隐式排序。

mysql> CREATE INDEX logs_type_item ON logs (type,item);

mysql> EXPLAIN SELECT logs.item, COUNT(logs.item) AS total FROM logs 
    WHERE logs.type = 4 GROUP BY logs.item\G

           id: 1
  select_type: SIMPLE
        table: logs
         type: ref
possible_keys: type,logs_type_item
          key: logs_type_item
      key_len: 1
          ref: const
         rows: 1
        Extra: Using where