具有数百万行的数据库的有效索引

Question

我有一个包含数百万行的MYISAM MySQL DB表，我被要求处理其中的行，但我需要首先使查询速度更快。

以前根本没有索引！我在“type”列上添加了一个新的索引，这很有帮助，但我想知道是否还有其他列可能也是最好的索引？

下面是我的CREATE TABLE：

CREATE TABLE `clicks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`companyid` int(11) DEFAULT '0',
`type` varchar(32) NOT NULL DEFAULT '',
`contextid` int(11) NOT NULL DEFAULT '0',
`period` varchar(16) NOT NULL DEFAULT '',
`timestamp` int(11) NOT NULL DEFAULT '0',
`location` varchar(32) NOT NULL DEFAULT '',
`ip` varchar(32) DEFAULT NULL,
`useragent` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `companyid` (`companyid`,`type`,`period`),
KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

典型的SELECT语句通常会根据companyid、type和contextid列进行过滤。

例如：

SELECT period, count(period) as count FROM clicks WHERE contextid in (123) AND timestamp > 123123123 GROUP BY period ORDER BY timestamp ASC

或

SELECT period, count(period) as count FROM clicks WHERE contextid in (123) AND type IN('direct') AND timestamp > 123123123 GROUP BY period ORDER BY timestamp ASC

我的问题的最后一部分是这样的:当我在type上添加索引时，大约花了1个小时--如果我要添加或删除多个索引，你可以在一个查询中完成吗?还是必须一个接一个地完成，然后等待每个查询完成？

谢谢你的想法。

Answer 1

索引真的很强大，但并不像你想象的那样是一门黑魔法。了解MySQL的EXPLAIN PLAN功能，这将帮助您系统地找到可以改进的地方：

http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

Answer 2

添加哪些索引实际上取决于您的查询。排序(GROUP BY)或选择(WHERE)的任何内容都是一个很好的索引候选者。

您可能还想看看how Mysql uses indexes。

至于添加索引所需的时间，如果您确定要添加多个索引，则可以执行mysqldump，在.sql文件中手动编辑表结构，然后重新导入。这可能需要一段时间，但至少您可以一次完成所有更改。然而，这并不真正符合边走边测试的想法……因此，请谨慎使用此方法。(当我修改许多具有相同结构的表，并希望为所有表添加一些索引时，我就这样做过。)

此外，我不能百分之百地确定，但我认为当您添加索引时，Mysql会创建一个带有索引的表的副本，然后删除原始表-因此请确保您的服务器/分区上有足够的空间来容纳当前表的大小&一定的余量。

Answer 3

在我看来，可以对timestamp和period进行索引，因为它们在WHERE子句中使用。

另外，用contextid = 123代替contextid in (123)，用type = 'direct'代替type IN('direct')