MySQLMemory存储引擎的优势及性能测试
本篇内容主要讲解“MySQL Memory存储引擎的优势及性能测试”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL Memory存储引擎的优势及性能测试”吧!
创新互联公司是一家朝气蓬勃的网站建设公司。公司专注于为企业提供信息化建设解决方案。从事网站开发，网站制作，网站设计，网站模板，微信公众号开发，软件开发，成都小程序开发，10余年建站对成都发电机维修等多个领域，拥有丰富的网站维护经验。
测试脚本：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | /******************************************************MYSQL STORAGE ENGINE TESThttp://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; |
测试代码：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | 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存储引擎的优势及性能测试”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
文章题目：MySQLMemory存储引擎的优势及性能测试
文章分享：http://6mz.cn/article/pdjogc.html