Question

Mysql 千万级数据量插入和查询应该怎么优化

Answer 1

一、使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快20倍。
二、使用多个值表的 INSERT 语句 ，可以大大缩减客户端与数据库之间的连接、语法分析等消耗，使得效率比分开执行的单个 INSERT 语句快很多，相关的命令我们会在 SQL 优化详细介绍。如果多值的 INSERT是往一个非空的数据表里增加记录 ，也可以通过调整 bulk_insert_buffer_size 参数来提高数据插入的效率，这个参数设置的是 bulk insert 的缓存大小，默认是 8M 。
insert本身的多个value：
INSERT INTO table (field1,field2,field3) VALUES ('a',"b","c"), ('a',"b","c"),('a',"b","c");
在my.cnf中添加如下语句，将insert语句的长度设为最大。
Max_allowed_packet=1M
Net_buffer_length=2k
查看bulk_insert_buffer_size的值。
mysql> SHOW VARIABLES;
+———————————+—————————————-+
| Variable_name | Value |
+———————————+—————————————-+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |

Answer 2

一、使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快20倍。
二、使用多个值表的 INSERT 语句 ，可以大大缩减客户端与数据库之间的连接、语法分析等消耗，使得效率比分开执行的单个 INSERT 语句快很多，相关的命令我们会在 SQL 优化详细介绍。如果多值的 INSERT是往一个非空的数据表里增加记录 ，也可以通过调整 bulk_insert_buffer_size 参数来提高数据插入的效率，这个参数设置的是 bulk insert 的缓存大小，默认是 8M 。
insert本身的多个value：
INSERT INTO table (field1,field2,field3) VALUES ('a',"b","c"), ('a',"b","c"),('a',"b","c");
在my.cnf中添加如下语句，将insert语句的长度设为最大。
Max_allowed_packet=1M
Net_buffer_length=2k
查看bulk_insert_buffer_size的值。
mysql> SHOW VARIABLES;
+———————————+—————————————-+
| Variable_name | Value |
+———————————+—————————————-+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |