面试题：MySQL批量插入优化

使用LOAD DATA INFILE语句：
- 优势：这是MySQL提供的专门用于快速导入数据的语句，性能比逐行插入高很多。
- 操作步骤：
  - 确保MySQL服务器有权限访问该CSV文件。如果文件在服务器本机，一般直接可以访问；如果在客户端，可能需要先上传到服务器合适目录。
  - 确保表结构和CSV文件数据格式匹配。例如，数据类型、字段顺序等。
  - 执行以下SQL语句：

LOAD DATA INFILE 'path_to_your_file.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

解释：
- path_to_your_file.csv是CSV文件的路径。
- your_table_name是要插入数据的表名。
- FIELDS TERMINATED BY ','指定字段之间的分隔符为逗号。
- ENCLOSED BY '"'指定字段值被双引号包围（如果有这种情况）。
- LINES TERMINATED BY '\n'指定行结束符为换行符。
- IGNORE 1 ROWS表示忽略CSV文件的第一行（通常是表头）。

事务处理：
- 优势：将多个插入操作放在一个事务中，可以减少磁盘I/O次数，因为事务提交时才会真正写入磁盘。
- 操作步骤：
  - 开启事务：START TRANSACTION;
  - 编写批量插入语句，例如使用INSERT INTO... VALUES (...),(...),...的形式，将多条记录一次性插入。
  - 提交事务：COMMIT;
- 示例：

START TRANSACTION;
INSERT INTO your_table_name (column1, column2, column3)
VALUES ('value1_1', 'value1_2', 'value1_3'),
       ('value2_1', 'value2_2', 'value2_3'),
       -- 更多值对
       ('valueN_1', 'valueN_2', 'valueN_3');
COMMIT;

禁用索引：
- 优势：在插入数据时，索引会增加额外的开销。先禁用索引，插入完成后再重建索引，可以提高插入性能。
- 操作步骤：
  - 禁用索引：对于普通索引，可以使用ALTER TABLE your_table_name DISABLE KEYS; 对于唯一索引和主键索引，MySQL不支持直接禁用，但是可以先删除再重建。例如删除主键：ALTER TABLE your_table_name DROP PRIMARY KEY;，删除唯一索引：ALTER TABLE your_table_name DROP INDEX index_name;
  - 执行批量插入操作（可以结合事务或LOAD DATA INFILE）。
  - 重建索引：对于普通索引：ALTER TABLE your_table_name ENABLE KEYS; 对于主键：ALTER TABLE your_table_name ADD PRIMARY KEY (primary_key_column); 对于唯一索引：ALTER TABLE your_table_name ADD UNIQUE (unique_column);
调整MySQL配置参数：
- innodb_buffer_pool_size：增大该参数值，可以让更多的数据和索引缓存到内存中，减少磁盘I/O。修改my.cnf（或my.ini）文件，在[mysqld]部分添加或修改：innodb_buffer_pool_size = XM（X根据服务器内存情况设置，例如2048M），然后重启MySQL服务。
- innodb_flush_log_at_trx_commit：设置为2可以减少I/O操作，但可能在系统崩溃时丢失最后一秒的数据。在my.cnf（或my.ini）文件[mysqld]部分添加或修改：innodb_flush_log_at_trx_commit = 2 ，然后重启MySQL服务。

星途面试题库

面试题：MySQL批量插入优化

知识考点

面试题答案