Shulou(Shulou.com)05/31 Report--

This article focuses on "the advantages and performance testing of MySQL Memory storage engine". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the advantages and performance testing of MySQL Memory storage engine.

Test script:

Double click code 12 34 5 6 7 8 9 10 11 12 13 14 15 16 17 18 18 20 21 22 23 24 26 27 28 29 30 31 32 33 34 35 36/***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

Test the code:

Double-click the code to select all 12 34 56 78 9 10 11 12 13 14 15 16 17 18 19 20 21 23 24 26 27 28 29 30 31 33 34 36 37 38 39 41 42 44 45 46 47 48 49 51 52 53 55 56 57 59 60 62 63 65 66 67 69 70 71 72 73 74 76 77 78 79 80 81 83 84 86 87 88 89 90 91 92 94 95 98 99 102 103 107 108 109 110 111 112 113 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 170using 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; iTunes +) {DB_InnoDBInsert (OBJ);} Console.WriteLine ("InnoDBInsert Result: {0}", DateTime.Now.Ticks-begin);} static void InnoDBSelect () {long begin = DateTime.Now.Ticks; for (int I = LOOP_BEGIN I < LOOP_END; iTunes +) {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; ionization +) {DB_MyIsamInsert (OBJ);} Console.WriteLine ("MyIsamInsert Result: {0}", DateTime.Now.Ticks-begin);} static void MyIsamSelect () {long begin = DateTime.Now.Ticks For (int I = LOOP_BEGIN; I < LOOP_END; iTunes +) {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; iTunes +) {DB_MemoryInsert (OBJ);} Console.WriteLine ("MemoryInsert Result: {0}", DateTime.Now.Ticks-begin) } static void MemorySelect () {long begin = DateTime.Now.Ticks; for (int I = LOOP_BEGIN; I < LOOP_END; iTunes +) {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; iTunes +) {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; iTunes +) {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}

Summary

There is no doubt that the read and write performance of .net Cache is much better than the database engine.

InnoDB takes about 5 times more time to write than MyIsam and Memory, and its row locking mechanism must determine more performance overhead when writing, and its strength lies in multithreaded concurrent processing, but this test does not show its advantage.

The three database engines are similar in SELECT performance, Memory is slightly superior, and the comparison under the same high concurrency needs to be further tested.

At this point, I believe you have a deeper understanding of "the advantages and performance testing of MySQL Memory storage engine". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.