当前位置:首页 > 数码 > SQL-五大提升-语句性能的小窍门 (SQL五大命令)

SQL-五大提升-语句性能的小窍门 (SQL五大命令)

admin5个月前 (05-04)数码39

在进行数据库操作时,优化 SQL 语句是提升性能和效率的关键步骤之一。无论处理的是大规模数据还是简单的查询,优化 SQL 语句都可以明显改善系统的响应时间和资源利用率。

  1. 使用内连接

    当涉及到多个表的连接查询时,通常使用 join 关键字。最常用的连接方式是左连接和内连接。以下使用 inner join 的示例:

    
            SELECT o.id, o.code, u.name
            FROM order o
            INNER JOIN user u
              ON o.user_id = u.id
            WHERE u.status = 1;
          

    如果两个表使用 inner join 关联,会自动选择两个表中的小表驱动大表,因此在性能上不会出现太多问题。以下使用 left join 的示例:

    
            SELECT o.id, o.code, u.name
            FROM order o
            LEFT JOIN user u
              ON o.user_id = u.id
            WHERE u.status = 1;
          

    如果两个表使用 left join 关联,MySQL 默认使用左连接关键字驱动右侧的表。如果左表中存在大量数据,则可能会出现性能问题。需要注意的是,在使用 left join 查询时,应该将小表放在左侧,将大表放在右侧。如果可以使用 inner join,应尽量避免使用 left join。

  2. 限制索引的数量

    众所周知,索引可以大幅提高 SQL 查询的性能,但索引的数量并不是越多越好。因为当向表中添加新数据时,同时需要为其创建索引,而索引需要额外的存储空间和一定的性能消耗。单个表中的索引数量应尽量控制在 5 个以内,单个索引中的字段数量也不应超过 5 个。MySQL 使用的 B+ 树结构来保存索引,B+ 树索引在插入、更新和删除操作时需要进行更新。如果索引过多,将消耗大量的额外性能。

    那么,如果表中的索引过多,超过了 5 个怎么办呢?这个问题需要辩证地看待。如果你的系统并发性较低,表中的数据量也不是很大,实际上可以使用超过 5 个的索引,只要不过度即可。但对于一些高并发的系统,务必遵守单个表上不超过 5 个索引的限制。那么,高并发系统如何优化索引的数量呢?

    • 如果可以建立联合索引,就不要建立单个索引,可以删除一些无用的单个索引。
    • 将一些查询功能迁移到其他类型的数据库中,比如 ElasticSearch、HBase 等,只需在业务表中建立少量的关键索引即可。
  3. 选择适当的字段类型

    char 表示固定长度的字符串类型,该类型的字段存储空间是固定的,会浪费存储空间。

    
            ALTER TABLE order
            ADD COLUMN code CHAR(20) NOT NULL;
          

    varchar 表示可变长度的字符串类型,该类型的字段存储空间会根据实际数据的长度进行调整,不会浪费存储空间。

    
            ALTER TABLE order
            ADD COLUMN code VARCHAR(20) NOT NULL;
          

    如果是固定长度的字段,比如用户的手机号码,一般是 11 位,可以定义为长度为 11 字节的 char 类型。但如果是企业名称字段,如果定义为 char 类型,会存在问题。如果长度定义得过长,例如定义为 200 字节,而实际企业名称只有 50 字节,将浪费 150 字节的存储空间。如果长度定义得过短,例如定义为 50 字节,而实际企业名称有 100 字节,将无法存储,并抛出异常。因此,建议将企业名称改为 varchar 类型。可变长度字段的存储空间较小,可以节省存储空间,对于查询来说,在相对较小的字段中搜索效率显然更高。

    选择字段类型时,应遵循以下原则:

    • 如果可以使用数字类型,就不要使用字符串,因为数字类型的存储空间更小,查询效率更高。
    • 尽量使用小型类型,例如使用 bit 类型存储布尔值,tinyint 类型存储枚举值等。
    • 对于固定长度的字段,可以使用 char 类型。
    • 对于可变长度的字段,可以使用 varchar 类型。
    • 对于金额字段,使用 decimal 类型,避免精度丢失的问题

如何提高sql语句的执行效率

