在线客服
扫描二维码
下载博学谷APP扫描二维码
关注博学谷微信公众号
一直以来,MySQL数据库优化都是每个程序员要面对的难题。网上有关SQL优化的教程也有很多,小编今天整理汇总了数据库优化方案。数据库优化主要从以下三方面:数据存储分区,表索引,语句优化。
1、数据存储分区
我们的理财系统,购买产品的用户来自不同的区,考虑到产品购买数量接近上亿条,单纯的为表建立索引不能满足性能的需要,因此交易记录按省份做了列表分区,使不同省的记录存储到不同的数据分区,当查询数据加上省份条件,只会检索对应分区的数据,大大缩小数据检索范围,从而提高查询性能,(备注:每个项目组按照自己项目的实际分区情况举例说明)分区还有:范围分区(比如按照日期字段建立分区,一个季度或者一个月的数据划分为一个区)散列分区(不指定分区条件,oracle自动将数据平均分配)复合分区(比如:做了分区后,每个分区再分区)
2、表索引
为建立好分区以后,在分区里面添加索引,进一步提高数据的查询速度,我们的项目里用到的索引比如(客户信息表 姓名建立btree索引,身份证号建立唯一索引,客户编号建立主键,主键自带唯一)(产品表 产品类别建立位图索引) (经常用到的表关联条件建立索引)[备注:按照自己项目建立索引的实际情况举例说明]
索引之所以会提高查询速度,索引有一套独立的系统表存储数据与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)避免向客户端返回大数据量
以上就是数据库优化方案的全部内容,希望可以帮助到有需要的小伙伴。
— 申请免费试学名额 —
在职想转行提升,担心学不会?根据个人情况规划学习路线,闯关式自适应学习模式保证学习效果
讲师一对一辅导,在线答疑解惑,指导就业!
相关推荐 更多
MYSQL好学吗?一般要学多久?
Mysql好学吗?一般要学多久?对于有一定计算机语言基础的同学来说,Mysql算是非常好学的数据库了。学多久要看自身基础,没有基础的同学也不用担心,Mysql绝对可以作为大家入门数据库的第一步。如果报名线上培训班有老师的指导的话,最快8天就可以掌握Mysql数据库的知识了。那么该如何学习Mysql呢?
16055
2019-08-16 16:38:27
MySQL数据库优化可以从哪些方面入手?具体怎么做?
MySQL数据库优化可以从哪些方面入手?具体怎么做?一般我们主要从优化SQL语句、优化数据库结构、优化Mysql服务器等几方面入手,而优化SQL语句又可以分为优化查询语句和其他执行语句;数据库结构也可以从字段类型、字符编码、适当拆分、增加冗余和数据库表来做优化;至于优化Mysql服务器就更简单了,需要遵循一定的优化原则即可。现在我们来看看具体的优化方案吧!
5122
2019-10-24 15:26:42
MySQL学习总结之DQL查询语句
DQL查询语句又叫数据库查询语句,这是Java学习者在学习MySQL时,必须了解和掌握的知识点之一。众所周知,DQL查询语句有四种查询方式:排序查询、聚合函数、分组查询和分页查询。下面让我们一一仔细梳理这四种查询方式,有需要学习的小伙伴赶紧来看看MySQL学习总结之DQL查询语句的知识点吧!
4427
2020-02-11 23:24:05
2年Java开发需要具有什么水平?
每个程序员都应该有自己的职业规划,清楚的了解自己在不同的阶段应该掌握什么技能。2年工作经验的Java程序员应该具备的技能是Java程序员们比较关心的内容。掌握得越多能得到的评价拿到的薪水势必也越高。
3660
2021-06-07 16:37:12
这一篇彻底告诉你ORACLE的前世今生
ORACLE数据库系统是美国ORACLE公(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一
3234
2021-08-17 18:51:21