大型数据库和MySQL

Question

我们正在开展一个新项目，需要大量数据分析，但我们发现这非常慢。我们正在寻找改变我们软件和/或硬件方法的方法。

我们目前正在运行亚马逊EC2实例（Linux）：

High-CPU Extra Large Instance

7 GB of memory
20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units each)
1690 GB of instance storage
64-bit platform
I/O Performance: High
API name: c1.xlarge


processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 26
model name      : Intel(R) Xeon(R) CPU           E5506  @ 2.13GHz
stepping        : 5
cpu MHz         : 2133.408
cache size      : 4096 KB

MemTotal:      7347752 kB
MemFree:        728860 kB
Buffers:         40196 kB
Cached:        2833572 kB
SwapCached:          0 kB
Active:        5693656 kB
Inactive:       456904 kB
SwapTotal:           0 kB
SwapFree:            0 kB

数据库的一部分是文章和实体，还有一个链接表，例如：

mysql> DESCRIBE articles_entities;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | char(36)     | NO   | PRI | NULL    |       | 
| article_id | char(36)     | NO   | MUL | NULL    |       | 
| entity_id  | char(36)     | NO   | MUL | NULL    |       | 
| created    | datetime     | YES  |     | NULL    |       | 
| modified   | datetime     | YES  |     | NULL    |       | 
| relevance  | decimal(5,4) | YES  | MUL | NULL    |       | 
| analysers  | text         | YES  |     | NULL    |       | 
| anchor     | varchar(255) | NO   |     | NULL    |       | 
+------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

从下面的表格中可以看出，我们有很多协会以每天超过100,000的速度增长。

mysql> SELECT count(*) FROM articles_entities;
+----------+
| count(*) |
+----------+
|  2829138 | 
+----------+
1 row in set (0.00 sec)

下面这个简单的查询需要太长时间（12秒）

mysql> SELECT count(*) FROM articles_entities WHERE relevance <= .4 AND relevance > 0;
+----------+
| count(*) |
+----------+
|   357190 | 
+----------+
1 row in set (11.95 sec)

我们应该考虑什么来改善我们的查找时间？不同的数据库存储方式？不同的硬件设备。

- Lizard

---

你是否考虑过使用非char类型的ID？在我使用PostgreSQL连接超过2000万条和5亿条记录的表时，使用数值类型的ID可以显著提高查询速度，比使用字符类型的ID要快得多。 - John P

@Fernando - 或许使用MyISAM可以加快速度？但是由于它不支持聚集索引，我对此表示怀疑！https://dev59.com/s2855IYBdhLWcg3wUCaC#4421601 - Jon Black

这是MyIsam，我会考虑切换到int ids。 - Lizard

你能否提供一下你提供的慢查询语句的 EXPLAIN 信息？ - 0xCAFEBABE

也许你的第一步应该是雇用一位有经验优化大型系统的数据库专业人士。从你的设计（一个char（36）PK？）和问题中可以清楚地看出你没有这样的人。高性能大型数据库的设计和优化不应该由业余爱好者来完成。这不是通过论坛上的问题就能解决的事情。 - HLGEM

Answer 1

如mrorigo所要求，请提供SHOW CREATE TABLE articles_entities，以便我们可以查看您表中实际的索引。

MySQL文档中有一些说明：http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html 如果该表具有多列索引，则优化器可以使用索引左侧的任意前缀来查找行。例如，如果您在(col1、col2、col3)上具有三列索引，则可以查询(col1)、(col1、col2)和(col1、col2、col3)。

如果列不是索引的最左侧列，则MySQL将无法使用索引。

因此，如果relevance是多列索引的一部分，但不是该索引的最左列，则该索引将不用于您的查询。

这是一个常见的问题，经常被忽视。

- YoGiN

Answer 2

使用 char(36) 作为 MySQL 键不是最快的方法。如果可能，使用 INT 类型作为键。如果索引 CHAR 列，与（BIG）INT 索引相比，索引将非常大（如果没有“正确”创建）。但是，如果您的列值不是数字，则必须使用 CHAR 列（它们仍然比 VARCHAR 快，但可能会创建大型索引）。

请提供表的 SHOW CREATE TABLE，以查看键/索引参数。正如先前的回答所说，查询的 EXPLAIN 也可以帮助提供更好的答案。

PS. 使用 SHOW TABLE STATUS LIKE '{table_name}' 查看表的索引（和数据）大小。

- origo

Answer 3

在查询性能方面，有三件事情是很重要的：

索引。 内存。 其他所有。

首先要做的是检查您的索引。对您的查询进行 EXPLAIN，以了解 MySQL 如何处理它们。

如果看起来合理，下一步就是检查内存。您的总数据库有多大？如今内存很便宜，从内存运行的查询比必须从磁盘读取的查询快得多。

在探索了这些之后，如果性能仍然很慢，那么可能是考虑其他选项的时候了。

- Pixy Misa

---

以上所有都完成了，因此问题是，您能提供任何指针吗？ - Lizard

甚至在讨论索引之前，我们需要了解磁盘I/O。对于花费12秒的查询，它需要多少磁盘I/O？DBMS使用了什么查询策略？是全表扫描吗？从那里我们可以进入索引策略。 - Walter Mitty