在线客服
扫描二维码
下载博学谷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和Oracle的区别?
当你面试的时候突然被问到:MySQL和Oracle的区别?你会怎么回答?其实面试官提问的时候主要是想考察你对数据库的理解以及熟练程度,什么情况下使用 mysql,什么情况下使用 Oracle,各有什么优缺点!下面小编带大家一起分析这个问题的解答方式!
14105
2019-06-28 18:29:20
怎么算一个合格的Java架构师?需要具备什么技能?
怎么算一个合格的Java架构师?需要具备什么技能?要从一名普通的Java程序员成长为一个合格的Java架构师并不容易,需要积累一定的项目经验,拓宽自己的视野,在工作中经常能够深度思考。具体需要掌握阅读、分析源码、掌握分布式架构、微服务架构、性能优化、并发编程等等技能。下面我们来详细看一看Java架构师的必备能力。
4688
2019-10-25 10:24:52
如何用Redis轻松实现秒杀系统?
如何用Redis轻松实现秒杀系统?一般来说有三个办法,即写入内存而不是写入硬盘;异步处理而不是同步处理;分布式处理。相信只要大家做好以上这三点,就能轻松用Redis轻松实现秒杀系统。下面本文将从秒杀的定义,秒杀系统场景特点,秒杀架构设计理念,架构方案和实际案例讲解等方面,为大家介绍如何实现秒杀系统。
6105
2019-11-04 15:00:18
普通的Java架构师要掌握哪些专业技术知识点?
普通的Java架构师要掌握专业技术知识点,一般来说普通的Java架构师要掌握Java架构基础、Java架构核心技术、主流技术框架、系统架构、分布式系统、项目实战能力、个人能力与专业素养等相关的知识点。
5194
2020-02-14 17:37:42
这一篇彻底告诉你ORACLE的前世今生
ORACLE数据库系统是美国ORACLE公(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一
3393
2021-08-17 18:51:21