如何加快MySQL的查询执行速度

Question

您必须填写表格1至1000000：

Table view
CREATE TABLE `test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `value` INT UNSIGNED NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;"

我写了一个函数来做这件事，但向表中添加数据太慢了，我该如何提高插入数据的性能？

function InsertData(){
global $MySQL;
for($i = 1; $i != 1000000; $i++){
$MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
}
$MySQL->close();
}

Answer 1

您可以使用Transactions，以便每数千次插入(或者，如果您勇敢的话，在数百万次查询之后(只提交一次。以下是(勇敢的(例子：

function InsertData(){
global $MySQL;
// Start transactions
$MySQL->query('SET autocommit=0;');
$MySQL->query('START TRANSACTION;');
for($i = 1; $i != 1000000; $i++){
$MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
}
// So far, nothing as actually been saved to database

// Commit all inserts.
$MySQL->query('COMMIT;');
$MySQL->query('SET autocommit=1;');
$MySQL->close();
}

如果由于MySQL的限制，这对于一个单独的事务来说太多了，你可以每隔10.000次插入就执行一次提交：

function InsertData(){
global $MySQL;
// Start transactions
$MySQL->query('SET autocommit=0;');
$MySQL->query('START TRANSACTION;');
for($i = 1; $i != 1000000; $i++){
$MySQL->query("INSERT INTO `name` (`id`, `value`) VALUES ($i, $i);");
if($i % 10000 == 0) {
$MySQL->query('COMMIT;');
$MySQL->query('START TRANSACTION;');
}
}
// So far, nothing as actually been saved to database

// Commit all inserts.
$MySQL->query('COMMIT;');
$MySQL->query('SET autocommit=1;');
$MySQL->close();
}

注意最终的极限->https://stackoverflow.com/a/2298325/2814721

当然，这是一个实验或一次性脚本。不建议在生产数据库中执行此操作。

Answer 2

使用递归CTE将逻辑移动到数据库怎么样？

insert into name (id, value)
with recursive cte as (
select 1 id
union all select id + 1 from cte where i < 1000000
)
select id, id from cte

这可能需要使用递归同时生成多行。另一种选择是只生成10行，然后将行相乘：

insert into name (id, value)
with recursive cte as (
select 0 id
union all select id + 1 from cte where i < 9
)
select id, id 
from (
select 1 + c0.id + c1.id * 10 + c2.id * 100 + c3.id * 1000 + c4.id * 10000 + c5.id * 100000 id
cte c0
cross join cte c1
cross join cte c2
cross join cte c3
cross join cte c4
cross join cte c5
) t

Answer 3

尝试此查询：

INSERT INTO `test` (`id`, `value`) 
SELECT @row := @row + 1 AS row, @row 
FROM (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t1,
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t2, 
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t3, 
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t4, 
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t5, 
(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS t6, 
(SELECT @row:=0) AS nums;

这是一个"；插入。。。选择"SELECT语句本身生成一百万行的语句类型，其中填充了对(1,1(、(2,2(等。以下是它的工作原理：

1. 表t1、t2、t3、t4、t5、t6各为10行。将它们交叉连接会生成10^6=1000000个组合，因此生成的表将包含一百万行
2. 对于每一行，我们选择@row变量两次。不仅如此，我们还将它加1
3. nums表仅用于在一开始就将变量初始化为0
4. 生成的表被传递给INSERT语句，数据被存储在表中

一个看起来更干净的解决方案是将递归CTE与更新的MySQL/MariaDB一起使用。这是由用户GMB:提交的

INSERT INTO test (id, value)
WITH RECURSIVE temp AS (
SELECT 1 AS row
UNION SELECT row + 1 
FROM temp
WHERE row < 1000000
)
SELECT row, row
FROM temp;

根据我的测试，它有点慢。我没有监控内存使用情况。