数据库监控与调优【十五】

#数据库监控与调优【十五】| 来源: 网络整理| 查看: 265

ORDER BY语句优化最好的做法：利用索引避免排序实验

目的：哪些情况下ORDER BY子句能用索引避免排序，哪些情况下不能

在这里插入图片描述

之前说过，B+Tree数据结构里面的关键字，也就是索引值都是按照顺序排列的，那么，当索引的顺序正好和ORDER BY子句所需要的顺序一致时，mysql就不需要针对ORDER BY子句专门作排序，只需要直接按照索引的顺序取数据，然后返回即可。这就是上面说最好的做法是利用索引避免排序

即利用索引本身的有序性，让MySQL跳过排序过程

使用employees表测试

添加索引

ALTER TABLE employees ADD INDEX employees_first_name_last_name_index ( first_name, last_name ); 示例一 EXPLAIN SELECT * FROM employees ORDER BY first_name, last_name;

在这里插入图片描述

可以看到结果中type是ALL，代表全表扫描，性能最差。

再次测试

EXPLAIN SELECT * FROM employees ORDER BY first_name, last_name LIMIT 10;

在这里插入图片描述

可以看到结果中type是index，使用了employees_first_name_last_name_index这个索引

提问：为什么一会是ALL一会是index

解答：因为一开始的sql相当于把整张表进行排序，而mysql的优化器是基于成本计算的，当它发现，如果全表扫描的开销比使用索引开销性能更小，那么就使用全表扫描

另外，如果Extra字段是NULL，代表这条sql可以使用索引避免排序；但是如果Extra字段是Using filesort，代表这条sql不可以使用索引避免排序

所以，上述之前的sql无法使用索引避免排序，之后的sql是可以的

示例二 -- 查询条件是组合索引的第一个字段，ORDER BY是组合索引的第二个字段 EXPLAIN SELECT * FROM employees WHERE first_name = 'Bader' ORDER BY last_name;

在这里插入图片描述

可以看到结果中type是ref，使用了索引，Extra字段是NULL，可以使用索引避免排序

分析：首先，使用WHERE first_name = 'Bader'扫描索引，赛选出来一堆符合条件的索引，大概是下面这样的

[Bader, last_name1, emp_no] [Bader, last_name2, emp_no] [Bader, last_name3, emp_no] [Bader, last_name4, emp_no] [Bader, last_name5, emp_no] ...

索引本身是有顺序的，对于字符串，会按照字典排序，WHERE first_name = 'Bader'这个条件指定了first_name，所以以上的一堆结果就相当于按照last_name作字典排序，自然是可以利用索引避免掉排序。

示例三 EXPLAIN SELECT * FROM employees WHERE first_name 'Peng' ORDER BY last_name;

在这里插入图片描述

可以看到结果中type是range，表示范围查询且使用了索引，Extra字段是Using index condition，可以使用索引避免排序

示例五 EXPLAIN SELECT * FROM employees ORDER BY first_name, emp_no;

在这里插入图片描述

可以看到结果中type是ALL，发生了全表扫描

难道也是因为mysql基于成本计算所以使用全表扫描觉得性能更好吗？

修改为：

EXPLAIN SELECT * FROM employees ORDER BY first_name, emp_no LIMIT 10;

在这里插入图片描述

发现结果中的type依然是ALL，并且Extra是Using filesort，也就是说这条sql无法利用索引避免排序

原因：当排序字段存在于多个索引中是无法使用索引避免排序

- first_name => 存在索引employees_first_name_last_name_index ( first_name, last_name ) - emp_no => 主键索引示例六 EXPLAIN SELECT * FROM employees ORDER BY first_name DESC, last_name ASC LIMIT 10;

在这里插入图片描述

发现结果中的type依然是ALL，并且Extra是Using filesort，也就是说这条sql无法利用索引避免排序

原因：升降序不一致是无法使用索引避免排序

示例七 EXPLAIN SELECT * FROM employees WHERE first_name file1 -- [(5555,'Hanmeimei'),(9999,'Jim'),(7777,'Lucy')] => file2 -- 最后经过归并排序算法后的结果，如下 -- [(10001,'Annel'),(5555,'Hanmeimei'),(9999,'Jim'),(8888,'Keeper'),(7777,'Lucy'),(100001,'Zaker')]

