Python :关系型数据库用 SQLite 妥妥足够了

2,848 阅读5分钟

一. 前言

最近在使用 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';

image.png

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

image.png

插入2000万数据 :

// 执行结果 :2000万数据大概 3 分钟 ,但是大小达到了 2G 左右
开始 Timestamp: 2024-09-22 13:40:22.255227
结束 Timestamp: 2024-09-22 13:43:40.879537

阶段总结 :

如果只是为了做一个桌面端的应用或者简单的应用 ,肯定是用不到这么大的数据量的

单以几万的数据量来说 ,SQLite 抗下完全没有问题。哪怕全表百万数据量 ,不论是占用大小还是效率,也都能满足一个桌面端应用的使用。

3.2 不同数据量的搜索性能

百万级数据 : 毫秒级

image.png

千万级数据 : ( 使用索引和不使用索引的差距)

image.png

  • 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

image.png

image.png

  • 一共大概50列 的 100万数据的结构表 ,总的占用大小在 100M左右
  • 100万数据的插入时间 10秒出头
  • 10059 条数据无索引查询的效率大概在 0.174s

总结

单纯从性能上说 ,完全够用了。 我预计的数据总量可能就 2-3万 ,完全可以实现我的桌面端应用的需求。

注意 : 此处没有考虑网络损耗 ,也没有考虑应用占用的本地资源损耗,这个在下一篇来了解。

最后的最后 ❤️❤️❤️👇👇👇