MySQL数据库的性能优化(只读)

Question

我们正在开发一个连接到大小为500 GB的MySQL数据库的报告工具。报告工具将从数据库中获取数百万行。该工具仅用于报表。

1. 它不会更新、插入或删除任何数据。
2. 我们将不需要使用任何连接，在某个时候，工具将只与单个表交互。
3. 工具将只在数据库上使用"order“、"where”、"limit (for分页)“子句。
4. 所有的表都有一个列"rowno“作为主键。两个表之间没有FK关系。

编辑：

MySQL版本：‘5.7.16-日志’

数据库引擎: MyISAM

使用的工具: MySQL Workbech

对此数据库进行性能优化的最佳方法是什么(比如使用特定的row_format等)？

Answer 1

MyISAM将成为已弃用 (Bill是stackexchange上的一个大击球手，以前在Percona!) --你现在应该把MyISAM改为InnoDB。您可以按照这里或这里的说明来做到这一点。

AIUI，甚至MySQL中的系统表都有4/正在被移动到InnoDB，参见这里和这里。后一种引用似乎表明，在即将发布的8版中，大多数系统表已移动到InnoDB。

您可以阅读MyISAM/InnoDB 这里的一些优缺点。

即使您现在没有使用InnoDB引擎的功能，将来也可能需要使用它们。

要回答这个问题，您有两个性能优化选项：

• 你可以把你的InnoDB TRANSACTIONs从这里调到READ ONLY，

或者你也可以

• 按照READ ONLY设置整个服务器为这里。

您可能想测试这些，但我相信，任何小的性能损失都值得为InnoDB引擎带来的优势付出代价！

它不会更新、插入或删除任何数据。

这有利于性能--在这种情况下，引擎不需要维护指向新记录和/或其他事务的指针链接列表。您应该将您的事务设置为仅按上面所示进行读取。

我们将不需要使用任何连接，在某个时候，工具将是只与单个表交互。

嗯..。一个没有连接的数据库--对我来说一直是个令人费解的概念。您确实意识到，如果表之间存在显式连接，优化者可以更好地决定处理哪个表的顺序--那些认为FOREIGN KEYs会招致性能损失的人不理解RDBMSs。

工具将只在数据库上使用"order“、"where”、"limit (用于分页)“子句。

GROUP BY肯定是吗？否则，为什么不简单地使用电子表格呢？在任何情况下，向人类用户提供数千/数百万行数据(在本例中显然是记录)是毫无意义的--人类只需扫描和跳过一页数字。

所有的表都有一个列"rowno“作为主键。两个表之间没有FK关系。

请参阅上面有关FOREGIN KEYs的内容，使用简单整数PRIMARY KEY作为您的代理项KEY没有问题。

Answer 2

不要使用LIMIT进行分页，而是使用记得你停下来的地方吗。

在一个500 on的表上报告--听起来像是“数据仓库”。另外，没有更新/删除--这是汇总表的主要候选--这将比其他任何东西都能给您带来更多的性能提升。考虑总结，然后抛出原始数据。

没有JOINs？听起来事实表会被长字符串弄得乱七八糟。这，如果没有其他的话，是一个很好的理由来做一些正常化。这500 to可能会缩小到200 to，从而加快许多任务。

“罗诺”--想想AUTO_INCREMENT。或者，想想是否有一个“自然”的PRIMARY KEY。当我看到模式时，我们可以进一步讨论这个问题。(我发现“自然”赢了2/3。)

不要争论，只需使用InnoDB。并将innodb_buffer_pool_size设置为大约70%的可用内存。其他任何东西都不需要从缺省值中改变。