Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 在相同环境下MYSQL执行需要0.8,tidb需要接近10秒
[Test Environment for TiDB]
[TiDB Version]
[Reproduction Path] With 700,000 records, there is a significant performance difference between MySQL and TiDB during SQL queries. The test environment has no load.
[Encountered Issue:]
[Resource Configuration] 3 machines with 8 cores and 16GB RAM each
SQL Statement:
SELECT
tsd.*
FROM
t_system_diagnosis tsd
LEFT JOIN t_combined_diagnosis_rel tcdr ON tsd.union_id = tcdr.ref_diagnosis_id
WHERE
tcdr.ref_conclusion_id = '934749606573506560'
Table Data Volume,
Table Structure
Table Indexes
MySQL Execution Plan
TiDB Execution Plan
MySQL Execution Result and Time
TiDB Execution Result and Time
How about trying to analyze the table?
Try changing the left join to an inner join.
TiDB did not use the index and performed a full table scan. This is not normal.
Are you sure tidb ref_conclusion_id has an index?
All possible indexes have been created, and the index fields all have values, but they are just not being used. I don’t know why.
It was already established before, but it didn’t work.
Please send the SQL text and the explain analyze text. The screenshot is incomplete and missing a lot of information at the end.
/*
Navicat Premium Data Transfer
Source Server : 192.168.9.80Tidb
Source Server Type : MySQL
Source Server Version : 50725
Source Host : 192.168.9.80:4000
Source Schema : temos_v2_5_2
Target Server Type : MySQL
Target Server Version : 50725
File Encoding : 65001
Date: 30/11/2023 10:13:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_combined_diagnosis_rel
-- ----------------------------
DROP TABLE IF EXISTS `t_combined_diagnosis_rel`;
CREATE TABLE `t_combined_diagnosis_rel` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`ref_diagnosis_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Intelligent diagnosis uid',
`ref_conclusion_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Combined diagnosis conclusion uid',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `index_unine_conclusion`(`ref_diagnosis_id`, `ref_conclusion_id`) USING BTREE COMMENT 'Unique index',
INDEX `conclusion_index_rel`(`ref_conclusion_id`) USING BTREE,
INDEX `index_diagnosis_id`(`ref_diagnosis_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'Combined intelligent diagnosis relationship table' ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for t_system_diagnosis
-- ----------------------------
DROP TABLE IF EXISTS `t_system_diagnosis`;
CREATE TABLE `t_system_diagnosis` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`ref_device_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Device ID',
`ref_point_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Measurement point ID',
`feature_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Feature value code',
`ref_diagnosis_task_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Diagnosis task ID',
`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Diagnosis conclusion',
`fault_category` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault category',
`fault_type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault type',
`level` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault level',
`create_time` bigint(20) NULL DEFAULT NULL COMMENT 'Creation time',
`union_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Business primary key',
`status` int(11) NULL DEFAULT NULL COMMENT 'Fault diagnosis status',
`status_description` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Fault diagnosis status description',
`ref_tenant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Tenant Uid',
`mp_loc` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Measurement point location',
`component` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Component',
`decision_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'Decision table Uid',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `union_id_index`(`union_id`) USING BTREE,
UNIQUE INDEX `union_id_index_system`(`union_id`) USING BTREE,
INDEX `index_point_uid`(`ref_point_id`) USING BTREE,
INDEX `index_system_diagnosis_ref_device_id`(`ref_device_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6180007 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'System diagnosis conclusion' ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
Is there no index on the unionid of TSD?
I have encountered the same problem. I found that the issue was due to the incorrect configuration of the max_allowed_packet
parameter in the MySQL client. After adjusting this parameter, the problem was resolved. You can try setting max_allowed_packet
to a larger value, such as 64M, in the MySQL client configuration file.
The character set and collation are different.
The problem is that there is a huge performance gap, and currently, I don’t know how to optimize it. It shouldn’t be this slow theoretically.
±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_20 | 12.50 | root | | inner join, inner:IndexLookUp_19, outer key:test.t_combined_diagnosis_rel.ref_diagnosis_id, inner key:test.t_system_diagnosis.union_id, equal cond:eq(test.t_combined_diagnosis_rel.ref_diagnosis_id, test.t_system_diagnosis.union_id) |
| ├─IndexLookUp_38(Build) | 10.00 | root | | |
| │ ├─IndexRangeScan_36(Build) | 10.00 | cop[tikv] | table:tcdr, index:conclusion_index_rel(ref_conclusion_id) | range:[“934749606573506560”,“934749606573506560”], keep order:false, stats:pseudo |
| │ └─TableRowIDScan_37(Probe) | 10.00 | cop[tikv] | table:tcdr | keep order:false, stats:pseudo |
| └─IndexLookUp_19(Probe) | 10.00 | root | | |
| ├─Selection_18(Build) | 10.00 | cop[tikv] | | not(isnull(test.t_system_diagnosis.union_id)) |
| │ └─IndexRangeScan_16 | 10.00 | cop[tikv] | table:tsd, index:union_id_index(union_id) | range: decided by [eq(test.t_system_diagnosis.union_id, test.t_combined_diagnosis_rel.ref_diagnosis_id)], keep order:false, stats:pseudo |
| └─TableRowIDScan_17(Probe) | 10.00 | cop[tikv] | table:tsd | keep order:false, stats:pseudo |
±-----------------------------------±--------±----------±----------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
You changed the collation, it’s faster now.
I don’t understand, could you please explain how to modify it?
Should CHARACTER SET and COLLATE be consistent?