MySQL分组优化 - InnoDB vs MyISAM？

Question

MySQL分组优化 - InnoDB vs MyISAM？

5

问题

有人知道为什么基于MyISAM的表没有GROUP BY优化吗？（我正在使用这个版本：5.1.49-3）

测试表

CREATE TABLE `_test2_innodb` (
    `i` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `n` smallint(5) unsigned NOT NULL,
    `t` int(10) unsigned NOT NULL,
    `v` smallint(6) NOT NULL,
    PRIMARY KEY (`i`),
    KEY `i_n` (`n`),
    KEY `i_t` (`t`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `_test2_myisam` (
    `i` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `n` smallint(5) unsigned NOT NULL,
    `t` int(10) unsigned NOT NULL,
    `v` smallint(6) NOT NULL,
    PRIMARY KEY (`i`),
    KEY `i_n` (`n`),
    KEY `i_t` (`t`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

测试查询

SELECT MAX(i) FROM _test2_myisam GROUP BY n;

SELECT MAX(i) FROM _test2_innodb GROUP BY n;

结果

id, select_type, table, type, poss_keys, key, key_len, ref, rows, extra

1, SIMPLE, _test2_myisam , ALL, , , , , 19998, Using temporary; Using filesort

1, SIMPLE, _test2_innodb, index, , i_n, 2, , 20024, Using index

问题在于如果我使用MyISAM，将会进行全表扫描，在大型表上需要数小时的时间......而MySQL文档没有提到表引擎有不同的实现（http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html）。有人知道为什么内部处理方式不同吗？

（注：不，切换到InnoDB不是一个好的解决方案）谢谢

- Kristof Kotai

这两个测试表的定义完全相同，唯一不同的是所使用的表引擎。当我运行测试时，这两个表中的数据也完全相同。我应该再看些什么？ - Kristof Kotai

在当今这个时代，几乎没有理由使用MyISAM。引擎内部有它们自己的数据结构，并且可以做额外的工作，以便MySQL核心不必这样做。至于为什么MyISAM没有某些东西或者为什么有某些东西 - 你需要问代码的创建者或者查阅源代码。 - N.B.

在今天这个时代，几乎没有使用MyISAM的理由。MyISAM有许多优点，而InnoDB则没有。例如，我们构建使用/严重依赖地理空间数据的移动应用程序。例如，在InnoDB中，表上的地理空间索引根本不存在。而我们的服务/代码经常使用它，因此由此获得了巨大的性能提升... - Kristof Kotai

@Kristof：你说得对，这是InnoDB的两个限制之一：空间和全文搜索。 - ypercubeᵀᴹ

全文存在，但是地理空间索引不存在。我不会详细介绍如何实现类似的功能，但事实是InnoDB在使用内存和I/O方面要好得多，并且可以完成许多工作，因此MySQL核心不必担心。就个人而言，我从未使用过地理空间索引或处理过地理空间数据，因此我对该领域的了解非常有限，因此我不具备提出解决方法的可信度。 - N.B.

2个回答

0

这是因为InnoDB以索引组织的方式存储，基于主键。因此表扫描和主键扫描将完全相同。不幸的是，对于MyISAM来说情况并非如此，它必须进行文件排序。

- Michael

抱歉，但这与主键无关。这个GROUP BY查询需要在二级索引上进行优化。这就是为什么InnoDB查询的结果在“key”列中显示“i_n”的原因。也许测试查询让你感到困惑了。如果我在SELECT中使用“MAX(t)”，我会得到相同的结果。 - Kristof Kotai

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ypercubeᵀᴹ · Accepted Answer

尽管定义看起来相同，但这两个表的区别在于MyISAM表是“堆”，而InnoDB表是聚簇组织的表，例如，聚簇索引是表（通常是主键，在这里是(i)索引）。导致不同执行计划的另一个差异是，在InnoDB中，所有非聚簇索引（在本例中为您的(n)索引）也包括聚簇索引的列(i)，因此不需要进行全表扫描。换句话说，(n) InnoDB索引大致相当于一个(n, PK) MyISAM索引。

MyISAM引擎必须执行全表扫描或(n)索引的索引扫描，然后还要扫描表（获取i列的值）。因此，它选择第一个计划（完整扫描+文件排序以查找最大值）。

在MyISAM表上添加一个(n, i)索引后再次进行测试：

ALTER TABLE _test2_myisam
    ADD INDEX n_i (n, i) ;