MySQL Fast Select Query | C# 고속 로드 데이터 테이블

MySQL Fast Select Query | C# 고속 로드 데이터 테이블

MariaDB를 사용하고 있습니다.첫 번째 삽입 시 저장 프로시저를 사용하여 모든 IoT 디바이스에 대해 작성하는 테이블이 있습니다.모든 기기에 대해 새로운 테이블을 만드는 이유가 5초마다 데이터를 공개하기 때문에 하나의 테이블에 모두 저장하는 것은 불가능합니다.

테이블 구조는 다음과 같습니다.

CREATE TABLE IF NOT EXISTS `mqttpacket_',device_serial_number,'`(
`data_type_id` int(11) DEFAULT NULL, 
`data_value` int(11) DEFAULT NULL,
`inserted_date` DATE DEFAULT NULL, 
`inserted_time` TIME DEFAULT NULL, 
FOREIGN KEY(data_type_id) REFERENCES datatypes(id), 
INDEX `index_mqttpacket`(`data_type_id`,`inserted_date`)) ENGINE = INNODB;

나는 매우 길다.SELECT아래와 같이 쿼리하여 선택한 사이에 데이터를 가져옵니다.type,date,그리고.time.

SELECT  mqttpacket_123.data_value, datatypes.data_name, datatypes.value_mult,
        CONCAT(mqttpacket_123.inserted_date, ' ',
               mqttpacket_123.inserted_time) AS 'inserted_date_time'
    FROM  mqttpacket_123
    JOIN  datatypes  ON mqttpacket_123.data_type_id = datatypes.id
    WHERE  mqttpacket_123.data_type_id IN(1,2,3,4,5,6)
      AND  CASE WHEN mqttpacket_123.inserted_date = '2021-11-08'
                    THEN mqttpacket_123.inserted_time > '12:25:00'
                WHEN mqttpacket_123.inserted_date = '2021-11-15'
                    THEN mqttpacket_123.inserted_time< '12:25:00'
                ELSE (mqttpacket_123.inserted_date BETWEEN '2021-11-08'
                                                       AND '2021-11-15')
                END;

아래 샘플의 약 50만 레코드가 반환됩니다.

|   data_value  |       data_name       |   value_mult  |   inserted_date_time  |
--------------------------------------------------------------------------------
|       271     |       name_1          |       0.1     |   2021-11-08 12:25:04 |
|       106     |       name_2          |       0.1     |   2021-11-08 12:25:04 |
|       66      |       name_3          |       0.1     |   2021-11-08 12:25:04 |
|       285     |       name_4          |       0.1     |   2021-11-08 12:25:04 |
|       61      |       name_5          |       0.1     |   2021-11-08 12:25:04 |
|       454     |       name_6          |       0.1     |   2021-11-08 12:25:04 |
|       299     |       name_7          |       0.1     |   2021-11-08 12:25:04 |

영향을 받는 행: 0 검색된 행: 395,332 경고: 0 쿼리 기간: 0.734초(+7.547초)네트워크)

백업 시스템이 있기 때문에 지난 2주간의 데이터만 테이블에 보관하고 이전 데이터를 정리합니다.

그러나 쿼리 결과를 DataTable에 로드하는 데에도 최대 30초가 소요됩니다.MySQL보다 4배 느립니다.

이 성능을 개선하기 위해 제안할 사항이 있습니까?

추신. 이 쿼리를 C#에서 Stored Procedure의 다음 문장으로 호출합니다.RunQuery쿼리를 그대로 수행합니다.

