mysql - 优化 MySQL 表索引/查询

Question

我有一个大约需要 19 秒才能运行的查询，这已经很长时间了。

这是我慢日志的结果:

# Query_time: 19.421110  Lock_time: 0.000171 Rows_sent: 6  Rows_examined: 48515488
use c3_xchngse;
SET timestamp=1398891560;
SELECT *
                 FROM rates
                 WHERE id IN (
                    SELECT Max(id)
                    FROM rates
                    WHERE LOWER(`currency`) = LOWER('eur')
                    GROUP BY bankId
                );

我尝试添加索引，例如:

ALTER TABLE  `c3_xchngse`.`rates` ADD INDEX  `searchIndex` (  `id` ,  `currency` ,  `bankId` )

但它似乎并没有优化查询，它们一直占用很长时间。我还尝试为上面查询中提到的每一列添加单独的索引，但也没有帮助。

这是我的表，今天包含大约 7000 行:

CREATE TABLE IF NOT EXISTS `rates` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bankId` int(11) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `buy` double NOT NULL,
  `sell` double NOT NULL,
  `addDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `since` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique` (`bankId`,`currency`,`since`),
  KEY `bankId` (`bankId`),
  KEY `currency` (`currency`),
  KEY `searchIndex` (`id`,`currency`,`bankId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='The rates' AUTO_INCREMENT=6967 ;

如何优化我的查询或优化表并获得完全相同的结果，但速度更快？

Answer 1

使用 LOWER(currency) 会使您的索引变得无用。

规范化表中的数据:

UPDATE rates SET currency = LOWER(currency) WHERE 1;

并确保传递给查询的任何参数在它命中查询之前都被放入小写。

此外，您可以将货币字段设为 ENUM 类型以帮助进行内部索引: https://dev.mysql.com/doc/refman/5.0/en/enum.html