如何提高这段 PHP/MySQL 代码的性能？

Question

以下是一个较大的 PHP 脚本的一部分，该脚本使用 CSV 文件和客户端 ID 字段作为输入来向 MySQL 数据库中添加或禁用用户。

涉及到两个表格，即 users 和 users_clients。后者将用户与客户端之间的关系保存为一个用户可以属于多个客户端。

以下为表格结构： users 表格结构（还有更多字段）

id        | int(11) (primary key)
user      | varchar(100)
pass      | varchar(100)
category  | int(11)
date      | timestamp
name      | varchar(100)
email     | varchar(255)

用户索引

SEARCH    | user        | FULLTEXT
SEARCH    | name        | FULLTEXT
SEARCH    | email       | FULLTEXT

用户客户端结构

id_user   | int(11)
id_client | int(11)
status    | enum('active','inactive')

这是从CSV文件中添加每个用户的脚本基本流程：

1. Check if the user exists for that client.

   SELECT 
       LOWER(user)
   FROM
       users u
       INNER JOIN users_clients uc ON u.id = uc.id_user
   WHERE
       u.user = '$user'
       and uc.id_client = $id_client
   

2. If it doesn't exist, add it to the database.

   INSERT INTO 
       users ($fields,id_client)
   VALUES 
       ($values,'$id_operation')
   

3. Get the id of the inserted user. I know I could use something like mysql_insert_id here, but what about the race conditions?.

   SELECT
       u.id as id
   FROM
       users u
   WHERE
       u.user = '$user'
       and u.id_client = '$id_operation'
   

4. Associate the user with the corresponding client.

   INSERT INTO
       users_clients (id_user, id_client) 
   VALUES
       ('$id_user','$id_client')
   

目前表格中有 400,000 名用户。该脚本处理包含 500 名用户的 CVS 需要 10 分钟以上。

您将如何改进它以使其更快？

提前感谢。

附注：如果您想查看完整的函数，可在 pastebin 上找到。

- Lando

---

你应该创建一个存储过程来完成所有的工作。 - Nir Alfasi

http://codereview.stackexchange.com/ - j08691

您不应该在mysql_insert_id中出现竞争条件。只需为插入使用的连接提供即可。 - Eric Hogue

顺便说一句，你也可以通过改进mySQL配置（谷歌搜索“mysql primer”）来加快这个事情的速度。我不知道你的规模，但如果你每天做这个10小时，可能会有显著的性能提升。 - Sliq

@ceejayoz：更新了问题，包括表结构和索引。用户具有一个FULLTEXT索引，其中包括用户、姓名和电子邮件字段。 - Lando

@Panique：看起来很有趣，我会去看看。性能对于这个脚本并不是关键，因为它每天只使用几次，但学习编写更好的代码可以节省一些时间，也无妨尝试。 :) - Lando

Answer 1

INSERT INTO table (id,a,b,c) VALUES (5454,1,2,3)
ON DUPLICATE KEY
UPDATE table SET foo WHERE id=xyz;

1. 在数据库中设置索引
2. 使用mysqli代替mysql
3. 收集所有要插入的内容，并使用预处理语句/存储过程进行操作，例如这里如何将数组插入单个MySQL预处理语句中 w/ PHP 和 PDO
4. 不要执行500次SELECT查询，简单地获取整个数据库并通过foreach/while循环逐个检查所需内容
5. 使用上述构造方式

重要提示：对于上述语句，列id应该有唯一索引!!!

- Sliq

---

我认为mysqli除了使用预处理语句（从第三点显而易见）之外，并没有比mysql更快。 - Mike

@Mike 我不确定，但官方的php文档说“使用mysqli因为它更快”之类的话。我们只是不知道而已 ;) ...也许有人可以在这里提供一些基准测试？ - Sliq

幸运的是，有人已经在https://dev59.com/FnVC5IYBdhLWcg3w1E1q上回答了这个问题。 - Mike

#4 - 不要每次都使用 foreach，将您的用户名列表放入数组中，然后使用 in_array 进行测试 - 更高效。 - Basic

@Panique： #1 刚刚更新了问题，包括表结构和索引。我应该添加额外的索引吗？ - Lando

Answer 2

将INSERT语句放入事务中，不用担心，mysql_insert_id()是完全安全的，除非您切换到另一个数据库连接。

还可以将所有查询语句封装在事务中，以获得巨大的速度提升。

- jkrcma