mysql2008 第11次 性能优化过程与分析
一.Sql语句的错误使用
问题：多次查询-循环
案例：查询粉丝最多的前十个用户的文章总数
粉丝关注表 user_fans
id,user_id,fans_id
文章表 article
id
title
content
user_id 作者
1.查询排名前10的用户
2.查询排名前10用户的文章数
select user_id from (select user_id,count(*) from user_fans group by user_id order by c dese limit,0,5) as fans_count
方案a 循环
for (fans_count)
select count(*) from article where user_id = ?;
方案b in
select count(*) from article where user_id in (fans_count);
方案c
select count(*) from article where user_id in (fans_count) group by user_id;
最优方案：b
a执行总次数1+10=11
b执行总次数1+1
c执行总次数1+1 多了多余的分组。
总次数=io次数 输入与输出
1.一分钟执行10000次 ，每次读写消耗5个 10000*5=5w =>优化=> 2w
2.一分钟执行100次 ，每次读写消耗3000个 100*3000=300000(30w)=>优化 =>二十万
sql语句的复杂度，可以决定的程序的性能问题
1.对于查询是否有过多的分组，排序，join
2.需要查询的表数量
3.是否使用了大量函数，如 ount sum AVG等
二.Sql语句优化之索引使用指标
mysql 的innodb引擎，是解析器根据 二分法 形成 平衡二叉树的b+tree 树形结构.形成有
二分法=》不断选择一个中位数，根据左小右大，=》形成有序的数据序列（叶子节点） 树形结构。
案例1
alter tabel user add index idx_name(name) 普通索引
这个普通索引形成的树形结构，存储的该索引（字符串数据转化为ascii 数字）及对应主键id数据
条件及所查询的结果数据列，不在索引中时，在查出索引后，还须根据索引对应的主键，找到其它非索引数据，这样就产生了回表，性能就是差。
案例2
业务初期：针对某一地区，比如长沙，用户所在地区长沙
中期扩展：增加武汉
统计功能=》可以根据地区来进行统计=》但武汉的用户很少很少，绝对大部分用户都是长沙。即使以地区建立索引，进行查询时，扫描的数据量太大，与不使用索引相关不大。
一般使用索引，在不是覆盖索引的情况下，建议索引的使用所扫描的数据量/指标,不要超过表数据量的30%；
多使用explain分析。
三.优化器执行过程与分析
案例：select * from customers1s where city="长沙" and gender=0;
show WARNINGS\G; //分析上面执行语句，底层的的重写
explain select * from customers1s where city="长沙" and gender=0; //分析语句
#开启trace查看优化器
set optimizer_trace="enabled=on";--开启trace查看优化器的结果
set end_markers_in_json=on;--增加注释
#优化的执行语句
select id,`name`,city,monthsalary,gender from customers1s where city=“长沙” and gender=0 and monthsalary=99;
#执行语句的优化执行过程打印
select * from information_schema.optimizer_trace \G;--查询打印执行计划
结果:各参数的意义
···
*************************** 1. row ***************************
QUERY: select id,`name`,city,monthsalary,gender from customers1s where city="长沙" and gender=0 and monthsalary=99
TRACE: {
"steps": [
{
##join_preparation展示了准备阶段的执行过程
"join_preparation": {
"select#": 1,
"steps": [
{
##重写查询语句，加库名称 列出字段及别名
"expanded_query": "/* select#1 */ select `customers1s`.`id` AS `id`,`customers1s`.`name` AS `name`,`customers1s`.`city` AS `city`,`customers1s`.`monthsalary` AS `monthsalary`,`customers1s`.`gender` AS `gender` from `customers1s` where ((`customers1s`.`city` = '长沙') and (`customers1s`.`gender` = 0) and (`customers1s`.`monthsalary` = 99))"
}
] /* steps */
} /* join_preparation */
},
{
##join_optimization：展示了优化阶段的执行过程，是分析OPTIMIZER TRACE的重点
"join_optimization": {
"select#": 1,
"steps": [
{
/*condition_processing：该段用来做条件处理，主要对WHERE条件进行优化处理。*/
"condition_processing": {
"condition": "WHERE", /*优化对象类型，如where,having */
#优化前的原始语句
"original_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`gender` = 0) and (`customers1s`.`monthsalary` = 99))",
#steps：主要包括三步， transformation：转换类型句 resulting_condition：转换之后的结果输出
"steps": [
{
/*等值条件 equality_propagation（等值条件句转换） */
"transformation": "equality_propagation",
"resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` = 99) and multiple equal(0, `customers1s`.`gender`))"
},
{
/*constant_propagation（常量条件句转换）无意义的条件，移除，如1=1 */
"transformation": "constant_propagation",
"resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` = 99) and multiple equal(0, `customers1s`.`gender`))"
},
{
/*trivial_condition_removal（无效条件移除的转换）条件字段数据类型转化 如 `gender` = '0' => `gender` = 0 */
"transformation": "trivial_condition_removal",
"resulting_condition": "((`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` = 99) and multiple equal(0, `customers1s`.`gender`))"
}
] /* steps */
} /* condition_processing */
},
{
#substitute_generated_columns用于替换虚拟生成列
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
/*table_dependencies：分析表之间的依赖关系*/
"table_dependencies": [
{
"table": "`customers1s`",#涉及的表名，如果有别名，也会展示出来
"row_may_be_null": false,#行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为 NULL。如果语句中使用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
"map_bit": 0,#表的映射编号，从0开始递增
#depends_on_map_bits：依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值。
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
#ref_optimizer_key_uses：列出所有可用的ref类型的索引。如果使用了组合索引的多个部分,（例 如本例，用到了index(from_date, to_date) 的多列索引），则会在ref_optimizer_key_uses下列 出多个元素，每个元素中会列出ref使用的索引及对应值.
"ref_optimizer_key_uses": [
{
"table": "`customers1s`",
"field": "gender",
"equals": "0",
"null_rejecting": false
},
{
"table": "`customers1s`",
"field": "city",
"equals": "'长沙'",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
#rows_estimation：顾名思义，用于估算需要扫描的记录数。
"rows_estimation": [
{
"table": "`customers1s`",#表名
"range_analysis": {
table_scan:如果全表扫描的话，需要扫描多少行（row，5073417），以及需要的代价（cost， 542989）
"table_scan": {
"rows": 5073417,#扫描行数
"cost": 542989 #性能
} /* table_scan */,
#可能使用到的索引.列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_gender_city_name_monthsalary_yearbonus",
"usable": true,
"key_parts": [
"gender",
"city",
"name",
"monthsalary",
"yearbonus",
"id"
] /* key_parts */
},
{
"index": "idx_name_photo",
"usable": false,
"cause": "not_applicable"
},
{
"index": "testfulltext",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"best_covering_index_scan": {
"index": "idx_gender_city_name_monthsalary_yearbonus",
"cost": 605355,
"chosen": false,
"cause": "cost"
} /* best_covering_index_scan */,
#setup_range_conditions：如果有可下推的条件，则带条件考虑范围查询
"setup_range_conditions": [
] /* setup_range_conditions */,
#group_index_range：当使用了GROUP BY或DISTINCT时，是否有合适的索引可用。当未使用GROUP BY或DISTINCT时，会显示chosen=false, cause=not_group_by_or_distinct；如使用了GROUP BY或 DISTINCT，但是多表查询时，会显示chosen=false，cause =not_single_table。其他情况下会尝试 分析可用的索引（potential_group_range_indexes）并计算对应的扫描行数及其所需代价
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
#analyzing_range_alternatives：分析各个索引的使用成本
"analyzing_range_alternatives": {
#range_scan_alternatives：range扫描分析
"range_scan_alternatives": [
{
"index": "idx_gender_city_name_monthsalary_yearbonus",#索引
#range扫描的条件范围
"ranges": [
"0 <= gender <= 0 AND 长沙 <= city <= 长沙"
] /* ranges */,
#index_dives_for_eq_ranges：是否使用了index dive，该值会被参数 eq_range_index_dive_limit变量值影响
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,#rowid_ordered：该range扫描的结果集是否根据PK值进行排序
"using_mrr": false,#是否使用了mrr
"index_only": true,#表示是否使用了覆盖索引
"rows": 471868,#扫描的行数
"cost": 56304,#索引的使用成本
"chosen": true #表示是否使用了该索引
}
] /* range_scan_alternatives */,
#analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用， 会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
#chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了 一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案
"chosen_range_access_summary": {
#range_access_plan：range扫描最终选择的执行计划。
"range_access_plan": {
"type": "range_scan",#展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect
"index": "idx_gender_city_name_monthsalary_yearbonus",#索引名
"rows": 471868,#扫描的行数
#range扫描的条件范围
"ranges": [
"0 <= gender <= 0 AND 长沙 <= city <= 长沙"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 471868,#该执行计划的扫描行数
"cost_for_plan": 56304,#该执行计划的执行代价
"chosen": true #是否选择该执行计划
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
#considered_execution_plans：负责对比各可行计划的开销，并选择相对最优的执行计划。
"considered_execution_plans": [
{
#plan_prefix：当前计划的前置执行计划。
"plan_prefix": [
] /* plan_prefix */,
"table": "`customers1s`",#涉及的表名，如果有别名，也会展示出来
#best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
"best_access_path": {
#当前考虑的访问路径
"considered_access_paths": [
{
"access_type": "ref",#使用索引的方式，可参考explain中的type字段
"index": "idx_gender_city_name_monthsalary_yearbonus",#索引
"rows": 471868,#行数
"cost": 56304,#开销
"chosen": true #是否选用这种执行路径
},
{
"access_type": "range",
"range_details": {
"used_index": "idx_gender_city_name_monthsalary_yearbonus"
} /* range_details */,
"chosen": false,
"cause": "heuristic_index_cheaper"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100, #类似于explain的filtered列，是一个估算值
"rows_for_plan": 471868,#执行计划最终的扫描行数，由considered_access_paths.rows X condition_filtering_pct计算获得。
"cost_for_plan": 56304,#执行计划的代价，由considered_access_paths.cost相加获得
"chosen": true #是否选择了该执行计划
}
] /* considered_execution_plans */
},
{
#attaching_conditions_to_tables 基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附 加条件，以便于单表数据的筛选。
"attaching_conditions_to_tables": {
#original_condition：原始的条件语句
"original_condition": "((`customers1s`.`gender` = 0) and (`customers1s`.`city` = '长沙') and (`customers1s`.`monthsalary` = 99))",
#attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问 类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
"attached_conditions_computation": [
] /* attached_conditions_computation */,
#attached_conditions_summary：附加之后的情况汇总
"attached_conditions_summary": [
{
"table": "`customers1s`",#表名
"attached": "(`customers1s`.`monthsalary` = 99)" #附加的条件或原语句中能直接下推给单表筛选的条件。
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
#refine_plan 改善执行计划
"refine_plan": [
{
"table": "`customers1s`" #表名
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
````
补未出现的部分
"finalizing_table_conditions": [
- 发表于 2020-11-06 17:38
- 阅读 ( 695 )
- 分类：数据库
