我的mysql server 版本为5.7 commutity 版本,低于该版本的trace 参数可能不存在(5.6.x 之后)!
mysql> EXPLAIN select * from employees where name > 'a';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 100 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set
mysql> EXPLAIN select * from employees where name > 'zzz' ;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 1 | 100 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected
mysql> select * from employees where name > 'a' order by position;
+----+-----------+-----+----------+---------------------+
| id | name | age | position | hire_time |
+----+-----------+-----+----------+---------------------+
| 2 | HanMeimei | 23 | dev | 2020-03-14 16:45:53 |
| 3 | Lucy | 23 | dev | 2020-03-14 16:45:53 |
| 1 | LiLei | 22 | manager | 2020-03-14 16:45:53 |
+----+-----------+-----+----------+---------------------+
3 rows in set
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
step1:SQL准备阶段
step2:SQL 优化阶段
steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
3.表依赖阶段:
"table_dependencies": [
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
4.预估表的访问成本,
rows_estimation": [
{
"table": "`employees`",
"range_analysis": {
"table_scan": {--全表扫描
"rows": 3,--扫描行
"cost": 3.7--查询成本
} /* table_scan */,
5.可能使用到的索引
potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", --辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name"
] /* ranges */, --索引使用范围
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,‐‐使用该索引获取的记录是否按照主键排序
"index_only": false, , ‐‐是否使用覆盖索引
"rows": 3,
"cost": 4.61,
"chosen": false,‐‐是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
7.
table": "`employees`",
"best_access_path": {‐‐最优访问路径
"considered_access_paths": [‐‐最终选择的访问路径
{
"rows_to_scan": 3,
"access_type": "scan", ‐‐访问类型:为scan,全表扫描
"resulting_rows": 3,
"cost": 1.6,
"chosen": true, ‐‐确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 3,
"cost_for_plan": 1.6,
"sort_cost": 3,
"new_cost_for_plan": 4.6,
"chosen": true
8.SQL执行阶段
join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 3,
"examined_rows": 3,
"number_of_tmp_files": 0,
"sort_buffer_size": 200704,
"sort_mode": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
同理, mysql> select * from employees where name > 'zzz' order by position;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set
mysql> explain select * from employees where name='LiLei'
order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set
mysql> explain select * from employees where name='LiLei' and age=18 order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
1 row in set
mysql> explain select * from employees where name='xxx' order by age asc,position desc;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set
mysql> explain select * from employees where name in('LiLei','xxx') order by age,position;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 66.67 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
1 row in set
对于排序来说,多个相等条件也是范围查询
mysql> explain select * from employees where name >'a' order by name;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 100 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
1 row in set
用了filesort ,使用覆盖索引优化:
mysql> explain select name,age,position from employees where name >'a' order by name;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | index | idx_name_age_position | idx_name_age_position | 140 | NULL | 3 | 100 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set
优化总结: