Ø 简介
本文记录了三大关系型数据库 SQL Server、MySQL、Oracle 的性能测试并比较,主要进行单线程、和多线程(模拟并发)的测试,测试内容如下:
1. 运行环境说明
2. 创建数据表
3. 测试代码
4. 测试结果
1. 运行环境说明
1) 测试运行环境:
1. 操作系统
版本 | Windows 10 专业版 |
版本号 | 1903 |
处理器 | i7-8550U CPU |
内存 | 32G |
2. VS 运行环境
测试项目类型 | 控制台应用程序 |
VS 版本 | Visual Studio 2019 |
版本号 | 16.3.4 |
2) 虚拟机运行环境
1. 操作系统
版本 | Window Server 2019 Datacenter |
版本号 | 1809 |
内存 | 12G |
2. 数据库
SQL Server 版本 | SQL Server 2019(SQL Server 2019 (CTP3.1) - 15.0.1700.37 (X64) Enterprise Evaluation Edition) |
MySQL 版本 | MySQL8.0(Server version: 8.0.17 MySQL Community Server – GPL) |
Oracle 版本 | Oracle19c(Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0) |
2. 创建数据表
1) SQL Server
--创建数据库
USE
master;
IF(EXISTS(SELECT
*
FROM
sys.sysdatabases
WHERE
name='TestDB'))
DROP
DATABASE
TestDB;
GO
CREATE
DATABASE TestDB
ON
PRIMARY
(
NAME='TestDB_Data',
FILENAME='D:\Databases\SQL
Server 2019\TestDB\TestDB_Data.mdf',
SIZE=3MB,
MAXSIZE=5120MB,
FILEGROWTH=20%
)
LOG
ON
(
NAME='TestDB_Log',
FILENAME='D:\Databases\SQL
Server 2019\TestDB\TestDB_Log.ldf',
SIZE=1MB,
MAXSIZE=1024MB,
FILEGROWTH=1MB
);
--创建表
USE
TestDB;
IF (OBJECT_ID('dbo.MyUser',
'U')
IS
NOT
NULL)
DROP
TABLE
dbo.MyUser;
GO
CREATE
TABLE
dbo.MyUser
(
UserId INT
NOT
NULL,
Name
VARCHAR(8)
NOT
NULL,
Sex TINYINT
NOT
NULL,
Age SMALLINT
NOT
NULL,
Birthday DATE
NOT
NULL,
City VARCHAR(6)
NOT
NULL,
IdNumber CHAR(18)
NOT
NULL,
Salary FLOAT
NULL,
Remarks VARCHAR(4000)
NOT
NULL,
CONSTRAINT
PK_MyUser_UserId PRIMARY
KEY
CLUSTERED
(
UserId ASC
)
ON
[PRIMARY],
CONSTRAINT
CK_MyUser_Sex CHECK
(Sex
>=
0 AND
Sex <=
2),
CONSTRAINT
UQ_MyUser_IdNumber UNIQUE(IdNumber)
)
ON
[PRIMARY];
IF (OBJECT_ID('dbo.MyOrder',
'U')
IS
NOT
NULL)
DROP
TABLE
dbo.MyOrder;
GO
CREATE
TABLE
dbo.MyOrder
(
OrderId INT
NOT
NULL,
UserId INT
NOT
NULL,
OrderNo VARCHAR(16)
NOT
NULL,
TotalAmount FLOAT
NOT
NULL,
OrderDate DATE
NOT
NULL,
Remarks VARCHAR(4000)
NULL,
CONSTRAINT
PK_MyOrder_OrderId PRIMARY
KEY
CLUSTERED
(
OrderId ASC
)
ON
[PRIMARY],
CONSTRAINT
UQ_MyOrder_OrderNo UNIQUE(OrderNo)
)
ON
[PRIMARY];
2) MySQL
--创建数据库
CREATE
SCHEMA `testdb` DEFAULT CHARACTER SET utf8mb4 ;
--创建表
CREATE
TABLE `testdb`.`myuser` (
`UserId` INT NOT NULL,
`Name` VARCHAR(8) NOT NULL,
`Sex` TINYINT NOT NULL,
`Age` SMALLINT NOT NULL,
`Birthday` DATE NOT NULL,
`City` VARCHAR(6) NOT NULL,
`IdNumber` CHAR(18) NOT NULL,
`Salary` FLOAT NULL,
`Remarks` VARCHAR(4000) NOT NULL,
PRIMARY KEY (`UserId`),
CHECK (Sex >= 0 AND Sex <= 2),
UNIQUE INDEX `IdNumber_UNIQUE` (`IdNumber` ASC)
VISIBLE);
CREATE
TABLE `testdb`.`myorder` (
`OrderId` INT NOT NULL,
`UserId` INT NOT NULL,
`OrderNo` VARCHAR(16) NOT NULL,
`TotalAmount` FLOAT NOT NULL,
`OrderDate` DATE NOT NULL,
`Remarks` VARCHAR(4000) NULL,
PRIMARY KEY (`OrderId`),
UNIQUE INDEX `OrderNo_UNIQUE` (`OrderNo` ASC)
VISIBLE);
3) Oracle
--创建表空间
create
tablespace myts datafile 'c:\oracle\oradata\testdb\myts01.dbf'
size 1024m;
--创建用户
create
user
user01 identified by
pwd123 default
tablespace myts;
--用户授权
grant
dba to
user01;
--用户登录
conn user01/pwd123;
--创建表
CREATE
TABLE MyUser (
UserId NUMBER(10) NOT NULL ,
Name VARCHAR2(8) NOT NULL ,
Sex NUMBER(3) NOT NULL ,
Age NUMBER(5) NOT NULL ,
Birthday DATE NOT NULL ,
City VARCHAR2(6) NOT NULL ,
IdNumber CHAR(18) NOT NULL ,
Salary FLOAT ,
Remarks VARCHAR2(4000) NOT NULL ,
PRIMARY KEY (UserId),
CONSTRAINT UQ_MyUser_IdNumber UNIQUE
(IdNumber),
CONSTRAINT CK_MyUser_Sex CHECK (Sex >= 0 AND Sex
<= 2)
)
CREATE
TABLE MyOrder (
OrderId NUMBER(10) NOT NULL ,
UserId NUMBER(10) NOT NULL ,
OrderNo VARCHAR2(16) NOT NULL ,
TotalAmount FLOAT NOT NULL ,
OrderDate DATE NOT NULL ,
Remarks VARCHAR2(4000) ,
PRIMARY KEY (OrderId),
CONSTRAINT UQ_MyOrder_OrderNo UNIQUE
(OrderNo)
)
4) 测试代码
数据库操作程序:
SQL Server | .NETFramework\v4.7.2\System.Data.dll |
MySQL | MySql.Data.8.0.17\lib\net452\MySql.Data.dll |
Oracle | Oracle.ManagedDataAccess.19.3.1\lib\net40\Oracle.ManagedDataAccess.dll 注意:使用"E:\Libs\ODAC\ODAC193Xcopy_x64\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll"将会报错! |
具体参考:\数据\Tests\DataBaseTesting\DataBaseTesting.sln
测试截图(举例):
5) 测试结果