为什么我的mysql表必须经常进行优化

Question

我有一个mysql表，12列，一个主键和两个唯一键。我在这个表中大约有86000行/记录。我使用以下mysql代码：

INSERT INTO table (col2,col3,-------col12) VALUES ($val2,$val3,----------$val12) ON DUPLICATE KEY UPDATE col2=VALUES($val2), col3=VALUES($val3),----------------col12=VALUES($val12)

当我从cpanel phpmyadmin查看这个表的结构时，我可以看到“优化表”链接就在表的索引信息下面。如果单击该链接，将对表进行优化。

但我的问题是，为什么我在这个表中经常看到“优化表”链接(出现在3/4天内)，而该数据库的其他表没有显示优化表链接(它们每月显示一次链接，甚至每两个月甚至每两个月显示一次)。

因为我没有删除这个表行，只是插入，如果找到重复的键，只是更新，那么为什么需要这么频繁地进行优化呢？

Answer 1

简短答案：切换到Innodb

MyISAM存储引擎使用BTree作为索引并创建索引文件。每次插入大量数据时，这些索引都会更改，这就是为什么需要优化表来重新组织索引并重新获得一些空间的原因。

与Innodb相比，MyISAM的索引机制占用了更多的空间。

阅读http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/下面的链接

Innodb与MyISAM相比还有许多其他优点，但这是另一个主题。

Answer 2

我将解释插入记录如何影响MyISAM表，并解释优化的作用，因此您将了解为什么插入记录会产生如此大的影响。

数据

使用MyISAM时，当您插入记录时，数据只是简单地附加到数据文件的末尾。

在MyISAM表上运行优化会整理数据，物理上对其重新排序以匹配主键索引的顺序。这加快了连续记录读取(和表扫描)的速度。

指数

插入记录还会向索引中的B树节点添加叶子。如果节点已满，则必须将其拆分，实际上至少重新生成索引的该页。

优化MyISAM表时，索引会被压缩，在重新生成索引页之前允许进行更多的扩展(插入)。这个更平的索引也加快了搜索速度。

统计

MySQL还存储关于密钥分发的每个索引的统计信息，查询优化器使用这些信息来帮助开发一个良好的执行计划。插入(或删除)许多记录会导致这些统计数据过时。

优化MySQL会在索引的删除和重建之后重新计算表的统计信息。

与追加的

当您附加数据(添加一个具有较高主键值的记录，例如使用auto_increment)时，该数据将不需要稍后整理，因为它已经处于正确的物理顺序。此外，在索引中追加(按顺序插入)时，节点保持不变，因此也不需要进行重建。

诉InnoDB

InnoDB在插入时也有同样的问题，但是由于数据是按主键按顺序排列的，这是因为它的聚集索引，所以您首先(在插入时)来保持数据的有序性，而不是以后必须整理数据。尽管如此，优化InnoDB确实通过平放B树节点和释放未使用的(已删除的)键来优化数据，从而改进了顺序读取(表扫描)，而辅助索引类似于MyISAM中的索引，因此它们被重建以使其扁平。

结论

我不是想找个理由来支持MyISAM。由于聚集索引，InnoDB具有更好的读取性能，并且由于记录级锁定和MyISAM的表锁定(假设并发用户)而具有更好的更新和附加性能。同时，InnoDB也有酸性。

不过，我的目标是回答你的直接问题，提供一些技术细节，而不是猜测和传闻。

两个数据库存储引擎都不会自动优化自身。