1,mysql索引性能优化最佳实战
2， 使用索引查询如何避免回表查询
3，为什么查询有时候加了索引也会失效？
4，如何使用 optimizer_trace 分析sql语句
5，order by 排序优化原则有哪些？
6 mysql排序支持2种 filesort 和index 的区别
7，Mysqlfilesort 排序底层实现原理
8， 单路与双路（回表）排序底层原理。
9， 为什么阿里巴巴手册不推荐使用存储过程？
Using index： 查询的列都是加上索引，不需要回表查询。
什么是回表查询：
select * from table where name='bob'
底层先查询username索引文件查找对应的username 'bob'的主键id
在根据主键id 查询主键索引文件 对应行数据。
如果全表扫描查询效率比索引 查询要高，则使用全表扫描。
image.png
如果用name索引查找数据需要遍历name字段联合索引树，然后根据遍历出来的主键值去主键索引树里再去查找最终的数据，成本比全表扫描还高。
可以覆盖索引优化，这样系需要遍历name字段的联合索引树就可以拿到所有的结果。
image.png
误区：
1， 所有的字段加上了索引 就一定使用索引查询
2，全表扫描查询是否一定会比索引查询慢。
排序的2种算法，
1， using filesort 索引失效 没有根据索引字段排序的情况
2, using index 根据索引的字段排序
可以看到通过select出的字段是覆盖索引，MySQL底层使用了索引优化。可以使用optimizer_trace 分析sql是否有走过索引：
optimizer_trace 分析sql 语句
如果全表查询效率比索引要高，则使用全表扫描。
image.png
如果用name 索引查找数据需要遍历name字段联合索引树，然后根据遍历出来的主键值再去查找最终的数据，成本比全表扫描要高。
可以使用覆盖索引，这样只需要遍历name的字段联合索引树就可以查找到结果：
image.png
SET optimizer_trace='enabled=on',end_markers_in_json=on;
SELECT * FROM employees WHERE name > 'mayikt' ;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
image.png
查询结果：
{
"steps": [
{
"join_preparation": { --第一阶段：SQl准备阶段
"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` > 'mayikt')"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'mayikt')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'mayikt')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'mayikt')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'mayikt')"
}
] /* 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": 68511, --扫描行数
"cost": 13929 --查询成本
} /* 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": [
"mayikt < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 34255, --‐‐索引扫描行数
"cost": 41107, --索引使用成本
"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": 68511,
"access_type": "scan",
"resulting_rows": 68511,
"cost": 13927,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 68511,
"cost_for_plan": 13927,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'mayikt')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'mayikt')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
全表扫描的成本低于索引扫描， 索引MySQL最终会选择全表扫描。
Order by 培训优化原则
1，.EXPLAIN SELECT * FROM employees WHERE name= 'meite' AND position ='ceo' order by age;
image.png
分析： 利用最左前缀法则： 中间字段不能断，因此查询到了name 索引
，从key_len=74 也能看出，age索引列用在排序过程中，因为Extra 字段里没有
using filesort 是为Using index condition
2,EXPLAIN SELECT * FROM employees WHERE name= 'meite' order by position;
image.png
从explain 的执行结果来看： key_len=74， 查询使用了name 索引，由于
用了position 进行排序，跳过了age，出现了Using filesort
3.EXPLAIN SELECT * FROM employees WHERE name= 'meite' order by age,position;
image.png
分析：
查找只用到索引 name， age 和position 用于排序。，无Using filesort.
4.EXPLAIN SELECT * FROM employees WHERE name= 'meite' order by position,age;
image.png
分析：
在 Extra中出现 Using Filesort, 因为age 为常量，在排序中被优化，所以索引未颠倒，会出现Using Filesort
5.
EXPLAIN SELECT * FROM employees WHERE name= 'meite' order by age asc ,position desc ;
image.png
分析： age字段采用升序排序，position 降序排序 导致索引的排序方式不同，从而产生 Using filesort
EXPLAIN SELECT * FROM employees WHERE name in('meite','xiaowei') order by age, position;
image.png
f
分析： 对于多个排序来说，多个相等条件也是范围查询。
7， EXPLAIN SELECT * FROM employees WHERE name>'meite' order by name;
image.png
可以优化为
EXPLAIN SELECT name,age,position FROM employees WHERE name>'meite' order by name;
排序优化总结
image.png
Mysql排序支持两种filesort和index
1.这种方式在使用explain分析时显示为using index，不需要额外的排序， 是指mysql扫描索引本身完成排序，操作效率较高
2.通过对返回数据进行排序，即filesort，所有不通过索引直接返回排序结果的排序都是filesort排序
Filesort实现原理：
filesort通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区中进行排序，如果内存装载不下，会将磁盘上的数据进行分块，再对各个数据块进行排序，再将各个块合并成有序的结果集。
order by满足两种情况会使用Using index。
- order by语句使用索引最左前列。
- 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
Using filesort 排序原理：
image.png
1,mysql有2种排序方法（双路和多路排序），如果需要排序的列的总大小加上order by列的大小超过了 max_length_for_sort_data 定义的字节（默认1024）,mysql 就会使用双路排序。当任何需要的列甚至不是order by的列(text.blob的时候)，也会使用双路排序，（可以使用substtring() 把这些列转化为可以单路排序的列）。
2，可以通过改变max_length_for_sort_data变量的值来影响mysql选择的算法。因为单路排序为将要排序的每一行创建了固定的缓冲区。varchar列的最大长度是 max_length_for_sort_data 规定的值，而不是排序数据的实际大小。
SHOW VARIABLES LIKE '%max_length_for_sort_data%';
SET max_length_for_sort_data = 1024
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
SELECT name,age,position FROM employees order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
3, 单路排序：满足排序的查询条件，将所有行的数据全部缓存到列sort buffer缓冲区中实现排序。用trace工具可以看到sort_mode信息里面显示sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >；
双路排序（回表查询）：满足排序查询条件，将排序的字段和直接定位于行数据的主键id，缓存sort buffer缓存区，中实现排序，排序好了在根据主键id获取其他字段的数据，相当于要查询2次，用trace工具可以看到sort_mode信息里面显示 < sort_key, rowid >；
4.Mysql可以通过 max_length_for_sort_data 默认是1024字节大小
A.如果查询字段总的长度大小比设定的max_length_for_sort_data 要小，则使用单路排序方式；
B.如果查询字段总的长度大小比设定的max_length_for_sort_data 要大，则使用多路排序方式
单路排序的步骤：
1，从 索引name字段中，查询到满足条件的数据name='meite'条件主键的id
2，根据主键id取出整行的数据，缓存到sort buffer中；
3，重复执行1,2步骤流程；
4，对sort buffer中的数据实现排序给客户端。
image.png
双路排序
1，从索引字段中，查询到满足条件的数据name='meite'条件主键的id
2，根据主键id,获取排序的字段和主键id缓存到sort buffer中。
3，重复执行1,2步骤流程。
4，对sort buffer 中的数据实现排序
5，根据排序好的主键id 和position，在从原来表中根据id查询数据给客户端。
image.png
总结：单路排序会将整行索引数据缓存到sort buffer中，双路排序只将主键id和排序字段放入到sort中进行排序，在根据排序好的数据，从原来表中根据id查询数据返回给客户端。
如何选项单路排序还是多路排序？
至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。
网友评论