MariaDB vs PostgreSQL插入- PostgreSQL速度快2-4倍

Question

我在比较MariaDB 10.6vsPostgreSQL 14在INSERT手术中。我在两个数据库中创建了一个表，然后尝试在这两个数据库中插入100万行。在PostgreSQL中，仅需2秒即可将100万行INSERT。但是在MariaDB中，花了9秒才能INSERT 100万行。但是我想使用MariaDB，所以我试着改进my.ini，但是PostgreSQL的速度还是比MariaDB快2-4倍。这是我在两个数据库中的表(这两个数据库中都没有索引，只有带有自动增量的主键除外)

CREATE TABLE `project_user` (
    `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_username` VARCHAR(16) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_mobile` VARCHAR(16) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_email` VARCHAR(64) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_first_name` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_first_name_en` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_last_name` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_last_name_en` VARCHAR(32) NULL DEFAULT NULL COLLATE 'ascii_general_ci',
    `user_country` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `user_gender` BIT(1) NULL DEFAULT NULL,
    `user_registered_ip` INT(10) UNSIGNED NOT NULL,
    `user_registered_at` INT(10) UNSIGNED NOT NULL,
    `user_status` BIT(1) NOT NULL DEFAULT b'1',
    PRIMARY KEY (`user_id`) USING BTREE
)
COLLATE='ascii_general_ci'
ENGINE=InnoDB
;

在PostgreSQL中也等于这个表(除了我使用的位).

在PostgreSQL中，我使用此操作插入了100万行

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT i,i+1
FROM generate_series(1, 1000000) AS i;

在MariaDB中，我使用了这个函数

BEGIN
    FOR i IN 1..1000000
    DO
        INSERT INTO `project_user` (`user_registered_ip`,`user_registered_at`)
        VALUES (i, i+1);
    END FOR;
    RETURN 1;
END

这是my.ini

[mysqld]
datadir=C:/Program Files/MariaDB 10.6/data
port=3306
max_connections=8192
max_allowed_packet=1G
skip_external_locking
skip_name_resolve
default_storage_engine=InnoDB
innodb_file_per_table=1
innodb_buffer_pool_size=24G
innodb_buffer_pool_instances=24
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=8M
innodb_log_file_size=2G
innodb_sort_buffer_size=16M
innodb_stats_on_metadata=0
innodb_thread_concurrency=6
innodb_read_io_threads=64
innodb_write_io_threads=64
key_buffer_size=512M
concurrent_insert=2
back_log=512
thread_cache_size=100
thread_stack=192K
interactive_timeout=60
wait_timeout=60
join_buffer_size=4M
read_buffer_size=3M
read_rnd_buffer_size=4M
sort_buffer_size=4M
table_definition_cache=40000
table_open_cache=40000
open_files_limit=60000
max_heap_table_size=128M
tmp_table_size=128M
slow_query_log=0
long_query_time=5
log_queries_not_using_indexes=0

[mysqldump]
quick
max_allowed_packet=1024M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.6/lib/plugin

有没有办法提高mariadb的插入速度？

Answer 1

为了比较相同的东西: postgresql：

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT i,i+1
FROM generate_series(1, 1000000) AS i;

mariadb相当于：

INSERT INTO project_user (user_registered_ip,user_registered_at) 
SELECT seq,seq+1 FROM seq_1_to_1000000;

那样做会更好

Answer 2

您应该尝试一种(稍微)更类似的方法，例如，在MariaDB中使用一个交叉连接来创建一个集合：

insert into project_user (user_registered_ip, user_registered_at)
select r, r+1
from (
  select Row_Number() over(order by (select null) ) r
  from information_schema.COLUMNS x
  cross join information_schema.COLUMNS y
  limit 1000000
)x;

另一个比较是，在我的本地Server (2019)上，使用类似的while循环和单行insert / values，100万行花费7秒；使用上述交叉连接方法中的单个插入时为1秒。