MySQL使用存储过程插入100万条记录
业务上为了测试DB写入性能，需测试大量数据写入速度，数据库部署在aws上，4核8G内存100G硬盘，MySQL5.7.27
--创建测试数据库
create database a1 default charset utf8mb4;
--创建测试表
CREATE TABLE `test110` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`comp` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
`aid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--创建插入存储过程
DELIMITER $$
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i=0;
while i<1000000 do
INSERT INTO `test110` VALUES (null,'TMP01','user11','会员中台','2019-08-15 00:00:02','11','22');
set i=i+1;
end while;
end $$
DELIMITER ;测试如下，速度很快:
mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)
mysql> call test_insert();
Query OK, 1 row affected (44.74 sec)
mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)
mysql> select count(1) from test110;
+----------+
| count(1) |
+----------+
| 3000000 |
+----------+
1 row in set (0.44 sec)MySQL配置如下my.cnf
[client]
default-character-set = utf8mb4
port = 3306
socket = /data/mysql/mysql.sock
[mysql]
default-character-set = utf8mb4
prompt="\u@oatestdb \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
secure_file_priv = ''
#validate-password = off
slow_query_log = On
log_output = FILE
slow-query-log-file=/data/mysql/slow.log
long_query_time = 30
log_queries_not_using_indexes = ON
lower_case_table_names=1
auto-increment-increment = 1
auto-increment-offset = 1
log_warnings=1
log_error_verbosity=1
default-storage-engine=INNODB
group_concat_max_len = 18446744073709551615
# generic configuration options
port = 3306
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/mysqld.pid
basedir = /usr/local/mysql
datadir = /data/mysql/db
language = /usr/local/mysql/share/english
user = mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-external-locking
skip-name-resolve
skip-character-set-client-handshake
character-set-server = utf8mb4
explicit_defaults_for_timestamp = true
init_connect='SET NAMES utf8mb4'
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
explicit_defaults_for_timestamp
#replication
expire_logs_days = 30
sync-binlog = 1000
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/mysql/binlog/binlog
binlog_format=ROW
relay_log_recovery = 1
skip_slave_start = 0
slave-skip-errors = all
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#group replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1:24901"
loose-group_replication_group_seeds= "db1:24901,db2:24902,db3:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 5000
max_connect_errors = 100000
max_prepared_stmt_count=1048576
table_open_cache = 10240
table_definition_cache = 10240
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 7680
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 900
wait_timeout = 900
tmp_table_size = 256M
max_heap_table_size = 256M
# innodb
innodb_buffer_pool_size = 12G
innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC
innodb_io_capacity = 50000
innodb_io_capacity_max = 80000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
[mysqld_safe]
open-files-limit = 8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid
[mysqldump]
quick
max_allowed_packet = 32M
Tag标签:「存储过程 mysql」更新时间:「2021-11-03 19:57:38」阅读次数:「931」