性能测试中数据库服务器的监控性能测试数据库

转载

mob64ca1410eb61 2024-05-06 16:28:42

文章标签 性能测试中数据库服务器的监控 Server SQL MySQL 文章分类 运维

Ø 简介

本文记录了三大关系型数据库 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"将会报错！