mysql sql分析 11

最新推荐文章于 2023-07-12 21:08:09 发布
最新推荐文章于 2023-07-12 21:08:09 发布
阅读量184 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44102152/article/details/109535243
版权

mysql sql分析

sql语句的错误使用

问题：可不可以多次查询优化查询效率 （针对join 或者 in ）

注意这个问题并没有很直接的正确答案，问题本身是模棱两可的 既可以多次查询也可以一起；
对于SQL的错误使用有两种情况就是过强和过弱 这是常常会可能出现的问题：
比如查询粉丝最多的前十个用户的文章总数

粉丝关注表：user_fans

FieldTypeNullKeyDefaultExtra
idint(11)NOPRI0.
user_idint(11)NO| MULNULL.
fans_idint(11)NO| MULNULL.

查询的方法

方案一

select user_id from (
select
user_id,count(*) c
from
user_fans
group by
user_id
order by c desc limit 0,5
) as fans_count;

然后再通过循环查询出用户的id信息并输出

for (fans_count)

select count(*) from article where user_id = ?

对比：

  1. 从MySQL执行的query数量来看第一种为 1 + 10 = 11 条，第二种为 1 + 1 =2
  2. 对应的交互数量11 ： 2
  3. io操作对比假设一条SQL就是一个IO，第一种最少有11次IO，第二种小余或等于11次IO；如果数据非常离散就是11次；
  4. 复杂度：前缀的SQL不管只看第二条SQL；第一种简单一些，第二种就增加了group by
  5. 数据库对于结果的返回地中是11次，第二种是2次，但是第二种方法中的第二次结果是第一次的10倍
  6. 从应用程序的数据处理来看，第二种比第一种多了一个拼接photo_id的过程

根据上面的点做对比分析：

  1. 由于MySQL不管客户端每次调教的query是相同还是不同，都需要进行完全解析这个动作主要消耗的资源师数据库主机的CPU，那么这里第一种方案和第二 种方案消耗CPU的比例是11:2. query语句的解析动作在整个query语句执行过程中的整体消耗的CPU比例是比较多的；
  2. 网络交互对比：11:2
  3. IO比 <= 1:1
  4. 在数据量少的情况下分组性能问题不大

所以相对来说第二种会更好一点-》 这个情况就是过度的弱化SQL 或者说数据库的处理能力
但是如果说根据第二种方法把两条SQL合在一起呢？

select count(*) from article where user_id in (
select user_id from (
select
user_id,count(*) c
from
user_fans
group by
user_id
order by c desc limit 0,5
) as fans_count;
) group by user_id;

当然是可以执行，但是目前的这个SQL复杂度同比之前任何一条SQL都要复杂 还不是一点点的问题…

从数据库的交互来说 2 ： 1，但是复杂来说不是一点点的大

之前其实有提过SQL语句会通过解析器分解为不同的令牌，任何生成解析树提供给优化器使用并对SQL进行计算和优化，再去执行该计划，但是因为SQL的复杂度增加，对于优化器来说分析的难度也会增加 – 执行的计算会更多。

粉丝关注及其用户信息统计：user_stat

FieldTypeNullKeyDefaultExtra
idint(11)NOPRI0.
user_idint(11)NO| MULNULL.
fans_countint(11)NO| MULNULL.

explain 如何分析

explain在之前的课程中我们已经有讲解，这里就不做说明…

本次分析依旧使用我们之前课程的sql案例如下：

explain select * from staffs where sex = 1  and age = 20 and name = 'starsky';
explain select * from customers1 where city = "长沙" and gender = 0;

结果如下：在这里插入图片描述
在这里插入图片描述

优化器的执行过程与分析

在这里插入图片描述

1. 开启OPTIMIZER_TRACE

set optimizer_trace="enabled=on";--开启trace查看优化器的结果
set end_markers_in_json=on;--增加注释

select id,`name`,city,monthsalary,gender from customers1 where city="长沙" and gender=0 and monthsalary=99;
select * from information_schema.optimizer_trace \G;

2. OPTIMIZER_TRACE结果

QUERY：查询语句
TRACE：QUERY字段对应语句的跟踪信息
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。
INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。

join_preparation
join_preparation段落展示了准备阶段的执行过程。

{
  "join_preparation": {
    "select#": 1,
    "steps": [
      {
        -- 对比下原始语句，可以知道，这一步做了个格式化。
        "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"
      }
    ]
    /* steps */
  }
  /* join_preparation */
}

join_optimization join_optimization展示了优化阶段的执行过程，是分析OPTIMIZER TRACE的重点。这段内容超级长，而且分了好多步骤，不妨按照步骤逐段分析：
condition_processing 该段用来做条件处理，主要对WHERE条件进行优化处理。

