测试数据：
DDL
CREATE TABLE `company` (
`id` bigint NOT NULL AUTO_INCREMENT,
`company_name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '公司名字',
`status` smallint NOT NULL DEFAULT '0' COMMENT '公司状态；0：初始化；1：正常；2：禁用',
`contract_status` smallint NOT NULL DEFAULT '0' COMMENT '签约状态；0：待签约；1：已签约；2：签约到期',
`is_deleted` bigint NOT NULL DEFAULT '0' COMMENT '未删除0,删除id',
`create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`creator` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建人',
`update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_by` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`parent_id` tinyint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`could_modified` bit(1) NOT NULL DEFAULT b'0',
`company_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_nam` (`name`(4)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
DML
INSERT INTO `company` (`id`, `company_name`, `status`, `contract_status`, `is_deleted`, `create_at`, `creator`, `update_at`, `update_by`) VALUES (1, '哥哥', 1, 1, 0, '2021-09-17 19:31:00', '', '2023-02-24 17:46:37', '');
INSERT INTO `company` (`id`, `company_name`, `status`, `contract_status`, `is_deleted`, `create_at`, `creator`, `update_at`, `update_by`) VALUES (2, '1', 1, 0, 0, '2023-02-22 11:37:11', '', '2023-02-24 17:46:37', '');
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (1, '哥哥', 00123, '1,', b'0', 1);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (2, '1', 00011, '1,2', b'0', 2);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (3, '2', 00033, '1,2,3', b'0', 2);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (11, '3', 00000, '1,22', b'0', 1);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (12, '4', 00000, '1,22', b'0', 8);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (13, '5', 00000, '1,22', b'0', 8);
INSERT INTO `test` (`id`, `name`, `parent_id`, `code`, `could_modified`, `company_id`) VALUES (14, '5', 00000, '1,22', b'0', 8);
- EXPLAIN
EXPLAIN
insert into company(id,company_name,status,contract_status)
select aa.id,aa.name,aa.status,aa.contract_status
from (
select case when row_number() over (partition by b.id )=1 then b.id end as id ,a.name,b.status,b.contract_status
from test a
join company b on a.company_id = b.id
) aa
ON DUPLICATE KEY UPDATE company_name = aa.name;
2. EXPLAIN FORMAT=json
EXPLAIN FORMAT=json
insert into company(id,company_name,status,contract_status)
select aa.id,aa.name,aa.status,aa.contract_status
from (
select case when row_number() over (partition by b.id )=1 then b.id end as id ,a.name,b.status,b.contract_status
from test a
join company b on a.company_id = b.id
) aa
ON DUPLICATE KEY UPDATE company_name = aa.name;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.95"
},
"table": {
"insert": true,
"select_id": 1,
"table_name": "company",
"access_type": "ALL"
},
"insert_from": {
"buffer_result": {
"using_temporary_table": true,
"table": {
"table_name": "aa",
"access_type": "ALL",
"rows_examined_per_scan": 4,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.55",
"eval_cost": "0.40",
"prefix_cost": "2.95",
"data_read_per_join": "256"
},
"used_columns": [
"id",
"name",
"status",
"contract_status"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "7.70"
},
"windowing": {
"windows": [
{
"name": "<unnamed window>",
"using_filesort": true,
"filesort_key": [
"`id`"
],
"functions": [
"row_number"
]
}
],
"cost_info": {
"sort_cost": "4.00"
},
"buffer_result": {
"using_temporary_table": true,
"nested_loop": [
{
"table": {
"table_name": "b",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 4,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.40",
"prefix_cost": "0.65",
"data_read_per_join": "3K"
},
"used_columns": [
"id",
"status",
"contract_status"
]
}
},
{
"table": {
"table_name": "a",
"access_type": "ALL",
"rows_examined_per_scan": 7,
"rows_produced_per_join": 4,
"filtered": "14.29",
"using_join_buffer": "hash join",
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.40",
"prefix_cost": "3.70",
"data_read_per_join": "4K"
},
"used_columns": [
"id",
"name",
"company_id"
],
"attached_condition": "(`test`.`a`.`company_id` = `test`.`b`.`id`)"
}
}
]
}
}
}
}
}
}
}
}
}
- EXPlAIN FORMAT=TREE
EXPLAIN FORMAT=TREE
insert into company(id,company_name,status,contract_status)
select aa.id,aa.name,aa.status,aa.contract_status
from (
select case when row_number() over (partition by b.id )=1 then b.id end as id ,a.name,b.status,b.contract_status
from test a
join company b on a.company_id = b.id
) aa
ON DUPLICATE KEY UPDATE company_name = aa.name;
- EXPLAIN ANALYZE
EXPLAIN ANALYZE
insert into company(id,company_name,status,contract_status)
select aa.id,aa.name,aa.status,aa.contract_status
from (
select case when row_number() over (partition by b.id )=1 then b.id end as id ,a.name,b.status,b.contract_status
from test a
join company b on a.company_id = b.id
) aa
ON DUPLICATE KEY UPDATE company_name = aa.name;
- SHOW WARNINGS;
这个语句能够显示优化后的语句，有时这个语句是个奇效，多试试这个； - OPTIMIZER_TRACE
SET SESSION optimizer_trace='enabled=on';
explain insert into company(id,company_name,status,contract_status)
select aa.id,aa.name,aa.status,aa.contract_status
from (
select case when row_number() over (partition by b.id )=1 then b.id end as id ,a.name,b.status,b.contract_status
from test a
join company b on a.company_id = b.id
) aa
ON DUPLICATE KEY UPDATE company_name = aa.name;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET SESSION optimizer_trace='enabled=off';
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select (case when (row_number() OVER (PARTITION BY `b`.`id` ) = 1) then `b`.`id` end) AS `id`,`a`.`name` AS `name`,`b`.`status` AS `status`,`b`.`contract_status` AS `contract_status` from (`test` `a` join `company` `b` on((`a`.`company_id` = `b`.`id`)))"
}
]
}
},
{
"derived": {
"table": " `aa`",
"select#": 2,
"materialized": true
}
},
{
"expanded_query": "insert into `company` (`company`.`id`,`company`.`company_name`,`company`.`status`,`company`.`contract_status`) /* select#1 */ select sql_buffer_result `aa`.`id` AS `id`,`aa`.`name` AS `name`,`aa`.`status` AS `status`,`aa`.`contract_status` AS `contract_status` from (/* select#2 */ select (case when (row_number() OVER (PARTITION BY `b`.`id` ) = 1) then `b`.`id` end) AS `id`,`a`.`name` AS `name`,`b`.`status` AS `status`,`b`.`contract_status` AS `contract_status` from (`test` `a` join `company` `b` on((`a`.`company_id` = `b`.`id`)))) `aa` on duplicate key update `company`.`company_name` = `aa`.`name`"
}
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"JOIN_condition_to_WHERE",
"parenthesis_removal"
],
"expanded_query": "/* select#2 */ select (case when (row_number() OVER (PARTITION BY `b`.`id` ) = 1) then `b`.`id` end) AS `id`,`a`.`name` AS `name`,`b`.`status` AS `status`,`b`.`contract_status` AS `contract_status` from `test` `a` join `company` `b` where (`a`.`company_id` = `b`.`id`)"
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"join_optimization": {
"select#": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`a`.`company_id` = `b`.`id`)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(`a`.`company_id`, `b`.`id`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(`a`.`company_id`, `b`.`id`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(`a`.`company_id`, `b`.`id`)"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`test` `a`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`company` `b`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`company` `b`",
"field": "id",
"equals": "`a`.`company_id`",
"null_rejecting": true
}
]
},
{
"rows_estimation": [
{
"table": "`test` `a`",
"table_scan": {
"rows": 7,
"cost": 0.25
}
},
{
"table": "`company` `b`",
"table_scan": {
"rows": 4,
"cost": 0.25
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`test` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 7,
"filtering_effect": [
],
"final_filtering_effect": 1,
"access_type": "scan",
"resulting_rows": 7,
"cost": 0.95,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 7,
"cost_for_plan": 0.95,
"rest_of_plan": [
{
"plan_prefix": [
"`test` `a`"
],
"table": "`company` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 2.45,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 4,
"filtering_effect": [
],
"final_filtering_effect": 1,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 4,
"cost": 3.05033,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 7,
"cost_for_plan": 3.4,
"chosen": true,
"windowing_sort_cost": 7,
"new_cost_for_plan": 10.4
}
]
},
{
"plan_prefix": [
],
"table": "`company` `b`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 4,
"filtering_effect": [
],
"final_filtering_effect": 1,
"access_type": "scan",
"resulting_rows": 4,
"cost": 0.65,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 4,
"cost_for_plan": 0.65,
"rest_of_plan": [
{
"plan_prefix": [
"`company` `b`"
],
"table": "`test` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 7,
"filtering_effect": [
],
"final_filtering_effect": 1,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 7,
"cost": 3.05005,
"chosen": true
}
]
},
"condition_filtering_pct": 14.2857,
"rows_for_plan": 4,
"cost_for_plan": 3.70005,
"chosen": true,
"windowing_sort_cost": 4,
"new_cost_for_plan": 7.70005
}
]
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`a`.`company_id` = `b`.`id`)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`company` `b`",
"attached": null
},
{
"table": "`test` `a`",
"attached": "(`a`.`company_id` = `b`.`id`)"
}
]
}
},
{
"optimizing_distinct_group_by_order_by": {
}
},
{
"finalizing_table_conditions": [
{
"table": "`test` `a`",
"original_table_condition": "(`a`.`company_id` = `b`.`id`)",
"final_table_condition ": "(`a`.`company_id` = `b`.`id`)"
}
]
},
{
"refine_plan": [
{
"table": "`company` `b`"
},
{
"table": "`test` `a`"
}
]
},
{
"considering_tmp_tables": [
{
"adding_tmp_table_in_plan_at_position": 2,
"write_method": "write_all_rows"
},
{
"adding_tmp_table_in_plan_at_position": 3,
"cause": "output_for_window_functions",
"with_buffer": false,
"write_method": "write_all_rows",
"adding_sort_to_previous_table": {
"filesort": {
"adding_sort_to_table": ""
}
}
}
]
}
]
}
},
{
"table_dependencies": [
{
"table": " `aa`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"rows_estimation": [
{
"table": " `aa`",
"table_scan": {
"rows": 4,
"cost": 2.55
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": " `aa`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 4,
"access_type": "scan",
"resulting_rows": 4,
"cost": 2.95,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 4,
"cost_for_plan": 2.95,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": null,
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": " `aa`",
"attached": null
}
]
}
},
{
"finalizing_table_conditions": [
]
},
{
"refine_plan": [
{
"table": " `aa`"
}
]
},
{
"considering_tmp_tables": [
{
"adding_tmp_table_in_plan_at_position": 1,
"write_method": "write_all_rows"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"in_plan_at_position": 1,
"columns": 5,
"row_length": 94,
"key_length": 0,
"unique_constraint": false,
"makes_grouped_rows": false,
"cannot_insert_duplicates": false,
"location": "TempTable"
}
}
},
{
"materialize": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": " `aa`",
"in_plan_at_position": 0,
"columns": 4,
"row_length": 53,
"key_length": 0,
"unique_constraint": false,
"makes_grouped_rows": false,
"cannot_insert_duplicates": false,
"location": "TempTable"
}
}
},
{
"materialize": {
"select#": 2,
"steps": [
{
"sorting_table": "<temporary>",
"filesort_information": [
{
"direction": "asc",
"expression": "`b`.`id`"
}
],
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [
],
"filesort_summary": {
"memory_available": 262144,
"key_size": 8,
"row_size": 73,
"max_rows_per_buffer": 15,
"num_rows_estimate": 15,
"num_rows_found": 4,
"num_initial_chunks_spilled_to_disk": 0,
"peak_memory_used": 32800,
"sort_algorithm": "std::stable_sort",
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
}
}
]
}
}
]
}
}
]
}
}
]
}
- 查看最近死锁日志：
SHOW ENGINE INNODB STATUS;
1215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
