mysql - 慢速 MySQL 左连接查询与中小型数据库

Question

我有两个基本表:

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT(32) NOT NULL auto_increment UNIQUE,
    `profile_type` INT(3),
    `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_login` DATETIME DEFAULT 0,
    `username` VARCHAR(255) NOT NULL,
    `password` VARCHAR(32) NOT NULL,
    PRIMARY KEY(`id`, `profile_type`)
);

+----------+
| count(*) |
+----------+
|     6455 |
+----------+


CREATE TABLE IF NOT EXISTS `client_dept` (
    `id` INT(32) NOT NULL auto_increment,
    `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `client_id` INT(32) NOT NULL,
    PRIMARY KEY (`id`, `client_id`)
);
+----------+
| count(*) |
+----------+
|     9729 |
+----------+

对于用户表中的这个示例，profile_type & 32 选择配置了客户端配置文件的所有用户。

我正在尝试在运行完整搜索和列表时加快对用户数据库的查询。目前，我正在尝试获取客户用户名的列表，以及分配给该用户的部门数量的计数 (id)。

以下是我尝试过的查询及其处理时间:

SELECT users.username 
FROM users 
LEFT JOIN client_dept ON users.id = client_dept.client_id 
WHERE users.profile_type & 32 
GROUP BY users.id;

集合中有 6020 行(9.85 秒)

SELECT users.username 
FROM users 
LEFT JOIN ( 
    SELECT client_id, id 
    FROM client_dept 
    GROUP BY client_id 
) AS dept ON users.id = dept.client_id;

集合中有 6020 行(2.27 秒)

SELECT user.username 
FROM ( 
    SELECT username, id 
    FROM users WHERE profile_type & 32 
) AS user 
LEFT JOIN ( 
    SELECT client_id, id 
    FROM client_dept 
    GROUP BY client_id ) AS dept ON user.id = dept.client_id;

集合中有 6020 行(2.21 秒)

我查看了其他资源，人们似乎认为子查询会降低 SQL 的速度，但在这种情况下，他们正在加速它。但是，我只有 <10k 用户，所以它不应该花费“秒”来处理，尤其是当我可以查询数据库标准并获得通常的 <0.01 秒响应时。

我是否明显做错了什么，或者是否有更好的方法来优化查询？这将用于(和正在用于)的一般页面执行 LIMIT $X,20，通过 HAVING 进行各种搜索，但主要查询速度慢让我担心。

Answer 1

你的查询很奇怪。

它可以优化它，但我可以提供其他查询来获得相同的结果。

   select u.username,c.c 

    from 
     (select count(*) as c,client_id from client_dept  group by client_id) as c
    left join 
      users as u 
      on u.id=c.client_id;

为了加速你可以在 my.conf 中增加这个变量

sort_buffer_size = 64M
join_buffer_size = 64M

此外，您还必须检查 users(id) 上的索引是否存在 - 很可能存在。