MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)

③. Mysql如何选择合适的索引


①. 如果用name索引需要遍历name字段联合索引树,然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据,成本比全表扫描还高,可以用覆盖索引优化,这样只需要遍历name字段的联合索引树就能拿到所有结果,如下:


mysql> EXPLAIN select * from employees where name > 'a';


微信图片_20220109125911.png


mysql> EXPLAIN select * from employees where name > 'zzz' ;


微信图片_20220109125929.png


②. 对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果,mysql最终是否选择走索引或者一张表涉及多个索引,mysql最终如何选择索引,我们可以用trace工具来一查究竟,开启trace工具会影响mysql性能,所以只能临时分析sql使用,用完之后立即关闭

trace工具用法:


--开启trace
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;


-- 查看trace字段:
{
  "steps": [
    {
      "join_preparation": {    --第一阶段:SQL准备阶段,格式化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` > 'a') order by `employees`.`position`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {    --第二阶段:SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {    --条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* 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": 10123,    --扫描行数
                    "cost": 2054.7    --查询成本
                  } /* 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": [
                          "a < name"      --索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,    --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,       --是否使用覆盖索引
                        "rows": 5061,              --索引扫描行数
                        "cost": 6074.2,            --索引使用成本
                        "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": 10123,
                      "access_type": "scan",     --访问类型:为scan,全表扫描
                      "resulting_rows": 10123,
                      "cost": 2052.6,
                      "chosen": true,            --确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 10123,
                "cost_for_plan": 2052.6,
                "sort_cost": 10123,
                "new_cost_for_plan": 12176,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`employees`.`position`",
              "items": [
                {
                  "item": "`employees`.`position`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`employees`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {    --第三阶段:SQL执行阶段
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}


结论:全表扫描的成本低于索引扫描,所以mysql最终选择全表扫描(针对name>‘a’)
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描,所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off";    --关闭trace
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
416 66
|
7天前
|
SQL 算法 搜索推荐
mysql 之order by工作流程
本文深入解析了MySQL中`ORDER BY`的排序机制,通过具体示例展示了排序过程及性能优化方法。文章首先分析了基于内存和磁盘的排序方式,包括`sort_buffer_size`的影响以及临时文件的使用场景。接着介绍了`rowid`排序算法,该算法通过减少参与排序的数据量来提升性能,并对比了其与传统排序的区别。此外,还探讨了随机查询`ORDER BY RAND()`的执行流程及其优化策略。最后提到了MySQL 5.6引入的优先队列排序算法,适用于仅需部分有序结果的场景。文章结合`optimizer_trace`工具详细说明了各配置参数对排序行为的影响,为优化查询提供了实用指导。
mysql 之order by工作流程
|
10天前
|
SQL Oracle 关系型数据库
【YashanDB知识库】如何将mysql含有group by的SQL转换成崖山支持的SQL
本文探讨了在YashanDB(崖山数据库)中执行某些SQL语句时出现的报错问题,对比了MySQL的成功执行结果。问题源于SQL-92标准对非聚合列的严格限制,要求这些列必须出现在GROUP BY子句中,而SQL:1999及更高版本允许非聚合列直接出现在选择列中。YashanDB和Oracle遵循SQL-92标准,因此会报错。文章提供了两种解决方法:使用聚合函数处理非聚合列,或将GROUP BY与ORDER BY拆分为两层查询。最后总结指出,SQL-92标准更为严谨合理,建议开发者遵循此规范以避免潜在问题。
|
2月前
|
监控 关系型数据库 MySQL
|
18天前
|
缓存 关系型数据库 MySQL
ThinkPHP框架show columns引发mysql性能问题
ThinkPHP框架的show columns引发mysql性能问题,结尾有关闭方式。
37 13
|
13天前
|
关系型数据库 MySQL OLAP
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
|
14天前
|
SQL 负载均衡 关系型数据库
MySQL复制以及调优
本文介绍了MySQL自带复制方案的实现及其注意事项。复制方案能提供数据备份、负载均衡与分布式数据管理的优势。文章详细描述了复制步骤:主库(master)记录更改到二进制日志,发送同步消息给从库(slave),从库接收后将日志复制到本地并执行。实现复制包括配置主库的server-id和二进制日志、创建复制账号、初始化主库数据、设置从库参数及开启复制。此外,还探讨了三种日志格式(row、statement、mixed)的特点及选择建议,并分析了主从复制延迟的优化方法,如控制事务大小、优化日志传输和多线程还原日志等。最后,文中列出了搭建过程中需要注意的关键点。
|
1月前
|
SQL Oracle 关系型数据库
【YashanDB 知识库】如何将 mysql 含有 group by 的 SQL 转换成崖山支持的 SQL
在崖山数据库中执行某些 SQL 语句时出现报错(YAS-04316 not a single-group group function),而这些语句在 MySQL 中能成功执行。原因是崖山遵循 SQL-92 标准,不允许选择列表中包含未在 GROUP BY 子句中指定的非聚合列,而 MySQL 默认允许这种操作。解决办法包括:使用聚合函数处理非聚合列或拆分查询为两层,先进行 GROUP BY 再排序。总结来说,SQL-92 更严格,确保数据一致性,MySQL 在 5.7 及以上版本也默认遵循此标准。
|
2月前
|
Cloud Native 关系型数据库 MySQL
无缝集成 MySQL,解锁秒级数据分析性能极限
在数据驱动决策的时代,一款性能卓越的数据分析引擎不仅能提供高效的数据支撑,同时也解决了传统 OLTP 在数据分析时面临的查询性能瓶颈、数据不一致等挑战。本文将介绍通过 AnalyticDB MySQL + DTS 来解决 MySQL 的数据分析性能问题。
|
2月前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
591 0