mysql2008 第11次 性能优化过程与分析

mysql 优化过程与分析

一.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 最终的、经过优化后的表条件。
{

"finalizing_table_conditions": [
{
"table": "`salaries`",
"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and
(`salaries`.`from_date` = DATE'1986-06-26'))",
"final_table_condition ": null
}
] /* finalizing_table_conditions */
 
优化过程做的事情

1.确定查询的表(多对多那就是多表)

2.查询字段信息,字段类型信息,索引信息等

3.重写sql语句或者是where条件。

4.判断索引使用

四、Explaine结果如何进行分析


优化器执行过程分析(在explain相当的条件下)

show index from customers1s;

alter table customers1s add index idx_city_gender(city,gender);

select * from customers1s where city="长沙" and gender=0;
S:9s多
explain select * from customers1s where city="长沙" and gender=0;

id select_type   table          partitions  type   possible_keys                               key              key_len   ref           rows    filtered  Extra
1 SIMPLE       customers1sNull        ref   idx_gender_city_name_monthsalary_yearbonus idx_city_gender  33      const,const471868100       Null
                                                   idx_city_gender

原因分析 使用了索引idx_city_gender,idx_city_gender索引树上存储的是主键、city、gender,但查询的是所有字段,又根据索引对应的主键,查找了满足条件的数据 ,即产生了回表。

回表查询的本质:索引当中并不存在sql语句需要查询的数据
1. 连接层 -》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据的时候发现索引
并不满足需要查询数据 =》 通过查询的数据的主键索引去磁盘重查询数据 =》返回数据

select id,city,gender from customers1s where city="长沙" and gender=0;
s:0.21s
explain select id,city,gender from customers1s where city="长沙" and gender=0;

id select_type   table          partitions  type   possible_keys                               key              key_len   ref           rows    filtered  Extra
1 SIMPLE       customers1sNull        ref   idx_gender_city_name_monthsalary_yearbonus idx_city_gender  33      const,const444500100       Using index
                                                   idx_city_gender

2. 连接层 -》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据 =》 返回数据

select * from customers1s where city="长沙" and yearbonus=0;
s:37.521s
explain select * from customers1s where city="长沙" and yearbonus=0;

id select_type   table          partitions  type   possible_keys      key              key_len   ref    rows    filtered  Extra
1 SIMPLE       customers1sNull        ref   idx_city_gender    idx_city_gender  33      const94478410        Using where

3. 连接层 -》 sql层 (优化器:得到最优的执行计划 idx_city_gender)=》查询数据的时候发现索引
并不满足需要查询数据 =》 通过查询的数据的主键索引去磁盘重查询数据 =》还需要通过where来进行数据过滤 =》 返回数据

即:索引过滤==》回表==》非索引过滤

总结:分析查询语句过程
1. 先看查询使用的索引
2. 确定索引数据
3. 在预判sql语句扫描的数据与索引数据做对比
4. 在确定这个sql执行是不是最好
  • 发表于 2020-11-06 17:38
  • 阅读 ( 66 )
  • 分类:数据库

你可能感兴趣的文章

相关问题

0 条评论

请先 登录 后评论
吉洪叶
吉洪叶

19 篇文章

作家榜 »

  1. 六星官方社区工作人员 1094 文章
  2. 吉洪叶 19 文章
  3. 天晓冥 2 文章
  4. muEasy 2 文章
  5. 百日慕晴 2 文章
  6. baiyang 2 文章
  7. plum 1 文章
  8. 周前 1 文章