SQL性能规范

这篇具有很好参考价值的文章主要介绍了SQL性能规范。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

🏆一、随聊

记录一下吧,2023年7月13日00:11:11,现在的状态真的很,忙,干不完的活,希望巨大的压力,能够让自己快速成长,回想我这一路,21年大专毕业,用一年时间熟悉软件,快捷键,熟悉开发思维,数据库、JS、HTML、Java都只能算是皮毛,故而当年没有选择Java开发,目前从事数据分析,帆软报表,餐饮零售业务多一点,涵盖,财务、门店、产品、会员、供应链、加盟商等多种场景。越发觉得,成年世界的残酷,多少次想过放弃,想想自己身后空无一人,唯有自渡,便继续咬紧牙关砥砺前行。有感而发~最近加班有点严重,博客断更了很久了~

以下分享自己的工作笔记~~~

🏆二、、SQL性能规范

简介:在项目中常用优化策略。针对本章的内容,需要掌握以下能力:

(1)了解数据库运算时基础的内存、硬盘交互,

(2)了解数据表扫描,基于对扫描的理解来提升SQL执行效率,

(3)了解执行计划,掌握到这一层级可以满足90%项目的需求。

基本理念:当我们执行SQL查询语句时,数据库的操作是先根据SQL语句从硬盘中获取数据,然后加载进内存中进行后续计算。由于SQL语句有执行的先后顺序,因此当SQL一开始查询的数据量越小,加载进内存时间越短,并且整个SQL执行也越快。

1、明确字段,不取多余

解释只取我们需要的字段,不用的不取。不要随意用“*”来查询全部。例如:

假设A表总共有10个字段,只需要用到其中两个字段,那么查询语句中只取我们需要的两个字段,速度比查询所有字段的速度快。

2、大表关联,先做过滤

解释大表关联查询语句中,做到先过滤再关联。不允许使用WHERE来进行关联。提前过滤好,在硬盘和内存交互上、内存计算上可以节省很多时间。

例如

SQL性能规范,# MySQL入门,sql,数据库

3、提取共性,减少重复

解释SQL语句中有多处重复的子查询,可以将其提取出来,进行参数化,减少数据库查询次数。

例如,以下语句中重复使用了一个子查询:

SELECT employee_id, first_name, last_name,

       (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name,

       (SELECT AVG(salary) FROM employees WHERE department_id = 80) AS avg_salary

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees)

ORDER BY department_name;

可以将子查询提取出来进行参数化,如下所示:

CREATE VIEW department_names AS

SELECT department_id, department_name

FROM departments;

CREATE VIEW avg_salaries AS

SELECT department_id, AVG(salary) as avg_salary 

FROM employees

GROUP BY department_id;

SELECT employee_id, first_name, last_name, department_names.department_name, avg_salaries.avg_salary

FROM employees

JOIN department_names ON employees.department_id = department_names.department_id

JOIN avg_salaries ON employees.department_id = avg_salaries.department_id

WHERE salary > (SELECT AVG(salary) FROM employees)

ORDER BY department_name;

通过将子查询提取到视图中,并且使用JOIN语句将相关表连接起来,可以避免多次执行相同的子查询,从而提高查询效率。

4、减少不必要的扫描计算

解释:如果UNION ALL可满足要求,就使用UNION ALL,而不用UNION,因为UNION会有一个比较然后去重的过程,而UNION ALL没有。

5、经常查询的大数据量表,需要创建索引,过滤和排序尽量放在索引列上操作

解释:索引会提高SELECT效率,但是会影响到INSERT及UPDATE的效率。索引的介绍、什么时候应该加索引、什么时候不建议加索引等内容不在此赘述。

创建方式:

-- 新建索引

CREATE INDEX index_name ON table_name(column_name);

-- 新建组合索引

CREATE INDEX index_name ON table_name(column_name1, column_name2, ...);

-- 修改索引

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name ADD INDEX index_name (column_name1, column_name2, ...);

-- 删除索引

DROP INDEX index_name ON table_name;

-- 查询单表索引

SHOW INDEX FROM table_name;

6、避免显式转换、隐式转换导致索引失效

解释避免数据库运算时对索引列进行转换,例如:

(1)显示转换举例,假设索引列为日期型数据,和字符串型日期比较。

错误写法:TO_CHAR(T.索引列,'YYYYMMDD')>='20190715',

这里是用函数将索引列由日期类型转换为字符串类型,导致索引失效。

正确写法:T.索引列 >=TO_DATE('20190715',’YYYY-MM-DD’)

(2)隐式转换举例,假设索引列是一串字符串型的数字,取索引列值为1的数据。

错误写法:T.索引列=1

这里是数据库用隐式转换将索引由字符串类型转换成数值类型,导致索引失效。

正确写法:T.索引列=’1’

7、大表查询时,避免子查询中的排序计算,排序需放在执行计划最后一步

