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

关于插入：

宏观上：建二个表，其中一个表不建主键，不键索引。只记录，到了晚上，在把这个表的记录导入 有主键有索引的表里。方法的目的，二表，一表为插入最优化，另一表为查询最优化。

微观上：以下是涉及到插入表格的查询的5种改进方法：

一、使用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 |

三、可以对myisam表并行插入Concurrent_insert系统变量可以被设置用于修改concurrent-insert处理。该变量默认设置为1。如果concurrent_insert被设置为0，并行插入就被禁用。如果该变量被设置为2，在表的末端可以并行插入，即便该表的某些行已经被删除。

四、使用插入延迟
    如果你的客户不能或无需等待插入完成的时候，这招很有用。当你使用MySQL存储，并定期运行需要很长时间才能完成的SELECT和UPDATE语句的时候，你会发现这种情况很常见。当客户使用插入延迟，服务器立刻返回，如果表没有被其他线程调用，则行会列队等待被插入。使用插入延迟的另一个好处就是从多个客户插入的情况会被绑定并记录在同一个block中。这将比处理多个独立的插入要快得多。

 

 

五、插入之前将表锁定(只针对非事务处理型的表)

    这将提高数据库 性能，因为索引缓冲区只是在所有的插入语句完成后才对磁盘 进行一次刷新。通常情况下，有多少个插入语句就会有多少次索引缓冲区刷新。如果你可以用一个插入语句实现所有行的插入，则无需使用显式锁定语句。

要想更快地对事务型表插入，你应该使用START TRANSACTION和COMMIT语句，而不是LOCK TABLES语句。

关于查询：一些网上找到的思路，把数字型的分出来，在吧字符串类型的分离出去，再按时间分割，比如1个月1个数据分区，超过1年的移到老的数据表
统计时，先把统计类数据计算好，放入单独的统计表中，再来新数据，再统计表上加上新数据