"condition_processing": {
  "condition": "WHERE",
  "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",
  "steps": [
    {
      "transformation": "equality_propagation",
      "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
    },
    {
      "transformation": "constant_propagation",
      "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"
    },
    {
      "transformation": "trivial_condition_removal",
      "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"
    }
  ] /* steps */
} /* condition_processing */

其中：

condition：优化对象类型。WHERE条件句或者是HAVING条件句
original_condition：优化前的原始语句
steps：主要包括三步，分别是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
transformation：转换类型句
resulting_condition：转换之后的结果输出

substitute_generated_columns substitute_generated_columns用于替换虚拟生成列

"substitute_generated_columns": {
} /* substitute_generated_columns */
table_dependencies 分析表之间的依赖关系

{
  "table_dependencies": [
    {
      "table": "`salaries`",
      "row_may_be_null": false,
      "map_bit": 0,
      "depends_on_map_bits": [
      ] /* depends_on_map_bits */
    }
  ] /* table_dependencies */
}

其中：

table：涉及的表名，如果有别名，也会展示出来
row_may_be_null：行是否可能为NULL，这里是指JOIN操作之后，这张表里的数据是不是可能为NULL。如果语句中使用了LEFT JOIN，则后一张表的row_may_be_null会显示为true
map_bit：表的映射编号，从0开始递增
depends_on_map_bits：依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时，会在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses 列出所有可用的ref类型的索引。如果使用了组合索引的多个部分（例如本例，用到了index(from_date, to_date) 的多列索引），则会在ref_optimizer_key_uses下列出多个元素，每个元素中会列出ref使用的索引及对应值。

{
  "ref_optimizer_key_uses": [
    {
      "table": "`salaries`",
      "field": "from_date",
      "equals": "DATE'1986-06-26'",
      "null_rejecting": false
    },
    {
      "table": "`salaries`",
      "field": "to_date",
      "equals": "DATE'1987-06-26'",
      "null_rejecting": false
    }
  ] /* ref_optimizer_key_uses */
}

rows_estimation 顾名思义，用于估算需要扫描的记录数。

{
  "rows_estimation": [
    {
      "table": "`salaries`",
      "range_analysis": {
        "table_scan": {
          "rows": 2838216,
          "cost": 286799
        } /* table_scan */,
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": false,
            "cause": "not_applicable"
          },
          {
            "index": "salaries_from_date_to_date_index",
            "usable": true,
            "key_parts": [
              "from_date",
              "to_date",
              "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": "salaries_from_date_to_date_index",
              "usable": false,
              "cause": "query_references_nonkey_column"
            }
          ] /* potential_skip_scan_indexes */
        } /* skip_scan_range */,
        "analyzing_range_alternatives": {
          "range_scan_alternatives": [
            {
              "index": "salaries_from_date_to_date_index",
              "ranges": [
                "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
              ] /* ranges */,
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 86,
              "cost": 50.909,
              "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": "salaries_from_date_to_date_index",
            "rows": 86,
            "ranges": [
              "0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"
            ] /* ranges */
          } /* range_access_plan */,
          "rows_for_plan": 86,
          "cost_for_plan": 50.909,
          "chosen": true
        } /* chosen_range_access_summary */
      } /* range_analysis */
    }
  ] /* rows_estimation */
  /* 其中： ` table：表名 */
}

range_analysis： table_scan：如果全表扫描的话，需要扫描多少行（row，2838216），以及需要的代价（cost，286799） potential_range_indexes：列出表中所有的索引并分析其是否可用。如果不可用的话，会列出不可用的原因是什么；如果可用会列出索引中可用的字段； 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）并计算对应的扫描行数及其所需代价 skip_scan_range：是否使用了skip scan

TIPS skip_scan_range是MySQL 8.0的新特性，感兴趣的可点击详见

analyzing_range_alternatives：分析各个索引的使用成本 range_scan_alternatives：range扫描分析 index：索引名 ranges：range扫描的条件范围 index_dives_for_eq_ranges：是否使用了index dive，该值会被参数eq_range_index_dive_limit变量值影响。 rowid_ordered：该range扫描的结果集是否根据PK值进行排序 using_mrr：是否使用了mrr index_only：表示是否使用了覆盖索引 rows：扫描的行数 cost：索引的使用成本 chosen：表示是否使用了该索引 analyzing_roworder_intersect：分析是否使用了索引合并（index merge），如果未使用，会在cause中展示原因；如果使用了索引合并，会在该部分展示索引合并的代价。 chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案

range_access_plan：range扫描最终选择的执行计划。
type：展示执行计划的type，如果使用了索引合并，则会显示index_roworder_intersect
index：索引名
rows：扫描的行数
ranges：range扫描的条件范围
rows_for_plan：该执行计划的扫描行数
cost_for_plan：该执行计划的执行代价
chosen：是否选择该执行计划