解释:尽量在子查询中避免ORDER BY、DISTINCT等语句。其中ORDER BY是对结果进行排序,而DISTINCT和GROUP BY是在计算过程中排序。子查询数据量较大时用EXISTS子句代替DISTINCT。

8、决策报表中,尽量将多个数据集合并为同一个数据集,减少并发数量

解释:一个数据集就是一个并发,数据集过多导致并发过高,数据库加载压力大,计算效率会降低。可以用以下方案减少决策报表中的数据集数量:

(1)将A、B、C三个指标的每月/每日的趋势,以年月/年月日为条件进行关联,而不是每个指标单独一个数据集。

(2)查询结果为一条记录的数据集,直接关联在一起,例如三个数据集A、B、C都返回一条记录,那么可以如下图进行数据集合并处理。

建议同比环比能一个数据集查询出来就用一个数据集查询

如下图

SQL性能规范,# MySQL入门,sql,数据库

9、报表中控件的数据字典如果数据量过大,并且涉及到控件联动,此时将一个数据集拆开成多个数据集可以提高控件加载效率,与本条规则相违背,需要酌情考虑。

🏆三、EXPLAIN 命令

EXPLAIN 命令来查看 MySQL 的查询执行计划

  1. id:每个 SELECT 子句或者操作表的唯一 ID。
  2. select_type:表示是哪种 SELECT 类型,例如 SIMPLE(简单 SELECT,不包含子查询或 UNION)、PRIMARY(最外层的 SELECT)、SUBQUERY(子查询)等。
  3. table:操作的表名。
  4. partitions:查询中涉及到的分区列表。
  5. type:表示用于执行查询的 Join 类型,例如 ALL,index,range,ref,eq_ref 等。
  6. possible_keys:可能用于查询的索引列表。
  7. key:实际使用的索引。
  8. key_len:表示索引字段长度。
  9. ref:表示索引和哪个字段或常量进行比较。
  10. rows:表示需要扫描的表行数。
  11. filtered:表示筛选出来的行占总行数的百分比。
  12. Extra:其他信息,如使用的索引类型、是否使用临时表、是否使用文件排序等。

🏆四、按照优化程度从最好到最差的顺序排列:

  1. system:表示只有一行记录(例如SELECT ... FROM DUAL),这是最快的访问类型。
  2. const:表示通过索引一次就能找到的常量表达式。例如,根据主键或唯一索引进行等值查询。
  3. eq_ref:表示使用了等值连接,对于每个索引键值,表中只有一条匹配记录。这通常出现在使用主键或唯一索引进行连接的情况下。
  4. ref:表示使用非唯一索引进行等值查询或使用唯一索引进行不完全的索引查询。这可能返回多个匹配记录。
  5. range:表示通过索引范围扫描来获取记录,例如使用BETWEENIN或某些算术操作符进行查询。
  6. index:表示全索引扫描,通常出现在查询中没有使用索引或无法使用索引的情况下。
  7. all:表示全表扫描,需要遍历整个表以找到匹配的记录,这是最慢的访问类型。

🏆五、SHOW INDEX指令

  • Table:表示所查询的表名。
  • Non_unique:表示索引是否允许重复值,如果为0则表示唯一索引,否则为非唯一索引。
  • Key_name:表示索引名称。
  • Seq_in_index:表示这个索引列在索引中出现的顺序,从1开始计数。
  • Column_name:表示索引列的名称。
  • Collation:表示索引列的字符集。
  • Cardinality:表示索引列的基数,即不同值的数量,数值越大则说明该索引是更加有用的。
  • Sub_part:表示索引列的子串长度,对于非字符串类型的索引列其值为null。
  • Packed:表示所有索引列使用的数据类型所占的字节数,数值越小则说明该索引更加紧凑。
  • Null:表示索引列是否允许为空。
  • Index_type:表示使用的索引方法,如 Btree、Hash 等。
  • Comment:表示索引的注释信息。

🏆六、常用优化手段

1、删除单个索引,增加组合索引

2、修改处理时间条件的函数function_xx_month【针对SQL查询中使用函数处理】

3、改进重复数据集【针对帆软数据集重复、冗余问题】

4、将in子查询改为内连接

5、优化group by字段

6、开启缓存【如果SQL因业务场景是在无法继续优化,可以开启帆软数据集中的数据缓存,牺牲服务器的空间换取SQL执行的时间】

7、条件属性的删除【帆软报表中的条件属性、过滤、形态、公式计算比较多的时候需要考虑优化】

8、删除索引【过量的索引也会导致查询速度变慢】

9、将between and关键字换为>= <=【这个仁者见仁,自己测试部分场景下的SQL有显著提速,但是大多数情况下提速不明显】

🏆七、总结

SQL的优化是一个不断试错的过程,同时观摩不同人的SQL风格,会有不一样的理解,如果你看的懂别人写的,静下心多看,这样自己写的时候,水到渠成,如果看不懂别人的SQL/代码,静下心慢慢看,只有看懂才会写!共勉!文章来源地址https://www.toymoban.com/news/detail-571895.html

