Dotmim.Sync
Dotmim.Sync copied to clipboard
The base table and the tracing table use different character set for the primary key, which greatly reduces the query performance
The base table and the tracing table use different character sets for the primary key, which greatly reduces the query performance. eg: The base table DDL:
CREATE TABLE `ezreportdevicecheckresult` (
`Id` char(36) CHARACTER SET ascii NOT NULL,
`DeviceId` char(36) CHARACTER SET ascii NOT NULL,
`ProjectId` char(36) CHARACTER SET ascii NOT NULL,
`CheckPointId` char(36) CHARACTER SET ascii NOT NULL,
`OptionId` char(36) CHARACTER SET ascii DEFAULT NULL,
`Rank` int(11) DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`Result` int(11) DEFAULT NULL,
`BeforPhoto` char(36) CHARACTER SET ascii DEFAULT NULL,
`AfterPhtot` char(36) CHARACTER SET ascii DEFAULT NULL,
`Measures` varchar(255) DEFAULT NULL,
`CreationTime` datetime(6) NOT NULL,
`CreatorId` char(36) CHARACTER SET ascii DEFAULT NULL,
`LastModificationTime` datetime(6) DEFAULT NULL,
`LastModifierId` char(36) CHARACTER SET ascii DEFAULT NULL,
`IsDeleted` tinyint(1) NOT NULL DEFAULT '0',
`DeleterId` char(36) CHARACTER SET ascii DEFAULT NULL,
`DeletionTime` datetime(6) DEFAULT NULL,
`Material` longtext,
PRIMARY KEY (`Id`) USING BTREE,
KEY `IX_EzReportDeviceCheckResult_CheckPointId` (`CheckPointId`) USING BTREE,
KEY `IX_EzReportDeviceCheckResult_DeviceId` (`DeviceId`) USING BTREE,
KEY `IX_EzReportDeviceCheckResult_OptionId` (`OptionId`) USING BTREE,
KEY `IX_EzReportDeviceCheckResult_ProjectId` (`ProjectId`) USING BTREE,
CONSTRAINT `FK_EzReportDeviceCheckResult_EzReportCheckPointOption_OptionId` FOREIGN KEY (`OptionId`) REFERENCES `ezreportcheckpointoption` (`Id`),
CONSTRAINT `FK_EzReportDeviceCheckResult_EzReportCheckPoint_CheckPointId` FOREIGN KEY (`CheckPointId`) REFERENCES `ezreportcheckpoint` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_EzReportDeviceCheckResult_MainDataDevice_DeviceId` FOREIGN KEY (`DeviceId`) REFERENCES `maindatadevice` (`Id`) ON DELETE CASCADE,
CONSTRAINT `FK_EzReportDeviceCheckResult_MainDataProject_ProjectId` FOREIGN KEY (`ProjectId`) REFERENCES `maindataproject` (`Id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;`
The generated tracking table DDL:
CREATE TABLE `ezreportdevicecheckresult_tracking` (
`Id` char(36) NOT NULL,
`update_scope_id` varchar(36) DEFAULT NULL,
`timestamp` bigint(20) DEFAULT NULL,
`sync_row_is_tombstone` bit(1) NOT NULL DEFAULT b'0',
`last_change_datetime` datetime DEFAULT NULL,
PRIMARY KEY (`Id`) USING BTREE,
KEY `ezreportdevicecheckresult_tracking_timestamp` (`timestamp`) USING BTREE,
KEY `ezreportdevicecheckresult_tracking_timestamp_scope_id` (`Id`,`timestamp`,`update_scope_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;`
The query statistics:
# Time: 2023-10-09T17:01:28.145013+08:00
# User@Host: root[root] @ [172.16.0.5] Id: 41807664
# Query_time: 1799.823835 Lock_time: 0.000354 Rows_sent: 102525 Rows_examined: 2461440 Thread_id: 41807664 Schema: sea-prd Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 24510676 Read_first: 24 Read_last: 0 Read_key: 24 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 2461464 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2023-10-09T16:31:28.321178+08:00 End: 2023-10-09T17:01:28.145013+08:00 Launch_time: 0.000000
# QC_Hit: No Full_scan: Yes Full_join: Yes Tmp_table: No Tmp_table_on_disk: No Filesort: No Filesort_on_disk: No
use sea-prd;
SET timestamp=1696840288;
SELECT `side`.`Id`,
`base`.`DeviceId`,
`base`.`ProjectId`,
`base`.`CheckPointId`,
`base`.`OptionId`,
`base`.`Rank`,
`base`.`Description`,
`base`.`Result`,
`base`.`BeforPhoto`,
`base`.`AfterPhtot`,
`base`.`Measures`,
`base`.`CreationTime`,
`base`.`CreatorId`,
`base`.`LastModificationTime`,
`base`.`LastModifierId`,
`base`.`IsDeleted`,
`base`.`DeleterId`,
`base`.`DeletionTime`,
`base`.`Material`,
`side`.`sync_row_is_tombstone`,
`side`.`update_scope_id` as `sync_update_scope_id`
FROM `EzReportDeviceCheckResult` `base`
RIGHT JOIN `EzReportDeviceCheckResult_tracking` `side` ON `base`.`Id` = `side`.`Id`
WHERE (
`side`.`timestamp` > 16922403351973
AND (`side`.`update_scope_id` <> 'f55c7d2a-1fd4-4b10-a140-3749596b0e20' OR `side`.`update_scope_id` IS NULL)
);`
sql explain:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | side | NULL | ALL | ezreportdevicecheckresult_tracking_timestamp | NULL | NULL | NULL | 203300 | 45.50 | Using where |
1 | SIMPLE | base | NULL | ALL | NULL | NULL | NULL | NULL | 199442 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
If I change character set of 'chezreportdevicecheckresult_tracking.id' to 'ascii',the sql expain is :
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | side | NULL | ALL | ezreportdevicecheckresult_tracking_timestamp | NULL | NULL | NULL | 201550 | 45.50 | Using where |
1 | SIMPLE | base | NULL | eq_ref | PRIMARY | PRIMARY | 36 | sea_prd.side.Id | 1 | 100.00 | NULL |
The query time is 0.002s