MySQL Memory存储引擎的优势及性能测试
本篇内容主要讲解“MySQL Memory存储引擎的优势及性能测试”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“MySQL Memory存储引擎的优势及性能测试”吧!
测试脚本:
/******************************************************
MYSQL STORAGE ENGINE TEST
http://wu-jian.cnblogs.com/
2011-11-29
******************************************************/
CREATE
DATABASE
IF
NOT
EXISTS test
CHARACTER
SET
'utf8'
COLLATE
'utf8_general_ci'
;
USE test;
/******************************************************
1.INNODB
******************************************************/
DROP
TABLE
IF EXISTS test_innodb;
CREATE
TABLE
IF
NOT
EXISTS test_innodb (
id
INT
UNSIGNED AUTO_INCREMENT COMMENT
'PK'
,
obj
CHAR
(255)
NOT
NULL
DEFAULT
''
COMMENT
'OBJECT'
,
PRIMARY
KEY
(id)
) ENGINE=INNODB;
/******************************************************
2.MYISAM
******************************************************/
DROP
TABLE
IF EXISTS test_myisam;
CREATE
TABLE
IF
NOT
EXISTS test_myisam (
id
INT
UNSIGNED AUTO_INCREMENT COMMENT
'PK'
,
obj
CHAR
(255)
NOT
NULL
DEFAULT
''
COMMENT
'OBJECT'
,
PRIMARY
KEY
(id)
) ENGINE=MYISAM;
/******************************************************
1.MEMORY
******************************************************/
DROP
TABLE
IF EXISTS test_memory;
CREATE
TABLE
IF
NOT
EXISTS test_memory (
id
INT
UNSIGNED AUTO_INCREMENT COMMENT
'PK'
,
obj
CHAR
(255)
NOT
NULL
DEFAULT
''
COMMENT
'OBJECT'
,
PRIMARY
KEY
(id)
) ENGINE=MEMORY;
测试代码:
using System;using System.Data;
using MySql.Data.MySqlClient;
namespace MySqlEngineTest{
class Program
{
const string OBJ =
"The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility."
;
const string SQL_CONN =
"Data Source=127.0.0.1;Port=3308;User ID=root;Password=root;DataBase=test;Allow Zero Datetime=true;Charset=utf8;pooling=true;"
;
const
int
LOOP_TOTAL = 10000;
const
int
LOOP_BEGIN = 8000;
const
int
LOOP_END = 9000;
#region
Database
Functions
public
static
bool DB_InnoDBInsert(string obj)
{
string commandText =
"INSERT INTO test_innodb (obj) VALUES (?obj)"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?obj"
, MySqlDbType.
VarChar
, 255)
};
parameters[0].Value = obj;
if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)
return
true
;
else
return
false
;
}
public
static
string DB_InnoDBSelect(
int
id)
{
string commandText =
"SELECT obj FROM test_innodb WHERE id = ?id"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?id"
, MySqlDbType.Int32)
};
parameters[0].Value = id;
return
DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();
}
public
static
bool DB_MyIsamInsert(string obj)
{
string commandText =
"INSERT INTO test_myisam (obj) VALUES (?obj)"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?obj"
, MySqlDbType.
VarChar
, 255)
};
parameters[0].Value = obj;
if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)
return
true
;
else
return
false
; }
public
static
string DB_MyIsamSelect(
int
id)
{
string commandText =
"SELECT obj FROM test_myisam WHERE id = ?id"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?id"
, MySqlDbType.Int32)
};
parameters[0].Value = id;
return
DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();
}
public
static
bool DB_MemoryInsert(string obj)
{
string commandText =
"INSERT INTO test_memory (obj) VALUES (?obj)"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?obj"
, MySqlDbType.
VarChar
, 255)
};
parameters[0].Value = obj;
if (DBUtility.MySqlHelper.ExecuteNonQuery(SQL_CONN, CommandType.Text, commandText, parameters) > 0)
return
true
;
else
return
false
;
}
public
static
string DB_MemorySelect(
int
id)
{
string commandText =
"SELECT obj FROM test_memory WHERE id = ?id"
;
MySqlParameter[] parameters = {
new MySqlParameter(
"?id"
, MySqlDbType.Int32)
};
parameters[0].Value = id;
return
DBUtility.MySqlHelper.ExecuteScalar(SQL_CONN, CommandType.Text, commandText, parameters).ToString();
}
#endregion
#region Test Functions InnoDB
static
void InnoDBInsert()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = 0; i < LOOP_TOTAL; i++)
{
DB_InnoDBInsert(OBJ);
}
Console.WriteLine(
"InnoDB Insert Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void InnoDBSelect()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = LOOP_BEGIN; i < LOOP_END; i++)
{
DB_InnoDBSelect(i);
}
Console.WriteLine(
"InnoDB SELECT Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void MyIsamInsert()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = 0; i < LOOP_TOTAL; i++)
{
DB_MyIsamInsert(OBJ);
}
Console.WriteLine(
"MyIsam Insert Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void MyIsamSelect()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = LOOP_BEGIN; i < LOOP_END; i++)
{
DB_MyIsamSelect(i);
}
Console.WriteLine(
"MyIsam SELECT Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void MemoryInsert()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = 0; i < LOOP_TOTAL; i++)
{
DB_MemoryInsert(OBJ);
}
Console.WriteLine(
"Memory Insert Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void MemorySelect()
{
long
begin
= DateTime.Now.Ticks;
for
(
int
i = LOOP_BEGIN; i < LOOP_END; i++)
{
DB_MemorySelect(i);
}
Console.WriteLine(
"Memory SELECT Result: {0}"
, DateTime.Now.Ticks -
begin
);
}
static
void DataTableInsertAndSelect()
{
//
Insert
DataTable dt = new DataTable();
dt.Columns.
Add
(
"id"
, Type.GetType(
"System.Int32"
));
dt.Columns[
"id"
].AutoIncrement =
true
;
dt.Columns.
Add
(
"obj"
, Type.GetType(
"System.String"
));
DataRow dr =
null
; long
begin
= DateTime.Now.Ticks;
for
(
int
i = 0; i < LOOP_TOTAL; i++)
{
dr =
null
;
dr = dt.NewRow();
dr[
"obj"
] = OBJ;
dt.
Rows
.
Add
(dr);
}
Console.WriteLine(
"DataTable Insert Result: {0}"
, DateTime.Now.Ticks -
begin
);
//
Select
long begin1 = DateTime.Now.Ticks;
for
(
int
i = LOOP_BEGIN; i < LOOP_END; i++)
{
dt.
Select
(
"id = "
+ i);
}
Console.WriteLine(
"DataTable Select Result: {0}"
, DateTime.Now.Ticks - begin1);
}
#endregion
static
void Main(string[] args)
{
InnoDBInsert();
InnoDBSelect();
//restart mysql
to
avoid query cache
MyIsamInsert();
MyIsamSelect();
//restart mysql
to
avoid query cache
MemoryInsert();
MemorySelect();
DataTableInsertAndSelect();
}
}//
end
class
}
总结
.Net Cache读写性能毫无疑问大大领先于数据库引擎
InnoDB写入耗时大概是MyIsam和Memory的5倍左右,它的行锁机制必然决定了写入时的更多性能开销,而它的强项在于多线程的并发处理,而本测试未能体现其优势。
三种数据库引擎在SELECT性能上差不多,Memory稍占优,同样高并发下的比较有待进一步测试。
到此,相信大家对“MySQL Memory存储引擎的优势及性能测试”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。