MariaDB 与 MySQL 的查询与插入基础性能对比 起因为在群里聊天部署halo时用的什么数据库

个人笔记本电脑 CPU：i5 1135G7 内存：16GB

使用Python Fake生成100000条数据

数据库版本为2024.9.12日官方最新稳定版

MariaDB 版本 11.5.2

MySQL 版本 8.0.39

数据

#!/usr/bin/env/ python3

from faker import Faker
import pandas as pd


salary = []
fake = Faker(locale='zh_CN')
for i in range(100000):
    item = {}
    item['name'] = fake.name()  # 名字
    item['address'] = fake.address()  # 地址
    item['ssn'] = fake.ssn()  # 身份证
    item['company'] = fake.company()  # 公司名（长）
    item['company_suffix'] = fake.company_suffix()  # 公司性质
    item['credit_card_number'] = fake.credit_card_number()  # 信用卡
    item['credit_card_provider'] = fake.credit_card_provider()  # 信用卡类型
    item['credit_card_security_code'] = fake.credit_card_security_code()  # 信用卡安全码
    item['credit_card_expire'] = fake.credit_card_expire()  # 随机信用卡到期日
    item['job'] = fake.job()  # 职位
    item['phone_number'] = fake.phone_number()  # 手机号码
    salary.append(item)

df = pd.DataFrame(salary)
df.to_csv('table.csv', index=False, header=True, encoding='utf_8_sig')

表结构

DROP TABLE IF EXISTS `userinfos`;
CREATE TABLE `userinfos`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `ssn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `company` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `company_suffix` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `credit_card_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `credit_card_provider` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `credit_card_security_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `credit_card_expire` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `job` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_uca1400_ai_ci ROW_FORMAT = Dynamic;

将数据生成的csv文件导入到数据表中即可

查询对比

分别执行以下查询

全表查询

SELECT * FROM test.userinfos;

MySQL

三次查询时间为：0.19s 0.23s 0.21s 平均时长为：0.21s

MariaDB

三次查询时间为：0.074s 0.071s 0.074s 平均时长为：0.073s

Like查询对比

SELECT * FROM TEST.userinfos WHERE userinfos.`name` LIKE "%丽%";

MySQL

0.1s

MariaDB

0.042s

插入对比

生成1000条数据

#!/usr/bin/env/ python3

from faker import Faker

fake = Faker(locale='zh_CN')
for i in range(1000):
    n = 100000 + i + 1
    sql = '(' + str(n) + ', \'' + fake.name() + '\', \'' + fake.address() + '\', \'' + fake.ssn() + '\', \'' + fake.company() + '\', \'' +  fake.company_suffix() + '\', \'' + fake.credit_card_number() + '\', \'' + fake.credit_card_provider() + '\', \'' + fake.credit_card_security_code() + '\', \'' + fake.credit_card_expire() + '\', \'' + fake.job() + '\', \'' + fake.phone_number()+'\'),'
    print(sql)
sql = 'INSERT INTO test.userinfos VALUES'

insert_1000.sql

MySQL

0.02s

MariaDB

0.016s

总结

不管是查询还是插入 似乎MariaDB都优于MySQL 在服务器中使用Docker部署这两个数据库时MariaDB内存占用也优于MySQL