在线客服
扫描二维码
下载博学谷APP扫描二维码
关注博学谷微信公众号
一直以来,MySQL数据库优化都是每个程序员要面对的难题。网上有关SQL优化的教程也有很多,数据库优化主要从以下三方面:数据存储分区,表索引,语句优化。小编今天整理了比较全面的数据库优化方案,下面我们来一起看看吧。
1、数据存储分区
我们的理财系统,购买产品的用户来自不同的区,考虑到产品购买数量接近上亿条,单纯的为表建立索引不能满足性能的需要,因此交易记录按省份做了列表分区,使不同省的记录存储到不同的数据分区,当查询数据加上省份条件,只会检索对应分区的数据,大大缩小数据检索范围,从而提高查询性能,(备注:每个项目组按照自己项目的实际分区情况举例说明)
分区还有:范围分区(比如按照日期字段建立分区,一个季度或者一个月的数据划分为一个区)散列分区(不指定分区条件,oracle自动将数据平均分配)复合分区(比如:做了分区后,每个分区再分区)
2、表索引
为建立好分区以后,在分区里面添加索引,进一步提高数据的查询速度。
索引之所以会提高查询速度,索引有一套独立的系统表存储数据与rowid的对应关系(如书的目录),比如btree索引,他是一个树形结构图,有根节点,分支节点,叶子节点,根节点只有一个,分支节点数量不固定由oracle合理分配,分支节点存储数据的范围,最终的分支节点下面有叶子节点,叶子节点负责存储具体的值和rowid.比如:根节点存储大于50小于50两个范围,两个支节点,大于50的支节点存储50-60,60-70,大于100等区段,每个区段都有对应的叶子节点,比如大于100的区段下面的叶子节点存储100,101,102和对应的rowid等具体数据,100,101,102的值即为表中建立索引的列(比如num)的值,当编写sql语句where num=101时,就会通过检索索引表,查询到叶子节点上存储的rowid,然后根据rowid查询对应的数据,避免了对数据表的全表扫描。
当然,索引不是越多越好,索引适合建立在经常作为条件列(即为where之后作为条件的列,包括关联条件),以及order by的列.如果对于增删改查性能要求特别高而查询要求不高的表就不建议建立索引,因为索引降低增删改查效率.
3、sql语句优化
(1)尽量避免全表查询。
(2)避免where子句中字段进行null判断,否则导致全表扫描select id from t where num is null。
尽可能添加not null .备注,描述,评可以设置为null.null需要空间,比如:char(100),在字段建立时空间固定,不论插不插值都是占用100个字符,如果varchar,null不占空间.可以再num上设置默认值为0,确保表中num列没有null值。
Select id from t where num=0
(3)尽量避免where子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
(4)尽量避免where子句中使用or来连接条件,如果一个字段有索引,一个字段没有,将导致引擎放弃索引而进行全表扫描。
Select id from t where num=10 or name=’admin’错误
可以这样:
Select id from t where num =10
Union all
Select id from t where name=’admin’
(5)In 和not in 也要慎用,导致全表扫描如
Select id from t where num in(1,2,3)
对于连续的值能用between就不要用in
Select id from t where num between 1 and 3
大多数用exists代替in
Select num from a where num in(select num from b)
Select num from a where exists(select 1 from b where num=a.num)
(6)如果在where子句中使用参数,会导致全表扫描.因为sql只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;他必须在编译时进行选择.然而,如果在编译时建立访问计划,变量的值还是未知的,因为无法作为索引选择的输入项。如下将进行全表扫描:
Select id from t where num =@num
可以改为强制查询使用索引
Select id from t with(index(索引名)) where num=@num
应尽量避免在where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描,如:
Select id from t where num/2=100
应该为
Select id from t where num =100*2
(7)尽量避免where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描,如:
Select id from t where substring(name,1,3) = ‘abc’ -----name以abc开头的name
Select id from t where datediff(day,createdate,’2005-11-30’) = 0
改为
Select id from t where name like ‘abc%’
Select id from t where createdate >=’2005-11-30’ and createdate <’2005-12-01
(7)不要在where 子句中的’=’ 左边进行函数,算数运算或者其他表达式计算,否则系统将可能无法正确使用索引。
(8)Update语句,如果只更改1,2个字段,不要update全部字段,否则频繁调用会引起性能消耗,同时带来大量日志。
(9)对于多张大数据表的表join,先分页在join,否则逻辑读会很高,性能差。
(10)索引固然可以提高相应select 的效率,但同时也降低insert 和update效率,因为insert或update 可能会重建索引,所以一个表最好不要超过6个索引。
(11)尽量使用数字型字段。
(12)尽可能使用varchar/nvarchar代替char/nchar.因为节省空间,查询效率高。
(13)不要用select * 要使用select 字段。
(14)避免向客户端返回大数据量。
(15)避免向客户端返回大数据量
以上就是数据库优化方案的全部内容,希望可以帮助到有需要的小伙伴。
— 申请免费试学名额 —
在职想转行提升,担心学不会?根据个人情况规划学习路线,闯关式自适应学习模式保证学习效果
讲师一对一辅导,在线答疑解惑,指导就业!
相关推荐 更多
组合模式深度解析
我们常常谈的组合模式,其实就是允许你将对象组合成树形结构来表现 "整体/部分" 层次结构。本文将从组合模式的定义、包含角色、通用源代码以及优缺点几方面来详细分析,帮助大家深刻了解Java设计模式之组合模式。
7411
2019-07-29 17:51:14
高级Java 程序员要掌握哪些职业技能?
要学习Core Java、多线程并发编程、I/O、JVM、设计模式、常用数据库JavaWeb开发的框架、Netty 等基础知识,Netty 简单易用容易上手,但网络编程复杂,会存在各种的状况和可能性。
3163
2020-10-23 15:45:43
从电工逆袭到月薪15k的Java开发工程师
之前在电厂工作,因为渴望进入办公室的环境经过再三考虑决定来到Java培训班学习改变命运。目前已经毕业并成功找到一份薪资15k的工作。
2408
2021-06-03 14:22:57
狂野架构师学习效果好不好?互联网Java架构师前景怎么样?
博学谷狂野架构师学习效果好不好?课程怎么样?狂野架构师课程共分为16个模块,分布式篇、微服务篇、源码篇、消息篇、数据篇、性能篇、云服务篇、⼯具篇、设计篇、算法篇、⿊⻢顺⻛⻋实战项⽬、⾯试突击篇、企业级通⽤解决⽅案、企业级实战项⽬库、⼈⼯智能、数据挖掘 。从技术应⽤、原理讲解、源码剖析、项⽬实战,并且整合了⽬前多⾏业通⽤的技术解决⽅案,拿来即⽤。
3544
2022-09-29 16:42:05
成为架构师,读书之外什么更重要?
做架构师这几年,除了去官网看技术说明书学习前沿技术,也买了不少架构师思维层面的书籍,硬性与软性实力同时提升,让自己做工作才游刃有余。 最近有不少同学留言让我推荐架构师相关的书籍,特此找出曾经拜读的几本书,分享给大家,也希望大家多看看别人的看法和观念,开拓一下思路。
1573
2022-07-25 11:32:10