5、循环执行上述过程，直到所有满足条件的记录全部参与排序

6、扫描排好序的(id,order_column)对，并利用id去取SELECT需要返回的其他字段

7、返回结果集

rowid排序特点看sort buffer是否能存放结果集里面的所有(id,order_column)，如果不满足，就会产生临时文件一次排序需要两次IO 第二步：把(id,order_column)扔到sort buffer；第六步：通过id去获取需要返回的其它字段。由于返回结果是按照order_column排序的，所以id是乱序的，会存在随机IO的问题。MySQL内部针对这种情况做了个优化，在用ID取数据之前，会按照ID排序并放到一个缓存里面，这个缓存大小由read_rnd_buffer_size控制，接着再去取记录，从而把随机IO转换为顺序IO 排序模式2-全字段排序（优化排序）直接取出SQL中需要的所有字段，放到sort buffer由于sort buffer已经包含了查询需要的所有字段，因此，在sort buffer中排序完成后可直接返回全字段排序 VS rowid排序好处：性能的提升，无需两次IO缺点：一行数据占用的空间一般比rowid排序多；如果sort buffer比较小，容易导致临时文件算法如何选择？ max_length_for_sort_data：当ORDER BY SQL中出现字段的总长度小于该值，使用全字段排序，否则使用rowid排序排序模式3-打包字段排序 MySQL 5.7引入全字段模式的优化，工作原理一样，但是将字段紧密地排列在一起，而不是使用固定长度空间某个字段数据类型是varchar(255)，值是“yes”：如果使用全字段排序，不打包情况下需要255字节；如果使用打包字段排序，需要2（存储字段长度）+3（存储"yes"字符串本身）字节。可以节省大量空间参数汇总

在这里插入图片描述

提出疑问

MySQL的排序实现细节特别多，但是使用explain只会在Extra中展示Using filesort，如果想要看更多的细节，可以使用OPTIMIZER_TRACE