1. SQL优化的原则是:将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。 调整不良SQL通常可以从以下几点切入: ? 检查不良的SQL,考虑其写法是否还有可优化内容 ? 检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写 ? 检查优化索引的使用 ? 考虑数据库的优化器 2. 避免出现SELECT * FROM table 语句,要明确查出的字段。 3. 在一个SQL语句中,如果一个where条件过滤的数据库记录越多,定位越准确,则该where条件越应该前移。 4. 查询时尽可能使用索引覆盖。 即对SELECT的字段建立复合索引,这样查询时只进行索引扫描,不读取数据块。 5. 在判断有无符合条件的记录时建议不要用SELECT COUNT (*)和select top 1 语句。 6. 使用内层限定原则,在拼写SQL语句时,将查询条件分解、分类,并尽量在SQL语句的最里层进行限定,以减少数据的处理量。 7. 应绝对避免在order by子句中使用表达式。 8. 如果需要从关联表读数据,关联的表一般不要超过7个。 9. 小心使用 IN 和 OR,需要注意In集合中的数据量。 建议集合中的数据不超过200个。 10. <> 用 < 、 > 代替,>用>=代替,<用<=代替,这样可以有效的利用索引。 11. 在查询时尽量减少对多余数据的读取包括多余的列与多余的行。 12. 对于复合索引要注意,例如在建立复合索引时列的顺序是F1,F2,F3,则在where或order by子句中这些字段出现的顺序要与建立索引时的字段顺序一致,且必须包含第一列。 只能是F1或F1,F2或F1,F2,F3。 否则不会用到该索引。 13. 多表关联查询时,写法必须遵循以下原则,这样做有利于建立索引,提高查询效率。 格式如下select sum() from table1 table1, table2 table2, table3 table3 where (table1的等值条件(=)) and (table1的非等值条件) and (table2与table1的关联条件) and (table2的等值条件) and (table2的非等值条件) and (table3与table2的关联条件) and (table3的等值条件) and (table3的非等值条件)。 注:关于多表查询时from 后面表的出现顺序对效率的影响还有待研究。 14. 子查询问题。 对于能用连接方式或者视图方式实现的功能,不要用子查询。 例如:select name from customer where customer_id in ( select customer_id from order where money>1000)。 应该用如下语句代替:select name from customer inner join order on _id=_id where >100。 15. 在WHERE 子句中,避免对列的四则运算,特别是where 条件的左边,严禁使用运算与函数对列进行处理。 比如有些地方 substring 可以用like代替。 16. 如果在语句中有not in(in)操作,应考虑用not exists(exists)来重写,最好的办法是使用外连接实现。 17. 对一个业务过程的处理,应该使事物的开始与结束之间的时间间隔越短越好,原则上做到数据库的读操作在前面完成,数据库写操作在后面完成,避免交叉。 18. 请小心不要对过多的列使用列函数和order by,group by等,谨慎使用disti软件开发t。 19. 用union all 代替 union,数据库执行union操作,首先先分别执行union两端的查询,将其放在临时表中,然后在对其进行排序,过滤重复的记录。 当已知的业务逻辑决定query A和query B中不会有重复记录时,应该用union all代替union,以提高查询效率。

ORACLE SQL语句优化技术分析

为了让更多的新手受益,我抽空把SQL语句优化部分进行了整理,希望大家一起进步。

一、操作符优化

1、IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:

ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。

推荐方案:在业务密集的SQL当中尽量不采用IN操作符,用EXISTS 方案代替。

2、NOT IN操作符

此操作是强列不推荐使用的,因为它不能应用表的索引。

推荐方案:用NOT EXISTS 方案代替

3、IS NULL 或IS NOT NULL操作(判断字段是否为空)

判断字段是否为空一般是不会应用索引的,因为索引是不索引空值的。

推荐方案:用其它相同功能的操作运算代替,如:a is not null 改为 a0 或a’’等。不允许字段为空,而用一个缺省值代替空值,如申请中状态字段不允许为空,缺省为申请。

4、 及操作符(大于或小于操作符)

大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A2与A=3的效果就有很大的区别了,因为A2时ORACLE会先找出为2的记录索引再进行比较,而A=3时ORACLE则直接找到=3的记录索引。

5、LIKE操作符

LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。

一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。

6、UNION操作符

UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如: select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys union all select * from ls_jg_dfys

二、SQL书写的影响

1、同一功能同一性能不同写法SQL的影响。

如一个SQL在A程序员写的为 Select * from zl_yhjbqk

B程序员写的为 Select * from _yhjbqk(带表所有者的前缀)

