在SQLite中插入10亿条Python VS Rust

在实际生活中，市场有这样的案例：写脚本来进行数据处理，比如说给数据库导入导出数据，这种任务一般来说最方便的方法是用python脚本，但是如果数据量比较大时候(比如上亿条)时候Python就会超级慢，看到无法忍受。在这种案例时候该怎么做呢，有一个外国老哥分享了自己的实践经历，并且对比了Python和Rust语言给SQLite插入十一条数据的情况，最后用Rust实现了在一分钟来完成任务。我们在此分享一下该实践过程，希望能对大家有所启迪，大家也可以尝试自己最拿手方法来实现该例子，并对比一下具体性能。

概述

案例中的任务是SQLite数据库插入10亿条的数据。表(user)数据结构和约束如下：

1. create table IF NOT EXISTS user 
2. ( 
3. id INTEGER not null primary key, 
4. area CHAR(6), 
5. age INTEGER not null, 
6. active INTEGER not null 
7. ); 

随机生成数据。其中are列为六位数的区号(任何六位数字)。age将是5、10 或15中的一个数字。Active为0或1。

• 实验环境硬件配置为：MacBook Pro，2019(2.4 GHz 四核i5，8GB内存，256GB SSD硬盘，Big Sur 11.1)。
• 任务前提：任务无需保持程序稳健性，如果进程崩溃并且所有数据都丢失了也没关系。可以再次运行脚本。
• 需要充分利用我的机器资源：100% CPU、8GB 内存和千兆字节的SSD空间。

无需使用真正的随机方法，stdlib伪随机方法即可。

Python

首先是原始版本的Python方法。Python标准库提供了一个SQLite模块，首先使用它编写了第一个版本。代码如下：

1. import sqlite3 
2. from commons import get_random_age, get_random_active, get_random_bool, get_random_area_code, create_table 
3. DB_NAME = "naive.db" 
4. def faker(con: sqlite3.Connection, count=100_000): 
5. for _ in range(count): 
6. age = get_random_age() 
7. active = get_random_active() 
8. # switch for area code 
9. if get_random_bool(): 
10. # random 6 digit number 
11. area = get_random_area_code() 
12. con.execute('INSERT INTO user VALUES (NULL,?,?,?)', (area, age, active)) 
13. else: 
14. con.execute('INSERT INTO user VALUES (NULL,NULL,?,?)', (age, active)) 
15. con.commit() 
16. def main(): 
17. con = sqlite3.connect(DB_NAME, isolation_level=None) 
18. con.execute('PRAGMA journal_mode = WAL;') 
19. create_table(con) 
20. faker(con, count=10_000_000) 
21. if __name__ == '__main__': 
22. main() 

在该脚本中，通for循环中一一插入1000万条数据。执行花了将近15分钟。基于此进行优化迭代，提高性能。

SQLite中，每次插入都是原子性的并且为一个事务。每个事务都需要保证写入磁盘(涉及IO操作)，因此可能会很慢。为了优化，可以尝试通过不同大小的批量插入，对比发现，100000是最佳选择。通过这个简单的更改，运行时间减少到了10分钟，优化了3分之一，但是仍然非常耗时。优化后，批量插入版本源码：

SQLite库优化

除了在代码层优化外，如果对于单纯的数据写入，对数据库本身搞的优化也是非常重要的。对于SQLite优化，可以做如下配置：

1. PRAGMA journal_mode = OFF; 
2. PRAGMA synchronous = 0; 
3. PRAGMA cache_size = 1000000; 
4. PRAGMA locking_mode = EXCLUSIVE; 
5. PRAGMA temp_store = MEMORY; 

具体解释：

首先，journal_mode设置为OFF，将会关闭回滚日志，禁用 SQLite 的原子提交和回滚功能，这样在事务失败情况下，无法恢复，基于例子实例稳健性要求可以设置，但是严禁在生产环境中使用。

其次，关闭synchronous，SQLite可以不再校验磁盘写入的数据可靠性。写入SQLite可能并不意味着它已刷新到磁盘。同样，严禁在生产环境中启用。