public DataTable CallStoredProcedureRunQuery(string QueryString)
        {
            DataTable dt = new DataTable();

            try
            {
                using (var conn = new MySqlConnection(_connectionString))
                {
                    conn.Open();
                    using (var cmd = new MySqlCommand("SP_RunQuery", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add("@query_string", MySqlDbType.VarChar).Value = QueryString; 

                        using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                        {
                            sda.Fill(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                IoTemplariLogger.tLogger.EXC("Call Stored Procedure for RunQuery failed.", ex);
            }
            return dt;
        }

편집: 센서가 단일 MQ를 푸시합니다.최대 50개의 다른 데이터를 포함하는 TT 패킷.12번 있습니다.5seconds잠시 후에.즉, 기본적으로는 디바이스당 최대 600개의 행을 수신할 수 있습니다.

데이터 삽입은 에서 이루어집니다.Stored Procedure비동기. JSON 콘텐츠와device_idJSON을 반복해서 해석하고insert into테이블

추신. 아래 코드는 설명을 위한 것입니다.잘 되고 있어요.

/*Dynamic SQL -- IF they are registered to the system but have notable, create it.*/
    SET create_table_query = CONCAT('CREATE TABLE IF NOT EXISTS `mqttpacket_',device_serial_number,'`(`data_type_id` int(11) DEFAULT NULL, `data_value` int(11) DEFAULT NULL,`inserted_date` DATE DEFAULT NULL, `inserted_time` TIME DEFAULT NULL, FOREIGN KEY(data_type_id) REFERENCES datatypes(id), INDEX `index_mqttpacket`(`data_type_id`,`inserted_date`)) ENGINE = InnoDB;');
    PREPARE stmt FROM create_table_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    /*Loop into coming value array. It is like: $.type_1,$.type_2,$.type_3, to iterate in the JSON. We reach each value like $.type_1*/
    WHILE (LOCATE(',', value_array) > 0)
    DO
        SET arr_data_type_name = SUBSTRING_INDEX(value_array,',',1); /*pick first item of value array*/
        SET value_array = SUBSTRING(value_array, LOCATE(',',value_array) + 1); /*remove picked first item from the value_array*/
        
        SELECT JSON_EXTRACT(incoming_data, arr_data_type_name) INTO value_iteration; /*extract value of first item. $.type_1*/
        
        SET arr_data_type_name := SUBSTRING_INDEX(arr_data_type_name, ".", -1); /*Remove the $ and the . to get pure data type name*/
        
        /*Check the data type name exists or not in the table, if not insert and assign it's id to lcl_data_type_id*/
            IF (SELECT COUNT(id) FROM datatypes WHERE datatypes.data_name = arr_data_type_name) > 0 THEN 
                SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
            ELSE
                SELECT devices.device_type_id INTO lcl_device_type FROM devices WHERE devices.id = lcl_device_id LIMIT 1;
                INSERT INTO datatypes (datatypes.data_name,datatypes.description,datatypes.device_type_id,datatypes.value_mult ,datatypes.inserted_time) VALUES(arr_data_type_name,arr_data_type_name,lcl_device_type,0.1,NOW());
                SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
            END IF;
            
            /*To retrieve the table of which device has which datatypes inserted, this is to not to retrieve the datatypes unneccesseraly for the selected device*/
            IF (SELECT COUNT(device_id) FROM devicedatatypes WHERE devicedatatypes.device_id = lcl_device_id AND devicedatatypes.datatype_id = lcl_data_type_id) < 1 THEN
                INSERT INTO devicedatatypes (devicedatatypes.device_id, devicedatatypes.datatype_id) VALUES(lcl_device_id,lcl_data_type_id);
            END IF;
            
            SET lcl_insert_mqtt_query = CONCAT('INSERT INTO mqttpacket_',device_serial_number,'(data_type_id,data_value,inserted_date,inserted_time) VALUES(',lcl_data_type_id,',',value_iteration,',''',data_date,''',''',data_time,''');');
            PREPARE stmt FROM lcl_insert_mqtt_query;
            EXECUTE stmt;
            SET affected_data_row_count = affected_data_row_count + 1;
                        
    END WHILE;

코멘트에 관한 서버 및 데이터베이스에 관한 추가 정보도 여기에 기재되어 있습니다.서버에 SSD가 있습니다.내 것 말고는 다른 중요한 것이 없다.dotnet응용 프로그램 및 데이터베이스.

• 일반적으로는 더 좋은 것이 있습니다.DATETIME컬럼을 두 개로 분할하는 대신 (DATE그리고.TIME)의 컬럼.이 경우, 데이터 전송이WHERE절을 클릭합니다.

• 일반적으로 디바이스당 1개의 테이블을 갖는 것은 좋지 않습니다.대신 device_id 열을 추가하십시오.

• 없는 것PRIMARY KEY좋지 않은 생각입니다.특정 장치에 대해 1초에 두 개의 판독치를 얻은 적이 있습니까?아마 아닐 것입니다.

이 두 가지를 함께 롤링하고 다른 변경 가능성이 있는 몇 가지 사항을 먼저 표 변경부터 시작합니다.

CREATE TABLE IF NOT EXISTS `mqttpacket`(
`device_serial_number` SMALLINT UNSIGNED NOT NULL,
`data_type_id` TINYINT UNSIGNED NOT NULL,
`data_value` SMALLINT NOT NULL,
`inserted_at` DATETIME NOT NULL, 
FOREIGN KEY(data_type_id) REFERENCES datatypes(id), 
PRIMARY KEY(device_serial_number, `data_type_id`,`inserted_at`)
) ENGINE = INNODB;

이 PK를 사용하면 쿼리가 빨라집니다.

'아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아,DATETIME:

  AND inserted_at >= '2021-11-08 12:25:00'
  AND inserted_at  < '2021-11-08 12:25:00' + INTERVAL 7 DAY

하려면 2주치 데이터를 보관해야 .DROP PARTITION이치노나는 사용할 것이다.PARTITION BY RANGE(TO_DAYS(inserted_at))http://mysql.rjweb.org/doc.php/partitionmaint에서 설명한 바와 같이 16개의 파티션이 있습니다.

5초마다 1,000개의 행을 삽입하는 경우 - 디바이스별 테이블에서는 각각 1개의 삽입을 수행하는 1,000개의 스레드가 필요합니다.을 사용하다의 테이블할 수 있는 1개의 으로 1000개의 행이 동시에 됩니다.INSERT5초마다.나는 다른 빠른 섭취에 대해 논의한다.

초당 속도 = RPS

인스턴스 [mysqld]섹션에서 고려해야 할 제안

innodb_io_capacity=500  # from 200 to use more of available SSD IOPS
innodb_log_file_size=256M  # from 48M to reduce log rotation frequency
innodb_log_buffer_size=128M  # from 16M to reduce log rotation avg 25 minutes
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
innodb_buffer_pool_size=10G  # from 128M to reduce innodb_data_reads 85 RPS
innodb_change_buffer_max_size=50  # from 25 percent to expedite pages created 590 RPhr

찰,,innodb_flush_method=O_DIRECT # from fsync for method typically used on LX systems

이를 통해 작업 완료 성능이 크게 향상됩니다.성능 조정을 지원하기 위해 무료로 다운로드할 수 있는 유틸리티 스크립트를 보려면 프로필을 확인하십시오.
글로벌 변수를 조정할 수 있는 추가 기회가 있습니다.

언급URL : https://stackoverflow.com/questions/69974168/mysql-faster-select-query-c-sharp-faster-load-datatable