IoT分表场景下MariaDB查询及C#加载DataTable性能优化问询

1. SQL查询与索引优化 #

• 优化联合索引：将现有索引index_mqttpacket扩展为覆盖索引，避免回表查询开销：

ALTER TABLE `mqttpacket_{device_serial_number}` DROP INDEX `index_mqttpacket`, ADD INDEX `index_mqttpacket`(`data_type_id`,`inserted_date`, `inserted_time`);

• 替换低效CASE WHEN条件，改为优化器更容易识别的范围条件，提升索引利用率：

WHERE  mqttpacket_123.data_type_id IN(1,2,3,4,5,6)
  AND inserted_date BETWEEN '2021-11-08' AND '2021-11-15'
  AND NOT (
    (inserted_date = '2021-11-08' AND inserted_time <= '12:25:00')
    OR (inserted_date = '2021-11-15' AND inserted_time >= '12:25:00')
  )

• 取消数据库侧的日期时间拼接操作，改为在C#侧合并inserted_date和inserted_time字段，减少数据库计算量和传输的数据包体积。

2. C#代码侧优化 #

• 取消动态传SQL的存储过程SP_RunQuery，改为直接使用参数化查询，避免SQL注入风险的同时，减少存储过程动态执行SQL的额外开销。
• 连接字符串添加传输压缩配置，大幅降低7.5秒的网络传输耗时：在连接串中加入UseCompression=true参数，开启MySQL传输层压缩。
• 替换DataTable加载逻辑：如果不需要DataTable的编辑、schema自动推断等功能，直接使用MySqlDataReader逐行读取映射为自定义实体类的List，50万行场景下加载速度可提升30%以上，示例逻辑如下：

public class DeviceData
{
    public int DataValue { get; set; }
    public string DataName { get; set; }
    public double ValueMult { get; set; }
    public DateTime InsertedDateTime { get; set; }
}

public List<DeviceData> QueryDeviceData(string deviceSn, List<int> typeIds, DateTime startTime, DateTime endTime)
{
    var result = new List<DeviceData>();
    using var conn = new MySqlConnection(_connectionString);
    conn.Open();
    string sql = $@"
        SELECT  m.data_value, d.data_name, d.value_mult, m.inserted_date, m.inserted_time
        FROM  mqttpacket_{deviceSn} m
        JOIN  datatypes d ON m.data_type_id = d.id
        WHERE  m.data_type_id IN @typeIds
          AND m.inserted_date BETWEEN @startDate AND @endDate
          AND NOT (
            (m.inserted_date = @startDate AND m.inserted_time <= @startTime)
            OR (m.inserted_date = @endDate AND m.inserted_time >= @endTime)
          )";
    using var cmd = new MySqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@typeIds", typeIds);
    cmd.Parameters.AddWithValue("@startDate", startTime.Date);
    cmd.Parameters.AddWithValue("@endDate", endTime.Date);
    cmd.Parameters.AddWithValue("@startTime", startTime.TimeOfDay);
    cmd.Parameters.AddWithValue("@endTime", endTime.TimeOfDay);
    
    using var reader = cmd.ExecuteReader();
    while(reader.Read())
    {
        result.Add(new DeviceData
        {
            DataValue = reader.GetInt32("data_value"),
            DataName = reader.GetString("data_name"),
            ValueMult = reader.GetDouble("value_mult"),
            InsertedDateTime = reader.GetDateTime("inserted_date").Add(reader.GetTimeSpan("inserted_time"))
        });
    }
    return result;
}

• 若必须使用DataTable，可配置MySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.Ignore，关闭自动schema推断逻辑，减少填充开销。

3. 表结构长期优化 #

• 将分存的inserted_date和inserted_time字段合并为单个inserted_dt DATETIME类型字段，插入时直接赋值，查询时可直接用inserted_dt BETWEEN '2021-11-08 12:25:00' AND '2021-11-15 12:25:00'作为条件，查询逻辑更简洁，索引效率更高。

4. 业务逻辑优化 #

• 若场景允许，不要一次拉取全量50万行数据，改为按天/按小时分片查询，或者做分页加载，单次数据量降低后加载速度会大幅提升。

内容的提问来源于stack exchange，提问作者Taylan Yuksel