MySQL查询速度优化

Question

我有一个关于查询速度的问题 - 目前它运行大约需要16秒钟，我需要加快速度！

我的表结构如下：

用户：

- id（int 10，主键） - username（varchar 100） - password（varchar 100）

用户元数据：

- id（int 10，主键） - user（int 10） - meta（varchar 100） - value（longtext）

我需要从用户元数据表中返回各种行的数据（例如名字、姓氏等）作为列。这个查询可以完成任务，但运行得太慢了：

SELECT
Users.id as id,
Users.username as username,
firstName.value as metaFirstName,
lastName.value as metaLastName,
userLevel.value as metaUsername,
employer.value as metaEmployer,
gto.value as metaGTO
FROM Users
LEFT JOIN (Users_meta as firstName) ON (firstName.user = Users.id AND firstName.meta = 'first_name')
LEFT JOIN (Users_meta as lastName) ON (lastName.user = Users.id AND lastName.meta = 'last_name')
LEFT JOIN (Users_meta as userLevel) ON (userLevel.user = Users.id AND userLevel.meta = 'user_level')
LEFT JOIN (Users_meta as employer) ON (employer.user = Users.id AND employer.meta = 'employer')
LEFT JOIN (Users_meta as gto) ON (gto.user = Users.id AND gto.meta = 'gto')

我需要能够在查询中添加WHERE和ORDER BY子句。

谢谢你的帮助。 :)

- Luke Carbis

---

你定义了哪些索引（使用 SHOW INDEXES FROM table1）？ - Salman A

来自用户：PRIMARY - id，以及来自users_meta：PRIMARY - id。 - Luke Carbis

这个问题在这里得到解答： https://dev59.com/cHRB5IYBdhLWcg3wWGEH - srini.venigalla

我真的不想使用group_concat。 - Luke Carbis

Answer 1

我不确定这是否更快，但或许可以尝试以下方法：

SELECT
    Users.id as id,
    Users.username as username,
    MAX(CASE WHEN Users_meta.meta = 'first_name' THEN Users_meta.value ELSE NULL END) AS metaFirstName,
    MAX(CASE WHEN Users_meta.meta = 'last_name' THEN Users_meta.value ELSE NULL END) AS metaLastName,
    MAX(CASE WHEN Users_meta.meta = 'user_level' THEN Users_meta.value ELSE NULL END) AS metaUsername,
    MAX(CASE WHEN Users_meta.meta = 'employer' THEN Users_meta.value ELSE NULL END) AS metaEmployer,
    MAX(CASE WHEN Users_meta.meta = 'gto' THEN Users_meta.value ELSE NULL END) AS metaGTO
FROM
    Users
    LEFT JOIN Users_meta
        ON Users_meta.user = Users.id
GROUP BY
    Users.ID,
    Users.username

- Arion

---

这个很好用！唯一的问题是，如果我想使用元数据添加WHERE子句，它就无法工作。我想我应该也提到了我打算这样做！例如，我不能运行“WHERE metaUserLevel ='student'”。我已经修改了原始描述以包括此要求。 - Luke Carbis

Answer 2

我首先会在表meta上添加一个复合索引：(meta, user, value)。或者(user, meta, value)。如果您的查询中有其他WHERE条件，这些索引肯定会有所帮助。

现在查询必须使用表Users_meta中的（几乎）所有数据，因此可能无法使用这些索引。 longtext数据类型是另一个问题。您确定需要这么宽的列吗？

- ypercubeᵀᴹ

---

谢谢大家，这确实有点帮助 :) 但仍然运行得相当慢。 - Luke Carbis

@Curbis：在添加了这个索引之后，你能加上EXPLAIN计划吗？ - ypercubeᵀᴹ