-- 开启OPTIMIZER_TRACE SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; SET optimizer_trace_offset=-30, optimizer_trace_limit=30; -- 执行SQL SELECT * FROM employees WHERE first_name "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`emp_no` AS `emp_no`,`employees`.`birth_date` AS `birth_date`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name`,`employees`.`gender` AS `gender`,`employees`.`hire_date` AS `hire_date`,`employees`.`first_name_hash` AS `first_name_hash` from `employees` where (`employees`.`first_name` < 'Bader') order by `employees`.`last_name`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`employees`.`first_name` < 'Bader')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`first_name` < 'Bader')" } ] /* 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": 292025, "cost": 29436.8 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "employees_first_name_last_name_index", "usable": true, "key_parts": [ "first_name", "last_name", "emp_no" ] /* 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": "employees_first_name_last_name_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "employees_first_name_last_name_index", "ranges": [ "first_name < 'Bader'" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 0.073913, "rows": 45208, "cost": 15823.1, "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": "employees_first_name_last_name_index", "rows": 45208, "ranges": [ "first_name < 'Bader'" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 45208, "cost_for_plan": 15823.1, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 45208, "access_type": "range", "range_details": { "used_index": "employees_first_name_last_name_index" } /* range_details */, "resulting_rows": 45208, "cost": 20343.9, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 45208, "cost_for_plan": 20343.9, "sort_cost": 45208, "new_cost_for_plan": 65551.9, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`first_name` < 'Bader')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`first_name` < 'Bader')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`employees`.`last_name`", "items": [ { "item": "`employees`.`last_name`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`last_name`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "employees_first_name_last_name_index", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`employees`", "original_table_condition": "(`employees`.`first_name` < 'Bader')", "final_table_condition ": "(`employees`.`first_name` < 'Bader')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`employees`", "pushed_index_condition": "(`employees`.`first_name` < 'Bader')", "table_condition_attached": null } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_sort_to_table": "employees" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "employees", "filesort_information": [ { "direction": "asc", "expression": "`employees`.`last_name`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 264, "row_size": 406, "max_rows_per_buffer": 645, "num_rows_estimate": 45208, "num_rows_found": 22287, "num_initial_chunks_spilled_to_disk": 6, "peak_memory_used": 262144, "sort_algorithm": "std::sort", "sort_mode": "" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ }

可以看到sql使用filesort会多了以下内容，主要关注filesort_summary

"filesort_information": [ { "direction": "asc", "expression": "`employees`.`last_name`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 264, "row_size": 406, "max_rows_per_buffer": 645, "num_rows_estimate": 45208, "num_rows_found": 22287, "num_initial_chunks_spilled_to_disk": 6, "peak_memory_used": 262144, "sort_algorithm": "std::sort", "sort_mode": "" } /* filesort_summary */ filesort_summary解读 memory_available：可用内存，其实就是sort_buffer_size配置的值num_rows_found：有多少条数据参与排序，越小越好num_initial_chunks_spilled_to_disk：产生了几个临时文件，0代表完全基于内存排序应该让这个值尽量取向于0，内存排序肯定比文件排序效率高降低这个值也可以降低归并排序的次数提高效率 sort_mode ：使用rowid排序模式：使用了全字段排序：使用了打包字段排序如何调优ORDER BY 利用索引，防止filesort的发生如果发生了filesort，并且没办法避免，想办法优化filesort 如何调优filesort

调大sort_buffer_size，减少/避免临时文件、归并操作

OPTIMIZER_TRACE中num_initial_chunks_spilled_to_disk的值

sort_merge_passes变量的值，表示执行归并的次数

-- 通过这个sql查看 SHOW STATUS LIKE '%sort_merge_passes%';

调大read_rnd_buffer_size，让一次顺序IO返回的结果更多

设置合理的max_length_for_sort_data的值

这个参数如果设置太大，各种排序的SQL都会利用全字段排序，可能会导致大量内存占用，如果写入临时文件，又会占用大量硬盘

如果设置太小，会导致各种排序的SQL都会利用rowid排序，从而产生两次IO，性能会差很多

一般不建议随意调整

调小max_sort_length

这个参数的作用是指定排序时最多取多少字节比如超长的text类型或者varchar类型字段，就会取前这么多字节排序，忽略后面部分调优实战调优之前

以下sql为例

SELECT * FROM employees WHERE first_name "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`emp_no` AS `emp_no`,`employees`.`birth_date` AS `birth_date`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name`,`employees`.`gender` AS `gender`,`employees`.`hire_date` AS `hire_date`,`employees`.`first_name_hash` AS `first_name_hash` from `employees` where (`employees`.`first_name` < 'Bader') order by `employees`.`last_name`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`employees`.`first_name` < 'Bader')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`first_name` < 'Bader')" } ] /* 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": 292025, "cost": 29436.8 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "employees_first_name_last_name_index", "usable": true, "key_parts": [ "first_name", "last_name", "emp_no" ] /* 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": "employees_first_name_last_name_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "employees_first_name_last_name_index", "ranges": [ "first_name < 'Bader'" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 0.073913, "rows": 45208, "cost": 15823.1, "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": "employees_first_name_last_name_index", "rows": 45208, "ranges": [ "first_name < 'Bader'" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 45208, "cost_for_plan": 15823.1, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 45208, "access_type": "range", "range_details": { "used_index": "employees_first_name_last_name_index" } /* range_details */, "resulting_rows": 45208, "cost": 20343.9, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 45208, "cost_for_plan": 20343.9, "sort_cost": 45208, "new_cost_for_plan": 65551.9, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`first_name` < 'Bader')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`first_name` < 'Bader')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`employees`.`last_name`", "items": [ { "item": "`employees`.`last_name`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`last_name`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "employees_first_name_last_name_index", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`employees`", "original_table_condition": "(`employees`.`first_name` < 'Bader')", "final_table_condition ": "(`employees`.`first_name` < 'Bader')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`employees`", "pushed_index_condition": "(`employees`.`first_name` < 'Bader')", "table_condition_attached": null } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_sort_to_table": "employees" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "employees", "filesort_information": [ { "direction": "asc", "expression": "`employees`.`last_name`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 262144, "key_size": 264, "row_size": 406, "max_rows_per_buffer": 645, "num_rows_estimate": 45208, "num_rows_found": 22287, "num_initial_chunks_spilled_to_disk": 6, "peak_memory_used": 262144, "sort_algorithm": "std::sort", "sort_mode": "" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ }

可以看到num_initial_chunks_spilled_to_disk为6，产生了6个临时文件

调优之后

调大sort_buffer_size

SET sort_buffer_size = 1024 * 1024;

再次执行

SELECT * FROM employees WHERE first_name "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`emp_no` AS `emp_no`,`employees`.`birth_date` AS `birth_date`,`employees`.`first_name` AS `first_name`,`employees`.`last_name` AS `last_name`,`employees`.`gender` AS `gender`,`employees`.`hire_date` AS `hire_date`,`employees`.`first_name_hash` AS `first_name_hash` from `employees` where (`employees`.`first_name` < 'Bader') order by `employees`.`last_name`" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "(`employees`.`first_name` < 'Bader')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`first_name` < 'Bader')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`first_name` < 'Bader')" } ] /* 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": 292025, "cost": 29436.8 } /* table_scan */, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "employees_first_name_last_name_index", "usable": true, "key_parts": [ "first_name", "last_name", "emp_no" ] /* 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": "employees_first_name_last_name_index", "usable": false, "cause": "query_references_nonkey_column" } ] /* potential_skip_scan_indexes */ } /* skip_scan_range */, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "employees_first_name_last_name_index", "ranges": [ "first_name < 'Bader'" ] /* ranges */, "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "in_memory": 0.073913, "rows": 45208, "cost": 15823.1, "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": "employees_first_name_last_name_index", "rows": 45208, "ranges": [ "first_name < 'Bader'" ] /* ranges */ } /* range_access_plan */, "rows_for_plan": 45208, "cost_for_plan": 15823.1, "chosen": true } /* chosen_range_access_summary */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [ { "plan_prefix": [ ] /* plan_prefix */, "table": "`employees`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 45208, "access_type": "range", "range_details": { "used_index": "employees_first_name_last_name_index" } /* range_details */, "resulting_rows": 45208, "cost": 20343.9, "chosen": true, "use_tmp_table": true } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 45208, "cost_for_plan": 20343.9, "sort_cost": 45208, "new_cost_for_plan": 65551.9, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`employees`.`first_name` < 'Bader')", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`employees`", "attached": "(`employees`.`first_name` < 'Bader')" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`employees`.`last_name`", "items": [ { "item": "`employees`.`last_name`" } ] /* items */, "resulting_clause_is_simple": true, "resulting_clause": "`employees`.`last_name`" } /* simplifying_order_by */ } /* optimizing_distinct_group_by_order_by */ }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ ] /* steps */, "index_order_summary": { "table": "`employees`", "index_provides_order": false, "order_direction": "undefined", "index": "employees_first_name_last_name_index", "plan_changed": false } /* index_order_summary */ } /* reconsidering_access_paths_for_index_ordering */ }, { "finalizing_table_conditions": [ { "table": "`employees`", "original_table_condition": "(`employees`.`first_name` < 'Bader')", "final_table_condition ": "(`employees`.`first_name` < 'Bader')" } ] /* finalizing_table_conditions */ }, { "refine_plan": [ { "table": "`employees`", "pushed_index_condition": "(`employees`.`first_name` < 'Bader')", "table_condition_attached": null } ] /* refine_plan */ }, { "considering_tmp_tables": [ { "adding_sort_to_table": "employees" } /* filesort */ ] /* considering_tmp_tables */ } ] /* steps */ } /* join_optimization */ }, { "join_execution": { "select#": 1, "steps": [ { "sorting_table": "employees", "filesort_information": [ { "direction": "asc", "expression": "`employees`.`last_name`" } ] /* filesort_information */, "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" } /* filesort_priority_queue_optimization */, "filesort_execution": [ ] /* filesort_execution */, "filesort_summary": { "memory_available": 1048576, "key_size": 264, "row_size": 406, "max_rows_per_buffer": 2582, "num_rows_estimate": 45208, "num_rows_found": 22287, "num_initial_chunks_spilled_to_disk": 2, "peak_memory_used": 1048576, "sort_algorithm": "std::sort", "sort_mode": "" } /* filesort_summary */ } ] /* steps */ } /* join_execution */ } ] /* steps */ }

可以看到num_initial_chunks_spilled_to_disk为2，此时，只产生了2个临时文件。

【本文地址】

数据库监控与调优【十五】

数据库监控与调优【十五】

今日新闻

推荐新闻