程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL Memory 存儲引擎淺析(2)

MySQL Memory 存儲引擎淺析(2)

編輯:關於MYSQL數據庫

測試腳本:

/******************************************************
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稍占優,同樣高並發下的比較有待進一步測試。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved