PHP和MySQL：优化数据库

Question

我有一个包含超过1000万行的数据库。现在查询它可能需要几秒钟才能找到一些基本信息。这并不理想，我知道最佳优化方法是最小化行数，但现在我没有时间这样做。

最简单的方法是优化MySQL数据库，使得查询时所需的时间短吗？

我不关心数据库的大小，这并不重要，因此任何增加大小的优化都可以。我不太擅长优化，目前我已经设置了索引，但我不确定从那里能获得多少改进。

我最终会适当地缩减数据库，但有没有快速的临时解决方案？

- sam

---

也许问题出在查询上... 也许是服务器硬件不够强大，我认为我们需要更多的信息。 - Michael B.

有没有什么方法可以改进简单的选择查询？ 查询文字就是“SELECT * FROM table WHERE column="something"”。从硬件方面来说，也许可以，但服务器并没有运行到最大容量，所以我不确定是否是这个原因，尽管显然更好的硬件是好事 :-) - sam

使用EXPLAIN命令来查看数据库服务器执行查询的方式。通过这些信息，您可以创建所需的索引。1000万条记录并不算太多，不应该会出现严重问题。除非每个记录都有1GB大小... ;) - Frank Heikens

我可以在不到0.5秒的时间内运行超过1亿行的复杂查询，但这是因为我有一个经过适当设计的数据库，利用了InnoDB集群索引、二级索引、去规范化、汇总表、正确的数据类型（tinyint unsigned 1字节 vs int unsigned 4字节）、正确的MySQL配置以及一系列其他优化。 （根据我的经验，分区会降低我的性能）1000万行数据并不算什么，正如Frank Heikens上面所说-向我们展示您的查询和解释计划，以便我们进一步评论。 - Jon Black

Answer 1

除了索引，您可能还需要查看表分区，特别是对于大型表格。 MySQL中的分区 由于我们拥有非常有限的信息，因此很难具体说明，但是适当的索引和分区可以大有作为。正确地创建索引可能是一个长话题，但是总的来说，您需要为查询的列创建索引。

例如，假设您有一个员工表，其中包含SSN、FNAME、LNAME等通常的列。除了这些列之外，我们还将在表中添加10个附加列。

现在您有以下查询：

SELECT FNAME, LNAME FROM EMPLOYEES WHERE SSN = 'blah';

忽略SSN可能是主键，并且可能已经在其上具有唯一索引的事实，您可以通过创建另一个包含列（SSN，FNAME，LNAME）的复合索引来获得性能优势。这样做有益处的原因是，数据库可以通过简单地查看复合索引来满足此查询，因为它包含所有所需值的排序和紧凑空间。(也就是说，更少的I/O)即使只有SSN的索引是执行全表扫描的更好访问方法，数据库仍然必须读取索引的数据块(I/O)，找到将包含指向满足查询所需记录的指针的值，然后需要读取不同的数据块(读取：更多随机I/O)以检索fname和lname的实际值。

显然，这非常简化了问题，但是使用这种方式的索引可以大大减少I/O并增加数据库的性能。

这里还有一些其他链接，您可能会发现有用：

• MySQL索引-需要多少个？
• 何时应该使用复合索引？
• MySQL查询优化（特别是“选择索引”部分）

- RC.

Answer 2

我看到你从数据库请求了40k行数据，这么多数据的负载需要一定的时间才能传输。

此外，不要问“如何总体上进行优化”。没有通用的优化方法。优化总是基于你特定情况的分析和研究得出的结果。

- Your Common Sense

Answer 3

在你经常搜索的列上使用索引。

- Felix Kling

---

已经在做这个了！有没有更好的方法来做呢？目前我已经对最活跃的搜索列（例如：WHERE x = y，其中x是我索引的列）进行了索引。 - sam

Answer 4

在你的例子中，'WHERE x=y'，如果y是列名，也要创建一个带有y的索引。

索引的关键是选择查询结果的数量应该在整个表的3％〜5％左右，并且速度会更快。

此外，归档表也很有帮助。我不知道如何做到这一点，大多数情况下是DBA的任务。 对于DBA来说，如果他们一直在做这个任务，那么这是一个简单的任务。

- exiter2000

Answer 5

如果您正在进行排序或复杂查询，您可能需要使用多列索引。例如，如果您正在搜索 x.name = 'y' OR x.phone = 'z'，则在 name、phone 上放置索引可能是值得的。这只是一个简化的例子，但如果您需要这样做，您仍需要进一步研究 :)

- spronkey

Answer 6

你的查询是否使用了索引？在选择查询上运行EXPLAIN会告诉你什么？

第一步（也是最简单的一步）是确保你的查询已经被优化。

- jasonbar

---

我刚刚运行了"ANALYZE TABLE"以获取更多信息，但基本查询的解释如下：http://pastebin.com/MfNEiqBy - sam