C程序员写的为 Select * from (大写表名)

D程序员写的为 Select * from (中间多了空格)

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同,则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。

2、WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如: Select * from zl_yhjbqk where dy_dj = 1KV以下 and xh_bz=1 Select * from zl_yhjbqk where xh_bz=1 and dy_dj = 1KV以下 以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = 1KV以下条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。

3、查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下,ORACLE会按表出现的顺序进行链接,由此可见表的顺序不对时会产生十分耗服物器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)

三、SQL语句索引的利用

1、操作符优化(同上)

2、对条件字段的一些优化

采用函数处理的字段不能利用索引,如:

substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate), 优化处理:sk_rq=trunc(sysdate) and sk_rqtrunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引,如:ss_df+2050,优化处理:ss_df30

SQL五大命令

‘X’ || hbs_bh’X’,优化处理:hbs_bh’’

sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5

hbs_bh=,优化处理:hbs_bh=’ ’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引,如:ys_dfcx_df,无法进行优化 qc_bh || kh_bh=’’,优化处理:qc_bh=’5400’ and kh_bh=’’

四、其他

ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。

免责声明:本文转载或采集自网络,版权归原作者所有。本网站刊发此文旨在传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及版权、内容等问题,请联系本网,我们将在第一时间删除。同时,本网站不对所刊发内容的准确性、真实性、完整性、及时性、原创性等进行保证,请读者仅作参考,并请自行核实相关内容。对于因使用或依赖本文内容所产生的任何直接或间接损失,本网站不承担任何责任。

标签: SQL

“SQL-五大提升-语句性能的小窍门 (SQL五大命令)” 的相关文章

的原因-高性能场景下选择-Postgres-SQL-而非-MySQL

的原因-高性能场景下选择-Postgres-SQL-而非-MySQL

Introduction When it comes to databases, MySQL and PostgreSQL are two of the most popular optio...

在不同SQL平台取前百分之N的记录方法

在不同SQL平台取前百分之N的记录方法

最近帮业务部门梳理业务报表,其中有个需求是就算某指标等待时间最长的前百分之十,其实就是对等待时长进行倒序排序后,取结果集的前百分之十。这个需求在SQLServer和Oracle上都很容易实现,甚...

Server-处置-全面指南-数据库备份环节中日志损坏疑问-SQL (server是什么意思)

Server-处置-全面指南-数据库备份环节中日志损坏疑问-SQL (server是什么意思)

译者|李睿 日志损坏疑问理论出当初对事务日志启动备份的时刻。其失误是这样形容的: 信息26019,级别16,形态1,第1行BACKUP在数据库日志中检测到损坏。无关具体信息,请检查失误...

连接最全总结-SQL-提升你的数据库查询技能 (连接最全总结的软件)

连接最全总结-SQL-提升你的数据库查询技能 (连接最全总结的软件)

在关系数据库管理系统 (RDBMS) 中,连接操作是连接两个或多个表的数据的强大工具。通过了解不同的连接类型及其使用方法,您可以构建高效且准确的查询,从而获得所需的见解。 SQL 中的连接类...

生产真实案例-几条SQL把服务器干崩了-事后还大言不惭! (有关生产的案例)

生产真实案例-几条SQL把服务器干崩了-事后还大言不惭! (有关生产的案例)

最近,我遇到了一次令人难忘的凌晨数据库服务器危机,并在事后撰写了报告。整个处理过程令人无语又气愤,特此分享。 一、事件背景 事件背景:...

SQL-如何让主机轰然倒塌-震惊的消费案例-几条-而闹事者却大言不惭! (sql如何导入数据)

SQL-如何让主机轰然倒塌-震惊的消费案例-几条-而闹事者却大言不惭! (sql如何导入数据)

确真实公司跟着老大能学到很多常识啊,之前确实也不怎样了解线程安保疑问和一些处置方案,如今了解了,也终于基于无法变类成功了一个便捷的配置,明日找老大帮我看看,小菜心里想着,脸上显露了满意的浅笑。...

SQL-中的排名技术-深入探讨-数据库技能进阶 (sql中的order by是什么意思)

SQL-中的排名技术-深入探讨-数据库技能进阶 (sql中的order by是什么意思)

SQL Server 提供了以下四个常用的排名函数: ROW_NUMBER() RANK() DENSE_RANK() NTILE()...