MySQL查询非常缓慢

Question

我在MySQL中有两个表，table1有1,013,347个实体和38个属性，table2有7,343,905个实体和10个属性。在下面的查询中（用于获取分页的行数），table1.ID是主键，table2.ID是外键（都有索引），HAVING子句获取值如果超过一定百分比，此处为50%。

SELECT SQL_CALC_FOUND_ROWS * 
FROM table1 INNER JOIN table2 ON table1.ID = table2.ID 
WHERE table1.attribute1 LIKE 'D%' 
GROUP BY table2.ID 
HAVING (COUNT(table2.ID) * (100/18)) >= '50'

即使是我在这里发布的简化状态，通过命令行运行此查询也需要不少于5分钟。我知道必须对查询、PHP代码（值“50”和“D”通过PHP变量分配）和/或我的MySQL配置进行更改以加快速度（我使用最新的XAMPP并具有默认配置）。非常感谢任何帮助。

编辑1：所有属性都是TINYTEXT，除了ID属性是VARCHAR（9）。

编辑2：EXPLAIN SELECT...返回：

+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
| id | select_type | table  | type | possible_keys | key         | key_len | ref  | rows    | Extra                           |
+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | table2 | ALL  | NULL          | NULL        | NULL    | NULL | 7343905 | Using temporary; Using filesort |
|  1 | SIMPLE      | table1 | ref  | ID            | ID          | 29      | func |       1 | Using where                     |
+----+-------------+--------+------+---------------+-------------+---------+------+---------+---------------------------------+
2 rows in set (0.00 sec)

- deathonater

---

当你使用EXPLAIN SELECT ...时，你会得到什么？ - user212218

不会影响性能，但不要引用数字'50'，因为最终会让你出错。此外，还有指定的编码恐怖 "分页已死" 参考链接。 - Ben

你是否为表格table1的attribute1字段创建了索引，因为你在执行一个LIKE操作？ - Marc

@deathonater，你能否在这个表上也发布索引。慢的原因是由于文件排序选项。我还要补充一下Ben所说的观察结果，如果你引用数字，那么MySQL需要进行atoi转换以进行比较。我曾经看到过这种情况影响早期版本的性能，但不确定你使用的是哪个版本。 - g13n

你用LIKE子句过滤掉了多少记录？用HAVING子句又过滤掉了多少记录？ - Ami

Answer 1

以下是一些潜在的改进方案：

• 您正在使用VARCHAR（9）类型的ID，并使用这些字段执行联接。引入整数代理键而不是varchars可能是一个好主意，以加速联接。请参见此讨论。
• LIKE运算符通常很耗费资源。考虑您的用法; 就像Marc建议的那样，您应该为table1.attribute1创建索引。
• 也许您可以通过完全省略LIKE来加快查询速度：例如，您可以使用RIGHT()而不是使用“D％”，尽管我不确定它是否会更快。如果表中的数据很少更改，则可以创建一个新的带有预剪切table1.attribute1值开头的索引列；但这取决于php脚本之后插入的值。

- Dominik

Answer 2

提高性能的几个想法：

1. 必须对index table2.ID和table1.ID进行索引
2. 如果可能，将id列设置为bigint，将table1.attribute1设置为varchar。请根据列的预定数据长度定义适当大小的varchar列。

3. 不要在SQL内部进行计算（100/18），请用以下方式替换：

   HAVING (COUNT(table2.ID) * (5.5555)) >= 50

（由于table2.ID现在是bigint，数学比较会稍微快一些）

我认为，在此查询中，like子句非常重要，即使like很昂贵，最好也对table1.attribute1进行索引。

希望这可以帮助到您。

- Sanath

---

在 attribute1 上创建索引会加速 like 子句吗？ - Kos