MySQL查询运行速度更快

问题描述

表结构:

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秒

Table holds 110k records out of which 50k type 4 records.Execution time: 0.13 seconds

我知道这很快，但是我可以更快吗?

I know this is fast, but can I make it faster?

我期望有100万条记录，因此时间会大大增加.

I am expecting 1 million records and thus the time would grow quite a bit.

推荐答案

使用EXPLAIN分析查询:

Analyze queries with 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

使用临时；使用文件排序"表示某些昂贵的操作.因为优化器知道它不能依赖将item的每个值存储在一起的行，所以它需要扫描整个表并在临时表中收集每个不同项的计数.然后对生成的临时表进行排序以生成结果.

The "Using temporary; Using filesort" indicates some costly operations. Because the optimizer knows it can't rely on the rows with each value of item being stored together, it needs to scan the whole table and collect the count per distinct item in a temporary table. Then sort the resulting temp table to produce the result.

您需要在日志表上以该顺序在列(类型，项目)上的索引.然后，优化器知道可以利用索引树对logs.item的每个值进行完全扫描，然后再移至下一个值.这样，它可以跳过临时表以收集值，并跳过结果的隐式排序.

You need an index on the logs table on columns (type, item) in that order. Then the optimizer knows it can leverage the index tree to scan each value of logs.item fully before moving on to the next value. By doing this, it can skip the temporary table to collect values, and skip the implicit sorting of the result.

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

这篇关于MySQL查询运行速度更快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！