我正在尝试优化InnoDB表上MariaDB（10.0.31）上的大型插入查询的速度。
下面是表的结构（1.31亿行）：
Field__ Type___ Null Key Default Extra
ID_num_ bigint(45) NO PRI NULL
Content varchar(250)YES NULL
User_ID bigint(24) NO MUL NULL
Location varchar(70) YES NULL
Date_creat datetime NO MUL NULL
Retweet_ct int(7) NO NULL
isRetweet tinyint(1) NO NULL
hasReetwet tinyint(1) NO NULL
Original bigint(45) YES NULL
Url____ varchar(150)YES NULL
Favorite_c int(7) NO NULL
Selected int(11) NO 0
Sentiment int(11) NO 0
以下是
CREATE TABLE的输出：CREATE TABLE `Twit` (
`ID_num` bigint(45) NOT NULL,
`Content` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`User_ID` bigint(24) NOT NULL,
`Location` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Date_create` datetime NOT NULL,
`Retweet_count` int(7) NOT NULL,
`isRetweet` tinyint(1) NOT NULL,
`hasReetweet` tinyint(1) NOT NULL,
`Original` bigint(45) DEFAULT NULL,
`Url` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`Favorite_count` int(7) NOT NULL,
`Selected` int(11) NOT NULL DEFAULT '0',
`Sentiment` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID_num`),
KEY `User_ID` (`User_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
以下是索引的结构：
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
Twit 0 PRIMARY 1 ID_num A 124139401 NULL NULL BTREE
Twit 1 User_ID 1 User_ID A 535083 NULL NULL BTREE
这里是
show engine innodb status：BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8942256128; in additional pool allocated 0
Total memory allocated by read views 184
Internal hash tables (constant factor + variable factor)
Adaptive hash index 141954688 (141606424 + 348264)
Page hash 4426024 (buffer pool 0 only)
Dictionary cache 35656039 (35403184 + 252855)
File system 845872 (812272 + 33600)
Lock system 21251648 (21250568 + 1080)
Recovery system 0 (0 + 0)
Dictionary memory allocated 252855
Buffer pool size 524286
Buffer pool size, bytes 8589901824
Free buffers 448720
Database pages 75545
Old database pages 27926
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 74639, created 906, written 39133
0.12 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 75545, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
我使用以下Python代码从第三方源下载数据，然后用它填充表：
add_twit = (" INSERT INTO Table (ID_num, Content,....) VALUES (%s, %s, ....)")
testtime=0
t0 = time.time()
data_twit = []
#### Data Retrieving ####
for page in limit_handled(...):
for status in page:
data_twit.append(processed_tweet)
####
##### MySQL Insert
tt0 = time.time()
cursorSQL.executemany(add_twit, data_twit)
testtime += time.time() - tt0
####
cnx.commit()
print('Total_TIME ' + str(time.time()-t0))
print('Sqlexecute_TIME ' + str(testtime))
代码的作用是：
它从第三方提供程序获取twits，共16页，每页200 twits（status），因此每个iteratin（user）总共要向表中添加3200行。我尝试在每个tweet中插入一个查询（使用
cursorSQL.execute(add_twit, data_twit)，在一个列表中插入16个查询200条tweet，但是几秒钟内最快的是使用优化的cursorSQL.executemany函数查询3200条tweet。对于3200条tweet，下载它们大约需要10秒，将它们写入数据库大约需要75秒，考虑到一条tweet（row）当前在表中需要0.2ko，因此3200条tweet仅需要640 Ko，这似乎是一个很大的问题。不需要75秒。。。
使用
iotop监视磁盘使用情况时会发生什么情况：在检索部分代码的数据期间（第一次迭代之后）：
读取=0.00 B/s
写入=6.50 M/s
在一次大的插入之后，磁盘实际上会以6Mbs/s的速度持续写入几分钟
在SQL插入部分代码期间：
读数=1.5 M/s
写入=300 K/s
看起来像是磁盘读取（我想是为了索引？）使写作速度下降。
我试过的：
尝试分割插入查询（我尝试了16*200行和3200*1行，而不是1*3200行，没有改变任何内容，1*3200是最快的）
优化表（获得15%的速度）
删除不必要的索引
我的问题：
为什么当我提交插入查询而不是写入时磁盘开始读取？有没有办法防止这种情况？
删除所有索引是否有助于加快插入速度？
我是否需要删除主键（不是列，只是它的唯一索引），即使这听起来是个坏主意，并且（MySQL slows down after INSERT）建议不删除？
还有其他建议吗？
另外，为什么磁盘在大插入数分钟后仍以6.00 Mb/s的速度写入？
最佳答案
桌上大约60GB？
用户ID索引中大约5GB？（见SHOW TABLE STATUS LIKE 'Twit中的索引长度）
每个INSERT有大约3200个新行？如果这是错误的，那么这就是主要问题。
您正在计算ID_num而不是使用AUTO_INCREMENT？
ID_num是单调递增的？（或至少近似）如果这是错误的，那么这就是主要的问题。
用户ID相当随机。
分析和结论：
数据被“附加到”；这对缓存（缓冲池，8GB）没有太大影响。User_ID索引正在随机更新；这会将大部分索引保存在缓存中，或者可能会溢出。如果您刚刚开始溢出，那么性能就会下降，并且随着缓存未命中率的增加，性能会越来越差。
“I/O在写入后继续”--这是正常的。有关血淋淋的详细信息，请查看“InnoDB Change buffering”。摘要：INDEX(User_ID)的更新被延迟，但必须最终发生。
部分解决方案：
更多的内存。
将innodb_buffer_pool_size增加到RAM的70%；确保不会导致交换。
你的用户肯定不会超过40亿吧？将User_ID从INT UNSIGNED（8字节）缩小到BIGINT（4字节）。这将使二级指数缩水约25%。DROP INDEX(User_ID)——你真的需要它吗？
你在别的地方使用ID_num吗？如果不是，请解释它的存在。
适当时从NULL更改为NOT NULL。（这无助于提高速度，但却是一种清理。）
使用AUTO_INCREMENT而不是手动卷id。（可能没有帮助。）
标杆管理：
我不会使用任何“原始”I/O指标——它们被InnoDB和更改缓冲区的“阻塞”搞混了。
等待“稳定状态”。也就是说，要避免小桌、冷机、爆料等，一张3200所用时间的图表就会因为这样的事情而起起伏伏。但最终它将达到“稳定状态”。但是，根据我对二级索引的分析，这可能会下降到3200行，耗时32秒（如果使用旋转磁盘）。
在75秒内3200是没有意义的。我想我真的需要看看生成的SQL。