MySQL · 性能优化· CloudDBA SQL优化建议之统计信息获取

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

阿里云CloudDBA具有SQL优化建议功能,包括SQL重写建议和索引建议。SQL索引建议是帮助数据库优化器创造最佳执行路径,需要遵循数据库优化器的一系列规则来实现。CloudDBA需要首先计算表统计信息,是因为:

  • 数据库优化器通常是基于代价寻找执行路径;
  • SQL优化建议所针对的数据库不限于MySQL数据库,也不局限于某一个特定版本;

1. 基本原则

数据库统计信息在SQL优化起到重要作用。用来估算查询条件选择度的常见统计信息包括表统计信息和字段统计信息。DBA计算查询条件选择度或代价时经常通过手工执行SQL语句获取,并进行返回行数或代价的粗略估算。

  • 表统计信息:表中总记录数;
  • 字段统计信息:包括最大值,最小值;以及不同值个数;

而要相对更准确的获取条件选择度的估算,往往需要统计直方图(Histogram),因为多数情况,每个值的出现频度是不一样的。针对复杂SQL的优化,比如多条件查询、Range查询以及多表关联查询等,统计直方图能帮助DBA更好的进行代价估算。

在云上环境,获取统计信息以最小代价为前提的,不能对生产系统造成任何性能上的负面影响,也不能耗费较长时间。获取统计数据的基本原则如下:

  • 从备库获取统计数据;
  • 只统计最近数据;
  • 采取抽样的方式获取数据;
  • 不抽取原始数据,只对数据的hash值进行统计;

2. 最近数据统计

长期变化的数据通常具有周期性,并且以天为基本周期符合一般业务逻辑。因此多数情况无需对全量数据进行统计,抽取最近一天的数据通常具有代表性。

3. 样例数据统计

云上数据库通常要求表设计中有自增主键。在这一条件下获取表的最近数据的方法较为简单,比如:

 select * from tab order by id desc limit 1000; 

该语句通过在自增主键上做排序并获取最近插入的1000行数据。由于id是主键,排序并无额外代价。类似方式可以获取第其它样例数据,比如:

 select * from tab order by id desc limit 10000, 1000; 

4. 数据特征分析

基于抽样数据,对影响选择度或查询返回行数的特性进行分析:

  • 数据频率

对每一份样例数据中不同字段的频率统计之后,需要推导出或预测字段中的某个数值在全表中的频率情况。通过分析不同样例数据间的数据重合度在具体实践中具有实际意义。

  • 数据密度

获取每个字段的最大值和最小值代价较高。变通方法就是通过样例数据的最大最小值以及频率进行数据密度计算。基于数据密度数据,估算范围查询返回行数。

  • 字段关联性

评估多条件查询的选择度需要首先获取字段之间的关联性。若多条件查询条件关联性很低,则综合选择度就是单个条件选择度的乘积;若多条件查询条件关联性较高,则采用最小选择度(或乘以系数)作为综合选择度。

5. 总结

  • 直方图是对基本数据的估计,任何直方图都不是精确的;
  • 云上环境以最小代价获取统计数据是基本前提;
  • 数据库优化器需要选择的是最佳路径,得出字段之间选择度的相对值更为重要;
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
SQL 数据采集 关系型数据库
实现MySQL与SQL Server之间数据迁移的有效方法
总的来说,从MySQL到SQL Server的数据迁移是一个涉及到很多步骤的过程,可能会遇到各种问题和挑战。但只要精心规划、仔细执行,这个任务是完全可以完成的。
81 18
|
1月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
75 12
|
2月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
2月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
SQL 关系型数据库 索引
SQL优化常用方法53
分离表和索引
1361 0
|
SQL
SQL优化常用方法51
使用显式的游标(CURSORs)
1137 0
|
SQL
SQL优化常用方法49
优化GROUP BY
1139 0
|
SQL 索引
SQL优化常用方法47
CBO下使用更具选择性的索引
1095 0

热门文章

最新文章

推荐镜像

更多
下一篇
oss创建bucket