如何提高innodb insert into select的插入速度

prayer21

lujinke 发表于 2014-6-26 09:55
跟你说了解决思路你还不信，自行去MySQL文档找找有关fast index creation相关的内容吧

谢谢！

i6first

是不是可以select outfile，然后在load data infile呢？

lujinke

i6first 发表于 2014-6-30 20:31
是不是可以select outfile，然后在load data infile呢？

本质上慢的原因是因为表大之后，每次插入索引更新代价大（包括主键索引），INNODB对于非唯一的二级索引是可以使用insert buffer的，可以在一定程度上改进插入性能，但是对于unique的二级索引，就没办法了采用insert buffer了，此时只有在导入前删除该索引才能从根本上解决这个问题(当然主键没办法，后面会提到)，等数据导入过后，再重建这些索引，5.5版本之后MySQL有一种称为fast index creation的技术，可以快速重建索引(可以参考Online DDL相关的内容)，但是这种技术对主键行不通，因为重建主键索引，需要重建整张表

PS：disable keys和set unique checks=0对innodb表不起作用

digdeep126

8.5.4 Bulk Data Loading for InnoDB Tables
1)When importing data into InnoDB, turn off autocommit mode
2)If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session
3)If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning off the foreign key checks for the duration of the import session
4)Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:
5)When doing bulk inserts into tables with auto-increment columns, set innodb_autoinc_lock_mode to 2 instead of the default value 1

8.2.2.1 Speed of INSERT Statements
1)If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster
2)When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.

digdeep126

insert into select * from  和 create table select * from 执行过程中，如果是RR隔离级别，则会对原表加锁，最好通过使用：
select * from ... into outfile 和 load data infile ... 语句来改写。

prayer21

lujinke 发表于 2014-7-1 12:00
本质上慢的原因是因为表大之后，每次插入索引更新代价大（包括主键索引），INNODB对于非唯一的二级索引是 ...

谢谢

我最近看了下有关fast index creation的内容，在5.1中就可以用了，不过需要innodb plugin，我这的线上版本是5.1.57,但是没有装。。

打算最近测试下pt-online-schema-change不知道好不好用。。

prayer21

digdeep126 发表于 2014-7-2 11:48
insert into select * from  和 create table select * from 执行过程中，如果是RR隔离级别，则会对原表加锁 ...

谢谢，，

我这边线上的都是RC级别的，，
好几个网友提出用 load data，但我们这边几乎不用这个来导数据，，
有时PM 要数据的时候，我们会用select * from ... into outfile导出来给他们，一般情况下，这两个都不怎么用。。

myownstars

lujinke 发表于 2014-7-1 12:00
本质上慢的原因是因为表大之后，每次插入索引更新代价大（包括主键索引），INNODB对于非唯一的二级索引是 ...

set unique checks=0对innodb表不起作用??
应该是有作用的，innodb会临时将unique key当成normal key，并用到insert buffer

lujinke

myownstars 发表于 2014-7-6 14:52
set unique checks=0对innodb表不起作用??
应该是有作用的，innodb会临时将unique key当成normal key，并 ...

你试试就知道起不起作用了