MySQL分区表的工作速度慢于等效的未分区表

Question

帮助!我在MySql中有一个表(没有分区)，它有4500 000行：

CREATE TABLE stat_accounts_history (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  accountId bigint(20) DEFAULT NULL,
  month varchar(255) DEFAULT NULL,
  purchase decimal(19, 2) DEFAULT NULL,
  purchaseCard decimal(19, 2) DEFAULT NULL,
  requiredPurchase decimal(19, 2) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX IDX_stat_accounts_history_accountId (accountId),
  INDEX IDX_stat_accounts_history_month (month)
)
ENGINE = INNODB
AUTO_INCREMENT = 4707631
AVG_ROW_LENGTH = 241
CHARACTER SET utf8
COLLATE utf8_general_ci;

我将表中的所有数据导出，并将其导入等效的分区表：

CREATE TABLE stat_accounts_history_part (
  accountId bigint(20) NOT NULL,
  month varchar(255) NOT NULL DEFAULT '',
  purchase decimal(19, 2) DEFAULT NULL,
  purchaseCard decimal(19, 2) DEFAULT NULL,
  requiredPurchase decimal(19, 2) DEFAULT NULL,
  monthRef bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (accountId, monthRef),
  INDEX IDX_stat_accounts_history_part10_monthRef (monthRef),
  UNIQUE INDEX UK_stat_accounts_history_part1 (id, monthRef)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 242
CHARACTER SET utf8
COLLATE utf8_general_ci
PARTITION BY LIST (monthRef)
(
PARTITION p2013_01 VALUES IN (9)
ENGINE = INNODB,
PARTITION p2013_02 VALUES IN (10)
ENGINE = INNODB,
PARTITION p2013_03 VALUES IN (11)
ENGINE = INNODB,
PARTITION p2013_04 VALUES IN (12)
ENGINE = INNODB,
PARTITION p2013_05 VALUES IN (13)
ENGINE = INNODB,
PARTITION p2013_06 VALUES IN (14)
ENGINE = INNODB,
PARTITION p2013_07 VALUES IN (15)
ENGINE = INNODB,
PARTITION p2013_08 VALUES IN (16)
ENGINE = INNODB,
PARTITION p2013_09 VALUES IN (17)
ENGINE = INNODB
);

在我的web应用程序(Java+Spring+Hibernate)中，我编写了测试对此表的查询的方法：

for(int i = 0; i < 100; i++){
    long timeIter = System.nanoTime();
    dao.createSQLQuery("select count(accountId) from stat_accounts_history_part where monthRef=9 and (purchase+purchaseCard)>=requiredPurchase").list();
    System.out.print((long)((System.nanoTime() - timeIter)/1000000) + "\t");

    timeIter = System.nanoTime();
    dao.createSQLQuery("select count(accountId) from stat_accounts_history_part where monthRef=17 and (purchase+purchaseCard)>=requiredPurchase").list();
    System.out.print((long)((System.nanoTime() - timeIter)/1000000) + "\t");

    timeIter = System.nanoTime();
    dao.createSQLQuery("select count(id) from stat_accounts_history where month='2013.01' and (purchase+purchaseCard)>=requiredPurchase").list();
    System.out.print((long)((System.nanoTime() - timeIter)/1000000) + "\t");

    timeIter = System.nanoTime();
    dao.createSQLQuery("select count(id) from stat_accounts_history where month='2013.09' and (purchase+purchaseCard)>=requiredPurchase").list();
    System.out.print((long)((System.nanoTime() - timeIter)/1000000) + "\t");

    System.out.println();
}

我看到的是(1-2 cols分区表，3-4 cols非分区表)：

1894   479   9   541    
436    442   2   2  
447    458   2   2  
469    469   2   2  
439    451   2   2  
468    453   3   2

我尝试过使用Hibernate和JDBC连接执行查询--结果是相同的。

为什么对分区表的查询工作速度较慢？

Answer 1

我自己解决了这个问题。

1. 您应该在innodb_file_per_table = 1中设置my.cnf。
2. MySQL - http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html中的分区表不支持查询缓存。