一个有趣的案例
??我在本地建了两个表（表结构是一样的）user，user_copy，两个表都有一个联合索引idx_name_age_address。user表中共有10w条数据，user_copy中没有数据。
/*
Navicat MySQL Data Transfer
Source Server : MySQL
Source Server Version : 50714
Source Host : localhost:3306
Source Database : db
Target Server Type : MYSQL
Target Server Version : 50714
File Encoding : 65001
Date: 2022-05-13 08:07:06
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`address` varchar(20) NOT NULL DEFAULT '' COMMENT '地址',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_age_address` (`name`,`age`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100003 DEFAULT CHARSET=utf8 COMMENT='用户表';
部分数据如下：
下面我们先看几个测试：
测试1：相同表结构，测试走索引情况及扫描行数（user表10w条数据，user_copy表0条数据）
对比结论：上方两条SQL的区别是user表10w条数据，user_copy表没有数据。可以看出MySQL在范围查询时，如果第一个字段就用范围查找，会根据数据量大小来判断是否要走索引。第一个字段就范围查询，MySQL可能认为结果集很大就直接扫全表了。
测试2：强制走索引与不强制走索引，SQL执行时间
可以看到强制走索引可能查询的行数是5w多行，没有强制走索引的可能查询的行数是10w行，我们关闭查询缓存看看二者的执行时间：
‐‐ 关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;
‐‐ 执行时间0.1s
SELECT * FROM user WHERE name > 'linguo';
‐‐ 执行时间0.4s
SELECT * FROM user force index(idx_name_age_address) WHERE name > 'linguo';
对比结论：虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高
测试3: 数据量大小对in，or的影响
对比结论：in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
对比4：数据量，对比like ‘kk%’ 效果
对比结论：不论数据量多少，都会走索引，like ‘kk%’ 是范围查询，之所以索引生效是用到了索引下推。
?? 所谓索引下推是MySQL 5.6引入的，它可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数.
trace工具
??trace工具是MySQL自带的功能，它可以清楚的看到每条SQL执行时，MySQL计算出执行的cost成本。默认是不打开的，因为极其耗费性能。
我们来看这name > ‘a’ 这条sql的MySQL是如何选择的，下方大家只看cost字段，这是数字越大代表MySQL执行的成本越高。 下方有两个cost：
- 全表扫描：rows10666,cost 20368
- 走联合索引idx_name_age_address：rows50033，cost60041
虽然全表扫描可能扫描的行数比较多，但是mysql计算出来扫全表比较快，因此上图通过explain看到的结果是选择不走索引。当然这也不是决定的，比如当select的字段都是联合索引中的字段时，这条SQL就会走联合索引（即cost成本比全表低）
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`address` AS `address`,`user`.`create_time` AS `create_time` from `user` where (`user`.`name` > 'a') order by `user`.`address`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`user`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`user`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`user`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`user`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`user`",
"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": "`user`",
"range_analysis": {
"table_scan": {
"rows": 100066,
"cost": 20368
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_address",
"usable": true,
"key_parts": [
"name",
"age",
"address",
"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 */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name_age_address",
"ranges": [
"a < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 50033,
"cost": 60041,
"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": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100066,
"access_type": "scan",
"resulting_rows": 100066,
"cost": 20366,
"chosen": true,
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 100066,
"cost_for_plan": 20366,
"sort_cost": 100066,
"new_cost_for_plan": 120432,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`user`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "(`user`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`user`.`address`",
"items": [
{
"item": "`user`.`address`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`user`.`address`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`user`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`user`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`user`",
"field": "address"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 100002,
"examined_rows": 100002,
"number_of_tmp_files": 31,
"sort_buffer_size": 261960,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
这里我们再看下name>‘z’下，全表扫描与联合索引的成本。这里为什么使用’a’与’z’ ，原因B+树是有序的，英文字母是通过a~z这个顺序排序。这个时候尽管只是字母不一样，但是成本是截然不同的，name> ‘a’ 意外着扫全表，name> 'z’意味着查询的数据量很小。
看看两个cost：
- 全表扫描：rows10666,cost 20368
- 走联合索引idx_name_age_address：rows1，cost2.21
全表扫描的cost成本还是不变，但是联合索引的成本大大降低。cost2.21，奈斯。所以对于索引的理解在于对B+树的理解，在于你心里能不能生成一颗B+树。
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name`,`user`.`age` AS `age`,`user`.`address` AS `address`,`user`.`create_time` AS `create_time` from `user` where (`user`.`name` > 'z') order by `user`.`address`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`user`.`name` > 'z')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`user`.`name` > 'z')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`user`.`name` > 'z')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`user`.`name` > 'z')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`user`",
"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": "`user`",
"range_analysis": {
"table_scan": {
"rows": 100066,
"cost": 20368
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_address",
"usable": true,
"key_parts": [
"name",
"age",
"address",
"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 */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name_age_address",
"ranges": [
"z < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 1,
"cost": 2.21,
"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_name_age_address",
"rows": 1,
"ranges": [
"z < name"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 1,
"cost_for_plan": 2.21,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 1,
"access_type": "range",
"range_details": {
"used_index": "idx_name_age_address"
} /* range_details */,
"resulting_rows": 1,
"cost": 2.41,
"chosen": true,
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1,
"cost_for_plan": 2.41,
"sort_cost": 1,
"new_cost_for_plan": 3.41,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`user`.`name` > 'z')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "(`user`.`name` > 'z')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`user`.`address`",
"items": [
{
"item": "`user`.`address`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`user`.`address`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`user`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_name_age_address",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`user`",
"pushed_index_condition": "(`user`.`name` > 'z')",
"table_condition_attached": null
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`user`",
"field": "address"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 0,
"examined_rows": 0,
"number_of_tmp_files": 0,
"sort_buffer_size": 261960,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
