导航：首页 > 数据库 >

MySQL Memory存储引擎的优势及性能测试

发表于：2025-01-18 作者：千家信息网编辑

千家信息网最后更新 2025年01月18日，本篇内容主要讲解"MySQL Memory存储引擎的优势及性能测试"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"MySQL Memory存储引擎的优势及性

千家信息网最后更新 2025年01月18日MySQL Memory存储引擎的优势及性能测试

本篇内容主要讲解"MySQL Memory存储引擎的优势及性能测试"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"MySQL 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	`/****************************************************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存储引擎的优势及性能测试"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

很赞哦！