mysql trace_Mysql Trace 工具

MySQL 优化

表

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=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

Trace 工具

mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

1. 开启Trace

set session optimizer_trace="enabled=on",end_markers_in_json=on;‐‐开启trace

select * from employees where name >'a' order by position;

sELECT * FROM information_schema.OPTIMIZER_TRACE;

执行这两句sql

select * from employees where name >'a' order by position;

sELECT * FROM information_schema.OPTIMIZER_TRACE;

提出来trace值

Title

{

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"

}

] /* steps */

} /* join_preparation */

},

{

"join_optimization": {

"select#": 1,

"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 */

},

{

"substitute_generated_columns": {

} /* substitute_generated_columns */

},

{

"table_dependencies": [

{

"table": "`employees`",

"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": "`employees`",

"range_analysis": {

"table_scan": {

"rows": 3,

"cost": 3.7

} /* table_scan */,

"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 */

}

] /* 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_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 */,

"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": "`employees`",

"best_access_path": {

"considered_access_paths": [

{

"rows_to_scan": 3,

"access_type": "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

}

] /* considered_execution_plans */

},

{

"attaching_conditions_to_tables": {

"original_condition": "(`employees`.`name` > 'a')",

"attached_conditions_computation": [

] /* attached_conditions_computation */,

"attached_conditions_summary": [

{

"table": "`employees`",

"attached": "(`employees`.`name` > 'a')"

}

] /* attached_conditions_summary */

} /* attaching_conditions_to_tables */

},

{

"clause_processing": {

"clause": "ORDER BY",

"original_clause": "`employees`.`position`",

"items": [

{

"item": "`employees`.`position`"

}

] /* items */,

"resulting_clause_is_simple": true,

"resulting_clause": "`employees`.`position`"

} /* clause_processing */

},

{

"reconsidering_access_paths_for_index_ordering": {

"clause": "ORDER BY",

"steps": [

] /* steps */,

"index_order_summary": {

"table": "`employees`",

"index_provides_order": false,

"order_direction": "undefined",

"index": "unknown",

"plan_changed": false

} /* index_order_summary */

} /* reconsidering_access_paths_for_index_ordering */

},

{

"refine_plan": [

{

"table": "`employees`"

}

] /* refine_plan */

}

] /* steps */

} /* join_optimization */

},

{

"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": ""

} /* filesort_summary */

}

] /* steps */

} /* join_execution */

}

] /* steps */

}

结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

SELECT * FROM information_schema.OPTIMIZER_TRACE;

查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描

set session optimizer_trace="enabled=off";‐‐关闭Trace

常见sql深入优化Order by与Group by优化Case1：分析：

order by 与 group by

主要关注 key 列 和 key_len 和Extra 的变化

1.

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' AND POSITION ='dev' ORDER BY age;

分析 ：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesortCase

2：

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' ORDER BY POSITION ;

分析：从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

3：

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' ORDER BY age,POSITION ;

分析：查找只用到索引name，age和position用于排序，无Using filesort

4：

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' ORDER BY POSITION,AGE ;

分析：和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

5：

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' AND AGE =18 ORDER BY POSITION ,age;

分析：与 4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

6：

EXPLAIN SELECT * FROM employees WHERE NAME ='LiLei' ORDER BY age asc,POSITION desc;

分析：虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

7：

EXPLAIN SELECT * FROM employees WHERE NAME in ('LiLei','Lee') ORDER BY age,POSITION ;

分析：对于排序来说，多个相等条件也是范围查询Case

8：

EXPLAIN SELECT * FROM employees WHERE NAME >'a' ORDER BY NAME ;

可以用覆盖索引优化

EXPLAIN SELECT NAME,AGE,POSITION FROM employees WHERE NAME >'a' ORDER BY NAME;

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于groupby的优化如果不需要排序的可以加上order by null禁止排序。

注意，where高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理

详解filesort文件排序方式

单路排序：

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >

双路排序(又叫回表排序模式)：

是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式；

如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式。

示例验证下各种排序方式：查看下这条sql对应trace结果如下(只展示排序部分)：