mysql 速度、表索引和选择/更新/插入

Question

我们有一个 MySQL 表，其中包含超过 7,000,000（是的，七百万）行。 我们总是每 5 秒执行如此多的 SELECT / INSERT / UPDATE 查询。

如果我们为该表创建 MySQL INDEX，这是一件好事吗？会不会造成数据损坏、MySQL服务丢失等不良后果？

小信息：

• MySQL 版本 5.1.56
• 服务器 CentOS
• 表引擎是 MyISAM
• MySQL CPU 负载始终在 200% - 400% 之间

Answer 1

一般来说，索引会提高 SELECT 操作的速度，但会减慢 INSERT/UPDATE/DELETE 操作的速度，因为发生更改时必须修改基表和索引。

Answer 2

说这样的话是非常困难的。我预计索引本身可能需要一些时间。但在那之后你应该会有一些改进。正如@Joe和@Patrick所说，这可能会影响你的修改时间，但选择会更快。

当然，还有一些其他方法可以提高插入和更新性能。如果立即可见更改并不重要，您当然可以批量更新。

Answer 3

索引将极大地帮助选择。特别是如果与常用过滤字段匹配得很好。并且您有一个很好的简单主键。它们将有助于缩短查询时间和处理周期。

缺点是如果您非常频繁更新/更改/删除这些记录，尤其是索引字段。即使在这种情况下，通常也是值得的。

您要报告的数量（选择语句）与更新的数量（应该！）会极大地影响您的初始设计以及数据库投入使用后的后续调整。既然您已经拥有了，测试将为您提供所需的答案。如果您确实执行了大量选择查询和大量更新，那么您的解决方案可能是时不时地将数据复制到报告表中。然后你就可以疯狂地建立索引而不会产生任何不良影响。

你其实问了一个很大的问题，你应该多研究一下。我上面提到的一般内容适用于大多数关系数据库，但特定数据库（在您的例子中为 MySQL）也有特定的行为，主要是它们如何决定何时何地使用索引。

Answer 4

如果您正在寻找性能，那么索引是最佳选择。索引可以加快查询速度。如果您有 700 万条记录，您的查询可能需要花费很多秒甚至一分钟，具体取决于您的内存大小。

一般来说，我会创建与最频繁的 SELECT 语句相匹配的索引。每个人都在谈论索引对表大小和速度的负面影响，但我会忽略这些影响，除非您有一个表 95% 的时间都在执行插入和更新，但即便如此，如果这些插入发生在晚上并且您进行查询白天，去创建这些索引，白天的用户会欣赏它。
如果有额外的索引，对插入或更新语句的实际时间影响是多少，可能是 0.001 秒？如果索引为每个查询节省了很多秒，我想更新索引所需的额外时间是非常值得的。

我唯一一次在创建索引时遇到问题（它实际上破坏了程序逻辑）是当我们向之前（由其他人）创建的没有主键的表添加主键并且程序期望SELECT 语句按创建顺序返回记录。创建主键改变了这一点，在没有任何 WHERE 子句的情况下选择时，记录以不同的顺序返回。
这显然首先是一个错误的设计，但是，如果您有一个较旧的程序并且遇到没有主键的表，我建议在添加主键之前查看读取该表的代码，以防万一。

最后还有一点关于创建索引的思考，字段的选择以及字段在索引中出现的顺序对索引的性能有影响。

Answer 5

我遇到了与您描述的相同的问题。

我做了一些更改，1 个查询从 11 秒传递到了几毫秒

1- 升级到 MariaDB 10.1

2- 将我的所有数据库更改为 ARIA 引擎

3- 将 my.cnf 更改为严格的最小值

4- 升级了 php 7.1（但这一个有一点影响）

5-使用 CentOS：在终端或通过 ssh 进行“Yum update”（通过保持所有内容最新）

---

1-MariaDB 是新的开源版本 MYSQL

2 - ARIA 引擎是 MYISAM 3 的演变 -

my.cnf 通常有太多变化，影响性能
这里是一个例子

[mysqld]
performance-schema=1
general_log=0
slow_query_log=0
max_allowed_packet=268435456

通过从 my.cnf 中删除所有额外的选项，它告诉 mysql 使用默认值。

在 MYSQL 5（5.1、5.5、5.6...）中，当我这样做时；我只注意到一个很小的差异。

但在 MariaDB 中 ->像这样的小 my.cnf 有很大的不同。

****** 所有这些变化；服务器硬件保持不变。

希望它可以帮助你