我有一个很大的表，其中包含大约 300 万条记录。 当运行一个非常简单的查询时，将此表连接到其他几个表(都带有索引和/或主键)，查询将需要大约 25 秒才能完成! “Handler_read_next”的值大约是700万!

Number of requests to read the next row in key order, incremented if you are querying an index column with a range constraint or if you are doing an index scan.

自从这张表开始变大后，这个问题才开始出现。

现在，如果我在此表上执行“优化表”，查询将在大约 0.02 秒内运行，“Handler_read_next”的值约为 1500。

差异怎么会这么大，我真的必须设置一个计划查询，每周大约优化一次这个表吗？即便如此，我还是想知道这背后的含义以及为什么mysql会有这样的行为。当然，此表中的行几乎被删除和更新，但它是否会在仅一周内变得如此严重碎片化以至于查询从 0.02 秒变为 25 秒？

编辑:根据请求，这里出现有问题的查询:

SELECT *
FROM budget_expenses 
  JOIN budget_categories 
    ON  budget_categories.BudgetAreaId = budget_expenses.BudgetAreaId 
    AND budget_categories.BudgetCategoryId = budget_expenses.BudgetCategoryId
  LEFT JOIN budget_types 
    ON  budget_types.BudgetAreaId = budget_expenses.BudgetAreaId 
    AND budget_types.BudgetCategoryId = budget_expenses.BudgetCategoryId 
    AND budget_types.BudgetTypeId = budget_expenses.BudgetTypeId
WHERE budget_expenses.BudgetId = 1 
  AND budget_expenses.ExpenseDate >= '2012-11-25' 
  AND budget_expenses.ExpenseDate <= '2012-12-24' 
  AND budget_expenses.BudgetAreaId = 2 
ORDER BY budget_expenses.ExpenseDate DESC, 
         budget_expenses.ExpenseTime IS NULL ASC, 
         budget_expenses.ExpenseTime DESC

(BudgetAreaId, BudgetCategoryId) 是 budget_categories 中的主键，(BudgetAreaId, BudgetCategoryId, BudgetTypeId) 是 中的主键>预算类型。在 budget_expenses 中，这 3 个键是索引，而且 ExpenseDate 也有一个索引。此查询返回大约 20 行。

显示创建表:

CREATE TABLE `budget_areas` (
  `BudgetAreaId` int(11) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`BudgetAreaId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `budget_categories` (
  `BudgetAreaId` int(11) NOT NULL,
  `BudgetCategoryId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `SortOrder` int(11) DEFAULT NULL,
  PRIMARY KEY (`BudgetAreaId`,`BudgetCategoryId`),
  KEY `BudgetAreaId` (`BudgetAreaId`,`BudgetCategoryId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


CREATE TABLE `budget_types` (
  `BudgetAreaId` int(11) NOT NULL,
  `BudgetCategoryId` int(11) NOT NULL,
  `BudgetTypeId` int(11) NOT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `SortId` int(11) DEFAULT NULL,
  PRIMARY KEY (`BudgetAreaId`,`BudgetCategoryId`,`BudgetTypeId`),
  KEY `BudgetAreaId` (`BudgetAreaId`,`BudgetCategoryId`,`BudgetTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 CREATE TABLE `budget_expenses` (
  `ExpenseId` int(11) NOT NULL AUTO_INCREMENT,
  `BudgetId` int(11) NOT NULL,
  `TempId` int(11) DEFAULT NULL,
  `BudgetAreaId` int(11) DEFAULT NULL,
  `BudgetCategoryId` int(11) DEFAULT NULL,
  `BudgetTypeId` int(11) DEFAULT NULL,
  `Company` varchar(255) DEFAULT NULL,
  `ImportCompany` varchar(255) DEFAULT NULL,
  `Sum` double(50,2) DEFAULT NULL,
  `ExpenseDate` date DEFAULT NULL,
  `ExpenseTime` time DEFAULT NULL,
  `Inserted` datetime DEFAULT NULL,
  `Changed` datetime DEFAULT NULL,
  `InsertType` int(1) DEFAULT NULL,
  `AccountId` int(11) DEFAULT NULL,
  `BankCardId` int(11) DEFAULT NULL,
  PRIMARY KEY (`ExpenseId`),
  KEY `BudgetId` (`BudgetId`),
  KEY `AccountId` (`AccountId`),
  KEY `Company` (`Company`) USING BTREE,
  KEY `ExpenseDate` (`ExpenseDate`),
  KEY `BudgetAreaId` (`BudgetAreaId`),
  KEY `BudgetCategoryId` (`BudgetCategoryId`),
  KEY `BudgetTypeId` (`BudgetTypeId`),
  CONSTRAINT `budget_expenses_ibfk_1` FOREIGN KEY (`BudgetId`) REFERENCES `budgets`    (`BudgetId`)
) ENGINE=InnoDB AUTO_INCREMENT=3604462 DEFAULT CHARSET=latin1

复制粘贴后，我将 budget_categories 表上的 MyIsam 更改为 Innodb。

编辑:从 myisam 到 innodb 的更改没有任何区别。查询现在非常慢，在我优化 budget_expenses 表后仅 12 小时!

下面是查询的解释，现在大约需要 9 秒:

http://jsfiddle.net/dmVPY/1/

最佳答案

啊 MyISAM....

尝试将表类型(也称为“存储引擎”)更改为 InnoDB。

如果这样做，请确保 my.cnf 中的 innodb_buffer_pool_size 为 a sensible value - 默认值太小。

关于mysql - 为什么我必须优化表格？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13829595/