cache_size用户指定SQLite允许在内存中保留多少内存页。不要在生产中分配太高的的数值。

使用在EXCLUSIVE锁定模式，SQLite连接持有的锁永远不会被释放。

设置temp_store到MEMOR将使其表现得像一个内存数据库。

优化性能

对上面的两个脚本，添加 SQLite优化参数，然后重新运行：

1. def main():     
2. con = sqlite3.connect(DB_NAME, isolation_level=None)     
3. con.execute('PRAGMA journal_mode = OFF;')     
4. con.execute('PRAGMA synchronous = 0;')     
5. con.execute('PRAGMA cache_size = 1000000;') # give it a GB     
6. con.execute('PRAGMA locking_mode = EXCLUSIVE;')     
7. con.execute('PRAGMA temp_store = MEMORY;')     
8. create_table(con)     

faker(con, count=100_000_000)

优化后版本，原始版本，插入1亿行数据，大概花了10分钟;对比批量插入版本大概花了8.5分钟。

pypy版本

对比CPython PyPy在数据处理中可以提高性能，据说可以提高4倍以上的性能。本实验中也尝试编译PyPy解释器，运行脚本(代码无需修改)。

使用pypy解释器，批处理版本，插入1亿行数据只需2.5分钟。性能大概是Cpython的3.5倍，可见传说的4倍性能提高确实是真的，诚不我欺也!。同时，为了测试在纯循环插入中消耗的时间，在脚本中删除SQL指令并运行：

以上脚本在CPython中耗时5.5分钟 。PyPy执行耗时1.5分钟(同样提高了3.5倍)。

Rust

在完成Python各种优化折腾。又尝试了Rust版本的插入，对比也有个原始版本和批量插入版本。原始版本，也是每行插入：

1. use rusqlite::{params, Connection}; 
2. mod common; 
3. fn faker(mut conn: Connection, count: i64) { 
4. let tx = conn.transaction().unwrap(); 
5. for _ in 0..count { 
6. let with_area = common::get_random_bool(); 
7. let age = common::get_random_age(); 
8. let is_active = common::get_random_active(); 
9. if with_area { 
10. let area_code = common::get_random_area_code(); 
11. tx.execute( 
12. "INSERT INTO user VALUES (NULL, ?, ?, ?)", 
13. params![area_code, age, is_active], 
14. ) 
15. .unwrap(); 
16. } else { 
17. tx.execute( 
18. "INSERT INTO user VALUES (NULL, NULL, ?, ?)", 
19. params![age, is_active], 
20. ) 
21. .unwrap(); 
22. } 
23. } 
24. tx.commit().unwrap(); 
25. } 
26. fn main() { 
27. let conn = Connection::open("basic.db").unwrap(); 
28. conn.execute_batch( 
29. "PRAGMA journal_mode = OFF; 
30. PRAGMA synchronous = 0; 
31. PRAGMA cache_size = 1000000; 
32. PRAGMA locking_mode = EXCLUSIVE; 
33. PRAGMA temp_store = MEMORY;", 
34. ) 
35. .expect("PRAGMA"); 
36. conn.execute( 
37. "CREATE TABLE IF NOT EXISTS user ( 
38. id INTEGER not null primary key, 
39. area CHAR(6), 
40. age INTEGER not null, 
41. active INTEGER not null)", 
42. [], 
43. ) 
44. .unwrap(); 
45. faker(conn, 100_000_000) 
46. } 

该版执行，大概用时3分钟。然后我做了进一步的实验：

将rusqlite，换成sqlx异步运行。

