[TOC]
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time)
VALUES
('LiLei',22,'manager',NOW()),
('HanMeimei', 23,'dev',NOW()),
('Lucy',23,'dev',NOW());
-- 插入一些示例数据
drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();-- 联合索引第一个字段就用范围查找，不会走索引
-- MySQL 可能认为结果集很大，二级索引还要回表，效率可能不如全表扫描。
explain select * from employees where name > 'LiLei' and age = 22 and position = 'manager';
-- 覆盖索引优化
explain select name, age, position from employees where name > 'LiLei' and age = 22 and position = 'manager';
-- in 和 or 在数据量大的时候走索引，数据量小的时候全表扫描
explain select * from employees where name in ('LiLei', 'HanMeimei') and age = 22 and postion = 'manager';
-- 右模糊 一般都会走索引
explain select * from employees where name like 'LiLei%' and age = 22 and position = 'manager';
# 上述 SQL 按 最左前缀原则，只会走 name 索引，
# 因为 name 索引过滤完，得到的索引行里的 age 和 position 是无序的。
# 在 MySQL5.6 之后，引入 索引下推 优化
# 在 索引遍历过程 中， 对 索引中包含的所有字段 先做判断，
# 过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
# 所以，上述 SQL 中，name 索引过滤时，也会 过滤 age 和 position 条件。
# 索引下推 只适用于 二级索引，因为 主键索引 叶子节点就是 行数据
# 所以当出现 联合主键 索引的时候，索引下推失效。# MySQL 中 优化器 根据 成本分析决定是否使用索引、使用哪个索引，-- 使用 mysql trace 工具
-- 开启 trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name > 'a' order by position;
select * from information_schema.OPTIMIZER_TRACE;// 自己表测试的
{
"steps": [
{
"join_preparation": { // 第一阶段: SQL 准备阶段，格式化 SQL
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `sys_region`.`id` AS `id`,`sys_region`.`code` AS `code`,`sys_region`.`name` AS `name`,`sys_region`.`level` AS `level`,`sys_region`.`parent_id` AS `parent_id`,`sys_region`.`pinyin` AS `pinyin` from `sys_region` where ((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area') and (`sys_region`.`parent_id` = 2))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { // 第二阶段: SQL 优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { // 条件处理
"condition": "WHERE",
"original_condition": "((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area') and (`sys_region`.`parent_id` = 2))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area') and multiple equal(2, `sys_region`.`parent_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area') and multiple equal(2, `sys_region`.`parent_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area') and multiple equal(2, `sys_region`.`parent_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ // 表依赖详情
{
"table": "`sys_region`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`sys_region`",
"field": "parent_id",
"equals": "2",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ // 预估表的访问成本
{
"table": "`sys_region`",
"range_analysis": {
"table_scan": { // 全表扫描情况
"rows": 3368, // 扫描行数
"cost": 342.4 // 查询成本
} /* table_scan */,
"potential_range_indexes": [ // 查询可能使用的索引
{
"index": "PRIMARY", // 主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "uk-code_level", // 辅助索引
"usable": true,
"key_parts": [
"code",
"level"
] /* key_parts */
},
{
"index": "idx-parent_id",
"usable": true,
"key_parts": [
"parent_id",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "uk-code_level",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx-parent_id",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": { // 分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "uk-code_level",
"ranges": [
"110\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t <= code <= 110�����������������"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 18,
"cost": 6.56,
"chosen": true
},
{
"index": "idx-parent_id",
"ranges": [
"2 <= parent_id <= 2" // 索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true, // 使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, // 是否使用覆盖索引
"rows": 16, // 索引扫描行数
"cost": 5.86, // 索引使用成本
"chosen": true // 是否选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx-parent_id",
"rows": 16,
"ranges": [
"2 <= parent_id <= 2"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 16,
"cost_for_plan": 5.86,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`sys_region`",
"best_access_path": { // 最佳访问路径
"considered_access_paths": [ // 最终选择的访问路径
{
"access_type": "ref", // 访问类型，ref 命中缓存
"index": "idx-parent_id",
"rows": 16,
"cost": 5.6,
"chosen": true // 确定选择
},
{
"access_type": "range",
"range_details": {
"used_index": "idx-parent_id"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 16,
"cost_for_plan": 5.6,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`sys_region`.`parent_id` = 2) and (`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area'))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`sys_region`",
"attached": "((`sys_region`.`parent_id` = 2) and (`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area'))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`sys_region`",
"original_table_condition": "((`sys_region`.`parent_id` = 2) and (`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area'))",
"final_table_condition ": "((`sys_region`.`code` like '110%') and (`sys_region`.`level` = 'area'))"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`sys_region`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { // 第三阶段: SQL 执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}# MySQL 支持两种排序方式:
# filesort: 文件排序（磁盘排序），效率低
# index: 索引排序，效率高
# index:
# SQL 语句符合 最左前缀，即使用 索引排序
# group by 与 order by 类似，实质是 先排序后分组，
# group by 如果不需要排序，可以加上 order by null 禁止排序# 一次性取出满足条件行的所有字段，在内存 sort buffer 中进行排序。
# trace 工具可以看到:
"sort_mode": "<sort_key, packed_additional_fields>"# 又叫 回表排序
# 首先 根据条件 取出 相应排序字段，直接定位 行数据的行id，
# 然后在 sort buffer 中进行排序，
# 排完序后，需要再次回表取出其余所需字段。
# trace 工具可以看到:
"sort_mode": "<sort_key, rowid>"# MySQL 通过 比较 系统变量 max_length_for_sort_data (默认1024字节，即1kb)
# 和 查询字段总大小 来判断使用哪种排序模式。
# 如果 查询字段总大小 < max_length_for_sort_data ,使用单路排序模式
# 如果 查询字段总大小 > max_length_for_sort_data ,使用双路排序模式
# 说白了就是 MySQL 给你维护了一块内存，但是你这查出来的数据也不能太大了（超过上面的变量值）
# 你 SQL 没有使用到索引，必然是 filesort（磁盘排序），
# 你 SQL 查出来的数据总大小 别太大，就给你搞到内存排序，
# 反之就只能 在文件里排序，效率自然低。-- 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
select * from employees where name = 'zhuge' order by position;
select * from information_schema.OPTIMIZER_TRACE;