以下是更大的PHP脚本的一部分,该脚本使用CSV文件和客户端ID字段作为输入来添加或禁用MySQL数据库中的用户。
涉及两个表:用户和users_clients。后者保持用户和客户端之间的关系,因为一个用户可以属于多个客户端。
这是表的结构
用户结构(它有更多的字段)
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
users_clients结构
id_user | int(11)
id_client | int(11)
status | enum('active','inactive')
以下是从 CSV 文件添加每个用户的脚本的基本流程:
检查该客户端是否存在用户。
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
如果不存在,请将其添加到数据库中。
INSERT INTO users ($fields,id_client) VALUES ($values,'$id_operation')
获取插入的用户的 ID。我知道我可以在这里使用类似mysql_insert_id的东西,但是比赛条件呢?
SELECT u.id as id FROM users u WHERE u.user = '$user' and u.id_client = '$id_operation'
将用户与相应的客户端关联。
INSERT INTO users_clients (id_user, id_client) VALUES ('$id_user','$id_client')
表中当前有 400.000 个用户。该脚本需要 10+ 分钟来处理具有 500 个用户的 CVS。
您将如何改进它以使其更快?
提前谢谢。
PD:如果你想看到完整的功能,可以在pastebin上找到。
INSERT INTO table (id,a,b,c) VALUES (5454,1,2,3)
ON DUPLICATE KEY
UPDATE table SET foo WHERE id=xyz;
- 在数据库中设置索引
- 使用 Mysqli 而不是 MySQL
- 收集所有要插入的内容,并使用预准备语句/存储过程进行操作,如下所示 如何将数组插入到带有PHP和PDO的单个MySQL准备语句中
- 不要做 500 个 SELECT,简单地获取整个数据库并通过 foreach/while 循环完成它,检查你需要的东西
- 使用类似上面的结构
重要提示:对于上述语句,列 id 应具有唯一的索引!!
将
INSERT 包装到事务中,不用担心,除非您切换到另一个数据库连接,否则mysql_insert_id()
是完全安全的。
还可以将所有查询包装到事务中,从而大幅提高速度。