search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.96k stars 5.82k forks source link

请优化一下mysql弱成渣的sql 重写，orsql优化器，目前tidb优化器，sql查询功能和mysql一个档次 #56275

Closed lisun8523 closed 2 hours ago

lisun8523 commented 2 hours ago

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.期望ob 有 optimizer_trace 那样的工具分析sql解析、优化过程。 Describe alternatives you've considered

explain select from test_complex_index where message_id = 1360977 and chat_id = -1001554634644 limit 1; 第一条走了全表扫描。 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";

ti-chi-bot[bot] commented 2 hours ago

[FORMAT CHECKER NOTIFICATION]

:rightwards_hand: ${\color{gold}\Huge{\textsf{Please use english to create or update issue.}}}$