1. use std::str::FromStr;     
2.  
3. use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqliteSynchronous};     
4. use sqlx::{ConnectOptions, Connection, Executor, SqliteConnection, Statement};     
5.  
6. mod common;     
7.  
8. async fn faker(mut conn: SqliteConnection, count: i64) -> Result<(), sqlx::Error> {     
9. let mut tx = conn.begin().await?;     
10. let stmt_with_area = tx     
11. .prepare("INSERT INTO user VALUES (NULL, ?, ?, ?)")     
12. .await?;     
13. let stmt = tx     
14. .prepare("INSERT INTO user VALUES (NULL, NULL, ?, ?)")     
15. .await?;     
16. for _ in 0..count {     
17. let with_area = common::get_random_bool();     
18. let age = common::get_random_age();     
19. let is_active = common::get_random_active();     
20. if with_area {     
21. let area_code = common::get_random_area_code();     
22. stmt_with_area     
23. .query()     
24. .bind(area_code)     
25. .bind(age)     
26. .bind(is_active)     
27. .execute(&mut tx)     
28. .await?;     
29. } else {     
30. stmt.query()     
31. .bind(age)     
32. .bind(is_active)     
33. .execute(&mut tx)     
34. .await?;     
35. }     
36. }     
37. tx.commit().await?;     
38. Ok(())     
39. }     
40.  
41. #[tokio::main]     
42. async fn main() -> Result<(), sqlx::Error> {     
43. let mut conn = SqliteConnectOptions::from_str("basic_async.db")     
44. .unwrap()     
45. .create_if_missing(true)     
46. .journal_mode(SqliteJournalMode::Off)     
47. .synchronous(SqliteSynchronous::Off)     
48. .connect()     
49. .await?;     
50. conn.execute("PRAGMA cache_size = 1000000;").await?;     
51. conn.execute("PRAGMA locking_mode = EXCLUSIVE;").await?;     
52. conn.execute("PRAGMA temp_store = MEMORY;").await?;     
53. conn.execute(     
54. "CREATE TABLE IF NOT EXISTS user (     
55. id INTEGER not null primary key,     
56. area CHAR(6),     
57. age INTEGER not null,     
58. active INTEGER not null);",     
59. )     
60. .await?;     
61. faker(conn, 100_000_000).await?;     
62. Ok(())     
63. } 

这个版本花了大约14分钟。性能反而下降下降了。比Python版本还要差(原因值得深析)。

对执行的原始SQL语句，切换到准备好的语句并在循环中插入行，但重用了准备好的语句。该版本只用了大约一分钟。

使用准备好的语句并将它们插入到50行的批次中，插入10亿条，耗时34.3 秒。

1. use rusqlite::{Connection, ToSql, Transaction}; 
2. mod common; 
3. fn faker_wrapper(mut conn: Connection, count: i64) { 
4. let tx = conn.transaction().unwrap(); 
5. faker(&tx, count); 
6. tx.commit().unwrap(); 
7. } 
8. fn faker(tx: &Transaction, count: i64) { 
9. // that is, we will batch 50 inserts of rows at once 
10. let min_batch_size: i64 = 50; 
11. if count < min_batch_size { 
12. panic!("count cant be less than min batch size"); 
13. } 
14. // jeez, refactor this! 
15. let mut with_area_params = " (NULL, ?, ?, ?),".repeat(min_batch_size as usize); 
16. with_area_params.pop(); 
17. let with_area_paramswith_area_params = with_area_params.as_str(); 
18. let mut without_area_params = " (NULL, NULL, ?, ?),".repeat(min_batch_size as usize); 
19. without_area_params.pop(); 
20. let without_area_paramswithout_area_params = without_area_params.as_str(); 
21. let st1 = format!("INSERT INTO user VALUES {}", with_area_params); 
22. let st2 = format!("INSERT INTO user VALUES {}", without_area_params); 
23. let mut stmt_with_area = tx.prepare_cached(st1.as_str()).unwrap(); 
24. let mut stmt = tx.prepare_cached(st2.as_str()).unwrap(); 
25. for _ in 0..(count / min_batch_size) { 
26. let with_area = common::get_random_bool(); 
27. let age = common::get_random_age(); 
28. let is_active = common::get_random_active(); 
29. let mut param_values: Vec<_> = Vec::new(); 
30. if with_area { 
31. // lets prepare the batch 
32. let mut vector = Vec::<(String, i8, i8)>::new(); 
33. for _ in 0..min_batch_size { 
34. let area_code = common::get_random_area_code(); 
35. vector.push((area_code, age, is_active)); 
36. } 
37. for batch in vector.iter() { 
38. param_values.push(&batch.0 as &dyn ToSql); 
39. param_values.push(&batch.1 as &dyn ToSql); 
40. param_values.push(&batch.2 as &dyn ToSql); 
41. } 
42. stmt_with_area.execute(&*param_values).unwrap(); 
43. } else { 
44. // lets prepare the batch 
45. let mut vector = Vec::<(i8, i8)>::new(); 
46. for _ in 0..min_batch_size { 
47. vector.push((age, is_active)); 
48. } 
49. for batch in vector.iter() { 
50. param_values.push(&batch.0 as &dyn ToSql); 
51. param_values.push(&batch.1 as &dyn ToSql); 
52. } 
53. stmt.execute(&*param_values).unwrap(); 
54. } 
55. } 
56. } 
57. fn main() { 
58. let conn = Connection::open("basic_batched.db").unwrap(); 
59. conn.execute_batch( 
60. "PRAGMA journal_mode = OFF; 
61. PRAGMA synchronous = 0; 
62. PRAGMA cache_size = 1000000; 
63. PRAGMA locking_mode = EXCLUSIVE; 
64. PRAGMA temp_store = MEMORY;", 
65. ) 
66. .expect("PRAGMA"); 
67. conn.execute( 
68. "CREATE TABLE IF NOT EXISTS user ( 
69. id INTEGER not null primary key, 
70. area CHAR(6), 
71. age INTEGER not null, 
72. active INTEGER not null)", 
73. [], 
74. ) 
75. .unwrap(); 
76. faker_wrapper(conn, 100_000_000) 
77. } 
78. 创建了一个线程版本，其中有一个从通道接收数据的写入线程和四个将数据推送到管道其他线程。 
79. use rusqlite::{Connection, ToSql}; 
80. use std::sync::mpsc; 
81. use std::sync::mpsc::{Receiver, Sender}; 
82. use std::thread; 
83. mod common; 
84. static MIN_BATCH_SIZE: i64 = 50; 
85. enum ParamValues { 
86. WithArea(Vec<(String, i8, i8)>), 
87. WithoutArea(Vec<(i8, i8)>), 
88. } 
89. fn consumer(rx: Receiver<ParamValues>) { 
90. let mut conn = Connection::open("threaded_batched.db").unwrap(); 
91. conn.execute_batch( 
92. "PRAGMA journal_mode = OFF; 
93. PRAGMA synchronous = 0; 
94. PRAGMA cache_size = 1000000; 
95. PRAGMA locking_mode = EXCLUSIVE; 
96. PRAGMA temp_store = MEMORY;", 
97. ) 
98. .expect("PRAGMA"); 
99. conn.execute( 
100. "CREATE TABLE IF NOT EXISTS user ( 
101. id INTEGER not null primary key, 
102. area CHAR(6), 
103. age INTEGER not null, 
104. active INTEGER not null)", 
105. [], 
106. ) 
107. .unwrap(); 
108. let tx = conn.transaction().unwrap(); 
109. { 
110. // jeez, refactor this! 
111. let mut with_area_params = " (NULL, ?, ?, ?),".repeat(MIN_BATCH_SIZE as usize); 
112. with_area_params.pop(); 
113. let with_area_paramswith_area_params = with_area_params.as_str(); 
114. let mut without_area_params = " (NULL, NULL, ?, ?),".repeat(MIN_BATCH_SIZE as usize); 
115. without_area_params.pop(); 
116. let without_area_paramswithout_area_params = without_area_params.as_str(); 
117. let st1 = format!("INSERT INTO user VALUES {}", with_area_params); 
118. let st2 = format!("INSERT INTO user VALUES {}", without_area_params); 
119. let mut stmt_with_area = tx.prepare_cached(st1.as_str()).unwrap(); 
120. let mut stmt_without_area = tx.prepare_cached(st2.as_str()).unwrap(); 
121. for param_values in rx { 
122. let mut row_values: Vec<&dyn ToSql> = Vec::new(); 
123. match param_values { 
124. ParamValues::WithArea(values) => { 
125. for batch in values.iter() { 
126. row_values.push(&batch.0 as &dyn ToSql); 
127. row_values.push(&batch.1 as &dyn ToSql); 
128. row_values.push(&batch.2 as &dyn ToSql); 
129. } 
130. stmt_with_area.execute(&*row_values).unwrap(); 
131. } 
132. ParamValues::WithoutArea(values) => { 
133. for batch in values.iter() { 
134. row_values.push(&batch.0 as &dyn ToSql); 
135. row_values.push(&batch.1 as &dyn ToSql); 
136. } 
137. stmt_without_area.execute(&*row_values).unwrap(); 
138. } 
139. } 
140. } 
141. } 
142. tx.commit().unwrap(); 
143. } 
144. fn producer(tx: Sender<ParamValues>, count: i64) { 
145. if count < MIN_BATCH_SIZE { 
146. panic!("count cant be less than min batch size"); 
147. } 
148. for _ in 0..(count / MIN_BATCH_SIZE) { 
149. let with_area = common::get_random_bool(); 
150. let age = common::get_random_age(); 
151. let is_active = common::get_random_active(); 
152. let mut param_values: Vec<_> = Vec::new(); 
153. if with_area { 
154. // lets prepare the batch 
155. let mut vector = Vec::<(String, i8, i8)>::new(); 
156. for _ in 0..MIN_BATCH_SIZE { 
157. let area_code = common::get_random_area_code(); 
158. vector.push((area_code, age, is_active)); 
159. } 
160. for batch in vector.iter() { 
161. param_values.push(&batch.0 as &dyn ToSql); 
162. param_values.push(&batch.1 as &dyn ToSql); 
163. param_values.push(&batch.2 as &dyn ToSql); 
164. } 
165. // send the values 
166. tx.send(ParamValues::WithArea(vector)).unwrap(); 
167. } else { 
168. // lets prepare the batch 
169. let mut vector = Vec::<(i8, i8)>::new(); 
170. for _ in 0..MIN_BATCH_SIZE { 
171. vector.push((age, is_active)); 
172. } 
173. for batch in vector.iter() { 
174. param_values.push(&batch.0 as &dyn ToSql); 
175. param_values.push(&batch.1 as &dyn ToSql); 
176. } 
177. // send the values 
178. tx.send(ParamValues::WithoutArea(vector)).unwrap(); 
179. } 
180. } 
181. } 
182. fn main() { 
183. // setup the DB and tables 
184. let (tx, rx): (Sender<ParamValues>, Receiver<ParamValues>) = mpsc::channel(); 
185. // lets launch the consumer 
186. let consumer_handle = thread::spawn(|| consumer(rx)); 
187. let cpu_count = num_cpus::get(); 
188. let total_rows = 100_000_000; 
189. let each_producer_count = (total_rows / cpu_count) as i64; 
190. let mut handles = Vec::with_capacity(cpu_count); 
191. for _ in 0..cpu_count { 
192. let thread_tx = tx.clone(); 
193. handles.push(thread::spawn(move || { 
194. producer(thread_tx, each_producer_count.clone()) 
195. })) 
196. } 
197. for t in handles { 
198. t.join().unwrap(); 
199. } 
200. drop(tx); 
201. // wait till consumer is exited 
202. consumer_handle.join().unwrap(); 
203. } 

这是性能最好的版本，耗时约32.37秒。

基准测试对比：

总结

通过案例不同任务实验，总体上可以得到：

• 通过SQLite PRAGMA语句优化设置可以提高插入性能。
• 使用准备好的语句可以提高性能
• 进行批量插入可以提高性能。
• PyPy 实际上比CPython快4倍
• 线程/异步不一定能提高性能。

原文地址：https://mp.weixin.qq.com/s?__biz=MzU0MTY5MzEwMA==&mid=2247488055&idx=1&sn=2848892932ca18c1bf6835870a795c15&chksm=fb2757f4cc50dee233e8b51e6d4fddabec2e78ae7fc63da1b70181329e3f730ebaa67cf01e96&mpshare=1&s