MySQL数据库性能调优、架构优化

Question

我有一个 MySQL 数据库，其架构非常简单。有 parent、child 和 access 表。

parent 存储 51 个字段，除 4 个 longtext 字段和主键 (一个bigint。除了主键之外，还有其他 3 个字段的索引。一个这样子表就可以将其作为外键引用。

child 存储 23 个字段，其中大部分是 varchar 以及一些 text 字段。 varchar(256) 字段用作外键以将其链接到父级。不过，外键字段的实际内容预计都少于 60 个字符。

accesss 有一个 bigint 字段和一个 varchar 字段，它们一起构成主键，bigint 字段是主键将其链接到parent的外键。

access 表用于指定哪些用户有权访问父级 中的哪些记录。可能有多个用户应该有权访问任何记录。

parent 中有大约 2e6 行（因此 access） ，child 中有大约 2e7 行。编辑：抱歉，access 有 5329840 行。即，parent 中的每一行在 access 中都有一行或多行。

上述架构基于旧的 FileMaker 数据库，我们希望将其迁移到 MySQL。我确信这并不理想，但我不知道具体原因。

查询的速度快慢取决于参数。因此，例如，如果 bob 可以访问多条记录，则以下查询将花费一两秒的时间。但是，如果用户 bob 没有访问权限的记录（例如，如果没有名为 bob 的用户），则查询将需要几分钟（例如 12 分钟）：

SELECT
    p."RecordID", p."SerialNumber", p."Folder", p."NoteType",
    p."FirstName", p."LastName", p."DOB", p."Body", p."From",
    p."DateTxt", a."UserName" AS Access
FROM parent AS p
INNER JOIN access AS a ON a."RecordID" = p."RecordID"
WHERE p."RecordID" > 123
AND a."UserName" = 'bob'
ORDER BY p."RecordID"
LIMIT 500;

以下是 EXPLAIN 关于查询的说法：

+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows   | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
|  1 | SIMPLE      | p     | range  | PRIMARY       | PRIMARY | 8       | NULL                | 731752 | Using where              |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 74      | db.p.RecordID,const |      1 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
2 rows in set (0.01 sec)

那么，有什么方法可以加快不匹配查询的速度吗？这可能是由于对所有内容都使用 varchar/text 字段造成的吗？使用 varchar(256) 字段作为外键会导致问题吗（尽管上面的查询中没有使用它）？或者是查询惹的祸？

编辑：我刚刚意识到 access 表上的 PRIMARY KEY ("RecordID", "UserName") 未用于 SELECT ... FROM 访问 WHERE UserName = 'blah'。我已经在 UserName 列上创建了一个索引，这似乎已经解决了问题。如果有人有建议，我仍然会很感激。

EXPLAIN 的当前输出：

+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows   | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
|  1 | SIMPLE      | p     | range  | PRIMARY       | PRIMARY | 8       | NULL                | 605020 | Using where              |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,UNidx | PRIMARY | 74      | db.p.RecordID,const |      1 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+--------------------------+
2 rows in set (0.00 sec)

编辑：@DRapp 的建议确实产生了巨大的差异。无论有或没有 access.UserName 上的索引，查询都很快。如果我删除索引并在没有 STRAIGHT_JOIN 的情况下尝试 DRapp 的查询，那么它会再次变慢。

DRapp 的查询在 access.UserName 上没有索引：

mysql> explain SELECT STRAIGHT_JOIN p."RecordID", p."SerialNumber", p."Folder", p."NoteType", p."FirstName", p."LastName", p."DOB", p."Body", p."From", p."DateTxt", a."UserName" AS Access     FROM access as a, parent AS p where a."UserName" = 'bob' and a."RecordID" > 123 and a."RecordID" = p."RecordID" order by a."RecordID" limit 500;
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
|  1 | SIMPLE      | a     | range  | PRIMARY       | PRIMARY | 8       | NULL          | 2382668 | Using where; Using index |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 8       | bb.a.RecordID |       1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
2 rows in set (0.00 sec)

与 access.UserName 上的索引相同的查询：

mysql> explain SELECT STRAIGHT_JOIN ...;
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
|  1 | SIMPLE      | a     | ref    | PRIMARY,UNidx | UNidx   | 66      | const         | 1209780 | Using where; Using index |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 8       | db.a.RecordID |       1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+--------------------------+
2 rows in set (0.00 sec)

在 access.UserName 上没有索引且没有 STRAIGHT_JOIN< 的相同查询/代码>：

mysql> explain SELECT p."RecordID", p."SerialNumber", p."Folder", p."NoteType", p."FirstName", p."LastName", p."DOB", p."Body", p."From", p."DateTxt", a."UserName" AS Access FROM access as a, parent AS p where a."UserName" = 'zzz' and a."RecordID" > 123 and a."RecordID" = p."RecordID" order by a."RecordID" limit 500;
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows   | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | p     | range  | PRIMARY       | PRIMARY | 8       | NULL                | 484016 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 74      | db.p.RecordID,const |      1 | Using where; Using index                     |
+----+-------------+-------+--------+---------------+---------+---------+---------------------+--------+----------------------------------------------+
2 rows in set (0.00 sec)

Answer 1

我总是很幸运地使用 MySQL 中的“STRAIGHT_JOIN”子句，并将主表基础放在限定符列表中的第一位。在这种情况下，您的“访问”表会查找 Bob，然后查看 Bob 有权查看的记录。如果 ACCESS 查询失败，则无需深入查看。此外，由于连接基于相同的“RecordID”，因此我更改了对“a”的引用。桌子。由于此查询首先基于用户名，因此我也有一个密钥。我对它的解释和性能也很感兴趣。

SELECT STRAIGHT_JOIN
      p."RecordID",
      p."SerialNumber",
      p."Folder",
      p."NoteType",
      p."FirstName", 
      p."LastName", 
      p."DOB",
      p."Body",
      p."From",
      p."DateTxt", 
      a."UserName" AS Access 
   FROM 
      access as a,
      parent AS p 
   where 
          a."UserName" = 'bob'
      and a."RecordID" > 123
      and a."RecordID" = p."RecordID"
   order by
      a."RecordID"
   limit 
      500