到了这里,关于SQL性能规范的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 数据库——MySQL高性能优化规范

    所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用 MySQL 保留(如果表名中包含查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过 32 个字符 临时库表必须以 tmp_为前缀并以日期为后缀,

    2024年02月11日
    浏览(99)
  • 数据库监控与调优【六】—— SQL性能分析

    TIPS 本文基于MySQL 8.0 EXPLAIN分析SQL它不香吗?如何更加细致分析SQL的性能呢?深入SQL内部分析性能! SHOW PROFILE:简单、方便,已废弃 INFORMATION_SCHEMA.PROFILING:和SHOW PROFILE本质是一样的,已废弃 PERFORMANCE_SCHEMA:MYSQL建议的方式,未来之光,但目前来说使用不够方便 先要做一定的

    2024年02月11日
    浏览(57)
  • 性能测试jmeter连接数据库jdbc(sql server举例)

    一、下载第三方工具包驱动数据库 1. 因为JMeter本身没有提供链接数据库的功能,所以我们需要借助第三方的工具包来实现。  (有这个jar包之后,jmeter可以发起jdbc请求,没有这个jar包,也有jdbc取样器,但不能发起请求。) 2. 进入maven仓库-http://mvnrepository.com/,找到想要的工

    2024年02月09日
    浏览(49)
  • 性能_3 jmeter连接数据库jdbc(sql server举例)

    一、下载第三方工具包驱动数据库 1. 因为JMeter本身没有提供链接数据库的功能,所以我们需要借助第三方的工具包来实现。  (有这个jar包之后,jmeter可以发起jdbc请求,没有这个jar包,也有jdbc取样器,但不能发起请求。) 2. 进入maven仓库-http://mvnrepository.com/,找到想要的工

    2024年02月11日
    浏览(50)
  • 数据库入门-----SQL基础知识

    目录 📖前言: 📑SQL概述通用语法:  🐳DDL: 🐻操作数据库: 🐞数据类型:  🦉操作表: 🦦DML: 语法规则: 案例演示: 🦏DQL: 语法规则: 案例演示: # 基础查询:  # 条件查询:  #聚合函数:  # 分组查询:  #排序查询:  #分页查询:  执行顺序: 🫎DCL: 语法规则:

    2024年04月10日
    浏览(53)
  • 【MySQL 数据库】7、SQL 优化

    ① 批量插入数据 ② 手动控制事务 ③ 主键顺序插入,性能要高于乱序插入 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3 主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89 【☆】 】 ① 如果需要一次性插入大批量数据(百万级别),使用 insert 语句插入性能 很低 ② 可使用 MySQL 数据库提供的 loa

    2024年02月08日
    浏览(59)
  • 数据库应用:MySQL数据库SQL高级语句与操作

    目录 一、理论 1.克隆表与清空表 2.SQL高级语句 3.SQL函数 4.SQL高级操作 5.MySQL中6种常见的约束 二、实验  1.克隆表与清空表 2.SQL高级语句 3.SQL函数 4.SQL高级操作 5.主键表和外键表  三、总结 克隆表:将数据表的数据记录生成到新的表中。 (1)克隆表 ① 先创建再导入 ② 创建

    2024年02月13日
    浏览(75)
  • GaussDB高斯数据库(SQL语法入门)

    DDL数据定于语言:用于定义或修改数据库中的对象,如:表,索引,视图,数据库,序列,用户,角色,表空间,会话等。 DDL不支持物化视图,存储过程,触发器,自定义函数,自定义类型。 DML数据操作语言:用于对数据库表中的数据进行操作,如插入,更新和删除。 DCL数

    2023年04月10日
    浏览(43)
  • 【MySQL数据库】MySQL 高级SQL 语句一

    ) % :百分号表示零个、一个或多个字符 _ :下划线表示单个字符 ‘A_Z’:所有以 ‘A’ 起头,另一个任何值的字符,且以 ‘Z’ 为结尾的字符串。例如,‘ABZ’ 和 ‘A2Z’ 都符合这一个模式,而 ‘AKKZ’ 并不符合 (因为在 A 和 Z 之间有两个字符,而不是一个字符)。 ‘ABC%’

    2024年02月09日
    浏览(197)
  • 主流数据库(SQL Server、Mysql、Oracle)通过sql实现多行数据合为一行

    1、方法一:使用 STUFF 和 FOR XML PATH 进行多行合并成一行 (1)FOR XML PATH用法 FOR XML 是 SQL Server 提供的一种功能,允许您将查询结果转换为 XML 格式。 PATH 模式则是其中一种灵活的方式来构造自定义的XML结构。 1、基本字符串连接 : 当您想从单列中提取所有行的数据并连接成一

    2024年04月10日
    浏览(57)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包