您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

C# 壓力測試程式同時支援 MySQL 與 SQL Server：連線工廠、批次寫入、效能比對實作

編輯：MySQL 綜合教學

設計目標

單一代碼庫支援 MySQL / SQL Server 切換
模擬高併發寫入壓力
驗證資料庫效能極限與穩定性

核心類別 `DbHelper`

using System;
using System.Data;
using MySql.Data.MySqlClient;  // NuGet: MySql.Data
using System.Data.SqlClient;

public static class DbHelper {
    // MySQL 連線工廠
    public static MySqlConnection CreateMySqlConn() {
        var cs = new MySqlConnectionStringBuilder {
            Server = "localhost",
            Database = "ylcg",
            UserID = "root",
            Password = "123456",
            CharacterSet = "utf8"
        };
        return new MySqlConnection(cs.ConnectionString);
    }

    // SQL Server 連線工廠
    public static SqlConnection CreateSqlConn() {
        return new SqlConnection("server=.;database=ylcg;uid=sa;pwd=123456;");
    }

    // 通用查詢（示範）
    public static DataTable GetYlTable(IDbConnection conn) {
        using (var cmd = conn.CreateCommand()) {
            cmd.CommandText = "SELECT * FROM yl";
            using (var da = CreateAdapter(conn)) {
                var dt = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(dt);
                return dt;
            }
        }
    }

    private static IDbDataAdapter CreateAdapter(IDbConnection conn) {
        if (conn is MySqlConnection) return new MySqlDataAdapter();
        if (conn is SqlConnection) return new SqlDataAdapter();
        throw new NotSupportedException();
    }

    // 壓力寫入：100 筆隨機資料
    public static void InsertPressureTest(IDbConnection conn) {
        using (var cmd = conn.CreateCommand()) {
            var times = new[] { "00:10","00:20","00:30","00:40","00:50",
                "01:10","01:20","01:30","01:40","01:50",
                "02:10","02:20","02:30","02:40","02:50",
                "03:10","03:20","03:30","03:40","03:50",
                "04:10","04:20","04:30","04:40","04:50",
                "05:10","05:20","05:30","05:40","05:50",
                "06:10","06:20","06:30","06:40","06:50",
                "07:10","07:20","07:30","07:40","07:50",
                "08:10","08:20","08:30","08:40","08:50",
                "09:10","09:20","09:30","09:40","09:50",
                "10:10","10:20","10:30","10:40","10:50",
                "11:10","11:20","11:30","11:40","11:50",
                "12:10","12:20","12:30","12:40","12:50",
                "13:10","13:20","13:30","13:40","13:50",
                "14:10","14:20","14:30","14:40","14:50",
                "15:10","15:20","15:30","15:40","15:50",
                "16:10","16:20","16:30","16:40","16:50",
                "17:10","17:20","17:30","17:40","17:50",
                "18:10","18:20","18:30","18:40","18:50",
                "19:10","19:20","19:30","19:40","19:50" };
            var rnd = new Random();
            for (int i = 0; i < 100; i++) {
                int val = rnd.Next(1120, 1890);
                cmd.CommandText = $"INSERT INTO yl (sendtime, sendvalue, cgID) VALUES ('{times[i]}', {val}, 11)";
                cmd.CommandType = CommandType.Text;
                if (conn.State != ConnectionState.Open) conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}

測試表結構

CREATE TABLE yl (
    ylID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    sendtime VARCHAR(20),
    sendvalue INT,
    cgID INT NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

執行方式

// MySQL 壓測
using (var conn = DbHelper.CreateMySqlConn()) {
    DbHelper.InsertPressureTest(conn);
}

// SQL Server 壓測
using (var conn = DbHelper.CreateSqlConn()) {
    DbHelper.InsertPressureTest(conn);
}

效能觀測指標

指標	MySQL 觀測	SQL Server 觀測
寫入延遲 (ms)	`SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';` / 慢查詢日誌	DMV: `sys.dm_exec_query_stats`
CPU/記憶體	`top` / `mysqladmin status`	效能監視器 / SSMS 活動監視器
鎖爭用	`SHOW ENGINE INNODB STATUS`	`sys.dm_os_wait_stats`
連線數	`Threads_connected`	`user connections`