一. 前言
最近在使用 Python 实现桌面端应用,其中涉及到数据库 ,看到 Python 集成了 SQLite ,那么对于一个基础的桌面端本地应用 , 它是否可以满足我们的需求呢?
二. SQLite 概览
2.1 支持的能力
- 支持 SQL 标准 ,也就是 SELECT / INSERT 等常见的 SQL 标准语法都支持,包括 GroupBy / OrderBy 等
- 支持 视图(Views)/ 触发器 (Triggers) / 存储过程
- 支持 显示的事务控制 ,支持 ACID 能力
- 支持索引 , 包括 普通索引、唯一索引、部分索引以及复合索引
- 支持 JSON ,也就是 JSON 文本的解析、查询和更新
- 支持多读单写 ,也就是 并发 的能力
从这些支持的能力上面大概能看出来,日常使用的关系型数据库能力它都是有的
-- 创建数据表
CREATE TABLE peoples (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code INTEGER,
name TEXT NOT NULL,
age INTEGER,
grade TEXT,
create_date DATE
);
-- 添加索引 : 这里尝试创建了3种数据类型
CREATE INDEX idx_people_name ON peoples (name);
CREATE UNIQUE INDEX idx_people_code ON peoples (code);
CREATE INDEX idx_people_grade_age ON peoples (grade, age);
-- 插入数据 :
INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811001,'Test001', 20, 'A', '2024-09-01');
INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811002,'Test002', 22, 'B', '2024-09-07');
INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811003,'Test003', 20, 'C', '2024-09-05');
INSERT INTO peoples (code,name, age, grade, create_date)
VALUES (19216811004,'Test004', 21, 'C', '2024-09-09');
-- 条件查询
SELECT * FROM peoples WHERE age > 21;
SELECT * FROM peoples ORDER BY create_date;
SELECT age,count(*) FROM peoples group by age;
-- 更新数据
UPDATE peoples SET grade = 'D' WHERE name = 'Test004';
-- 支持 Explan 查询
EXPLAIN SELECT * FROM peoples WHERE name = 'Test002';
EXPLAIN QUERY PLAN SELECT * FROM peoples WHERE name = 'Test002';
2.2 简单看看 SQLite 的 Explain
下面专门来看一下执行计划 ,了解这个有助于我们后续看 SQLite 的性能 :
-- explain 有两种执行方式 :
-- 方式一 :查询执行明细 (也就是上图看到的)
EXPLAIN SELECT * FROM peoples WHERE name = 'Test002';
-- 方式二 : 通过 EXPLAIN QUERY 查询摘要信息
EXPLAIN QUERY PLAN SELECT * FROM peoples WHERE name = 'Test002';
-- 打印结果 : 可以明确看到使用的是什么类型的索引
id parent notused detail
3 0 0 SEARCH TABLE peoples USING INDEX idx_people_name (name=?)
三. 数据库性能
前置代码 : Python 版 SQLite 使用
import sqlite3
import random
from datetime import datetime
# 1. 连接到数据库(如果数据库文件不存在,会自动创建一个)
conn = sqlite3.connect('example.db')
# 2. 创建一个游标对象,用于执行 SQL 语句
cursor = conn.cursor()
# 4. 使用 for 循环逐条插入 100,000 条数据
current_timestamp_start = datetime.now()
print("开始 Timestamp:", current_timestamp_start)
for i in range(1, 1000000):
# 生成用户名,例如:User_1, User_2, ..., User_100000
name = f'User_{i}_{random.randint(1, 100)}'
age = i % 100 + 1 # 生成年龄,1 到 100 之间循环
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
# 每插入 1000 条记录后提交一次,避免过多数据累积在内存中
if i % 1000 == 0:
conn.commit()
current_timestamp_end = datetime.now()
print("结束 Timestamp:", current_timestamp_end)
print("耗时 Timestamp:", (current_timestamp_end-current_timestamp_start))
# 8. 关闭游标和连接
cursor.close()
conn.close()
3.1 如果有100万数据 ,会花多久?
这里插入100万数据 ,看看要多久:
// 执行结果 (100万数据差不多执行了 8 秒)
开始 Timestamp: 2024-09-19 22:34:49.516289
结束 Timestamp: 2024-09-19 22:34:57.204632
插入2000万数据 :
// 执行结果 :2000万数据大概 3 分钟 ,但是大小达到了 2G 左右
开始 Timestamp: 2024-09-22 13:40:22.255227
结束 Timestamp: 2024-09-22 13:43:40.879537
阶段总结 :
如果只是为了做一个桌面端的应用或者简单的应用 ,肯定是用不到这么大的数据量的
。
单以几万的数据量来说 ,SQLite 抗下完全没有问题。哪怕全表百万数据量 ,不论是占用大小还是效率,也都能满足一个桌面端应用的使用。
3.2 不同数据量的搜索性能
百万级数据 : 毫秒级
千万级数据 : ( 使用索引和不使用索引的差距)
- 100万的数据量下查询其实不消耗多少资源 ,这里直接在大数据量(2000万)下才考虑使用索引
- 查询效率不在一个量级 ,有了
索引
之后查询效率要快的多
- 相对的和大多数数据库一样,
加入索引后DB文件大了不少
(2000万数据2G
,加入索引后大概在2.5G
)
3.3 超大宽表的性能
import sqlite3
import random
from datetime import datetime
# 连接 SQLite 数据库
conn = sqlite3.connect('large_wide_table.db')
cursor = conn.cursor()
# 创建一个包含 50 列的宽表
columns = [f"col_{i} INTEGER" for i in range(50)]
create_table_sql = f"CREATE TABLE IF NOT EXISTS wide_table ({', '.join(columns)});"
cursor.execute(create_table_sql)
# 插入大量数据
def random_data():
return [random.randint(1, 100) for _ in range(50)]
insert_sql = f"INSERT INTO wide_table VALUES ({', '.join(['?' for _ in range(50)])});"
# 模拟插入 100万 行数据
print("开始 Timestamp:", datetime.now())
for _ in range(1000000):
cursor.execute(insert_sql, random_data())
print("结束 Timestamp:", datetime.now())
# 提交事务
conn.commit()
-- 执行结果
开始 Timestamp: 2024-09-22 14:11:21.831756
结束 Timestamp: 2024-09-22 14:11:35.137666
-- 文件大小 :103M
- 一共大概50列 的 100万数据的结构表 ,
总的占用大小在 100M左右
- 100万数据的插入时间
10秒出头
- 10059 条数据无索引查询的效率大概在
0.174s
总结
单纯从性能上说 ,完全够用了。 我预计的数据总量可能就 2-3万 ,完全可以实现我的桌面端应用的需求。
注意 : 此处没有考虑网络损耗 ,也没有考虑应用占用的本地资源损耗,这个在下一篇来了解。
最后的最后 ❤️❤️❤️👇👇👇
- 👈 欢迎关注 ,超200篇优质文章,未来持续高质量输出 🎉🎉
- 🔥🔥🔥 系列文章集合,高并发,源码应有尽有 👍👍
- 走过路过不要错过 ,知识无价还不收钱 ❗❗