>considered_execution_plans
负责对比各可行计划的开销，并选择相对最优的执行计划。
```json
{
  "considered_execution_plans": [
    {
      "plan_prefix": [
      ] /* plan_prefix */,
      "table": "`salaries`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "access_type": "ref",
            "index": "salaries_from_date_to_date_index",
            "rows": 86,
            "cost": 50.412,
            "chosen": true
          },
          {
            "access_type": "range",
            "range_details": {
              "used_index": "salaries_from_date_to_date_index"
            } /* range_details */,
            "chosen": false,
            "cause": "heuristic_index_cheaper"
          }
        ] /* considered_access_paths */
      } /* best_access_path */,
      "condition_filtering_pct": 100,
      "rows_for_plan": 86,
      "cost_for_plan": 50.412,
      "chosen": true
    }
  ] /* considered_execution_plans */
}

其中

plan_prefix：当前计划的前置执行计划。
table：涉及的表名，如果有别名，也会展示出来
best_access_path：通过对比considered_access_paths，选择一个最优的访问路径
considered_access_paths：当前考虑的访问路径
access_type：使用索引的方式，可参考explain中的type字段
index：索引
rows：行数
cost：开销
chosen：是否选用这种执行路径
condition_filtering_pct：类似于explain的filtered列，是一个估算值
rows_for_plan：执行计划最终的扫描行数，由considered_access_paths.rows X condition_filtering_pct计算获得。
cost_for_plan：执行计划的代价，由considered_access_paths.cost相加获得
chosen：是否选择了该执行计划
attaching_conditions_to_tables
基于considered_execution_plans中选择的执行计划，改造原有where条件，并针对表增加适当的附加条件，以便于单表数据的筛选。

TIPS 这部分条件的增加主要是为了便于ICP（索引条件下推），但ICP是否开启并不影响这部分内容的构造。 ICP参考文档

{
  "attaching_conditions_to_tables": {
    "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
    "attached_conditions_computation": [
    ] /* attached_conditions_computation */,
    "attached_conditions_summary": [
      {
        "table": "`salaries`",
        "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
      }
    ] /* attached_conditions_summary */
  } /* attaching_conditions_to_tables */
}

其中：

original_condition：原始的条件语句
attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
attached_conditions_summary：附加之后的情况汇总
table：表名
attached：附加的条件或原语句中能直接下推给单表筛选的条件。

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

refine_plan 改善执行计划：

{
  "refine_plan": [
    {
      "table": "`salaries`"
    }
  ] /* refine_plan */
}

其中：table：表名及别名

join_execution join_execution段落展示了执行阶段的执行过程。

"join_execution": {
  "select#": 1,
  "steps": [
  ] /* steps */
}

下一篇：mysql 单表优化 索引选择 12
上一篇：mysql事务隔离级别与IO的关系 10

哒哒-blog
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL：SQL性能分析
知识改变命运，ヾ(◍°∇°◍)ﾉﾞ【求关注】
03-27 704
Sql性能分析详解
SQL Injection with MySQL 注入分析
09-14
本文作者：angel文章性质：原创　国内能看到php+Mysql注入的文章可能比较少，但是如果关注各种WEB程序的漏洞，就可以发现，其实这些漏洞的文章其实就是一个例子。
关于Mysql Explain慢查询，你该知道的分析
weixin_38171468的博客
04-23 579
explain分析Explain 详解Explain字段详解（重点关注加粗项）select_type解读 （UNION:联合查询）table 解读type 解读possible_keys 解读Key 解读key_len 解读ref 解读rows 解读Extra 解读 Explain 详解 在日常工作中，我们会遇到sql语句查询速度特别慢，常常用到explain这个命令来查看一个这些SQL语...
mysql in优化_MySQL优化器和SemiJoin优化
weixin_39932611的博客
12-01 183
提示：公众号展示代码会自动折行，建议横屏阅读1. 背景1.1. MySQL执行流程MySQL的执行过程包括多个子阶段：语法分析、语义检查、逻辑优化、物理优化和执行。其中逻辑优化和物理优化统称为查询优化。一个查询优化器的输入是查询树，输出是查询执行计划。逻辑优化也称为基于规则的查询优化(Rule Based Optimization，简称RBO)。主要是对查询进行逻辑上的等价变换，目的是通...
MySQL----SQL性能分析
emmmm.....
04-24 2775
文章目录SQL性能分析1 SQL执行频率2 慢查询日志2.1 查询慢日志是否开启2.2 查询慢日志的时间2.3 查看慢日志文件中记录的信息3 profile详情3.1 查询是否支持 profile3.2 查询 profile 是否开启3.3 开启 profile3.4 查看每一条SQL的耗时基本情况3.5 查看指定query_id的SQL语句各个阶段的耗时情况3.6 查看指定query_id的SQL语句CPU的使用情况4 explain4.1 语法4.2 Explain 执行计划中各个字段的含义 SQL性
mysql中进行数据分析_SQL数据分析（一）
weixin_33747775的博客
02-02 1304
写在前面：抖音重燃我对数据分析的兴趣。粗略略算是众多兴趣爱好之一，和追剧，画画，睡觉没啥区别，非要说个差别那就是体现方式不同而已。兴趣爱好这个东西有两面性，或坚持或随性。在我看来，它是流动的，轮回的，自由的。喜欢的时候用力跟，淡忘的时候干脆放，重要的还是获取当下的满足与快乐。很久以前看SQL语句，认为基础语法简单，只要用的时候百度就够了。动手以后发现实践的快乐不是看和听所拥有的。那现在立个flag...
MySQL线上SQL捕获及分析
05-01
MySQL线上SQL捕获及分析
Yearning Mysql SQL审核平台.rar
最新发布
07-29
今天分享一款可视化的SQL自动检测平台：Yearning MYSQL，可以方便开发人员初步定位问题，同时将SQL快速提交给DBA处理，DBA可以通过预设的规则来规范SQL语句的规范，必要时可以把错误的SQL语句快速回滚。 MySQL是十分...
慢sql分析，慢日志设置，慢Sql分析工具
05-16
慢sql分析，慢日志设置，慢Sql分析工具：mysql.slow_log 表日志的操作；慢 sql 的 explain 分析；explain 结果的 type 类型举例；索引失效举例；慢 sql 的 profile 分析；慢 sql 的 optimizer_trace 分析；慢日志...
MySQL对SQL查询的分析（二）
weixin_43879445的博客
04-26 550
性能下降SQL慢，执行时间长，等待时间长，查询语句写得烂，索引失效（单值、复合），关联查询太多join（设计缺陷或不得已的需求），服务器调优及各个参数设置（缓冲\线程数等）。
Mysql的sql分析步骤
itman_cx的博客
06-09 550
Mysql的sql分析步骤 一、查询SQL的执行频率 MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。 下面的命令显示了当前 session 中所有统计参数的值(模糊查询，
MYSQL | SQL性能 | 有效分析的技巧和方法
devops
07-12 4992
在SQL性能优化过程中，经常遇到的问题包括查询响应时间过长、数据库负载过高、不适当的索引策略以及查询语句的复杂性等。SQL性能分析可以帮助我们发现潜在的性能问题，定位瓶颈所在，并提供相应的优化策略。通过深入分析SQL查询的执行计划、监控关键性能指标以及使用专业的性能分析工具，我们可以及时优化和改进数据库应用程序的性能。
MySQL之四种SQL性能分析工具
weixin_52690231的博客
03-15 6624
MySQL知识总结之四种SQL性能分析工具
explain sql 结果参数解读
念念的博客,记录学习的点点滴滴！
11-18 939
● explain结果： mysql&amp;amp;gt; explain select * from emp; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partit...
declare @t table
02-09 1526
DECLARE@tTABLE(datechar(21))INSERT@tSELECT'1900-1-100:00:00.000'INSERT@tSELECT'1900-1-100:00:00.001'INSERT@tSELECT'1900-1-100:00:00.009'INSERT@tSELECT'1900-1-100:00:00.002'INSE...
MySQL declare语句用法介绍
热门推荐
superbfly的专栏
10-11 5万+
MySQLdeclare语句是我们经常用到的语句，下文就为您举例说明了MySQL declare语句的用法，希望对您学习MySQL declare语句的使用能有所帮助。 MySQL declare语句是在复合语句中声明变量的指令。 (1)Example with two DECLARE statements 两个DECLARE语句的实例 CREATEPROCEDUREp8
表变量。 declare table @t_var
weixin_30642029的博客
12-19 1617
1. 声明表变量：declare table @tablename(columnname type), 如： declare table @t(id int); 2. 把查询 结果插入表变量中： insert into @t.columnname select id from table1, 把table1的id 插入@t中 转载于:https://www.cnblogs.com/hell...
MySQL 执行计划分析：EXPLAIN
Tony_stark_L的博客
12-31 435
MySQL EXPLAIN 执行计划分析
MSSQL中的数组-Table类型.
Jeremiah's Wikipedia
10-21 2244
table     一种特殊的数据类型，用于存储结果集以供后续处理。该数据类型主要用于临时存储一组行，这些行将作为表值函数的结果集返回。         语法             说明     使用   DECLARE   @local_variable   声明   table   类型的变量。             table_type_definition   ::=    
linux mysql如何分析sql语句
06-08
在 Linux MySQL 中，您可以使用 EXPLAIN 关键字来分析 SQL 查询语句。 EXPLAIN 关键字可以让您查看 MySQL 优化器在执行查询时的执行计划，包括使用哪些索引、表的连接方式等等。您可以通过分析执行计划来了解查询的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值