Ask for help with one issue planner-related or sql-rewriting related

Use Case

create database test;
use test;
CREATE TABLE test_complex_index (
id bigint unsigned AUTO_INCREMENT,
chat_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
message_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id) ,
key i_chat_message_id(chat_id,message_id)
);
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1058584');
insert into test_complex_index(chat_id,message_id) value('-1001596791137','1623617');
insert into test_complex_index(chat_id,message_id) value('-1001596791137','1623617');
insert into test_complex_index(chat_id,message_id) value('-1001596791137','1623617');
insert into test_complex_index(chat_id,message_id) value('-1001552238630','2872919');
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1069848');
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1069849');
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1069850');
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1069851');
insert into test_complex_index(chat_id,message_id) value('-1001554634644','1069852');
explain select * from test_complex_index where message_id = 1360977 and chat_id = -1001554634644 limit 1;
explain select * from test_complex_index where message_id = '1360977' and chat_id = '-1001554634644' limit 1;
Describe the solution you'd like

1.以下两个sql中，期望第一条sql 能像第二条一样，使用到复合索引。
explain select * from test_complex_index where message_id = 1360977 and chat_id = -1001554634644 limit 1;
explain select * from test_complex_index where message_id = '1360977' and chat_id = '-1001554634644' limit 1;
2.期望tidb 有 optimizer_trace 那样的工具分析sql解析、优化过程。
Describe alternatives you've considered
explain select * from test_complex_index where message_id = 1360977 and chat_id = -1001554634644 limit 1; 第一条走了全表扫描。期望使用该sql复合索引key i_chat_message_id
Additional context
补充一下前面两个sql的 optimizer_trace内容
set optimizer_trace='enabled=on';
explain select * from test_complex_index2 where message_id = 1360977 and chat_id = -1001554634644 limit 1;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
*************************** 1. row ***************************
QUERY: explain select * from test_complex_index2 where message_id = 1360977 and chat_id = -1001554634644 limit 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select test_complex_index2.id AS id,test_complex_index2.chat_id AS chat_id,test_complex_index2.message_id AS message_id,test_complex_index2.message AS message from test_complex_index2 where ((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644))) limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "test_complex_index2",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
]
},
{
"rows_estimation": [
{
"table": "test_complex_index2",
"range_analysis": {
"table_scan": {
"rows": 10,
"cost": 5.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i_chat_message_id",
"usable": true,
"key_parts": [
"chat_id",
"message_id",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"chosen": false,
"cause": "disjuntive_predicate_present"
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "test_complex_index2",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 10,
"filtering_effect": [
],
"final_filtering_effect": 0.1,
"access_type": "scan",
"resulting_rows": 1,
"cost": 1.25,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 1.25,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))",
"attached_conditions_computation": [
{
"table": "test_complex_index2",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 1,
"row_estimate": 1,
"range_analysis": {
"table_scan": {
"rows": 10,
"cost": 5.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i_chat_message_id",
"usable": true,
"key_parts": [
"chat_id",
"message_id",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"chosen": false,
"cause": "disjuntive_predicate_present"
}
}
}
}
],
"attached_conditions_summary": [
{
"table": "test_complex_index2",
"attached": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "test_complex_index2",
"original_table_condition": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = -(1001554634644)))",
"final_table_condition ": "((test_complex_index2.message_id = 1360977) and (test_complex_index2.chat_id = (-(1001554634644))))"
}
]
},
{
"refine_plan": [
{
"table": "test_complex_index2"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE \G
*************************** 1. row ***************************
QUERY: explain select * from test_complex_index2 where message_id = '1360977' and chat_id = '-1001554634644' limit 1
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select test_complex_index2.id AS id,test_complex_index2.chat_id AS chat_id,test_complex_index2.message_id AS message_id,test_complex_index2.message AS message from test_complex_index2 where ((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644')) limit 1"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "test_complex_index2",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "test_complex_index2",
"field": "chat_id",
"equals": "'-1001554634644'",
"null_rejecting": true
},
{
"table": "test_complex_index2",
"field": "message_id",
"equals": "'1360977'",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "test_complex_index2",
"range_analysis": {
"table_scan": {
"rows": 10,
"cost": 5.5
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "i_chat_message_id",
"usable": true,
"key_parts": [
"chat_id",
"message_id",
"id"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "i_chat_message_id",
"usable": false,
"cause": "query_references_nonkey_column"
}
]
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "i_chat_message_id",
"ranges": [
"chat_id = '-1001554634644' AND message_id = '1360977'"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 1,
"cost": 0.61,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "i_chat_message_id",
"rows": 1,
"ranges": [
"chat_id = '-1001554634644' AND message_id = '1360977'"
]
},
"rows_for_plan": 1,
"cost_for_plan": 0.61,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "test_complex_index2",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "i_chat_message_id",
"rows": 1,
"cost": 0.35,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "i_chat_message_id"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 0.35,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "test_complex_index2",
"attached": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))"
}
]
}
},
{
"finalizing_table_conditions": [
{
"table": "test_complex_index2",
"original_table_condition": "((test_complex_index2.message_id = '1360977') and (test_complex_index2.chat_id = '-1001554634644'))",
"final_table_condition ": null
}
]
},
{
"refine_plan": [
{
"table": "test_complex_index2"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

set optimizer_trace="enabled=off";