select * from dubbo_invoke2 where application != 'experts-web';
EXPLAIN SELECT application FROM dubbo_invoke2 WHERE application != 'experts-web';
查看其执行计划如下：
set optimizer_trace='enabled=on';set optimizer_trace_max_mem_size=1000000;set end_markers_in_json=on;
SELECT * FROM dubbo_invoke2 WHERE application != 'experts-web';select * FROM information_schema.optimizer_trace;
{"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `dubbo_invoke2`.`uu_id` AS `uu_id`,`dubbo_invoke2`.`application` AS `application`,`dubbo_invoke2`.`app_type` AS `app_type`,`dubbo_invoke2`.`service` AS `service`,`dubbo_invoke2`.`method` AS `method`,`dubbo_invoke2`.`consumer_host` AS `consumer_host`,`dubbo_invoke2`.`consumer_port` AS `consumer_port`,`dubbo_invoke2`.`provider_host` AS `provider_host`,`dubbo_invoke2`.`provider_port` AS `provider_port`,`dubbo_invoke2`.`success` AS `success`,`dubbo_invoke2`.`failure` AS `failure`,`dubbo_invoke2`.`elapsed` AS `elapsed`,`dubbo_invoke2`.`concurrent` AS `concurrent`,`dubbo_invoke2`.`max_elapsed` AS `max_elapsed`,`dubbo_invoke2`.`max_concurrent` AS `max_concurrent`,`dubbo_invoke2`.`invoke_date` AS `invoke_date`,`dubbo_invoke2`.`invoke_hour` AS `invoke_hour`,`dubbo_invoke2`.`invoke_time` AS `invoke_time`,`dubbo_invoke2`.`gmt_create` AS `gmt_create` from `dubbo_invoke2` where (`dubbo_invoke2`.`application` <> 'experts-web')"}] /* steps */} /* join_preparation */},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`dubbo_invoke2`.`application` <> 'experts-web')","steps": [{"transformation": "equality_propagation","resulting_condition": "(`dubbo_invoke2`.`application` <> 'experts-web')"},{"transformation": "constant_propagation","resulting_condition": "(`dubbo_invoke2`.`application` <> 'experts-web')"},{"transformation": "trivial_condition_removal","resulting_condition": "(`dubbo_invoke2`.`application` <> 'experts-web')"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": {} /* substitute_generated_columns */},{"table_dependencies": [{"table": "`dubbo_invoke2`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{"ref_optimizer_key_uses": [] /* ref_optimizer_key_uses */},{"rows_estimation": [ --预估访问成本{"table": "`dubbo_invoke2`","range_analysis": {"table_scan": { --全表扫描情况"rows": 71159, --扫描行数"cost": 15359 --查询成本} /* table_scan */,"potential_range_indexes": [ --查询可能使用的索引{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "idx_method","usable": false,"cause": "not_applicable"},{"index": "idx_application","usable": true,"key_parts": ["application","uu_id"] /* key_parts */},{"index": "idx_service","usable": false,"cause": "not_applicable"},{"index": "idx_type","usable": false,"cause": "not_applicable"}] /* potential_range_indexes */,"setup_range_conditions": [] /* setup_range_conditions */,"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"} /* group_index_range */,"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "idx_application","ranges": ["application < experts-web","experts-web < application"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 35922,"cost": 43108,"chosen": false,"cause": "cost"}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [{"plan_prefix": [] /* plan_prefix */,"table": "`dubbo_invoke2`","best_access_path": { --最优访问路径"considered_access_paths": [{"rows_to_scan": 71159,"access_type": "scan", --全表扫描"resulting_rows": 71159,"cost": 15357,"chosen": true --是否选择}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 71159,"cost_for_plan": 15357,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": "(`dubbo_invoke2`.`application` <> 'experts-web')","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`dubbo_invoke2`","attached": "(`dubbo_invoke2`.`application` <> 'experts-web')"}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"refine_plan": [{"table": "`dubbo_invoke2`"}] /* refine_plan */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */}
SELECT * FROM dubbo_invoke2 WHERE application = 'experts-web';select * FROM information_schema.optimizer_trace;
{"steps": [{"join_preparation": {"select#": 1,"steps": [{"expanded_query": "/* select#1 */ select `dubbo_invoke2`.`uu_id` AS `uu_id`,`dubbo_invoke2`.`application` AS `application`,`dubbo_invoke2`.`app_type` AS `app_type`,`dubbo_invoke2`.`service` AS `service`,`dubbo_invoke2`.`method` AS `method`,`dubbo_invoke2`.`consumer_host` AS `consumer_host`,`dubbo_invoke2`.`consumer_port` AS `consumer_port`,`dubbo_invoke2`.`provider_host` AS `provider_host`,`dubbo_invoke2`.`provider_port` AS `provider_port`,`dubbo_invoke2`.`success` AS `success`,`dubbo_invoke2`.`failure` AS `failure`,`dubbo_invoke2`.`elapsed` AS `elapsed`,`dubbo_invoke2`.`concurrent` AS `concurrent`,`dubbo_invoke2`.`max_elapsed` AS `max_elapsed`,`dubbo_invoke2`.`max_concurrent` AS `max_concurrent`,`dubbo_invoke2`.`invoke_date` AS `invoke_date`,`dubbo_invoke2`.`invoke_hour` AS `invoke_hour`,`dubbo_invoke2`.`invoke_time` AS `invoke_time`,`dubbo_invoke2`.`gmt_create` AS `gmt_create` from `dubbo_invoke2` where (`dubbo_invoke2`.`application` = 'experts-web')"}] /* steps */} /* join_preparation */},{"join_optimization": {"select#": 1,"steps": [{"condition_processing": {"condition": "WHERE","original_condition": "(`dubbo_invoke2`.`application` = 'experts-web')","steps": [{"transformation": "equality_propagation","resulting_condition": "(`dubbo_invoke2`.`application` = 'experts-web')"},{"transformation": "constant_propagation","resulting_condition": "(`dubbo_invoke2`.`application` = 'experts-web')"},{"transformation": "trivial_condition_removal","resulting_condition": "(`dubbo_invoke2`.`application` = 'experts-web')"}] /* steps */} /* condition_processing */},{"substitute_generated_columns": {} /* substitute_generated_columns */},{"table_dependencies": [{"table": "`dubbo_invoke2`","row_may_be_null": false,"map_bit": 0,"depends_on_map_bits": [] /* depends_on_map_bits */}] /* table_dependencies */},{"ref_optimizer_key_uses": [{"table": "`dubbo_invoke2`","field": "application","equals": "'experts-web'","null_rejecting": false}] /* ref_optimizer_key_uses */},{"rows_estimation": [{"table": "`dubbo_invoke2`","range_analysis": {"table_scan": {"rows": 71159,"cost": 15359} /* table_scan */,"potential_range_indexes": [{"index": "PRIMARY","usable": false,"cause": "not_applicable"},{"index": "idx_method","usable": false,"cause": "not_applicable"},{"index": "idx_application","usable": true,"key_parts": ["application","uu_id"] /* key_parts */},{"index": "idx_service","usable": false,"cause": "not_applicable"},{"index": "idx_type","usable": false,"cause": "not_applicable"}] /* potential_range_indexes */,"setup_range_conditions": [] /* setup_range_conditions */,"group_index_range": {"chosen": false,"cause": "not_group_by_or_distinct"} /* group_index_range */,"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "idx_application","ranges": ["experts-web <= application <= experts-web"] /* ranges */,"index_dives_for_eq_ranges": true,"rowid_ordered": true,"using_mrr": false,"index_only": false,"rows": 35579,"cost": 42696,"chosen": false,"cause": "cost"}] /* range_scan_alternatives */,"analyzing_roworder_intersect": {"usable": false,"cause": "too_few_roworder_scans"} /* analyzing_roworder_intersect */} /* analyzing_range_alternatives */} /* range_analysis */}] /* rows_estimation */},{"considered_execution_plans": [{"plan_prefix": [] /* plan_prefix */,"table": "`dubbo_invoke2`","best_access_path": { --最优访问路径"considered_access_paths": [{"access_type": "ref","index": "idx_application", -- 索引"rows": 35579,"cost": 10491,"chosen": true -- 是否选中},{"rows_to_scan": 71159,"access_type": "scan","resulting_rows": 71159,"cost": 15357,"chosen": false}] /* considered_access_paths */} /* best_access_path */,"condition_filtering_pct": 100,"rows_for_plan": 35579,"cost_for_plan": 10491,"chosen": true}] /* considered_execution_plans */},{"attaching_conditions_to_tables": {"original_condition": "(`dubbo_invoke2`.`application` = 'experts-web')","attached_conditions_computation": [] /* attached_conditions_computation */,"attached_conditions_summary": [{"table": "`dubbo_invoke2`","attached": null}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{"refine_plan": [{"table": "`dubbo_invoke2`"}] /* refine_plan */}] /* steps */} /* join_optimization */},{"join_execution": {"select#": 1,"steps": [] /* steps */} /* join_execution */}] /* steps */}
EXPLAIN SELECT * FROM dubbo_invoke2 WHERE application = 'experts-web' AND service = 'a' AND method = 'b';
文章转载自编程阁楼，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
